计算机二级msoffice公式excel汇总题目难题汇总

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

2、利用公式计 A A B E 学枝 班级： 誠学皱握高分 24|77.00（ 1 算, 滨海韦第一中学 1 滨海市第一中学 2 2879.00 平均分列由成绩单 工作表数据计算得出： （2） “分值表工作表中给出了本次考试各题的类型及分值。（备注：本次考试一共50道小题, 其中【1】至【40】为客观题，41至【50】为主观题）严 小分统计工作表中包含了 各班级每一道小题的平均得分，通过英可计算出各班级的“客观题平均分”和主观题平均分”。 （备注:由于系统生成每题平均得分时已经进行了四舍五入操作,因此通过英计算“客观题平 均分和主观题平均分”之和时，可能与根据“成绩单”工作表的讣算结果存在一立误差

3、）； （Q利用公式汁算按学校汇总工作表中的客观题平均分和主观题平均分，计算方法 为：每个学校的所有班级相应平均分乘以对应班级人数，相加后再除以该校的总考生数；a讣算按学校汇总”工作表中的每题得分率，即:每个学校所有学生在该题上的得分之和 除以该校总考生数，再除以该题的分值。 (6)所有工作表中考试学生数、最高分”、“最低分显示为整数;类平均分显示为数值格 式,并保留2位小数;各题得分率显示为百分比数据格式，并保留2位小数。 (2)【解题步骤】a步骤1 ：切换至按班级汇总工作表中，选择C2单元格，在该单元格中 输入=COUNT I FS(成绩单!$A$2:$A$9 5 0,按班级汇总！ $A 2

4、 ,成绩单！ $B$2:$B$9 5 0, 按班级汇总！ $B2)”公式，按Enter键完成输入，双击右下角的填充柄填充数据。 提示：多条件计数函数 COUNTIFS（Cr i t e r ia_r a n g el,Criteri a 1, Cr i t e ria_ r ange2. Cri t e ria 2 .） 主要功能:统计指龙单元格区域中符合多组条件的单元格的个数。a参数说明：Criteria_ rangel必需的参数。第1组条件中指泄的区域。亠Criteria必需的参数。第1组条件中指 N的条件，条件的形式可以为数字、表达式、单元格地址或文本。Crite r ia_Ra n g

5、e2, Cri t e r i a2可选参数。第2组条件，还可以有苴他多组条件。亠本题中公式表示统讣同时满足以 下条件的单元格的个数：成绩单工作表A2:A950区域中等于按班级汇总工作表A 2单元格内 容（即滨海市第一中学），并且成绩单工作表B2:B950区域中等于按班级汇总工作表B2单 元格内容（即1班）。步骤2：在工作表中选择D2单元格，在该单元格中输入公式“=M AX （成绩单!$A$2： $A$9 5 0 =按班级汇总！ $A2）*倣绩单!$B$2： $B$950=按班级汇 总!$B2）* 成绩单!$D$2： $D$950,按Ct rl+Shift+E nter 组合键完成输入。 提示

6、:最大值函数 MAX （n u mberl. number 2 主要功能:返回一组值或指泄区域中的最大值亠参数说明：参数至少有一个，且必须是数值, 最多可以有255个。a本题中：成绩单!$A$2:$A$950=按班级汇总!$A2返回值为逻辑值 （True 或 False）, 成绩单!$B $ 2:$B$950=按班级汇总!$ B 2返回值也为逻辑值（True或Fal s e ）, 将两个表达式进行乘运算时，逻辑值Tr u e转换为1，逻辑值False转换为0参加运算。a也即, 同时满足两个条件时，MAX中的求值区域才为非0数，最终形成的表达式只有是MAX（1* 1 *成绩单!$D$2:$D$

7、9 50）,才会统计这个区域的最大值。 当编辑完第一个公式后，此时注意,必须同时按下Ct rl+Shift+Enter组合键，才能显示出 统计结果，之后就可以使用填充柄进行公式填充。 上步骤3：选择E2单元格，在该单元格中输入公式=N4IN（IF（成绩单!$A$2:$A$ 9 5 0 = 按班级汇总!$A2）* （成绩单!$B$2:$ B$950=按班级汇总!$B2）,成绩单!$D$2： $ D$95O）, 按C t rl+Shif t+Ente r组合键完成输入。 提示：最小值函数 MIN （n u mberl, n um b er 2 主要功能:返回一组值或指泄区域中的最小值亠参数说明：参

