默认广告
默认广告

Excel表格函数公式大全,43个数据分析常用函数解析

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

vlookup大家应该知道,而Excel 有哪些和 vlookup 一样重要的函数或功能呢?

做为多年的数据分析师,带大家系统的了解下什么是函数,什么是公式,函数与分类,常用函数有哪些,点赞收藏,相信你肯定会用到!

01什么是函数

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

Excel表格函数公式大全,43个数据分析常用函数解析-1

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

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

02什么是公式

什么是Excel的公式?

公式就是由用户自行设计并结合常量数据单元格引用运算符等元素进行数据处理和计算的算式。用户使用公式是为了有目的地计算结果,因此Excel的公式必须(且只能)返回值。

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

公式中的运算符
符号 说明 实例
算数运算符:负号 =8*-5=-40
% 算数运算符:百分号 =60*50%=3
^ 算数运算符:乘幂 =3^2=9
*和/ 算数运算符:乘和除 =3*2/4=1.5
+和- 算数运算符:加和减 =3+2-5=0
=,<>,>,<,>=,<= 比较运算符:等于、不等于、大于、小于、大于等于、小于等于 =(A1=A2)判断A1与A2相等=(B1<>“ABC”) 判断B1不等于“ABC”
文本运算符:连接文本 =“Excel”&“Home”=“ExcelHome”
: 区域运算符:冒号 =SUM(A1:A10)
空格 交叉运算符:单个空格 =SUM(A1:B5 A4:D9)相当于=SUM(A4:B5)
, 联合运算符:逗号 =RANK(A1(A1:A10,C1:C10))

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

运算符优先级
序号 符号
1 : 空格 ,
2
3 %
4 ^
5 *和/
6 +和-
7
8 =,<>,>,<,>=,<=

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

错误值类型 含义
##### 当使用了负的日期或负的时间时,出现错误
#VALUE! 当使用的参数或操作数类型错误时,出现错误
#DIV/O! 当数字被零(0)除时,出现错误
#NAME? 当Excel未识别公式中的文本时,出现错误
#N/A 当数值对函数或公式不可用时,出现错误
#REF! 单元格引用无效时,出现错误
#NUM! 公式或函数中使用无效数字值时,出现错误
#NULL! 当指定并不相交的两个区域的交点时,出现错误

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

03函数与分类

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

函数公式结构:=IF(A1>0,”正数”,IF(A1<0,负数,””))对于函数的参数来说,可以由数值、日期和文本等元素组成,也可以使用常量、数组、单元格引用或其他函数。

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

Excel表格函数公式大全,43个数据分析常用函数解析-2

 

 

04常用函数

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

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

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

Excel表格函数公式大全,43个数据分析常用函数解析-4

1、关联匹配类

需要的数据不在同一个Excel表或同一个Excel表不同sheet中,数据太多copy起来麻烦还容易出错,如何整合呢?下面这些函数就是用于多表关联或者行列比对时的场景,而且表格越复杂,用起来越爽!

01.VLOOKUP

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

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

*备注:[ ]内为可选参数,其余为必需参数,下文同理。=VLOOKUP (要查找的项、要查找位置、区域中包含要返回的值的列号、返回近似匹配或精确匹配 – 指示为 1/TRUE 或 0/FALSE) 。例:查询姓名是F5单元格中的员工是什么职务。

Excel表格函数公式大全,43个数据分析常用函数解析-5

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表格函数公式大全,43个数据分析常用函数解析-6

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列是第四列。

08.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

09.MID

功能:从中间截取字符串

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

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

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

Excel表格函数公式大全,43个数据分析常用函数解析-7

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表格函数公式大全,43个数据分析常用函数解析-8

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表格函数公式大全,43个数据分析常用函数解析-9

23.COUNTIF

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

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

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

Excel表格函数公式大全,43个数据分析常用函数解析-10

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表格函数公式大全,43个数据分析常用函数解析-11

27.MAX

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

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

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

28.AVERAGE

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

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

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

Excel表格函数公式大全,43个数据分析常用函数解析-12

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中分类汇总的值之和。

http://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表格函数公式大全,43个数据分析常用函数解析-13

38.NOW

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

语法:=Now()

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

Excel表格函数公式大全,43个数据分析常用函数解析-14

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”)忽略日期中的年份,一段时期内的天数

附:

函数参数或其他常用单词
Sheet(Worksheet) 工作表
Workbook 工作簿
Cell 单元格
TRUE
FALSE
Logical_test 逻辑判断式
Value
Value if true 如果为真
Value if false 如果为假
Logical 逻辑
Value if error 如果错误
Function num 函数编号
Number 数字
Ref (reference) 涉及的内容
Range 范围
Criteria 标准
Sum range 求和的范围
Digits 数字
Divisor 除数
Lookup 查找
Lookup value 查找的值
Array 数组、区域、阵列
Col index num (Col=Column) 索引的列号
Num (Number) 数字的缩写
Type 类型
Text 文本
Num chars 字符数量

该文章已被以下专题收入

相关商品

【正版授权】Office 365 个人版家庭版 / Office 2019 / Office 2016 Word Excel PPT[出售]

相关专题

Excel教程

Microsoft Excel Microsoft Excel是Microsoft为使用Windows和Apple Macintosh操作系统的电脑编写的一款电子表格软件。直观的界面、出色的计算功能和图表工具,再加上成功的市场营销,使Excel成为最流行的个人计算机数据处理软件。 立刻购买 交流圈子…

Word教程

Microsoft Office Word Microsoft Office Word是微软公司的一个文字处理器应用程序。它最初是由Richard Brodie为了运行DOS的IBM计算机而在1983年编写的。 立刻购买 交流圈子 推荐模板/插件 虚位以待 马上入驻 虚位以待 马上入驻 虚位以待 马…

PPT教程

Microsoft Office PowerPoint Microsoft Office PowerPoint是指微软公司的演示文稿软件。用户可以在投影仪或者计算机上进行演示,也可以将演示文稿打印出来,制作成胶片,以便应用到更广泛的领域中。 立刻购买 交流圈子 推荐模板/插件 虚位以待 马上入驻 虚…

给TA打赏
共{{data.count}}人
人已打赏

【龙萱坤诺】声明:

龙萱坤诺所有资源来源于网络及用户分享或为本站原创,仅限用于学习和研究,如有侵权请邮件联系站长!不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站概不负责!

 

龙萱坤诺欢迎您投稿发表文章,更有龙萱坤诺奖励和额外收入!根据投稿内容质量,可获邀入驻本平台,开启您的站长之路!

 

如有链接无法下载、失效或广告,请到圈子反馈!

© 转载请声明:转载自“龙萱坤诺"

应用程序软件教程

介绍四种免费使用微软Azure进行文字转语音的方法!最自然接近人声的机器配音。

2022-9-13 13:22:29

软件教程

面容 ID 使用技巧:为 iPhone 12 开启解锁触感反馈

2021-5-14 7:19:26

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索