计算机二级办公软件试题及解析之Excle示例

1、AECDE1日期杭州平均气温上海平均气温温度较高的域行相差圈度值212018321S194319175%21186519207622218719189920191092120111018191211212013122220141323221514：242516152521171118杭州最高气温：19杭州最低气温：20上海最高气温：21上海最低气温：1.使用函数，对1中的“温度较高的城市”列进行自动填充。单击D2单元格一一单击”- “插入函数”按钮一一选择函数设置如图：IFLogicsl_+sstValne_i f_true | 杭州.玉J 二 面杭州“V alue_i f_f：dlse司二“

2、上海“=嗫州“、妹.Ihkr 4 冷 JlU 曰E 七一由 LI、U =1 4H 4irn FAL=1 口 4 H选择D2单元格一一鼠标定位到右下角变为十字光标时拖动（即自动填充）2 .使用数组公式，对 1中的相差温度值（杭州相对于上海的温差）进行填充。选择E2单元格一一输入“ 22” 一一按键输入数组公式选择E2单元格一一鼠标定位到右下角变为十字光标时拖动（即自动填充）3 .利用函数，根据1中的结果，符合以下条件的进行统计。a.杭州这半个月以来的最高气温和最低气温；b.上海这半个月以来的最高气温和最低气温。单击C18单元格一一单击 W “插入函数”按钮一一选择函数拖动鼠标选择 B2到B16单

3、元格，确定，即求得杭州半个月以来的最高气温。选择C19单元格一一单击单单插入函数”按钮一一选择函数拖动鼠标选择B2到B16单元格，确定，即求得杭州半个月以来的最低气温。同理可求得上海这半个月以来的最高气温和最低气温。4 .将1复制到2中，在2中，重新编辑数组公式，将2中的“相差的温度值”中的数值取绝对值（均为正数） 。选择A1到E21复制一一单击 2表一一粘贴选择E2单元格一一按键一一单击 因“插入函数”按钮一一选择函数输入“ B22” 一一确定一一按键输入数组公式选择E2单元格一一鼠标定位到右下角变为十字光标时拖动（即自动填充）5 .将2复制到3中，并对3进行高级筛选，要求：筛选条件：“杭州

4、平均气温” =20, “上海平均气温” 20选择A1到E21复制一一单击 3表一一粘贴在表中输入筛选条件，如图所示：12345689101：11213141516L71S192021222324日期桃州平均气温t:海平均气溟磊度较高的城市相差温度值21上21201B21319319174211851920G2221719188201992120101819112120122220132324221425152521州海州州海州州州州海州州州海州 杭上杭杭上杭杭杭杭上杭杭杭上杭1112杭州最高气得：25杭州最低气温:1E上海最高气温：25上海最低气温：17T杭州平均气温 ”20上海平均气温20数

5、据一一筛选一一高级筛选设置如图所示：高绫篇选。区方式 岸在原有区域显示筛选结果（L） r将筛选结果复制到其他位善电）数据区域 国） ：|Sheet31000000;b.将结果保存在 3中。选择A1到E26复制一一单击 3表一一粘贴在表中输入筛选条件，如图所示：ABC28户型房价总额29两室一厅1000000数据一一筛选一一高级筛选一一设置如图所示:高级第选 区方式 俗在原有区域显示筛选结果 将筛选结果复制到其他位置)数据区域 :|5hst3!$A$5：$K$2丁%条件区域 ：|Shet3!$B$28:$C$29.复制到：|2r选择不重复的记录也)| 一定 | 取消6 .根据1中的结果，创建一张

6、数据透视表1,要求：a.显示每个销售人员销售房屋所缴纳契税总额； b.行区域设置为“销售人员”；c.计数项设置为契税总额。d.将对应的数据透视表保存在4中。单击“ 1”表一一数据一一数据透视表和数据透视图按向导完成如下操作：数据盘视表和救据透福图向导一3步葬之1r?ix- - - -E商;占：；工行盛陡避曲r曲yr邯觌姻渊里j-r塞重合并计茸数据区域】另一个数据透祺表或数据透规图3)7数据透视赛|数据透祝图板数据透视菠)遮)回取寤 .！一步IF丁| 完成Q)设置选定区域为“ 1!$A$2:$E$26 ” 一一单击“下一步”按钮选择“新建工作表”一一单击“布局”按钮将“销售人员”拖动到行区域，将

