Excel心得

学习视频来源：
https://www.bilibili.com/video/BV184411C7Ci?p=35&spm_id_from=pageDriver

重点：排序、筛选、格式化为表格、交叉分析筛筛选器、设定格式化的条件、工作表、合并汇算、图表！、枢纽分析表(数据透视表)、打印分页设定、页首页尾、浮水印、常用函数、资料验证功能(学号输入错误，弹窗提示，在数据->有效性)、辅助栏、储存格的定义名称&IDIRECT函数、资料剖析(数据->分列)、数值格式、日期的格式设定&运算、成绩排名、使用LEFT函数提取内容，index,match，保护表格机密，重复资料操作，随机数random()分组抽奖等，工作进度，甘特图Gannt Chart（平面横条图） / 误差线、Boolean逻辑、AND/OR 函数、SUMPRODUCT函数，Dynamic Arrays 动态阵列功能，Pareto Chart 2/8法则 帕累托图/柏拉图、累计百分比、截取网络上的资料（数据->导入数据->来自网站链接）、parsehub（网页爬取工具）、3D地图、线上问卷（Microsoft Forms）、Macro（宏）录制视频->套用到下一个excel中

1.获取日期：
control+; --获取年月日
control+‘’ --获取十分秒
2.冻结窗口
3.分割视窗
4.自定义排序，并提供内部降序
5.自定义清单的设置，可以完成自动补全
5.自定义颜色筛选排序
6.自动增加列，直接tab
7.交叉分析筛筛选器：（插入->切片器） 多维筛选
8.设定格式化的条件：(给筛选的枚举打上颜色等，路径：开始->条件格式↓->突出显示单元格规则，其中还包含额外：数据条，色阶，图表集，这三个可以丰富列表的展示，百分比深浅之类的)
9.工作表：底部那个sheet，可以跨excel复制表。工作群组：可以crtl多选工作表，一起改格式
10.合并汇算：三个sheet表数据，合并汇算。数据->合并计算
11.图表1：框选范围，拆入->图表。进入图表编辑界面可以，添加各种图片元素。图表右侧，有筛选功能（漏斗记号）。行列转换
12.图表2：图片的横纵坐标，可以精确自定义，例如阈值，单位值，样式，精度等。条形图的样式也可以自定义，右键图片即可。
1）饼图 2）折线图 3）散点图 4）雷达图 5）组合图
13.枢纽分析表：（插入->枢纽分析表）效果图：多维度排列组合，组成群组，时间表

14.打印分页设定， (视图->分页预览)手工拉到最后一列即可。（页面布局->拆入分页符）可以完成按照1月2月3月的分类。列印：增加线框。打印标题：给三个列都设置标题。减少纸张：页面布局->缩放比例
15.页首页尾：视图->(普通/分页预览/页面布局)。其中分页预览可以自定义页脚页眉。可以插入图片
16.excel函数：（直接输入，或者fx按钮）

