stat函数_数据分析工具入门 掌握这些Excel函数就够了

8fb93f9102767925b664e725ab55f40e.png

5b72256922f7ae07f75f7a9f7bd9707f.png

03b4b19e58f4038114bc8c0912581cbe.png

      1.什么是函数

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

fb913eca40c1666824e07c4527a03cb8.png

      2.常见函数分类

  • 文本清洗函数

  • 关联匹配函数

  • 逻辑运算函数

  • 计算统计函数

  • 时间序列函数

02

文本清洗函数

      1.常用的文本清洗函数

  • 清除字符串空格：TRIM

  • 合并单元格：CONCATENATE

  • 截取字符串：LEFT / RIGHT / MID

  • 替换单元格中的内容：REPLACE / SUBSTITUTE

  • 查找文本在单元格中的位置：FIND / SEARCH

      2.清除字符串空格

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

=TRIM(text)

f72b305da7529d211c012043d73a3e17.png

清除A1单元格左右的空格

      3.合并单元格

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

=CONCATENATE(text1, text2, ...)

cba9e5498e0561e25b8973bcc295c315.png

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

      4.截取字符串

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

=LEFT(text, num_cahrs)

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

=RIGHT(text, num_chars)

      MID：从text中，从stat_num开始，提取num_chars个字符串。

=MID(text, start_num, num_chars)

      例子：

7a8d41448d3363d93bb97b67bc0acd29.png

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

fd9385251ccdd7c531f2e2f61b7e316e.png

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

5879fa6aaca63c7c10acd77cbbd4cfe0.png

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

      5.替换单元格中内容

      REPLACE：替换指定位置，从“原字符串”的“开始位置”开始，选择“字符个数”个，替换为“新字符串”。

=REPLACE(原字符串, 开始位置, 字符个数, 新字符串)

      例子：

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

fdaab6579dd32cdeed050dabd7342114.png

将2019替换为2018

      SUBSTITUTE：替换指定文本，在text中用new_text替换old_text，instance_num指定要替换第几次出现的old_text，如果不指定则替换old_text。

=SUBSTITUTE(text, old_text, new_text, instance_num)

      例子：

400b134dd2d1ceddc682fab397a941ac.png

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

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

      FIND & SEARCH：从within_test中查找FIND_text，返回查找字符的起始位置编号。

=FIND(FIND_text, within_text, start_num)=SEARCH(要查找字符, 字符所在的文本, 从第几个字符开始查找)

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

      例子：

982ecf9540c587c38bd8b734b2fba146.png

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

03

关联匹配函数

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

beace7aa42fc319d48bdaf6c2652a7cd.png

      1.四种查询方式

  • 单条件查找

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

=VLOOKUP(F2,$A$2:$D$55,4,0)

59d8433745286e3008bf2f091b1d9e98.png

单条件查询

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

  • 反向查找

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

=VLOOKUP(G2,IF({1,0},B2:B8,A2:A8),2,0)

a66d6efca2768f005735ce3f201aba08.png

反向查找

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

=VLOOKUP(检索关键字，IF({1,0},检索关键字所在列，查找值所在列)，2，0)

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

  • 多条件查询

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

=VLOOKUP(F2&G2,IF({1,0},A2:A53&B2:B53,D2:D53),2,0)

77585ed562a38663930ff159222f0429.png

多条件查找

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

  • 查询返回多列

      COLUMN

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

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

=COLUMN(待查询单元格/区域)

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

=VLOOKUP($G2,$A$2:$E$55,COLUMN(D1),0)

77585ed562a38663930ff159222f0429.png

多条件查找

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

=VLOOKUP(混合引用关键字，查找范围，COLUMN(xx)，0)

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

      HLOOKUP

=HLOOKUP(用谁去找, 匹配对象范围, 返回第几行, 匹配方式)

      和VLOOKUP的区别：HLOOKUP返回的值与查找的值在同一列上，而VLOOKUP返回的值与查找的值在同一行上。

      INDEX

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

=INDEX(array, Row_num, Column_num)

3f97308898e1e7f776087b88744ac782.png

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

      MATCH

  • 功能：在区域内查找指定的值，返回第一个查找值的位置。

  • lookup_value：需要查找的值。

  • lookup_array：查找的区域。

  • match_type：-1、0或1，0表示查找等于lookup_value的值。

=MATCH(lookup_value, lookup_array, [match_type])

