就业等位来【这些Excel公式，简单却超实用！】①查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","") ②求和：=SUM(K2:K56) ——对K2到K56这一区域进行求和 ③平均数：=AVERAGE(K2:K56) ——对K2 K56这一区域求平均数 ④排名： =RANK(K2，K$2:K$56) ——对55人的成绩进行排名。 @央视新闻：就业等位来【快转走！常用Excel公式都在这儿了！】求和、平均数、最大值、标准差……如何查找重复值；如何自动标出符合条件的数值……进阶版Excel超实用技巧。存好！有用！

版权声明：如涉及版权问题，请作者持权属证明与本网联系

来源：央视新闻

在使用Excel时，经常会通过编辑公式来进行计算，但使用公式时，如果把含公式的单元格复制粘贴到其他位置，则公式的计算结果可能就会发生改变，另外更改公式中引用单元格的内容或者删除引用的单元格等都会令公式的计算结果发生改变。如果想避免这种情况，通常需要把公式转变成数字，Excel中把公式转变成数字的方法有多种，这里以Excel2007为例，介绍一种个人认为是最快速的方法，供大家参考。

●例如下图表格“合计”列中显示的内容虽然是数字，但如果选中其中的单元格，在编辑栏就会发现实际上该列中的内容都是公式，显示的数字只是公式的计算结果。

●如果想公式转变成数字，一般比较常用的是选择性粘贴的方法。即先选择公式所在的区域，进行复制操作，再在该区域中点击鼠标右键，然后点击右键菜单中的“选择性粘贴”。

●再在选择性粘贴对话框中点击“数值”选项，最后点击“确定”按钮，则所选区域中的公式就转变成数字了。

●上述选择性粘贴的转换方法虽然也不算多复杂，但实际上还有更快的转换方法。如果只是想转换一个单元格中的公式，可以在该单元格中双击鼠标，则该单元格就会处于编辑状态，显示的内容变成了实际的公式。

●此时按键盘的“F9”键，则该单元格中的内容就会立即变成数字，即公式的计算结果。

●如果想把一个区域内的单元格中的公式转变成数字，也有一种更快的方法：先选中要转换的区域。

●选中区域后，将鼠标指针指向区域的边缘，当指针形状变成四个箭头时，按住鼠标右键不放。

●按住鼠标右键不放的同时，向左侧或者右侧移动鼠标，移动一列即可。

●不要松开鼠标右键，再将鼠标移回选择区域的位置。

●鼠标移回选择区域的位置后，松开鼠标右键，此时会弹出一个菜单，点击其中的“仅复制数值”。

●点击后，选择区域内单元格中的公式就都变成数字了。

上述介绍的只是个人认为最快将Excel公式变成数字的方法。当然如果会编辑宏，则制作一个专门的宏会更快的将公式变成数字，但启用宏可能会有一定的安全问题，另外也不适合非相关专业的人员。如果读者还有其他更快的方法，欢迎在评论中提出，大家互相学习。

大年三十了，给大家拜年了，新的一年里万事如意，同时感谢大家一年来的支持和厚爱。祝大家新年快乐，狗年大吉，财源滚滚……

再难的问题，也会有解决的办法，达到最终的目的。但是，解决的方法，思路不同，达到目的过程也会不同。例如，Excel2016新增的下属功能，为我们的工作带来了极大的方便。

一、快速将二维表转换为一维表。

方法：

1、选定表格中的任意单元格。

2、【数据】-【从表格】，单击【表数据的来源】右侧的箭头，选定需要转换的范围（不能包括表头），【确定】。

3、在弹出的新窗口中单击【转换】-【逆透视其他列】-【开始】-【关闭并上载】。

4、调节结构并美化。

二、利用逆透视快速拆分数据。

方法：

1、选定表格中的任意单元格。

2、【数据】-【从表格】，单击【表数据的来源】右侧的箭头，选定需要转换的范围（不能包括表头），【确定】。

3、选中“内容”列，单击【拆分列】-【按分隔符】，在【选择或输入分隔符】下拉列表中选择【空格】-【确定】。

4、按Ctrl键选中所有“内容”列，【转换】-【逆透视列】。

5、删除“属性”列，美化表格。

今天的Excel公式和平时用的不一样，不知道是哪位高人想出的。

1、公式中加备注

【例1】 如果公式中加入一个数字，可能会让别人不清楚它的来历，如：

=SUM(B6:M6)+10000

加上备注后就明白了

=SUM(B6:M6)+10000+N("注：10000是赔付受伤工人款项")

原理：N可以把字符串转换为0，+N()后公式原值不变

2、带名子的excel公式

【例2】如下面的经营分析表中，净利润的公式计算比较复杂，你能一眼看出利润是怎么计算出的吗？

=B8+B14+B20+B26+B32+B38

我们给各个费用所在的单元格分别定义个名称：

选取单元格 - 在名称框中输入名称 - 按enter完成

然后重新设置公式后，会发现单元格引用已自动显示成定义的名称：

=汽车销售+维修+二手车+保险+上牌+加装

公式马上变得一目了然。

如果你想看某个项目的具体利润值，选中项目名称按F9键即可。

很多高手做的Excel表格中，你都可以看到用定义的名称代替单元格，以前觉得很迷惑，学会今天的技巧，以后只需打开 公式 - 名称管理器，就可以看出公式的含义了。

Excel是文字处理、电子表格、幻灯片制作中的电子表格，Excel有大量的公式函数应用选择，极大的方便了数据处理、统计分析和辅助决策操作，所以被广泛地应用于众多领域。如果你从事会计工作或者是跟数据打交道较多的职业，在excel中怎么引用公式是你必须掌握的，下面就跟着小编一起来认识、熟悉一下基本操作吧。

首先大家都知道的，excel数据引用分为四种：excel数据相对引用、excel数据的绝对引用、同文件内excel表间引用、不同文件间的excel引用。下面我来一一阐述。

1、excel数据相对引用，这种最简单，就不详细讲解了。

（1） 需要计算数据一和数据二的平均数与数据三的差，可以输入：=(A2+B2)/2-C2或者=sum(A2:B2)/2-C2，大家千万不要漏掉了前面的“=”哦！

（2）计算数据一、数据二、数据三的最大值与最小值的差，可以输入：=max(a2:d2)-min(a2:d2)，max是求最大值函数，min是求最小值函数。

2、excel数据的绝对引用：计算数据中每个数据与最大值的差

在d2单元格输入=MAX(A2:A6)-A2，我们无法通过拖动，生成d3、d4、d5等下面单元格的数据，我们在d3单元格想要的是=MAX(A2:A6)-A3，我们用“$”来固定那些需要不变的数据，如上面a2单元格的公式就可以写成=MAX(A$2:A$6)-A2。

3、同文件内excel表间引用：同文件内不同表之间的数据引用和计算通常是使用英文状态的引号表名结合而来的。

引用a列数据，可以在a2单元格输入='1'!a2 英文单引号中为表的名字，表和单元格名称间用英文的!分割开来。

4、不同文件间的excel引用：用英文的中括号 [ ]来引用文件，我们将正在操作的文件保存到d盘，默认名字为book1.xls 新建一个excel文件，引用book1.xls文件中第一列的数据：=[book1.xls]1!a2输入完公式，打回车键之后，excel会自动加上文件地址。图例会变为：='d:\[book1.xls]1'!a2。

大家是不是发现其实也并不会太难，关注“智虎科技”头条号，带你掌握更多的知识。

你还在为制作Excel表格而苦恼吗？今天为大家整理了5个常用的Excel函数，均可直接套用，再也不用担心会出现“#N/A”、“#VALUE!” 问题而烦恼了。

1、 判断表格数据（注：函数单元格坐标，要按照实际表格坐标做出调整）

作用：可以更根据条件判断数据，并返回数值。

函数：=IF(B2>60,"及格","不及格")

2、 统计表格相同个数（注：函数单元格坐标，要按照实际表格坐标做出调整）

作用：可以根据条件，统计个数。

函数：= COUNTIF(B2:B9,"1班")

3、 统计表格相同类和（注：函数单元格坐标，要按照实际表格坐标做出调整）

作用：可以根据条件，统计相同类数据和

函数：= SUMIF(C2:C9,"*1班*",B2:B9)

4、 多条件统计人数（注：函数单元格坐标，要按照实际表格坐标做出调整）

作用：可以设置多个条件，统计表格中人数

函数：= COUNTIFS(B2:B9,"男",D2:D9,"1班")

5、 计算条件数值的平均数（注：函数单元格坐标，要按照实际表格坐标做出调整）

作用：根据条件计算出数值的平均数。

函数：= AVERAGEIF(C2:C9,"1班",B2:B9)

感兴趣的朋友赶快动手操作下，（注：大家在套用函数公式的时候要按照实际表格修改下单元格坐标，并修改你想要的函数条件即可完成！）

Excel曾经一度出现了严重Bug，主要有两种比较悲催的情况，首先是这种：

更加悲催的是这种：

言归正传，今天和大家分享一组常用函数公式的使用方法：职场人士必须掌握的12个Excel函数，用心掌握这些函数，工作效率就会有质的提升。

建议收藏备用着，有时间多学习操练下。

目录：数字处理、判断公式、统计公式、求和公式、查找与引用公式、字符串处理公式、其他常用公式等

一、数字处理

01.取绝对值

=ABS(数字)

02.数字取整

=INT(数字)

03.数字四舍五入

=ROUND(数字,小数位数)

二、判断公式

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

公式：C2

=IFERROR(A2/B2,"")

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

05.IF的多条件判断

公式：C2

=IF(AND(A2<500,B2="未到期"),"补款","")

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

三、统计公式

06.统计两表重复

公式:B2

=COUNTIF(Sheet15!A:A,A2)

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

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

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

08.统计不重复的总人数

公式：C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

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

09.按多条件统计平均值

F2公式

=AVERAGEIFS(D:D,B:B,"财务",C:C,"大专")

10.中国式排名公式

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

四、求和公式

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.单条件查找

公式1：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个

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

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

八、其他常用公式

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

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

使用方法：

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

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

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

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

32.中英文互译公式

=FILTERXML(WEBSERVICE("http://fanyi.youdao/translate?&i="&A2&"&doctype=xml&version"),"//translation")

excel中的函数公式千变万化，今天就整理这么多了。如果你能掌握一半，在工作中也基本上遇到不难题了。

建议收藏备用着，有时间多学习操练下。

批量输入公式批量修改公式快速查找公式显示公式部分计算结果保护公式隐藏公式显示所有公式把公式转换为数值显示另一个单元格的公式把表达式转换为计算结果快速查找公式错误原因

1批量输入公式

选取要输入的区域，在编辑栏中输入公式，按CTRL+ENTER即可批量填充公式。

2批量修改公式

如果是修改公式中的相同部分，可以直接用替换功能即可。

3快速查找公式

选取表格区域 - 按Ctrl+g打开定位窗口 - 定位条件 - 公式，即可选取表中所有的公式

4显示公式部分计算结果

选取公式中的要显示的部分表达式，按F9键

按F9键后的结果

想恢复公式，按esc退出编辑状态即可。

5保护公式

选取非公式的填写区域，取消单元格锁定。公式区域不必操作。

设置单元格格式后，还需要保护工作表：审阅 - 保护工作表。

6隐藏公式

隐藏公式和保护公式多了一步：选取公式所在单元格区域，设置单元格格式 - 保护 - 勾选“隐藏” - 保护工作表。

隐藏公式效果：

7显示所有公式

需要查看表中都设置了哪些公式时，你只需按alt+~组合键（或 公式选项卡 - 显示公式）

把公式转换为数值

8把公式转换为数值

公式转换数值一般方法，复制 - 右键菜单上点123（值）

9显示另一个单元格的公式

如果想在B列显示A列的公式，可以通过定义名称来实现。

公式 - 名称管理器 - 新建名称：G =get.cell(6,sheet3!a4)

在B列输入公式=G，即可显示A列的公式。

excel2013中新增了FORMULATEXT函数，可以直接获取公式文本。

10把表达式转换为计算结果

方法同上，只需要定义一个转换的名称即可。

zhi =Evaluate(b2)

在B列输入公式 =zhi即可把B列表达式转换为值

11快速查找公式错误原因

当一个很长的公式返回错误值，很多新手会手足无措，不知道哪里出错了。兰色介绍排查公式错误的技巧，3秒就可以找到错误。

下面兰色做了一个简单的小例子

【例】：如下图所示，单元格的公式返回值错误。要求排查出公式的哪部分出现了错误。

操作方法：

1、 打开单元格左上角绿三角，点“显示计算步骤”

2、在打开的“公式求值”窗口中，求值会自动停在即将出错的位置。这时通过和编辑栏中的公式比对，就可以找出产生错误的单元格。（D7）

如果公式中有多处错误，可以先修正前一次，然后再点显示计算步骤，查找下一处错误。

职场中的人少不了与数据打交道，Excel中的函数公式无疑是提高工作效率的神器，但是Excel中的函数公式太多，一下记不住，但我们如果掌握常用的函数公式，则会提高工作效率。

一、IF+COUNTIF:查找重复值。

方法：

在目标单元格中输入公式：=IF(COUNTIF($E$3:$E$9,E3)>1,"重复","")。

二、IF+COUNTIF：重复两次以上提示。

方法：

在目标单元格中输入公式：=IF(COUNTIF(E$3:E3,E3)>1,"重复","")。

备注：

重复提示和重复两次及以上提示，COUNTIF函数的第一个单数，暨范围不一样，重复提示的范围是当前所有范围的绝对引用。而重复两次以上提示是第一条记录到当前记录的相对引用。

三、TEXT+MID：提取出生日期。

方法：

1、选定目标单元格。

2、在单元格中输入公式：=TEXT(MID(C3,7,8),"0-00-00")。

3、Ctrl+Enter填充。

四、DATEDIF：计算年龄。

方法：

1、选定目标单元格。

2、输入公式：=DATEDIF(D3,NOW(),"y")。

3、Ctrl+Enter填充。

五、IF+MOD+MID：提取性别。

方法：

1、选定目标单元格。

2、输入公式：=IF(MOD(MID(C3,17,1),2),"男","女")。

3、Ctrl+Enter填充。

六、RANK：排名。

方法：

1、选定目标单元格。

2、输入公式：=RANK(E3,$E$3:$E$9,1)。

3、Ctrl+Enter填充。

七、SUMPRODUCT+COUNTIF:中国式排名。

排名过程中，我们经常会遇到成绩或数值相同的情况，如果用RANK函数来时排名，其计算的结果不符合我们的实际需求。于是就有了中国式排名。

方法：

1、选定目标单元格。

2、输入公式：=SUMPRODUCT(($E$3:$E$9>E3)/COUNTIF($E$3:$E$9,$E$3:$E$9))+1。

3、Ctrl+Enter填充。

八、SUMPRODUCT+COUNTIF:统计不重复个数。

方法：

在目标单元格输入公式：=SUMPRODUCT(1/COUNTIF(B3:B9,B3:B9))。

九、INDEX+MATCH+COUNTIF:提取不重复的值。

方法：

1、在目标单元格输入公式：=INDEX($B$3:$B$9,MATCH(,COUNTIF($J$2:J2,$B$3:$B$9),))&""。

2、快捷组合键：Ctrl+Shift+Enter填充。

备注：

填充时，用快捷组合键Ctrl+Shift+Enter，而不是Ctrl+Enter。因为此公式是数组公式。

实际的工作中，填写序列号的地方非常的多，如果手动去处理，非常的麻烦，是否可以用函数公式来解决了？

一、字母序列。

目的：生成字母序列（大写、小写）。

方法：

在目标单元格中输入公式：=CHAR(ROW(A65))、=CHAR(ROW(A97))。

解读：

1、大写字母A的ASCII码为65，利用函数ROW函数获取单元格A65的行号，及字母A的ASCII码，然后利用函数CHAR转为为对应的字母。

2、利用ROW函数获取行号的目的在于变量为动态值。便于填充。

二、星期序列（中文、英文）。

目的：根据日期快速的填充星期。

方法：

在目标单元格中输入公式：=TEXT(A3,"aaaa")、=TEXT(A3,"dddd")。

三、中文大写数字。

目的：根据对应的阿拉伯数字生成中文大写数字。

方法：

在目标单元格总输入公式：=TEXT(A3,"[dbnum2]")。