Excel数据处理

本文主要记录学习b站视频《Excel数据处理》中遇到的问题和学习的知识点总结。

第1讲 数据透视表

创建数据透视表： 点击数据源中任意一个单元格，插入数据透视表。
完成数据透视表： 将数据透视表中的所需要的字段拖拽至左边表格的行、列、值中完成数据透视表。
值字段显示：
值的汇总方式中，求和值可以双击更改为计数值。
也可将值的汇总方式更改为值的显示方式可显示金额的百分比。

双击数据透视表中的数据可以查看其背后的明细数据（每次查看会生成一个新的统计表，查看完记得删除）
数据透视表中日期可以进行组合，可以按年、季度、月、日组合。
数据透视表刷新需要右键—刷新

第2讲 条件格式

创建条件格式： 选中数据整列，开始—条件格式
清除条件格式： 点击需要清楚的单元格，开始—条件格式—清除规则
条件格式运用：
1.重复值：通过条件格式—重复值标记突显表格中的重复值来判断数值是否重复。
2.标记涨跌：通过条件格式突出显示单元格规则大于小于来进行标记，具体的数值删除后点击相比较的单元格即可，设置好一个单元格的条件格式后可使用格式刷进行格式复制。（注意相对引用时要删除单元格前的绝对锁定）
3.使用数据条：‌通过颜色填充的条形图来直观展示数据大小。

第3讲 动态数据分析报表

数据透视表：行、列用来做分类，值用来做统计，页用来做筛选。
数据透视表字段删除：在右边工具栏里直接将勾选取消避免拖拽失误。
数据透视表切片器：进行动态的数据展示
数据透视表切片器只能在新版的EXCEL文件.xlsx格式中使用，97-03格式的文件.xls格式不能使用，使用delete键删除切片器。
切片器同时控制两个透视表：右键点击切片器—报表连接
数据格式不规范会导致数据透视表不能使用。

第4讲 单元格数字格式

常规性数字：数量、大小、多少、可运算

非常规性数字：可以左右相连或中间截取

excel数字计算精度只有15位，超过15位后几位精确不了。
excel自动数字靠右对齐，文本靠左对齐。
文本型编号编码数字输入：选中整列—右键—设置单元格格式—文本格式
文本格式转为常规格式：
文本型数字：选中需要更改的数字单元格区域—点击区域右上角黄色感叹号—转换为数字。
文本型日期：选中需要更改的数字单元格整列—点击数据—分列—分列中可直接指定单元格格式（文本格式数字不能直接通过设置单元格格式改为常规格式数字。）
善用分列来更改数据格式，确保数据的正确性。
数据分列：选中A列—点击数据—分列
分列时注意数据中间分隔的“，”是中文还是英文，分列正确会有数据预览。
分列时可以直接指定列数据的数据格式。

第5讲 使用排序工具

排序：
无空行，数据表无间断：点击需要排序的列值中任意一个单元格（不建议选一整列）—点击开始—排序和筛选
有空行：手工选中所有数据范围—点击开始—排序和筛选—自定义排序

排序过程中发现表头参与排序：手工选中所有数据范围—自定义排序—勾选数据包含标题
按照颜色排序：手工选中所有数据范围—点击开始—排序和筛选—自定义排序—按颜色排序（注意颜色多时选择添加条件）
自定义排序：
1.添加自定义排序：
文件—选项—高级—编辑自定义列表—输入顺序，顺序用回车键隔开—添加—确定
2.排序：手工选中所有数据范围—点击开始—排序和筛选—自定义排序—次序选择自定义序列
排序意义：排序工具可以改变行的次序，往往被用来重构数据结构。
具体应用：工资条

第6讲 筛选与高级筛选

自动筛选工具的使用
1.操作：开始—排序和筛选—筛选—取消勾选全部—勾选需要的数据
2.取消使用：点击下拉三角—勾选全部/清楚筛选器
3.数字筛选：开始—排序和筛选—筛选—点击下拉三角—数字筛选
4.文本筛选：开始—排序和筛选—筛选—点击下拉三角—文本筛选（文本筛选中，在“*”或“？”前加入字符“~”来确定筛选文本）
高级筛选的使用
1.操作：选中数据—高级筛选
2.高级筛选表达==“或”==的关系
在高级筛选中条件区域数值放在同行表示“且”，数据不同行表示“或”。

数据透视表中筛选
数据透视表中的筛选是分层级的，在数据透视表中，数字上不能筛选，依据值筛选但需要在行子段上进行。

