数据分析-excel基础篇

清洗处理类

主要针对文本、格式以及脏数据的清洗和转换。

Trim

用于清除掉字符串两边的空格。

MySQL有同名函数，Python有近似函数strip。

=TRIM(目标单元格)

Concatenate

合并单元格中的内容，还有另一种合并方式是& 。”我”&”很”&”帅” ＝我很帅。当需要合并的内容过多时，concatenate可提高单元格合并的效率。

MySQL有近似函数concat。

=Concatenate(单元格1，单元格2……)

Replace

替换掉单元格的字符串，清洗使用较多。

MySQL中有同名函数，Python中有同名函数。

=Replace（指定字符串，哪个位置开始替换，替换几个字符，替换成什么）

Substitute　

和replace接近，区别是替换为全局替换，没有起始位置的概念。

=Substitute(要替换的文本,旧文本,新文本,[替换第几个])

=Substitute(要替换的文本,旧文本,新文本,[替换第几个])

Left／Right／Mid

截取字符串中的字符。Left/Right（指定字符串，截取长度）。left为从左，right为从右，mid如上文示意。

MySQL中有同名函数。

=Mid(指定字符串，开始位置，截取长度)                         #MId默认向右截取

Len／Lenb

返回字符串的长度。

在len中，中文计算为一个，在lenb中，中文计算为两个。

MySQL中有同名函数，Python中有同名函数

=Len("我")                    >>> 1
=LenB(“我”)                   >>> 2

Find

查找某字符串出现的位置，可以指定为第几次出现，与Left／Right／Mid结合能完成简单的文本提取
MySQL中有近似函数 find_in_set，Python中有同名函数

语法：

=Find(find_text,within_text,start_num)　　　　　　

Find_text 是要查找的字符串;
Within_text 是包含要查找关键字的单元格;
Start_num 指定开始进行查找的字符数。

注意事项：

若find_text为空文本，则Find函数返回1；
若within_text无find_text或start_num不大于0或start_num大于within_text长度，则Find函数返回错误值#VALUE!；
find_text中不能包含通配符（*或？）。
应用：
　　
A2=“广东省东莞市东城区…”　　　=FIND("省",A2)【默认start_num为1】　　　　>>>3
A2=“广东省东莞市东城区…”　　　=FIND("东",A2,3)　　　　　　　　　　　　　  >>>4，start_num是3会跳过前3个字符，因此返回值为4

Search

和Find类似，区别是：Find区分大小写，Search不区分大小写且支持＊通配符，用法与Find函数类似。

Text

将数值转化为指定的文本格式，可以和时间序列函数一起看

语法：

=Text(值，数值格式)

应用：

销售人员　　　　业绩
小李　　　　　　2000　　　　　=A2&"本月业绩为："&Text(B2,"$0.00")　　　>>>小李本月业绩为：2000美元

关联匹配类

Lookup

返回向量或数组中的数值。

函数 LOOKUP 的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；

语法：

= LOOKUP(lookup_value,lookup_vector,result_vector)
　　lookup_value—函数LOOKUP在第一个向量中所要查找的数值，它可以为数字、文本、逻辑值或包含数值的名称或引用；
　　lookup_vector—只包含一行或一列的区域l，可以为文本、数字或逻辑值；
　　result_vector—只包含一行或一列的区域，其大小与 lookup_vector 相同。

=Lookup（查找的值，值所在的位置，返回相应位置的值）

函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。

语法：

= LOOKUP(lookup_value,array)
 array—包含文本、数字或逻辑值的单元格区域或数组，它的值用于与 lookup_value 进行比较

Vlookup

功能是按列查找，最终返回该列所需查询列序所对应的值。

=Vlookup(要查找的值,要查找的区域,返回数据在查找区域的第几列数,匹配方式)

Index

返回表或区域中的值或对值的引用。

数组形式：

语法：

=Index(array,row_num,column_num)　　　　　　　　　　　　　　　　#Array是一个单元格区域或数组常量
=Index（查找的区域，区域内第几行，区域内第几列）

引用形式：

语法：

