文章目录
- Excel 的进阶学习
- 1. 常用的 Excel 函数及用途
- 1. 关联匹配类
- 2. 清洗处理类
- 3.逻辑运算类
- 4.计算统计类
- 5.时间序列类
- 2.基础
-
- 3. 实战分析
-
Excel 的进阶学习
Excel 是我们工作中经常使用的一种工具,对于数据分析来说,这也是处理数据最基础的工具。 很多传统行业的数据分析师甚至只要掌握 Excel 和 SQL 即可。 对于初学者,有的时候并不需要急于苦学 R 语言等专业工具(当然会也是加分项),因为 Excel 涵盖的功能足够多,也有很多统计、分析、可视化的插件。只不过我们平时处理数据的时候很 多函数都不知道怎么用。
1. 常用的 Excel 函数及用途
1. 关联匹配类
功能 | 函数 | 语法 |
---|
用于查找首列满足条件的元素 | VLOOKUP | vlookup(lookup_value查找值,table_array所在数组,col_index_num,返回值相对列数,range_looup模糊\精准查找-TRUE\FLASE) |
搜索表的顶行或值的数组中的值,并在表格或数组中指定的行的同一列中返回一个值。 | HLOOKUP | hlookup(lookup_value查找值,table_array所在数组,row_index_num返回值对应的行数,range_lookup) |
返回列表或数组中的元素值,此元素由行序号和列序号的索引值给定 | INDEX | INDEX(A2:B3,2,1)位于区域 A2:B3 中第二行和第一列交叉处的数值。 |
用于返回指定内容在指定区域(某行或者某列)的位置 | MATCH | match(lookup_value,lookup_array,match_type)(要返回值的单元格区域或数组,查找的区域,查找方式) |
求某一个数值在某一区域内一组数值中的排名。 | RANK | =RANK(参与排名的数值, 排名的数值区域, 排名方式-0 是降序-1 是升序-默认为 0)。 |
返回单元格所在的行 | ROW | |
返回单元格所在的列 | Column | |
从指定的基准位置按行列偏移量返回指定的引用 | OFFSET | offset(reference,rows,cols,height,width)(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列) |
2. 清洗处理类
数据处理之前,需要对提取的数据进行初步清洗,如清除字符串空格,合并单元格、替换、截 取字符、查找字符串出现的位置等。
-
清除字符串空格:使用 Trim/Ltrim/Rtrim
Trim清除掉字符串两边的空格
-
合并单元格:使用 concatenate
语法:=Concatenate(单元格 1,单元格 2……) 合并单元格中的内容,还有另一种合并方式是&,需要合并的内容过多时,concatenate 效率 更快。
-
截取字符串:使用 Left/Right/Mid
= Mid(指定字符串,开始位置,截取长度)
-
替换单元格中内容:Replace/Substitute
Replace 实现固定位置的文本替换,Substitute 实 现固定文本替换。
=Replace(指定字符串,哪个位置开始替换,替换几个字符,替换成什么)
而 Substitute 根据文本内容替换,需要提供替换的旧文本和 新文本,以及替换第几个旧文本等。
3.逻辑运算类
4.计算统计类
在利用 excel 表格统计数据时,常常需要使用各种 excel 自带的公式,也是最常使用的一类。 重要性不言而喻。不过 excel 都自带快捷功能。
-
最值:MIN 、MAX 函数
返回最大的值 | MAX ) |
---|
第****K个最大值 | LARGE |
返回最小的值 | MIN |
第****K个最小值 | SMALL |
-
均值:AVERAGE/AVERAGEIF/AVERAGEIFS
AVERAGE 函数:计算某区域中的平均值
TRIMMEAN:去掉最大值和最小值**(按比例)之后返回平均值**
-
计数:
-
COUNT : 计算某区域中包含数字的单元格的数目
-
COUNTIF:计算某个区域中满足给定条件的单元格数目
-
COUNTIFS 函数:统计一组给定条件所指定的单元格数
-
COUNTBLANK :统计空值 countbland(range)
-
求和
-
标准差 Stdev
-
汇总型函数 Substotal
汇总型函数,将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化,换言之,只 要会了这个函数,上面的都可以抛弃掉了
-
取整函数:Int/Round
-
int 向下取整
-
round 按小数位取数。
ROUNDUP 向上取整
ROUNDDOWN
-
频数
FREQUENCY,frequency(data_array,bins_array)
MODE 频率最多的数值
-
求余
MOD
-
正态分布的密度函数的值
NORMDIST(x,mean,standard_dev,cumulative),
男性的身高是均值 179.5、标准差为 3.697 的正态分布。所以男性的身高为 180 的概率为NORMDIST(180,179.5,3.697,0)=0.1069
-
绝对值
ABS
5.时间序列类
专门用于处理时间格式以及转换。
2.基础
1.快捷键
隐藏 | **浅隐藏;深影藏;**查看代码(alt+F11)VBAproject-1变2右键属性加密 |
---|
宽和高 | 粗调、细调;单个和批量 |
限定区域内快速选择 | |
左下和右上 | ctrl+home/end |
跳空白选 | ctrl+shift+home/end |
对齐 | 分散对齐 文字方向 |
批注:插入图片 | 右键选择编辑在边框处,选择编辑批注,在颜色选择中并且保持长宽比 |
纵向和横向批量填充 | 全部选择要填充的单元格 按F2或者鼠标点击输入区:输入内容 ctrl+R\D |
自动****抽取单元格内容填充 | Ctrl+E 合并日期 分段显示号码 身份证提取日期 批量添加前缀或者后缀 顺序调整带括弧 拆分数据代替了分列 混合复杂数据拆分 |
2.数据组
数组公式 | 数组公式是相对于普通公式而言的。普通公式(如上面的=SUM(B2:D2),=B2+C2+D2等),只占用一个单元格,只返回一个结果。而数组公式可以占用一个单元格,也可以占用多个单元格。它对一组数或多组数进行多重计算,并返回一个或多个结果 |
---|
显示 | {} |
结束公式 | Ctrl+Shift+Enter |
维数 | 维数”是数组里的又一个重要概念。数组有一维数组,二维数组,三维数组,四维数组…… 在公式里,我们更多接触到的只是一维数组和二维数组 一维数组 单独的一行或一列 二维数组 多行多列 |
元素 | 数组里的元素,同一行内的各元素用英文逗号“,”分开,用英文分号“;”将各行分开 |
-
计算
行列数相同数组的运算 | 规律很简单:两个同行同列的数组计算是对应元素间进行运算,并返回同样大小的数组 |
---|
数组与单一的数据的运算 | 一个数组与一个单一的数据进行运算,是将数组的每一元素均与那个单一数据进行计算,并返回同样大小的数组。 |
单列数组与单行数组的计算 | A、计算结果返回一个多行列的数组; B、返回数组的行数同单列数组的行数相同、列数同单行数组的列数相同。 C、返回数组中第R行第C列的元素是单列数组的第R个元素和单行数组的第C个元素运算的结果 |
错误信息
#DIV/0! | 零作除数 |
---|
#NAME? | 在公式中使用了不能识别的名称 删除了公式中使用的名称,或者使用了不存在的名称。 函数名的拼写错误 |
#VALUE! | 使用了不正确的参数或运算符 在需要数字或逻辑值时输入了文本 |
#REF! | 引用了无效的单元格地址 删除了由其它公式引用的单元格 将移动单元格粘贴到由其它公式引用的单元格中。 |
#NULL! | 指定了两个并不相交的区域,故无效 使用了不正确的区域运算符或不正确的单元格引用。 |
#N/A | 当在函数或公式中引用了无法使用的数值内部函数或自定义工作表函数中缺少一个或多个参数。使用的自定义工作表函数不存在。VLOOKUP()函数中的查找值lookup_value、FALSE/TRUE参数指定了不正确的值域。 |
#NUM! | 数字类型不正确在需要数字参数的函数中使用了不能接受的参数。由公式产生的数字太大或太小:在-10307和(10307之间 |
######! | 不属于错误(列宽设置问题)输入到单元格中的数值太长,在单元格中显示不下;单元格公式所产生的结果太长,单元格容纳不下;日期和时间产生了负值时将产生。 |
基本认识
- 工作薄、工作表
计算操作符
指数 | ^ |
---|
比较 | < 、> 、 <=、 >= 、= 、<> |
定义计算优先级 | () |
合并两个文本值 | & |
除100或百分数 | % |
固定单元格 | $ |
套用表格引用 | [] |
数组 | {} |
通配符 | * |
参数分隔符 | , |
3.数据图展示
3. 实战分析
- Vlookup 区间查找)
注意
- 尽量不要使用合并单元格展示
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)