AOA办公自动化二级计算机.doc

第 6 章 Excel 2003 高级应用操作题本章的重点主要包括：条件格式；数据有效性设置；相对地址和绝对地址；一般 公式和数组公式；单元格数字格式；数据填充；各种 Excel 函数及函数的嵌套；自动 筛选和高级筛选；数据透视表和数据透视图；等等。6.1 常用 EXCEL 函数的使用方法在进行各种计算时，经常要用到 EXCEL 函数，常用 EXCEL 函数主要有：1. 数学与三角函数（1）SUM 函数 主要功能：计算所有参数数值的和。 使用格式：SUM(number1,number2, .)参数说明：number1,number2, .：代表需要计算的值，可以是具体的数值、引用的单元格（区域）、逻辑值等。应用举例：在 D64 单元格中输入公式：“=SUM(D2:D63)”，即可求出 D2:D63 区域的和。（2）SUMIF 函数 主要功能：根据指定条件对若干单元格求和。 使用格式：SUMIF(range,criteria,sum_range)参数说明：range 代表条件判断的单元格区域；criteria 为指定条件表达式；sum_range前言119代表需要求和的实际单元格区域。应用举例：参见图 6-1。（3）INT 函数图 6-1 SUMIF 函数用法示例主要功能：将数字向下舍入到最接近的整数。 使用格式：INT(number)参数说明：number 为需要进行向下舍入取整的实数。应用举例：INT(8.9)的值为 8, INT(-8.9)的值为-9。（4）MOD 函数 主要功能：返回两数相除的余数。结果的正负号与除数相同。 使用格式：MOD(number,divisor)参数说明：number 为被除数；divisor 为除数。 应用举例：MOD(3,2)的值为 1, MOD(-3,2)的值为 1（负号与除数相同）。 ABS(number)函数是求 number 的绝对值。2日期和时间函数（1）YEAR 函数主要功能：返回某日期对应的年份。返回值为 1900 到 9999 之间的整数。 使用格式：YEAR(serial_number)参数说明：serial_number 为一个日期值，其中包含要查找的年份。应用举例：参见图 6-2。图 6-2 YEAR 函数用法示例MONTH 函数和 DAY 函数的用法与 YEAR 函数的用法类似，分别返回某日期中的月和日。 TODAY 函数（该函数不需要参数）返回系统的当前日期。NOW 函数（该函数不需要参 数）返回系统的当前的日期和时间。（2）HOUR 函数主要功能：返回时间值的小时数。即一个介于 0 (12:00 AM) 到 23 (11:00 PM) 之间的整数。 使用格式：HOUR(serial_number)参数说明：serial_number 表示一个时间值，其中包含要查找的小时。应用举例：参见图 6-3。图 6-3 HOUR 函数用法示例MINUTE 函数的用法与 HOUR 函数的用法类似，返回时间值中的分钟数。3文本函数（1）REPLACE 函数 主要功能：使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文 本。使用格式：REPLACE(old_text,start_num,num_chars,new_text)参数说明：old_text 是要替换其部分字符的文本；start_num 是要用 new_text 替换的 old_text 中字符的位置（=1）；num_chars 是希望 REPLACE 使用 new_text 替换old_text 中字符的个数（=0）；new_text 是要用于替换 old_text 中字符的文本。应用举例：参见图 6-4。（2）MID 函数图 6-4 REPLACE 函数用法示例主要功能：返回文本字符串中从指定位置开始的特定数目的字符。 使用格式：MID(text,start_num,num_chars)参数说明：text 是包含要提取字符的文本字符串；start_num 是文本中要提取的第一 个字符的位置（=1）；num_chars 指定希望 MID 从文本中返回字符的个数（=0）。 应用举例：MID(abcdefgh,3,2)的值为“cd”。CONCATENATE(text1,text2, .)函数用于将几个文本字符串合并为一个文本字符串。 也可以用&（和号）运算符代替函数 CONCATENATE 实现文本项的合并。如，假设 A1、 A2 单元格的内容分别为“1983”、“9”，则 CONCATENATE(A1,年,A2,月)或“A1& 年&A2&月”的值为“1983 年 9 月”。 EXACT(text1,text2)函数用于测试两个字符串是否完全相同。 FIND(find_text,within_text,start_num) 函数 用 于 查找其 他 文 本 字 符 串 (within_text) 内的文本字符串 (find_text)，并从 within_text 的首字符开始返 回 find_text 的起始位置编号。Start_num 指定开始进行查找的字符位置编号（如果 省略，默认为 1）。如，FIND(cd,abcdeabcde)的值为 3，FIND(cd,abcdeabcde,5) 的值为 8。TEXT(value,format_text) 函数 用 于 将 数 值 (value) 转 换 为按 指 定 数 字 格 式 (format_text) 表 示 的 文 本 。如 “ TEXT(123.456,$0.00) ”的 值 为 “ $123.46 ”， “TEXT(1234,dbnum2)”的值为“壹仟贰佰叁拾肆”。 UPPER(text)函数用于将文本(text)转换成大写形式。LOWER(text)函数用于将文本 (text)转换成小写形式。4逻辑函数（1）AND 函数主要功能：返回逻辑值。如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，否则返回逻辑“假（FALSE）”。 使用格式：AND(logical1,logical2, .)参数说明：logical1,logical2, .：表示待测试的条件值或表达式，最多为 30 个。 应用举例：在 C5 单元格中输入公式：“=AND(A5=60,B5=60)”，如果 C5 中返回 TRUE， 说明 A5 和 B5 中的数值均大于或等于 60；如果返回 FALSE，说明 A5 和 B5 中的数值至 少有一个小于 60。OR(logical1,logical2, .) 函数返回逻辑值。仅当所有参数值均为逻辑“假（FALSE）”时，返回逻辑“假（FALSE）”，否则返回逻辑“真（TRUE）”。NOT(logical) 函数对参数值求反。（2）IF 函数 主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 使用格式：IF(logical,value_if_true,value_if_false)参数说明：logical 代表逻辑判断表达式；value_if_true 表示当判断条件为逻辑“真（TRUE）”时的显示内容，如果忽略返回“TRUE”；value_if_false 表示当判断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。应用举例：在 C29 单元格中输入公式：“=IF(C26=18,符合要求,不符合要求)”， 如果 C26 单元格中的数值大于或等于 18，则 C29 单元格显示“符合要求”字样，否则 显示“不符合要求”字样。5统计函数（1）COUNTIF 函数 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(range,criteria)参数说明：range 代表要统计的单元格区域；criteria 表示指定的条件表达式。 应用举例：在 C17 单元格中输入公式：“=COUNTIF(B1:B13,=80)”，即可统计出 B1 至 B13 单元格区域中，数值大于或等于 80 的单元格数目。 COUNTBLANK(range)函数用于计算指定单元格区域(range)中空白单元格的个数。 COUNTA(value1,value2, .)函数返回参数列表(value1,value2, .)中非空值的单 元格个数。（2）AVERAGE 函数 主要功能：求出所有参数的算术平均值。使用格式：AVERAGE(number1,number2, .)参数说明：number1,number2, .：需要求平均值的数值或引用单元格（区域），参 数不超过 30 个。应用举例：在 B8 单元格中输入公式：“=AVERAGE(B7:D7,F7:H7,7,8)”，即可求出 B7至 D7 区域、F7 至 H7 区域中的数值和 7、8 的算术平均值。 MAX(number1,number2, .)函数用于求出一组数中的最大值。 MIN(number1,number2, .)函数用于求出一组数中的最小值。（3）RANK 函数 主要功能：返回一个数字在数字列表中的排位。 使用格式：RANK(number,ref,order)参数说明：number 为需要找到排位的数字；ref 为数字列表数组或对数字列表的引用； order 为一数字，指明排位的方式（0 或省略，降序排位；非 0，升序排位）。 应用举例：参见图 6-5。图 6-5 RANK 函数用法示例6数据库函数（1）DAVERAGE 函数 主要功能：返回列表或数据库中满足指定条件的列中数值的平均值。 使用格式：DAVERAGE(database,field,criteria)参数说明：database 为构成列表或数据库的单元格区域；field 指定函数所使用的数 据列，列表中的数据列必须在第一行具有标志项，field 可以是文本，即两端带引号 的标志项，如“使用年数”、“产量”等，field 也可以是代表列表中数据列位置的 数字：1 表示第一列，2 表示第二列，等等；criteria 为一组包含给定条件的单元格 区域。应用举例：参见图 6-6。DCOUNT、DSUM、DMAX、DMIN、DGET 函数分别返回列表或数据库中满足指定条件的列中数值的单元格数目、总和、最大值、最小值、单个值。其用法与 DAVERAGE 函数类似。应用举例参见图 6-6。图 6-6 数据库函数用法示例7查找与引用函数（1）VLOOKUP 函数 主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列处的 数值。 使用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参数说明：lookup_value 代表在 table_array 数据表第一列中需要查找的数值； table_array 代表 需 要 在 其 中 查找 数 据 的 单 元 格 区 域 ； col_index_num 为 在table_array 区域中待返回的匹配值的列序号（当 col_index_num 为 2 时,返回table_array 中第 2 列中的数值，为 3 时，返回第 3 列的值 .）；range_lookup 为 一逻辑值，如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹 配值，则返回小于 lookup_value 的最大数值；如果为 FALSE，则返回精确匹配值，如 果找不到，则返回错误值#N/A。应用举例：参见图 6-7。图 6-7 VLOOKUP 函数用法示例HLOOKUP 函数的功能是：在数据表的首行查找指定的数值，并由此返回数据表当前列中指定行处的数值。其用法与 VLOOKUP 函数类似。应用举例参见图 6-8。图 6-8 HLOOKUP 函数用法示例8财务函数（1）FV 函数 主要功能：基于固定利率及等额分期付款方式，返回某项投资的未来值。 使用格式：FV(rate,nper,pmt,pv,type)参数说明：rate 为各期利率；nper 为总投资期，即该项投资的付款期总数；pmt 为各 期所应支付的金额，其数值在整个年金期间保持不变；pv 为现值，即从该项投资开始 计算时已经入帐的款项，也称为本金；type 为数字 0 或 1，用以指定各期的付款时间 是在期初（1）还是期末（0 或省略）。应用举例：参见图 6-9。图 6-9 FV 函数用法示例（2）PV 函数主要功能：返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。 使用格式：FV(rate,nper,pmt,fv,type)参数说明：rate 为各期利率；nper 为总投资（或贷款）期，即该项投资（或贷款） 的付款期总数；pmt 为各期所应支付的金额，其数值在整个年金期间保持不变；fv 为 未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一 笔贷款的未来值即为零）；type 为数字 0 或 1，用以指定各期的付款时间是在期初（1） 还是期末（0 或省略）。应用举例：参见图 6-10。图 6-10 PV 函数用法示例如果年金的购买成本是(60,000)，则年金的现值(59,777.15)小于实际支付值。因此， 这不是一项合算的投资。 PMT(rate,nper,pv,fv,type)函数基于固定利率及等额分期付款方式，返回贷款的每 期付款额。 IPMT(rate,per,nper,pv,fv,type)函数基于固定利率及等额分期付款方式，返回给定 期数（per）内对投资的利息偿还额。参数 per 用于计算其利息数额的期数，必须在 1 到 nper 之间。 PPMT(rate,per,nper,pv,fv,type)函数基于固定利率及等额分期付款方式，返回投资 在某一给定期间内的本金偿还额。（3）SLN 函数 主要功能：返回某项资产在一个期间中的线性折旧值。 使用格式：SLN(cost,salvage,life)参数说明：cost 为资产原值；salvage 为资产在折旧期末的价值（也称为资产残值）；life 为折旧期限（有时也称作资产的使用寿命）。应用举例：参见图 6-11。图 6-11 SLN 函数用法示例SYD(cost,salvage,life,per)函数返回某项资产按年限总和折旧法计算的指定期间（per）的折旧值。9信息函数ISBLANK、ISERR、ISERROR、ISLOGICAL、ISNA、ISNONTEXT、ISNUMBER、ISREF 和 ISTEXT 函数统称为 IS 类函数，可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。例 如，如果数值为对空白单元格的引用，函数 ISBLANK 返回逻辑值 TRUE，否则返回 FALSE。 应用举例：参见图 6-12。图 6-12 IS 类函数用法示例Excel 中包含了极为丰富的函数，这些函数的功能和使用方法可借助“帮助”菜单获得。前言1296.2 典型试题典型试题 1打开素材库中的“典型试题 6-1.xls”文件，按下面的操作要求进行操作，并把操作结果存盘。注意：在做题时，不得将数据表进行更改。一、操作要求1. 在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 使用数组公式，对 Sheet1 中“教材订购情况表”的订购金额进行计算，并将结果保存在该表的“金额”列当中。计算方法：金额 = 订数 * 单价3. 使用统计函数，对 Sheet1 中“教材订购情况表”的结果按以下条件进行统计，并 将结果保存在 Sheet1 中的相应位置。要求：（1）统计出版社名称为“高等教育出版社”的书的种类数,并将结果保存在 sheet1中 L2 单元格中。（2）统计订购数量大于 110 且小于 850 的书的种类数,并将结果保存在 sheet1 中L3 单元格中。4. 使用函数，计算每个用户所订购图书所需支付的金额总数，并将结果保存在 Sheet1中“用户支付情况表”的“支付总额”列中。5. 使用函数，判断 Sheet2 中的年份是否为闰年，如果是，结果保存“闰年”，如果不是，则结果保存“平年”，并将结果保存在“是否为闰年”列中。说明：闰年定义：年数能被 4 整除而不能被 100 整除，或者能被 400 整除的年份。6. 将 Sheet1 中的“教材订购情况表”复制到 Sheet3 中，对 Sheet3 进行高级筛选.要求：（1）筛选条件为“订数=500，且金额总数=30000”。（2）将结果保存在 Sheet3 中。注意：（1）无需考虑是否删除或移动筛选条件。（2）复制过程中，将标题项“教材订购情况表”连同数据一同复制。（3）数据表必须顶格放置。7. 根据 Sheet1 中“教材订购情况表”的结果，在 Sheet4 中新建一张数据透视表。 要求：（1）显示每个客户在每个出版社所订的教材数目。（2）行区域设置为“出版社”。（3）列区域设置为“客户”。（4）求和项为“订数”。（5）数据区域设置为“订数”。二、解答步骤 1：选中 Sheet5 工作表中的 A1 单元格，选择菜单“数据”“有效性”命 令（如图 6-13 所示），在打开的“数据有效性”对话框中，选择“设置”选项卡， 再选择“允许”为“文本长度”，“数据”为“等于”，在“长度”文本框中输入“5”， 如图 6-14 所示。选择“出错警告”选项卡，再选择“样式”为“警告”，在“错误信息”文本框中输入“只能录入 5 位数字或文本”，如图 6-15 所示，单击“确定”按钮。图 6-13 选择“有效性”命令图 6-14 “设置”选项卡图 6-15 “出错警告”选项卡步骤 2 ：在 Sheet1 工作表中，先选中 I3:I52 单元格区域，再输入公式“=G3:G52*H3:H52”，然后同时按组合键 Shift+Ctrl+Enter，此时，公式编辑栏显示“=G3:G52*H3:H52”。说明：公式中单元格或单元格区域也可用鼠标选择输入，下同。步骤 3：选中 L2 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉列表框中选择“统计”，在“选择函数”列表框中选择“COUNTIF” 函数，如图 6-16 所示。单击“确定”按钮，弹出“函数参数”对话框，输入“Range” 参数为“D3：D52”，“Criteria”参数为“高等教育出版社”，如图 6-17 所示。再按 “确定”按钮，即可统计出出版社名称为“高等教育出版社”的书的种类数（6）。此 时，公式编辑栏显示“=COUNTIF(D3:D52,高等教育出版社)”。图 6-16 “插入函数”对话框图 6-17 “函数参数”对话框选 中L3单 元 格 ， 在 公 式 编 辑 栏 中 输 入 公 式 “=COUNTIF(G3:G52,850)-COUNTIF(G3:G52,=110)”，再按回车键确认，即可统 计出订购数量大于 110 且小于 850 的书的种类数（28）。说明：COUNTIF 函数的功能和用法，请参见上一节的相关内容。步骤 4：选中 L8 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉列表框中选择“数学与三角函数”，在“选择函数”列表框中选择 “SUMIF”函数。单击“确定”按钮，弹出“函数参数”对话框，输入“Range”参数 为“A3：A52”，“Criteria”参数为“=c1”，“Sum_range”参数为“I3:I52”，如图6-18 所示。再按“确定”按钮，即可计算出 c1 用户所订购图书需支付的金额总数（721301）。此时，公式编辑栏显示“=SUMIF(A3:A52,=c1,I3:I52)”。图 6-18 “函数参数”对话框在 L9 单元格中输入公式“=SUMIF(A3:A52,=c2,I3:I52)”，按回车键确认，即可计算出 c2 用户所订购图书需支付的金额总数（53337）。在 L10 单元格中输入公式“=SUMIF(A3:A52,=c3,I3:I52)”，按回车键确认，即可计算出 c3 用户所订购图书需支付的金额总数（65122）。在 L11 单元格中输入公式“=SUMIF(A3:A52,=c4,I3:I52)”，按回车键确认，即可计算出 c4 用户所订购图书需支付的金额总数（71253）。说明：SUMIF 函数的功能和用法，请参见上一节的相关内容。步骤 5：在 Sheet2 工作表中，选择 B2 单元格，在公式编辑栏中输入公式 “=IF(OR(AND(MOD(A2,4)=0,MOD(A2,100)0),MOD(A2,400)=0),闰年,平年)”， 按回车键确认，即可判断出该年份是“闰年”还是“平年”。双击 B2 单元格的填充柄，即可判断出其余年份是“闰年”还是“平年”。说明：IF、OR、AND、MOD 函数的功能和用法，请参见上一节的相关内容。步骤 6：选中 Sheet1 工作表中的 A1：I52 单元格区域，单击右键在快捷菜单中选 择“复制”命令。单击 Sheet3 工作表中的 A1 单元格，单击右键在快捷菜单中选择“粘 贴”命令。在 Sheet3 工作表的空白区域创建筛选条件，如图 6-19 所示，在 K2：L3 单元格 区域中输入筛选条件。单击 Sheet3 工作表中 A1：I52 单元格区域中的任一单元格， 选择菜单“数据”“筛选”“高级筛选”命令（如图 6-20 所示），打开“高级筛 选”对话框，“列表区域”文本框中已自动填入数据清单所在的单元格区域（如图 6-21 所示）。将光标定位在“条件区域”文本框内，用鼠标拖选前面创建的筛选条件单元格区域 K2：L3，“条件区域”文本框内会自动填入该区域地址（如图 6-22 所示）。再单击“确定”按钮。图 6-19 创建筛选条件图 6-20 选择“高级筛选”命令图 6-21 设置“列表区域” 图 6-22 设置“条件区域”步骤 7：单击 Sheet1 工作表中 A1：I52 单元格区域中的任一单元格，选择菜单“数 据”“数据透视表和数据透视图”命令（如图 6-23 所示），打开“数据透视表和数 据透视图向导3 步骤之 1”对话框（如图 6-24 所示），选中“数据透视表”单选 钮后，再单击“下一步”按钮，打开“数据透视表和数据透视图向导3 步骤之 2” 对话框，“选定区域”文本框中已自动填入数据清单所在的单元格区域（如图 6-25 所 示），单击“下一步”按钮，打开“数据透视表和数据透视图向导3 步骤之 3”对 话框，选中“现有工作表”单选钮，再单击 Sheet4 工作表中的 A1 单元格，该单元格 地址会自动填入“现有工作表”下方的地址文本框中（如图 6-26 所示），单击“完成” 按钮。在弹出的如图 6-27 所示界面中，将字段“出版社”拖至行字段区域，将字段“客户”拖至列字段区域，将字段“订数”拖至数据项区域，结果如图 6-28 所示。保存文件后退出。图 6-23 选择“数据透视表和数据透视图”命令图 6-24 “数据透视表和数据透视图向导3 步骤之 1”对话框图 6-25 “数据透视表和数据透视图向导3 步骤之 2”对话框图 6-26 “数据透视表和数据透视图向导3 步骤之 3”对话框图 6-27 “数据透视表”布局前言139图 6-28 数据透视表典型试题 2打开素材库中的“典型试题 6-2.xls”文件，按下面的操作要求进行操作，并把操作结果存盘。注意：在做题时，不得将数据表进行更改。一、操作要求1. 在Sheet5的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 使用时间函数，对 Sheet1 中用户的年龄进行计算。要求：假设当前时间是“2008-11-15”，结合用户的出生年月，计算用户的年龄，并将其计算结果保存在“年龄”列当中。计算方法为两个时间年份之差。3. 使用 REPLACE 函数，对 Sheet1 中用户的电话号码进行升级。 要求：对“原电话号码”列中的电话号码进行升级。升级方法是在区号（0571）后面加上“8”，并将其计算结果保存在“升级电话号码”列的相应单元格中。例如：电话号码“05716742808” 升级后为 “057186742808”4. 在 Sheet1 中，使用 AND 函数，根据“性别”及“年龄”列中的数据，判断所有用户是否为大于等于 40 岁的男性，并将结果保存在“是否=40 男性”列中。注意：如果是，保存结果为 TRUE；否则，保存结果为 FALSE。5. 根据 Sheet1 中的数据，对以下条件，使用统计函数进行统计。 要求：（1）统计性别为“男”的用户人数，将结果填入 Sheet2 的 B2 单元格中。（2）统计年龄为“40”岁的用户人数，将结果填入 Sheet2 的 B3 单元格中。6. 将 Sheet1 复制到 Sheet3，并对 Sheet3 进行高级筛选。 要求：（1）筛选条件为：“性别”女、“所在区域”西湖区。（2）将筛选结果保存在 Sheet3 中。注意：（1）无需考虑是否删除或移动筛选条件。（2）数据表必须顶格放置。7. 根据 Sheet1 的结果，创建一个数据透视图 Chart1。 要求：（1）显示每个区域所拥有的用户数量。（2）x 坐标设置为“所在区域”。（3）计数项为“所在区域”。（4）将对应的数据透视表保存在 Sheet4 中。二、解答步骤 1：操作方法参见“典型试题 1”中的步骤 1。步骤 2：在 Sheet1 工作表中，选中 D2 单元格，在公式编辑栏中输入公式 “=YEAR(2008-11-15)-YEAR(C2)”，再按回车键确认，即计算出该用户的年龄（41）。 双击 D2 单元格的填充柄，即可计算出其余用户的年龄。说明：YEAR 函数的功能和用法，请参见上一节的相关内容。步骤 3：选中 G2 单元格，单击插入函数按扭，打开“插入函数”对话框，在“选 择类别”下拉列表框中选择“文本”，在“选择函数”列表框中选择“REPLACE”函数， 单击“确定”按钮后，弹出“函数参数”对话框，输入“Old_text”参数为“F2”， “Start_num”参数为“5”，“Num_chars”参数为“0”，“New_text”参数为“8”，如 图 6-29 所示。再按“确定”按钮，即可对第一个电话号码进行升级。此时，公式编 辑栏显示“=REPLACE(F2,5,0,8)”。双击 G2 单元格的填充柄，即可对其余电话号码进行升级。说明：REPLACE 函数的功能和用法，请参见上一节的相关内容。图 6-29 “函数参数”对话框步骤 4：在 H2 单元格中，输入公式“=AND(B2=男,D2=40)”，再按回车键确认，即可判断该用户是否为大于等于 40 岁的男性。双击 H2 单元格的填充柄，即可判断其余用户是否为大于等于 40 岁的男性。步骤 5：在 Sheet2 工作表的 B2 单元格中，输入公式“=COUNTIF(Sheet1!B2:B37,男)”，再按回车键确认，即可统计出男性用户人数（18）。在 Sheet2 工作表的 B3 单元格中，输入公式“=COUNTIF(Sheet1!D2:D37,40)”，再按回车键确认，即可统计出大于 40 岁的用户人数（14）。步骤 6：选中 Sheet1 工作表中的 A1：H37 单元格区域，单击右键在快捷菜单中选 择“复制”命令。单击 Sheet3 工作表中的 A1 单元格，单击右键在快捷菜单中选择“粘 贴”命令。在 Sheet3 工作表的空白区域创建筛选条件，如图 6-30 所示，在 J1：K2 单元格区域 中输入筛选条件。单击 Sheet3 工作表中 A1：H37 单元格区域中的任一单元格，选择 菜单“数据”“筛选”“高级筛选”命令，打开“高级筛选”对话框，“列表区 域”文本框中已自动填入数据清单所在的单元格区域。将光标定位在“条件区域”文 本框内，用鼠标拖选前面创建的筛选条件单元格区域 J1：K2，“条件区域”文本框内 会自动填入该区域地址（如图 6-31 所示）。再单击“确定”按钮。注意：J1 单元格中“性”和“别”之间有空格，原因是 B1 单元格中“性”和“别”之间有空格。图 6-30 创建筛选条件图 6-31 设置“条件区域”步骤 7：单击 Sheet1 工作表中 A1：H37 单元格区域中的任一单元格，选择菜单“数据”“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导3 步骤之 1”对话框，选中“数据透视图（及数据透视表）”单选钮（如图 6-32 所示）， 再单击“下一步”按钮，打开“数据透视表和数据透视图向导3 步骤之 2”对话 框，“选定区域”文本框中已自动填入数据清单所在的单元格区域（如图 6-33 所示）， 单击“下一步”按钮，打开“数据透视表和数据透视图向导3 步骤之 3”对话框， 选中“现有工作表”单选钮，再单击 Sheet4 工作表中的 A1 单元格，该单元格地址会 自动填入“现有工作表”下方的地址文本框中（如图 6-34 所示）。单击“布局”按钮， 在弹出的如图 6-35 所示界面中，将字段“所在区域”分别拖至“行”区域和“数据” 区域内，然后按“确定”按钮返回图 6-34 所示对话框，按“完成”按钮。这时在 Sheet4 工作表前会插入一张名为 Chart1 的工作表。打开该工作表可看到根据 Sheet1 工作表 创建的数据透视图（如图 6-36 所示），打开 Sheet4 工作表有如图 6-37 所示的相应的 数据透视表。保存文件后退出。图 6-32 “数据透视表和数据透视图向导3 步骤之 1”对话框图 6-33 “数据透视表和数据透视图向导3 步骤之 2”对话框图 6-34 “数据透视表和数据透视图向导3 步骤之 3”对话框图 6-35 “数据透视表”布局图 6-36 数据透视图图 6-37 数据透视表典型试题 3打开素材库中的“典型试题 6-3.xls”文件，按下面的操作要求进行操作，并把操作结果存盘。注意：在做题时，不得将数据表进行更改。一、操作要求1. 在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 使用 VLOOKUP 函数，对 Sheet1 中的“三月份销售统计表”的“产品名称”列和“产 品单价”列进行填充。要求：根据“企业销售产品清单”，使用 VLOOKUP 函数，将产品名称和产品单价填充到“三月份销售统计表”的“产品名称”列和“产品单价”列中。3. 使用数组公式，计算 Sheet1 中的“三月份销售统计表”中的销售金额，并将结果填入到该表的“销售金额”列中。计算方法：销售金额 = 产品单价 * 销售数量4. 使用统计函数，根据“三月份销售统计表”中的数据，计算“分部销售业绩统计表”中的总销售额，并将结果填入该表的“总销售额”列。5. 在 Sheet1 中，使用 RANK 函数，在“分部销售业绩统计”表中，根据“总销售额”对各部门进行排名，并将结果填入到“销售排名”列中。6. 将 sheet1 中的“三月份销售统计表”复制到 Sheet2 中，对 Sheet2 进行高级筛选。 要求：（1）筛选条件为：“销售数量” 3、“所属部门” 市场 1 部、“销售金额”-1000。（2）将筛选结果保存在 Sheet2 中。注意：（1）无需考虑是否删除或移动筛选条件。（2）复制过程中，将标题项“三月份销售统计表”连同数据一同复制。（3）数据表必须顶格放置。7. 根据 Sheet1 的“三月份销售统计表”中的数据，新建一个数据透视图 Chart1。 要求：（1）该图形显示每位经办人的总销售额情况。（2）x 坐标设置为“经办人”。（3）数据区域设置为“销售金额”。（4）求和项为“销售金额”。（5）将对应的数据透视表保存在 Sheet3 中。二、解答步骤 1：操作方法参见“典型试题 1”中的步骤 1。步骤 2：选中 Sheet1 工作表中的 G3 单元格，单击插入函数按扭，打开“插入函数” 对话框，在“选择类别”下拉列表框中选择“查找与引用”，在“选择函数”列表框 中选择“VLOOKUP”函数，单击“确定”按钮后，弹出“函数参数”对话框。输入 “ Lookup_value ” 参 数为 “ F3 ”，“ Table_array ” 参 数为 “ $A$3:$B$10 ”， “Col_index_num”参数为“2”，“Range_lookup”参数为“FALSE”，如图 6-38 所示。 再按“确定”按钮，即可查找到产品型号为“A01”的产品名称（卡特扫描枪）。此时， 公式编辑栏显示“=VLOOKUP(F3,$A$3:$B$10,2,FALSE)”。双击 G3 单元格的填充柄，即可查找到其余产品型号的产品名称。在 H3 单元格中，输入公式“=VLOOKUP(F3,$A$3:$C$10,3,FALSE)”，再按回车键确认， 即可查找到产品型号为“A01”的产品单价（368）。双击 H3 单元格的填充柄，即可查 找到其余产品型号的产品单价。说明：VLOOKUP 函数的功能和用法，请参见上一节的相关内容。图 6-38 “函数参数”对话框步骤 3：先选中 L3:L44 单元格区域，再输入公式“=H3:H44*I3:I44”，然后同时按组合键 Shift+Ctrl+Enter，此时，公式编辑栏显示“=H3:H44*I3:I44”。前言149步骤 4：选中 O3 单元格，单击插入函数按扭，打开“插入函数”对话框，在“选择类别”下拉列表框中选择“数学与三角函数”，在“选择函数”列表框中选择“SUMIF”函数。单击“确定”按钮，弹出“函数参数”对话框，输入“Range”参数 为“K3：K44”，“Criteria”参数为“=市场 1 部”，“Sum_range”参数为“L3:L44”， 如图 6-39 所示。再按“确定”按钮，即可计算出市场 1 部的总销售额（35336）。此 时，公式编辑栏显示“=SUMIF(K3:K44,=市场 1 部,L3:L44)”。在 O4 单元格中，输入公式“=SUMIF(K3:K44,=市场 2 部,L3:L44)”后，按回车键确认。在 O5 单元格中，输入公式“=SUMIF(K3:K44,=市场 3 部,L3:L44)”后，按回车键确认。图 6-39 “函数参数”对话框步骤 5：选中 P3 单元格，单击插入函数按扭，打开“插入函数”对话框，在 “选择类别”下拉列表框中选择“统计”，在“选择函数”列表框中选择“RANK”函 数。单击“确定”按钮，弹出“函数参数”对话框，输入“Number”参数为“O3”，“Ref” 参数为“$O$3:$O$5”，“Order”参数为“0”，如图 6-40 所示。再按“确定”按钮， 即 可 统 计 出 市场 1 部 的 总 销售额 排 名 （ 1 ）。 此 时 ，公 式 编 辑 栏显 示 “=RANK(O3,$O$3:$O$5,0)”。双击 P3 单元格的填充柄，可统计出其他部门的总销售额排名。说明：RANK 函数的功能和用法，请参见上一节的相关内容。图 6-40 “函数参数”对话框步骤 6：选中 Sheet1 工作表中的 E1：L44 单元格区域，单击右键在快捷菜单中选 择“复制”命令。单击 Sheet2 工作表中的 A1 单元格，单击右键在快捷菜单中选择“选 择性粘贴”命令，打开如图 6-41 所示的“选择性粘贴”对话框，选中“值和数字格 式”单选钮，再单击“确定”按钮。在 Sheet2 工作表的空白区域创建筛选条件，如图 6-42 所示，在 J2：L3 单元格 区域中输入筛选条件。单击 Sheet2 工作表中 A1：H44 单元格区域中的任一单元格， 选择菜单“数据”“筛选”“高级筛选”命令，打开“高级筛选”对话框，“列 表区域”文本框中已自动填入数据清单所在的单元格区域。将光标定位在“条件区域” 文本框内，用鼠标拖选前面创建的筛选条件单元格区域 J2：L3，“条件区域”文本框 内会自动填入该区域地址（如图 6-43 所示）。再单击“确定”按钮。图 6-41 “选择性粘贴”对话框图 6-42 创建筛选条件图 6-43 设置“条件区域”步骤 7：单击 Sheet1 工作表中 E1：L44 单元格区域中的任一单元格，选择菜单“数据”“数据透视表和数据透视图”命令，打开“数据透视表和数据透视图向导3 步骤之 1”对话框，选中“数据透视图（及数据透视表）”单选钮，再单击“下一步” 按钮，打开“数据透视表和数据透视图向导3 步骤之 2”对话框，“选定区域”文 本框中已自动填入数据清单所在的单元格区域，单击“下一步”按钮，打开“数据透 视表和数据透视图向导3 步骤之 3”对话框，选中“现有工作表”单选钮，再单 击 Sheet3 工作表中的 A1 单元格，该单元格地址会自动填入“现有工作表”下方的地 址文本框中。单击“布局”按钮，在弹出的如图 6-44 所示界面中，把字段“经办人” 拖至“行”区域内，把字段“销售金额”拖至“数据”区域内，然后按“确定”按钮 返回，再按“完成”按钮。这时在 Sheet3 工作表前会插入一张名为 Chart1 的工作表。 打开该工作表可看到根据 Sheet1 中“三月份销售统计表”创建的数据透视图（如图6-45 所示），打开 Sheet3 工作表有如图 6-46 所示的相应的数据透视表。保存文件后退出。图 6-44 “数据透视表”布局图 6-45 数据透视图图 6-46 数据透视表典型试题 4打开素材库中的“典型试题 6-4.xls”文件，按下面的操作要求进行操作，并把操作结果存盘。注意：在做题时，不得将数据表进行更改。一、操作要求1. 在Sheet4的A1单元格中设置为只能录入5位数字或文本。当录入位数错误时，提示错误原因，样式为“警告”，错误信息为“只能录入5位数字或文本”。2. 使用 HLOOKUP 函数，对 Sheet1“停车情况记录表”中的“单价”列进行填充。 要求：根据 Sheet1 中的“停车价目表”价格，使用 HLOOKUP 函数对“停车情况记录表”中的“单价”列根据不同的车型进行填充。注意：函数中如果需要用到绝对地址的将使用绝对地址进行计算，其他方式无效。3. 在 Sheet1 中，使用时间函数计算汽车在停车库中的停放时间。 要求：（1）计算方法为：“停放时间 = 出库时间 -入库时间”。（2）格式为：“小时：分钟：秒”。（3）将结果保存在“停车情况记录表”中的“停放时间”列中。（例如：一小时十五分十二秒在停放时间中的表示为：“1：15：12”）4. 使用函数公式，对“停车情况记录表”的停车费用进行计算。要求：根据 Sheet1 停放时间的长短计算停车费用，将计算结果填入到“停车情况记录表”的“应付金额”列中。注意：（1）停车按小时收费，对于不满一个小时的按照一个小时计费。（2）对于超过整点小时数十五分钟（包含十五分钟）的多累积一个小时。（例如 1 小时 23 分，将以 2 小时计费）5. 使用统计函数，对 Sheet1 中的“停车情