excel常用函数大全讲解(关于Excel函数看这一篇就够了)

Excel到底有多强大?小易想说,只是Excel函数这一点,你学会50%,你就很强大了!

Excel函数,不是数学概念里的方程式,它是Excel表格里的——超级计算器,机械重复的人工计算,函数一条公式就能解决。

01

什么是函数

它是由Excel内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块。因此,函数被人们称为“特殊公式”,与公式一样,Excel函数的最终返回结果为值。

函数只有唯一的名称且不区分大小写,它决定了函数的功能和用途。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(1)

举个简单的例子——处理表格时,如何把名字首个字母全部变成大写?

不懂函数的你,是不是一个个手动修改?

如果你知道Proper这个函数,就不会一个个修改了。输入函数公式,3秒搞定!

02

什么是公式

那什么是Excel的公式?

公式就是由用户自行设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的算式。

用户使用公式是为了有目的地计算结果,因此Excel的公式必须(且只能)返回值。

公式的结构:=(C2 D2)*5

从公式结构来看,构成公式的元素通常包括等号、常量、引用和运算符等元素。其中,=号是不可或缺的。但在实际应用中,公式还可以使用数组、Excel函数或名称(命名公式)来进行运算。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(2)

通常情况下,Excel按照从左向右的顺序进行公式运算,当公式中使用多个运算符时,Excel将根据各运算符的优先级进行运算,对于同一级次的运算符,则按自左而右的顺序运算。具体的优先顺序如下表:

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(3)

在使用Excel公式进行计算时,可能会因为某种原因无法得到正确结果,而返回一个错误值。常见的错误值及其含义如下表所示。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(4)

当公式的结果返回错误值时,应该及时地查找错误原因,并修改公式来解决问题。

03

函数与分类

Excel函数通常是由函数名称、左括号、参数、半角逗号和右括号构成。

函数公式结构:=IF(A1>0,”正数”,IF(A1<0,负数,””))

对于函数的参数来说,可以由数值、日期和文本等元素组成,也可以使用常量、数组、单元格引用或其他函数。当函数的参数也是函数时,Excel称之为函数的嵌套。

函数一共有11类,分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(5)

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(6)

04

数据分析师常用函数

本文内容为目录式的,介绍每个函数是做什么的、遇到某个问题可以用哪个函数解决等,具体使用方法各位可以自行百度学习。

对于函数,不用死记硬背,只需要知道应该选取什么类别的函数,以及需要哪些参数怎么用就行了!比如选取字段,用LEFT/RIGHT/MID函数......其他细节交给万能的百度吧!

下面根据不同的运用场景,对这些常用的必备函数进行分类介绍。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(7)

TopBDA大数据分析师培训提供更全面的数据分析内容~

1

关联匹配类

需要的数据不在同一个Excel表或同一个Excel表不同sheet中,数据太多copy起来麻烦还容易出错,如何整合呢?

下面这些函数就是用于多表关联或者行列比对时的场景,而且表格越复杂,用起来越爽!

01.VLOOKUP

功能:用于查找首列满足条件的元素。

语法:=VLOOKUP (lookup_value,table_array, col_index_num, [range_lookup])

*备注:[ ]内为可选参数,其余为必需参数,下文同理。

=VLOOKUP (要查找的项、要查找位置、区域中包含要返回的值的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 0/FALSE) 。

例:查询姓名是F5单元格中的员工是什么职务。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(8)

02.HLOOKUP

功能:在表格的首行或数值数组中搜索值,然后返回表格或数组中指定行的所在列中的值。HLOOKUP中的H代表“行”。

语法:=HLOOKUP(lookup_value,table_array, row_index_num, [range_lookup])

例:=HLOOKUP("车轴",A1:C4, 2, TRUE) 在首行查找车轴,并返回同列(列A)中第2行的值。

LOOKUP和HLOOKUP区别:当比较值位于数据表格的首行时,如果要向下查看指定的行数,则可使用HLOOKUP。当比较值位于所需查找的数据的左边一列时,则可使用VLOOKUP。

