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

六、Excel高档应用
考生文献夹：d:\result\

1．使用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，“上海平均气温”<20
①选用A1到E21——复制——单击Sheet3表——粘贴
②在表中输入筛选条件，如图所示：

③数据——筛选——高档筛选
④设置如图所示：

6．根据Sheet1中成果，在Sheet4中创立一张数据透视表，规定：
a．显示杭州气温高于上海气温天数和上海气温高于杭州气温天数；
b．行区域设置为“温度较高都市”；
c．计数项设置为温度较高都市。
①单击“Sheet1”表——数据——数据透视表和数据透视图
②按向导完毕如下操作：

成果图
考生文献夹：d:\result\

1．运用公式，计算Sheet1中房价总额。房价总额计算公式为：“面积*单价”
①选用I3单元格——输入“=F3*G3”——按Enter键
②选用I3单元格——鼠标定位到右下角变为十字光标时拖动（即自动填充）
2．使用数组公式，计算Sheet1中契税总额。契税总额计算公式为：“契税*房价总额”
①选用J3单元格——输入“=H3*I3”——按“Ctrl+Shift+Enter”键输入数组公式
②选用J3单元格——鼠标定位到右下角变为十字光标时拖动（即自动填充）
3．使用函数，根据Sheet1中成果，记录每个销售人员销售总额，将成果保留在Sheet2
中对应单元格中。
①单击Sheet2表——单击B2单元格——单击“插入函数”按钮——选用“SUMIF”函数
②设置参数如图所示：

③对该列下方值实行自动填充
4．使用RANK函数，根据Sheet2成果，对每个销售人员销售状况进行排序，并将成果保留在“排名”列当中。
①单击C2单元格——单击“插入函数”按钮——选用“RANK”函数
②设置参数如图所示：

③对该列下方值实行自动填充
5．将Sheet1复制到Sheet3中，并对Sheet3进行高档筛选，规定：
a．筛选条件为：“户型”为两室一厅，“房价总额”>1000000;
b．将成果保留在Sheet3中。
①选用A1到E26——复制——单击Sheet3表——粘贴
②在表中输入筛选条件，如图所示：

③数据——筛选——高档筛选——设置如图所示：

6．根据Sheet1中成果，创立一张数据透视表Chart1，规定：
a．显示每个销售人员销售房屋所缴纳契税总额；
b．行区域设置为“销售人员”；
c．计数项设置为契税总额。
d．将对应数据透视表保留在Sheet4中。
①单击“Sheet1”表——数据——数据透视表和数据透视图
②按向导完毕如下操作：

③设置选定区域为“Sheet1!$A$2:$E$26”——单击“下一步”按钮
④选用“新建工作表”——单击“布局”按钮

⑤将“销售人员”拖动到行区域，将“契税总额”拖动到数据区域
⑥单击“完毕”按钮
考生文献夹：d:\result\

1．使用时间函数，对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．显示每个区域所拥有顾客数量；
b．x坐标设置为“所在区域”；
c．计数项为“所在区域”；
d．将对应数据透视表保留在Sheet4中。
①单击“Sheet1”表——数据——数据透视表和数据透视图
②按向导完毕如下操作：

③设置选定区域为“Sheet1!$A$1:$H$37”——单击“下一步”按钮
④选用“新建工作表”——单击“布局”按钮

⑤将“所在区域”拖动到行区域及数据区域中
⑥单击“完毕”按钮

考生文献夹：d:\result\

1．使用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:\result\

1． 使用数组公式，对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:\result\

1．使用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．显示工厂中各个职称人数；
b．x坐标设置为“职称”；
c．计数项为“职称”；
d．将对应数据透视表保留在Sheet3中。
①单击“Sheet1”表——数据——数据透视表和数据透视图
②按向导完毕如下操作：

③设置选定区域为“Sheet1!$A$1:$K$65”——单击“下一步”按钮
④选用“新建工作表”——单击“布局”按钮

⑤如图所示，将“职称”拖动到行及数据区域

⑥单击“完毕”按钮——将Sheet4更名为“chart1”
考生文献夹：d:\result\

1．使用VLOOKUP函数，对Sheet1中商品单价进行自动填充。
规定：根据“价格表”中商品单价，运用VLOOKUP函数，
将其单价自动填充到采购表“单价”列中。
①单击D11单元格——单击“插入函数”按钮——选用“VLOOKUP”函数
②设置参数如图所示：

③对该列下方值实行自动填充。
2．使用逻辑函数，对Sheet1中商品折扣率进行自动填充。
规定：根据“折扣表”中商品折扣率，运用对应函数，将其折扣率自动填充到采购表中“折扣”列中。
①单击E11单元格——输入“=IF(B11<=1,0,IF(B11<=100,0.06,IF(B11<=200,0.08,0.1)))”——回车
②对该列下方值实行自动填充
③选用E11：E43，右键单击选用“设置单元格格式”——“数字”标签。

3．运用公式，计算Sheet1中“合计金额”。
规定：根据“采购数量”，“单价”和“折扣”，计算采购“合计金额”。
计算公式：单价*采购数量*（1-折扣率）
①单击F11单元格——输入“=B11*D11*(1-E11)”——回车
②对该列下方值实行自动填充
4．使用SUMIF函数，记录多种商品采购总量和采购总金额，将成果保留在Sheet1中“登记表”当中。
①单击J12单元格——单击“插入函数”按钮——选用“SUMIF”函数
②设置参数如图所示：

③对该列下方值实行自动填充。
④单击K12单元格——单击“插入函数”按钮——选用“SUMIF”函数
⑤设置参数如图所示：

⑥对该列下方值实行自动填充。
5．将Sheet2中“采购表”，进行高档筛选。
a．筛选条件为：“采购数量”>150、“折扣率”>0；
b．将成果保留在Sheet2中。
略。。。
6．根据Sheet1中采购表，新建一种数据透视图Chart1，规定：
a．该图形显示每个采购时间点所采购所有系那个吗数量汇总状况，
b．x坐标设置为“采购时间”；
c．将对应数据透视图保留在Sheet3中。
略。。。
考生文献夹：d:\result\

1．使用数组公式，计算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．计数项为“采购盒数”。
略。。。