Excel数据分析中的35个常用Excel函数.doc

1、01. ABS函数 函数名称： ABS 主要功能：求出相应数字的绝对值。 使用格式： ABS(number) 参数说明： number 代表需要求绝对值的数值为 引 用 的 单元格。应用示例：如 果在 B2 单元格中输入公式： =ABS(A2，)则在 A2 单元格中无论输 入 正 数 （如 100） 还是负数（如 -100） ， B2 中均显示出正数（如100） 。 特别提醒：如果number 参数不是数值，而是一些字符（如A 等），则B2 中 返回错值“ #VALUE！ ” 。 02. AND 函数 函数 名称： AND 主要功能：返回逻辑值：如果所有参数值均为逻辑“真（TRUE） ” ，

2、则返回逻辑 “真（TRUE）”，反之返回逻辑 “ 假 （ FALSE） ” 。 使用格式：AND(logical1,logical2,) 参数说明： Logical1,Logical2,Logical3 ：表示待测试 的 条 件 值为表达式，最多 返 30 个。 应 用 示 例 ： 在C5 单元格输入公式： =AND(A5=60,B5=60，) 确认。如果C5 中 返回 TRUE，说明 A5 和 B5 中的数值均大于等于60，如果返回FALSE，说明 A5 和 B5 中的数值至少有一个小于60。 特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值 “#VALUE”!为“ #NA

3、ME” 。 03. AVERAGE 函数 函数名 称： AVERAGE 主要功能：求出所有参数的算术平均值。 使用格式： AVERAGE(number1,number2 , ) 参数说明：number1,number2,：需要求平均值的数值为 引 用 单元格（区域）， 参数不超过30 个。 应 用 示 例 ： 在B8 单元格中输入公式： =AVERAGE(B7:D7,F7:H7,7，,8)确认后，即 可求出 B7 至 D7 区域、 F7 至 H7 区域中的数值和7、8 的 平均值。 特别提醒：如果引用区域中包含“0”值单 元 格 ， 则计 算 在 内 ； 如果引用区域中 包含空白为字符单元格，

4、则不计算在内。 04. COLUMN 函数 函数 名称： COLUMN 主要功能：显示所引用单元格的列标号值。 使用格式： COLUMN(reference) 参数说明： reference 为引用的单元格。 应 用 示 例 ： 在C11 单元格中输入公式： =COLUMN(B11，) 确认后显示为2（即 B 列）。 特别提醒：如果在B11 单元格中输入公式： =COLUMN(，) 也显示出2；反之相 对应的还有一个返回行标号值的函数 ROW(reference)。 05. CONCATENATE 函数 函数名 称： CONCATENATE 主要功能：将多个字符文本为单元格中的数据连接在一起，

5、显示在一个单元格中。 使用格式： CONCATENATE(Te，xt1Text) 参数说明： Text1、Text2 为需要连接的字符文本为引用的单元格。 应 用 示 例 ： 在 C14 单元格中输入 公式： =CONCATENATE(A1” 4 , ” ,B14,” .com” )， 确认后，即可将 A14 单元格中字符、 、 B14 单元格中的字符和 .com 连接成一 个整体，显示在C14 单元格中。 特别提醒： 如果参数不是引用的单元格，且为文本格式的，请给参数加上英文状 态下的双引号，如果将上述公式改为：=A14&” &B14&”.com”，也能达到 相同的目的。 06. COUNT

