六 数据分析基础 -关于 Excel

目录

一 关联匹配类 -8个

1 vlookup 函数

2 hlookup 函数

3 index 函数

4 match 函数

5 rank 函数

6 row 函数

7 column 函数

8 offset 函数

二 清洗处理类 -13个

1 trim 函数

2 rtrim 函数

3 ltrim 函数

4 concatenate 函数

5 left 函数

6 right 函数

7 mid 函数

8 replace 函数

9 substitute 函数

10 find 函数

11 search 函数

12 len 函数

13 lenb 函数

三 逻辑运算类 -3个

1 if 函数

2 and 函数

3 or 函数

四 计算统计类 -13个

1 min 函数

2 max 函数

3 average 函数

4 count 函数

5 countif 函数

6 countifs 函数

7 sum 函数

8 sumif 函数

9 sumifs 函数

10 sumproduct 函数

11 stdev 函数

12 substotal 函数

13 int/round 函数

五 时间序列类 -7个

1 today 函数

2 now 函数

3 year 函数

4 month 函数

5 day 函数

6 weekday 函数

7 datedif 函数

---

一 关联匹配类 -8个

        通常，当我们需要的数据不在同一个Excel表格中 or 同一个Excel 表的不同sheet中时，数据太多，复制粘贴也过于麻烦时，我们需要如何去进行整合呢？

1 vlookup 函数

• 用于查找首列满足条件的元素

= vlookup( 需要查找的值，需要查找值的区域，区域中包含的返回值的列号，精确或近似匹配 )

# 精确或近似匹配指定为 0 - FALSE；1 - True

2 hlookup 函数

• 搜索表的顶行或者值的数组中的值，并在表格化数组中指定的行的同一列中返回一个值

= hlookup( 需要查找的值，需要查找值的区域，区域中包含的返回值的行号，精确或近似匹配 )

# 精确或近似匹配指定为 0 - FALSE；1 - True

区别 hlookup返回的值与需要查找的值在同一列上,而vlookup返回的值与需要查找的值在同一行上

3 index 函数

• 返回表格或区域中的值或引用该值

= index ( 要返回值的单元格或者数组，所在行，所在列 )

4 match 函数

• 用于返回指定内容在指定区域 （某行或者某列）的位置

= match ( 要返回值的单元格区域或者数组，查找的区域，查找的方式 )

5 rank 函数

• 求某一个数值在某一个区域内一组数值中的排名

= rank ( 参与排名的数值，排名的数值区域，排名的方式 )

# 0 -为降序,1 -为升序，默认为 -0

6 row 函数

• 返回单元格所在行

7 column 函数

• 返回单元格所在列

8 offset 函数

• 从指定的基准位置按行列偏移量返回指定的引用

= offset ( 指定点，偏移多少行，偏移多少列，返回多少行，返回多少列)

二 清洗处理类 -13个

        数据处理之前，需要对提取的数据进行初步清洗，如清除字符串空格，合并单元格、替换、截取字符、查找字符串出现的位置等

1 trim 函数

• 清除掉字符串两边的空格

2 rtrim 函数

• 清除单元格右边的空格

3 ltrim 函数

• 清除单元格左边的空格

4 concatenate 函数

= concatenate ( 单元格1，单元格2，...... )

# 合并单元格中的内容，还有另一种合并方式是&，需要合并的内容过多时，concatenate效率更高

5 left 函数

• 从左边截取字符串

= left ( 值所在单元格，截取长度 )

6 right 函数

• 从右边截取字符串

= right ( 值所在单元格，截取长度 )

7 mid 函数

• 从中间截取字符串

= mid( 指定字符串，开始位置，截取长度 )

8 replace 函数

• 替换掉单元格的字符串

= replace ( 指定字符串，哪个位置开始替换，替换几个字符，替换成什么 )

9 substitute 函数

        与 replace 函数相似，不同之处在于 replace 函数根据位置进行替换，需要我们提供从第几个位置开始替换，替换几位，替换后的新文本；而 substitute 根据文本内容替换，需要提供替换的旧文本和新文本，以及替换第几个旧文本等。因此 Replace 实现固定位置的文本替换，Substitute 实现固定文本替换

10 find 函数

• 查找文本位置

= find ( 要查找的字符，指定的字符串，第几个字符 )

11 search 函数

• 返回一个指定字符或者文本字符串在字符中第一次出现的位置，从左到右查找

= search ( 要查找的字符，字符所在的文本，从第几个字符开始查找 )

        Find 和 Search 这两个函数功能几乎相同，实现查找字符所在的位置，区别在于 Find 函数精
确查找，区分大小写；Search 函数模糊查找，不区分大小写

12 len 函数

• 文本字符串的字符个数

13 lenb 函数

• 返回文本中所包含的字符数

三 逻辑运算类 -3个

1 if 函数

• 使用逻辑函数 if 时，如果条件为真，该函数将返回一个值；如果条件为假，函数将返回另外一个值

= if ( 条件，true时返回值，false返回值 )

2 and 函数

• 逻辑判断，相当于“并”
• 全部 参数为 True，则返回 True，经常用于多条件判断

3 or 函数

• 逻辑判断，相当于“或”
• 只要参数有 一个 True，则返回 Ture，经常用于多条件判断

四 计算统计类 -13个

1 min 函数

• 找到某区域中的最小值

2 max 函数

• 找到某区域中的最大值

3 average 函数

• 计算某一区域中的平均值

4 count 函数

• 计算含有数字的单元格个数

5 countif 函数

• 计算某个区域中满足给定条件的单元格数目

= countif ( 单元格1：单元格2，条件 )

6 countifs 函数

• 统计一组给定条件所指定的单元格数

= countifs ( 第一个条件区域，第一个对应的条件，第二个条件区域，第二个对应的条件,...,第n个条件区域，第n个对应的条件 )

7 sum 函数

• 计算单元格区域中的所有数值的和

8 sumif 函数

• 求满足条件的单元格

= sumif ( 单元格1：单元格2，条件，单元格3，单元格4 )

9 sumifs 函数

• 对一组满足条件指定的单元格求和

= sumifs ( 实际求和区域，第一个条件区域，第一个对应的求和条件，第二个条件区域，第二个对应的求和条件,...,第n个条件区域，第n个对应的求和条件)

10 sumproduct 函数

• 返回相应的数组或区域乘积的和

 = sumproduct ( 单元格 1: 单元格 2 ,单元格 3: 单元格 4 )

11 stdev 函数

• 统计型函数，求标准差

12 substotal 函数

= substotal ( 引用区域，参数 )

        汇总型函数，将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化，换言之，只要会了这个函数，上面的都可以抛弃掉

13 int/round 函数

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

五 时间序列类 -7个

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

1 today 函数

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

2 now 函数

• 返回当前的日期，动态函数

3 year 函数

• 返回日期的年份

4 month 函数

• 返回日期的月份

5 day 函数

• 返回以序列数表示的某日期的天数

6 weekday 函数

• 返回对应于某个日期的一周中的第几天。 默认情况下，天数是 1（星期日）到 7（星

期六）范围内的整数

= weekday ( 指定时间，参数 )

7 datedif 函数

• 计算两个日期之间相隔的天数、月数或年数

= datedif ( 开始日期，结束日期，参数 )