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,操作更方便哦

posted @ 2020-03-29 22:13  依羽杉  阅读(1544)  评论(0编辑  收藏  举报