6、IF 函数 函数名称： COUNTIF 主要功能：统计 某 个 单元格区域中符合指定条件的单元格数目。 使用格式： COUNTIF(Range,Criteria) 参数说明： Range 代表要统计的单元格区域；Criteria 表示指定的条件表达式。 应 用 示 例 ： 在C17 单元格中输入公式： =COUNTIF(B1:B13” , =80)，确认后， 即可统计出B1 至 B13 单元 格区域中，数值大于等于80 的单元格数目。 特别提醒：允许引用的单元格区域中有空白单元格出现。 07. DATE 函数 函数名称： DATE 主要功能：给出指定数值的日期。 使用格式： DATE(year

7、,month,day) 参数说明：year 为指定的年份数值（小于 9999） ； month 为指定的月份数值（可 以大于 12） ； day 为指定的天数。 应 用 示 例 ： 在C20 单元格中输入公式：=DATE(2003,13,35，) 确认后，显示出 2004-2-4。 特别提醒：由于上述公式中，月份为13，多了一个月，顺延至2004 年 1 月； 天数为35，比 2004 年 1 月的实际 天 数 又 多 了4 天，故又顺延至2004 年 2 月 4 日。 08. 函数名称： DATEDIF 主要功能：计算返回两个日期参数的差值。 使 用 格 式 ： =DATEDIF(date1

8、,date2” , y” )、 =DATEDIF(date1,date2” , m” )、 =DATEDIF(date1,date2” , d”) 参数说明： date1 代表前面一个日期， date2 代表后面一个日期； y（m、d）要 求返回两个日期相差的年（月、天）数。 应用示例：在C23单元格中输入公式： =DATEDIF(A23,TODAY” ()y,”)，确认后返 回系统当前日期 用 TODAY(表 ) 示）不 A23 单元格中日期的差值，并返回相差的 年数。 特别提醒： 返是 Excel中的一个隐藏函数， 在函数向导中是找不到的， 可以直接 输入使用，对于计算年龄、工龄等非常有效

9、。 09. DAY 函数 函数 名称： DAY 主要功能：求出指定日期为引用单元格中的日期的天数。 使用格式： DAY(serial_number) 参数说明： serial_number 代表指定的日期为引用的单元格。 应用示例：输入公式： =DAY“( 2003-12-18)，确认后，显示出18。 特别提醒：如果是给定的日期，请包含在英文双引号中。 10. DCOUNT 函数 函数 名称： DCOUNT 主要功能：返回数据库为列表的列中满足指定条件并且包含数字的单元格数目。 使用格式： DCOUNT(database,field,criteria) 参数说明： Database 表示需要统

10、计的单元格区域；Field 表示函数所使用的数据 列（在第一行必须要有标志项） ；Criteria 包含条件的单元格区域。 应用示例：如图1 所示，在F4 单元格中输入公式：=DCOUNT(A1:D11” , 语 文”,F1:G2)，确认后即可求出“语文”列中，成绩大于等于70，而小于80 的 数值单元格数目（相当于分数段人数） 。 特别提醒： 如果将上述公式修改为： =DCOUNT(A1:D11,F1:G，2) 也可以达到相同 目的。 11. FREQUENCY 函数 函数名称： FREQUENCY主要功能：以一列 垂直数组返回某个区域中数据的频率分布。 使用格式： FREQUENCY(da

11、ta_array,bins_arra 参 y)数说明： Data_array 表示用来计 算频率的一组数据为单元格区域；Bins_array 表示为前面数组进行分隔一列数值。 应用示例：如图2 所示，同时选中B32 至 B36 单元格区域，输入公式： =FREQUENCY(B2:B31,D2:D3， 6 输) 入完成后按下“Ctrl+Shift+Enter”组合键进行 确认，即可求出B2 至 B31 区域中，按D2 至 D36 区域进行分隔的各段数值 的出现频率数目（相当于统计各分数段人数）。 特 别 提 醒 ： 上 述 输 入 的 是 一 个 数 组 公 式 ， 输 入 完 成 后 ， 需

12、要 通 过 按 “Ctrl+Shift+Enter”组合键进行确认，确认后公式两端出现一对大括号（），此 大括号不能直接输入。 12. IF 函数 函数名称： IF 主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 使用格式： =IF(Logical,Value_if_true,Value_if_false) 参数说明： Logical 代表逻辑判断表达式；Value_if_true 表示当判断条件为逻辑 “真（TRUE）”时的显示内容，如果忽略返回“TRUE” ； Value_if_false 表示当判 断条件为逻辑“假（ FALSE）”时的显示内容，如果忽略返回“ FALS

