《AOA办公自动化二级计算机.doc》由会员分享，可在线阅读，更多相关《AOA办公自动化二级计算机.doc（75页珍藏版）》请在金锄头文库上搜索。
1、第 6 章 Excel 2003 高级应用操作题本章的重点主要包括：条件格式；数据有效性设置；相对地址和绝对地址；一般 公式和数组公式；单元格数字格式；数据填充；各种 Excel 函数及函数的嵌套；自动 筛选和高级筛选；数据透视表和数据透视图；等等。6.1 常用 EXCEL 函数的使用方法在进行各种计算时，经常要用到 EXCEL 函数，常用 EXCEL 函数主要有：1. 数学与三角函数（1）SUM 函数 主要功能：计算所有参数数值的和。 使用格式：SUM(number1,number2, .)参数说明：number1,number2, .：代表需要计算的值，可以是具体的数值、引用的单元格（区域
2、）、逻辑值等。应用举例：在 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 为需要进行向下舍入取整的
3、实数。应用举例：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_num
4、ber 为一个日期值，其中包含要查找的年份。应用举例：参见图 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
5、。图 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_
6、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, .)函数用于将几个文本字符串合并为一
7、个文本字符串。 也可以用&（和号）运算符代替函数 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 的
8、起始位置编号。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)转换成大写形式。L
9、OWER(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 中的数值至 少
10、有一个小于 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 表示当判
11、断条件为逻辑“假（FALSE）”时的显示内容，如果忽略返回“FALSE”。应用举例：在 C29 单元格中输入公式：“=IF(C26=18,符合要求,不符合要求)”， 如果 C26 单元格中的数值大于或等于 18，则 C29 单元格显示“符合要求”字样，否则 显示“不符合要求”字样。5统计函数（1）COUNTIF 函数 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 使用格式：COUNTIF(range,criteria)参数说明：range 代表要统计的单元格区域；criteria 表示指定的条件表达式。 应用举例：在 C17 单元格中输入公式：“=COUNTIF(B1:B13,=8
12、0)”，即可统计出 B1 至 B13 单元格区域中，数值大于或等于 80 的单元格数目。 COUNTBLANK(range)函数用于计算指定单元格区域(range)中空白单元格的个数。 COUNTA(value1,value2, .)函数返回参数列表(value1,value2, .)中非空值的单 元格个数。（2）AVERAGE 函数 主要功能：求出所有参数的算术平均值。使用格式：AVERAGE(number1,number2, .)参数说明：number1,number2, .：需要求平均值的数值或引用单元格（区域），参 数不超过 30 个。应用举例：在 B8 单元格中输入公式：“=AVER
13、AGE(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
14、。图 6-5 RANK 函数用法示例6数据库函数（1）DAVERAGE 函数 主要功能：返回列表或数据库中满足指定条件的列中数值的平均值。 使用格式：DAVERAGE(database,field,criteria)参数说明：database 为构成列表或数据库的单元格区域；field 指定函数所使用的数 据列，列表中的数据列必须在第一行具有标志项，field 可以是文本，即两端带引号 的标志项，如“使用年数”、“产量”等，field 也可以是代表列表中数据列位置的 数字：1 表示第一列，2 表示第二列，等等；criteria 为一组包含给定条件的单元格 区域。应用举例：参见图 6-6。DCO
15、UNT、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 为 在tab
