第2章 Excel高级应用Word格式文档下载.docx

第2章 Excel高级应用Word格式文档下载.docx

《第2章 Excel高级应用Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《第2章 Excel高级应用Word格式文档下载.docx（21页珍藏版）》请在冰豆网上搜索。

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

b.行区域设置为：

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

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

【操作步骤】

1．选中区域F2：

F39，输入“=C2:

C39+D2:

D39+E2:

E39”（注：

公式中单元格或区域可用鼠标选择代替输入，下同），然后同时按“Shift+Ctrl+Enter”，公式编辑栏显示{=C2:

E39}；

选中区域G2：

G39,输入“=F2:

F39/3”，然后同时按“Shift+Ctrl+Enter”,公式编辑栏显示{=F2:

F39/3}。

2．选中单元格H2，单击插入函数工具“fx”，在选择类别中选择“全部”，然后选中RANK函数（选择函数时，先从键盘键入“R”，可快速定位至以R字母开头的函数），如图2-1-1所示。

单击“确定”按钮后在弹出函数参数设置对话框中分别输入F2（毛莉同学的总分）、F2：

F39（所有同学的总分区域，可用鼠标选择，选择后按F4键自动会将其绝对引用）、0（由高到低排位），如图2-1-2所示。

按回车键结束编辑，拖动H2填充柄到H39（双击H2填充柄可快速填充至H39）。

图２-1-1　　　　　　　　　　　　　　　　　　　图２-1-２

3．选中单元格I2，单击“fx”，在选择类别中选择“全部”，然后选中AND函数并确定，在弹出函数参数设置对话框中分别输入“C2>

AVERAGE（$C$2:

$C$39）”、“D2>

AVERAGE（$D$2:

$D$39）”和“E2>

AVERAGE（$E$2:

$E$39）”，如图2-1-3所示，单击“确定”按钮。

双击I2填充柄。

图2-1-3

4．选中表Sheet2中单元格B2,单击“fx”，在选择类别中选择“全部”，然后选中COUNTIF函数并确定。

在弹出函数参数设置对话框中分别输入“Sheet1!

$D$2:

$D$39”,“<

=20”，如图2-1-4所示，单击“确定”按钮。

双击B2填充柄。

修改B3为“=COUNTIF（Sheet1!

$D$39,"

<

=40"

）-B2”，按回车键确定。

修改B4为“=COUNTIF（Sheet1!

=60"

）-B2-B3”，按回车键确定。

修改B5为“=COUNTIF（Sheet1!

=80"

）-B2-B3-B4”，按回车键确定。

修改B6为“=COUNTIF（Sheet1!

$D$2:

>

80"

）”按回车键确定。

图2-1-4

5．选中表Sheet1中区域A1：

I39，右键快捷菜单中选复制，单击表Sheet3中单元格A1，右键快捷菜单选粘贴。

在表Sheet3中空白区域创建筛选条件，如图2-1-5所示在K1：

N2输入题目要求的筛选条件。

单击表Sheet3中区域A1：

I39中任一单元格，选择菜单“数据/筛选/高级筛选”项，如图2-1-6所示，打开“高级筛选”对话框，在列表区域中自动填入数据清单所在区域，将光标定位在条件区域文本框内，如图2-1-7所示，用鼠标拖选前面创建的筛选条件区域K1：

N2，则条件区域文本框内自动填入，如图2-1-8所示，单击“确定”按钮。

图2-1-5

图2-1-6

图2-1-7图2-1-8

6．单击表Sheet1中区域A1：

I39中任一单元格，选择菜单“数据/数据透视表和数据透视图”项，如图2-1-9所示。

按照数据透视表和数据透视图向导所指引，进行设置和选择，如图2-1-10、2-1-11、2-1-12所示直到完成。

如图2-1-13所示拖动“三科成绩是否均超过平均”到行字段和数据项处，完成后的效果如图2-1-14所示。

图2-1-9图2-1-10

图2-1-11图2-1-12