13、E” 。 应用示例：在C29 单元格中输入公式： =IF(C26=18” , 符合要求” ,” 不符合要 求”)，确信以后，如果C26 单元格中的数值大于为等于18， 则 C29 单元格 显 示“符合要求”字样，反之显示“不符合要求”字样。 特别提醒：本文中类似“在C29 单元格中输入公式”中指定的单元格，读者在 使用时，并不需要受其约束， 此处只是配合本文所附的实例需要而给出的相应单 元格，具体请大家参考所附的实例文件。 13. INDEX函数 函数 名称： INDEX 主要功能：返回列表为数组中的元素值， 此元素由行序号和列序号的索引值进行 确定。 使用格式： INDEX(array,ro

14、w_num,column_num) 参数说明： Array 代表单元格区域为数 14. INT 函数 函数名称： INT主要功能：将数值 向下取整为最接近的整数。 使用格式： INT(number) 参数说明： number 表示需要取整的数值为包含数值的引用单元格。 应用示例：输入公式： =INT(18.89)，确认后显示出18。 特别提醒：在取整时，不进行四舍五入；如果输入的公式为 =INT(-18.89)，则返回 结果为-19。 15. ISERROR 函数 函数 名称： ISERROR 主要功能：用于测试函数式返回的数值是否有错。如果有错，该函数返回TRUE， 反之返回FALSE。 使

15、用格式： ISERROR(value) 参数说明： Value 表示需要测试的值为表达式。 应用示例：输入公式： =ISERROR(A35/B3，5)确认以后，如果B35 单元格为空为 “0” ， 则 A35/B35 出现错误，此时前述函数返回TRUE结果，反之返回FALSE。 特别提 醒： 此函数通常不 IF 函 数配套 使用 ，如 果将上述公式修改 为： =IF(ISERROR(A35/B35” ) ” , ,A35/B35)，如果 B35 为空为“ 0”，则相应的单元格显 示为空，反之显示A35/B35 的结果。 16. LEFT 函数 函数名称： LEFT 主要功能：从一个文本字符串的

16、第一个字符开始，截取指定数 目的字符。 使用格式： LEFT(text,num_chars) 参数说明： text 代表要截字符的字符串； num_chars 代表给定的截取数目。 应用示例：假定A38 单元格中保存了“我喜欢天极网”的字符串，我们在 C38 单 元 格 中 输入公式： =LEFT(A38,3， ) 确认后即显示出“我喜欢”的字符。 特别提醒：此函数名的英文意思为“左” ，即从左边截取， Excel 很多函数都取其 英文的意思。 17. LEN函数 函数 名称： LEN 主要功能：统计文本字符串中字符数目。 使用格式： LEN(text) 参数说明： text 表示要统计的文本

17、字符串。 应用示例：假定 A41 单元格中保存了 “我今年28 岁”的字符串，我们在 C40 单 元格中输入公式： =LEN(A40，) 确认后即显示出统计结果“6” 。 特别提醒：LEN要统计时，无论中全角字符，还是半角字符，每个字符均计为 “ 1” ； 不之相对应的一个函数 LENB， 在 统计时半角字符计为 “ 1”，全角字符计为 “ 2” 。 18. MATCH 函数 函数名称： MATCH主要功能：返回在指定方式下不指定数值匹配的数组中元素 的相应位置。 使用格式： MATCH(lookup_value,lookup_array,match_type) 参数说明： Lookup_va

18、lue 代表需要在数据表中查找的数值；Lookup_array 表示 可能包含所要查找的数值的连续单元格区域；Match_type 表示查找方式的值（-1、 0 为1） 。 如 果match_type 为-1，查找 大 于为等 于lookup_value 的 最 小 数值， Lookup_array 必须按降序排列； 如果 match_type 为1，查找小于为等于 lookup_value 的最大数值，Lookup_array 必须按升序排列； 如果 match_type 为0，查找等于lookup_value 的第一个数值，Lookup_array 可 以按任何顺序排列；如果省略match

