excel数据处理技巧笔记

from万门大学

 

1、数据透视表

 

2、条件格式

使用公式时用F4/fn+F4可以锁定数据

标记涨跌：使用条件格式（相对单元格）+格式刷

 

3、动态数据分析报表

数据透视表筛选功能

切片器进行动态筛选

切片器结合图表动态展示

切片器可以右键设置连接报表，同时操控多个数据透视表

 

4、单元格数字格式

数字、文本、日期

文本格式不能通过修改单元格格式改为数字/日期等其他格式，但可以在选中后出现的感叹号警告中改为数字格式

分列：设置中的逗号为英文逗号，在向导第三步可以对单独某列进行数据格式设置

文本型日期可以通过分列来修改成日期格式

 

5、排序工具

自定义排序

添加自定义排序序列：文件-选项-高级-常规-编辑自定义列表

通过在新一列中填写数字并排序来完成在表格中的隔行插入

 

6、筛选与高级筛选

数字筛选 文本筛选：~*，~？可以表示*，?符号本身

高级筛选表示“或”  设置的条件不同行

数据透视表内筛选：在表头分类的箭头中进行值筛选设置，而不是直接对值进行筛选

 

7、使用分类汇总

进行分类汇总前要先排序

具有多个层级

自定义排序

拷贝经过分类汇总后的数据：不能直接复制 查找和选择-定位条件-可见单元格-复制

定位条件可以用于直接删除图片

 

8、公式与基础函数

计数：COUNTA() 计数非空单元格 数字和文本都可以计数 而COUNT()只能计数数字

快捷键输入：拉取选中单元格-写公式-crtl+enter

定位条件：空值 批量填充公式

公式反推：模拟分析-单变量求解

 

9、Vlookup函数

VLOOKUP只会通过选中的第一列来查找

模糊查找：只有数字之间才存在近似关系，只找小于等于的值 可以用于计算提成/个税等找匹配区间的值

拖取复制公式时需要用F4锁定范围

不同格式的数据不能vlookup 需要强制转换

公式中常规转文本：A1&""

公式中文本转常规：A2*1

vlookup不能通过右侧数据找左侧数据

 

10、Match与Index函数

实现从右侧数据找左侧数据

右键下拉 选择不带格式填充：不覆盖单元格格式

index做动态员工表

选项中添加开发工具 插入控件滚动条

 

11、使用函数进行统计

sumif countif

sumifs countifs 多个条件

写函数是单元格要左对齐

vlookup可以强行使用多个条件筛选

 

12、使用日期函数

推算日期：

拆分-year month day

组合-date

计算日期间隔：=DATEDIF(A2,B2,"y")  即=DATEDIF(开始日期,结束日期,"y/m/d")

标记快到期的数据：

条件格式-介于&=today()

 

13、使用表格

数据透视表的缺点：新增数据无法更新 若整列选择则无法分组

将数据设置为表格后 用表格生成数据透视表 就可以刷新数据

=ifna(VLOOKUP()，“”）将na变为空白单位格

将数据转换为表格后，之后再更新数据透视表、函数、图表会自动进行相应改变

 

14、文档安全性设置

输入限制：

数据验证：固定数据范围+出错警告

可以限制输入法中英文

删除限制：

审阅-保护工作表

一半保护一半不保护工作表：设置单元格格式 取消锁定选项

能修改数据但不能修改列宽、删除列：先取消单元格锁定选项，再通过保护工作表勾选规定用户权限

 

15、邮件合并工具

统一模板 抓取数据 批量打印

将模板粘贴到word中-邮件-选择收件人-使用现有列表将excel表格与word文档相连接-插入合并域

完成并合并-编辑单个文档

使数据不分页：将开始邮件合并中默认的信函改为目录