18个高效WPS表格技巧,值得收藏

EXCEL篇

  • 方方格子
  • power map
  • data chart
  • 慧办公
  • Excel易用宝
  • 方方格子

全能型 EXCEL插件:方方格子 下载
方方格子是我最常用的 EXCEL 插件,它内置了 文本处理、批量录入、删除工具、合并转换、重复值工具、数据对比、高级排序等上百个实用功能。

  • PowerMap

三维地图可视化插件:PowerMap
Office 技巧 - 图1

  • Data Chart

数据图表插件:Data Chart
经常与 EXCEL 打交道,有关「图表」各位肯定都不会陌生。各种条形图、扇形图、折线图……是我们展示数据的基础。
Office 技巧 - 图2

  • 慧办公

  • Excel易用宝

Word篇

  • 小恐龙公文排版助手
  • 开启自动保存
  • 公式插入与引用

插入题注
设置题注样式
设置制表位:20字符居中,无前导符;40字符右对齐,无前导符。
光标位于公式前,一个tab键,光标位于公式与编号之间,两个tab键。
引用问题:
Office 技巧 - 图3
可手动修改。

  • 表格插入与引用

直接插入,设置三线表样式
插入Excel,设置三线表

PPT篇

  • islide
  • 口袋动画PA
  • OneKeyTools
  • islide

