3小时整理了20个Excel小技巧，动图演示，让你一学就会

好久没跟大家分享Excel动图小技巧了，今天跟大家分享20个Excel动图小技巧，都是一些粉丝经常提问到的问题，大家可以先收藏，后期遇到类似的问题，直接套用即可，废话不多说，让我们直接开始吧

想要从零学习Excel，这里↓↓

1.取消合并批量填充

先吧，随后按下快捷键F5调出定位，在【定位条件】选择【空值】点击确定

紧接着在编辑栏中输入等于号，然后点击第一个数据就是A3单元格，最后按下【Ctrl+回车】

2.下拉菜单自动更新

把数据源设置为超级表后，再使用【数据验证】来设置下拉菜单，在数据源中新增数据，下拉菜单也会实现自动更新

3.批量加减乘除

实现这个效果，需要用到【选择性粘贴】，比如现在我们想要为所有人的考核得分都增加5分

需要先输入一个5，然后复制他，随后选择需要增加分数的区域，点击鼠标右键，在选择性粘贴的运算中选择【加】即可

它可以进行四则运算，需要根据自己的需求来设置

4.禁止录入重复数据

需要选择禁止录入重复数据的区域，然后打开【数据验证】，将【允许】设置为【自定义】，在下方将公式设置为=COUNTIF(A:A,A4)=1，在这里A4就是第一个数据的位置

5.禁止合并单元格

合并单元格是数据统计的毒瘤，想要禁用它也非常的简单，只需按下Ctrl+T就可以将普通表转换为超级表，在超级表中合并单元格是无法使用的

6.更正E+显示

首先我们选择需要更正的数据区域，然后按快捷键Ctrl+1调出格式窗口，随后点击【自定义】在类型中输入一个0然后点击确定即可

需要注意的是如果数据超过了15位，15位之后的数据会被舍去，且无法恢复

7.将负数标注为红色

选择数据区域，按下【ctrl+1】调出格式窗口，然后找到【数值】，在负数中选择最后的一种显示样式，再设置保留小数位数0即可

8.数字前面显示0

首先需要选择对应的数据区域，然后按下快捷键【Ctrl+1】调出设置窗口，点击【自定义】将类型设置为：000000，这样的话输入23就会显示000023

9.快速输入性别

选择需要输入性别的区域，然后按下Ctrl+1调出格式窗口，点击自定义，在类型中输入：[=1] "男";[=2]"女"

现在输入1就是显示男，输入2就会显示女

10.一键调出工作表目录

只需要在工作表名称的左边点击一下鼠标右键就能调出Excel目录，点击工作表名称就能快速的跳转到对应的工作表，这个很多人都不知道，用起来还是比较方便的

11.根据颜色计数求和

按下Ctrl+F调出查找，然后找到在格式中选择【从单元格选取格式】，找到想要统计的单元格颜色，然后点击全部查找，再按下快捷键Ctrl+A选择所有，在表格的右下角就会看到求和、计数、平均等结果

12.批量删除空白行

选中数据区域，按下Ctrl+F调出查找，直接点击全部查找，随后按下Ctrl+A选择所有的空白单元格，点击鼠标右键删除整行即可

13.不打印颜色

按下【Ctrl+P】进入打印预览，然后找到【工作表】勾选【单色打印】点击确定即可，就会仅仅打印黑色，而不打印其他颜色了

14.打印标题行

首先需要d点击【页面设置】找到【打印标题】在【顶端标题行】中选择需要打印的标题行，然后点击确定即可，按下快捷键Ctrl+P进入打印预览，就会看到每一页都有标题行了

15.快速更改引用方式

选中数据后，可以按下【F4键】来快速更改单元格的引用方式，这样就不用再一个一个的输入美元符号了

16.一键求和

只需选择对应的数据区域，然后按下【Alt+=】就能实现一键求和

17.快速核对2列数据

选中这2列数据，然后按下快捷键【Ctrl+\】，就会选中右侧的差异数据，随后为其填充一个颜色即可

18.快速去除小数点

只需选择数据，然后按下快捷键：Ctrl+shift+1，在这里数据会进行四舍五入

19.快速拆分数据

只需在旁边输入想要提取的数据，然后按下快捷键【Ctrl+E】即可将数据提取出来，它是需要版本支持的

Excel最低2013

WPS最低2019

20.快速录入时间与日期

录入日期：ctrl+；

录入时间：ctrl+shift+；

在一个单元格中是可以同时使用这2个快捷键的，这样既可以录入日期，还能录入时间

以上就是今天分享的20个Excel小技巧，怎么样？你觉得实用吗？

我是Excel从零到一，关注我，持续分享更多Excel技巧

32个Excel函数公式大全，直接套用，快速提升工作效率！收藏学习！

阅读前请点击右上角“关注”，每天免费获取职场文化及管理知识。职场千里马文化，只做职场干货，完全免费分享！

今天和大家分享一组常用函数公式的使用方法。

职场人士必须掌握的32个Excel函数，用心掌握这些函数，工作效率就会有质的提升。

建议收藏备用，有时间多练习一下，才能迅速掌握函数命令，工作起来得心应手。

一、判断公式

1、把公式返回的错误值显示为空

公式：C2=IFERROR(A2/B2,\"\")

说明：如果是错误值则显示为空，否则正常显示。

把公式返回的错误值显示为空

2、IF的多条件判断

公式：C2=IF(AND(A2<500,B2=\"未到期\"),\"补款\",\"\")

说明：两个条件同时成立用AND,任一个成立用OR函数。

IF的多条件判断

二、统计公式

3、统计两表重复

公式：B2=COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

统计两表重复

4、统计年龄在30~40之间的员工个数

公式：=FREQUENCY(D2:D8,{40,29})

统计年龄在30~40之间的员工个数

5、统计不重复的总人数

公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明：用COUNTIF统计出每人的出现次数，用1除的方式把出现次数变成分母，然后相加。

统计不重复的总人数

6、按多条件统计平均值

F2公式：=ERAGEIFS(D:D,B:B,\"财务\",C:C,\"大专\")

按多条件统计平均值

7、中国式排名公式

公式：=SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))

二、数字处理

8、取绝对值：=ABS(数字)

9、数字取整：=INT(数字)

10、数字四舍五入：=ROUND(数字,小数位数)

四、求和公式

11、隔列求和

公式：H3=SUMIF($A$2:$G$2,H$2,A3:G3)

或=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

说明：如果标题行没有规则用第2个公式

隔列求和

12、单条件求和

公式：F2=SUMIF(A:A,E2,C:C)

说明：SUMIF函数的基本用法

13、单条件模糊求和

公式：详见下图

说明：如果需要进行模糊求和，就需要掌握通配符的使用，其中星号是表示任意多个字符，如\"*A*\"就表示a前和后有任意多个字符，即包含A。

单条件模糊求和

14、多条求模糊求和

公式：C11=SUMIFS(C2:C7,A2:A7,A11&\"*\",B2:B7,B11)

说明：在sumifs中可以使用通配符*

多条求模糊求和

15、多表相同位置求和

公式：b2=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。

多表相同位置求和

16、按日期和产品求和

公式：F2=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

说明：SUMPRODUCT可以完成多条件求和

按日期和产品求和

五、查找与引用公式

17、单条件查找

公式：C11=VLOOKUP(B11,B3:F7,4,FALSE)

说明：查找是VLOOKUP最擅长的，基本用法

单条件查找

18、双向查找

公式：=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

说明：利用MATCH函数查找位置，用INDEX函数取值

双向查找

19、查找最后一个符合条件记录

公式：详见下图

说明：0/(条件)可以把不符合条件的变成错误值，而lookup可以忽略错误值

查找最后一个符合条件记录

20、多条件查找

公式：详见下图

说明：公式原理同上一个公式

多条件查找

21、指定非空区域最后一个值查找

公式：详见下图

说明：略

指定非空区域最后一个值查找

22、区间取值

公式：详见下图

说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。

区间取值

六、字符串处理公式

23、多单元格字符合并

公式：c2=PHONETIC(A2:A7)

说明：Phonetic函数只能对字符型内容合并，数字不可以。

多单元格字符合并

24、截取除后3位之外的部分

公式：=LEFT(D1,LEN(D1)-3)

说明：LEN计算出总长度,LEFT从左边截总长度-3个

截取除后3位之外的部分

25、截取 - 之前的部分

公式：B2=Left(A1,FIND(\"-\",A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

截取 - 之前的部分

26、截取字符串中任一段

公式：B1=TRIM(MID(SUBSTITUTE($A1,\" \",REPT(\" \",20)),20,20))

说明：公式是利用强插N个空字符的方式进行截取

截取字符串中任一段

27、字符串查找

公式：B2=IF(COUNT(FIND(\"河南\",A2))=0,\"否\",\"是\")

说明：FIND查找成功，返回字符的位置，否则返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是否成功。

28、字符串查找一对多

公式：B2=IF(COUNT(FIND({\"辽宁\",\"黑龙江\",\"吉林\"},A2))=0,\"其他\",\"东北\")

说明：设置FIND第一个参数为常量数组，用COUNT函数统计FIND查找结果

七、日期计算公式

29、两日期间隔的年、月、日计算

A1是开始日期（2011-12-1），B1是结束日期(2013-6-10)。

计算：

相隔多少天？=datedif(A1,B1,\"d\") 结果：557相隔多少月? =datedif(A1,B1,\"m\") 结果：18相隔多少年? =datedif(A1,B1,\"Y\") 结果：1不考虑年相隔多少月？=datedif(A1,B1,\"Ym\") 结果：6不考虑年相隔多少天？=datedif(A1,B1,\"YD\") 结果：192不考虑年月相隔多少天？=datedif(A1,B1,\"MD\") 结果：9

datedif函数第3个参数说明：

\"Y\" 时间段中的整年数。\"M\" 时间段中的整月数。\"D\" 时间段中的天数。\"MD\" 天数的差。忽略日期中的月和年。\"YM\" 月数的差。忽略日期中的日和年。\"YD\" 天数的差。忽略日期中的年。

30.扣除周末的工作日天数

公式：

C2=NETWORKDAYS.INTL(IF(B2<DATE(2015,1,1),DATE(2015,1,1),B2),DATE(2015,1,31),11)

说明：返回两个日期之间的所有工作日数，使用参数指示哪些天是周末，以及有多少天是周末。周末和任何指定为假期的日期不被视为工作日

扣除周末的工作日天数

八、其他常用公式

31、创建工作表目录的公式

把所有的工作表名称列出来，然后自动添加超链接，管理工作表就非常方便了。

使用方法：

第1步：在定义名称中输入公式：

=MID(GET.WORKBOOK(1),FIND(\"]\",GET.WORKBOOK(1))+1,99)&T(NOW())

创建工作表目录的公式步骤1

第2步：在工作表中输入公式并拖动，工作表列表和超链接已自动添加

=IFERROR(HYPERLINK(\"#'\"&INDEX(Shname,ROW(A1))&\"'!A1\",INDEX(Shname,ROW(A1))),\"\")

创建工作表目录的公式步骤2

32、中英文互译公式

公式：=FILTERXML(WEBSERVICE(\"/d/file/gt/2023-09/uvr2x0agjay.html