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

1、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”

2、，可快速定位至以R字母开头的函数），如图2-1-1所示。单击“确定”按钮后在弹出函数参数设置对话框中分别输入F2（毛莉同学的总分）、F2：F39（所有同学的总分区域，可用鼠标选择，选择后按F4键自动会将其绝对引用）、0（由高到低排位），如图2-1-2所示。按回车键结束编辑，拖动H2填充柄到H39（双击H2填充柄可快速填充至H39）。图-1-1图-1-3选中单元格I2，单击 “fx”，在选择类别中选择“全部”，然后选中AND函数并确定，在弹出函数参数设置对话框中分别输入“C2AVERAGE（$C$2:$C$39）”、“D2AVERAGE（$D$2:$D$39）”和“E2AVERAGE（$E$2:

3、$E$39）”，如图2-1-3所示，单击“确定”按钮。双击I2填充柄。图2-1-34选中表Sheet2中单元格B2, 单击“fx”，在选择类别中选择“全部”，然后选中COUNTIF函数并确定。在弹出函数参数设置对话框中分别输入“Sheet1!$D$2:$D$39”,“=20”，如图2-1-4所示，单击“确定”按钮。双击B2填充柄。修改B3为“=COUNTIF（Sheet1!$D$39,80）”按回车键确定。图2-1-45选中表Sheet1中区域A1：I39，右键快捷菜单中选复制，单击表Sheet3中单元格A1，右键快捷菜单选粘贴。在表Sheet3中空白区域创建筛选条件，如图2-1-5所示在K1

4、：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-86单击表Sheet1中区域A1：I39中任一单元格，选择菜单“数据/数据透视表和数据透视图”项，如图2-1-9所示。按照数据透视表和数据透视图向导所指引，进行设置和选择，如图2-1-10、2

5、-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中

6、，利用时间函数计算汽车在停车库中的停放时间，要求：a公式计算方法为“出库时间-入库时间”b格式为：“小时：分钟：秒”（例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”）3使用函数公式，计算停车费用，要求：根据停放时间的长短计算停车费用，将计算结果填入到“应付金额”列中。注意：a停车按小时收费，对于不满一个小时的按一个小时计费；b对于超过整点小时十五分钟的多累积一个小时。（例如1小时23分，将以2小时计费）4使用统计函数，对Sheel1中的“停车情况记录表”根据下列条件进行统计并填入相应单元格，要求：a统计停车费用大于等于40元的停车记录条数b统计最高的停车费用5对Sheel2进

7、行高级筛选，要求：a筛选条件为：“车型” 小汽车，“应付金额”=30；b将结果保存在Sheel2中。6根据Sheel1，创建一个数据透视图Chart1，要求：a显示各种车型所收费用的汇总；b行区域设置为“车型”；c计费项为“应付金额”；d将对应的数据透视表保存在Sheel3中。1使用HLOOKUP函数的操作如下：单击“停车情况记录表”中的C9单元格，使其成为活动单元格。在编辑栏单击按钮，弹出 “插入函数”对话框。在“选择函数”列表框中选择“HLOOKUP”函数，按“确定”按钮后弹出“函数参数”对话框。在“函数参数”对话框中的第一行“Lookup_value”框输入搜索值“停车情况记录表”第一条

8、记录“车型”的单元格地址“B9”（确定在数组区域首行进行搜索时的搜索值）。在第二行“Table_array”框输入要搜索区域“停车价目表”的绝对地址“$A$2：$C$3”（可选择A2：C3单元格区域后按F4键来绝对引用），用于确定要搜索的数组或数据表所在的区域。在第三行“Row_index_num”框输入数字“2”，用以确定在数组区域首行搜索到满足搜索值时，要取的值位于该列的第几行，本题是取第2行的值。在第四行“Range_lookup”框输入查找精确匹配的逻辑值“FALSE”，如图2-1-15所示。按“确定”按钮，在“停车情况记录表”第一条记录的“单价”单元格C9得到使用了HLOOKUP函数

