转载自百家号作者：Excel函数公式

函数和公式，是Excel中的灵魂，也是非常实用的技巧，如果你对常用的函数公式能够熟练地掌握，对于提高工作效率将会有很大的帮助！

一、“重复”类。

1、查找重复内容，重复即提示。

方法：

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

解读：

先用Countif函数统计当前单元格的值在E列中的数量，然后用If函数进行判断，如果大于1，返回“重复”，否则返回空值。

2、重复内容首次出现时不予提示。

方法：

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

解读：

将本示例中的公式和上一示例中的公式进行对比，结果只是参数做了修改，将原来的范围E:E变为E$3：E3，而统计结果却不相同，其实范围E:E是绝对引用，暨统计范围为E列的所有单元格，而为了达到“重复内容”首次出现时不予提示的目的，我们将统计范围定位从开始单元格到当前单元格，所以，重复内容首次出现时不予提示。

3、重复内容首次出现时提示重复。

方法：

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

解读：

1、从公示的计算过程请参阅示例2的解读。

2、此公式在应用时要区分对待，如果重复内容进出线一次，则重复内容仅在首次出现时提示，如果有多次重复，仅最后一次不提示重复。

二、身份证号类。

1、根据出生年月计算年龄。

方法：

在目标单元格中输入公式：=DATEDIF(E3,TODAY(),"y")。

解读：

Datedif函数为系统隐藏函数，其主要作用为按指定的统计方式统计两个日期之间相差的值。其中“y”的意思为按年统计。

2、根据身份证号码提取出生年月。

方法：

在目标单元格中输入公式：=TEXT(MID(D3,7,8),"00-00-00")。

解读：

1、首先利用Mid函数提取D3字符串中从第7位开始，长度为8的字符。

2、利用Text函数将其转换为“00-00-00”的格式。

3、根据身份证号码判断性别。

方法：

在目标单元格中输入公式：=IF(MOD(MID(D3,17,1),2),"男","女")。

解读：

1、首先用Mid函数提取D3字符串中从17位开始，长度为1，暨第17位的数字。

2、利用Mod函数求余。

3、利用If函数判断，余数为1，暨第17位的数为奇数，则返回“男”、如果余数为0，暨第17位的数为偶数，则返回“女”。

三、常用汇总函数。

方法：

在对应的目标单元格中输入公式：=SUM(D:D)、=MAX(D:D)、=MIN(D:D)、=AVERAGE(D:D)、=COUNTA(B3:B9)。

四、成绩排名。

1、美式排名。

方法：

在目标单元格中输入公式：=RANK.EQ(D3,D:D)。

解读：

从排序的结果来看，出现了“跳跃”的情况，如果你对这一结果不满意，请继续往下阅读。

2、中国式排名。

方法：

在目标单元格中输入公式：=SUMPRODUCT(($D$3:$D$9>D3)/COUNTIF($D$3:$D$9,$D$3:$D$9))+1。

解读：

从排序的结果中我们可以看出，名次没有“跳跃”的情况，是不是更符合我们的实际需要了？

五、个数类统计。

1、单条件计数。

方法：

在目标单元格中输入公式：=COUNTIF(D3:D9,">="&G3)。

解读：

Countif函数为单条件计数函数，其语法结构为=Countif（统计范围，统计条件）。

2、连续范围计数。

方法：

1、在目标单元格中输入公式：=FREQUENCY(D3:D9,G3:G6)。

2、Ctrl+Shift+Enter填充。

解读：

1、函数Frequency的作用为：以一列垂直数组返回一组数据的频率分布。公式中D3:D9为数据，G3:G6为垂直数组。

2、因为要循环多次计算，所以填充时用Ctrl+Shift+Enter，而不是常规的Ctrl+Enter哦！

3、结果解读：30分以下的个数为0；31-40的个数为1；41-50的个数为：0；51-60的个数为：3；61-70的个数为：3人。

3、单条件统计平均值。

方法：

在目标单元格中输入公式：=AVERAGEIF(C3:C9,G3,D3:G9)。

解读：

