计算机二级ms-office公式excel汇总题目(难题汇总)

1、第十二套3. 在 “产品销售汇总表”中 ,分别计算各型号产品的一、二季度销量、销售额及合计数，填入相应列中。所有销售额均设为数值型、小数位数0，使用千位分隔符，右对齐。步骤 2： 在 C2 单元格中插入公式 “=SUMIF(一季度销售情况表 !$B$2:$B$44, B2,一季度销售情况表 !$D$2:$D$44) ”，按 Enter 键，双击右下角的填充柄自动填充。第十三套2. 利用 “成绩单 ”、“小分统计 ”和 “分值表 ”工作表中的数据， 完成 “按班级汇总 ”和 “按学校汇总 ” 工作表中相应空白列的数值计算。具体提示如下：“考试学生数”列必须利用公式计算，“平均分 ”列由 “成绩单

2、 ”工作表数据计算得出； “分值表 ”工作表中给出了本次考试各题的类型及分值。（备注：本次考试一共50 道小题，其中【 1】至【 40】为客观题，【 41】至【 50】为主观题） ； “小分统计 ”工作表中包含了各班级每一道小题的平均得分，通过其可计算出各班级的“客观题平均分 ”和 “主观题平均分 ”。（备注：由于系统生成每题平均得分时已经进行了四舍五入操作，因此通过其计算 “客观题平均分 ”和 “主观题平均分 ”之和时，可能与根据 “成绩单 ” 工作表的计算结果存在一定误差） ； 利用公式计算“按学校汇总 ”工作表中的 “客观题平均分 ”和 “主观题平均分 ”，计算方法为：每个学校的所有班级

3、相应平均分乘以对应班级人数，相加后再除以该校的总考生数；1/13 计算 “按学校汇总 ”工作表中的每题得分率， 即：每个学校所有学生在该题上的得分之和除以该校总考生数，再除以该题的分值。 所有工作表中“考试学生数 ”、“最高分 ”、 “最低分 ”显示为整数；各类平均分显示为数值格式，并保留2 位小数；各题得分率显示为百分比数据格式，并保留2 位小数。(2)【解题步骤】步骤 1：切换至 “按班级汇总 ”工作表中，选择C2 单元格，在该单元格中输入“=COUNTIFS(成绩单 !$A$2:$A$950, 按班级汇总 !$A2, 成绩单 !$B$2:$B$950, 按班级汇总 !$B2) ”公式，按

4、 Enter 键完成输入，双击右下角的填充柄填充数据。2/13提示： 多条件计数函数COUNTIFS（ Criteria_range1 ，Criteria1 ，Criteria_range2 ，Criteria2 ）主要功能：统计指定单元格区域中符合多组条件的单元格的个数。参数说明： Criteria_range1必需的参数。第 1 组条件中指定的区域。Criteria1 必需的参数。第1 组条件中指定的条件，条件的形式可以为数字、表达式、单元格地址或文本。Criteria_Range2, Criteria2 可选参数。第2 组条件，还可以有其他多组条件。本题中公式表示统计同时满足以下条件的单

5、元格的个数：成绩单工作表 A2:A950 区域中等于按班级汇总工作表 A2 单元格内容 （即滨海市第一中学） ，并且成绩单工作表 B2:B950 区域中等于按班级汇总工作表B2 单元格内容（即 1 班）。步骤 2：在工作表中选择D2 单元格，在该单元格中输入公式“=MAX(成绩单 !$A$2:$A$950=按 班 级 汇 总 !$A2)*( 成 绩 单 !$B$2:$B$950= 按 班 级 汇 总 !$B2)* 成 绩 单 !$D$2:$D$950) ”， 按Ctrl+Shift+Enter 组合键完成输入。提示：最大值函数 MAX(number1,number2, )主要功能：返回一组值或

6、指定区域中的最大值参数说明：参数至少有一个，且必须是数值，最多可以有255 个。本题中：成绩单 !$A$2:$A$950= 按班级汇总 !$A2 返回值为逻辑值 (True 或 False)，成绩单 !$B$2:$B$950=按班级汇总 !$B2 返回值也为逻辑值(True 或 False)，将两个表达式进行乘运算时，逻辑值True 转换为1，逻辑值 False 转换为 0 参加运算。也即，同时满足两个条件时，MAX 中的求值区域才为非0 数，最终形成的表达式只有是MAX(1*1* 成绩单 !$D$2:$D$950) ，才会统计这个区域的最大值。当编辑完第一个公式后，此时注意， 必须同时按下C

