EXCEL操作

• [[@🎯  快捷键]]

  • [[@常用快捷键]]

• [[@⛳️  操作技巧]]

  • [[@批量填充/删除空白单元格]]

  • [[@保护锁定的单元格，防止别人修改]]

  • [[@删除下面多余空行]]

  • [[@批量创建文件夹]]

  • [[@跨列居中]]

  • [[@定位合并单元格]]

  • [[@其他快捷技巧]]

• [[@⭐️  常用函数]]

  • [[@常用函数：可下载后搜索学习]]

  • [[@VLOOKUP函数：单结果匹配查询]]

  • [[@FILTER函数：多结果匹配查询]]

    • [[@FILTER参数语法]]

    • [[@进阶应用]]

  • [[@MATCH函数：匹配]]

  • [[@INDEX函数：匹配]]

  • [[@TEXTJOIN 函数-连接多个文本]]

  • [[@Trim() 函数-去空格]]

  • [[@ROW()函数-自动序号]]

• [[@💡  复杂组合公式与操作]]

  • [[@IF函数和AND、OR函数组合多条件判断技巧]]

  • [[@VLOOKUP+MATCH，实现批量快速匹配]]

  • [[@INDEX+MATCH，实现快速匹配查询]]

  • [[@SUMPRODUCT，按月度+年度汇总]]

  • [[@TEXT + MID，提取文字格式化]]

  • [[@LEN+SUBSTITUTE，统计某个字符出现的次数]]

  • [[@word引用excel文件内容,并随之改变]]

  • [[@SUMIFS函数求和与不含某条件求和]]

  • [[@其他操作]]

    • [[@照相机]]

• [[@Excel插件对比]]

　　在此知识库中记录财务工作中会用到的，性价比较高的操作技巧与公式运用说明。日常过程中遇到不会的，学习后会整理到此文档。

　　使用软件：WPS 365

　　‍

🎯 快捷键

常用快捷键

　　Ctrl+A ：全选

　　Ctrl+C ：复制

　　Ctrl+X ：剪切

　　Ctrl+V ：粘贴

　　Ctrl+Z ：撤销

　　Alt + =：快速求和

　　Ctrl+S ：保存

　　Ctrl+P ：打印

　　Ctrl+E ：居中

　　Ctrl+H ：替换

　　Ctrl+F ：查找

　　Ctrl+R ：右对齐

　　Ctrl+L ：左对齐

　　Ctrl+鼠标左键：选定不连续的单元格

　　Ctrl+鼠标滚轮：放大缩小

　　Ctrl+W ：快速关闭当前浏览所有网页

　　Ctrl+空格键 ： 中英文切换

　　Tab : 表格向右切换

　　Enter : 表格向下切换