函数Averageif为按条件计算平均值函数，语法结构为：=Averageif(条件范围，条件，数值范围）。

4、多条件统计平均值。

方法：

在目标单元格中输入公式：=AVERAGEIFS(D3:D9,C3:C9,G3,D3:D9,">="&H3)。

解读：

Averageif是函数为多条件计算平均值，其语法结构为：=Averageifs(数值范围，条件1范围，条件1，条件2范围，条件2……条件N范围，条件N)。

六、统计不重复值。

方法：

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

七、提取不重复的内容。

方法：

1、在目标单元格中输入公式：=INDEX(E:E,2+MATCH(,COUNTIF(F$2:F2,E$3:E$9),))&""。

2、用Ctrl+Shift+Enter填充。

3、拖动填充柄填充其他单元格。

结束语：

本文针对不同的情况，对常用的函数公式进行了罗列，对简单的用法也进行了讲解和示范，如果能够熟练掌握，对于工作效率的提高绝对不是一点点哦！

学习过程中如果有不同的见解，欢迎大家在留言区留言讨论哦！

Excel就像一把天山寒铁淬炼而成的杀猪刀， 本身已经很厉害，但具体有多厉害取决于用它的人。

今儿Q来和大家伙儿一起了解

最实用Excel公式大全！

以后做表格不再求人！

中秋的正确打开方式 | 你送祝福我送礼，月饼礼盒赠爸妈

如有其他想要学习的职场技能，可后台留言呦

欢迎关注求职者

一个暖心的求职平台

只为你能成功拿到心仪的offer

我们将为你提供最新职场资讯，各类笔面求职技巧，海量简历模板，最全招聘及实习信息，职场测试等内容。

下方查看历史精彩

中秋的正确打开方式 | 你送祝福我送礼，月饼礼盒赠爸妈

能好好睡觉的年轻人都是潜力股，你昨天几点睡？

TED演讲：为什么你应该知道同事的薪资？

教育部发话：大学生千万别做这件事！

Excel有强大的计算功能，平时我们计算的数字加减乘除运算一般是不带单位的，很容易计算，直接利用excel自带的函数功能就好了。那如果碰到带单位的如何计算呢？今天就教大家一个利用公式计算带单位的数据相加之和，这个公式能计算带单位的数字之和，并且数据有变化时，他们的和也会同是变化。非常省心，效率很高。下面就把详细把过程讲一下，大家掌握用公式计算带单位的数据能大大提升工作效率，一起来看一下。

1、以下的表格是带单位的数据，求A1到A5单元格之和

2、在表格空白处任意位置，这里我们先择A6单元格，输入公式 =SUM(SUBSTITUTE(A1:A5,"元","")*1)&"元"。记住此处要同时按下ctrl+shift+enter三个键就可以得到计算结果。注意：如果只按下enter键，则公式会报错

3、这里我们得到带单位的数据之和是15元。

4、当我们把原来的带单位的数据，A5单元格内容换成6元时

5、则得到的带单位的数据之和是16元，实现了数据之和自动更新

总结：通过上面用公式实现了带单位的数据求和自动计算，并随着原数据变化时，和也同时变化，省时省力，效率非常高。快去试试吧！

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

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

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

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

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

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

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

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

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

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

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

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

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

首先，给各位朋友说一句，我用的是WPS表格，wps表格和excel的功能基本上差不多，我感觉用WPS快些。

wps表格(excel)的自动求差(减法计算和公式)教程：

一、直接在单元格输入【=】，然后鼠标选中单元格，再输入【-】，最后按回车键

计算出来的差如图：

二、怎么让一列计算呢，可以直接复制公式，也可以：把鼠标放到单元格右下角会有一个+号 ，我们可以往下拖动，就会出现相应的公式了。

这是最简单的计算，用不到公式函数，但是有些新手朋友还是不会的，所以，我还是把教程写出来，教大家一下。给大家看一段我用的较为复杂的跨表多条件求各函数：=SUMIFS(日报表!$I:$I,日报表!$A:$A,$C$2,日报表!$D:$D,$A11)

关注我，以后我会慢慢的都把教程以最简单的方式写出来。

转载自百家号作者：Excel函数公式

在实际的工作中，经常会遇到函数公式是正确的，但是计算结果却不正确的情况，遇到此类情况，往往是一脸的无助……今天我么就针对函数公式完全正确但得不到正确结果的情况做一下分享和解读。

一、输入公式后不计算，却在单元格内显示公式本身。

如图所示，在目标单元格中输入公式：=C3*D3后，并未得到正确的计算结果，而是显示公式本身。

原因：

将目标单元格的格式设置成了文本类型。

解决办法：

方法：

1、选定目标单元格。

2、删除原有公式，并设置单元格格式为【常规】。

3、输入公式并填充。

二、修改数据源后结果不自动更新。

如图所示，第一次计算可以得到正确的计算结果，但是修改数据源之后，销售额并不自动更新。这很显然不符合我们的实际应用。

原因：

目标单元格的【计算选项】为【手动】模式。

方法：

1、选定目标单元格，【公式】-【计算选项】-【自动】。

三、目标单元格格式为【常规】，可单元格内还是显示公式。

从图中可以看出，目标单元格的格式为【常规】，且通过分列也无法显示计算结果，很显然不是单元格格式的问题。

原因：

目标单元格设置了【显示公式】。

1、选中目标单元格，【公式】-【显示公式】。

结束语：

公式正确但得不到计算结果的情况，主要有以上三种表现，对于使用Excel的人来说具有极大的帮助作用和指导意义，同时对于提高工作效率具有很大的帮助。

如果大家有更多的实用技巧，欢迎大家在留言区讨论交流哦！

在实际的工作中，经常会遇到函数公式是正确的，但是计算结果却不正确的情况，遇到此类情况，往往是一脸的无助……今天我么就针对函数公式完全正确但得不到正确结果的情况做一下分享和解读。

一、输入公式后不计算，却在单元格内显示公式本身。

如图所示，在目标单元格中输入公式：=C3*D3后，并未得到正确的计算结果，而是显示公式本身。

原因：

将目标单元格的格式设置成了文本类型。

解决办法：

方法：

1、选定目标单元格。

2、删除原有公式，并设置单元格格式为【常规】。

3、输入公式并填充。

二、修改数据源后结果不自动更新。

如图所示，第一次计算可以得到正确的计算结果，但是修改数据源之后，销售额并不自动更新。这很显然不符合我们的实际应用。

原因：

目标单元格的【计算选项】为【手动】模式。

方法：

1、选定目标单元格，【公式】-【计算选项】-【自动】。

三、目标单元格格式为【常规】，可单元格内还是显示公式。

从图中可以看出，目标单元格的格式为【常规】，且通过分列也无法显示计算结果，很显然不是单元格格式的问题。

原因：

目标单元格设置了【显示公式】。

1、选中目标单元格，【公式】-【显示公式】。

结束语：

公式正确但得不到计算结果的情况，主要有以上三种表现，对于使用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）

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

问题来源：

一位毕业几年的学生，向我请教一个问题：一组数据求和，就是得不到正确结果，他把数据传给韩老师，韩老师一看，那组数据，既有真数值，又有“文本型数值“，文本是不可能直接参与计算的。

韩老师用一组简单的数据给大家讲一讲如何对“文本型数值”计算，以求和为例。

关键步骤提示：

第一种方法：选择性粘贴

第一种方法：数据—分列

第三种方法：VALUE函数转换

第四种方法：SUMPRODUCT函数

输入公式“=SUMPRODUCT(E2:E11+0)”，因为是数据函数，公式结束时，一定要按“Ctrl+Shift+Enter”。

EXCEL表格的公式问题 , 在EXCEL表格里面设置的自动求和公式等，突然不进行运算，复制时总是显示复制的数值，也不进行自动累加了。但双击进入单元格后，再按一下回车就自动求和了，可在输入其他数据又不变了。

如何解决那？

2003版的在工具-选项-公式中修改设置

2010版的在文件-选项-公式中修改设置