7、trl+Shift+Enter 组合键， 才能显示出统计结果，之后就可以使用填充柄进行公式填充。步骤 3：选择 E2 单元格，在该单元格中输入公式“=MIN(IF(成绩单 !$A$2:$A$950= 按班级汇总!$A2)*( 成绩单 !$B$2:$B$950=按班级汇总 !$B2), 成绩单 !$D$2:$D$950) ”，按 Ctrl+Shift+Enter组合键完成输入。提示：最小值函数MIN(number1,number2, )主要功能：返回一组值或指定区域中的最小值3/13参数说明：参数至少有一个，且必须是数值，最多可以有255 个逻辑判断函数IF(logical_test,value

8、_if_true,value_if_false)主要功能：如果指定条件的计算结果为TRUE， IF 函数将返回某个值；如果该条件的计算结果为 FALSE，则返回另一个值。参数说明：logical_test 必须的参数，作为判断条件的任意值或表达式value_if_true 可选的参数。 Logical_test 参数的计算结果为TRUE时所要返回的值value_if_false 可选的参数。Logical_test 参数的计算结果为FALSE时所要返回的值本题中：成绩单!$A$2:$A$950= 按班级汇总 !$A2 返回值为逻辑值(True 或 False)，成绩单 !$B$2:$B$950

9、=按班级汇总 !$B2 返回值也为逻辑值(True 或 False)，将两个表达式进行乘运算时，逻辑值True 转换为 1，逻辑值 False 转换为 0 参加运算。也即，同时满足两个条件时，IF 函数中的条件表达式为TRUE，才能返回表达式“成绩单!$D$2:$D$950”，此时使用MIN 函数统计指定区域中的最小值步骤4：选择F2 单元格，在该单元格中输入公式“=AVERAGEIFS(成绩单 !$D$2:$D$950, 成绩单!$A$2:$A$950, 按班级汇总 !$A2, 成绩单 !$B$2:$B$950, 按班级汇总 !$B2) ”，按 Enter 键完成输入。提示：多条件平均值函数

10、AVERAGEIFS(average_range,criteria_range1,criteria1, criteria_range2, criteria2,)主要功能：对指定区域中满足多个条件的所有单元格中的数值求算数平均值。参数说明：average_range 必需的参数。要计算平均值的实际单元格区域criteria_range1 ， criteria_range2在其中计算关联条件的区域criteria1 ， criteria2求平均值的条件其中每个criteria_range 的大小和形状必须与average_range 相同本题中公式表示对成绩单D2:D950 区域中符合以下条件的单

11、元格的数值求平均值：成绩单工作表 A2:A950 区域中等于按班级汇总工作表 A2 单元格内容 （即滨海市第一中学） ，并且成绩单工作表 B2:B950 区域中等于按班级汇总工作表 B2 单元格内容（即 1 班）。步骤 5：选择 G2 单元格，在该单元格中输入公式“=SUM(小分统计 !$C2:$AP2) ”，按 Enter 键完成输入，双击右下角的填充柄填充数据。步骤 6：选择 H2 单元格，在该单元格中输入公式“=SUM(小分统计 !$AQ2:$AZ2)”，按 Enter 键完成输入，双击右下角的填充柄填充数据。步骤 7：选择 C、D、 E列，单击鼠标右键，在弹出的快捷菜单中选择【设置单元

12、格格式】选项，在弹出的对话框中选择【分类】列表中选择【数值】选项，将【小数位数】设置为0，设置完成后，单击“确定 ”按钮即可。按同样的方式，将F、G、 H 列【小数位数】设置为2。步骤 8：切换至 “按学校汇总 ”工作表中，选择 B2 单元格，在该单元格中输入公式“=COUNTIF(成绩单 !$A$2:$A$950, 按学校汇总 !$A2) ”，按 Enter 键完成输入， 双击右下角的填充柄填充数据。提示：条件计数函数COUNTIF(range,criteria)主要功能：统计指定区域中满足单个指定条件的单元格的个数参数说明：range 必需的参数。计数的单元格区域4/13criteria