7、“契税总额”拖动到数据区域单击“完成”按钮3+5673910一二三四五六七八九男女男女男女女男C出生年月19G7 C 15 1574-9271953-2-211986-3-301953-8-31959-5=40男性”。单击H2单元格一一单击”- “插入函数”按钮一一选择函数设置参数如图所示：对该列下方的值实行自动填充4 .对1中的数据，根据以下条件，利用函数进行统计：a.统计性别为“男”的用户人数，将结果填入2的B2单元格中；b.统计年龄为“ 40”岁的用户人数，将结果填入2的B2单元格中。单击2表一一单击B2单元格一一单击“插入函数”按钮一一选择函数设置参数如图所示：UCOlffiTIFRa

8、nge |Sht etl B2: B3T= 男；方女* ； *男 * ；Criteria 晤=时男单击B3单元格一一单击阕“插入函数”按钮一一选择函数设置参数如图所示：C0UNTIFRange|Sheetl!D2：D37虱 二42;35;56;23;56;?5 .将1复制到3中，并对3进行高级筛选，要求：a.筛选条件为：“性别”一一女、“所在区域”一一西湖区;b.将筛选结果保存在 3中。选择A1到H37表一复制一一单击 3表一一粘贴在表中输入筛选条件，如图所示：I 1 J K1 鞋别所在区域2 女 西湖区数据一一筛选一一高级筛选一一设置如图所示:6 .根据1中的结果，创建一张数据透视表1,要求

9、:a.显示每个区域所拥有的用户数量；b. x坐标设置为“所在区域”；c.计数项为“所在区域”；d.将对应的数据透视表保存在4中。单击“ 1”表一一数据一一数据透视表和数据透视图按向导完成如下操作：数据透视表和数据透视图向导-3步骤之1三二三三三三一三三=一三三三三 11_=|一H0.三=三=氢_=_=_=_=p瓶苍；募靛灰屋iia艘或舞据库 1r多重合并计算数据区域另一个数据透视表或数据透视图I片手，冷出1堆亲外:剩.二教蠢羲表;)数据透视图国数据透视表)取消 上一 |下一步an完成富|设置选定区域为“ 1!$A$1:$H$37 ” 一一单击“下一步”按钮选择“新建工作表”一一单击“布局”按钮

10、将“所在区域”拖动到行区域及数据区域中单击“完成”按钮ABC- EFc n1停车价目表2小汽车中客车大客车358104561?停车情况记录表S车牌号车型单价入库时间出库时间停放时何应付金甄19MA12345小汽车0:12:2511:15:3510浙A32581大客车6:3i:129:32:4511浙A21584中客车9:00:3615:06:1412浙A668Tl小汽车9:30:4915;13:4813#TA51271中客车9:49:2310:16:2514浙A54S44大客车10:32:582M15浙蚯6894小汽车1 .”1。:56:2311:15:11111 .使用函数，对1中的停车单价

11、进行自动填充。要求：根据1中的“停车价目表”价格，利用函数对“停车情况记录表”中的“单价” 列根据不同的车型进行自动填充。单击C9单元格一一单击圈“插入函数”按钮一一选择函数设置参数如图所示：二 5对该列下方的值实行自动填充2 .在1中，利用时间函数计算汽车在停车库中的停放时间。要求：a.公式计算方法为“出库时间一入库时间”；b.格式为：“小时：分钟：秒”。(例如：一小时十五分十二秒在停放时间中的表示为：“1: 15: 12”)选择F9单元格一一输入“ 99” 一一按键对该列下方的值实行自动填充3 .使用函数公式，计算停车费用，要求：根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”