=Index(reference,row_num,column_num,area_num)   　　 　　　　　　
　　Reference是对一个或多个单元格区域的引用；
　　Area_num用于选择要从中返回 行和列的交叉点的引用区域。

Match

MATCH函数用途：

1.确定列表中某个值的位置；

2.对某个输入值进行检验，确定这个值是否存在某个列表中；

3.判断某列表中是否存在重复数据；

4.定位某一列表中最后一个非空单元格的位置。

注意事项：MATCH函数不区分大小写。

语法：

=Match(lookup_value, lookup_array, match_type)

　　lookup_value：需要在数据表（lookup_array）中查找的值
　　lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据，引用的查找区域是一维数组。
　　match_type：表示查询的指定方式，用数字-1、0或者1表示
　　　　　　　　  
　　　　　　　　　为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列
　　　　　　　　　为0时，查找等于lookup_value的第一个数值，lookup_array按任意顺序排列
　　　　　　　　　为-1时，查找大于或等于lookup_value的最小数值在lookup_array中的位置，lookup_array必须按降序排列


=Match(需要查找的字符,被查找的区域，匹配类型)

Row

返回单元格所在的行

Column

返回单元格所在的列

Offset

OFFSET函数的功能为以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。

语法：

＝Offset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）

应用：（空白工作表里进行）

公式　　　　　　　　　　　　　　　　说明（结果）

=Offset(C3,2,3,1,1)　　　　　　    　　显示F5单元格中的值（0）
=Sum(Offset(C3:E5,-1,0,3,3))　　  　　对数据区域C2:E4，求和（0）
=Offset(C3:E5,0,-3,3,3)　　　　　 　　返回错误值#REF!，因为引用区域不在工作表中

逻辑运算类

逻辑运算返回的均是布尔类型，True和False。

IF

根据逻辑计算返回相应的值

MySQL中有同名函数，Python中有同名函数

语法：

=If(logical_test,value_if_true,value_if_false)

And

全部参数为True，则返回True，经常用于多条件判断。

MySQL中有同名函数，Python中有同名函数。

语法：

=And(logical1,logical2, ...)

语法：

=And(logical1,logical2, ...)

Or

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

MySQL中有同名函数，Python中有同名函数。

语法：

=Or(logical1,logical2, ...)

IS系列

常用判断检验，返回的都是布尔数值True和False。常用ISERR，ISERROR，ISNA，ISTEXT，可以和IF嵌套使用。

常用IS系列函数及功能：

=ISBLANK(value)     　　　　　　　　#ISBLANK 值为空白单元格。
=ISERR(value)      　　 　　　　　　#ISERR 值为任意错误值（除去 #N/A）。
=ISERROR(value)     　　　　　　　　#ISERROR 值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。
=ISLOGICAL(value)   　　　　　　　  #ISLOGICAL 值为逻辑值。
=ISNA(value)        　　　　　　　　#ISNA 值为错误值 #N/A（值不存在）。
=ISNONTEXT(value)   　　　　　　　　#ISNONTEXT 值为不是文本的任意项（注意此函数在值为空白单元格时返回 TRUE）。
=ISNUMBER(value)    　　　　　　　  #ISNUMBER 值为数字。
=ISREF(value)       　　　　　　    #ISREF 值为引用。
=ISTEXT(value)      　　　　　　　  #ISTEXT 值为文本。

计算统计类

常用的基础计算、分析、统计函数，以描述性统计为准。

Sum／Sumif／Sumifs

统计满足条件的单元格总和，SQL有中同名函数。

MySQL中有同名函数，Python中有同名函数。

SUM函数语法：

=SUM(number1,number2, ...)

SUMIF函数语法：

=SUMIF(range，criteria，sum_range)
SUMIF 函数可以对报表范围中符合指定条件的值求和

SUMIFS函数语法：  

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
该函数可快速对多条件单元格求和
　　criteria_range1为计算关联条件的第一个区域
　　criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本，可用来定义将对criteria_range1参数中的哪些单元格求和
　　criteria_range2为计算关联条件的第二个区域
　　criteria2为条件2。和 3） 均成对出现。最多允许127个区域、条件对，即参数总数不超255个
　　sum_range 是需要求和的实际单元格