13、必需的参数。计数的条件，条件的形式可以为数字、表达式、单元格地址或文本本题中公式表示统计成绩单工作表 A2:A950 区域中，等于 A2 单元格内容（即滨海市第一中学）的单元格的个数。步骤 9：选择 C2 单元格， 在该单元格中输入“=MAX(成绩单 !$A$2:$A$950= 按学校汇总 !$A2)*成绩单 !$D$2:$D$950) ”，按 Ctrl+Shift+Enter 键完成输入，双击右下角的填充柄填充数据。在 D2 单元格中输入 “=MIN(IF(成绩单 !$A$2:$A$950= 按学校汇总 !$A2,成绩单 !$D$2:$D$950) ”，按 Ctrl+Shift+Enter

14、键完成输入，双击右下角的填充柄填充数据。在 E2 单元格中输入 “=AVERAGEIFS(成绩单 !$D$2:$D$950, 成绩单 !$A$2:$A$950, 按学校汇总!$A2) ”，按 Enter 键完成输入，双击右下角的填充柄填充数据。步骤 10：在 F2 单元格中输入“=SUM(按班级汇总 !$A$2:$A$33= 按学校汇总 !$A2)*( 按班级汇总!$C$2:$C$33)*( 按班级汇总 !$G$2:$G$33)/$B2 ”，按 Ctrl+Shift+Enter 键完成输入， 双击右下角的填充柄填充数据。在 G2 单 元 格 中 输 入 “=SUM(按 班 级 汇 总 !$A$

15、2:$A$33= 按 学 校 汇 总 !$A2)*( 按 班 级 汇总!$C$2:$C$33)*( 按班级汇总 !$H$2:$H$33)/$B2 ”，按 Ctrl+Shift+Enter 键完成输入， 双击右下角的填充柄填充数据。提示：本题中按班级汇总 !$A$2:$A$33= 按学校汇总 !$A2 返回的值为逻辑值 (TRUE 和 FALSE)，参加运算时， TRUE转换为 1， FALSE转换为 0。即统计指定学校的学生，使用SUM 函数将数组按班级汇总!$C$2:$C$33 与数组按班级汇总!$G$2:$G$33 对应位置的数据进行乘运算，然后求出乘积的和，最后将得到的总成绩与学生人数相

16、除，求得平均分。步骤 11：在 H2 单元格中输入 “=SUM(小分统计 !$A$2:$A$33=$A2)* 小分统计 !C$2:C$33*按班级汇总 !$C$2:$C$33)/$B2/ 分值表 !B$3”，按 Ctrl+Shift+Enter 键完成输入， 拖动填充柄， 横向填充到 BE2 单元格，紧接着从 BE2 单元格，拖动填充句柄，纵向填充到BE5 单元格。步骤 12：选择 B、 C、 D 列，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】选项， 在弹出的对话框中选择 【分类】 列表中选择 【数值】 选项， 将【小数位数】 设置为0，设置完成后，单击 “确定 ”按钮。按同样的方

17、式，将E、 F、 G 列【小数位数】设置为 2。步骤 13：选择 H 列到 BE 列，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】选项，在弹出的对话框中选择【分类】列表中选择【百分比】选项，将【小数位数】设置为2，设置完成后，单击 “确定 ”按钮即可。第14套5/13第15套2.在“停车收费记录 ”表中，涉及金额的单元格格式均设置为保留2 位的数值类型。 依据 “收费标准 ”表，利用公式将收费标准对应的金额填入“停车收费记录 ”表中的 “收费标准 ”列；利用出场日期、时间与进场日期、时间的关系，计算“停放时间 ”列，单元格格式为时间类型的“XX时 XX 分”。步骤 4：计算停放时间，

18、首先利用DATEDIF计算日期的差值，乘以24；再加上进场时间和出场时间的差值。即在J2 单元格中输入“=DATEDIF(F2,H2,”YD”)*24+(I2-G2)，按 ”Enter，并向下拖动将数据进行填充。说明：DATEDIF(start_date,end_date,unit) ，返回两个日期之间的年月 日间隔数。 Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。参数 End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。参数 Unit 为所需信息的返回类型。当单位代码为”YD”时 ,计算结果是两个日期间隔的天数.忽略年数差。注：结束日期必须大于起

19、始日期。3.依据停放时间和收费标准，计算当前收费金额并填入 “收费金额 ”列；计算拟采用的收费政策的预计收费金额并填入 “拟收费金额 ”列；计算拟调整后的收费与当前收费之间的差值并填6/13入“差值 ”列。(3)【解题步骤】考点提示：本题主要考核公式和函数的使用。步骤1：计算收费金额，在K2单元格中输入公式“ =ROUNDUP(HOUR(J2)*60+MINUTE(J2)/15,0)*E2，并” 向 下 自 动 填 充 单元 格 。步骤 2：计算拟收费金额， 在 L2 单元格中输入公式 “=INT(HOUR(J2)*60+MINUTE(J2)/15)*E2，”并向下自动填充单元格。步骤 3：计

20、算差值，在M2 单元格中输入公式“=K2-L2，”，并向下自动填充单元格。说明： 1.Roundup 函数表示：向上舍入数字，跟四舍五入不一样，不管舍去的首位数字是否大于 4，都向前进 1。2.Roundup 函数的语法格式=Roundup(number,num_digits)Number 表示：用来向上舍入的数字。Num_digits 舍入后的数字的小数位数（即保留几位小数）。3.当 Roundup 函数中参数 num_digits 大于 0 时，则表示向上舍入到指定的小数位 （这里保留一位小数）。第17套3."课时费统计表" 中的 F 至 I 列中的空白内容必须采用公式