12、列中。注意：a.停车按小时收费，对于不满一个小时的按照一个小时计费；b.对于超过整点小时数十五分钟的多累积一个小时。(例如：1小时23分，将以2小时计费)选择G9单元格一一输入如下公式一一按键“ (F9)0(F9)15(F9)+1(F9)*C99)”对该列下方的值实行自动填充4 .使用统计函数，对 1中的“停车情况记录表”根据下列条件进行统计，要求：a.统计停车费用大于等于 40元的停车记录条数；b.统计最高的停车费用。在E3单元格中输入“停车费用大于等于40元的停车记录条数”一一单击F3单元格单击司“插入函数”按钮一一选择函数设置参数如图所示：在E4单元格中输入“最高的停车费用”一一单击F4

13、单元格一一单击 E “插入函数”按钮一一选择函数输入“ G939” 按确定按钮5 .对2,进行高级筛选，要求：a.筛选条件为：“车型”一一小汽车、“应付金额” =30;b.将结果保存在 2中。在表中输入筛选条件，如图所示:H I J1车型应付金额2-卜汽车=30q数据一一筛选一一高级筛选设置如图所示：6.根据1,创建一张数据透视表 1,要求：a.显示各种车型所收费用的汇总；b.行区域设置为“车型”；c.计数项为“应付金额”；d.将对应的数据透视表保存在 3中。单击1表一一数据一一数据透视表和数据透视图按向导完成如下操作：领据透视表和数据透视图向导-3建森之I一反三=_ _ _=三-福途嬴亳或数

14、据询：f 外前SHJ&雨量JC篓重合并计算数据区域()另一上数据透视表或数据透视图她)设置选定区域为“ 1!$A$8:$G$39 ” 一一单击“下一步”按钮选择“新建工作表”一一单击“布局”按钮数据透视表和数据透视圉向导3步靠之3冈数懑透视表显示位置，新建工作表如|C现有工作表（X）|二圳粕iJ单击“完成，按钮创建数据透视衰.回| |战局E选项I 取 | I完成3）将“车型”拖动到行区域，将“应付金额”拖动到数据区域单击“完成”按钮AEC口看EFGHI1学号姓名语文数学4英语总分平均井名 三科或然是否均超过平均220041001毛莉T535E0320041002杨吉68756442004100

15、3陈小魔566975520041004陆竟兵9490916200U005闻亚东8497887200U006曾吉武7268B58200U007彭晓玲85717692004100S博珊珊B88075102004:1009钟争秀7Eao76111220041010周旻璐9487B220041011柴安琪6067711320041012吕秀宏S193671120041013陈华“LS4671520041014姚小玮6B54701 GHil 石7ERF只1 .使用数组公式，对1计算总分和平均分，将其计算结果保存到表中的“总分”和 “平均分”列当中。选择F2单元格一一单击国“插入函数”按钮一一选择函数一一

16、按键输入数 组公式选择F2单元格一一鼠标定位到右下角变为十字光标时拖动（即自动填充）选择G2单元格一一单击圈“插入函数”按钮一一选择函数一一按键输入数 组公式选择G2单元格一一鼠标定位到右下角变为十字光标时拖动（即自动填充）2 .使用函数，对1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名” 列当中。单击I2单元格一一单击国“插入函数”按钮一一选择函数设置参数如图所示：RAOtaber F2= 240：F$2 $F$39Order |，J 二对该列下方的值实行自动填充3 .使用逻辑函数，判断1中每个同学的每门功课是否均高于平均分，如果是，保存结果为,否则，保存结果为，将结果保存在

17、表中的“三科成绩是否均超过平均”列当中。单击I2单元格一一单击“插入函数”按钮一一选择函数设置参数如图所示：对该列下方的值实行自动填充4 .根据1的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计 结果保存到2中的相应位置。单击2表一一双击B2单元格一一输入如下公式一一回车“(1239,0)(1239,20) ”单击2表一一双击B3单元格一一输入如下公式一一回车”(1239, =20)(1239,40) ”单击2表双击 B4单元格输入如下公式回车”(1239, =40)(1239,60) ”单击2表双击 B5单元格输入如下公式回车”(1239, =60)(1239,80)

