Excel在数据分析和日常工作的运用

数据大师
数据大师
数据大师
173
文章
0
评论
2021-06-2217:46:32 评论 44 4995字
摘要

Excel作为日常使用率较高的工具,不仅提供了强大的数据处理函数,同时也提供了编程功能,实现定制化的数据处理能力,下面将简要介绍excel在我们日常工作中涉及到的数据处理功能,欢迎大家补充;我常用的处理方法是遇到问题才去查询,但是这样效率会比慢,不如平时积累一些。

excel的函数使用非常的多,如下将总结和介绍我们常用的函数介绍,欢迎补充:

1、MIN+IF 组合(重点)

功能:计算指定条件下的最小值

Excel在数据分析和日常工作的运用

说明:先用IF函数判断A列的部门是否等于F3指定的部门,如果条件成立,则返回D列对应的分数,否则返回逻辑值FALSE;

接下来再使用MIN函数计算出其中的最小值。MIN函数有一个特性,就是可以自动忽略逻辑值,所以只会对数值部分计算,最终得到指定部门的最低分数。

G3 单元格公式:=MIN(IF(A2:A9=F3,D2:D9))

注:执行多项计算,在输入公式时要按 Shift+ctrl+Enter 键

2、IF+AND 组合(重点)

功能:并列多条件判断

例:在C列设置公式,如果A列值小于500且B列值为未到期,则返回"补款",否则显示为空。

Excel在数据分析和日常工作的运用

C4 单元格公式:=IF(AND(A4<500,B2="否"),"补款","")

注:两个条件同时成立用 AND,任一个成立用 OR 函数。

3、INDEX+MATCH 组合

功能:根据条件查询

例:根据月份和费用项目,查找金额。

Excel在数据分析和日常工作的运用

C10 单元格公式:=INDEX(B2:G6,MATCH(B10,$A$2:$A$6,0),MATCH(A10,$B$1:$G$1,0))

说明:先用MATCH函数查找3月在第一行中的位置

=MATCH(B10,$A$2:$A$6,0)

再用MATCH函数查找费用项目在A列的位置

=MATCH(A10,$B$1:$G$1,0)

最后用INDEX根据行数和列数提取数值

=INDEX(区域,行数,列数)

4、VLOOKUP+MATCH 组合(重点)

功能:用于不确定列数的数据查询

例:根据B13单元格的姓名,在数据表中查询对应的项目。

Excel在数据分析和日常工作的运用

C13 单元格公式:=VLOOKUP(B13,A1:E9,MATCH(C12,1:1,),0)

5、IFERROR+VLOOKUP 组合(重点)

功能:当 VLOOKUP 查找不到时屏蔽查错误值

例:根据产品名称在上表中查找单价,如果产品不存在则显示为空白。

Excel在数据分析和日常工作的运用

B9 单元格公式:=IFERROR(VLOOKUP(A8,$A$1:$D$5,3,0),"")

6、TEXT+MID 组合(重点)

功能:用于日期字符串的提取和转换

例:根据B列身份证号码提取出生年月。

Excel在数据分析和日常工作的运用

C3 单元格公式为:=TEXT(MID(B2,7,8),"0-00-00")

说明:MID 函数用于从字符串的指定位置开始,提取特定数目的字符串。MID(B2,7,8)就是从 B2 单元格的第 7 位开始,提取 8 位数字,结果为:19881109。再使用 TEXT 函数,将这个字符串变成"0-00-00"的样式,结果为"1988-11-09"。

7、MID+FIND 组合(重点)

功能:根据条件截取字符串

例:在个人信里截取出年龄

Excel在数据分析和日常工作的运用

B2 单元格公式:=MID(A2,FIND(" ",A2)+1,9)

注:find 查找分隔符的位置,MID 负责截取字符

8、LEN+SUBSTITUTE 组合

功能:计算一个单元格内有几个项目

例:计算每个部门的人数。

Excel在数据分析和日常工作的运用

C2 单元格公式:=(LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1)*(B2<>"")

注:加上一个判断B2单元格是否为空格的判定,否则如果为空格就会返回错误的结果1

