2023年计算机二级办公软件高级应用试题及解析excle篇.doc

六、Excel高档应用考生文献夹：d:result1使用IF函数，对Sheet1中“温度较高都市”列进行自动填充。单击D2单元格单击“插入函数”按钮选用“IF”函数设置如图： 选用D2单元格鼠标定位到右下角变为十字光标时拖动（即自动填充）2使用数组公式，对Sheet1中相差温度值（杭州相对于上海温差）进行填充。选用E2单元格输入“=B2-C2”按“Ctrl+Shift+Enter”键输入数组公式选用E2单元格鼠标定位到右下角变为十字光标时拖动（即自动填充）3运用函数，根据Sheet1中成果，符合如下条件进行记录。 a杭州这半个月以来最高气温和最低气温；b上海这半个月以来最高气温和最低气温。单击C18单元格单击“插入函数”按钮选用“MAX”函数拖动鼠标选用B2到B16单元格，确定，即求得杭州半个月以来最高气温。选用C19单元格单击“插入函数”按钮选用“MIN”函数拖动鼠标选用B2到B16单元格，确定，即求得杭州半个月以来最低气温。 同理可求得上海这半个月以来最高气温和最低气温。4将Sheet1复制到Sheet2中，在Sheet2中，重新编辑数组公式，将Sheet2中“相差温度值”中数值取绝对值（均为正数）。选用A1到E21复制单击Sheet2表粘贴选用E2单元格按Delete键单击“插入函数”按钮选用“ABS”函数输入“B2-C2”确定按“Ctrl+Shift+Enter”键输入数组公式选用E2单元格鼠标定位到右下角变为十字光标时拖动（即自动填充）5将Sheet2复制到Sheet3中，并对Sheet3进行高档筛选，规定： 筛选条件：“杭州平均气温”=20，“上海平均气温”1000000; b将成果保留在Sheet3中。选用A1到E26复制单击Sheet3表粘贴在表中输入筛选条件，如图所示：数据筛选高档筛选设置如图所示：6根据Sheet1中成果，创立一张数据透视表Chart1，规定： a显示每个销售人员销售房屋所缴纳契税总额； b行区域设置为“销售人员”； c计数项设置为契税总额。 d将对应数据透视表保留在Sheet4中。单击“Sheet1”表数据数据透视表和数据透视图按向导完毕如下操作：设置选定区域为“Sheet1!$A$2:$E$26”单击“下一步”按钮选用“新建工作表”单击“布局”按钮将“销售人员”拖动到行区域，将“契税总额”拖动到数据区域 单击“完毕”按钮考生文献夹：d:result1使用时间函数，对Sheet1中顾客年龄进行计算。 规定：计算顾客年龄，并将其计算成果填充到“年龄”列当中。单击D2单元格输入“=YEAR(TODAY()-YEAR(C2)”回车对该列下方值实行自动填充2使用REPLACE函数，对Sheet1中顾客 号码进行升级。 规定：对“原 号码”列中 号码进行升级。升级措施是在区号（0571）背面加上“8”， 并将其计算成果保留在“升级 号码”列对应单元格中。单击G2单元格单击“插入函数”按钮选用“REPLACE”函数设置参数如图所示：对该列下方值实行自动填充3使用逻辑函数，判断Sheet1中“不不不小于等于40岁男性”， 将成果保留在Sheet1中“与否=40男性”。单击H2单元格单击“插入函数”按钮选用“IF”函数设置参数如图所示：对该列下方值实行自动填充4对Sheet1中数据，根据如下条件，运用函数进行记录： a记录性别为“男”顾客人数，将成果填入Sheet2B2单元格中； b记录年龄为“40”岁顾客人数，将成果填入Sheet2B2单元格中。单击Sheet2表单击B2单元格单击“插入函数”按钮选用“COUNTIF”函数设置参数如图所示：单击B3单元格单击“插入函数”按钮选用“COUNTIF”函数设置参数如图所示：5将Sheet1复制到Sheet3中，并对Sheet3进行高档筛选，规定： a筛选条件为：“性别”女、“所在区域”西湖区； b将筛选成果保留在Sheet3中。选用A1到H37复制单击Sheet3表粘贴在表中输入筛选条件，如图所示：数据筛选高档筛选设置如图所示：6根据Sheet1中成果，创立一张数据透视表Chart1，规定： a显示每个区域所拥有顾客数量； bx坐标设置为“所在区域”； c计数项为“所在区域”； d将对应数据透视表保留在Sheet4中。单击“Sheet1”表数据数据透视表和数据透视图按向导完毕如下操作：设置选定区域为“Sheet1!$A$1:$H$37”单击“下一步”按钮选用“新建工作表”单击“布局”按钮将“所在区域”拖动到行区域及数据区域中单击“完毕”按钮考生文献夹：d:result1使用HLOOKUP函数，对Sheet1中停车单价进行自动填充。 规定：根据Sheet1中“停车价目表”价格，运用HLOOKUP函数对“停车状况登记表”中“单价”列根据不一样车型进行自动填充。单击C9单元格单击“插入函数”按钮选用“HLOOKUP”函数设置参数如图所示：对该列下方值实行自动填充2在Sheet1中，运用时间函数计算汽车在停车库中停放时间。 规定：a公式计算措施为“出库时间入库时间”； b格式为：“小时：分钟：秒”。 （例如：一小时十五分十二秒在停放时间中体现为：“1：15：12”）选用F9单元格输入“=E9-D9”按Enter键对该列下方值实行自动填充3使用函数公式，计算停车费用，规定：根据停放时间长短计算停车费用，将计算成果填入到“应付金额”列中。注意：a停车按小时收费，对于不满一种小时按照一种小时计费； b对于超过整点小时数十五分钟多累积一种小时。 （例如：1小时23分，将以2小时计费）选用G9单元格输入如下公式按Enter键“=IF(HOUR(F9)0,IF(MINUTE(F9)15,HOUR(F9)+1,HOUR(F9)*C9,C9)” 对该列下方值实行自动填充4使用记录函数，对Sheet1中“停车状况登记表”根据下列条件进行记录，规定： a记录停车费用不不不小于等于40元停车记录条数； b记录最高停车费用。在E3单元格中输入“停车费用不不不小于等于40元停车记录条数”单击F3单元格单击“插入函数”按钮选用“COUNTIF”函数设置参数如图所示：在E4单元格中输入“最高停车费用”单击F4单元格单击“插入函数”按钮选用“MAX”函数输入“G9:G39”按确定按钮5对Sheet2，进行高档筛选，规定： a筛选条件为：“车型”小汽车、“应付金额”=30； b将成果保留在Sheet2中。在表中输入筛选条件，如图所示：数据筛选高档筛选设置如图所示：6根据Sheet1，创立一张数据透视表Chart1，规定： a显示多种车型所收费用汇总； b行区域设置为“车型”； c计数项为“应付金额”；d将对应数据透视表保留在Sheet3中。单击Sheet1表数据数据透视表和数据透视图按向导完毕如下操作：设置选定区域为“Sheet1!$A$8:$G$39”单击“下一步”按钮选用“新建工作表”单击“布局”按钮将“车型”拖动到行区域，将“应付金额”拖动到数据区域 单击“完毕”按钮考生文献夹：d:result1 使用数组公式，对Sheet1计算总分和平均分，将其计算成果保留到表中“总分”和“平均分”列当中。选用F2单元格单击“插入函数”按钮选用“SUM”函数按“Ctrl+Shift+Enter”键输入数组公式选用F2单元格鼠标定位到右下角变为十字光标时拖动（即自动填充）选用G2单元格单击“插入函数”按钮选用“AVERAGE”函数按“Ctrl+Shift+Enter”键输入数组公式选用G2单元格鼠标定位到右下角变为十字光标时拖动（即自动填充）2使用RANK函数，对Sheet1中每个同学排名状况进行记录，并将排名成果保留到表中“排名”列当中。单击I2单元格单击“插入函数”按钮选用“RANK”函数设置参数如图所示：对该列下方值实行自动填充3使用逻辑函数，判断Sheet1中每个同学每门功课与否均高于平均分，假如是，保留成果为TRUE，否则，保留成果为FALSE，将成果保留在表中“三科成绩与否均超过平均”列当中。单击I2单元格单击“插入函数”按钮选用“IF”函数设置参数如图所示：对该列下方值实行自动填充4根据Sheet1成果，使用记录函数，记录“数学”考试成绩各个分数段同学人数，将记录成果保留到Sheet2中对应位置。单击Sheet2表双击B2单元格输入如下公式回车“=COUNTIF(Sheet1!D2:D39,0)-COUNTIF(Sheet1!D2:D39,20)” 单击Sheet2表双击B3单元格输入如下公式回车“=COUNTIF(Sheet1!D2:D39,20)-COUNTIF(Sheet1!D2:D39,40)” 单击Sheet2表双击B4单元格输入如下公式回车“=COUNTIF(Sheet1!D2:D39,40)-COUNTIF(Sheet1!D2:D39,60)” 单击Sheet2表双击B5单元格输入如下公式回车“=COUNTIF(Sheet1!D2:D39,60)-COUNTIF(Sheet1!D2:D39,80)” 单击Sheet2表双击B6单元格输入如下公式回车“=COUNTIF(Sheet1!D2:D39,80)” 5将Sheet1复制到Sheet3中，并对Sheet3进行高档筛选，规定： a筛选条件为：“语文”=75，“英语”=75，“总分”=250； b将成果保留在Sheet3中。选用A1到I39复制单击Sheet3表粘贴在表中输入筛选条件，如图所示：数据筛选高档筛选设置如图所示：6根据Sheet1中成果，在Sheet4中创立一张数据透视表，规定： a显示与否三科均超过平均分学生人数； b行区域设置为“三科成绩与否超过平均”； c计数项设置为三科成绩与否均超过平均。单击“Sheet1”表数据数据透视表和数据透视图按向导完毕如下操作：设置选定区域为“Sheet1!$A$1:$I$39”单击“下一步”按钮选用“新建工作表”单击“布局”按钮如图所示，将“三科成绩与否超过平均”拖动到行及数据区域 单击“完毕”按钮考生文献夹：d:result1使用REPLACE函数，对Sheet1中员工代码进行升级。 规定：a升级措施：在PA背面加上0； b升级后员工代码成果填入表中“升级员工代码”列中。单击C2单元格单击“插入函数”按钮选用“REPLACE”函数设置参数如图所示：对该列下方值实行自动填充2使用时间函数，对Sheet1员工“年龄”和“工龄”进行计算。 并将成果填入到表中“年龄”列和“工龄”列中。单击F2单元格输入“=YEAR(TODAY()-YEAR(E2)”回车对该列下方值实行自动填充单击H2单元格输入“=YEAR(TODAY()-YEAR(G2)”回车对该列下方值实行自动填充3使用记录函数，对Sheet1中数据，根据如下记录条件进行如下记录。 a记录男性员工人数，成果填入N3单元格中； b记录高档工程师人数，成果填入N4单元格中。 c记录工龄不不不小于等于10人数，成果填入N5单元格中。单击N3单元格单击“插入函数”按钮选用“COUNTIF”函数设置参数如图所示：单击N4单元格单击“插入函数”按钮选用“COUNTIF”函数设置参数如图所示：单击N5单元格单击“插入函数”按钮选用“COUNTIF”函数设置参数如图所示：4使用逻辑函数，判断员工与否有资格评“高档工程师”。 评比条件为：工龄不不不小于20，且为工程师员工。单击K2单元格单击“插入函数”按钮选用“IF”函数设置参数如图所示：对该列下方值实行自动填充5将Sheet1复制到Sheet2中，并对Sheet2进行高档筛选，规定： a筛选条件为：“性别”男、“年龄”30，“工龄”=10，“职称”助工； b将成果保留在Sheet2中。选用A1到N65复制单击Sheet2表粘贴在表中输入筛选条件，如图所示：数据筛选高档筛选设置如图所示：6根据Sheet1中成果，创立一张数据透视表Chart1，规定： a显示工厂中各个职称人数； bx坐标设置为“职称”； c计数项为“职称”； d将对应数据透视表保留在Sheet3中。单击“Sheet1”表数据数据透视表和数据透视图按向导完毕如下操作：设置选定区域为“Sheet1!$A$1:$K$65”单击“下一步”按钮选用“新建工作表”单击“布局”按钮如图所示，将“职称”拖动到行及数据区域 单击“完毕”按钮将Sheet4更名为“chart1”考生文献夹：d:result1使用VLOOKUP函数，对Sheet1中商品单价进行自动填充。 规定：根据“价格表”中商品单价，运用VLOOKUP函数， 将其单价自动填充到采购表“单价”列中。单击D11单元格单击“插入函数”按钮选用“VLOOKUP”函数设置参数如图所示：对该列下方值实行自动填充。2使用逻辑函数，对Sheet1中商品折扣率进行自动填充。 规定：根据“折扣表”中商品折扣率，运用对应函数，将其折扣率自动填充到采购表中“折扣”列中。单击E11单元格输入“=IF(B11=1,0,IF(B11=100,0.06,IF(B11150、“折扣率”0； b将成果保留在Sheet2中。 略。6根据Sheet1中采购表，新建一种数据透视图Chart1，规定： a该图形显示每个采购时间点所采购所有系那个吗数量汇总状况， bx坐标设置为“采购时间”； c将对应数据透视图保留在Sheet3中。略。考生文献夹：d:result1使用数组公式，计算Sheet1中每种产品价值，将成果保留到表中“价值”列中。 计算价值计算措施为：“单价*每盒数量*采购盒数”。略。2在Sheet2中，运用数据库函数及已设置条件区域，计算如下状况成果，并将成果保留对应单元格中。 a计算：商标为上海，寿命不不不小于等于1000白炽灯平均单价。 b计算：产品为白炽灯，其瓦数不不不小于等于80且不不小于等于100数量。“数据”“筛选”“高档筛选”设置如图所示：选用G23单元格单击“插入函数”按钮选用“AVERAGE”函数。设置参数如图所示：b小题类似，略。3某企业对各个部门员工吸烟状况进行记录，作为人力资源搭配一种数据根据，对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。根据调查状况，制作出Sheet3，请使用函数，记录符合如下条件数值。a记录未登记部门个数；b记录在登记部门中，吸烟部门个数。单击B14单元格单击“插入函数”按钮选用“COUNTIF”函数。b小题类似，略。4使用函数，对Sheet3中B21单元格中内容进行判断，判断其时候为文本，假如是，成果为“TRUE”，假如不是，成果为“FALSE”，并将成果保留在Sheet3中B22单元格当中。单击B22单元格单击“插入函数”按钮选用“IF”函数设置参数如图所示：5将Sheet1复制到Sheet4中，对Sheet4进行高档筛选，规定： a筛选条件为：“产品为白炽灯，商标为上海”，并将成果保留； b将成果保留在Sheet4中。略。6根据Sheet1成果，在Sheet5中创立一张数据透视表，规定： a显示不一样商标不一样产品采购数量， b行区域设置为“产品”；c列区域设置为“商标”； d计数项为“采购盒数”。略。