8、数至少有一个，且必须是数值, 最多可以有 255 个a逻辑判断函数 I F （ 1 ogical_t e st, v alue_ i f_ t rue ,v a lu e _i f _fa 1 se卜主要功能：如果指左条件的计算结果为TRUE,IF函数将返回某个值;如果该条件的计 算结果为FALSE,则返回另一个值。如参数说明：减logical, e st必须的参数，作为判断条 件的任意值或表达式J v a 1 u ef_true可选的参数。Logical_test参数的计算结果为 TRUE时所要返回的值从valu e _if_fa I s e可选的参数。Logic al_te s t参数的计

9、算结果为 FALSE时所要返回的值 本题中:成绩单!$A$2:$A$9 5 0=按班级汇总!$A2返回值为逻辑值（True或False成绩 单!$B$2:$B$ 9 5 0=按班级汇总! $B2返回值也为逻辑值（True或False）, 将两个表达式进行乘运算时，逻辑值Tr u e转换为1,逻借值F a Is e转换为0参加运算。也 即，同时满足两个条件时,IF函数中的条件表达式为TRUE，才能返回表达式成绩单!$D $ 2 :$ D$9 5 0,此时使用MIN函数统计指迄区域中的最小值a 步骤4：选择F 2单元格，在该单元格中输入公式=AVERAGEIFS （成绩单! $D$ 2 : $D$

10、95 0 , 成绩单!$A$ 2 :$A$ 9 50,按班级汇总! $ A 2,成绩单!$B$2:$B$950,按班级汇总!$B2）”，按Enter 键完成输入。a提示：多条件平均值函数AVERAG E I F S（avera g e_ran g e,cr i t eria_ra n g e 1, criter i a 1, c rite r ia_ran g e2, cr i teri a 2,.卜主要功能:对指定区域中满足多个条 件的所有单元格中的数值求算%平均值。 参数说明亠入a verage_ran g e必需的参数。要计算平均值的实际单元格反域 K criter i a _ r a

11、n g e l,cri t e r ia_ r a ng e 2 .在其中计算关联条件的区域*X c r i te r ia 1 , cr i te ria 2 .求平均值的条件 K貝中每个c r i teri a _r a n g e的大小和形状必须与aver age _ra n ge相同 本题中公式表示对成绩单D 2:D950区域中符合以下条件的单元格的数值求平均值：成绩单 工作表A2： A9 5 0区域中等于按班级汇总工作表A2单元格内容（即滨海市第一中学），并 且成绩单工作表B2:B 9 50区域中等于按班级汇总工作表B2单元格内容（即1班）。 步骤5：选择G 2单元格，在该单元格中输

12、入公式-SUM（小分统计!$C2:$AP2）,按Ente r 键完成输入，双击右下角的填充柄填充数据。 步骤6：选择H2单元格，在该单元格中输入公式-SUM （小分统计!$AQ 2： $AZ2）”，按 Enter键完成输入，双击右下角的填充柄填充数据。上 步骤7:选择C、1）、E列，单击鼠标右键，在弹出的快捷菜单中选择【设置单元格格式】选项， 在弹出的对话框中选择【分类】列表中选择【数值】选项，将【小数位数】设豊为0,设置 完成后，单击“确定按钮即可。按同样的方式，将F、G、II列【小数位数】设置为2。 步骤8：切换至“按学校汇总工作表中，选择B2单元格，在该单元格中输入公式-COUNT IF

13、 （成绩单!$A$2:$ A$950,按学校汇总！ $A2）”,按Enter键完成输入,双击右下角的 填充柄填充数据。a提示:条件计数函数COUNTIF （range, cri t e ria） 主要功能：统计指圧区域中满足单个指立条件的单元格的个数a参数说明：a入range必需 的参数。计数的单元格区域 Z crite ria必需的参数。计数的条件，条件的形式可以为数字、表达式、单元格地址或 文本 本题中公式表示统计成绩单工作表A2:A950区域中，等于A2单元格内容（即滨海市第一中 学）的单元格的个数。 步骤9:选择C2单元格，在该单元格中输入M=MAX（成绩单!$A$2:$A$95 0

