2026年办公表格函数运用试题及答案

2026年办公表格函数运用试题及答案一、单项选择题（每题5分，共25分）1.在Excel365中，要提取A2单元格18位身份证号的出生日期，输出格式为YYYY年MM月DD日，以下哪个公式正确A.=TEXT(MID(A2,7,8),"0000年00月00日")B.=MID(TEXT(A2,7,8),"0000年00月00日")C.=TEXT(MID(A2,8,8),"0000年00月00日")D.=TEXT(LEFT(A2,7,8),"0000年00月00日")答案：A解析：18位身份证号的出生日期从第7位开始，共占8个字符，MID函数的语法为MID(文本,起始位置,提取字符数)，因此先通过MID(A2,7,8)提取出8位日期字符串，再用TEXT函数将字符串转换为指定格式，因此A正确；C选项起始位置错误，BD选项函数参数结构错误，无法运行。2.需要对B列中大于10000小于50000的销售额统计个数，以下哪种写法能得到正确结果A.COUNTIF(B:B,">10000")COUNTIF(B:B,">=50000")B.COUNT(B:B,">10000","<50000")C.COUNTIFS(B:B,">10000",B:B,"<50000")D.A和C都正确答案：D解析：COUNTIFS支持多条件计数，直接输入两个同区域的条件即可得到符合范围的计数结果；同时也可以用单条件COUNTIF相减，用大于10000的总个数减去大于等于50000的个数，剩下的就是10000到50000之间的个数，两种方法结果一致，因此D正确。3.动态数组函数XLOOKUP查找不到匹配值，且未手动设置未找到参数时，默认返回的结果是A.#N/AB.#VALUE!C.空文本""D.0答案：A解析：XLOOKUP的语法为XLOOKUP(查找值,查找区域,返回区域,[未找到值],[匹配模式],[搜索模式])，其中未找到值为可选参数，未手动设置时默认返回错误值#N/A，因此A正确。4.要计算A1:A10区域中非空不重复值的个数，Excel365环境下以下最简公式是A.=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))B.=COUNTA(UNIQUE(A1:A10))C.=COUNT(UNIQUE(FILTER(A1:A10,A1:A10<>"")))D.=SUMPRODUCT(--(COUNTIF(A1:A10,A1:A10)=1))答案：C解析：题目要求计算非空不重复值，选项B的COUNTA会把区域内的空单元格也统计为1个不重复值，结果错误；A选项遇到空单元格时会出现除零错误，结果不准确；C选项先用FILTER过滤掉所有空单元格，再用UNIQUE提取不重复值，最后用COUNT统计个数，逻辑准确且公式简洁，因此C正确。5.函数INDIRECT的核心作用是A.返回由文本字符串指定的单元格引用B.对数据区域进行间接排序C.提取括号内的间接计算结果D.返回当前工作表的名称答案：A解析：INDIRECT函数的核心作用就是将文本形式的单元格地址转换为实际可引用的单元格，常用于生成动态引用，因此A正确。二、多项选择题（每题6分，共18分）1.以下属于Excel365原生动态数组函数的有A.UNIQUEB.FILTERC.SORTD.XLOOKUPE.VLOOKUP答案：ABCD解析：VLOOKUP是传统查找函数，不属于动态数组体系，无法自动溢出结果；UNIQUE（提取不重复值）、FILTER（条件筛选）、SORT（数据排序）都是原生动态数组函数，XLOOKUP支持动态数组溢出结果，属于新动态数组体系的函数，因此ABCD正确。2.使用VLOOKUP实现逆向查找（查找值在查找区域右侧，需要返回左侧列的值），以下可行的写法是A.=VLOOKUP(A1,IF({1,0},B:B,A:A),2,0)B.=VLOOKUP(A1,CHOOSE({1,2},B:B,A:A),2,0)C.=VLOOKUP(A1,A:B,1,0)D.=VLOOKUP(A1,SORT(A:B,2),2,0)答案：AB解析：VLOOKUP要求查找值必须在查找区域的第一列，逆向查找需要通过IF或者CHOOSE重构数组，将查找列换到第一列，返回列放到第二列，即可让VLOOKUP正常查询，AB两种写法都可以实现重构，CD无法满足逆向查找的要求，因此AB正确。3.以下关于TEXTJOIN函数的说法正确的有A.TEXTJOIN可以选择忽略连接区域中的空单元格B.TEXTJOIN支持自定义分隔符C.TEXTJOIN可以连接多个不连续区域的内容D.Excel所有版本都支持TEXTJOIN函数答案：ABC解析：TEXTJOIN的第一个参数为“是否忽略空值”，设置为TRUE即可忽略空单元格；第二个参数为连接用的分隔符，可以自定义；参数支持输入多个不连续区域，因此ABC正确；TEXTJOIN仅支持Excel2019及以上版本、Excel365，旧版本不支持，因此D错误。三、实操应用题（共57分）1.（10分）某公司考勤表结构：第1行为表头，A2:A200是员工姓名，B2:B200是出勤日期，C2:C200是当日出勤状态，值为"出勤""迟到""事假""旷工"四种。请写出公式，要求自动统计所有员工的旷工天数，支持动态溢出，不需要手动下拉填充。参考答案：方法一（Excel365简洁写法）：=HSTACK(UNIQUE(A2:A200),COUNTIFS(A2:A200,UNIQUE(A2:A200),C2:C200,"旷工"))方法二（兼容LAMBDA写法）：=BYROW(UNIQUE(A2:A200),LAMBDA(x,XLOOKUP(x,UNIQUE(A2:A200),COUNTIFS(A2:A200,x,C2:C200,"旷工"))))解析：首先通过UNIQUE函数提取所有不重复的员工姓名，再用COUNTIFS配合动态数组，按姓名和出勤状态条件计数，结果会自动溢出输出，不需要手动下拉填充，HSTACK可以将员工姓名和对应的旷工天数合并输出，更直观。2.（12分）销售表中B2:B100是单笔订单的销售金额，要求对销售金额分级：小于1000记为C级，1000（含）到5000（含）记为B级，大于5000记为A级，请写出三种不同函数实现的批量分级公式，并说明逻辑。参考答案：写法一（IFS函数）：=IFS(B2<1000,"C级",B2<=5000,"B级",TRUE,"A级")逻辑：IFS是原生多条件判断函数，按顺序匹配条件，满足第一个条件就返回对应结果，先判断小于1000返回C级，再判断小于等于5000，此时符合条件的就是1000-5000区间，返回B级，最后剩余大于5000的用TRUE匹配，返回A级，逻辑清晰，比嵌套IF更简洁。写法二（SWITCH函数）：=SWITCH(TRUE,B2<1000,"C级",B2<=5000,"B级","A级")逻辑：SWITCH将判断表达式设为TRUE，依次匹配条件，满足条件即返回对应结果，逻辑和IFS一致，同样可以实现分级。写法三（XLOOKUP匹配）：=XLOOKUP(B2,{0,1000,5001},{"C级","B级","A级"},,"匹配")逻辑：利用XLOOKUP的区间匹配模式，将分级区间的下限作为查找数组，对应等级作为返回数组，开启近似匹配后，自动匹配对应区间返回等级，后续需要修改分级标准时，只需要修改数组即可，更易维护。3.（12分）当前工作簿有12个月份工作表，名称分别为"1月""2月"……"12月"，每个工作表的A1单元格都存放了对应月份的总销售额，需要在汇总表的A1单元格计算全年销售总额，请写出公式。参考答案：Excel365写法：=SUM(BYCOL(ROW(1:12),LAMBDA(m,INDIRECT(m&"月!A1"))))兼容旧版本写法：=SUMPRODUCT(SUMPRODUCT(INDIRECT(ROW(1:12)&"月!A1")))解析：ROW(1:12)生成1到12的数组，拼接字符串得到每个月份工作表A1单元格的文本地址，通过INDIRECT转换为实际引用，再遍历所有引用得到每个月的销售额，最后求和得到全年总额，不需要手动逐个点击12个工作表的A1，适合批量引用多工作表相同位置数据。4.（11分）A列存放了混杂中文、英文、数字的字符串，请写出提取A2单元格中所有数字的公式（分Excel365版本和兼容旧版本写法）。参考答案：Excel365写法：=CONCAT(FILTER(MID(A2,SEQUENCE(LEN(A2)),1),ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1))))解析：先用LEN得到A2单元格的总字符长度，SEQUENCE生成1到总长度的顺序序列，MID依次提取每个字符，将字符转为数值后用ISNUMBER判断是否为数字，FILTER过滤出所有数字字符，最后用CONCAT拼接成完整的数字字符串，得到结果。兼容旧版本写法：=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))，输入完成后按Ctrl+Shift+回车三键结束即可得到结果。5.（12分）绩效考核表中，A2:A50是员工姓名，B2:B50是业绩得分，C2:C50是能力得分，综合得分规则：综合得分=业绩得分0.7+能力得分0.3，要求提取综合得分前5名的员工姓名，按综合得分从高到低排序，写出Excel365环境下的公式。5.（12分）绩效考核表中，A2:A50是员工姓名，B2:B50是业绩得分，C2:C50是能力得分，综合得分规则：综合得分=业绩得分0.7+能力得分0.3，要求提取综合得分前5名的员工姓名，按综合得分从高到低排序，写出Excel365环境下的公式。参考答案：=LET(s,B2:B500.7+C2:C50,top_s,TAKE(SORT(s,,-1),5),names,XLOOKUP(top_s,s,A2:A50),SORTBY(names,top_s,-1))=LET(s,B2:B500