图2-1-13图2-1-14

［例2-2］打开《二级OFFICE考试指导书》配套练习盘中“AOA-Excel2003\典型题\02”文件夹下的DExcel.xls文件，按下列要求操作，并将结果存盘。

1．使用HLOOKUP函数，对Sheel1中的停车单价进行自动填充。

要求：

根据Sheel1中的“停车价目表”价格，利用HLOOKUP函数对“停车情况记录表”中的“单价”列根据不同的车型进行自动填充。

2．在Sheel1中，利用时间函数计算汽车在停车库中的停放时间，要求：

a．公式计算方法为“出库时间-入库时间”

b．格式为：

“小时：

分钟：

秒”

（例如：

一小时十五分十二秒在停放时间中的表示为：

“1：

15：

12”）

3．使用函数公式，计算停车费用，要求：

根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。

注意：

a．停车按小时收费，对于不满一个小时的按一个小时计费；

b．对于超过整点小时十五分钟的多累积一个小时。

（例如1小时23分，将以2小时计费）

4．使用统计函数，对Sheel1中的“停车情况记录表”根据下列条件进行统计并填入相应单元格，要求：

a．统计停车费用大于等于40元的停车记录条数

b．统计最高的停车费用

5．对Sheel2进行高级筛选，要求：

a．筛选条件为：

“车型”—小汽车，“应付金额”>

=30；

b．将结果保存在Sheel2中。

6．根据Sheel1，创建一个数据透视图Chart1，要求：

a．显示各种车型所收费用的汇总；

b．行区域设置为“车型”；

c．计费项为“应付金额”；

d．将对应的数据透视表保存在Sheel3中。

1．使用HLOOKUP函数的操作如下：

单击“停车情况记录表”中的C9单元格，使其成为活动单元格。

在编辑栏单击

按钮，弹出“插入函数”对话框。

在“选择函数”列表框中选择“HLOOKUP”函数，按“确定”按钮后弹出“函数参数”对话框。

在“函数参数”对话框中的第一行“Lookup_value”框输入搜索值“停车情况记录表”第一条记录“车型”的单元格地址“B9”（确定在数组区域首行进行搜索时的搜索值）。

在第二行“Table_array”框输入要搜索区域“停车价目表”的绝对地址“$A$2：

$C$3”（可选择A2：

C3单元格区域后按F4键来绝对引用），用于确定要搜索的数组或数据表所在的区域。

在第三行“Row_index_num”框输入数字“2”，用以确定在数组区域首行搜索到满足搜索值时，要取的值位于该列的第几行，本题是取第2行的值。

在第四行“Range_lookup”框输入查找精确匹配的逻辑值“FALSE”，如图2-1-15所示。

按“确定”按钮，在“停车情况记录表”第一条记录的“单价”单元格C9得到使用了HLOOKUP函数后小汽车的小时停车单价为“5”。

双击C9单元格“填充柄”，完成停车单价的自动填充。

图2-1-15

2．利用时间函数计算停放时间操作如下：

将光标定位于“停车情况记录表”第一条记录的“停放时间”单元格F9，在编辑栏输入“=E9-D9”后按回车键；

双击F9单元格“填充柄”完成“停放时间”列的自动填充。

3．使用函数计算停车费用操作如下：

将光标定位于“停车情况记录表”第一条记录的“应付金额”单元格G9，在编辑栏单击

在“插入函数”对话框的“选择函数”列表框中选择“IF”函数，按“确定”命令按钮，将会弹出如图2-1-16所示的“函数参数”对话框。

图2-1-16

在第一行“Logical_test”框输入逻辑表达式“HOUR（F9）<

0”，使用时间函数HOUR（）将提取F9单元格内的小时数进行判断是否小于0。

在第二行“Value_if_true”框输入当逻辑表达式“HOUR（F9）<

0”为“true”的值，根据题意，在该栏输入数字“1”。

在第三行“Value_if_false”框输入当逻辑表达式“HOUR（F9）<