说明:先用LEN函数计算出 B 列单元格的字符长度,然后再用 SUBSTITUTE 函数将顿号全部替换掉之后,计算替换后的字符长度。用字符长度减去替换后的字符长度,就是单元格内顿号的个数。

9、LEFT+LENB+LEN 组合

功能:分离汉字、数字和字母

Excel在数据分析和日常工作的运用

B5 单元格公式:=LEFT(A2,LENB(A2)-LEN(A2))

注:带B的函数是按字节计数,而一个汉字占2个字节,数字和字母则占1个。所以用 LENB(A2)-LEN(A2) 可以倒推出汉字的个数,然后用 left 或 mid 函数截取。

10、SUMPRODUCT+COUNTIF 组合

功能:计算不重复值个数

例:统计B列的客户数量

Excel在数据分析和日常工作的运用

D2 单元格公式:=SUMPRODUCT(1/COUNTIF(B2:B11,B2:B11))

注:Countif 函数统计出每个客户的出现次数,Sumprodcut 对 1/出现次数进行求和。每个客户无论出现多少次,求和的结果都是1,求和后正好是不重复个数。

11、SUM+OFFSET+COUNT 组合

功能:最后N天求和

例:在D2单元格返回B列最近7天的销量

Excel在数据分析和日常工作的运用

D2 单元格公式:=SUM(OFFSET(B1,COUNTA(B:B)-7,0,7,1))

注:Counta 负责统计 B 列非空值个数,offset 负责生成动态最后 N 天区域,SUM 负责求和

12、INDEX+SMALL+ROW 组合

功能:一对多查找

例:在F列查找"张丽"的个人消费记录

Excel在数据分析和日常工作的运用

Excel在数据分析和日常工作的运用F2 单元格公式:{=INDEX(C:C,SMALL(IF(B$2:B$10=F$1,ROW($2:$10)),ROW(A1)))}

说明:

  • IF(B$2:B$10=F$1,ROW($2:$10)):如果B列的姓名和F1的姓名相同,就返回它的行号。不相同的返回FALSE
  • Row(a1):是返回A1的行号1,如果向下复制会变为 Row(a2),返回2,其实用它的目的是当公式向下复制时可以生成序号:1,2,3...然后取符合条件的第1个行号,第2个行号...
  • SMALL(): 从符合条件的行号中从小到大,逐个提取符合条件的行
  • INDEX() :根据取得的行号从C列提取值
  • { }:数组公式(含有逐一运算的公式)需要按 ctrl+shift+enter 输入大括号(一定要是自动生成的,不能手输入大括号)。

13、MID函数(重点)

函数定义:从一个文本字符串的指定位置开始,截取指定数目的字符

使用格式:MID(text, start_num, num_chars)

Excel在数据分析和日常工作的运用

14、CONCATENATE函数

函数定义:将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中

使用格式:CONCATENATE(text1,text2,……)

说明:也可以用&(和号)运算符代替函数CONCATENATE实现文本项的合并.

Excel在数据分析和日常工作的运用

15、AND函数

函数定义:检测所有的条件是否为真

使用格式:AND(logical1,logical2,……logical30)

注意事项:

(1)如果指定的区域中不包含逻辑值或数值时,函数AND返回错误值#VALUE!.

(2)Logical1,logical2,……logical30表示待检测的1到30个条件值,各条件值可为TRUE或FALSE。

Excel在数据分析和日常工作的运用

可以与IF函数连用:

Excel在数据分析和日常工作的运用

16、IF函数(重点)

函数定义:根据条件满足与否返回不同的值

使用格式:IF(logical_test,value_if_true,value_if_false)

白话:IF(条件,与条件一样时运算这个,与条件不同时运算这个)

Excel在数据分析和日常工作的运用

17、DATEDIF函数(重点)

函数定义:计算期间内的年数、月数、天数

使用格式:DATEDIF(start_date,end_date,"y")、=DATEDIF(start_date,end_date,"m")、=DATEDIF(start_date,end_date,"d")、、=DATEDIF(start_date,end_date,"ym")、=DATEDIF(start_date,end_date,"yd")、=DATEDIF(date1,date2,"md")

白话:DATEDIF(开始日期,结束日期,要计算的单位)

Excel在数据分析和日常工作的运用

说明:

y:计算满年数,返回值为0以上的整数;m:计算满月数,返回值为0以上的整数;d:计算满日数,返回值为0以上的整数;ym:计算不满一年的月数,返回值为1~11之间的整数;yd计算不满一年的天数,返回值为0~365之间的整数;md:计算不满意一个月的天数,返回值为0~30之间的整数.

18、COUNTIF函数

函数定义:计算满足条件的单元格计数

使用格式:COUNTIF(range,criteria)

白话:COUNTIF(要找的内容所在的区域,要找的内容)

注意事项:

指定的条件必须用 " " (双引号括起来),如 ">=100、"男" 等.但,当指定条件为引用单元格时无需双引号括住.通配符使用参看SUMIF函数中的通配符说明.

Excel在数据分析和日常工作的运用

19、SUMIF函数(重点)

函数定义:对满足条件的单元格的数值求和

使用格式:SUMIF(range,criteria,sum_range)

参数解释:

range:为用于条件判断的单元格区域.指定作为搜索对象的单元格区域.

Criteria:为确定哪些单元格将被相加求和的条件,其形式可以为数字、表达式、文本或通配符.

Sum_range:是需要求和的实际单元格

Excel在数据分析和日常工作的运用

(1)以"文胸"开头的任意文本的销量=SUMIF($B$4:$B$14,"文胸*",$C$4:$C$14)

20、DCOUNT函数

函数定义:计算满足条件的数值的个数

使用格式:DCOUNT(database,field,criteria)

参数定义:

database: 构成列表或数据库的单元格区域.数据库是包含一组相关数据的列表,其中包含相关信息的行为记录,而包含数据的列为字段.列表的第一行包含着每一列的标志项.

Field: 指定函数所使用的数据列.列表中的数据列必须在第一行具有标志项.Field可以是文本,即两端带引号的标志项,如"使用年数"或"产量";此外,Field也可以是代表列表中数据列位置的数字:1表示第一列,2表示第二列,等等.

Criteria: 为一组包含给定条件的单元格区域.可以为参数criteria指定任意区域,只要它至少包含一个列标志和列标志下方用于设定条件的单元格.

21、VLOOKUP函数(重点)

函数定义:计算满足条件的数值的个数

函数定义:按照垂直方向搜索区域

使用格式:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

参数定义:

Lookup_value:为需要在数组第一列中查找的数值.Lookup_value可以为数值、引用或文本字符串.

Table_array: 为需要在其中查找数据的数据表.可以使用对区域或区域名称的引用,例如数据库或列表.

Col_index: 为table_array中待返回的匹配值的列序号. Col_index_num为1时,返回table_array第一列中的数值;col_index_num为2,返回table_array第二列中的数值,以此类推.如果col_index_num小于1,函数VLOOKUP返回错误值值#VALUE!;如果col_index_num大于table_array的列数, 函数VLOOKUP返回错误值#REF!.

Range_lookup:为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配.如果为TRUE或省略,则返回近似匹配值.也就是说.如果找不到精确匹配值,则返回小于lookup_value的最大数值;如果range_value为FALSE,函数VLOOKUP将返回精确匹配值.如果找不到,则返回错误值#N/A.

注意:

  1. 如果range_lookup为TRUE,则table_array的第一列中的数值必须按升序排列:…、 -2、-1、0、1、2、…、-Z、FALSE、TRUE;否则,函数VLOOKUP不能返回正确的数值.如果range_lookup为FALSE,table_array不必进行排序.
  2. Table_array的第一列中的数值可以为文本、数字或逻辑值.
  3. 文本不区分大小写.
  4. 如果函数VLOOKUP找不到lookup_value,且range_lookup为TRUE,则使用小于等于lookup_value的最大值.
  5. 如果lookup_value小于table_array第一列中的最小数值,函数VLOOKUP返回错误值#N/A.
  6. 如果函数VLOOKUP找不到lookup_value且range_lookup为FALSE,函数VLOOKUP返回错误值#N/A.
  7. 若有多个符合条件的情况:vlookup返回的是第一个满足条件的值,lookup返回的是最后一个满足条件的值.

 

End.

作者:Bonus_F

转载如果涉及作品问题请联们第一时间删除(微信lovedata0520

更多文章前往首页浏览http://www.itongji.cn/

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: