大家整理了,数据分析入门常用的EXCEL在数据分析行业,EXCEL是最基础的、入门级的,也是最常用,最容易上手的工具了。想要学习数据分析的小伙伴可以选择先从EXCEL入手,下面小编就给函数,希望对各位小伙伴有所帮助。
Excel函数分类:关联匹配类、清洗处理类、逻辑运算类、计算统计类、时间序列类
本篇先对关联匹配类和清洗处理类函数进行解说,其余三个在接下来的文章中继续分享。
关联匹配类
在数据分析中,数据的查找、对比等非常常见,这就需要用到关联匹配类函数,本文将介绍Excel数据分析中常用的关联匹配类函数,如:VLOOKUP、HLOOKUP、INDEX、MATCH、RANK、Row、Column、Offset
1. VLOOKUP
功能:用于查找首列满足条件的元素
语法:=VLOOKUP(要查找的值,要在其中查找值的区域,区域中包含返回值的列号,精确匹配(0)或近似匹配(1) )
(1) 单表查找
在G8中VLOOKUP(F8,$B$2:$6$6,2,0),这样把选手Tian的战队找到之后,接下来自动填充G9和G10单元格的公式。
(2) 跨多工作表查找
如下一个工资表格文件,里面包含各个部门有一张表,有4个部门对应的部门工资表和一个需要查询工资的查询表。
要求从销售~人事4个工作表中查询某个员工的基本工资。
假设A1是销售部的,那么公式可以写为:
=VLOOKUP(A2,销售!A:C,3,0)
如果A1可能在销售或财务表这2个表中,公式可以写为:
=IFERROR(VLOOKUP(A2,销售!A:C,3,0),VLOOKUP(A2,财务!A:C,3,0))
公式说明:如果在销售表中查找不到(用IFERROR函数判断),则去财务表中再查找。
如果A1可能在销售、财务或服务表中,公式可以再次改为:
=IFERROR(VLOOKUP(A2,销售!A:C,3,0),IFERROR(VLOOKUP(A2,财务!A:C,3,0),VLOOKUP(A2,服务!A:C,3,0)))
如果,有更多的表,如本例中4个表,那就一层层的套用下去,如果4个表都查不到就设置为"无此人信息":
=IFERROR(VLOOKUP(A2,销售!A:C,3,0),IFERROR(VLOOKUP(A2,财务!A:C,3,0),IFERROR(VLOOKUP(A2,服务!A:C,3,0),IFERROR(VLOOKUP(A2,人事!A:C,3,0),"无此人信息"))))
2. HLOOKUP
当查找的值位于查找范围的首行,并且返回的值在查找范围的第几行,可以使用hlookup函数。
hlookup函数vlookup函数查找的方式
hlookup函数——按行查找。
vlookup函数——按列查找。
语法:=HLOOKUP(要查找的值,查找的范围,返回的值在查找范围的第几行,精确匹配(0)或近似匹配(1) )
区别:HLOOKUP按行查找,返回的值与需要查找的值在同一列上,VLOOKUP按列查找,返回的值与需要查找的值在同一行上。
可以看出,hlookup函数是横向查找,按行查找。
当查找的值(高数)位于查找范围(A1:E6)的首行,返回的值在查找范围(A1:E6)的第几行,这第几行由姓名所在行决定,例如张曹在第二行,可利用公式=row(G2)实现。
输入公式=HLOOKUP($H$1,$A$1:$E$6,ROW(G2),FALSE)。
公式解释
在数据表第一行中查找的值——H1高数。
查找的范围——A1:E6。
返回的值在查找范围的第几行——使用row函数确。
模糊匹配/精确匹配——精确匹配。
3. INDEX
index+match是一个比较经典的组合,类似vlookup,但是比vlookup更加灵活,比如数据列进行剪切移位时,不受影响,还能向前查找列内容。
使用方法为 =INDEX(明细!A:A,MATCH(A2,明细!B:B,0))。
功能:返回表格或区域中的值
语法:= INDEX(要返回值的单元格区域或数组,所在行,所在列)
4. MATCH
含义:返回目标值在查找区域中的位置。
语法:
=match(lookup_value, lookup_array, match_type)
=Match(目标值,查找区域,0/1/-1)
5. RANK
含义:求某一个数值在某一区域内一组数值中的排名。
语法:
=rank(number,ref,[order])
number ——参与排名的数值
ref ——排名的数值区域
order——有1和0两种。0——从大到小排名(降序),1——从小到大排名(升序)。
0默认不用输入,得到的就是从大到小的排名。
6. Row
含义:返回所选择的某一个单元格的行数。
语法:
=row(reference)
如果省略reference,则默认返回row函数所在单元格的行数。
7. Column
功能:返回单元格所在的列
语法:
=column(reference)
如果省略reference,则默认返回函数column所在单元格的列数。
8. Offset
功能:以指定的(单元格或相连单元格区域的引用)为参照系,通过给定偏移量得到新的引用。返回的引用可以是一个单元格也可以是一个区域(可以指定行列数)。
语法:
=offsetreference,rows,cols,height,width)
=Offset(参照单元格,行偏移量,列偏移量,返回几行,返回几列)
清洗处理类
数据处理之前,需要对提取的数据进行初步清洗,如清除字符串空格,合并单元格、替换、截取字符串、查找字符串出现的位置等,以下将做简要介绍:
清除字符串空格:使用Trim/Ltrim/Rtrim
合并单元格:使用concatenate
截取字符串:使用Left/Right/Mid
替换单元格中内容:Replace/Substitute
查找文本在单元格中的位置:Find/Search
1. Trim
语法:TRIM(text)
说明:除了单词之间的单个空格外,清除文本中所有的 7 位 ASCII 空格字符(值 32)。
TRIM 函数语法具有下列参数 :
Text 必需。需要删除其中空格的文本。
2. concatenate
含义:把多个字符文本或数值连接在一起,实现合并的功能。
语法:
=concatenate(text1, [text2], ...)
Text1,Text2可以是文本或者数值
最多为 255 项,但是项与项之间必须用逗号隔开。
合并单元格中的内容,还有另一种合并方式是&,需要合并的内容过多时,concatenate效率更快。
3. Left
含义:用来对单元格内容进行截取。从左边第一个字符开始截取,截取指定的长度。
语法:
=left(text,num_chars)
text代表用来截取的单元格内容。
num_chars代表从左开始截取的字符数。
4. Right
功能:从字符串右边截取n个字符
语法:
=Right(string, length)
=right(字符串,截取个数)
5. Mid
功能:可以从从任意位置截取字符串。
语法:=mid(字符串,开始截取位置,截取字符个数
Text函数表示将数值转化为自己想要的文本格式,语法:
=TEXT(value,format_text)
6. Replace
功能:替换掉单元格的字符串
语法:=Replace(指定字符串,哪个位置开始替换,替换几个字符,替换成什么)
7. Substitute
和replace接近,不同在于Replace根据位置实现替换,需要提供从第几位开始替换,替换几位,替换后的新的文本。
而Substitute根据文本内容替换,需要提供替换的旧文本和新文本,以及替换第几个旧文本等。因此Replace实现固定位置的文本替换,Substitute实现固定文本替换。
8. Find
功能:查找文本位置
语法:=Find(要查找字符,指定字符串,从第几个字符开始查起)
9. Search
功能:返回一个指定字符或文本字符串在字符串中第一次出现的位置,从左到右查找
语法:=search(要查找的字符,字符所在的文本,从第几个字符开始查找)
Find和Search这两个函数功能几乎相同,实现查找字符所在的位置,区别在于Find函数精确查找,区分大小写;Search函数模糊查找,不区分大小写。
10. Len
功能:返回字符串的字符数
语法:=LEN(字符串)
字符串是指包含数字、字母、符号等的一串字符。
11. Lenb
功能:返回字符串的字节数
区别在于,len是按字符数计算的、lenb是按字节数计算的。数字、字母、英文、标点符号(半角状态下输入的哦)都是按1计算的,汉字、全角状态下的标点符号,每个字符按2计算。
综合应用:
筛选内容:IF+OR+COUNTIF
=IF(OR(COUNTIF(A1,"*"&{"Python","java"}&"*")),A1,"0")
如果含有字段Python或java中的任何一个则为本身,否则为"0",* 代表任意内容,之后就可以通过Excel的筛选功能,把B列的"0"筛选掉。
12. VALUE
功能:将所选区域转为数值类型
13.TEXT
功能:将所选区域转为文本类型
联系客服