EXCEL学习笔记-透视表，条件查询，高级刷选，动态图表，切片器，vlookup函数，日期函数，index match函数、表格（动态数据），连接word邮件等

​第 1 讲数据透视表

选中单个单元格-插入-数据透视表

透视表的数据，双击都可以显示所有的统计数据

 

日期统计后，点年那可以组合为年月日

 

第 2 讲使用条件格式-标红某些列，去除格式-锁定值F4-去锁定就是去$

 

清除特定单元格的条件格式

 

标记重复值-可以警示-所以不能重复的列可以先使用条件格式

 

锁定某个单元格按F4

 

标记涨跌，单元格-开始-条件格式-再点击前面一个单元格，去掉$ 即锁定，点确定

同理，条件格式选小于，选红色，则就可以标记涨跌了，最后格式刷刷下。

 

数据条显示

 

第 3 讲动态数据分析报表-更改透视表格式-筛选字段进行统计-切片器进行切换-报表连接（多个图怎么切片器关联）

 

筛选字段进行统计

 

切片器可以让数据动态的变化

动态数据分析表

 

两个图标怎么关联

第二个图标按第一个图标生成后，点击，ctrl+c复制，然后到第一个图标下，选中一个单元格，ctrl+v进行黏贴

---关键问题，怎么和切片器关联

 

第 4 讲单元格数字格式-格式转换选分列-分列可以统一格式-透视表无法组合时记得分列-金额等敏感信息求和也记得分列下，否则文本的就不统计啦

文本类型的数值改为常规数值变不了

文本型日期改为常规的日期怎么改，通过分列，选择常规或者日期

当很多数据有时候其实格式不一样，有可能是常规，或者文本，所以为了统一格式，建议多使用分列来统一格式

 

另外透视表有时候格式不统一，导致不能组合，统一修改格式后，要刷新两次，透视表才能正常

第 5 讲使用排序工具-透视表可以通过自定义排序进行显示

一般排序选中某列的某个单元格，点击排序

当有空格时，则需要删除空格进行排序，否则会认为是两张表，从而不能一起排序

不过选中整张表，然后点自定义排序

 

 

自定义排序：文件-选项-高级-编辑自定义列表-输入列表，换行分隔-确定

 

这样数据透视表的时候就可以按照自定义的列表进行排序了

 

工资条的做法

其实就是创建个列表进行排序

 

第 6 讲筛选与高级筛选

有数据筛选，主要大于等于小于等

也有文本筛选，有开头包含，和不包含等搜索

文本搜索时，假如有* 和 ？，则可以通过~进行转义，如下图：

 

高级筛选-写一个条件区域，同行的条件是且，不同行是或的关系

 

数据透视表筛选数据的方法，一般筛选左边的列

 

第 7 讲使用分类汇总

分类汇总前一定要先排序，点击分类的单个单元格，排序，然后再分类汇总

 

假如要分地区和产品类别分类统计呢？？

分类汇总后，数据拿着，得先定位到可见单元格，才能复制，否则会把所有的表格都复制。

有时候从网上拷贝的数据导入单元格，有图片，则可以定位 对象，则图像可以删除

 

分组和分类汇总左侧都会有分级显示

第 8 讲公式与基础函数

定位到空值，然后自动求和就可以了

 

count函数只能计算数字，不能计算文本，且空值不计算

但countA可以计算数字和文本，这个函数用的比较多

 

有些表格不让往下拉填充，因为格式不同

这时候则选中要填充的区域，然后直接=输入公式，不直接回车，按住ctrl键，再回车

 

各项已经计算出来了，则通过模拟分析，反推可变的参数应该为多少？比如说原价报价为2976，现在改为4000，则反推单价应该为多少？

 

第 9 讲使用Vlookup函数

数字之间才有近似关系

查找值（比如说下面的客户ID）必须在选取的最左侧

Vlookup不同格式的数据是不能互相转换

通过某列A的值查找另外列B的值  =VLOOKUP(A2,数据源!A:B,2,)

Vlookup只能A列开始选中，为最左列，B列填选中的第几列

 

下图通过ID获取地址，索引值的计算方法如下图：

 

另外只有近似匹配，只有数字之间才有近似匹配

 

源格式-文本，目的格式-常规，则F4&””

 

源格式-常规，目的格式-文本，源格式*1，文本转为常规

 

第 10 讲Match与Index函数-match找，index取值

=INDEX(客户信息!A:A,MATCH(查询!B2,客户信息!B:B,0))

 

填充的时候要右键下拉，拉下来后不松开左键下，选择不带格式填充

文件-选项-高级-自定义功能区-开发工具

 

开发者工具-插入-滚动条-横着拉就横向

 

 

 

 

第 11 讲使用函数进行统计

=SUMIF(B:B,I4,E:E)

 

 

sumifs 多个条件

多条件的VLOOKUP--&连接起来后再查询

 

第 12 讲使用日期函数-先拆分为年月日，然后再组合- year，month，day，date-到期自动提醒

 

工龄的计算，通过datedif 计算

 

到期自动提醒-today() +条件格式

 

第 13 讲使用表格更新数据-表格可以动态更新透视表的数据

 

表格对数据透视表的作用：动态抓取数据，透视表刷新就可以了

 

第 14 讲文档安全性设置-数据验证-序列有效性设置-审阅保护工作表

 

序列有效性设置-枚举值之间用英文逗号隔开

 

中英文切换

 

第 15 讲邮件合并工具-excel关联导出到word

万门大学学习的操作的excel：

链接：https://pan.baidu.com/s/15V1jUSFc5K57dXVWdeIFOQ

提取码：f3ma

复制这段内容后打开百度网盘手机App，操作更方便哦