第7讲 使用分类汇总

1.操作：数据—分类汇总（最右）分类汇总前要先排序
分类汇总会在表格左边提供三个层级，1总计、2分类总计、3原始数据。
2.优点：源数据和分类汇总后的数据在一起
3.多层次的分类汇总需要先排序再第一层分类汇总后进行第二层分类汇总，并在第二次取消勾选替换当前分类汇总。
4.缺点：分类汇总中做统计级别太多会不清晰。
5.结果使用：分类汇总后的统计结果不能直接复制，直接复制会把数据源一起复制。需要开始—查找和选择—定位条件—勾选可见单元格再进行复制。
表格左边有表格层级不一定是分类汇总，也有可能是组合。

第8讲 公式和基础函数

基础函数：开始—自动求和（求和、平均值、计数、最大值、最小值）

计数函数如果需要统计文本，将“COUNT”改为“COUNTA”
如果表格中公式不能复制即不能通过下拉得到其他值，可先选中区域，在第一个格中写完公式后按“Ctrl+Enter”。
跳跃式使用公式：先通过查找定位空值，选中空白格后输入公式，最后按“Ctrl+Enter”。
公式反推：结果跟随某一个量的变化而变化，结果确定时，在excel中反推变量。通过数据—模拟分析—单变量求解

第9讲 使用vlookup函数

具体使用
=VLOOKUP(A2，数据源!A:B，2，0）
A2表示查找的值，数据源!A:B表示数据范围，2表示结果在数据范围的第2列，0表示精确查找，1表示近似查找。
写vlookup函数时可以用tab键补充，vlookup找值只会在第一列找，数据范围必须保证查找的值在第一列。
近似匹配：只找小于等于自己的值中找最大值。只有数字之间才有近似关系，只有数字找近似值时才会用到近似匹配，例如年龄，数据找区间。
用vlookup查找但数据格式不相同时：
=VLOOKUP(A2&“”，数据源!A:B，2，0）常规数字变文本数字，在查找的常规数字值后面左右相连一个东西就会自动转变为文本格式。
=VLOOKUP(A2*1，数据源!A:B，2，0）文本数字变常规数字，在查找的文本数字值后面*1进行运算就会自动转变为常规数字格式。
数据源如果不是一整列是特定的区域不会随着公式运算进行更改，注意在数据范围前用Fn+F4进行锁定。

第10讲 使用Match与Index函数

Match：找 （找到数据具体所在的位置）
match（B2，客户信息！B：B，0）
B2表示寻找的数据 ，客户信息！B：B表示从包含数据的具体的一列数据范围，0表示精确查找，1表示近似查找。
Index：取（负责在一个区域里取回第几个）
Index（客户信息！A：A，7）
客户信息！A：A表示从具体的客户信息表A列这个数据范围中取数据，7表示取第7行的数据
Match+Index：match给index提供查询的依据
具体使用：=index（客户信息！A：A，match（B2，客户信息！B：B，0））

用index函数做动态员工报告书
滚动条
设计滚动条：开发工具—插入—表单控件—滚动条
移动滚动条：右键—按住移动—点击其他位置
具体设置：右键—设置控件格式—单元格链接/最大值最小值
用index函数查找

第11讲 使用函数进行统计

1.sumif条件求和：计数需要区域条件
=SUMIF（B:B，I4，E:E）
B:B是寻找的数据的范围，I4是数据所在的具体的单元格，E:E是需要求和的值所在的范围。
在B:B中查找值为I4的对应的E:E范围中的数据进行求和。
2.countif条件计数：计数需要区域条件
=countif（B:B，I4）
B:B是寻找的数据的范围，I4是数据所在的具体的单元格
在B:B中查找值为I4的数据并进行计数。
3.sumif的变态能力：解决多列循环数据计算问题
=SUMIF（A:J，L3，$ B$ 1）
A:J是包含所有循环数据的数据范围而不是一列，L3是数据所在的具体的单元格，$B$1是指将绝对引用后在范围里所有和B1一样的单元格进行求和。
4.sumifs多条件求和
=sumifs（E:E，B:B，I12，C:C，J12）
E:E表示求和数据所在的区域，B:B是条件1I12所在的区域，C:C是条件2J12所在的区域。
对B:B中数据为I12，C:C中数据为J12的E:E中的值进行求和。
5.countifs多条件计数
=COUNTIFS（B:B，I12，C:C，J12）
B:B是条件1I12所在的区域，C:C是条件2J12所在的区域。
6.多条件的vlookup
可以将两个条件的联合****构造成新的一列放在第一列进行vlookup
=VLOOKUP（I3&J3，A:D，4，0)
I3&J3表示两个条件的联合，A列是新构造的两个条件联合后的列，A:D是数据范围，4表示输出第4列的值，0表示精确查询。
如果是数据也可以考虑用sumifs函数进行替代。

第12讲 使用日期函数

1.推算日期（先拆后组）
开始——间隔——结束
a.日期（数字另外的表现形式可计算）+天数=新的日期，可以直接用公式计算
b.将日期的年月日进行分解
=year（A2），=month（A2)，=day（A2）
将日期的年月日再合上
=date（D6，E6+4，F6）
D6表示刚分解后的年份，E6表示刚分解后的月份，4是间隔的月份，F6表示刚分解后的日期
将两个公式组合
=date（year（A2），month（A2)+B2，day（A2））
2.日期间隔
开头——结束——间隔
=datedif（A2，B2，“y”）
A2表示第一个时间参数，B2表示第二个时间参数，**“y”**表示输出间隔的结果单位是年。
3.案例：标记快到期（15天）的数据
（要标记的日期大于今天小于15天）
今天是哪一天函数=today（）
操作：条件格式—突出显示单元格规则—介于—在介于的两个格子中写下函数today（）和today（）+15