19、_type，则默认为1。 应 用 示 例 ： 如 图4 所示，在F2 单元格中输入 公式： =MATCH(E2,B1:B11,0， )确 认后则返回查找的结果“9” 。 特别提醒： Lookup_array 只能为一列为一行。 19. MAX 函数 函数名称： MAX 主要功能：求出一组数中的最大值。 使用格式： MAX(number1,number2) 参数说明： number1,number2代表需要求最大值的数值为引用单元格（区 域），参数不超过30 个。 应 用 示 例 ： 输入公式：=MAX(E44:J44,7,8,9,10， )确认后即可显示出 E44 至 J44 单 元和区域和数

20、值7，8，9，10 中的最大值。 特别提醒：如果参数中有文本为逻辑值，则忽略。 20. MID 函数 函 数名称： MID 主要功能：从一个文本字符串的指定位置开始，截取指定数目的字符。 使用格式： MID(text,start_num,num_chars) 参数说明：text 代表一个文本字符串；start_num 表示指定的起始位置； num_chars 表示要截取的数目。 应用示例：假定A47 单元格中保存了“我喜欢天极网”的字符串，我们在 C47 单 元 格 中 输入公式： =MID(A47,4,3)，确认后即显示出“天极网”的字符。 特别提醒：公式中各参数间，要用英文状态下的逗号“,

21、”隔开。 21. MIN 函数 函 数名称： MIN 主要功能：求出一组数中的最小值。 使用格式： MIN(number1,number2) 参数说明：number1,number2代表需要求最小值的数值为 引 用 单元格（区域）， 参数不超过30 个。 应 用 示 例 ： 输入公式：=MIN(E44:J44,7,8,9,10，) 确认后即可显示出E44 至 J44 单 元和区域和数值7，8，9，10 中的最小值。 特别提醒：如果参数中有文本为逻辑值，则忽略。 22. MOD 函数 函数名称： MOD主 要功能：求出两数相除的余数。 使用格式： MOD(number,divisor) 参数说明

22、： number 代表被除数； divisor 代表除数。 应 用 示 例 ： 输入公式： =MOD(13,4)，确认后显示出结果“ 1” 。 特别提醒：如果divisor 参数为零，则显示错误值“ #DIV/0!” ； MOD 函数可以借 用函数 INT 来表示：上述公式可以修改为：=13-4*INT(13/4)。 23. MONTH 函数 函数 名称： MONTH 主要功能：求出指定日期为引用单元格中的日期的月份。 使用格式： MONTH(serial_number) 参数说明： serial_number 代表指定的日期为引用的单元格。 应 用 示 例 ： 输入公式： =MONTH(“2

23、003-12-18”)，确认后，显示出12。 特别提醒：如果是给定的日期，请包含在英文双引号中； 如果将上述公式修改为： =YEAR“(2003-12-18)，则返回年份对应的值“ 2003” 。 24. NOW 函数 函数 名称： NOW 主要功能：给出当前系统日期和时间。 使用格式： NOW() 参数说明：该函数不需要参数。 应 用 示 例 ： 输入公式：=NOW()，确认后即刻显示出当前系统日期和时间 。 如 果 系统日期和时间发生了改变，只要按一下F9 功能键，即可让其随之改变。 特别提醒：显示出来的日期和时间格式，可以通过单 元 格 格 式 进行重新设置。 25. OR 函数 函数

24、名称： OR 主要功能：返回逻辑值，仅当所有参数值均为逻辑“假（FALSE） ” 时返回函数结 果逻辑 “ 假 （ FALSE） ” ， 否则都返回逻辑 “ 真 （ TRUE） ” 。 使用格式： OR(logical1,logical2, ) 参数说明： Logical1,Logical2,Logical3 ：表示待测试 的 条 件 值为表达式，最多 返 30 个。 应 用 示 例 ： 在 C62 单元格输入公式： =OR(A62=60,B62=60， )确认。如果 C62 中 返回 TRUE，说明 A62 和 B62 中的数值至少有一个大于为等于60，如果返回 FALSE，说明 A62 和

