超级干货I 43 Excel函数,数据分析必备!

Excel是我们工作中经常用到的工具。对于数据分析来说,也是最基本的数据处理工具。很多传统行业的数据分析师甚至可以掌握Excel和SQL。

对于初学者来说,有时候没必要急着去学习R语言等专业工具(当然学了是加分项),因为Excel涵盖的函数已经足够多了,有很多统计、分析、可视化的插件,但是我们在处理数据的时候很多函数通常不知道怎么用!

Excel的高级学习主要分为两部分——一是数据分析常用的Excel函数,二是用Excel进行简单完整的分析。

超全干货 I 43个Excel函数,数据分析必备!

本文主要介绍数据分析中常用的43个Excel函数及其用法,实际分析将在下篇文章中讲解。

(本文的内容是一个目录,介绍每个函数是做什么的,哪个函数可以用来解决某个问题等等。具体使用方法可以自己学习。)

关于功能:

Excel的函数其实就是一些复杂的计算公式。这些函数将复杂的计算步骤留给程序来处理。只要按照函数格式输入相关参数,就可以得到结果。比如求一个区域的和(A1:C100),可以直接用SUM(A1:C100)的形式。

并且,对于函数,不用死记硬背,只要知道应该选择什么样的函数,需要什么参数,怎么用就行了!例如,选择一个字段,并使用左/右/中功能& # 8230;…其他细节给万能的百度!

功能分类介绍:

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

01关联的匹配类

经常需要的数据不在同一个Excel表中或者在同一个Excel表的不同表中,数据太多,复制起来比较麻烦,容易出错。如何整合它们?

以下函数用于多表关联或行列比较,表越复杂越好用!

1.纵向查找函数

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

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

(例如,查询的名称是单元格F5中雇员的职位)

超全干货 I 43个Excel函数,数据分析必备!

2.HLOOKUP

函数:搜索表格的首行或值数组中的值,并返回表格或数组中指定行的同一列中的值。

语法:=VLOOKUP(要查找的值,要在其中查找值的区域,包含返回值的区域中的行号,精确匹配或近似匹配-指定为0/FALSE或1/TRUE)。

区别:HLOOKUP和VLOOKUP两个函数都是用来查找表中的数据的,但是HLOOKUP返回的值与要查找的值在同一列,而VLOOKUP返回的值与要查找的值在同一行。

3.指数

函数:返回或引用表格或区域中的值。

语法:= INDEX(单元格区域或数组,要返回值的行和列)

超全干货 I 43个Excel函数,数据分析必备!

4.比赛

函数:用于返回指定区域(一行或一列)中指定内容的位置。

语法:= MATCH(要返回值的单元格区域或数组,搜索区域,搜索方法)

5.等级

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

语法:=RANK(参与排名的数值,排名的数值范围,排名方式-0为降序-1为升序-默认为0)。

6.排

函数:返回单元格所在的行。

7.圆柱

函数:返回单元格所在的列。

8.抵消

函数:从指定的基准位置按行列偏移量返回指定的引用。

语法:=Offset(指定返回的点、行数、列数、行数和列数)

02清洁处理类

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

清除字符串空格:使用Trim/Ltrim/Rtrim。

合并单元格:使用连接

截断字符串:使用左/右/中间

替换单元格中的内容:替换/替代

查找文本在单元格中的位置:查找/搜索

9.附加装饰

作用:清除绳子两边的空格。

10.Ltrim

功能:清除单元格右侧的空间。

11.Rtrim

功能:清除单元格左侧的空间。

12.连锁的

语法:=Concatenate(单元格1,单元格2 …)

还有另一种方法来合并单元格的内容&当需要合并太多内容时,Concatenate更有效。

13.左边的

函数:从左边截取字符串。

语法:=Left(值所在的单元格,截取长度)

14.对吧

函数:从右边截取字符串

语法:= Right(值所在的单元格,截取长度)

15.中间的

函数:从中间截取字符串

语法:= Mid(指定字符串,起始位置,截取长度)

(例:根据身份证号提取年月)

超全干货 I 43个Excel函数,数据分析必备!

16.替换

功能:更换电池串。

语法:=Replace(指定字符串、开始替换的位置、要替换的字符数以及要替换的内容)

17.代替者

和replace类似,不同的是Replace是根据位置实现替换的,需要提供替换后的新文本,位置的个数,以及替换的位置。替换,根据文本内容,需要提供被替换的旧文本和新文本,以及替换哪个旧文本。因此,Replace实现了固定位置的文本替换,Substitute实现了固定位置的文本替换。

(例如:替换一些电话号码)

超全干货 I 43个Excel函数,数据分析必备!

18.发现

功能:查找文本位置

语法:=Find(要查找字符,请指定字符串和字符数)