第13讲 使用表格

将list数据转换为table表格解决数据更新问题。
1.表格与透视表
数据透视表缺陷：不能将新增的数据更新。
表格对数据透视表的作用：动态抓取数据
操作：插入—表格（表格会包括新更改的数据）—插入—数据透视表—表/区域为表一
2.表格与函数
a.vlookup函数N/A处理：=ifna（vlookup（D2，J:K，2，0），“”））
**vlookup（D2，J:K，2，0）是完整的vlookup函数，“”**表示如果N/A则输入空值
b.插入—表格—再进行vlookup函数书写
3.表格与图表
插入—表格—再进行图表的插入

第14讲 文档安全性设置

通过限制用户的行为来保障文档的安全性
1.数据验证（防止其他用户在表格乱录入）
a.案例：针对固定数据范围的限制
选中数据范围（A列)—数据—数据验证—设置
选中数据范围（A列)—数据—数据验证—出错警告
b.案例：支付方式中只能输入现金/转账/支票
选中数据范围（A列)—数据—数据验证—设置—序列—来源中填写固定词，固定词中间用英文的“,”间隔开。
c.通过数据—数据验证—输入法模式来控制表格中英文输入法
2.工作表保护（限制用户删除数据）
a.操作：审阅—保护工作表
b.公式、手工录入一半保护一半不保护
操作：选中不想被保护的单元格范围—右键—设置单元格格式—保护—取消勾选锁定

第15讲 邮件合并工具

用于抓取数据 做好布局 合并文档
1.把所有人的信息打印出来
excel不便于打印，将表格复制到word方便打印，需要将excel中的数据抓取到word中。
操作:邮件—选择收件人—使用现有列表—插入合并域—完成布局—预览结果—完成并合并—编辑多个文档—全部
2.怎么让通过邮件合并后的数据在word中不分页
使用邮件合并工具之后—邮件—开始邮件合并—目录

其他补充操作

公式的引用方式：
相对引用
绝对引用：引用的数值不变，在数值前面用Fn+F4进行锁定。
公式复制：
1.鼠标放在表格右下角，黑色十字之后按住左键下拉。
2.鼠标放在表格右下角，黑色十字之后双击。
按**“ctrl”键拖拽，数字会累加。
3.鼠标放在表格右下角，黑色十字之后按住右键**下拉可以选择不带格式填充，可以保证公式复制之后不破坏原表格格式。

数据透视表行列值蓝格如何显示
操作：数据透视表选项—显示—经典数据透视表布局
原因：旧版的EXCEL文件.xls格式生成数据透视表后是经典的蓝格布局，新版的EXCEL文件.xlsx格式生成数据透视表后是新的布局

excel开发工具：文件—选项—自定义功能区—主选项卡—勾选开发工具
写公式注意单元格需要左对齐

定位
可以通过开始—查找和选择—定位条件—对象来删除表格上方的图片等对象。

数据不符合规范会怎样？
1.不能做透视
2.用不了vlookup
3.做不了动态员工报告信息查询
4.word无法抓取数据

EXCEL中，怎样把#DIV/0!修改为0或空值或不显示：IF(AND(A2<>0,B2<>0),A2/B2,“”)