Count／Countif／Countifs

统计满足条件的字符串个数

MySQL中有同名函数，Python中有同名函数。

Count函数语法：

=Count(value1,value2, ...)
将把数值型的数字计算进去；但是错误值、空值、逻辑值、文字则被忽略

Countif函数语法：

=Countif（range，criteria）
    参数：range 要计算其中非空单元格数目的区域
    参数：criteria 以数字、表达式或文本形式定义的条件

对指定区域中符合指定条件的单元格计数的一个函数

Countif函数用法：

1.求各种类型单元格的个数
　　(1) 求真空单元格个数:=COUNTIF(数据区,"=")
　　(2) 非真空单元格个数: =COUNTIF(数据区,"<>") 相当于counta()函数
　　(3) 文本型单元格个数: =COUNTIF(数据区,"*") 假空单元格也是文本型单元格
　　(4) 区域内所有单元格个数: =COUNTIF(数据区,"<>"""),如果数据区内有",该公式不成立.
　　(5) 逻辑值为TRUE的单元格数量 =COUNTIF(数据区,TRUE)

2.求大于或小于某个值的单元格个数
　　(1) 大于50=COUNTIF(数据区,">50")
　　(2) 等于50=COUNTIF(数据区,50)
　　(3) 小于50 =COUNTIF(数据区,"<50")
　　(4) 大于或等于50 =COUNTIF(数据区,">=50")
　　(5) 小于或等于50 =COUNTIF(数据区,"<=50")
　　(6) 大于E5单元格的值 =COUNTIF(数据区,">"&$E$5)
　　(7) 等于E5单元格的值 =COUNTIF(数据区,$E$5)
　　(8) 小于E5单元格的值 =COUNTIF(数据区,"<"&$E$5)
　　(9) 大于或等于E5单元格的值 =COUNTIF(数据区,">="&$E$5)
　　(10) 小于或等于E5单元格的值 =COUNTIF(数据区,"<="&$E$5)

3.等于或包含某N个特定字符的单元格个数
　　(1) 两个字符 =COUNTIF(数据区,"??")
　　(2) 两个字符并且第2个是B=COUNTIF(数据区,"?B")
　　(3) 包含B =COUNTIF(数据区,"*B*")
　　(4) 第2个字符是B =COUNTIF(数据区,"?B*")
　　(5) 等于“你好”=COUNTIF(数据区,"你好")
　　(6) 包含D3单元格的内容 =COUNTIF(数据区,"*"&D3&"*")
　　(7) 第2字是D3单元格的内容 =COUNTIF(数据区,"?"&D3&"*")
　　

　　注：countif()函数对英文字母不区分大小写，通配符只对文本有效

4.两个条件求个数
　　(1)>10并且<=15 =SUM(COUNTIF(数据区,">"&{10,15})*{1,-1})
　　(2)>=10并且<15 =SUM(COUNTIF(数据区,">="&{10,15})*{1,-1})
　　(3)>=10并且<=15 =SUM(COUNTIF(数据区,{">=10",">15"})*{1,-1})
　　(4)>10并且<15 =SUM(COUNTIF(数据区,{">10",">=15"})*{1,-1})或者=SUM(COUNTIF(数据区,{">10","<15"}))-样本数


　　补充：三个区域计数： 三个区域中>=60 =SUM(COUNTIF(INDIRECT({"a46:a48","b48:b50","c47:c48"}),">=60"))
　　补充：（集合运算法）
　　统计范围，比如5<= x <=7
　　可以分解为(x>=5)-(x>7)
　　写为函数：
　　=countif(range,">=5")-countif(range,">7")

Countifs函数语法：

=Countifs(criteria_range1,criteria1,criteria_range2,criteria2,…)
　　参数说明
　　criteria_range1：为第一个需要计算其中满足某个条件的单元格数目的单元格区域（简称条件区域），
　　criteria1为第一个区域中将被计算在内的条件（简称条件），其形式可以为数字、表达式或文本
　　用来计算多个区域中满足给定条件的单元格的个数，可以同时设定多个条件

Max

返回数组或引用区域的最大值

