AOAEXCEL答案Word格式文档下载.docx

AOAEXCEL答案Word格式文档下载.docx

《AOAEXCEL答案Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《AOAEXCEL答案Word格式文档下载.docx（15页珍藏版）》请在冰豆网上搜索。

须用数组公式，否则不得分。

按Ctrl+Shift+Enter组合健后，公式自动用“{}”括起。

4.使用RANK函数，对Sheet1中的每个同学排名情况进行统计，并将排名结果保存到表中的“排名”列当中。

排名：

=RANK（F2,$F$2:

$F$39）然后利用填充柄复制公式。

5.使用逻辑函数判断Sheet1中每个同学的每门功课是否均高于平均分，如果是，保存结果为TRUE，否则，保存结果为FALSE，将结果保存在表中的“三科成绩是否均超过平均”列当中。

公式为：

=IF（AND（C2*****（$C$2:

$C$39）,D2*****（$D$2:

$D$39）,E2*****（$E$2:

$E$39））,TRUE,FALSE）

或者：

=IF（C2*****（$C$2:

$C$39）,IF（D2*****（$D$2:

$D$39）,IF（E2*****（$E$2:

$E$39）,TRUE,FALSE）,FALSE）,FALSE）

6.根据Sheet1中的结果，使用统计函数，统计“数学”考试成绩各个分数段的同学人数，将统计结果保存到Sheet2中的相应位置。

数学分数位于0到20分的人数：

=*****（Sheet1!

$D$2:

$D$39,“20"

）数学分数位于20到40分的人数：

$D$39,"

40"

）-*****（Sheet1!

20"

）数学分数位于40到60分的人数：

60"

）数学分数位于60到80分的人数：

80"

）数学分数位于80到100分的人数：

=100"

）或

=80"

）

7.将Sheet1复制到Sheet3中，并对Sheet3进行高级筛选，要求：

筛选条件：

“语文”＝75，“数学”＝75，“英语”＝75，“总分”＝250；

将结果保存在Sheet3中。

先复制表格，再在Sheet3中设置高级筛选所需的条件区域：

（注：

“=”要在英文状态下输入）

然后点击[数据]→[筛选]→[高级]，在对话框选好列表区域和条件区域，

根据条件区域设置，不一定相同

得到筛选结果：

8.根据Sheet1中的结果，在Sheet4中创建一张数据透视表，要求：

显示是否三科均超过平均分的学生人数；

行区域设置为：

“三科成绩是否均超过平均”；

计数项为三科成绩是否均超过平均。

鼠标先点在Sheet1的成绩表上，再选[插入]→[数据透视表]，在对话框中选“现有工作表”，如下图：

然后在布局窗口拖动字段“三科成绩是否均超过平均”至“行标签”和“数值”区，如下图：

完成后的数据透视表如下所示：

第2套服装采购

1．在Sheet5中，使用函数，将A1单元格中的数四舍五入到整百，存放在B1单元格中。

=ROUND（A1,-2）说明：

第二个参数表示圆整的位数，“2”表示小数点后2位，“-2”表示小数点前2位（即百位）。

2．在Sheet1中，使用条件格式将“采购数量”列中数量大于100的单元格中字体颜色设置为红色、加粗显示。

先选中“采购数量”这列的单元格，再点击[开始]→[条件格式]→[新建规则]，跳出如下对话框进行相应设置：

选择

设置

3.使用*****函数，对Sheet1中的商品单价进行自动填充。

要求：

根据“价格表”中的商品单价，利用*****函数，将其单价自动填充到采购表

中的“单价”列中。

=*****（A11,$F$2:

$G$5,2,0）

4.使用逻辑函数，对Sheet1中的商品折扣率进行自动填充。

根据“折扣表”中的商品折扣率，利用相应的函数，将其折扣率自动填充到采购

表中的“折扣“列中。

=IF（B11=A$6,B$6,IF（B11=A$5,B$5,IF（B11=A$4,B$4,B$3）））或者：

=IF（B11$A$4,$B$3,IF（B11$A$5,$B$4,IF（B11$A$6,$B$5,$B$6）））注：

嵌套的IF函数，判断时一般都用“”或者都用“”。

5.利用公式，计算Sheet1中的“合计金额”。

根据“采购数量”，“单价”和“折扣”，计算采购的“合计金额”。

计算公式：

单价*采购数量*（1-折扣）

=D11*B11*（1-E11）

使用公式“=B11*D11*（1-E11）”理论上也对，但系统不给分。

6.使用SUMIF函数，计算各种商品的采购总量和采购总金额，将结果保存在Sheet1中的“统计表”当中相应位置。