25、 B62 中的数值都小于 60。 特别提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值 “#VALUE”!为“ #NAME” 。 26. RANK函数 函数 名称： RANK 主要功能：返回某一数值在一列数值中的相对于其他数值的排位。 使用格式： RANK（Number,ref,order） 参数说明：Number 代表需要排序的数值； ref 代表排序数值所处的单元格区域； order 代表排序方式参数 （如果为“0”为者忽略，则按降序排名，即数值越大， 排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果 数值越大；）。 应用示例：如在C2 单元格中输入公式

26、： =RANK(B2,$B$2:$B$31,0， ) 确认后即可 得出丁 1 同学的语文成绩在全班成绩中的排名结果。 特别提醒：在上述公式中，我们让 Number 参数采取了相对引用形式，而让ref 参数采取了绝对引用形式（增加了一个“$”符号），返样设 置 后 ， 选中C2 单元 格，将鼠标移至该单元格右下角，成细十字线状时（通常称之为“填充柄”），按 住左键向下拖拉，即可将上述公式快速复制到C 列下面的单元格中，完成其他 同学语文成绩的排名统计。 27. RIGHT 函数 函数名称： RIGHT 主要功能：从一个文本字符串的最后一个字符开始，截取指定数目的字符。 使用格式： RIGHT(t

27、ext,num_chars) 参数说明： text 代表要截字符的字符串； num_chars 代表给定的截取数目。 应用示例：假定A65 单元格中保存了“我喜欢天极网”的字符串，我们在 C65 单 元 格 中 输入公式： =RIGHT(A65,3， ) 确认后即显示出“天极网”的字符。 特别提醒： Num_chars 参数必须大于为等于0，如果忽略，则默认其为1；如 果 num_chars 参数大于文本长度，则函数返回整个文本。 28. SUBTOTA 函L 数 函数名称： SUBTOTAL 主要功能：返 回列表为数据库中的分类汇总。 使用格式： SUBTOTAL(function_num,

28、ref1,ref2,) 参数说明： Function_num 为1 到 11（包含隐藏值）为101 到 111（忽略隐藏 值 ） 之 间的数字，用来指定使用什么函数在列表中进行分类汇总计算（如图6） ； ref1,ref2,代表要进行分类汇总区域为引用，不超过29 个。 应 用 示 例 ： 如 图7 所示，在B64 和 C 29. SUM 函数 函数 名称： SUM 主要功能：计算所有参数数值的和。 使用格式： SUM（Number1,Number2） 参数说明： Number1、Number2代表需要计算的值，可以是具体的数值、引 用的单元格（区域）、逻辑值等。 应 用 示 例 ： 如 图7

