前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >数据分析常用的Excel函数

数据分析常用的Excel函数

作者头像
活用数据
发布2019-06-03 17:04:47
4.1K0
发布2019-06-03 17:04:47
举报
文章被收录于专栏:数据医生专栏

参考资料: 七周成为数据分析师 知乎 | 怎样快速掌握 VLookup? 【训练营】职场Excel零基础入门

Excel常用函数

简介

什么是函数

可以把函数理解为一个可以控制的黑箱子,输入X到黑箱子中,他就会输出Y,参数就是黑箱子的控制开关,打到不同的档位,黑箱子会输出不同的Y。

函数示意图

常见函数分类

  • 文本清洗函数
  • 关联匹配函数
  • 逻辑运算函数
  • 计算统计函数
  • 时间序列函数

文本清洗函数

常用的文本清洗函数

  • 清除字符串空格:TRIM
  • 合并单元格:CONCATENATE
  • 截取字符串:LEFT / RIGHT / MID
  • 替换单元格中的内容:REPLACE / SUBSTITUTE
  • 查找文本在单元格中的位置:FIND / SEARCH

清除字符串空格

TRIM

清除字符串text左右的空格。

代码语言:javascript
复制
=TRIM(text)

清除A1单元格左右的空格

合并单元格

CONCATENATE

将几个文本字符串合并为一个文本字符串。

代码语言:javascript
复制
=CONCATENATE(text1, text2, ...)

合并字符串以及单元格内容

截取字符串

LEFT:从text中,提取num_chars个字符(从左开始)。

代码语言:javascript
复制
=LEFT(text, num_cahrs)

RIGHT:从text中,提取num_chars个字符(从右开始)。

代码语言:javascript
复制
=RIGHT(text, num_chars)

MID:从text中,从stat_num开始,提取num_chars个字符串。

代码语言:javascript
复制
=MID(text, start_num, num_chars)

例子

在A2中从左开始提取2个字符

在A1中从右开始提取2个字符

在A1中,从位置3开始,提取2个字符

替换单元格中内容

替换指定位置:REPLACE

从“原字符串”的“开始位置”开始,选择“字符个数”个,替换为“新字符串”

代码语言:javascript
复制
=REPLACE(原字符串, 开始位置, 字符个数, 新字符串)

例子

从A1的位置1开始,选取4个字符串,替换为新的字符串“2018”。

将2019替换为2018

替换指定文本:SUBSTITUTE

在text中用new_text替换old_text,instance_num指定要替换第几次出现的old_text,如果不指定则替换old_text。

代码语言:javascript
复制
=SUBSTITUTE(text, old_text, new_text, instance_num) 

例子

用“k”替换A1中第二次出现的“应届”

查找文本在单元格中的位置

FIND & SEARCH

从within_test中查找FIND_text,返回查找字符的起始位置编号。

代码语言:javascript
复制
=FIND(FIND_text, within_text, start_num)
=SEARCH(要查找字符, 字符所在的文本, 从第几个字符开始查找)

FINDSEARCH 两个函数几乎相同,区别在于FIND 精确查找,区分大小写; SEARCH 模糊查找,不区分大小写。

例子

从A1中查找k,并返回第一个k的起始位置编号

关联匹配函数

VLOOKUP

VLOOKUP 函数简介

VLOOKUP 函数总共有4个参数,分别是:用谁去找、匹配对象范围、返回第几列、匹配方式(0/FALSE表示精确匹配,1/TRUE表示模糊匹配)。

vlookup简介

四种查询方式

1.单条件查找

根据工号,将左边检索区域的“电脑销售额”匹配到右边对应位置,只需要使用VLOOKUP函数,结果存在则显示对应的“电脑销售额”;结果不存在则显示#N/A

代码语言:javascript
复制
=VLOOKUP(F2,$A$2:$D$55,4,0)

单条件查询

注意:检索关键字必须在检索区域的第1列,也就是说如果是根据“姓名”检索,那么检索区域应该从B列开始。

2.反向查找

当检索关键字不在检索区域的第1列,可以使用虚拟数组公式IF来做一个调换。

代码语言:javascript
复制
=VLOOKUP(G2,IF({1,0},B2:B8,A2:A8),2,0)

反向查找

反向查找的固定公式用法:

代码语言:javascript
复制
=VLOOKUP(检索关键字,IF({1,0},检索关键字所在列,查找值所在列),2,0)

注意:其实反向查找除了检索区域改成一个虚拟数组公式IF之外,其他和单条件查找没有区别。

3.多条件查询

在匹配数据时,往往条件不是单一的,那么就可以利用&将字段拼接起来,并且利用IF数组公式构建出一个虚拟的区域。

代码语言:javascript
复制
=VLOOKUP(F2&G2,IF({1,0},A2:A53&B2:B53,D2:D53),2,0)

多条件查找

注意事项,所有使用了数组的公式,不能直接回车,需要使用Ctrl+Shift+Enter,否则会出错。

4.查询返回多列

查找返回多列需要用到另外一个辅助函数——COLUMN函数。

返回结果为单元格引用的列数。 例如:column(B1)返回值为2,因为B1为第2列。

代码语言:javascript
复制
=COLUMN(待查询单元格/区域)

需要注意的是第三个参数“返回第几列”的写法。

代码语言:javascript
复制
=VLOOKUP($G2,$A$2:$E$55,COLUMN(D1),0)

多条件查找

返回多列的固定公式用法:

代码语言:javascript
复制
=VLOOKUP(混合引用关键字,查找范围,COLUMN(xx),0)

返回第几列就用COLUMN函数引用第几列的单元格即可。

HLOOKUP

代码语言:javascript
复制
=HLOOKUP(用谁去找, 匹配对象范围, 返回第几行, 匹配方式)