03.INDEX

功能:返回表格或区域中的值或值的引用。

语法:=INDEX(array,row_num, [column_num])

例:= INDEX(B2:D11,3,3)位于区域A2:B3中第三行和第三列交叉处的数值。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(9)

04.MATCH

功能:用于返回指定内容在指定区域(某行或者某列)的位置。

语法:=MATCH(lookup_value,lookup_array, [match_type])

例:=MATCH(41,B2:B5,0) 单元格区域B2:B5中值41的位置。

match_type:

1或省略:MATCH 查找小于或等于lookup_value的最大值。

0:MATCH查找完全等于lookup_value的第一个值。

-1:MATCH查找大于或等于lookup_value的最小值。

05.RANK

功能:求某一个数值在某一区域内一组数值中的排名。

语法:=RANK(number,ref,[order])

例:=RANK(A3,A2:A6,1) A3在上表中A2:A6的排位

排名方式:0是降,1是升序,默认为0

06.ROW

功能:返回引用的行号。

语法:= ROW([reference])

例:= ROW() 公式所在行的行号

07.COLUMN

功能:返回单元格所在的列。

语法=COLUMN(reference)

例:=COLUMN (D10) 返回4,因为D列是第四列。

8.OFFSET

功能:返回对单元格或单元格区域中指定行数和列数的区域的引用。返回的引用可以是单个单元格或单元格区域。可以指定要返回的行数和列数。

语法:OFFSET(reference, rows, cols,[height], [width])

例:=OFFSET(D3,3,-2,1,1)显示单元格 B6中的值,其中3为下方三行,-2为左方两行,1为行高和列宽。

2

清洗处理类

数据处理之前,需要对提取的数据进行初步清洗,如清除字符串空格,合并单元格、替换、截取字符串、查找字符串出现的位置等。

截取字符串:使用MID /LEFT/ RIGHT

替换单元格中内容:SUBSTITUTE /REPLACE

合并单元格:使用CONCATENATE

清除字符串空格:使用TRIM/LTRIM/RTRIM

查找文本在单元格中的位置:FIND/ SEARCH

9.MID

功能:从中间截取字符串

语法:=MID(text,start_num, num_chars)

例:=MID(A2,1,5) 从A2内字符串中第1个字符开始,返回5个字符。

根据身份证号码提取年月。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(10)

10.LEFT

功能:从左截取字符串。

语法:=LEFT(text,[num_chars])

例:=LEFT(A2,4) 第一个字符串中的前四个字符。

11.RIGHT

功能:从右截取字符串。

语法:=RIGHT(text,[num_chars])

例:=RIGHT(A2,5)第一个字符串的最后5个字符

12. SUBSTITUTE

功能:在文本字符串中用new_text替换old_text。

语法:=SUBSTITUTE(text,old_text, new_text, [instance_num])

例:=SUBSTITUTE(A2, "销售", "成本")将“销售”替换为“成本”(成本数据)

替换部分电话号码。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(11)

13.REPLACE

功能:替换掉单元格的字符串。

语法:=REPLACE(old_text,start_num, num_chars, new_text)

例:=REPLACE(A2,6,5,"*") 在A2中,从第六个字符(f)开始使用单个字符*替换五个字符。

REPLACE和SUBSTITUTE区别:两个函数很接近,不同在于REPLACE根据位置实现替换,需要提供从第几位开始替换,替换几位,替换后的新的文本;而SUBSTITUTE根据文本内容替换,需要提供替换的旧文本和新文本,以及替换第几个旧文本等。

因此REPLACE实现固定位置的文本替换,SUBSTITUTE实现固定文本替换。

14.CONCATENATE

功能:将两个或多个文本字符串联接为一个字符串。

语法:=CONCATENATE(text1,[text2], ...)

合并单元格中的内容,还有另一种合并方式是&,需要合并的内容过多时,CONCATENATE 效率更快。

例:=CONCATENATE(B2, " ", C2) 联接三部分内容:单元格B2中的字符串、空格字符以及单元格C2中的值。

15.TRIM

功能:除了单词之间的单个空格之外,移除文本中的所有空格。

语法:=TRIM(text)

Text为要去掉空格的文本。

例:=TRIM("First Quarter Earnings ") 从公式的文本中移除前导空格和尾随空格。

16.LTRIM

功能:从字符串左侧删除空格或其他预定义字符。

语法:=LTRIM (string, [charlist])

17.RTRIM

功能:从字符串右侧删除空格或其他预定义字符。

语法:= LTRIM(string, [charlist])

18.FIND

功能:查找文本位置

语法:=FIND(find_text,within_text, [start_num])

例:=FIND("M",A2) 单元格A2中第一个“M”的位置

19.SEARCH

功能:返回一个指定字符或文本字符串在字符串中第一次出现的位置,从左到右查找。

语法:=SEARCH(find_text,within_text,[start_num])

例:=SEARCH("e",A2,6) 单元格A2中的字符串中,从第6个位置起,第一个“e”的位置。

FIND和SEARCH区别:这两个函数功能几乎相同,实现查找字符所在的位置,区别在于FIND函数精确查找,区分大小写;SEARCH函数模糊查找,不区分大小写。

20.LEN

功能:返回文本字符串中的字符个数。

语法:=LEN(text)

例:=LEN(A1) A1单元格字符串的长度

21. LENB

功能:返回文本字符串中用于代表字符的字节数。

语法:=LENB(text)

例:=LEN(A1)A1单元格字符串的字节数。

3

逻辑运算类

逻辑,顾名思义,不赘述,直接上函数。

22.IF

功能:使用逻辑函数IF 函数时,如果条件为真,该函数将返回一个值;如果条件为假,函数将返回另一个值。

语法:=IF(Logical,Value_if_true,Value_if_FALSE)

如果指定条件的计算结果为true,IF函数将返回某个值;如果该条件的计算结果为false,则返回另一个值。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(12)

23.COUNTIF

功能:用于统计满足某个条件的单元格的数量;例如,统计特定城市在客户列表中出现的次数。

语法:=COUNTIF(单元格1: 单元格2 ,条件)

统计特定店铺在列表中出现的次数。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(13)

24.AND

功能:逻辑判断,相当于“并”。

语法:全部参数为True,则返回True,经常用于多条件判断。

例:=AND(A2>1,A2<100) 如果A2大于1并且小于100,则显示TRUE;否则显示FALSE。

25.OR

功能:逻辑判断,相当于“或”。

语法:只要参数有一个True,则返回Ture,经常用于多条件判断。

例:=OR(A2>1,A2<100) 如果A2大于1或者小于100,则显示TRUE;否则显示FALSE。

4

计算统计类

在利用Excel表格统计数据时,常常需要使用各种Excel自带的公式,也是最常使用的一类。(对于这些,Excel自带快捷功能)

26.MIN

功能:找到某区域中的最小值。

语法:=MIN(number1, [number2], ...)

例:=MIN(D2:D11) 区域D2:D11中的最小数。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(14)

27.MAX

功能:找到某区域中的最大值。

语法:=MAX(number1, [number2], ...)

例:=MAX(A2:A6) 区域A2:A6中的最大值。

28.AVERAGE

功能:计算某区域中的平均值。

语法:=AVERAGE(number1, [number2], ...)

例:=AVERAGE(D2:D11) 单元格区域D2到D11中数字的平均值。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(15)

29.COUNT

功能:计算含有数字的单元格的个数。

语法:=COUNT(value1, [value2], ...)

例:=COUNT(A2:A7) 计算单元格区域A2到A7中包含数字的单元格的个数。

30.COUNTIFS

功能:统计一组给定条件所指定的单元格数。

语法:COUNTIFS(criteria_range1,criteria1, [criteria_range2, criteria2],…)

