SUM函数复杂应用试题及答案

SUM函数复杂应用试题及答案考试时间：______分钟总分：______分姓名：______一、单项选择题（每题2分，共30分）1.在Excel中，要计算A1到A10单元格中所有大于5的整数的总和，以下公式正确的是？A.=SUM(A1:A10>5)B.=SUM(IF(A1:A10>5,A1:A10,0))C.=SUMPRODUCT((A1:A10>5)*A1:A10)D.=SUM(A1:A10)-SUMIF(A1:A10,"<5")2.如果要统计B列中只包含字母字符的单元格数量，可以使用以下哪个函数？A.COUNT(B:B)B.COUNTA(B:B)C.COUNTBLANK(B:B)D.COUNTIF(B:B,"*")3.函数`=SUMIF(C1:C10,">20",D1:D10)`的功能是？A.计算C1到C10单元格中大于20的天数总和B.计算D1到D10单元格中对应C1到C10单元格大于20的记录总和C.计算C1到C10单元格中大于20的单元格数量D.计算C1到C10和D1到D10中所有大于20的值的总和4.在Excel中，SUMIFS函数与SUMIF函数的主要区别在于？A.SUMIFS可以处理文本条件，SUMIF只能处理数字条件B.SUMIFS可以同时使用多个条件进行求和，SUMIF只能使用一个条件C.SUMIFS函数返回的结果总是大于SUMIF函数D.SUMIFS是Excel2007以后版本新增的函数，SUMIF是早期版本就有的函数5.要查找订单号在F1单元格中指定的订单信息，并返回该订单对应的客户名称（假设客户名称在查找区域的第3列），以下公式正确的是？A.=VLOOKUP(F1,查找区域,1,FALSE)B.=VLOOKUP(F1,查找区域,3,TRUE)C.=VLOOKUP(F1,查找区域,3,FALSE)D.=HLOOKUP(F1,查找区域,3,FALSE)6.使用VLOOKUP函数时，如果设置第四个参数为TRUE或者省略，表示？A.函数将返回查找区域的第一列数据B.函数将忽略查找区域的第一列C.函数将在查找区域中查找精确匹配的值，如果没有找到则返回#N/AD.函数将在查找区域中查找精确匹配的值，如果没有找到则返回07.公式`=SUMPRODUCT((A1:A5="完成")*(B1:B5))`的功能是？A.计算A1到A5单元格中等于"完成"的单元格数量B.计算B1到B5单元格中所有值的总和C.计算A1到A5单元格中等于"完成"的行，其对应B1到B5单元格中值的总和D.将A1到A5单元格和B1到B5单元格中的值相乘后的总和8.要计算E列中不同部门数量的总和（假设部门名称在E列），可以使用以下哪个函数组合？A.=SUM(1/COUNTIF(E:E,E:E))B.=SUMPRODUCT(1/COUNTIF(E:E,E:E))C.=SUMIF(E:E,"<>",E:E)D.=COUNTA(E:E)9.如果要计算A列中所有偶数值的平均值，以下公式正确的是？A.=AVERAGE(A:A)B.=AVERAGEIF(A:A,"偶数")C.=AVERAGE(IF(MOD(A:A,2)=0,A:A))D.=AVERAGEPRODUCT((MOD(A:A,2)=0)*A:A)10.在SUM与IF组合的数组公式中，例如`=SUM(IF(A1:A10>5,B1:B10,0))`，如果取消数组公式的输入（按Enter键），Excel会？A.提示错误信息B.将公式结果放在A1单元格C.将公式结果放在B1单元格D.不改变任何单元格的显示11.要统计C列中“销售部”和“市场部”的员工人数总和，可以使用？A.=COUNTIF(C:C,"销售部")+COUNTIF(C:C,"市场部")B.=SUMIF(C:C,{"销售部","市场部"})C.=SUMPRODUCT((C:C="销售部")+(C:C="市场部"))D.=COUNTIFS(C:C,"销售部",C:C,"市场部")12.函数`=SUMPRODUCT((A1:A5>=10)*(A1:A5<=20),B1:B5)`的功能是？A.计算A1到A5单元格中大于等于10且小于等于20的单元格数量B.计算B1到B5单元格中所有值的总和C.计算A1到A5单元格中满足条件“大于等于10且小于等于20”的行，其对应B1到B5单元格中值的总和D.将A1到A5单元格和B1到B5单元格中的值相乘后的平均值13.在使用VLOOKUP查找时，如果查找值不在查找列的最小值位置，但存在，函数会返回？A.查找列中最小值对应的值B.查找列中最大值对应的值C.查找值本身D.#N/A错误14.要计算D列中所有大于平均值的数据之和，以下公式正确的是？A.=SUMIF(D:D,">平均值",D:D)B.=SUM(D:D)-AVERAGE(D:D)C.=SUMPRODUCT((D:D>AVERAGE(D:D))*D:D)D.=SUM((D:D>平均值)*D:D)15.函数`=SUM((B1:B5="东")*(C1:C5="A"))`的功能是？A.计算B1到B5单元格中等于"东"的单元格数量B.计算C1到C5单元格中等于"A"的单元格数量C.计算B1到B5单元格中等于"东"且对应C1到C5单元格中等于"A"的行，其A列值的总和D.将B1到B5单元格和C1到C5单元格中的值相乘后的总和二、多项选择题（每题3分，共15分，每题有两个或两个以上正确答案）16.下列关于SUMIF函数的描述，正确的有？A.可以同时设置多个条件进行求和B.第一个参数是求和区域C.第二个参数是条件区域D.第三个参数是条件值E.条件区域与求和区域可以不同17.VLOOKUP函数的语法结构通常包含哪些参数？A.查找值B.查找区域C.返回列索引号D.查找方式（TRUE或FALSE）E.求和区域18.以下哪些函数可以用于统计满足特定条件的单元格数量？A.COUNTB.COUNTAC.COUNTBLANKD.COUNTIFE.SUMIF19.SUMPRODUCT函数在处理数组时，可以实现的功能有？A.对数组中的元素进行相乘B.对数组中的元素进行求和C.实现多个条件的逻辑判断D.替代数组公式进行某些计算E.对数组中的元素进行平均值计算20.下列关于SUM与IF组合应用的说法，正确的有？A.可以使用数组公式实现B.可以使用SUMPRODUCT函数替代C.必须使用数组公式才能实现条件求和D.如果条件判断结果不是简单的TRUE/FALSE，可能需要嵌套其他函数E.公式中的IF函数可以处理多个嵌套条件三、判断题（每题1分，共10分，对的打√，错的打×）21.SUM函数可以与VLOOKUP函数直接嵌套使用来实现复杂的求和计算。（）22.使用SUMIF函数时，条件区域和求和区域必须相同。（）23.VLOOKUP函数总是从查找区域的第一列开始查找。（）24.COUNTIF函数可以同时统计多个不同条件下的单元格数量。（）25.SUMPRODUCT函数的第一个参数可以是数值数组，也可以是单元格区域或数组公式。（）26.在Excel中，任何求和计算都可以通过SUM函数完成，不需要其他函数的帮助。（）27.如果SUMIFS函数的查找结果为空，则SUMIFS函数返回的值一定是0。（）28.使用数组公式时，必须按Ctrl+Shift+Enter组合键确认输入。（）29.AVERAGEIF函数可以计算满足单个条件的数据的平均值。（）30.VLOOKUP函数的查找结果是文本时，可以将其作为SUM函数的参数进行求和。（）四、操作题（共15分）假设有一个数据集，包含以下几列数据：部门（A列，文本：“销售部”、“市场部”、“技术部”）员工姓名（B列）销售额（C列，数字）销售日期（D列，日期）请根据以下要求，编写相应的Excel公式：31.（4分）计算“销售部”和“市场部”部门的总销售额之和。32.（5分）计算“技术部”部门在2023年（包括2023年）的销售额总和。33.（6分）计算每个部门的平均销售额，并将结果放在新的一列（假设在E列，部门在A列，平均销售额在E列）。要求公式能够自动适应所有部门。试卷答案一、单项选择题1.B解析：SUM需要数值参数。选项A中，A1:A10>5返回一个数组，SUM直接作用时会报错。选项C中，SUMPRODUCT需要乘积数组，*运算符在这里正确地将逻辑数组与数值数组相乘。选项D中，SUMIF计算的是大于0的值的总和，不满足题意。2.D解析：COUNT统计数字个数。COUNTA统计非空单元格个数。COUNTBLANK统计空单元格个数。COUNTIF统计满足单个条件的单元格个数，"*"代表任意字符，COUNTIF(B:B,"*")统计B列非空单元格个数。3.B解析：SUMIF的第一个参数是求和区域，第二个参数是条件区域及其条件值，第三个参数是求和区域。该公式计算C1到C10中大于20的单元格对应的D1到D10单元格中值的总和。4.B解析：SUMIFS允许多个条件，这是它与SUMIF的主要区别。SUMIF只能有一个条件区域和条件值。其他选项描述不准确。5.C解析：VLOOKUP的第一个参数是查找值，第二个参数是查找区域，第三个参数是返回列的索引号（从1开始计数），第四个参数是查找方式（FALSE表示精确查找，TRUE或省略表示近似查找）。客户名称在查找区域的第3列，需要精确查找，故第三个参数为3，第四个参数为FALSE。6.D解析：VLOOKUP的第四个参数（range_lookup）控制查找方式。TRUE或省略表示查找近似匹配，即查找区域的第一列中大于等于查找值的最小值，或者查找值介于查找区域第一列的最小值和最大值之间。如果找不到精确匹配，则返回基于查找区域第一列的最小值和最大值的近似匹配结果。FALSE表示精确查找。7.C解析：SUMPRODUCT接受数组或数组表达式作为参数。`(A1:A5="完成")`生成一个逻辑数组（TRUE/FALSE）。`*`运算符将逻辑数组与B1:B5中的值相乘，TRUE变为1，FALSE变为0。SUMPRODUCT计算乘积数组中所有元素的和，即满足A列等于"完成"且对应的B列有值的行的B列值之和。8.B解析：`COUNTIF(E:E,E:E)`会生成一个数组，其中每个元素对应E:E中对应元素的计数（如果E列没有重复，则每个元素计数为1）。`1/COUNTIF(E:E,E:E)`生成一个数组，每个元素为1。`SUMPRODUCT`计算这个逻辑数组的总和，实际是统计了E列非空单元格的数量，因为每个非空单元格都会被计数一次。9.C解析：AVERAGE需要数值参数。BVERAGEIF没有此函数。选项C使用了数组公式语法（需要按Ctrl+Shift+Enter），`MOD(A:A,2)=0`生成一个逻辑数组，表示A列每个单元格是否为偶数。`IF(...,A:A)`根据逻辑数组返回对应的A列值或0。SUMPRODUCT计算这个数组中所有元素的和，即所有偶数值的总和，再除以偶数的数量得到平均值。注意：如果A列有文本或其他非数值类型，需要先处理。10.B解析：在Excel中，输入了数组公式（以Ctrl+Shift+Enter结束输入时，单元格地址会自动加上{}花括号）后，按普通Enter键，数组公式不会改变，但花括号会消失，公式结果显示在第一个单元格（通常是数组公式开始的位置）。11.A解析：`COUNTIF(C:C,"销售部")`统计C列中等于"销售部"的单元格数量。`COUNTIF(C:C,"市场部")`统计C列中等于"市场部"的单元格数量。两者相加得到两个部门的总人数。12.C解析：`A1:A5>=10`生成一个逻辑数组。`A1:A5<=20`生成另一个逻辑数组。两个逻辑数组相乘（*），只有当两个条件同时为TRUE时，结果才为TRUE，否则为FALSE。`SUMPRODUCT`将这个逻辑数组与B1:B5中的值相乘，得到满足两个条件的行的B列值。SUMPRODUCT计算这些值的总和。13.D解析：VLOOKUP的查找方式参数（第四个参数）。如果设置为TRUE或省略，查找区域会被排序，然后查找大于等于查找值的最小值所在的行。如果该值存在，则返回该行对应的返回列值。如果不存在，则返回#N/A。14.C解析：`AVERAGE(D:D)`计算D列的平均值。`D:D>AVERAGE(D:D)`生成一个逻辑数组，表示每个D列值是否大于平均值。`(D:D>AVERAGE(D:D))*D:D`将逻辑数组与D列值相乘，大于平均值的行对应值不变，小于等于平均值的行对应值变为0。`SUMPRODUCT`计算这个数组中所有元素的和，即所有大于平均值的数据之和。15.C解析：`B1:B5="东"`生成一个逻辑数组。`C1:C5="A"`生成另一个逻辑数组。两个逻辑数组相乘（*），只有当B列值等于"东"且对应C列值等于"A"时，结果才为TRUE，否则为FALSE。`SUM`计算这个逻辑数组中TRUE（值为1）的总和，即满足这两个条件的行对应的A列值的总和。二、多项选择题16.B,C,D,E解析：SUMIF的第一个参数是求和区域，第二个是条件区域，第三个是条件值。它可以同时使用多个条件（通过增加更多的条件区域和条件值参数）。条件区域和求和区域可以不同。17.A,B,C,D解析：VLOOKUP的语法是：VLOOKUP(查找值,查找区域,返回列索引号,[查找方式])。其中，查找值、查找区域、返回列索引号是必需参数，查找方式是可选参数（默认为TRUE）。18.C,D解析：COUNTBLANK统计空单元格数量。COUNTIF统计满足单个条件的单元格数量。SUMIF虽然主要用于求和，但其核心也是统计满足条件的单元格数量（用于决定哪些单元格参与求和）。COUNT和COUNTA不专门用于统计满足条件的单元格。19.A,B,C,D,E解析：SUMPRODUCT可以接收数组参数。它可以对数组元素进行相乘（A），然后求和（B）。可以通过逻辑数组实现多个条件的判断（C），例如`SUMPRODUCT((条件1)*(条件2)*...)`。它可以替代某些需要数组公式的计算（D），例如条件求和、条件计数等。它也可以通过对数组求和后除以元素个数来计算平均值（E）。20.A,B,D,E解析：SUM与IF组合可以实现条件求和（A）。SUMPRODUCT可以替代SUM+IF的数组公式，避免手动输入数组公式（B）。当条件比较复杂时，可能需要嵌套其他函数（如AND,OR,VLOOKUP等）在IF函数内部或作为SUMPRODUCT的参数（D）。IF函数可以包含多个嵌套条件（E）。三、判断题21.√解析：SUM函数可以直接作为数组公式的第一部分，IF函数根据条件返回对应的值或0，两者结合可以实现条件求和。例如`=SUM(IF(A