18、”单击2表双击 B6单元格输入如下公式回车”(1239, =80)”5 .将1复制到3中，并对3进行高级筛选，要求：a.筛选条件为：“语文” =75, “英语” =75, “总分” =250;b.将结果保存在 3中。选择A1到I39一一复制一一单击 3表一一粘贴在表中输入筛选条件，如图所示：设置如图所示:高级筛选方式3在原有区域显示筛选结果但)将筛选结果复制到其他位置 (jD)数据区域 ) ：|Sheet31$A$l：$I$39 V条件区域 ：|Sheet31 $C$42:$E$43| Vr选择不重复的记录确定取消6.根据1中的结果，在4中创建一张数据透视表，要求:a.显示是否三科均超过平均分

19、的学生人数；b.行区域设置为“三科成绩是否超过平均”；c.计数项设置为三科成绩是否均超过平均。单击“ 1”表一一数据一一数据透视表和数据透视图按向导完成如下操作：数据透视表和数据透祝图向导- 3步骤之1- =二三三_I=J1 -=_=ii 二三=三三氧r多重合并计算数据区域6另一个数据透视表或数据透视圉3)后数嬴遑表:T数据遗攫图嵌数据透视衰)信)取消|二二11下一但、完成设置选定区域为“ 1!$A$1:$I$39 ” 一一单击“下一步”按钮选择“新建工作表”一一单击“布局”按钮如图所示，将“三科成绩是否超过平均”拖动到行及数据区域单击“完成”按钮AECDEFGBITLn口1员工排包品工代码升

