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

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

《计算机二级办公软件高级应用试题及解析excle篇.docx》由会员分享，可在线阅读，更多相关《计算机二级办公软件高级应用试题及解析excle篇.docx（27页珍藏版）》请在冰豆网上搜索。

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

六、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．计数项为“采购盒数”。

略。

。

。