21、的方式计算结果。根据"教师基本信息 "工作表和 "课时费标准 " 工作表计算 " 职称 "和 "课时标准 "列内容，根据 " 授课信息表 " 和 " 课程基本信息 "工作表计算 "学时数 "列内容，最后完成 "课时费 " 列的计算。【提示：建议对 "授课信息表 " 中的数据按姓名排序后增加 " 学时数 " 列，并通过 VLOOKUP查询 "课程基本信息 " 表获得相应的值。 】

22、(3)【解题步骤】步骤 1：选择 F3 单元格， 单击【公式】 |【函数库】 组中的 “插入函数 ”按钮， 弹出 “插入函数 ”对话框， 在“选择函数 ”下拉列表中找到VLOOKUP函数，单击 “确定 ”按钮， 弹出 “函数参数 ”对话框。步骤 2：在第 1 个参数框中用鼠标选择“E3；”第 2 个参数框中选择“教师基本信息 ”工作表中的 D3： E22 区域；第 3 个参数框中输入 “2；”第 4 个参数框中输入 “FALSE或”者 “0；”单击 “确定”按钮即可。7/13步骤 3：本题也可直接在 “课时费统计表 ”工作表的 F3 单元格中输入公式 “=VLOOKUP(E3,教师基本信息 !

23、$D$3:$E$22,2, FALSE)”，按 Enter 键，双击 F3 单元格右下角的填充柄完成职称的自动填充。步骤 4：同理，使用 VLOOKUP函数计算 “课时标准 ”。第 1 个参数框中用鼠标选择 “G3”；第 2 个参数框中选择 “课时费标准 ”工作表中的 A3：B6 区域；第 3 个参数框中输入 “2；”第 4 个参数框中输入 “FALSE或”者 “0；”单击 “确定 ”按钮即可。步骤 5：本题也可直接在 “课时费统计表 ”工作表的 G3 单元格中输入公式 “=VLOOKUP(F3,课时费标准 !$A$3:$B$6,2,FALSE)，”按 Enter 键，双击 G3 单元格右下角

24、的填充柄完成课时标准的自动填充。步骤 6：根据题目提示，先在 “授课信息表 ”中增加 “学时数 ”列。在 “授课信息表 ”的 F2 单元格输入 “学时数 ”，回车选择 “F3，”同理使用 VLOOKUP函数计算 “学时数 ”。第 1 个参数框中用鼠标选择 “E3；”第 2 个参数框中选择 “课程基本信息 ”工作表中的 B3：C16 区域；第 3 个参数框中输入 “2；”第 4 个参数框中输入 “FALSE或”者 “0；”单击 “确定 ”按钮，双击 F3 单元格右下角的填充柄完成学时数的自动填充。步骤 7：根据题目提示，再计算 “课时费统计表 ”中的 “学时数 ”列。切换到 “课时费统计表 ”工

25、作表，选择 H3 单元格，单击【公式】 | 【函数库】组中的 “插入函数 ”按钮，弹出 “插入函数 ” 对话框，在 “选择函数 ”下拉列表中找到 SUMIF 函数，单击 “确定 ”按钮，弹出 “函数参数 ”对话框。步骤 8：在第 1 个参数框中用鼠标选择“授课信息表 ”的 “D3： D72”数据区域；第2 个参数框中输入 “E3；”第 3 个参数框中输入选择“授课信息表 ”的 “F3：F72”数据区域；单击 “确定 ”按钮，双击 H3 单元格右下角的填充柄自动填充到最后一行。步骤 9：在 I3 单元格中输入公式“=G3*H3”完成课时费的计算。第18套2 .输入并填充公式： 在 "余

26、额 " 列输入计算公式， 余额 =上期余额 +本期借方 -本期贷方， 以自动填充方式生成其他公式。8/133." 方向列中 " 只能有借、 贷、平三种选择， 首先用数据有效性控制该列的输入范围为借、贷、平三种中的一种，然后通过IF 函数输入 "方向 "列内容，判断条件如下所列：余额大于0等于0小于0方向借平贷9/13第20套4. 学号第 4、 5 位代表学生所在的班级，例如："C120101" 代表 12 级 1 班。请通过函数提取每个学生所在的专业并按下列对应关系填写在"班级 "列中："学号 "的 4、5位对应班级011 班022 班033 班第22套2. 根据身份证号， 请在 " 员工档案表 "工作表的 " 出生日期 "列中，使用 MID 函数提取员工生日，单元格式类型为 "yyyy' 年 'm' 月 'd' 日 '" 。3