9、后小汽车的小时停车单价为“5”。双击C9单元格 “填充柄”，完成停车单价的自动填充。图2-1-152利用时间函数计算停放时间操作如下：将光标定位于“停车情况记录表”第一条记录的“停放时间”单元格F9，在编辑栏输入“=E9-D9”后按回车键；双击F9单元格“填充柄”完成“停放时间”列的自动填充。3使用函数计算停车费用操作如下：将光标定位于“停车情况记录表”第一条记录的“应付金额”单元格G9，在编辑栏单击在“插入函数”对话框的“选择函数”列表框中选择“IF”函数，按“确定”命令按钮，将会弹出如图2-1-16所示的“函数参数”对话框。图2-1-16在第一行“Logical_test”框输入逻辑表达式

10、“HOUR（F9）0”，使用时间函数HOUR（）将提取F9单元格内的小时数进行判断是否小于0。在第二行“Value_if_true”框输入当逻辑表达式“HOUR（F9）0”为“true”的值，根据题意，在该栏输入数字“1”。在第三行“Value_if_ false”框输入当逻辑表达式“HOUR（F9）15,HOUR（F9）+1,HOUR（F9），其中MINUTE（）将提取F9单元格内的分钟数。按“确定”按钮，在编辑栏显示的G9单元格的“应付金额”整个表达式为“=IF（HOUR（F9）15,HOUR（F9）+1,HOUR（F9）”，此时在G9单元格显示的是按计时要求的停车小时数。在编辑栏中计算停

11、车小时数的表达式后输入“*C9”后即得到每小时停车费，整个表达式为“=IF（HOUR（F9）=40” （不包括引号，使用非中文输入状态输入）。按“确定”按钮，在J9单元格得到停车费用大于等于40的记录条数为“4”。将光标定位于J10单元格，在编辑栏单击按钮，在“插入函数”对话框的“选择函数”列表框中选择“MAX”函数，按“确定”按钮，将会弹出 “函数参数”对话框。在第一行“Number1”框输入或选择要进行最大数统计的单元格区域，根据本题要求输入“G9：G39”，第二行“Number2”框不输入值，如图2-1-18所示。按“确定”命令按钮，在J10单元格得到最高停车费用为“50”。图2-1-1

12、85对Sheel2进行高级筛选操作如下：在Sheel2工作表的无内容区域按题目要求设置条件区域，如图2-1-19所示。图2-1-19单击表Sheet2要进行高级筛选数据清单中任一单元格，选择菜单“数据/筛选/高级筛选”项，如图2-1-6所示，打开“高级筛选”对话框，在列表区域中自动填入数据清单所在区域，将光标定位在条件区域文本框内，用鼠标拖选前面创建的筛选条件区域I1：J2，则条件区域文本框内自动填入，单击“确定”按钮。6创建数据透视图Chart1的操作如下：将光标定位于Sheel1要建立数据透视图的数据区域内（停车情况记录表）的任意单元格，选择菜单“数据/数据透视表和数据透视图”项。在“数据

13、透视表和数据透视图向导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所示“布局”对话框。将“布局”对话框右边的“车型”按钮拖至左边的“行”区域内；将“布局”对话框右边的“应付金额”按钮拖至左边

14、的“数据”区域内，然后按“确定”按键回到图2-1-22所示对话框，在该对话框内按“完成”按钮，这时在工作簿中会增加一张Chart1的工作表。打开该工作表有如图2-1-24所示根据Sheel1创建的数据透视图，打开Sheet3有如图2-1-25所示的相应的数据透视表。图2-1-22 图2-1-23图2-1-24 图2-1-252.2 实训题集题1打开二级OFFICE考试指导书配套练习盘中“AOA-Excel2003实训题题1”文件夹下的DExcel.xls文件，按下列要求操作，并将结果存盘。1. 使用时间函数，对Sheet1中用户的年龄进行计算。计算用户的年龄，并将其计算结果填充到“年龄”列当中

15、。2. 使用REPLACE函数，对Sheet1中用户的电话号码进行升级。对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。3. 使用逻辑函数，判断Sheet1中的“大于等于40岁的男性”，将结果保存在Sheet1中的“是否=40男性”。4. 对Sheet1中的数据，根据以下条件,利用函数进行统计：a. 统计性别为“男”的用户人数，将结果填入Sheet2的B2单元格中；b. 统计年龄为“40”岁的用户人数，将结果填入Sheet2的B3单元格中。5. 将Sheet1复制到Sheet3，并对Sheet3进行高级筛