0”为“false”的值，此栏要对于超过整点小时十五分钟进行判断，如超过还要多累积一个小时，输入的内容较为复杂，要用到函数的嵌套，表达式为IF（MINUTE（F9）>

15,HOUR（F9）+1,HOUR（F9）），其中MINUTE（）将提取F9单元格内的分钟数。

按“确定”按钮，在编辑栏显示的G9单元格的“应付金额”整个表达式为“=IF（HOUR（F9）<

0,1,IF（MINUTE（F9）>

15,HOUR（F9）+1,HOUR（F9）））”，此时在G9单元格显示的是按计时要求的停车小时数。

在编辑栏中计算停车小时数的表达式后输入“*C9”后即得到每小时停车费，整个表达式为“=IF（HOUR（F9）<

15,HOUR（F9）+1,HOUR（F9）））*C9”。

双击G9单元格右下角的“填充柄”，完成停车应付金额的自动填充。

4．使用统计函数进行统计的操作如下：

将光标定位于J9单元格，在编辑栏单击

按钮，在“插入函数”对话框的“选择函数”列表框中选择“COUNTIF”函数，按“确定”按钮后弹出如图2-1-17所示的“函数参数”对话框。

图2-1-17

在第一行“Range”框中选择或输入要进行条件计数统计的单元格区域，根据本题要求选择“G9：

G39”（见图2-1-17）。

在第二行“Criteria”框输入要统计的单元格的条件，根据本题要求输入“>

=40”（不包括引号，使用非中文输入状态输入）。

按“确定”按钮，在J9单元格得到停车费用大于等于40的记录条数为“4”。

将光标定位于J10单元格，在编辑栏单击

按钮，在“插入函数”对话框的“选择函数”列表框中选择“MAX”函数，按“确定”按钮，将会弹出“函数参数”对话框。

在第一行“Number1”框输入或选择要进行最大数统计的单元格区域，根据本题要求输入“G9：

G39”，第二行“Number2”框不输入值，如图2-1-18所示。

按“确定”命令按钮，在J10单元格得到最高停车费用为“50”。

图2-1-18

5．对Sheel2进行高级筛选操作如下：

在Sheel2工作表的无内容区域按题目要求设置条件区域，如图2-1-19所示。

图2-1-19

单击表Sheet2要进行高级筛选数据清单中任一单元格，选择菜单“数据/筛选/高级筛选”项，如图2-1-6所示，打开“高级筛选”对话框，在列表区域中自动填入数据清单所在区域，将光标定位在条件区域文本框内，用鼠标拖选前面创建的筛选条件区域I1：

J2，则条件区域文本框内自动填入，单击“确定”按钮。

6．创建数据透视图Chart1的操作如下：

将光标定位于Sheel1要建立数据透视图的数据区域内（停车情况记录表）的任意单元格，选择菜单“数据/数据透视表和数据透视图”项。

在“数据透视表和数据透视图向导—3步骤之1”对话框中单击“数据透视图（及数据透视表）”单选按钮，如图2-1-10所示，单击“下一步”按钮，弹出如图2-1-21所示“数据透视表和数据透视图向导—3步骤之2”，单击“下一步”按钮。

图2-1-20图2-1-21

在“数据透视表和数据透视图向导—3步骤之3”对话框中单击“现有工作表”单选按钮，并在下面的文本框输入数据透视表显示的位置“Sheet3！

$A$1”，也可用光标直接定位，如图2-1-22所示。

然后单击“布局”按钮，弹出如图2-1-23所示“布局”对话框。

将“布局”对话框右边的“车型”按钮拖至左边的“行”区域内；

将“布局”对话框右边的“应付金额”按钮拖至左边的“数据”区域内，然后按“确定”按键回到图2-1-22所示对话框，在该对话框内按“完成”按钮，这时在工作簿中会增加一张Chart1的工作表。

打开该工作表有如图2-1-24所示根据Sheel1创建的数据透视图，打开Sheet3有如图2-1-25所示的相应的数据透视表。