20、装茴工代码性弼出牛年月年陡破匐工件时间工龄斗图岗栉疆剧品百评造高维工皆J12掰PA1B女国一年LU月1疣年，月技术负濯统计事件统计落果3扬音PA125在5睥二月虹搦男性员工人教：4麻小鹰PA12S男L颇年I.L月月助工濯K近程匠人到：5陆东兵PA212女19：坪一月】顺拗月虹湖工静大于等于1底的人就二6闻亚在PA216禺网点1：月1时奉调鬲机工程W闻71(吉武PA313男,法1明0优年用技术向L皴3曲晓玲PA32J女腐陋月H!而珥高艘工5饿g博珊训%女例辉:月京年】月技术员湖10钟事费PA327男比弹L2月1圈客二月虹璐11周雪瑞PA329戈:箝在4月】四二年4月助T湖1 .使用函数，对1中员

21、工代码进行升级。要求：a.升级方法：在后面加上 0;b.升级后的员工代码结果填入表中的“升级员工代码”列中。单击C2单元格一一单击圈“插入函数”按钮一一选择函数设置参数如图所示:REPLACE对该列下方的值实行自动填充2 .使用时间函数，对 1员工的“年龄”和“工龄”进行计算。并将结果填入到表中的“年龄”列和“工龄”列中。单击F2单元格一一输入“ ()(E2) ” 一一回车对该列下方的值实行自动填充单击H2单元格一一输入“ ()(G2) ” 一一回车对该列下方的值实行自动填充3 .使用统计函数，对 1中的数据，根据以下统计条件进行如下统计。a.统计男性员工的人数，结果填入N3单元格中；b.统计

22、高级工程师人数，结果填入N4单元格中。c.统计工龄大于等于 10的人数，结果填入 N5单元格中。单击N3单元格一一单击“插入函数”按钮选择函数2: D65Criteria男单击N4单元格一一单击“插入函数”按钮选择函数设置参数如图所示:COUNTIF51= 饯丁女、男”51=1t 男设置参数如图所示:COUNTIF2:165Cri twna l6封，技术员;“助工. 引二“高级工程！I币”4 .使用逻辑函数，判断员工是否有资格评“高级工程师”评选条件为：工龄大于 20,且为工程师的员工。选择函数单击K2单元格一一单击词“插入函数”按钮-设置参数如图所示:工FFALSE管Logical_test

23、 |ABE而22。,21工程师y5Jujif trg是Value_i f_fal w e否对该列下方的值实行自动填充5 .将1复制到2中，并对2进行高级筛选，要求:a.筛选条件为:“性别”一一男、“年龄” 30, “工龄” =10, “职称”b.将结果保存在选择A1到2中。I0P统计条件统计结果男性员工A数：34高虹程师人胡14工酷大于等于10年的人数154性划年龄工号职称男30=10助工助工;N65-复制单击 2=-=-fi l=l_=l_li_ 昌 卷三三=！= =_= =!=-1数据透视表工）r数据透视图限数据透视表（1）则H上一步e） 下一步也） 完就fj表一一粘贴在表中输入筛选条件，

24、如图所示:数据一一筛选一一高级筛选设置如图所示：高级筛选lT|x|方式滓在原有区域显示筛选结果任）C将筛选结果复制到其他位置）数据区域|5h0t2!$A$h$K$65 父条件区域（：|$M$0：$F$9三r选择不重复的记录通: 确定 睡6 .根据1中的结果，创建一张数据透视表 1,要求：a.显示工厂中各个职称的人数；b. x坐标设置为“职称”；c.计数项为“职称”；d.将对应的数据透视表保存在 3中。单击“ 1”表一一数据一一数据透视表和数据透视图按向导完成如下操作：敦据选挑表和数据透视圉向导- 3步馨之1=rMi crozoft Excel数据现表或数据库也打Hinwiinr5niin II

25、I ifwHiniHHi III mm irinri-rnmr!Hiiiiri III miinrCTHiirwiunrrjTnirin冲前翔体滥名J广多重合并计篁数据区域C）另1个数据透视表或数据透视国设置选定区域为“ 1!$A$1:$K$65 ” 一一单击“下一步”按钮选择“新建工作表”一一单击“布局”按钮如图所示，将“职称”拖动到行及数据区域单击“完成”按钮一一将 4更名为“ 1_4_ 1 BCDS_E_史I11折扣表航事用表2教室萍豆宴衣藤120310%据子3041006%鞋子1505zoo8%610%8gL0KIM合计统计表U双熊J2Q2008-1-12统计类别总事购昆助采购金观12

26、裤子452008-1-12衣服13鞋子702008-1-12裤子L4衣服1252008-2-5鞋子15裤子1S5200fi-2-516鞋子14Q2C(i8-2-5L7衣服2252C08-3-1418裤子2102008-3-1419拄孑2602008-3-141 .使用函数，对1中的商品单价进行自动填充。要求：根据“价格表”中的商品单价，利用函数， 将其单价自动填充到采购表的“单价”列中。单击D11单元格一一单击“插入函数”按钮一一选择函数设置参数如图所示:=120对该列下方的值实行自动填充。2 .使用逻辑函数，对 1中的商品折扣率进行自动填充。要求：根据“折扣表”中的商品折扣率，利用相应的函数

27、，将其折扣率自动填充到采购 表中的“折扣”列中。单击 E11 单元格输入 “(B11=1,0(B11=100,0.06(B11150、“折扣率” 0;b.将结果保存在 2中。略。6 .根据1中的采购表，新建一个数据透视图1,要求：a .该图形显示每个采购时间点所采购的所有系那个吗数量汇总情况，b. x坐标设置为“采购时间”；c.将对应的数据透视图保存在3中。略。ABC DEFGHI丁EL1产品 比数 寿命（小时） 商标 单价 柜盒数冕 果购盒数箭触条件区域2白织灯2003000上海4. 5013商标产品瓦数3至管1002000上海工00152上海白黑灯=30=1009其他25未知-匕京0. 5

28、010310白织灯2003000-却5. 003211殡管2000匕京L B020512白炽灯100未如.匕京0. 2510513白炽灯10800上海0. 2025214白炽灯601GD0-匕京6 1525015白炽灯01000匕京0, 200216白炽灯1002000上海。B010517百炽灯401000上海0-102051819202122情况计算纯奥23曾标为上海，瓦数小于1二。的白炽灯的平均单价：111揽产品为白炽灯.其频大于等于前且小于等于10。的裁量：1 .使用数组公式，计算 1中的每种产品的价值，将结果保存到表中的“价值”列中。计算价值的计算方法为：“单价*每盒数量*采购盒数”。略。2 .在2中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将