VLOOKUP的区别:HLOOKUP返回的值与查找的值在同一列上,而VLOOKUP返回的值与查找的值在同一行上。

INDEX

返回数组array中指定索引的单元格的值。

代码语言:javascript
复制
=INDEX(array, Row_num, Column_num)

返回指定区域第2行第2列的单元格内容

MATCH

功能:在区域内查找指定的值,返回第一个查找值的位置。 lookup_value:需要查找的值; lookup_array:查找的区域; match_type:-1、0或1,0表示查找等于lookup_value的值。

代码语言:javascript
复制
=MATCH(lookup_value, lookup_array, [match_type])

查找A1到A4中6的位置

Index & Match联合使用 = VLookup

ROW & COLUMN

ROW:返回指定引用的行号; COLUMN:返回指定引用的列号。

代码语言:javascript
复制
=ROW(reference)
=COLUMN(reference)

例子

求C列为第几列

OFFSET

OFFSET:以指定的引用reference为起点,按照偏移量偏移之后,返回值。 rows:向下偏移多少行; columns:向右偏移多少列; height:返回多少行; width:返回多少列。

代码语言:javascript
复制
=OFFSET(reference, rows, columns, height, width)

例子

A1向下偏移一行,向右偏移一列

计算返回的两行两列的和,如果不求和,则会报错,因为一个cell不能填充四个cell的内容。

HYPERLINK

HYPERLINK:创建一个超链接指向link_location,以friendly_name的字符串进行显示,link_location可以是URL链接或文件路径。

代码语言:javascript
复制
=HYPERLINK(link_location, friendly_name)

插入超链接

逻辑运算函数

一般用于条件运算,在Excel中,True代表数值1,False代表0。

IF

如果满足判断条件,则返回“真值”,否则返回“假值”。

代码语言:javascript
复制
=IF(判断条件, 真值, 假值)
=IF(AND(条件1, 条件2), 真值, 假值)
=IF(OR(条件1, 条件2), 真值, 假值)

例子

计算统计函数

求最值

MAX MIN

MAX:求某区域中的最大值; MIN:求某区域中的最小值。

求数目

COUNT COUNTIF COUNTIFS

COUNT:计数。 COUNTIF:单条件计数。 COUNTIFS:多条件计数。

代码语言:javascript
复制
=COUNTIF(区域, 条件)
=COUNTIFS(区域1, 条件1, [区域2, 条件2], ...)

例子 单条件计数:

A1到A3中大于等于10的数量

多条件计数:

A1到A3中大于5小于10的数量

求和

SUM

功能:计算单元格区域中所有数值的和。

SUMIF

功能:求满足条件的单元格的和。

代码语言:javascript
复制
=SUMIF(条件判断区域, 判断条件, 求和区域)

计算一班的总成绩

SUMPRODUCT

将数组间的对应元素相乘,并返回乘积之和。

代码语言:javascript
复制
=SUMPRODUCT(array1, array2, ......)

例子

如果只选取一列,和SUM一样只是求和。

只选一列

选取多列,就会返回对应元素乘积之和。

选取多列

取整

INT ROUND

INT:向下取整; ROUND:四舍五入取整,num_digits指定精确到哪一位。

代码语言:javascript
复制
=INT(number)
=ROUND(number, num_digits)

例子

向下取整

0表示精确到个位数

排序:RANK

功能:计算number在reference中排名。 order:0或默认,则为降序排列;其他数值则为升序排列。

代码语言:javascript
复制
=RANK(number, reference, order)

升序排列

描述统计

AVERAGEA

求算数平均值。

QUARTILE

求分位数。

STDEV

求标准差。

SUBTOTAL

该函数相当于以上几个函数的汇总,通过输入function_num参数,选择不同的函数。

代码语言:javascript
复制
=SUBTOTAL(function_num, ref1, ref2, ...)

时间序列函数

时间的本质是数字。

YEAR MONTH DAY

分别返回日期序号的年、月、日。

代码语言:javascript
复制
=YEAR(日期序号)
=MONTH(日期序号)
=DAY(日期序号)

DAY


DATE

将year,month,day组合成一个日期,相当于这三个函数的逆操作。

代码语言:javascript
复制
=DATE(year, month, day)

DATE


WEEKDAY WEEKNUM

WEEKDAY:根据一个日期是星期几来返回一个数字。 return_type:设置返回数字的规则。

WEEKNUM:根据一个日期是今年的第几周来返回一个数字。 return_type:

代码语言:javascript
复制
=WEEKDAY(serial_number, return_type)
=WEEKNUM(serial_number, return_type)

例子

2019/2/28属于第九周星期四。

WEEKDAY

WEEKNUM


NOW TODAY

返回当前的时间,now精确到时间,today只精确到日期。

代码语言:javascript
复制
=NOW()
=TODAY()
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2019.04.27 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 简介
    • 什么是函数
      • 常见函数分类
      • 文本清洗函数
        • 清除字符串空格
          • 合并单元格
            • 截取字符串
              • 替换单元格中内容
                • 替换指定位置:REPLACE
                • 替换指定文本:SUBSTITUTE
              • 查找文本在单元格中的位置
              • 关联匹配函数
                • VLOOKUP
                  • VLOOKUP 函数简介
                  • 四种查询方式
                • HLOOKUP
                  • INDEX
                    • MATCH
                      • ROW & COLUMN
                        • OFFSET
                          • HYPERLINK
                          • 逻辑运算函数
                          • 计算统计函数
                            • 求最值
                              • 求数目
                                • 求和
                                  • SUM
                                  • SUMIF
                                  • SUMPRODUCT
                                • 取整
                                  • 排序:RANK
                                    • 描述统计
                                    • 时间序列函数
                                    领券
                                    问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档