在J12单元格填入采购总量：

=SUMIF（A$11:

A$43,I12,B$11:

B$43）然后用填充柄填充至J13、J14。

在K12单元格填入采购总金额：

A$43,I12,F$11:

F$43）然后用填充柄填充至K13、K14。

7.将Sheet1中的“采购表”复制到Sheet2中，并对Sheet2进行高级筛选。

要求：

筛选条件为：

“采购数量”150，“折扣”0；

将筛选结果保存在Sheet2中。

先复制“采购表”至Sheet2，其中“单价”和“折扣”两列数据出错，需重新复制、粘贴（用“值”粘贴）。

再设置条件区域如下：

然后用高级筛选设置好列表区域和条件区域，得如下结果：

8.根据Sheet1中的采购表，新建一个数据透视图Chart1，要求：

该图形显示每个采购时间点所采购的所有项目数量汇总情况；

x坐标设置为“采购时间”；

求和项为采购数量；

将对应的数据透视表保存在Sheet3中。

鼠标先点在Sheet1的成绩表上，再选[插入]→[数据透视图]，在对话框中选“现有工作表”，如下图：

在布局窗口，拖动字段“采购时间”至“行标签”、“采购数量”至“数值”区，如下图所示：

点中产生的图表，[鼠标右键]→[移动图表]，出现下图窗口，选“新工作表”：

Chart1中产生的透视图如下所示：

同时，在Sheet3中产生了相应的数据透视表，如下所示：

第

3

套教材订购

按下列要求操作，并将结果

存盘。

1.在Sheet5的A1单元格中设置为只能录入5位数字或文本。

当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。

选定Sheet5的A1，点击[数据]→[数据有效性]，打开对话框进行设置：

2.在Sheet5的B1单元格中输入分数1/3输入：

3.使用数组公式，对Sheet1中“教材订购情况表”的订购金额进行计算。

将结果保存在该表的“金额”列当中。

计算方法为：

金额=订数*单价。

先选中I3:

I52，输入=G3:

G52*H3:

H52，再按Ctrl+Shift+Enter注意：

4.使用统计函数，对Sheet1中“教材订购情况表”的结果按以下条件进行统计，并将结果保存在Sheet1中的相应位置。

统计出版社名称为“高等教育出版社”的书的种类数，并将结果保存在Sheet1中L2单

元格中；

统计订购数量大于110且小于850的书的种类数，并将结果保存在Sheet1中L3单元格

中。

L2单元格：

=*****（D3:

D52,"

高等教育出版社"

）L3单元格：

=*****（G3:

G52,"

110"

）-*****（G3:

850"

5.使用函数，计算每个用户所订购图书所需支付的金额，并将结果保存在Sheet1中“用户支付情况表”的“支付总额”列中。

在单元格L8中输入公式：

=SUMIF（A$3:

A$52,K8,I$3:

I$52）然后填充至L11。

6.使用函数，判断Sheet2中的年份是否为闰年，如果是，结果保存“闰年”；

如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。

闰年定义：

年数能被4整除而不能被100整除，或者能被400整除的年份。

=IF（MOD（A2,400）=0,"

闰年"

IF（MOD（A2,4）0,"

平年"

IF（MOD（A2,100）0,"

"

）））

=IF（OR（MOD（A4,400）=0,AND（MOD（A4,4）=0,MOD（A4,100）0））,"

7.将Sheet1中的“教材订购情况表”复制到Sheet3中，对Sheet3进行高级筛选。

要求筛选条件为“订数=500，且金额=*****”；

先复制表格，再在Sheet3中设置筛选所需的条件区域：

用高级筛选置好列表区域和条件区域，得到如下结果：

8.根据Sheet1中“教材订购情况表”的结果，在Sheet4中新建一张数据透视表。

显示每个客户在每个出版社所订的教材数目；

行区域设置为“出版社”；

列区域设置为“客户”；

求和项为订数；

数据区域设置为“订数”。

鼠标先点在Sheet1的表上，再选[插入]→[数据透视表]，选“现有工作表”为“Sheet4!

$A$1”，进入以下数据布局窗口：

在Sheet4上得到数据透视表，如下所示：

第4套电话升级

2.在Sheet5的B1单元格中输入公式，判断当前年份是否为闰年，结果为TRUE或FALSE。

输入公式：

=IF（OR（AND（MOD（YEAR（NOW（））,4）=0,MOD（YEAR（NOW（））,100）0）,MOD（YEAR（NOW（））,400）=0）,TRUE,FALSE）

注：

当前年份用YEAR（NOW（））表示，也可要YEAR（TODAY（））。

3.使用时间函数，对Sheet1中用户的年龄进行计算。

使用当前时间，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在“年龄”

列当中。

计算方法为两个时间年份之差。

=YEAR（NOW（））-YEAR（C2）或者：

=YEAR（TODAY（））-YEAR（C2）

4.使用*****函数，对Sheet1中用户的电话号码进行升级。

对“原电话号码”列中的电话号码进行升级。

升级方法是在区号（0571）后面加上“8”，

并将其计算结果保存在“升级电话号码”列的相应单元格中。

例如：

电话号码“***-*****808”升级后为“***-*****2808”公式为：

=*****（F2,5,0,"

8"

从电话号码的第5位起选0位，指在第5位前插入。

5.在Sheet1中，使用AND函数，根据“性别”及“年龄”列中的数据，判断所有用户是否为大于等于40岁的男性，并将结果保存在“是否=40男性”列中。

如果是，保存结果为TRUE；

否则，保存结果为FALSE。

=IF（AND（B2="

男"

D2=40）,TRUE,FALSE）

6.根据Sheet1中的数据，对以下条件，使用统计函数进行统计。

统计性别为“男”的用户人数，将结果填入Sheet2的B2单元格中；

统计年龄为“40”岁的用户人数，将结果填入Sheet2的B3单元格中。

“男”的用户人数：

B2:

B37,"

）“40”岁的用户人数：

D2:

D37,"

7.将Sheet1复制到Sheet3，并对Sheet3进行高级筛选。

“性别”－女，“所在区域”－西湖区；

将筛选结果保存在Sheet3中。

先将表格从Sheet1复制到Sheet3，再设置筛选所需的条件区域为：

“性别”中间有空格。

应用高级筛选后的结果为：

8.

根据Sheet1的结果，创建一个数据透视图Chart1。

显示每个区域所拥有的用户数量；

x坐标设置为“所在区域”；

计数项为“所在区域”；

将对应的数据透视表保存在Sheet4中。

鼠标先点在Sheet1的表上，再选[插入]→[数据透视图]，选“现有工作表”为“Sheet4!

$A$1”；

进入数据布局窗口后，把字段“所在区域”分别拖入行标签和数值区，如下图：

点中产生的图表，[鼠标右键]→[移动图表]，出现窗口，选“新工作表”为“Chart1”。

即在Chart1上产生如下透视图：

同时，在Sheet4上产生的数据透视表如下：

第5套灯泡采购

1.在Sheet1的B30单元格中输入分数1/3。

2.在Sheet1中设定第31行中不能输入重复的数值。

先选中31行，点击[数据]→[数据有效性]，打开对话框，输入自定义公式：

在Excel中第31行用“31:

31”表示；

在选中第31行时，单元格A31被激活，公式“=*****（31:

31,A31）=1”实际是输入在A31上的，所以在B31会自动复制成“=*****（31:

31,B31）=1”，以此类推，各单元格的值只能出现一次即不能重复。

3.使用数组公式，计算Sheet1中的每种产品的价值，将结果保存到表中的“价值”列中。

计算价值的计算方法为：

“单价*每盒数量*采购盒数”。

数组公式为：

{=E2:

E17*F2:

F17*G2:

G17}

先选中H2:

H17，再输入公式，结束按Ctrl+Shift+Enter。

4.在Sheet2中，利用数据库函数及已设置的条件区域，计算以下情况的结果，并将结果保存相应的单元格中。

a.计算：

商标为上海，瓦数小于100的白炽灯的平均单价；

=*****E（A1:

H17,E1,J2:

L3）

b.计算：

产品为白炽灯，其瓦数大于等于80且小于等于100的数量。

=DSUM（A1:

H17,G1,J7:

L8）

5.某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。

对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。

根据调查情况，制做出Sheet3。

请使用函数，统计符合以下条件的数值。

a.统计未登记的部门个数；

=*****ANK（B2:

E11）

b.统计在登记的部门中，吸烟的部门个数。

=*****（B2:

E11,"

Y"

6.使用函数，对Sheet3中的B21单元格中的内容进行判断，判断其是否问文本，如果是，结果为“TRUE”；

如果不是，结果为“FALSE”，并将结果保存在Sheet3中的B22单元格当中。

在B22单元格写入公式：

=ISTEXT（B21）

7.将Sheet1复制到Sheet4中，对Sheet4

进行高级筛选，要求：

“产品为白炽灯，商标为上海”，并将结果保存；

将结果保存在Sheet4中。

先将表格从Sheet1复制到Sheet4，再设置筛选所需的条件区域为：

点击[数据]→[筛选]→[高级]，经高级筛