老师简介：王佩丰
2009~2013连续五年荣获MVP（微软全球最有价值专家）称号、MCT（微软认证讲师）、MCITP（微软认证信息技术专家）、MCP（微软认证专家）、MCSE（微软认证系统工程师）、CCNP（思科认证网络专家）.拥有8年Office企业培训经验。
一、数据透视表
二、使用条件格式
1、标记有条件的数据或字段、重复值
2、公式格式中锁定单元格：选中再点击Fn+F4（会在锁定单元格前出现：e.g.$A$1）
3、标记涨跌：绝对和相对的应用，如：$A$1表绝对；A1表相对
注意：绝对表示锁定单元格，在公式中引用时不会随下拉或横拉改变单元格
4、条件格式——数据条/色阶（温度）
三、动态数据分析报表
1、xlsx（07——）经典版数透表格式
xls（03——07）新版数透表格式；（数据透视表选项—显示—勾选经典版数透表）
2、切片器的使用：只能在xlsx上才能使用
3、折线图/柱状图+切片器的使用
4、两个图表用切片器同时控制，右击切片器—数据透视表链接
四、单元格数字格式：“文本格式”可以用“分列”解决
五、使用排序工具
1、有空行排序：全选中—自定义排序—选中字段/大小/颜色&表格包含标题&升/降序—确定
2、自定义排序：文件—选项—高级—编辑自定义列表—1/2/3/4&添加—确定—全选中—自定义排序—......
3、隔行插入（如做工资条）：隔行插入空行
六、筛选与高级筛选
1、数据筛选：大于/小于/等于/.......
2、文本筛选：开头是/结尾是/包含/......（注意：C*和C~*的区别：前者表示包含C即可，后者表示包含C*）
3、高级筛选：
4、透视表的筛选是分层筛选
七、使用分类汇总
1、将字段升/降排序—分类汇总（用左上角1/2/3控制），若多条件的，用自定义排序写入多个条件（有几个条件，就分类汇总几次），
2、定位应用：选中—查找选择—定位条件—可见单元格—ctrl c—ctrl v（不复制隐藏数据）
3、注意和“创建组”区分（没有汇总的是用“创建组”区分的）
八、公式与基础函数
1、多个单元格同时使用同一函数：“查找和选择”（F5）—空值
2、不能下拉重复公式时，可以用：“查找和选择”—空值—输入公式—ctrl enter
3、count函数只能数数字，不能数文本，数文本用countA
4、要保证公式后的数字为不变值，可用反除法：数据—模拟分析—单变量求解
九、Vlookup函数
1、Vlookup(A2,数据源!B:C,2,0)
A2：要找的单元格
数据源!B:C：在B列中寻找A2单元格，取C列的值
2：取B、C列中C列的值
0：精确匹配；1：近似匹配
注意：Vlookup只能左边找右边的数据/文本，要保证查找的线索要在第一列，右边找左边要用match和index函数
2、Vlookup(B2,数据源!B:E,4,0)
3、Vlookup(F4,$A$1,$C$6,3,0)
4、常规转文本：Vlookup(F4&"",$A$1,$C$6,3,0)
5、文本转常规：Vlookup(F4*1,$A$1,$C$6,3,0)
十、match和index函数
1、match函数找，index函数取，取值要两者连用
index(数据源!A:A,match(查询!B2,数据源!B:B,0))
2、index函数做重复型的报告书（如员工信息表）
十一、使用函数进行统计
1、sumif(B:B,I4,E:E)+sumif(....)+sumif(.....)
countif(B:B,I4)
2、多列循环数据计算问题：sumif(A:J,L6,$B$1)
3、多条件求和：sumifs(......)
十二、使用日期函数
1、单元格A2（2019/8/11）使用公式加B2（4个月）：date(year(A2),month(A2)+B2,day(A2))
2、两个日期间隔月时间：datedif(A2,B2,"Y")
3、标记即将到期的数据（如15天之内）：开始—条件格式—突出显示—介于（today(),today()+15,设置为红色）
十三、使用表格
1、动态数透表抓取新增表格：将原表设置为“表格”
2、ifna(vlookup(D2,J:K,2,0),""),如果出现#N/A，则显示空白
3、表格与图标抓取新增表格：将原表设置为“表格”
十四、文档安全性设置
1、防止其他用户在我的表格中乱写，针对固定数据范围的限制：数据—数据有效性/数据验证—整数（数据）/序列（文本）/输入法模式
2、工作表保护（防止乱删）
①整张工作表保护：审阅—保护工作表
②部分保护：选中不保护列右击—设置单元格格式—保护—取消锁定—审阅—......
十五、邮件合并工具：统一模板，抓取数据，批量打印
将word和excel建立联系，建立模板：选择收件人—使用现有列表—插入合并域—完成并合并—编辑单个文档
网友评论