Office 技巧 - 图4

  • 口袋动画PA

  • OneKeyTools

    PDF篇

  • Acrobat(正版付费)

  • PDF-Viewer(PDF转图片)

    EXCEL快捷键

    常用快捷键

    | 快捷键 | 功能 | 快捷键 | 功能 | | —- | —- | —- | —- | | Ctrl+W | 关闭工作簿 | Ctrl+Shift+End | 将单元格选定区域扩展至工作表上最后一个使用的单元格(右下角) | | Ctrl+O | 打开工作簿 | Ctrl+Shift+冒号 (:) | 输入当前时间 | | Ctrl +S | 保存工作簿 | Ctrl+空格键 | 选择工作表中的整列。 | | Ctrl+C | 复制 | Shift+空格键 | 选择工作表中的整行。 | | Ctrl+V | 粘贴 | F4 | 绝对引用 | | Ctrl+Alt+V | 打开“选择性粘贴”对话框 | | | | Ctrl+X | 剪切 | | | | Ctrl+Z | 撤销最后一个操作 | | | | Ctrl+B | 加粗 | | | | Ctrl+分号 (;) | 输入当前日期 | | | | Ctrl+重音符 (`) | 在工作表中,在显示单元格值或公式之间切换。 | | | | Ctrl+A 或 Ctrl+Shift+空格键 | 选择整个工作表 | | | | Esc | 取消单元格或编辑栏中的输入。 | | | | Shift+F11 | 插入一个新工作表。 | | | | F12 | 显示“另存为”对话框。 | | |

EXCEL函数

常用函数

  • SUM(number1,number2,.…)返回某一单元格区域中所有数值之和。
  • SUMIF(range,criteria,sum_range):WPS学院教程
  • SUMIFS(sum_range,criteria range1,criteria1,[criteria range2,criteria2].…)对区域中满足多个条件的单元格求和。WPS学院教程
  • MATCH(lookup value,lookup array,match type)返回在指定方式下与指定项匹配的数组中元素的相应位置。
  • AVERAGE(number1,number2,.…)返回所有参数的平均值(算术平均值)。参数可以是数值、名称、数组、引用。
  • IF(logical test,value_if true,value if false)判断一个条件是否满足:如果满足返回一个值,如果不满足则返回另外一个值。
  • COUNT(vale1,value2.…)返回包含数字的单元格以及参数列表中的数字的个数。
  • MAX(number1,number2.….)返回参数列表中的最大值,忽略文本值和逻辑值。
  • SIN(number)返回给定角度的正弦值。

    逻辑函数

  • IF(logical test,value if true,value if false)判断一个条件是否满足:如果满足返回一个值,如果不满足则返回另外一个值。WPS学院教程

  • IFS(logical test,value_if true.…)检查是否满足一个或多个条件并返回与第一个TRUE条件对应的值。WPS学院教程
  • IFNA(value,value_if_na)如果表达式解析为#N/A,则返回您指定的值;否则返回表达式的结果。WPS学院教程
  • IFERROR(value,value if_error)如果公式的计算结果错误,则返回您指定的值;否则返回公式的结果。WPS学院教程
  • AND(logical1,logical2.….)在参数组中,任何一个参数逻辑值为FALSE,即返回FALSE;只有当所有参数逻辑值为TRUE,才返回TRUE。
  • OR(logical1,logical2.…)在参数组中,任何一个参数逻辑值为TRUE,即返回TRUE;只有当所有参数逻辑值为FALSE,才返回FALSE。
  • NOT(logical)对参数逻辑值求反。(当要确保一个值不等于某一特定值时,可以使用NOT函数
  • SWITCH(expression,value1,result1,[default or_value2,result2].….)根据值列表计算表达式并返回与第一个匹配值对应的结果。如果没有匹配项,则返回可选默认值。
  • TRUE()返回逻辑值 TRUE。
  • FALSE()返回逻辑值 FALSE。

    日期与时间函数

  • DATE(year,month,day)返回代表特定日期的序列号。

  • DATEDIF(start date,end date,unit)计算两个日期之间的天数、月数或年数。
  • DATEVALUE(date text)返回以字符串所表示的日期值所对应的序列号。

    INDIRECT 函数

Excel 数据透视表大全

Excel 仪表板

https://www.thesmallman.com/dashboards

Excel 实例小技巧

日期 | 单元格修改,日期单元格自动更新日期

  • VBA 方法 ```vbnet 右击sheet1标签>查看代码>粘贴下面的代码

Private Sub Worksheet_Change(ByVal Target As Range) For Each c In Target.Cells ‘如果用填充等同时改变了多个单元格,则一个个的进行判断 With c If .Column = 2 Then ‘判断被改变的单元格是否在B列 Application.EnableEvents = False .Offset(0, -1) = Date ‘如果在B列,则在同行的A列填充当前系统日期 Application.EnableEvents = True End If End With Next End Sub

  1. **代码注释**:If .Column = 2 表示第二列的任意个单元格发生变化,如果你是第三个单元格,则改成 If .Column = 3,以此类推。.Offset(0, -1) = Date 这个可以望文生义,offset表示偏移,比如.Offset(0, -1)表示同行左边一列 .Offset(0,2)表示同行右边数2 ,.Offset(1,2)表示下面一行,右边数2列,=date表示将这个单元格改成当前的日期。
  2. <a name="qnACX"></a>
  3. ## 图表 | 滑珠进度图
  4. [视频教程](https://haokan.baidu.com/v?vid=4238450904366488186&pd=bjh&fr=bjhauthor&type=video)<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1628908159691-9eaff803-2952-4e5f-ae13-90bf50b9afbc.png#clientId=ufe7c07c0-e9b1-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=134&id=u9b2467ed&margin=%5Bobject%20Object%5D&name=image.png&originHeight=267&originWidth=746&originalType=binary&ratio=1&rotation=0&showTitle=false&size=94793&status=done&style=none&taskId=u1e6e83a5-d2db-4836-8601-8a4a08a8e2d&title=&width=373)
  5. <a name="oulBg"></a>
  6. ## 图表 | 对比条形图
  7. > 只适合两指标值大约在同一范围大小,或不是指标而是两维度值得比较
  8. > 制作详情点击图片链接
  9. [![image.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1628908400314-fefb4e89-4141-495a-ac11-e43bbf6514f2.png#clientId=ufe7c07c0-e9b1-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=u835523af&margin=%5Bobject%20Object%5D&name=image.png&originHeight=380&originWidth=439&originalType=binary&ratio=1&rotation=0&showTitle=false&size=122214&status=done&style=none&taskId=u9bc4bc83-047c-49bd-9079-70c6af98b08&title=)](https://www.jianshu.com/p/40a32cf782a0)<br />[![](https://cdn.nlark.com/yuque/0/2021/jpeg/2981571/1634176343254-48847030-dc88-431d-a612-6188ca385120.jpeg#clientId=u35e1974d-06d1-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=u59fca003&margin=%5Bobject%20Object%5D&originHeight=385&originWidth=640&originalType=url&ratio=1&rotation=0&showTitle=false&status=done&style=none&taskId=u57f4882d-fbb8-4d48-869d-021271bd848&title=)](https://www.xitongtiandi.net/wenzhang/office/22382.html)<br />[
  10. ](https://www.xitongtiandi.net/wenzhang/office/22382.html)
  11. <a name="axtD4"></a>
  12. ## 颜色 | 条件格式公式填充整行
  13. [https://zhidao.baidu.com/question/35638961.html](https://zhidao.baidu.com/question/35638961.html)
  14. <a name="KOmXY"></a>
  15. ## 可视化 | 来自美团商业分析师的Excel可视化宝典
  16. > [https://zhuanlan.zhihu.com/p/28131779](https://zhuanlan.zhihu.com/p/28131779) 一份不容错过的Excel数据可视化宝典~
  17. <a name="YEPBY"></a>
  18. ## 提取个十百千万上的数字
  19. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1630999278922-c23557d6-3f87-40c5-9b21-6c3a5df4e0fe.png#clientId=uc9b3ea9d-63ad-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=133&id=ue77c4b1d&margin=%5Bobject%20Object%5D&name=image.png&originHeight=133&originWidth=1002&originalType=binary&ratio=1&rotation=0&showTitle=false&size=14257&status=done&style=none&taskId=u4b32d0fb-3fe8-489e-93ff-de744fcdc44&title=&width=1002)
  20. <a name="q9YHQ"></a>
  21. ##
  22. <a name="IybSW"></a>
  23. ## 单元格数字格式设置颜色
  24. [https://www.office26.com/excelhanshu/excel-customise-cell-format.html](https://www.office26.com/excelhanshu/excel-customise-cell-format.html)<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631167569317-368cd10d-4f6f-4dbc-89e4-c121f0d741a9.png#clientId=u1f7a6354-6fd4-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=20&id=u2cc7fe7a&margin=%5Bobject%20Object%5D&name=image.png&originHeight=20&originWidth=432&originalType=binary&ratio=1&rotation=0&showTitle=false&size=1472&status=done&style=none&taskId=u3d50949e-c097-4082-98f1-a5bb37cf24d&title=&width=432)
  25. - 基本颜色:黑色,白色,红色,洋红,绿色,黄色,蓝色,青色,
  26. - [颜色1/2...]
  27. - ![](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631168757545-2bd5d7b8-ae9f-42a0-b5f0-8fab297779a2.png#clientId=u1f7a6354-6fd4-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=uff9f68fd&margin=%5Bobject%20Object%5D&originHeight=696&originWidth=1056&originalType=url&ratio=1&rotation=0&showTitle=false&status=done&style=none&taskId=u28b5bd18-e804-41fa-827a-e7c8f07e1bb&title=)
  28. <a name="ZNl9v"></a>
  29. ## 不重复计数
  30. **SUM**(1/COUNTIF(C2:C13,C2:C13))<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631174739457-c52a518b-42ea-46a8-b35f-d2fe831d315f.png#clientId=u1f7a6354-6fd4-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=u5210e74a&margin=%5Bobject%20Object%5D&name=image.png&originHeight=300&originWidth=393&originalType=url&ratio=1&rotation=0&showTitle=false&size=46272&status=done&style=none&taskId=u576f751e-d8b1-42f3-b62b-8c92734b1d7&title=)<br />**SUMPRODUCT**(1/COUNTIF(C2:C13,C2:C13))<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631174746882-83c7011b-854e-4b81-bda3-35010e9d1848.png#clientId=u1f7a6354-6fd4-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=u1da4379e&margin=%5Bobject%20Object%5D&name=image.png&originHeight=310&originWidth=450&originalType=url&ratio=1&rotation=0&showTitle=false&size=51128&status=done&style=none&taskId=uda3ce77a-c01c-419d-9d23-454048626de&title=)
  31. <a name="vY3jQ"></a>
  32. ## char() 字符大全
  33. 用法:char(十进制值)<br />添加:换行符—char(10)<br />![ASCII字符对应表.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631676636385-4211cc10-0a4f-498b-90b7-dc7b38a986e3.png#clientId=u5c1911aa-d611-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=1208&id=u6ec17679&margin=%5Bobject%20Object%5D&name=ASCII%E5%AD%97%E7%AC%A6%E5%AF%B9%E5%BA%94%E8%A1%A8.png&originHeight=1208&originWidth=612&originalType=binary&ratio=1&rotation=0&showTitle=false&size=196638&status=done&style=none&taskId=ue5ad9c1c-325a-421b-b2a2-0ed4274abea&title=&width=612)
  34. <a name="eLOHU"></a>
  35. ## 公式判断单元格是否含空格、回车符
  36. > [https://zh-cn.extendoffice.com/documents/excel/2896-excel-combine-cells-with-line-break.html](https://zh-cn.extendoffice.com/documents/excel/2896-excel-combine-cells-with-line-break.html)
  37. > [https://www.muzijie.com/a2/93y4wn963.html](https://www.muzijie.com/a2/93y4wn963.html)
  38. ![](https://cdn.nlark.com/yuque/0/2021/jpeg/2981571/1631675809484-4e5cc05c-1749-408e-a6d4-e8f20a581b7b.jpeg#clientId=u125759be-4358-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=u4cbba0a0&margin=%5Bobject%20Object%5D&originHeight=385&originWidth=500&originalType=url&ratio=1&rotation=0&showTitle=false&status=done&style=none&taskId=ub5768966-b66d-4dc4-9a2a-4e13cb9c73a&title=)<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631674985650-dc06f973-a407-4155-ab2a-d8048f9e3e5b.png#clientId=u54edbe48-1d89-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=74&id=u97463a60&margin=%5Bobject%20Object%5D&name=image.png&originHeight=74&originWidth=360&originalType=binary&ratio=1&rotation=0&showTitle=false&size=5753&status=done&style=none&taskId=ue4b0d405-e189-43e0-b7cb-06dc3512a6c&title=&width=360)
  39. <a name="yyQKs"></a>
  40. ## 公式删除/添加空格、回车符
  41. - 添加回车符
  42. 公式中换行符用char(10)表示<br />![](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631675406013-9c98c74b-c3c0-469b-bcba-2984c39b3934.png#clientId=u125759be-4358-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=uffd1b770&margin=%5Bobject%20Object%5D&originHeight=427&originWidth=658&originalType=url&ratio=1&rotation=0&showTitle=false&status=done&style=none&taskId=ua6337830-d3d8-453a-af3c-27c2f64025e&title=)<br />![](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631675474159-11bb1b8a-773f-4cf9-9ebb-f60e6db66f8a.png#clientId=u125759be-4358-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=u97a7598c&margin=%5Bobject%20Object%5D&originHeight=411&originWidth=655&originalType=url&ratio=1&rotation=0&showTitle=false&status=done&style=none&taskId=ua717bd3f-228f-449b-bd84-3e510f4fd5d&title=)
  43. - 删除空格回车符
  44. ![image.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631675601862-6fc5e1e9-df56-4544-9309-066fbc8da756.png#clientId=u125759be-4358-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=u8de9825c&margin=%5Bobject%20Object%5D&name=image.png&originHeight=170&originWidth=891&originalType=url&ratio=1&rotation=0&showTitle=false&size=10567&status=done&style=none&taskId=uf9f033dc-4c0a-4fa2-bb09-93d03f41e66&title=)<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/2981571/1631675707209-bfe0c2f3-fb2c-48c7-ac75-4a77a7deacfc.png#clientId=u125759be-4358-4&crop=0&crop=0&crop=1&crop=1&from=paste&id=uff826ad4&margin=%5Bobject%20Object%5D&name=image.png&originHeight=198&originWidth=1142&originalType=url&ratio=1&rotation=0&showTitle=false&size=13420&status=done&style=none&taskId=ucf464ed5-d880-4c54-a00e-a00cdbe4525&title=)
  45. <a name="fyn2j"></a>
  46. # VBA 教程
  47. > [易百VBA教程](https://www.yiibai.com/vba/vba_programming_charts.html)
  48. <a name="RnWB1"></a>
  49. ### POWER QUERY
  50. <a name="2hQUW"></a>
  51. #### 添加新列
  52. <a name="sFRTg"></a>
  53. ### VBA宏
  54. <a name="m08gq"></a>
  55. #### 合并工作簿
  56. <a name="W19cm"></a>
  57. # 实用案例
  58. <a name="vgxrV"></a>
  59. #### 时间的四舍五入
  60. <a name="oiIAM"></a>
  61. #### 查看某单元格被谁引用
  62. <a name="lqHVn"></a>
  63. #### 4钟转置排列方法
  64. <a name="khurg"></a>
  65. #### 合并多个工作簿到一张工作表
  66. <a name="Ufikz"></a>
  67. #### 透视表自动更新计算内容
  68. [点击查看【bilibili】](https://player.bilibili.com/player.html?aid=80034872)
  69. <a name="HOGi2"></a>
  70. #### 提取身份证号码信息
  71. <a name="5tKV1"></a>
  72. #### 提取数字
  73. <a name="uq9vy"></a>
  74. #### 自动加边框
  75. [点击查看【bilibili】](https://player.bilibili.com/player.html?aid=800496399)
  76. <a name="GrbbY"></a>
  77. #### 自动添加当前时间而不变化
  78. <a name="D9vM1"></a>
  79. #### 快速分列
  80. [用符号提取,快速完成Excel数据分列 - 知乎](https://zhuanlan.zhihu.com/p/53490696)
  81. <a name="KilRt"></a>
  82. #### 筛选整点时间
  83. [怎样在Excel中筛选整点的时间?_Office教程网](https://www.office26.com/excel/excel_1468.html)
  84. <a name="UUwq5"></a>
  85. #### 智能考勤表
  86. [智能考勤表](https://www.ixigua.com/6736525898612212238/)
  87. <a name="jJq9I"></a>
  88. #### 自动记录录入数据时间
  89. [http://www.360doc.com/content/20/0925/18/6907828_937581466.shtml](http://www.360doc.com/content/20/0925/18/6907828_937581466.shtml)
  90. <a name="egi2M"></a>
  91. ##
  92. <a name="RoT6v"></a>
  93. ## 条件累计求和sumifs
  94. ```basic
  95. = Table.AddColumn(筛选的行, "近三天数量", each List.Sum(Table.SelectRows(筛选的行,(x)=>x[产品类型]=[产品类型] and Number.From(x[日期]-[日期])>-3 and Number.From(x[日期]-[日期])<1)[销售数量])

条件累计求和.png
求每日近三天累计数据—PowerQuery.xlsx
powerquery的counif&countifs函数.xlsx

重复值累计计数

  1. 方法一:有效
  2. let
  3. = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
  4. 索引 = Table.AddIndexColumn(源, "索引", 1),
  5. 计数 = Table.AddColumn(索引, "times", each Table.RowCount(Table.SelectRows(索引,(x)=>x[要计数的列]=[要计数的列] and x[索引]<=[索引]))),
  6. 删除 = Table.RemoveColumns(计数,{"索引"})
  7. in
  8. 删除

计算当日处于本月第几周

  1. # 思路:当日所处本年周次-当月1号所处本年周次+1
  2. weeknum(A2,2)-weeknum(eomonth(A2,-1)+1,2)+1

第一个汉字出现的位置

  1. =MATCH(,0/(MID(A2,COLUMN(2:2),1)>="吖"),) Ctrl+Alt+Enter

条件排名排序

image.png
不过这样得到的结果有个问题,名次是从0开始的,要解决也很简单,有两个方法。

方法1:直接在公式后加1,结果如图所示:
方法2::将大于号改成大于等于,结果如图所示:

公式中花括号/中括号的影响

Office 技巧 - 图7

分隔符提取字符串

image.png

  • 提取第一个分隔符前面的字符:=LEFT(A2,FIND(“/“,A2)-1)
  • 提取最后一个字符:=TRIM(RIGHT(SUBSTITUTE(A2,”/“,REPT(“ “,100)),100))
  • 提取中间字符:=TRIM(MID(SUBSTITUTE(A2,”/“,REPT(“ “,100)),100*N,100)) N表示提取第N+1个分隔符前面的字符

    TEXT() 函数的高级应用

    微信图片_20211014112218.png