MySQL中有同名函数，Python中有同名函数。

Min

返回数组或引用区域的最小值

MySQL中有同名函数，Python中有同名函数。

Rank

排序，返回指定值在引用区域的排名，重复值同一排名。

SQL中有近似函数row_number() 。

语法:

=rank(number,ref,[order])
　　number 为需要求排名的那个数值或者单元格名称（单元格内必须为数字）;
　　ref 为排名的参照数值区域;
　　order的为0和1，默认不用输入，得到的就是从大到小的排名，若是想求倒数第几，order的值请使用1。

用法：

假设：A列从A1单元格开始，数据依次为：80、98、65、79、65

B1中编辑公式：=RANK(A1,$A$1:$A$5,0)
回车确认，下拉至B5单元格
效果：
从B1单元格起依次返回值为2、1、4、3、4

Rand／Randbetween

常用随机抽样，前者返回0~1之间的随机值，后者可以指定范围。

MySQL中有同名函数。

Averagea

求平均值，也有Averageaif，Averageaifs

MySQL中有同名函数，python有近似函数mean。

Quartile

计算四分位数，比如1~100的数字中，25分位就是按从小到大排列，在25%位置的数字，即25。参数0代表最小值，参数4代表最大值，1~3对应25、50（中位数）、75分位

Stdev

求标准差，统计型函数

Substotal

汇总型函数，将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化。

Int／Round

取整函数，int向下取整，round按小数位取数。

round(3.1415,2) =3.14 ;

round(3.1415,1)=3.1

时间序列类

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

Year

返回日期中的年

MySQL中有同名函数。

Month

返回日期中的月

MySQL中有同名函数。

Weekday

返回指定时间为一周中的第几天，参数为1代表从星期日开始算作第一天，参数为2代表从星期一开始算作第一天（中西方差异）。我们中国用2为参数即可。

MySQL中有同名函数。

Weeknum

返回一年中的第几个星期，后面的参数类同weekday，意思是从周日算还是周一。

MySQL中有近似函数 week。

Day

返回日期中的日（第几号）

MySQL中有同名函数。

Date

时间转换函数，等于将year()，month()，day()合并

MySQL中有近似函数 date_format。

Now

返回当前时间戳，动态函数

MySQL中有同名函数。

Today

返回今天的日期，动态函数

MySQL中有同名函数。

Datedif

日期计算函数，计算两日期的差。参数决定返回的是年还是月等。

MySQL中有近似函数 DateDiff。

语法：

=DATEDIF(start_date,end_date,unit)
    Start_date 为一个日期，它代表时间段内的第一个日期或起始日期。(起始日期必须在1900年之后)
    End_date 为一个日期，它代表时间段内的最后一个日期或结束日期。
    Unit 为所需信息的返回类型。
    
    Unit取值：

    "Y" 时间段中的整年数。
    "M" 时间段中的整月数。
    "D" 时间段中的天数。
    "MD" 起始日期与结束日期的同月间隔天数。 忽略日期中的月份和年份。
    "YD" 起始日期与结束日期的同年间隔天数。忽略日期中的年份。
    "YM" 起始日期与结束日期的间隔月数。忽略日期中年份

用法：

=DATEDIF(A1,TODAY(),"Y")计算年数差
=DATEDIF(A1,TODAY(),"M")计算月数差
=DATEDIF(A1,TODAY(),"D")计算天数差

posted @ 2017-12-11 01:07 Nice1949 阅读(665) 评论(0) 编辑收藏举报

刷新页面返回顶部

Nice1949

数据分析-excel基础篇

清洗处理类

Trim

Concatenate

Replace

Substitute

Left／Right／Mid

Len／Lenb

Find

Search

Text

关联匹配类

Lookup

Vlookup

Index

Match

Row

Column

Offset

逻辑运算类

IF

And

Or

IS系列

计算统计类

Sum／Sumif／Sumifs

Count／Countif／Countifs

Max

Min

Rank

Rand／Randbetween

Averagea

Quartile

Stdev

Substotal

Int／Round

时间序列类

Year

Month

Weekday

Weeknum

Day

Date

Now

Today

Datedif

公告

Substitute　