图2-1-22图2-1-23

图2-1-24图2-1-25

2.2实训题集

［题1］打开《二级OFFICE考试指导书》配套练习盘中“AOA-Excel2003\实训题\题1”文件夹下的DExcel.xls文件，按下列要求操作，并将结果存盘。

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

计算用户的年龄，并将其计算结果填充到“年龄”列当中。

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

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

升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。

3.使用逻辑函数，判断Sheet1中的“大于等于40岁的男性”，将结果保存在Sheet1中的“是否>

=40男性”。

4.对Sheet1中的数据，根据以下条件,利用函数进行统计：

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

b.统计年龄为“>

40”岁的用户人数，将结果填入Sheet2的B3单元格中。

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

a.筛选条件为：

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

b.将筛选结果保存在Sheet3中。

6.根据Sheet1的结果，创建一数据透视图Chart1，要求：

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

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

c.计数项为“所在区域”；

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

【操作提示】

1．D2单元格中输入“=YEAR（TODAY（））-YEAR（C2）”然后按回车键，双击D2单元格填充柄。

2．G2单元格中输入“=REPLACE（F2,4,1,18）”然后按回车键，双击G2单元格填充柄。

3．在H2单元格中输入“=AND（B2="

男"

D2>

=40）”然后按回车键，双击H2单元格填充柄。

4．在Sheet2中B1单元格中输入“=COUNTIF（Sheet1!

B2:

B37,"

）”然后按回车键，在SHEET2中B2单元格中输入“=COUNTIF（Sheet1!

D2:

D37,"

40"

）”然后按回车键。

H37，右键快捷菜单复制，单击表Sheet3中单元格A1,右键快捷菜单粘贴，在表Sheet3中区域J1：

K2输入条件，如图2-2-1所示，单击表SHEET3中区域A1：

H37中任一单元格，打开菜单“数据/筛选/高级筛选”项，打开“高级筛选”对话框，如图2-2-2，输入或选择条件区域，单击确定按钮。

图2-2-1图2-2-2

H37中任一单元格，打开“数据”菜单，选择“数据透视表和数据透视图”项，按照数据透视表和数据透视图向导所指引，如图2-2-3直到完成。

如图2-2-4拖动“所在区域”到行字段和数据。

点击插入菜单中的图表。

图2-2-3图2-2-4

［题2］打开《二级OFFICE考试指导书》配套练习盘中“AOA-Excel2003\实训题\题2”文件夹下的DExcel.xls文件，按下列要求操作，并将结果存盘。

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

计算价值的计算方法为：

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

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

a.计算：

商标为上海，寿命大于等于1000的白炽灯的平均单价；

b.计算：

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

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

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

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

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

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

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

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

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

5.将Sheet1复制到Sheet4中，对Sheet4进行高级筛选，要求：

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

b.将结果保存在Sheet4中。

…………

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

a.显示不同商标的不同产品的采购数量；

b.行区域设置为“产品”；

c.列区域设置为“商标”；

d.计数项为“采购盒数”。

1．选中区域H2：

H17,输入“=E2:

E17*F2:

F17*G2:

G17”，然后同时按“Shift+Ctrl+Enter”，公式编辑栏显示{=E2:

G17}。

2．在Sheet2表G23单元格中输入“=DAVERAGE（A1:

H17,E1,J2:

L3）”然后按回车键，在Sheet2表G24单元格中输入“=DSUM（A1:

H17,G1,J7:

L8）”然后按回车键。

3．在Sheet3中B14单元格中输入“=COUNTBLANK（B2:

E11）”然后按回车键，在Sheet3中B15单元格中输入“=COUNTIF（B2:

E11,"

Y"

4．在Sheet3中B22单元格中输入“=ISTEXT（B21）”然后按回车键。

H17，右键快捷菜单复制，单击表Sheet4中单元格A1,右键快捷菜单粘贴，在表Sheet4中区域J1：

K2输入条件，如图2-2-5所示。