14、=按学校汇 总!$A2） *成绩单!$D$2： $D$950）,按Ctrl+Sh i f t+Enter键完成输入,双击右下角的填充 柄填充数据。 在D2单元格中输入=MIN（IF（成绩单!$A$2： $A$95 0=按学校汇总!$A2,成绩单!$D$2： $D$950）”,按Ctrl+Shi f t+Ent e r键完成输入，双击右下角的填充柄填充数据。a在E2单 元格中输入-AVERAGEIFS （成绩单！ $D$2: $ D $950,成绩单！ $ A$2:$ A $95 0 ,按学校汇 总!$A2）,按En t e r键完成输入，双击右下角的填充柄填充数据。“步骤10：在F2单元格 中

15、输入=SUM（按班级汇总！ $A$2:$A$33=按学校汇总!$A 2）*（按班级汇总!$C$2:$C$33） *（按班级汇总! $G$2:$G$33） /$B2,按Ctrl+Sh i ft+Ent e r键完成输入，双击右下角的填 充柄填充数据。 在G2单元格中输入-SUM（按班级汇总!$A$2:$A$33=按学校汇总!$A2）* （按班级汇总!$C $2： $C$33）* （按班级汇总!$H$2:$H$3 3）/$B2,按Ct rl+Sh i ft+Enter 键完成输 入，双击右下角的填充柄填充数据。 提示：本题中按班级汇总!$A$2:$A$33=按学校汇总！ $A 2返回的值为逻辑值（

16、TRUE和FA LSE）,参加运算时,TRUE转换为1, FALSE转换为0。 即统计指立学校的学生，使用SUM函数将数组按班级汇总!$C$2:$C$3 3与数组按班级汇 总!$G$2： $G$33对应位垃的数据进行乘运算，然后求岀乘积的和，最后将得到的总成绩与学 生人数相除，求得平均分。步骤1 1：在H2单元格中输入W=SUM（小分统计!$A$2:$A$ 33=$A2)*小分统讣!C$2: C$33拿按班级汇总! $ C $ 2 : $ C $ 33)/$B 2 /分值表！ B $ 3耸按 Ctrl +Shift+E n ter键完成输入，拖动填充柄，横向填充到B E2单元格，紧接着从BE2