16、选。a. 筛选条件为：“性别”女、“所在区域”西湖区；b. 将筛选结果保存在Sheet3中。6. 根据Sheet1的结果，创建一数据透视图Chart1，要求：a. 显示每个区域所拥有的用户数量；b. x坐标设置为“所在区域”；c. 计数项为“所在区域”；d. 将对应的数据透视表保存在Sheet4中。【操作提示】1D2单元格中输入“=YEAR（TODAY（）-YEAR（C2）”然后按回车键，双击D2单元格填充柄。2G2单元格中输入“=REPLACE（F2,4,1,18）”然后按回车键，双击G2单元格填充柄。3在H2单元格中输入“=AND（B2=男,D2=40）”然后按回车键，双击H2单元格填充柄

17、。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-2H37中任一单元格，打开 “数据”菜单，选择“数据透视表和数据透视图”项

18、，按照数据透视表和数据透视图向导所指引，如图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

19、的白炽灯的平均单价；b. 计算：产品为白炽灯，其瓦数大于等于80且小于等于100的盒数。3. 某公司对各个部门员工吸烟情况进行统计，作为人力资源搭配的一个数据依据。对于调查对象，只能回答Y（吸烟）或者N（不吸烟）。根据调查情况，制做出Sheet3。 请使用函数，统计符合以下条件的数值。a. 统计未登记的部门个数；b. 统计在登记的部门中，吸烟的部门个数。4. 使用函数，对Sheet3中的B21单元格中的内容进行判断，判断其是否为文本，如果是，结果为“TRUE”；如果不是，结果为“FALSE”，并将结果保存在Sheet3中的B22单元格当中。5. 将Sheet1复制到Sheet4中，对Sheet

20、4进行高级筛选，要求：“产品为白炽灯，商标为上海”，并将结果保存；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单元格中输入“=DS

21、UM（A1:H17,G1,J7:L8）”然后按回车键。3在Sheet3中B14单元格中输入“=COUNTBLANK（B2:E11）”然后按回车键，在Sheet3中B15单元格中输入“=COUNTIF（B2:E11,Y4在Sheet3中B22单元格中输入“=ISTEXT（B21）”然后按回车键。H17，右键快捷菜单复制，单击表Sheet4中单元格A1,右键快捷菜单粘贴，在表Sheet4中区域J1：K2输入条件，如图2-2-5所示。单击表Sheet4中区域A1：H17中任一单元格，打开菜单“数据/筛选/高级筛选”项，打开“高级筛选”对话框，如图2-2-6，输入或选择条件区域，单击确定按钮。 图2-

22、2-5 图2-2-66单击表SHEET1中区域A1：H17中任一单元格，打开菜单“数据/数据透视表和数据透视图”项，按照数据透视表和数据透视图向导所指引直到完成。如图2-2-7拖动“所在区域”到行字段和数据。图2-2-7题3打开二级OFFICE考试指导书配套练习盘中“AOA-Excel2003实训题题3”文件夹下的DExcel.xls文件，按下列要求操作，并将结果存盘。1. 使用IF函数，对Sheet1中的“学位”列进行自动填充。填充的内容根据“学历”列的内容来确定（假定学生均已获得相应学位）：- 博士研究生博士- 硕士研究生硕士- 本科学士- 其他无2. 使用数组公式，在Sheet1中计算：

23、a. “笔试比例分”，计算方法为：（笔试成绩/3）*60b. “面试比例分”，计算方法为：面试成绩*40c. “总成绩”，计算方法为：笔试比例分+面试比例分3. 修改数组公式，将Sheet1复制到Sheet2，在Sheet2中计算：修改“笔试比例分”的计算，计算方法为：（笔试成绩/2）*60）。4. 在 Sheet2中，添加一列，将其命名为“排名”。使用RANK函数，根据“总成绩”对所有考生排名。5. 将Sheet2复制到Sheet3，并对Sheet3进行高级筛选。“报考单位”一中院、“性别”男、“学历”硕士研究生b. 将筛选结果保存在Sheet3中6. 根据Sheet2，在Sheet4中新建

24、一数据透视表。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

25、: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，右键快捷菜单

26、粘贴，在表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中的商品单价进行自动填充.根