例:=COUNTIFS(A2:A7,"<6",A2:A7,">1")计算1和6之间(不包括1和6)有几个数包含在单元格A2到A7中。

31.SUM

功能:计算单元格区域中所有数值的和。

语法:=SUM(单元格1:单元格2)

例:=SUM(A2:A10) 将单元格A2:10中的值加在一起。

32.SUMIF

功能:求满足条件的单元格和。

语法:=SUMIF(range,criteria, [sum_range])

例:=SUMIF(A2:A7,"水果",C2:C7) “水果”类别下所有食物的销售额之和。

32.SUMIFS

功能:对一组满足条件指定的单元格求和。

语法:SUMIFS(sum_range,criteria_range1, criteria1, [criteria_range2, criteria2], ...)

例:=SUMIFS(A2:A9, B2:B9, "=香*", C2:C9, "卢宁") 计算以“香”开头并由“卢宁”售出的产品的总量。

33.SUMPRODUCT

功能:返回相应的数组或区域乘积的和。

语法:=SUMPRODUCT (array1, [array2], [array3], ...)

例:=SUMPRODUCT(Table1!A1:Table1!A100,Table2!B1:Table2!B100) 计算表格1的A1到A100与表格2的B1到B100的乘积和,即A1*B1 A2*B2 A3*B3 …

34.STDEV

功能:基于样本估算标准偏差。

语法:STDEV(number1,[number2],...)

例:=STDEV(D2:D17) 列的标准偏差

35.SUBSTOTAL

功能:返回列表或数据库中的分类汇总。

语法:=SUBTOTAL(function_num,ref1,[ref2],...)

例:=SUBTOTAL(9,A2:A5)使用9作为第一个参数,算出的单元格A2:A5中分类汇总的值之和。

36.INT/ROUND

功能:ROUND 函数将数字四舍五入到指定的位数。

语法:=ROUND(A1, 2)

例:=ROUND(2.15, 1)将2.15四舍五入到一个小数位

功能:INT将数字向下舍入到最接近的整数。

语法:=INT(8.9) 将 8.9 向下舍入到最接近的整数。

5

时间序列类

专门用于处理时间格式以及转换。

37.TODAY

功能:返回当前日期的序列号。

语法:TODAY()

li'z=TODAY() 5返回当前日期加5天。例如,如果当前日期为1/1/2012,此公式会返回1/6/2012。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(16)

38.NOW

功能:返回当前日期和时间的序列号。

语法:Now()

=NOW() 7 返回7天后的日期和时间。

excel常用函数大全讲解(关于Excel函数看这一篇就够了)(17)

39.YEAR

功能:返回对应于某个日期的年份。

语法:=YEAR(serial_number)

=YEAR(A3) 单元格A3中日期的年份

40.MONTH

功能:返回日期中的月份。

语法:MONTH(serial_number)

=MONTH(A2) 单元格A2中日期的月份

41.DAY

功能:返回以序列数表示的某日期的天数。

语法:DAY(serial_number)

=DAY(A2) 单元格A2中日期的天数

42.WEEKDAY

功能:返回对应于某个日期的一周中的第几天。默认情况下,天数是1(星期日)到7(星期六)范围内的整数。

语法:=WEEKDAY(serial_number,[return_type])

=WEEKDAY(A2) 1(星期日)到7(星期六)一周中的第几天

=WEEKDAY(A2, 2) 1(星期一)到7(星期日)一周中的第几天。

43.DATEDIF

功能:计算两个日期之间相隔的天数、月数或年数。

语法:=DATEDIF(start_date,end_date,unit)

=DATEDIF(Start_date,End_date,"Y")一段时期内的年数

=DATEDIF(Start_date,End_date,"D")一段时期内的天数

=DATEDIF(Start_date,End_date,"YD")忽略日期中的年份,一段时期内的天数

以上,就是今天要讲的Excel函数全部内容,希望能够帮到大家!

整理不易,点赞、转发帮忙点一下!

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页