19.搜索

函数:返回指定字符或文本字符串在字符串中的第一个出现位置,从左向右搜索。

语法:=search(要搜索的字符,字符所在的文本,从哪个字符开始搜索)

区别:Find和Search的功能差不多,都能找到字符的位置。不同的是,find函数是精确的,并且区分大小写;搜索函数模糊搜索,不区分大小写。

20.低输入联网(low-entry networking的缩写)

函数:文本字符串中的字符数

21.Lenb

函数:返回文本中包含的字符数。

(例如:从姓名电话列表中提取姓名)

超全干货 I 43个Excel函数,数据分析必备!

03逻辑运算类

逻辑,顾名思义,直接进入功能而不涉及细节:

22.如果

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

语法:=IF(条件,如果为真则返回值,如果为假则返回值)

超全干货 I 43个Excel函数,数据分析必备!

23.和

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

语法:如果所有参数都为真,则返回True,常用于多条件判断。

24.运筹学

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

语法:只要参数有真值,就返回真,常用于多条件判断。

04计算统计类

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

MIN函数:求某一区域的最小值。

MAX函数:求某一区域的最大值。

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

COUNT函数:计算一个区域中包含数字的单元格的数量。

COUNTIF函数:计算一个区域中满足给定条件的单元格的数量。

COUNTIFS函数:计算由一组给定条件指定的单元格的数量。

SUM函数:计算单元格区域中所有值的总和。

SUMIF函数:对满足条件的单元格求和。

SUMIFS函数:对满足指定条件的一组单元格求和。

SUMPRODUCT函数:返回相应数组或面积乘积的和。

25.部

功能:求一个区域的最小值。

26.最大函数

功能:求某一区域的最大值。

27.平均的

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

28.数数

功能:统计包含数字的单元格的个数。

29.COUNTIF

函数:计算一个区域中满足给定条件的单元格的数量。

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

例如=COUNTIF(表1!A1:表1!C100,“是”)计算表1中A1到C100中值为“是”的单元格的数量。

(例如:统计和制定商店的业务交易数量)

超全干货 I 43个Excel函数,数据分析必备!

30.COUNTIFS

函数:计算由一组给定条件指定的单元格的数量。

语法:=COUNTIFS(第一个条件区域,第一个对应条件,第二个条件区域,第二个对应条件,第n个条件区域,第n个对应条件)

例如:=COUNTIFS(Table1!A1:表1!A100,“是”,表1!C1:第一桌!C100,“否”)计算表1中A1到A100范围内单元格的个数,同时在C范围内取值“是”和“否”。

31.总和

计算单元格区域中所有值的总和。

32.苏米夫

函数:查找符合条件的单元格

语法:=SUMIF(单元格1:单元格2,条件,单元格3:单元格4)

(例:计算一班总成绩)

32.苏米夫斯

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

语法:=SUMIFS(实际求和区域,第一个条件区域,第一个对应求和条件,第二个条件区域,第二个对应求和条件,第n个条件区域,第n个对应求和条件)。

例如=SUMIFS(表1!C1:第一桌!C100,表1!A1:表1!A100,“是”,表1!B1: Table1b100,“否”)计算表1中C1到C100区域的单元格之和,对应的行和列A值为“是”,对应的列B值为“否”。

33.求和乘积

函数:返回相应数组或面积乘积的和。

语法:=SUMPRODUCT(单元格1:单元格2,单元格3:单元格4)

示例:=SUMPRODUCT(Table1!A1:表1!A100,2号桌!B1表2!B10)计算表1中A1到A100和表2中B1到B100的乘积和,即A1*B1+A2*B2+A3*B3+…

34.标准差(standarddeviation)

统计函数,求标准差。

35.亚总数

语法:= Substotal(引用区域,参数)

汇总函数,参数化平均值、计数、最大值和最小值、乘法、标准差、求和、方差等。换句话说,只要你知道这个功能,以上都可以丢弃。

36.整数/整数

舍入函数,int向下舍入,舍入到小数位。

round(3.1415,2)= 3.14;

round(3.1415,1)=3.1

05时间序列类

它专门用于处理时间格式和转换。

37.今天

返回今天的日期,一个动态函数。

38.现在

返回当前时间,一个动态函数。

39.年

函数:返回日期的年份。

40.月

函数:返回日期中的月份。

41.天

函数:返回某个日期的天数,用序列号表示。

42.工作日

函数:返回与某个日期对应的一周中的某一天。默认情况下,天数是1(星期日)到7(星期六)之间的整数。

语法:=Weekday(指定时间,参数)

43.Datedif

函数:计算两个日期之间的天数、月数或年数。

语法:=Datedif(开始日期,结束日期,参数)

以上是我经常整理和学习的Excel函数。希望能帮到你!