1. =SUM(C3:C6)
2. =AVERAGE(C3:C6)
3. =MAX(C3:C6)
4. =LARGE(C3:C6,2)    array资料范围，第2顺位
5. =SMALL(C3:C6,2)    array资料范围，第2顺位
6. =IF(C7>=60,"及格","不及格")    可以嵌套使用
7. =IFS(C7>90,"A",C7>=80,"B",C7<80,"C")
8. =VLOOKUP(C3,E2:K13,2,FALSE)  学号，应用表范围，第几列，Fasle精确，ture模糊（递增排序）。相对参照，绝对参照↓
9. =HLOOKUP(入参，查询范围，要传回的列数)  
10. =VLOOKUP(J3,$M$3:$N$7,2,FALSE)  绝对参照使用$
10.=IFERROR(VLOOKUP($C$3,$E$2:$K$12,2,FALSE),"查无此人") 如果没有值，则显示"查无此人"
11.=IF(C3="","",IFERROR(VLOOKUP($C$3,$E$2:$K$12,2,FALSE),"查无此人"))  
↑如果C3为空，则不显示。否则，如果能匹配上显示匹配到的值，否则显示差不粗人“”
12.=COUNTA()  记录个数，null不计
13.=COUNTIF(C2:C14,"手機")  
14.=COUNTIF(C2:C14,某位置)  在某位置输入条件
15.=COUNTIFS(B2:B14,F2,C2:C14,G2)
↑范围，条件，范围，条件...
16.=COUNTIFS(B2:B14,F2,C2:C14,">1000")
17.=COUNTIFS(B2:B14,F2,C2:C14,">"&G2)  G2自己输入条件
18.=COUNTIFS(C2:C14,G2,A2:A14,">=2018/6/1",A2:A14,">=2018/6/30") 6月份信用卡消费金额
11. =MONTH(A2) 输出月份
12. =SUMIFS(B2:B14,F2,C2:C14,H2)  
13. =INDIRECT(D6)   通过索引，找到下一层存的数据，联动下拉用的上
14. =DATE()  =now()  获取当前日期  F9
15. =TODAY()-H3  时间间隔
16. =DATEDIF(H3,TODAY(),"y")  "m"   "ym" 就职时间间隔
17. =NETWORKDAYS(M3,N3)   工作日计算
18. =NETWORKDAYS.INTL(M4,N4,自定周末代码编号,假日范围)  
19. =RANK.EQ(D3,$D$3:$D$10)  D3为排名，同名显示相同，后面区间为选定单位，绝对参照
20. =RANK.AVG(D3,$D$3:$D$10)  D3为排名，后面区间为选定单位，绝对参照
21. =RANK.EQ(G3,$G$3:$G$10,1)  1递增，0递减排序
22. PapayaClassroom
=LEFT(B2,6)     Papaya
=RIGHT(B2,9)    Classroom
=MID(B2,7,5)    Class
=FIND("y",B2)   5
=FIND("a",B2,7)    9
=LEN(B2)       15
毛絨連帽衫-女-M   
圓領毛衣-男-XL  
=LEFT(FIND("-",B3)-1)     截取名字
=MID(B3,FIND("-",B3)+1,1)    截取性别
=RIGHT(B3,LEN(B3)-FIND("-",B3,FIND("-",B3)+1))  ※ 截取尺寸 思路是得到第二个-的位置，然后用总长度-这个位置，从右截取相应的个数
23. =INDEX(栏/列范围，顺位)
24. =INDEX(B2:E5,3,3)   取矩阵的横纵坐标
25. =MATCH("阿福",B4:E4,0)  要查找的对象，范围，1是小于，0是精确匹配 1是大于
26. 案例： =INDEX(C3:F10,MATCH(I2,D3:D10,0),MATCH(H3,C2:F2,0)) 如26图
27. 串联储存格  =A1&B1&C1
28. =RANDBETWEEN(1,12)  随机值
29. =INDEX(C3:C14,RANDBETWEEN(1,12))  +index得出获奖人姓名
30. =CHOOSE(RANDBETWEEN(1,2),"A卷","B卷")  学生考试试卷
31. F9 刷新函数
32. =CHOOSE(ROUNDUP(RANK(D3,$D$3:$D$14)/6,0),"A","B")  平均分租：产生随机数，根据除法余数的规则，向上进位形成1,2。在choose
33. win + ;  或者 UNICHAR(128336)   快速插入图片时钟
34. =E3>1000  或 =B3>"有" 或 =C3<>1000    布尔逻辑 返回true / false
35. =AND(条件1,条件2，条件3)  
36. =OR(条件1,条件2，条件3)  可以混合使用，再加上IF更完善输出
37. =IF(OR(C3="有",AND(E3>1000,D3>=5)),"可升级VIP","不可升级") 有交年费、消费金额>1000吗，消费>5次，可升级VIP
38. 布尔 true=1 false=0
39. =SUMPRODUCT(C3:C8,D3:D8)  乘积加总函数 C3xD3+C4xD4+...
40. =UNQUE()   wps暂时没有  office需要开启开发者模式（Spill自动溢出能力）
41. =SUMIF()   满足条件相加
42. =FILTER(表格，(条件1)*(条件2)，报错提示)  做查询，输出行全部内容
43. =SORT() 排序，某列，增序，降序
44. Alt + =  快速加和 快捷键
45. XLOOKUP(关键字，搜索范围，回传范围，错误提示，近似比对，模糊匹配，查询方向， )   优点1.可以跨工作表查询2.WPS没有该函数 3.错误提示功能  4.近似比对值-1 5.横向查询 6.模糊查询 郭*  7.查询方向 -1逆向查询 8.双向筛选(嵌套VLOOKUP)
46. 

17.储存格的定义名：公式->名称管理器 ，起到引用作用，不需要再框范围

18.快速选取资料：
crtl+↑ 跳转到最上面
Ctrl + Shift + ↓ 全选到最下面
F4 重复以上个指令
19.移除重复项
数据->重复项
20.行列互换
复制，右键->选择性粘贴
21.显示公式 ctrl + ~
22.模拟分析 可以根据结果值，预算公式的自变量取值
23.分数避免识别成日期：输入 0 1/2 -> 识别为分数
输入 ’ 2132131 ->识别为文本
**24单元格存储格式设置：**快捷键 Ctrl + 1 ，其中自定义的格式，可以使用类似正则表达式去拼写
25.日期相关 同样是Ctrl + 1 设置自定义，当前日期快捷键：Ctrl + ； 再按 当前日期快捷键：Ctrl + shift + ; 、 当前时间
=NETWORKDAYS.INTL(M4,N4,下图选择,假日范围)
26.非VLOOKUP()的搜索使用 ->index+match来完成

**27.保护工作表：**审阅->保护工作表 、、隐藏公式：右键单元格->设置单元格格式->保护->隐藏√、、保护工作表->输入密码、、保护工作簿、、允许用户编辑区域、、另存为-可以设密码
28.重复值处理 数据->重复项->选择匹配的列√；、、方法二：通过&合并要对比的单元格，开始->条件格式->新建格式规则-> =COUNTIF($E$2:$E$12,$E2)>1 ->格式 给个颜色、、防止输入重复的值：数据->有效性->规则管理->=COUNATIF(A:A,A1)=1
29.工作进度 开发工具->√设置
30.=SUMPRODUCT() 复杂运用的原理
31.Pareto Chart 2/8法则 帕累托图/柏拉图
三个方式：
1.组合图表
2.枢纽分析表->枢纽分析图
3.内建图标（最简单！）

31.XLOOKUP(关键字，搜索范围，回传范围 )原理