单击表Sheet4中区域A1：

H17中任一单元格，打开菜单“数据/筛选/高级筛选”项，打开“高级筛选”对话框，如图2-2-6，输入或选择条件区域，单击确定按钮。

图2-2-5图2-2-6

6．单击表SHEET1中区域A1：

H17中任一单元格，打开菜单“数据/数据透视表和数据透视图”项，按照数据透视表和数据透视图向导所指引直到完成。

如图2-2-7拖动“所在区域”到行字段和数据。

图2-2-7

［题3］打开《二级OFFICE考试指导书》配套练习盘中“AOA-Excel2003\实训题\题3”文件夹下的DExcel.xls文件，按下列要求操作，并将结果存盘。

1.使用IF函数，对Sheet1中的“学位”列进行自动填充。

填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）：

-博士研究生－博士

-硕士研究生－硕士

-本科－学士

-其他－无

2.使用数组公式，在Sheet1中计算：

a.“笔试比例分”，计算方法为：

（笔试成绩/3）*60％

b.“面试比例分”，计算方法为：

面试成绩*40％

c.“总成绩”，计算方法为：

笔试比例分+面试比例分

3.修改数组公式，将Sheet1复制到Sheet2，在Sheet2中计算：

修改“笔试比例分”的计算，计算方法为：

（（笔试成绩/2）*60％）。

4.在Sheet2中，添加一列，将其命名为“排名”。

使用RANK函数，根据“总成绩”对所有考生排名。

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

“报考单位”－一中院、“性别”－男、“学历”－硕士研究生

b.将筛选结果保存在Sheet3中

6.根据Sheet2，在Sheet4中新建一数据透视表。

a.显示每个报考单位的人的不同学历的总人数

b.行区域设置为“报考单位”

c.列区域设置为“学历”

d.数据区域设置为“学历”

e.计数项为学历

1．选中区域H3,输入“=IF（G3="

博士研究生"

"

博士"

IF（G3="

硕士研究生"

硕士"

本科"

学士"

无"

）））”，然后按回车键。

2．选中区域J3：

J18，输入“=I3:

I18/3*0.6”，然后同时按“Shift+Ctrl+Enter”，公式编辑栏显示{=I3:

I18/3*0.6},选中区域L3：

L18,输入“=K3:

K18*0.4”，然后同时按“Shift+Ctrl+Enter”，公式编辑栏显示{=K3:

K18*0.4},选中区域M3：

M18,输入“=J3:

J18+L3:

L18”，然后同时按“Shift+Ctrl+Enter”，公式编辑栏显示{=J3:

L18}。

3．选中表Sheet1中区域A1：

N18，右键快捷菜单复制，单击表Sheet2中单元格A1,右键快捷菜单粘贴,在Sheet2中选中区域J3：

J18,在编辑栏修改“=I3:

I18/3*0.6”为“=I3:

I18/2*0.6”，然后同时按“Shift+Ctrl+Enter”。

4．在Sheet2中N3单元格中输入“=RANK（M3,$M$3:

$M$18）”然后按回车键。

双击N3单元格的填充柄。

5．选中表Sheet2中区域A1：

N18，右键快捷菜单复制，单击表Sheet3中单元格A1，右键快捷菜单粘贴，在表Sheet3中区域A20：

C21输入条件，单击表Sheet3中区域A1：

N18中任一单元格，打开菜单“数据/筛选/高级筛选”项，打开“高级筛选”对话框，输入或选择条件区域，单击确定按钮。

6．单击表SHEET2中区域A1：

N18中任一单元格，打开“数据”菜单，选择“数据透视表和数据透视图”项，按照数据透视表和数据透视图向导所指引，直到完成。

如图2-2-8拖动“所在区域”到行字段和数据。

图2-2-8

［题4］打开《二级OFFICE考试指导书》配套练习盘中“AOA-Excel2003\实训题\题4”文件夹下的DExcel.xls文件，按下列要求操作，并将结果存盘。

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

根