　　CTRL + `：一键展现所有公式

　　双击格式刷：刷多次单元格

　　Ctrl + PgDn：切换到右边的工作表

　　Ctrl + PgUp：切换回左边的工作表

　　函数中 F4：锁定取数单元格

　　快捷键大全

​ ​

⛳️ 操作技巧

批量填充/删除空白单元格

　　定位填充：按快捷键<Ctrl+G>打开“定位”对话框，点击“定位条件”按钮，在弹出的对话框中，定位条件选择“空值”，将所用空值选中，然后在编辑栏中输入“未付”，按<Ctrl+Enter>完成填充。或右键删除，整行/整列。

​ ​

　　筛选填充：先在【开始】选项卡中依次点击【排序和筛选】【筛选】按钮，添加筛选，将空白单元格全部都筛选出来，选中所有单元格在编辑栏中输入“未付”，按<Ctrl+Enter>完成填充。

​ ​

　　查找替换填充：按快捷键<Ctrl+H>打开查找替换窗口，“查找内容”中保持为空，“替换为”中填入要填充的字符，本例中是“未付”，点击“全部替换”按钮完成。

​ ​

　　批量向下、向上填充：

　　将A列的部门向下填充到空白单元格，效果如D1:E11所示。

​ ​

　　1、选中A2:A11，按F5键打开【定位】对话框。单击【定位条件】，打开【定位条件】对话框，选择【空值】，如下图所示。

　　2、输入公式“=A2”，按Ctrl+Enter键即可。

　　具体操作如下图所示：

​ ​

　　3、输入公式“=A5”，按Ctrl+Enter键即可完成向上填充。

保护锁定的单元格，防止别人修改

　　电子表所有单元格默认锁定，可先全选，右键-单元格属性-保护，取消勾选锁定。

​ ​

​ ​

　　选中想保护的单元格，右键-单元格属性-保护，勾选锁定。点击上方“保护工作表”对已设置锁定的单元格进行保护，

​ ​

　　保护权限一般允许其他用户进行“选定、排序、筛选、更改格式”，禁止“增减行列、修改编辑内容”。并设置密码。

​ ​

删除下面多余空行

　　在认为后面没有数据的行中，选中任一行，按Ctrl+Shift+下方向键，则下面的所有行全被选中，右击--删除；

​ ​

　　删除后，会发现滚动条仍然没有变长；

​ ​

　　此时需要先将该工作簿保存，再打开，就会发现滚动条变长了。

​ ​

　　往右选中同样可以删除多余列

批量创建文件夹

　　 按下面的班级和姓名每个学生建一文件夹，这样方便每位学生都有相应的档案明细。可是一个一个新建太麻烦了，如何批量创建

​ ​

　　图 一

　　1，先将班级和姓名利用 “&” 合并在一个单元格中

​ ​

　　2，建立辅助列输入公式。在I2单元格输入公式：="MD "&A2，然后选中I2到I20，按Ctrl+D (或者将鼠标移动到I2单元格右下角的小方块上，当变成黑色十字架时，双击填充)

　　注意MD后面有个英文状态的空格，不要漏了

​ ​

​ ​

　　3、复制一下I2:I20的内容。然后在需要批量建立文件夹的位置，单击右键，新建一个文本文档

​ ​

　　4, 打开新建的文本文档，粘贴刚才的内容后保存，关闭文本文档

​ ​

　　5,右键重命名，修改文本文档后缀名为“.bat”，或者按F2重命名，修改后缀名时会弹出警告对话框，不要管它，选择“是”

​ ​

​ ​

　　6, 双击“新建文本文档.bat”，不到一秒钟的时间，瞬间完成。

​ ​

　　● 需要注意的是 如果你双击时变成了乱码情况,那么要重新将“新建文本文档”的后缀改回txt格式，再打开TXT，左上角文件-另存为一下，看见下图的编码，将它改为ANSI，然后保存，关掉。最后再将“新建文本文档”的后缀改为bat，双击。

​ ​

　　还要强调一点.像这种以多列内容为文件名的,列与列之间的内容中间不能有空格.但在MD后面一定要加一空格,否则是建立不出来的

跨列居中

　　合并单元格居中会影响整列的剪切， ​

　　取消合并，选中标题行多列（原合并单元格），设置单元格格式——对齐——水平对齐——跨列居中。则不再影响整列剪切。

​ ​

定位合并单元格

1. 在查找对话框中，点击“选项”按钮，然后在下拉菜单中选择“格式”。

2. 在格式菜单中，切换到“对齐”选项卡，并勾选“合并单元格”。
   ​ ​

3. 点击“确定”后，点击“查找全部”，所有合并的单元格将被选中并显示在查找结果的列表中。

其他快捷技巧

⭐️ 常用函数

常用函数：可下载后搜索学习

VLOOKUP函数：单结果匹配查询

　　VLOOKUP是一个比较常用的数据查找和匹配的函数，其函数语法可以理解为：

　　=VLOOKUP(匹配对象,匹配区域,匹配第几列的内容,精确匹配或模糊匹配）

　　举个例子：统计学生各科考试成绩，公式可以写为：

　　=VLOOKUP(I2,A:G,2,0)

　　其中2，是需要随着我们统计学科的相对列数进行调整的，统计历史则为5，政治为7；

​ ​

　　还有一种模糊匹配的用法，在之前讲IFS函数的时候已经讲过了，我们来复习一下：

　　用VLOOKUP函数匹配不同销售台次对应的单台提成标准：

　　=VLOOKUP(B2,3:6,3,TRUE)

​ ​

FILTER函数：多结果匹配查询

　　Excel函数FILTER,实现“筛选”功能。

　　函数板是了解一个函数最直观的方式，上面有官方定义“筛选区域或数组”。

​ FILTER函数板 ​

　　案例一，筛选”语文大于70”的数据

　　筛选功能：

​ 筛选 ​

　　FILTER：

=FILTER(A2:D10,B2:B10>70)

​ FILTER ​

　　案例二，筛选“语文大于70”或“数学大于70”的数据。

　　筛选功能无法实现

　　FILTER可以用“+”代表“或”:

=FILTER(A2:D10,(B2:B10>70)+(C2:C10>70))

​ FILTER筛选“语文大于70”或“数学大于70”的数据 ​

---

FILTER参数语法

​ FILTER语法参数 ​

　　第一参数，要筛选的数据区域不一定是整个数据区域，比如只需要知道语文大于70的姓名，只把姓名列作为筛选区域。

=FILTER(A2:A10,C2:C10>70)

​ FILTER筛选 ​

　　第二参数是实现正确筛选的关键，单条件的情况很简单，多条件则需要注意。

　　FILTER多条件筛选时，多个条件同时满足，条件之间用星号（*）连接，多个条件满足其中之一，条件之间用加号（+）连接。

　　筛选满足条件1与条件2与条件3…..的数据

FILTER(区域，(条件1)*(条件2)*(条件3)……)

　　筛选满足条件1或条件2或条件3…..的数据

FILTER(区域，(条件1)+(条件2)+(条件3)……)

　　筛选三科成绩都大于70的姓名

=FILTER(A2:A10,(B2:B10>70)*(C2:C10>70)*(D2:D10>70))

​ 筛选三科成绩都大于70的姓名 ​

　　筛选其中一科大于70的姓名

=FILTER(A2:A10,(B2:B10>70)+(C2:C10>70)+(D2:D10>70))

​ 筛选其中一科大于70的姓名 ​

　　第三参数，如果没有符合筛选条件的结果，FILTER可以返回第三参数。

=FILTER(A2:A10,C2:C10=100,"没有考100的")

​ FILTER返回第三参数 ​

---

进阶应用

　　希望查询学历中包含关键字“科”的所有姓名。不论是本科、专科还是民科，都符合要求。

　　FILTER(A2:A16,ISNUMBER(FIND(F2,C2:C16)))

​ ​

　　‍

　　‍

　　两科成绩都大于90的评选为优秀学生：

=TEXTJOIN(",",1,FILTER(A2:A10,(B2:B10>90)*(C2:C10>90)))

　　FILTER筛选后用TEXTJOIN连接。

​ FILTER筛选后用TEXTJOIN连接 ​

　　查询指定姓名指定科目的成绩。

=FILTER(FILTER(B2:D10,A2:A10=F2),B1:D1=G2)

　　内层的FILTER筛选“小吴”的成绩，注意，它返回的是一个数组，包括小吴的三科成绩。外层FILTER在此基础上筛选“语文”

​ 查询指定姓名指定科目的成绩 ​

　　对指定单位两个日期之间的金额求和:

=SUM(FILTER(C2:C17,(A2:A17=E3)*(B2:B17>=F3)*(B2:B17<=G3)))

　　FILTER筛选出满足条件的数据后求和，条件有三个，需要同时满足。

​ 三个条件同时满足 ​

　　给重庆武汉，或室外工作的发放高温补贴,用FILTER筛选出名单。

=FILTER(A2:A12,(B2:B12="重庆")+(B2:B12="武汉")+(ISNUMBER(FIND("室外",C2:C12))))

　　三个条件：

　　在重庆，(B2:B12="重庆")

　　在武汉，(B2:B12="武汉")

　　室外工作，(ISNUMBER(FIND("室外",C2:C12)))

　　三个条件满足其中之一即可，用“+”连接。

​ 三个条件满足其中之一 ​

　　‍

　　‍

MATCH函数：匹配

　　MATCH函数查找的是指定项在指定区域中的位置，返回的是相对的位置数字，其基本语法为：

　　=MATCH(指定字段,指定区域,精确查找0）

　　(ps:当然查找方式还有1（小于），要求先对指定区域的数值进行升序排列，查询的是小于指定值的数量；对应的查询方式还有-1（大于），要先对指定区域的数值进行降序排列，查询的是大于指定值的数量。)

　　查找对应的学科在成绩表中的列数，公式为：

　　=MATCH(JAG$1,0)

　　得出的结果为数学在我指定的A1:G1区域的第3列，历史在第5列，政治在第7列

​ ​

INDEX函数：匹配

　　下图中A2单元格就是这个单元格区域中的第2行第1列的单元格

​ ​

　　而INDEX函数的作用就是返回表格或区域中的值或值的引用。

　　如果想要返回指定单元格或单元格数组的值，则使用语法1

　　INDEX(array,row_num,[column_num])

　　点击图片可查看完整电子表格

　　用INDEX函数来获取下图单元格区域中B3单元格中的内容

​ ​

　　我们可以这样使用公式：=INDEX(B2:D4,2,1)，通过这3个参数就可以得出一个结果，也就是B3单元格的内容。

TEXTJOIN 函数-连接多个文本

　　TEXTJOIN 函数的语法：TEXTJOIN(分隔符, ignore_empty, text1, [text2], …)

　　参数说明：

• delimiter：指定分隔符，可以是任意字符串。如果 delimiter 为空字符串 ("")，则函数会使用逗号作为默认分隔符。

• ignore_empty：一个可选参数，指定是否忽略空单元格。如果设置为 TRUE（或者1），则忽略空白单元格。默认值为 FALSE。

• text1，text2，...：要连接的文本单元格或数组。

　　例如，=TEXTJOIN (" "，TRUE， "The"， "sun"， "will"， "come"， "up"， "tomorrow"。) ") 将返回 The， sun， will， come， up， tomorrow。

　　示例 1：连接同列单元格的文本

　　可以使用 TEXTJOIN 函数公式：=TEXTJOIN ("， "， TRUE， A2：A8)

　　结果：美元、澳大利亚元、中国货币、香港特别行政区、以色列谢克尔、韩国元、俄罗斯卢布

​ ​

　　示例2：连接相邻两列单元格的文本

　　公式：=TEXTJOIN ("， "， TRUE， A2：B8)

　　结果：a1、b1、a2、b2、a4、b4、a5、b5、a6、b6、a7、b7

　　如果 ignore_empty=FALSE，则结果为： a1、b1、a2、b2、a4、b4、a5、b5、a6、b6、a7、b7

​ ​

　　示例 3：更高级的文本合并

　　公式：=TEXTJOIN(A8:D8,1,A2:D7)

　　结果：Tulsa，OK，74133，US;Seattle，WA，98109，US;Iselin，NJ，08830，US;FortDaleerdale，FL，33309，US;Tempe，AZ，85285，US;end

​ ​

Trim() 函数-去空格

　　这个函数可以轻松把单元格内容里的空格去掉。例如=trim(A1)，如果A1单元格里有空格，这个公式会只显示其中非空格的内容。

ROW()函数-自动序号

　　row（）函数为返回当前行，当有标题占据1行时，则下方序号即为“row（）-1”，标题占据2行时，则下方序号即为“row（）-2”

💡 复杂组合公式与操作

IF函数和AND、OR函数组合多条件判断技巧

　　一、IF+AND：同时满足多个条件

　　目的：将“上海”地区的“男”通知标识为“Y”。

​ ​

　　方法：

　　在目标单元格中输入公式：=IF(AND(D3="男",E3="上海"),"Y","")。

　　解读：

　　1、AND函数的语法：AND(条件1,=标准1，条件2=标准2……条件N=标准N）。如果每个条件和标准都相等，则返回TRUE，否则返回FALSE 。

　　2、用IF函数判断AND函数的返回结果，如果为TRUE，则返回“Y”，否则返回""。

　　二、IF+OR:满足多个条件中的一个即可。

　　目的：将性别为“男”或地区为“上海”的标记为“Y”

​ ​

　　方法：

　　在目标单元格中输入公式：=IF(OR(D3="男",E3="上海"),"Y","")。

　　解读：

　　1、OR函数的语法结构为：(条件1,=标准1，条件2=标准2……条件N=标准N）。如果任意参数的值为TRUE，则返回TRUE ,当所有条件为FALSE时，才返回FALSE。

　　2、用IF函数判断OR函数的返回结果，如果为TRUE,则返回“Y”，否则返回""。

VLOOKUP+MATCH，实现批量快速匹配

　　若无法直接理解，可先复习一下VLOOKUP和MATCH函数的用法

　　理解VLOOKUP和MATCH的用法，用MATCH函数返回的列数值替代VLOOKUP函数中第三个参数列数

　　=VLOOKUP(AG1,1:1,0),0)

​ ​

　　要注意各种绝对引用的用法，锁住对应的取数单元格和取数区间，然后就可以Ctrl+C、Ctrl+V了

INDEX+MATCH，实现快速匹配查询

　　使用INDEX和MATCH函数从“全体员工信息表”中查找信息

​ ​

　　查找“优秀员工信息表”中的姓名对应的身份证号。

​ ​

　　思路（以赖某玲为例）：①在全体员工信息表中查找赖某玲所在行（可以使用MATCH函数运算之后得出）；②前面MATCH函数运算之后得到的行数和我们已知的这个红线框选的单元格区域中第1列交叉之处即为需要查找的赖某玲对应的身份证。

　　据此，我们可以写成如下函数：

　　=INDEX(B3:C37,MATCH(G3,C3:C37,0),1)

　　现在来解析一下这个公式是如何得出的，对于嵌套函数的使用和分解，请查阅底部往期精彩内容。

　　在这个嵌套公式中，我们分成两步走。

　　INDEX的3个参数如下图中①、②、③所示。②中的MATCH函数运算后会得到一个数字，也就是行数，通过②中的行数和③中的这个参数1（设置为1是由于在B3:C37这个单元格区域内，身份证列是在第1列）就可以在①中的这个区域确定具体的单元格，从而得到相应的值。

​ ​

　　其中的MATCH函数可以如下分析。G3也就是我们要查找的赖某玲所在单元格，而要查找的②所在的区域就是全员表中姓名列所在区域，我们只要知道她在姓名列中的第几行，就能够根据身份证列的这一行得到赖某玲的身份证号码。第③个参数代表的是精确查找这个内容。

​ ​

　　当我们成功计算出第一个结果后，便可以拖拉单元格右下角，批量完成其他优秀员工的身份证号填充。但是要注意的事，如果我们没有使用绝对引用将单元格区域固定住就会出现拖拉单元格后，单元格区域也随之偏移的问题。

SUMPRODUCT，按月度+年度汇总

　　按月度汇总：

　　将光标放置在F2单元格，输入公式：

　　=SUMPRODUCT((YEAR(2:278)=FAAE2)*2:278)

　　此公式的意思是，先用Year函数和Month函数判断A列的销售日期的月份与年份。

　　然后与F列指定的年份和E列指定的月份进行对比，将结果乘以C列的销售数量。

　　最后用SUMPRODUCT函数求和。

　　将光标放在F2右下角，呈十字形向右和向左下拉填充公式。

　　即可快速计算出此表的月度销售总数量。

​ ​

　　按年度汇总：

　　将光标放置在J2单元格，输入公式：

　　=SUMPRODUCT((YEAR(2:278)=CC$278)

　　此公式的意思是，首先使用YEAR函数判断A列销售日期的年份。

　　然后与I2单元格的年份进行对比，再用对比出来的年份结果乘以C列的销售数量。

　　最后使用SUMPRODUCT函数求和，计算出此年的销售总数量。

​ ​

　　按年度+月度汇总:

　　=SUMPRODUCT((YEAR(B2:B20000)=MONTH(A2))*D$2:D20000)

TEXT + MID，提取文字格式化

　　在身份证号中提取出生日，=TEXT(MID(A2,7,8),"0000-00-00")

​ ​

LEN+SUBSTITUTE，统计某个字符出现的次数

1. 在表格内输入一串字符，这里统计这串字符里字符D一共出现了几次。

​ ​

1. 在B1单元格内输入函数公式”=SUBSTITUTE(A1,"D","")“将A1单元格内字符串D转换为空值。

​ ​

1. 在C1单元格内输入函数公式”=LEN(A1)-LEN(B1)“，用原来的字符个数减去转换后的字符个数得出D一共出现的次数。

​ ​

1. 按下回车，求出字符D一共在字符串中出现6次。

​ ​

1. 也可以把两个函数公式合并成一个函数公式为”=LEN(A1)-LEN(SUBSTITUTE(A1,"D",""))“。

​ ​

word引用excel文件内容,并随之改变

　　打开excel表格，选中需要复制的表格，点击复制。

​ ​

　　在word文档中，鼠标右击点击粘贴-选择性粘贴。

​ ​

　　在选择性粘贴对话框，勾选粘贴链接，再选择WPS表格对象或无格式文本，其中WPS表格为自带表格设置属性，如边控、颜色等，不需要格式的，可选择无格式文本。点击确定。

​ ​

　　在word中插入了表格内容。以”WPS表格对象“为例。

​ ​

　　然后在excel表格中更改数据。

​ ​

　　在word中插入的表格也随着更改数据。

​ ​

SUMIFS函数求和与不含某条件求和

　　例如，求华东地区销售公式为SUMIFS(C,B,"华东")

　　我想求不含华东地区的销售，我的公式这样写：

　　=SUM(C)-SUMIFS(C,B,"华东")

​ ​

　　如果遇到了更为复杂的条件，按照上述逻辑，公式会更为复杂。

　　例如：

​ ​

　　其实可以直接写为：

　　=SUMIFS(C,B,"<>华东")
"<>"表示不等于

​ ​

　　图3 简单的做法

　　=SUMIFS(C,B,"<>华东*")

　　注意这里多了一个“”，表示通配符，不管后面是什么，只要开头包含“华东”，就自动跳过这部分不求和。号代表的是任意多个字符，适用于文本，对于数字无效。

​ ​

　　图4 复杂条件下的简单做法

其他操作

照相机

　　完美截图，可实时变动。选中需要拍照的单元格区域，点击照相机，再次选中需要拍照的单元格区域，完成。

​ ​

　　拍照区域内容变动后，拍照结果实时更新变动

​ ​

　　‍

　　‍

Excel插件对比

主键	一键转万元	一键转负数	Excel目录导航	收费
慧办公	有	无	有	65一台电脑
方方格子	有	有	有	45一年，
Excel必备工具箱	有	有	有
易用宝	无	无	无	0
不坑盒子
易财审	无	有	无	0