《2022年Excel常用函数使用技巧.pdf》由会员分享，可在线阅读，更多相关《2022年Excel常用函数使用技巧.pdf（19页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Excel 常用函数使用技巧Excel 常用函数使用技巧目录一、EXCEL 函数概述二、逻辑函数三、日期时间函数四、文本函数五、数学函数六、统计函数七、检视与参照函数精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 1 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧一、EXCEL 函数概述1、函数结构 : =函数名称 (参数) 说明: 函数以公式形式表达 , 输入公式应加引导符号= ;参数: 可以就是数字、文本、逻辑值(TRUE 或 FALSE) 、数组、错误值 (#
2、N/A) 、单元格引用等 ; 圆括号 “() ”尽量在英文状态下输入。2、数据格式 : 通用型文本型 (文字文本与数字文本 ) 数值型百分比日期型时间型货币型科学计数3、运算符(1) 算术运算符 :+ - * / (2) 比较运算符 : = = = 比较运算的结果为逻辑值TRUE 或 FALSE (3) 文本运算符 : 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 2 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧&(连接多个文本字符 , 产生一串新文本 ) (4
3、) 引用运算符 : 引用单元格 , 有相对引用 (A5:B7) 、绝对引用($A$5:$B$7) 与混合引用 ($A5:B$7) 。(5) 逻辑运算符 :NOT AND OR (6) 通配符 (特殊):星号“*”: 可代替任意数目的字符 (一个或多个 ) 。问号“?”: 可代替任何单个字符。仅对字符型数据 进行操作的 (包括:Match() 、Vlookup() 、Countif()、Sumif() 等函数), 对数值型数据无效的。4、函数嵌套函数嵌套就是指根据需要, 在一个函数的使用过程中调用其她函数。比如: =WEEKDAY(TODAY(),2); =IF(MAX(A3:A8),IF(),
4、“ ”) 5、公式运算结果出错信息 (举例):(1) #NUM!:数字不能被运算(2) #NAME!:引用了不能被识别的函数名或单元格(3) #VALUE!:使用了错误的数据类型(4) #!:单元格显示不下内容 ( 宽度不够 ) 运算符与数字、文本等连接, 可构成表达式精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 3 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧(如:A5=30;left(A4,1)=“王” ) 二、逻辑函数IF 主要功能 : 根据对指定条件的逻
5、辑判断的真假结果, 返回相对应的内容。使用格式 : =IF(Logical,Value_if_true,Value_if_false)参数说明 : Logical代表逻辑判断表达式 ;Value_if_true表示当判断条件为逻辑“真 (TRUE) ”时的显示内容 , 如果忽略返回“TRUE ”;Value_if_false表示当判断条件为逻辑“假(FALSE)”时的显示内容 , 如果忽略返回“ FALSE ”。特别提醒 : IF 函数嵌套最多 :excel07版 64 层;excel03版 7 层。OR 主要功能 : 返回逻辑值 , 仅当所有参数值均为逻辑“假(FALSE)”时返回函数结果逻
6、辑 “假(FALSE)” , 否则都返回逻辑“真(TRUE)” 。使用格式 :=OR(logical1,logical2, 、 )参数说明 : Logical1,Logical2,Logical3: 表示待测试的条件值或表达式 , 最多这 30 个。特别提醒 : AND 与 NOT 函数的用法相似三、日期时间函数精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 4 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧DATETODAY/NOW DATEDIF YEAR /M
7、ONTH/DAY WEEKDAY HOUR/MINUTE/SECOND DATE 主要功能 : 给出指定数值的日期。使用格式 := DATE(year,month,day) 参数说明 : year 为指定的年份 (小于 9999);month 为指定的月份( 可以大于 12);day 为指定的天数 (可以大于 30)。TODAY/NOW 主要功能 : 用于显示系统日期 / 时间。使用格式 := TODAY();=NOW() 参数说明 : 该函数不需要参数。特别提醒 : 显示出来的日期 / 时间格式 , 可以通过单元格格式进行重新设置。DATEDIF 主要功能 : 计算返回两个日期参数的差值。使
8、用格式 : =DATEDIF(start_date,end_date,unit) 参数说明 : start_date表示开始日期 ,end_date 代表结束日期 , 而且结束日期必须大于开始日期 ; unit( 形式): (Y 、M 、D)要求返回两个日期相差的年、月、天数; 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 5 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧YM:忽略年与日 , 计算两个日期的月份差 ; YD:忽略年 , 计算两个日期中的天数差 ;
9、 MD: 忽略年与月 , 计算日差。特别提醒 : 这就是 Excel 中的一个隐藏函数 , 在函数向导中就是找不到的 , 可以直接输入使用 , 对于计算年龄、工龄 等非常有效。YEAR/MONTH/DAY 主要功能 : 返回指定日期年份 / 月份/ 日。使用格式 : =YEAR(date) WEEKDAY 主要功能 : 给出指定日期的对应的星期数( 星期几 )使用格式 : =WEEKDAY(serial_number,return_type)参数说明 : serial_number代表指定的日期或引用含有日期的单元格; return_type代表星期的表示方式 ( 参数): 参数就是 2:(星
10、期一 ) 为 1,( 星期日)为 7(符合习惯 ); 参数就是 1:(星期日 ) 为 1,( 星期六 )为 7; 参数就是 3:(星期一 ) 为 0,( 星期日)为 6。特别提醒 : 如果就是指定的日期 , 请放在英文状态下的双引号中,如=WEEKDAY(2003-12-18,2)。HOUR/MINUTE/SECOND 主要功能 : 返回指定时间的时 / 分/ 秒。使用格式 : = HOUR/MINUTE/SECOND (time) 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 6 页，共 19 页 -
11、 - - - - - - - - - Excel 常用函数使用技巧四、文本函数LEN LEFT/RIGHT MID REPLACE TEXT VALUE TRIMLEN 主要功能 : 统计文本字符串中字符数目。使用格式 := LEN (text)参数说明 : LEN要统计时 , 无论中全角字符 , 还就是半角字符 , 每个字符均计为“ 1”。LEFT/RIGHT 主要功能 : 从一个文本字符串的最后一个字符/ 第一个字符 , 截取指定数目的字符。使用格式 := LEFT/RIGHT (text,num_chars)参数说明 : text 代表要截字符的字符串 ,num_chars 代表给定的截
12、取数目。MID 主要功能 : 从一个文本字符串的指定位置开始, 截取指定数目的字符。使用格式 := MID (text,start_num,num_chars)参数说明 : text 代表要截字符的字符串 , start_num表示指定的起始位置 ,num_chars 代表给定的截取数目。精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 7 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧REPLACE 主要功能 : 根据所指定的字符数 , 使用其她文本替换某字符串中的
13、部分文本 ( 连续一些 ) 。使用格式: =REPLACE(old_text,start_num,num_chars,new_text) 参数说明 :Old_text就是要替换其部分字符的文本或文本所在单元格。Start_num 就是要用 new_text 替换的 old_text 中字符的位置。Num_chars就是希望 REPLACE 使用 new_text 替换 old_text 中字符的个数。New_text 就是要用于替换 old_text 中字符的文本。区别: =SUBSTITUE (text,old_text,new_text,instance_num) 本函数 : 用新文本替换
14、原字符串中指定位置的文本(或者)从某位置开始, 用新文本替换原字符串中的指定文本(此时省略instance_num)TEXT 主要功能 : 根据指定的数值格式将相应的数字转换为文本形式。使用格式 := TEXT(value,format_text)参数说明 : value 代表需要转换的数值或引用的单元格;format_text为指定文字形式的数字格式。精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 8 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧特别提醒 : f
15、ormat_text参数可以根据 “单元格格式” 对话框“数字”标签中的类型进行确定。VALUE 主要功能 : 将一个代表数值的 文本型字符串转换为数值型。使用格式 : =VALUE(text) 参数说明 : text 代表需要转换文本型字符串数值。特别提醒 : 如果文本型数值不经过上述转换, 在用函数处理这些数值时 , 常常返回错误。TRIM 主要功能 : 删除字符串 中多余的空格 , 但会在英文字符串中保留一个作为词与词之间分隔的空格。使用格式 := TRIM(string)五、数学函数INTMOD PRODUCT SUMPRODUCT ROUNDUP SUMIF SUMIFS CEILI
16、NG FLOOR ODD ABS INT 主要功能 : 将数值向下取整为最接近的整数。使用格式 : =INT(number) 参数说明 : number表示需要取整的数值或包含数值的单元格。精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 9 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧特别提醒 : number大于 0 时, 不进行四舍五入 ;number 小于 0 时,进行四舍五入。MOD 主要功能 : 求出两数相除的 余数。使用格式 := MOD(number
17、,divisor) 参数说明 : number代表被除数 ,divisor代表除数应用举例 : 从身份证号码中提取性别 ; 判断就是否就是闰年特别提醒 : 如果 divisor参数为零 , 则显示错误值“ #DIV/0! ”; 值的符号与 divisor(除数)的符号相同。PRODUCT 主要功能 : 求若干个数字的连乘积使用格式 := PRODUCT(number1,number2、 ) 特别提醒 : 最多可以使用 255 个参数SUMPRODUCT 主要功能 : 计算多列中对应值相乘之后的与, 即乘积之与。使用格式 : =SUMPRODUCT(array1,array2,array3, )
18、 或 =SUMPRODUCT(条件 1)* (条件 2)* , 加总范围 ) 参数说明 : 数组为 2 到 30 个, 各个数组参数必须 具有相同的维数 , 否则将返回错误值 #VALUE!。 若数据区域中有错误值时 , 计算出现错误值 #N/A。精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 10 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧 若数据区域中有非数值时 , 计算时视为 0 ROUNDUP 主要功能 : 对任意实数 向上舍入 。使用格式 : =ROU
19、NDUP(number,num_digits) 参数说明 : number表示需要舍入的实数 ,Num_digits 舍入后的数字的小数位数。特别提醒 : 如果 num_digits 大于 0, 则向上舍入到指定的小数位; 如果 num_digits 等于 0, 则向上舍入到最接近的整数; 如果num_digits 小于 0, 则在小数点左侧向上进行舍入。SUMIF 主要功能 : 计算符合指定条件的单元格区域内的数值与。使用格式 := SUMIF(Range,Criteria,Sum_Range) 参数说明 : Range代表条件判断所在的区域(参考区域 );Criteria为指定 条件表达式
20、 (仅一个条件 ) ;Sum_Range 代表需要计算 的数值区域。SUMIFS 主要功能 : 根据多个指定条件 对若干单元格求与使用格式 := SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, 、 ) 参数说明 : sum_range表示实际求与区域 , criteria_range1表示参考区域 1, criteria1表示条件 1。CEILING 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 11 页
21、，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧主要功能 : 返回大于等于数值的最小整数使用格式 : = CEILING(number,significance) 参数说明 : number 必须为数值型 ,significance为该数值的倍数。FLOOR 主要功能 : 返回小于等于数值的最大整数使用格式 : = FLOOR(number,significance) 参数说明 : number 必须为数值型 ,significance为该数值的倍数。ODD 主要功能 : 将一个不就是奇数的 数值向上舍 入为最接近的奇数。使用格式 : = ODD(numbe
22、r) 参数说明 : number 必须为数值型 , 若 number非数值参数 , 函数ODD 将返回错误值 #VALUE!。ABS 主要功能 : 求数值或数值表达式的绝对值。使用格式 : = ABS(number/reference) 参数说明 : number/reference必须为数值型。六、统计函数COUNTIF COUNTIFS COUNTBLANK 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 12 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧RA
23、NKFREQUENCY AVERAGEIF MAX/MIN MODE MEDIAN LARGE/SMALL COUNTIF 主要功能 : 对符合指定条件的单元格区域计数。使用格式 := COUNTIF(Range,Criteria) 参数说明 : Range表示需要计数的区域 ;Criteria为指定 条件表达式( 数字、表达式或文本 ) 。COUNTIFS 主要功能 : 根据多个指定条件 对若干单元格计数使用格式 := COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, 、 ) 参数说明 : criteria_ra
24、nge1表示参考区域 1, criteria1表示条件 1。 用法与 countif类似, 但 countif针对单一条件 , 而 countifs可以实现多个条件同时求结果。COUNTBLANK 主要功能 : 计算指定单元格区域中空白单元格个数使用格式 := COUNTBLANK(range) 参数说明 : 空文本 ” ” 也会计算在内应用举例 :查瞧员工缺勤天数 /次数RANK 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 13 页，共 19 页 - - - - - - - - - - Excel
25、常用函数使用技巧主要功能 : 求某一个数值在某一区域内的排名使用格式 := RANK(number,reference,order) 参数说明 : Number必须有一个数字值 ,reference必须就是一个数组或单元格区域 ( 数据值 ); order: 为 0 或不输入 , 结果按降序排列。为1, 结果按升序排列。特别提醒 : reference采用绝对引用符号 ( “$”) 。这样设置后 ,选中 “填充柄”向下拖拉 , 公式才不会随之变动。FREQUENCY 主要功能 : 以一列垂直数组返回某个区域中数据的频率分布。使用格式 := FREQUENCY(data_array,bins_a
26、rray) 参数说明 : Data_array 表示用来计算频率的一组数据或单元格区域;Bins_array表示为前面数组进行分隔一列数值(分段标准 )。特别提醒 : 输入结果时 , 先选定结果所在区域。输入完成后, 需要通过按“ Ctrl+Shift+Enter”组合键进行确认 , 确认后公式两端出现一对大括号 (),此大括号不能直接输入。AVERAGEIF 主要功能 : 返回某个区域内满足给定条件的的平均值( 求非零平均值) 。使用格式 := AVERAGEIF(criteria_range, criteria, average_range) 或精品资料 - - - 欢迎下载 - - -
27、- - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 14 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧= AVERAGEIF(average_range, criteria) 参数说明 : criteria_Range为必需 , 指条件所在或代表的区域, 其中包含数字或包含数字的名称、数组或引用; Criteria就是条件表达式 , 形式为数字、表达式、单元格引用或文本的条件 ; Average_range 可选, 计算平均值的实际单元格组。如果省略 ,则使用 range 。MAX/MIN 主要功能 : 求一组
28、数的最大值 / 最小值使用格式 := MAX/MIN (number1, number2, number3 ) 参数说明 : 参数含文本或逻辑值 , 则忽略 , 且参数不超过 30 个。MODE 主要功能 : 返回在某一数组或数据区域中出现频率最多的数值。使用格式 := MODE (number1, number2, number3 ) 参数说明 : 如果数组中不含有重复的数据, 则返回错误值 N/A。参数不超过 30 个。MEDIAN 主要功能 : 返回在某一数组或数据区域的值。使用格式 := MEDIAN (number1, number2, number3 ) 精品资料 - - - 欢迎
29、下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 15 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧参数说明 : 参数不超过 255 个。LARGE/SMALL 主要功能 : 返回在某一数据组中的第K个最大值 / 最小值。使用格式 := LARGE/SMALL (array,k) 参数说明 : array 为需要找到第 k 个最大值 / 最小值的数组或数字型数据区域。 k 为返回的数据在数组或数据区域里的位置( 从大到小 ) /(从小到大) 。七、检视与参照函数INDEX 主要功能 : 返回
30、列表或数组中的元素值, 此元素由行序号与列序号的索引值进行确定。使用格式 := INDEX(array,row_num,column_num)参数说明 : Array 代表单元格区域或数组常量;Row_num 表示指定的行序号 ( 如果省略 row_num,则必须有column_num);Column_num 表示指定的列序号 (如果省略column_num,则必须有 row_num)。( row_num与 column_num必须有一个 ) MACTH 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 1
31、6 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧主要功能 : 返回在指定方式下与指定数值匹配的数组中元素的相应位置。使用格式 : =MATCH(lookup_value,lookup_array,match_type) 参数说明 : Lookup_value 代表需要在数据表中查找的数值; Lookup_array 表示可能包含所要查找的数值的区域; Match_type 表示查找方式 (-1 、0 或 1):值为-1: 查找大于等于 lookup_value的最小数值,Lookup_array 必须按降序排列 ;值为 0: 查找等于 lookup_v
32、alue 的第一个数值,Lookup_array 可以按任何顺序排列 ; 值为 1: 查找小于等于 lookup_value 的最大数值,Lookup_array 必须按升序排列 ; 如果省略 match_type, 则默认为 1。特别提醒 : 区域必须就是 某一行或某一列 , 即一维数据ROW 主要功能 : 返回一个引用的行号。使用格式 := ROW(reference)参数说明 : Reference 为需要得到其行号的单元格或单元格区域。精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 17 页，共
33、19 页 - - - - - - - - - - Excel 常用函数使用技巧特别提醒 : 若 Reference 为一个区域 , 输入结果时 , 先选定结果所在区域。输入完成后 , 需要通过按“ Ctrl+Shift+Enter”组合键进行确认。COLUMN 主要功能 : 返回一个引用的列号。使用格式 := COLUMN(reference)参数说明 : Reference 为需要得到其列号的单元格或单元格区域。特别提醒 : 若 Reference 为一个区域 , 输入结果时 , 先选定结果所在区域。输入完成后 , 需要通过按“ Ctrl+Shift+Enter”组合键进行确认。VLOOKU
34、P 主要功能 : 在数据表的首列查找指定的数值, 并由此返回数据表当前行中指定 列处的数值。使用格式: =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 时, 返回第 3 列的值 );Range_lookup 为一逻辑
35、值 , 如果为 FALSE 或 0, 表示精精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 18 页，共 19 页 - - - - - - - - - - Excel 常用函数使用技巧确查找 ; 如果为 TRUE 或 1, 表示模糊查找 ( 返回小于 lookup_value的最大数值 ); 如果找不到 , 则返回错误值 #N/A。特别提醒 : 查找值(lookup_value)必须位于查找区域 (table_array)的第一列; 查找值与被查找值 ( 在查找区域中 )的数据类型必须一样 , 否则返回“
36、#NA ”。ISERROR 主要功能 : 用于测试函数式返回的数值就是否有错。如果有错, 该函数返回 TRUE, 反之返回 FALSE 。使用格式 := ISERROR(value) 参数说明 : Value 表示需要测试的值或表达式。特别提醒 : 此函数通常与 IF 函数配套使用 , 比如公式:=IF(ISERROR(A35/B35),A35/B35),如果 B35为空或“ 0”,则相应的单元格显示为空, 反之显示 A35/B35。精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 19 页，共 19 页 - - - - - - - - - -