17、单元 格，拖动填充句柄，纵向填充到BE5单元格。小步骤12：选择B、C、D列，单击鼠标右键, 在弹出的快捷菜单中选择【设巻单元格格式】选项，在弹出的对话框中选择【分类】列表中 选择【数值】选项，将【小数位数】设置为0,设置完成后，单击“确泄按钮。按同样的方式, 将E、F. G列【小数位数】设置为2。 步骤13:选择II列到BE列，单击鼠标右键，在弹岀的快捷菜单中选择【设置单元格格式】选 项，在弹出的对话框中选择【分类】列表中选择【百分比】选项，将【小数位数】设置为2, 设置完成后，单击确左按钮即可。 第14套 季度服装服怖饮倉 水电气房租 交通 =INT(l +(M0N300800110026

18、0 第15套 2.在“停车收费记录表中，涉及金额的单元格格式均设宜为保留2位的数值类型。依据收 费标准表，利用公式将收费标准对应的金额填入“停车收费记录表中的“收费标准列;利用 出场日期、时间与进场日期、时间的关系，计算停放时间列，单元格格式为时间类型的XX 时XX分。 =VLOOKUP(C2, 标准!$A$3:$B$5, 2, 0) _F _ 厂湮曩躍丁一匙场日期 I2014年5 月 26 日 Ct L4 丿T r 匚3 r- c 1 I G 进场时间 0:06:00 步骤4 “|算停放时间，首先利用DATED I F讣算日期的差值，乘以24;再加上进场时世和世 场时间的差值a即在J 2单元

19、格中输入=DATEDIF(F2, H 2 ,YDF2 4+(I2G2*按Ent e r 并向下拖动将数据进行填充。4说明:DATE DlF(st a r t_dateze n d _d a t e,unit),返回两个 日期之间的年月日间隔数。Sta r t _date为一个日期，它代表时间段内的第一个日期或 起始日期。 参数End_date为一个日期，它代表时间段内的最后一个日期或结朿日期。 参数Unit为所需信息的返回类型。当单位代码为”Y D”时，汁算结果是两个日期间隔的天 数.忽略年数差。 注:结朿日期必须大于起始日期。 3.依据停放时间和收费标准川算当前收费金额并填入收费金额列：计算

20、拟采用的收费政 策的预讣收费金额并填入“拟收费金额列;计算拟调整后的收费与当前收费之间的差值并填 入差值”列。 (3)【解题步骤】a考点提示：本题主要考核公式和函数的使用。如步骤1:计算收费金额，在K 2 单元格中输入公式二ROUNDUP( ( H O UR( J 2) * 6 O+MINU T E(J 2 )/1 5,0)*E2,并向 F G n I T 厂6 进场日期 进场时间 出场日期 出场时间 传就时间收苗金荻札 014年5月26曰 0:06:00 2014年5月26曰 14:27:04 14H抱 1分卜、 a步骤2:计算拟收费金额，在L2单元格中输入公式/=INT(HOUR(J2)*

21、60+M I NUTE(J2) / 15)*E2并向下自动填充单元格。 KT (BOUR C2) *60+MIITUT3*E2 F GHI K / 迸场日期 进场时间出场E 日期出场时间 停放时间 收茅金颔核qj?岳金衲. 2QL4年 5 月药日0:06:00 2014年 5 戸 26 日14:2T:0414 时 21 分87：OOJ _JB5.-50* 步骤3:计算差值，在M2单元格中输入公式-K2 L2：,并向下自动填充单元格。金说明： l.Roundup函数表示:向上舍入数字，跟四舍五入不一样，不管舍去的首位数字是否大于4,都 向前进1。 亠2Roundup函数的语法格式 =Roun d

22、 u p (n u mbeGnumdigits)Numbe r 表示:用来向上舍入的数字 Num_d i gi t s 舍 入后的数字的小数位数(即保留几位小数)。 3.当Roundup函数中参数n um_digi t s大于0时，则表示向上舍入到指立的小数位(这里 保留一位小数)。 第17套 3.课时费统讣表”中的F至I列中的空白内容必须采用公式的方式汁算结果。根据”教师基 本信息”工作表和课时费标准”工作表计算”职称和”课时标准“列内容，根据“授课信息表“和课程基本信息丁作表计算学时数“列内容，最后完成课时费咧的计算【提示：建 议对”授课信息表”卬的数据按姓名排序后增加学时数列,并通过V

23、L 00 KU P查询”课程基 本信息”表获得相应的值。】 （3）【解题步骤】亠步骤1：选择F3单元格，单击【公式】丨【函数库】组中的插入函数按 钮，弹出插入函数”对话框，在选择函数下拉列表中找到VLOOKUP函数，单击确左按钮， 弹出函数参数”对话框。亠步骤2:在第个参数框中用鼠标选择“E3”：第2个参数框中选择 教师基本信息工作表中的D 3 :E22区域；第3个参数框中输入“2：第4个参数框中输入FA LSE或者“0;单击“确定按钮即可。 步骤3:本题也可直接在“课时费统计表”工作表的F 3单元格中输入公式=VL00KUP（E3,教 师基本信息！ $D$3： $E$22,2,FALSE）,

24、按En t e r键，双击F3单元格右下角的填充 柄完成职称的自动填充。 步骤4:同理，使用V LOOKUP函数汁算课时标准”。第1个参数框中用鼠标选择“G3;第2 个参数框中选择“课时费标准工作表中的A3:B6区域;第3个参数框中输入2；第4个参 数框中输入FALSE或者0：单击确定”按钮即可。上步骤5:本题也可直接在课时费统汁表 工作表的G 3单元格中输入公式“=VLOO K U P（F3,课时费标准!$ A $3： $ B$6, 2, F ALS E）, 按Enter键，双击G3单元格右下角的填充柄完成课时标准的自动填充。 步骤6:根据题目提示，先在授课信息表中增加学时数”列。在授课信息

25、表的F 2单元格 输入学时数，回车选择F3,同理使用VLOOKUP函数计算学时数”。第1个参数框中用鼠 标选择E3”；第2个参数框中选择课程基本信息工作表中的B3： C16区域;第3个参数框 中输入“2：第4个参数框中输入FALSE或者“0;单击确左按钮，双击F3单元格右下角 的填充柄完成学时数的自动填充。 步骤7:根据题目提示，再计算课时费统计表中的学时数列。切换到课时费统讣表”工作 走义的名称 表，选择H3单元格，单击【公式】|【函数库】组中的插入函数按钮，弹出插入函数”对话 框，在选择函数下拉列表中找到SUMIF函数，单击“确左按钮，弹出函数参数”对话框。 囲数库 r =SUMIF（授课

26、信息表!E3:E了厶授课信息表!略授课信息表!F3:F72） D I E I F I GI 宛计表一 教研室姓名 职称 课时标淮；学时数，课时费 ,计算机基础室陈国庆教授Z !12013201 FT计算机基础室张慧龙教授I 涉h| 必步骤8：在第1个参数框中用鼠标选择“授课信息表”的53： D7 2数拯区域;第2个参数框 中输入第3个参数框中输入选择授课信息表的F3:F72数据区域；单击确芒按 钮，双击H3单元格右下角的填充柄自动填充到最后一行。 步骤9：在13单元格中输入公式-G 3呻3完成课时费的计算。 第18套 2 .输入并填充公式:在”余额列输入计算公式，余额=上期余额+本期借方一本期

27、贷方，以自动 填充方式生成英他公式。 仍”方向列中”只能有借、 贷、平三种选择，首先用数据有效性控制该列的输入范用为借、贷、平三种中的一种，然后 通过I F函数输入方向列内容，判断条件如下所列： 余额大于0 等于0小于0 方向借平 贷 比序如花送 KKZJI )000 X)01 上明结转 JJJ_ IwW 2M4 斗 S lf0Ft8 谕) giv3l-ksl 2，luU；Luejl 皿麻茲珈ET同 曲 58.05 aoo 1185.55 第20套 4.学号第4、5位代表学生所在的班级，例如:C120 1 0 1 代表22级1班。请通过函数提 取每个学生所在的专业并按下列对应关系填写在呦级咧中

28、m 嶂号啲4、5位 对应 班级1 03 01a班金0 22班 3班 X的名押 /； i =IF=O1YZ1M IF=*02,2班圈F) l D 口 JK 1 8930 94.00 也00 9140 1 1oe.00 1 1 1Q840 1 -421 L 第22套 2 根据身份证号，请在”员工档案表工作表的出生日期”列中，使用M I D函数提取员工生 日，单元格式类型为My y yy年卅 月日5 对齐方式（数字 3 根据入职时间，请在,员工档案表”工作表的11工龄咧中，使用TO DAY函数和1 NT函 数计算员工的工龄，工作满一年才计入工龄匚 ；=INT( (TODAY 0-13)/365) E

29、-F* g H II 一 2务身份证号出生日期 学历入职时间:I工龄 吾; 3门经理 41020519641227E1964年L2月2了E 硕士2001 年3月|_16 . 事行政 09 月 28E 硕十2006 年 12 月 E r J| =J3*I龄工资！$B$3 F G 】职务 卸门经理 1020519641227? 19641227E?S+#16 身份证号 出生日期 学历入职时li工龄基本工资 1*0600 新i套 在按月统计”工作表中,利用公式计算1月6月的销售达标率，即销售额大于60000元 的人数所占比例，并填写在销售达标率”行中。要求以百分比格式显示计算数据，并保留2 位小数。 4 引用,工龄工资n工作表中的数据来汁算,员工档案表,工作表员工的工龄工资，在,基础工 资”列中川算每个人的基础工资。（基础工资二基本工资+工龄工资） ril 对齐方式 Contoso公司上半年销售统计表（按月乡 / 1 一月份 j二月份 三月份 四月份 1 （5）在“按月统计工作表中，分别通过公式计算各月排需第1.第2和第3的销售业绩，并 填写在“销售第一统业绩、销售第二冬业绩和销售第三需业