excel小技巧总结

基于2016版，B站王佩丰+自己的简化描述

1. 使用视图新建窗口，建立镜像表格，然后全部重排，垂直并排，可以同时看到两个表格，同步修改表格
2. 两列数据快速调换位置：shift+上下左右箭头，同理想把第8名插入到第5名后面，也用这个方法，其余的可以快速排上
3. 选择多列，鼠标在列名称，双击，所有列都是最合适的列宽，或者调整列宽，每一列宽都相同
4. 快速到达表格边界，1，ctrl+上下左右；2，鼠标在十字箭头时，双击当前单元格下边界，快速到达最下端
5. 冻结前两行，前两列，选中C3单元格，视图·拆分，会以C3为中心，将整个表拆分为4部分
6. Ctrl+： 为当前日期
7. 填充柄：单独使用填充柄为复制，比如选1，向下拉，if Ctrl+填充柄，则为序列。反之亦反，比如选中日期。
8. 填充柄：选中日期，填充柄状态，右键下拉，出现特殊填充，如以工作日填充、等比数列、快速填充
9. 填充柄：自定义填充：文件·选项·高级·常规·编辑自定义列表，输入：张三,李四,王五
10. 日期1000，表示1900-01-01开始的第1000天，比如2===1900-01-02
11. 隐藏不想显示的数据，单元格格式自定义写成;;;，即正值不显示，负值不显示，0不显示，文本不显示
12. 日期类型里mmm表示英文月份缩写，ddd表示英文星期缩写，dddd表示全写，aaa表示六，aaaa表示星期六
13. 替换里*很多个字符，?表示一个字符，~*转义字符
14. A900:B1000,选一个范围||||反选：先选好一个区域，左上角编辑commonuse，下次一点就能到
15. 更改批注形状：先插入一个性状，进入绘图工具，右键编辑形状，添加到快速访问工具栏，显示批注·选中批注，前面设置的icon亮起，即可修改批注形状
16. 批注设为图片：选择批注，右键·设置批注格式·颜色与线条·填充效果·图片
17. 取消合并后快速填充：定位空值，编辑栏输入=↑，ctrl+enter
18. 选中一个区域，写一个值，ctrl+enter整个区域都填充为该值
19. 多列排序：按照重要次序，倒着排序，或者自定义排序
20. 本文排序，自定义排序·次序·添加序列，类似9自定义填充柄
21. 辅助列排序：工资条：n个员工，复制表头n-1次，原表格辅助列为0-n，n-1表头辅助列为1.5-n-1.5-1，按照辅助列升序
22. 辅助列排序：筛选后添加信息，全系的人里补上1班的信息，见https://www.cnblogs.com/super-yb/p/12753949.html
23. 打印行数较多表格，使每一页都有表头，页面布局·打印标题|页面设置·工作表·顶端标题行输入1:1
24. 某列去重：数据·筛选高级·将筛选结果复制到其他位置（只能复制到当前sheet）☆···勾选选择不重复记录
25. 高级筛选：多条件··且：条件区域设置为，多条件··或：

26. 分组统计：mysql：max（salary），group by department-id，sex ：excel：分类汇总：先给sex，然后de-id排序，·数据·分类汇总，不勾选替换当前分类

27. 复制粘贴筛选后表格，可能会把虽有表格复制上：先选中要复制的区域，（F5，定位可见单元格）==alt+; ，再复制选中单元格
28. 重复数据合并单元格成1个：先分类汇总该列，将新出现的列选中2:n行，F5·定位空值·合并单元格·复制·点击任意单元格·删除分类汇总，选中党员那列的2:n行，只粘贴格式

29.  数据透视表：最好提前选好要透视的表格范围，列名称不能有空。|  双击统计出来的字段值，直接整理出一个得出该值的原始数据

    1. 勾选数据透视表选项·显示里的经典数据透视表布局。　　

    2. 日期在行名称，变成按季度显示：选中任意一个日期，右键·组合（日期有空格无法组合）  |  将积极分子，预备，党员选中，组合成新的类别，不勾选分类汇总

    3. 数值在行名称，distinct数值太多，也可以组合

    4. 双击值字段的表头，可修改统计方式，还可多次拖拽同列到值字段，并列显示不同的统计

    5. 多列值字段时，计算得到新的值字段列：数据透视表工具·分析·[字段项目和值]

    6. 根据透视表的筛选分多个sheet：透视表工具·分析·选项·显示报表筛选页 ☆！！用shift选中所有sheet，在任意sheet复制N行粘贴（覆盖）到透视表上（相当于清除内容） 

30. 一列以文本储存的数字，给这列加0快速变为数值类型
31. =(A1="男")*30+A2：利用运算符得到的布尔值实现类似if函数的功能==  if(A1="男",A2,A2+30)
32. 切换绝对引用和相对引用：：光标放在E1后面··F4      | 思考编写九九乘法表

33. 应用定位，写公式，然后快速填充。

34. 　　对于if多层嵌套：两种替代

    1. 　　建立一个小表，使用vlookup

    2. =if(A1="高",100,0)+if(A1="中",100,0)+if(A1="低",100,0)
35. =if(iserror(A1/B1),0,A1/B1)规避除数为0带来的单元格报错显示，影响后续的计算
36. =if(and(A1="",B1=""),1,0)  |   =if(OR(A1="",B1=""),1,0)  |   =IF(OR(AND(A1,B1),AND(A1,B1)),1,0)
37. 数据有效性，只需编辑选中列的第一个单元格F1

38. 数据有效性：出库数量T小于库存O

39. 双引号的转义： 多加一个引号 ： =A1&":"&"""GOOD"""