daedaa0b968299efbe45d73f283d3820.png

查找A1到A4中6的位置

      Index & Match联合使用 = VLookup

7f646b9bebe415265037d223e488f6d7.png

      ROW & COLUMN

  • ROW：返回指定引用的行号。

  • COLUMN：返回指定引用的列号。

=ROW(reference)=COLUMN(reference)

      例子：

ace25322e99b8e9a98c2c84413ee9b96.png

求C列为第几列

      OFFSET

  • OFFSET：以指定的引用reference为起点，按照偏移量偏移之后，返回值。

  • rows：向下偏移多少行。

  • columns：向右偏移多少列。

  • height：返回多少行。

  • width：返回多少列。

=OFFSET(reference, rows, columns, height, width)

      例子：

053da6520adb688525ad98f04210a32f.png

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

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

d7957cd973fc9ee328a332882799a3e7.png

      HYPERLINK

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

=HYPERLINK(link_location, friendly_name)

c907b42682ba96b76f7520283df87b23.png

插入超链接

04

逻辑运算函数

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

      IF

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

=IF(判断条件, 真值, 假值)=IF(AND(条件1, 条件2), 真值, 假值)=IF(OR(条件1, 条件2), 真值, 假值)

      例子：

9c2c4ad590d7c0203476bfe53c9a1e24.png

05

计算统计函数

      1.求最值

      MAX MIN

  • MAX：求某区域中的最大值。

  • MIN：求某区域中的最小值。

      2.求数目

      COUNT COUNTIF COUNTIFS

  • COUNT：计数

  • COUNTIF：单条件计数

  • COUNTIFS：多条件计数

=COUNTIF(区域, 条件)=COUNTIFS(区域1, 条件1, [区域2, 条件2], ...)

      例子：

      单条件计数：

28216b693af3555e89cbbd90f73943dd.png

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

      多条件计数：

f2e0a6f3af836914b948b96c669c9d8b.png

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

      3.求和

      SUM

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

      SUMIF

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

=SUMIF(条件判断区域, 判断条件, 求和区域)

767cd9ef68febd7ac5e328d1280dd63e.png

计算一班的总成绩

      SUMPRODUCT

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

=SUMPRODUCT(array1, array2, ......)

      例子：

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

6f27c1eb90f4b35c3df58e7e53de894a.png

只选一列

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

d1eb8fcc9fd844216f46e69f470c266b.png

选取多列

      4.取整

      INT ROUND

  • INT：向下取整

  • ROUND：四舍五入取整，num_digits指定精确到哪一位

=INT(number)=ROUND(number, num_digits)

      例子：

c4d488ee0780febfd621166a63006514.png

向下取整

652699f26cad144cafc55b208b3377dc.png

0表示精确到个位数

06

排序

      RANK

  • 功能：计算number在reference中排名

  • order：0或默认，则为降序排列；其他数值则为升序排列

=RANK(number, reference, order)

8bb5028eb4573c55e803403d5e695e5c.png

升序排列

07

描述统计

  • AVERAGEA：求算数平均值。

  • QUARTILE：求分位数。

  • STDEV：求标准差。

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

=SUBTOTAL(function_num, ref1, ref2, ...)

08

时间序列函数

      时间的本质是数字。

      YEAR MONTH DAY

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

=MONTH(日期序号)=DAY(日期序号)

eddc4398db8e53860573420fea47cad1.png

DAY

      DATE

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

=DATE(year, month, day)

9c7d480046e3d64224ec6c9807e12612.png

DATE

      WEEKDAY WEEKNUM

  • WEEKDAY：根据一个日期是星期几来返回一个数字。

  • return_type：设置返回数字的规则。

121fb3e1ee158161979e28f01b535ef8.png

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

f52b9117e2a2664737d61bd826353ad5.png

=WEEKDAY(serial_number, return_type)=WEEKNUM(serial_number, return_type)

      例子：

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

86cb0f4d797337a29c3650fbd43b457c.png

WEEKDAY

ffc1e2e43f4652101d52ed505288b5b8.png

WEEKNUM

      NOW TODAY

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

=NOW()=TODAY()

来源：腾讯云

dfbf399419443b5bb5202c2f2cc5f549.png

创作挑战赛新人创作奖励来咯，坚持创作打卡瓜分现金大奖

Published by

风君子

独自遨游何稽首 揭天掀地慰生平

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注