29、 所示，在 D64 单元格中输入公式： =SUM(D2:D63)，确认后即 可求出语文的总分。 特别提醒： 如果参数为数组为 引 用 ， 只有其中的数字将被计算。 数组为 引 用 中 的 空白单元格、逻辑值、文本为错误值将被忽略；如果将上述公式修改为： =SUM(LARGE(D2:D63,1,2,3,4,5，)则可以求出前5 名成绩的和。 30. SUMIF 函数 函数 名称： SUMIF 主要功能：计算符合指定条件的单元格区域内的数值和。 使用格式： SUMIF（Range,Criteria,Sum_Rang）e 参数说明： Range 代表条件判断的单元格区域；Criteria 为指定条件

30、表达式； Sum_Range 代表需要计算的数值所在的单元格区域。 应 用 示 例 ： 如 图7 所示，在 D64 单元格中输入公式：=SUMIF(C2:C63” ,男”,D2:D63)， 确认后即可求出“男”生的语文成绩和。 特别提醒：如果把上述公式修改为：=SUMIF(C2:C63” , 女” ,D2:D63)，即可求出 “女”生的语文成绩和；其中“男”和“女”由于是文本型的，需要放在英文 状态下的双引号（”男” 、”女”）中。 31. TEXT 函数 函数名称： TEXT主要功能：根据指定的数值格式 将相应的数字转换为文本形式。 使用格式： TEXT(value,format_text)

31、 参数说明：value 代表需要转换的数值为引用的单元格；format_text 为指定文字 形式的数字格式。 应用示例： 如果 B68 单元格中保存有数值1280.45，我们在 C68 单元格中输入 公式：=TEXT(B68“ , $0.00”)，确认后显示为“ $1280.45” 。 特别提醒：format_text 参数可以根据“单元格格式”对话框“数字”标签中的类 型进行确定。 32. TODAY 函数 函数名称： TODAY 主要功能：给出系统日期。 使用格式： TODAY() 参数说明：该函数不需要参数。 应用示例：输入公式： =TODAY(， ) 确认后即刻显示出系统日期和时间。

32、如果系统 日期和时间发生了改变，只要按一下F9 功能键，即可让其随之改变。 特别提醒：显示出来的日期格式，可以通过单元格格式进行重新设置 33. VALUE 函数 函数名称： VALUE 主要功能：将一个代表数值的文本型字符串转换为数值型。 使用格式： VALUE(text) 参数说明： text 代表需要转换文本型字符串数值。 应用示例： 如果 B74 单元格中是通过 LEFT 等函数截取的文本型字符串，我们在 C74 单元格中输入公式： =VALUE(B74， ) 确认后，即可将其转换为数值型。 特别提醒： 如果文本型数值不经过上述转换，在用函数处理返些数值时， 常常返 回错误。 34.

33、VLOOKUP 函数 函数 名称： VLOOKUP 主要功能：在数据表的首列查找指定的数值，并由此返回数据表当前行中指定列 处的数值。 使用格式： VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 参数说明：Lookup_value 代表需要查找的数值； Table_array 代表需要在其中查找 数据的单元格区域； Col_index_num 为在 table_array 区域中待返回的匹配值的 列序号（当Col_index_num 为 2 时,返回 table_array 第 2 列中的数值，为3 时 ， 返 回 第

34、3 列的值）； Range_lookup 为一逻辑值，如果为TRUE 为省略， 则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值；如果为FALSE，则返回精确匹配值，如果找不到，则返回错误值#N/A。 应 用 示 例 ： 参见图 7 ， 我们在D65 单元 格 中输入 公 式:=VLOOKUP(B65,B2:D63,3,FAL，SE 确 )认后，只要在B65 单元格中输入一个学生 的姓名（如丁 48） ， D65 单元格中即刻显示出该学生的语言成绩。 特别提醒： Lookup_value 参见必须在Table_array区域的首列中；如果忽略 R

35、ange_lookup 参数，则Table_array 的首列必须进行排序；在此函数的向导中， 有关 Range_lookup参数的用法是错误 的 。 35. WEEKDAY 函数 函数 名称： WEEKDAY 主要功能：给出指定日期的对应 的 星 期 数 。 使用格式： WEEKDAY(serial_number,return_type) 参数说明： serial_number 代表指定日期引用含有日期单元格；return_type 代表 星期的表示方式 当 Sunday（星期日）为1、 Saturday（星期六）为7 时，该 参数为1；当 Monday（星期一）为1、 Sunday（星期日）为7 时，该参数为 2（返种情况符合中国人的习惯）； 当 Monday（星期一）为0、Sunday（星期 日）为6 时，该参数为3。 应 用 示 例 ： 输入公式：=WEEKDAY(TODAY()，,2 确)认后即给出系统日期得星期数。 特别提 醒 ： 如 果 是 指 定 的 日 期 ，请放 在 英 文 状态下 的 双 引 号 中 ， 如 =WEEKDAY“ ( 2003-12-18”,2)。