收藏的Excel函数大全公式再多，几天不用也会忘记。怎么才能不忘？你需要了解公式的运行原理。兰色今天不再推送一大堆函数公式，而是根据提问最多的问题，精选出6个实用的，然后详细的解释给大家。

1、计算两个时间差

=TEXT(B2-A2,"[m]分钟")

兰色注：

Text函数（数字,格式）：可以把数字或文本格式化显示，如同单元格自定义格式效果。"[m]分钟"：在表示时间的自定义格式中，小时为h，分种是m，秒是s，所以如果是间隔小时，把m替换成h。而加[ ]则是可以显示大于24小时的小时数、60分种的分钟数。

2、计算到期时间

=EDATE($A2,$B2)

EDate（开始时间,间隔月数)：N月后的日期。如间隔月数为负则表示N月前的日期。

3、隔列求和公式

按标题求和

=SUMIF($A$2:$G$2,H$2,A3:G3)

因为有规则的标题，所以隔列求和转换为条件求和公式

如果没有标题

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

Column(区域):取得区域的所在列数，构成一组数字。MOD（数字,2）：一个数字除以2的余数。如果余数是0表示该数字为偶数，否则为奇数（条件）**B3:G3：在公式中经常会见到这样的结构。条件成立时返回的是True（运算时等同于1），不成立返回的是False（运算时等同于0）。两组数据相乘的结果是满足条件的数会保留下来，不满足的会变成0。Sumproduct：它和Sum函数的主要区别是，这里可以直接执行数组运算的求和，而Sum函数需要按三键输入。

4、超链接公式

=HYPERLINK("#"&B2&"!A1","进入")

Hyperplink函数：在Excel中能用公式创建超链接的，只有Hyperplink函数，它可以把一个字符串转换为超链接。"#"&B2&"!A1"：B列是工作表中，连接后 "#工作表名!A1"，前面加#表示本工作簿

5、跨表批量取值

=INDIRECT(B2&"!b10")

Indirect函数：它的作用是把单元格地址串转换为引用。如本例中 B2&"!b10" 连接起来虽然是工作表名！单元格，但它只是个字符串而已，并不会把单元格中的值取出来。利用Indirect转换后则可以取单元格中的值了。

6、提取不重复值

数组公式：按三键输入

｛=INDEX(A$1:A$99,MATCH(0,COUNTIF(C$1:C1,A$1:A$99),0))&""｝

COUNTIF(C$1:C1,A$1:A$99)：让C列本公式上面的姓名在A列逐个用Countif函数统计个数，如果不存在（正是要提取的唯一值）结果为0，然后用Match(0,统计的个数,0)查找0所在的行数。INdex（区域,行数）:可以根据位置提取出相对应的值。

兰色说：Excel函数公式的学习也需要循序渐进，如果上面的解释你看的还是不明白，就要从函数的基础用法学起了。

End.

来自：Excel精英培训

表界高手都说，函数公式是 Excel 的灵魂！因为它能纵横捭阖，轻松实现各种数据计算。

要说 Excel 灵魂中的精髓，当数「排山倒海」这一招。

掌握此绝技，就能一招制敌，用一个公式搞定海量数据计算，厉害吧。

GIF

要学会「排山倒海」，首先得会「猴子偷桃」，哦不，是「吸星大法」~

哦，就是「引用」啦。

引用就是，不直接输入数据，而是把其他单元格的内容吸过来，自动得到结果。

看一个最简单的例子，一个等号和一个引用。

GIF

在单元格 B2 中输入 =A2，他的值就自动变成了 A2 中的值。引用的好处很明显，修改源数据时，结果也会自动更新。

引用的方式可以分为三种：相对引用、绝对引用和混合引用。

也就是这三种：A1，$A$1，A$1。

逐个看看三种引用方式的区别吧 ~

Excel 公式中默认的引用方式就是相对引用，已经自带排山倒海的功力了。

怎么全都变了呢？

打开【公式】选项卡，点击【显示公式】按钮一看，原来，公式竟然会自动跟随变化。

要算个总价，也是一样轻轻松松啊。

蛋素，即使很多人知道向下填充知道怎么回事，换一种方式就懵逼了。

比如下面这个表格，是一家 10 元店的清单，几样商品的金额都是 10。那怎么算每项商品的总金额？

同样是根据「数量*单价」计算「总额」，如果还是直接写入 B4*C1，然后向下填充，结果就全错了！

你看，红色的数值才是正确的哦~

要解决这个问题，就要上升到排山倒海第三重：绝对引用。

前面的表格计算，引用单价时必须锁定在一个单元格，才能得到正确结果。怎么办呢？

你需要一个神奇的按键 F4。

正在上传...取消

按下【F4键】，C2 就变成了 $C$2，这就是绝对引用。花了点钱($)，就把价格参数锁定在 C2 了。果然还是见钱眼开啊。

有了「相对引用」和「绝对引用」这对好基友，公式的排山倒海威力初见成效。

好啦，练功不能急进，以免走火入魔。更厉害的排山倒海 第4重、第5重…… 且看下回分解。

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

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

最实用Excel公式大全！

以后做表格不再求人！

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

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

欢迎关注求职者

一个暖心的求职平台

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

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

下方查看历史精彩

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

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

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

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

1、最快求和：选择区域，按快捷键ALT+=，瞬间完成。2、累计求和：=SUM(B$2:B2)=N(C1)+B2N是最简单的函数，就是将文本转换成0。跟这个类似的是T，将数字转换成空文本。 =N(C1) =T(C1)3、跨列求和：=SUM(A2:F7)。

4、单条件求和：统计销售额大于500的人的总销售额=SUMIF(B:B,">500",B:B)=SUMIF(B:B,">500")。5、避开错误值求和：A列的数字中包含错误值，如何避开这些错误值对数字求和=SUMIF(A:A,"<9E+307")。9E+307是接近最大的数字，而错误值比最大的数字还大，小于最大值的数字，就能对数字进行求和。6、通配符求和：统计最后字符为河营业部的总金额=SUMIF(A2:A11,"*河",F2:F11)。 通配符有2个，*代表全部字符，?代表一个字符。如果是2个字符，最后一个字是河，可用"?河"。7、多区域条件求和：多个相同的区域，统计型号为B03的数量=SUMIF(A2:G10,"B03",B2:H10)。8、乘积求和：获取数量*单价的总金额=SUMPRODUCT(B2:B4,C2:C4)。9、多条件求和：统计营业部为天河，数量大于20的总金额=SUMIFS(F2:F11,A2:A11,"天河",D2:D11,">20")。10、统计每个月的金额：根据每天的销售明细，统计每个月的金额=SUMPRODUCT((MONTH($A$2:$A$11)=F2)*$D$2:$D$11)。 MONTH函数就是获取月份。

在需要查找时，很多童鞋会首先想到VLOOKUP函数。但有时候仅仅只会VLOOKUP函数是远远不够的。今天小编为大家进行了一次全面的整理，一起来看吧！

一、普通查找

查找李晓峰的应发工资。在对应单元格中输入“=VLOOKUP(H2,B:F,5,0)”

二、反向查找

查找吴刚的员工编号。在对应单元格中输入“=INDEX(A:A,MATCH(H2,B:B,0))”

三、交叉查找

查找3月办公费的金额。在对应单元格中输入“=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)”

四、多条件查找

查找上海产品B的销量。在对应单元格中输入“=LOOKUP(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)”

五、区间查找

根据销量从右表中查找提成比率。在对应单元格中输入“=LOOKUP(A2,$D$2:$E$5)”

六、双区间查找

根据销量和比率完成情况，从表中查找返利。

在对应单元格中输入“=IDNEX(B3:F7,MATCH(D11,A3:A7),MATCH(E11,B2:F2))”

七、线性插值

求数字8所对应的系数值。在对应单元格中输入“=TREND(OFFSET(B1,MATCH(D3,A2:A6,1),,2,1),OFFSET(A1,MATCH(D3,A2:A6,1),,2,1),D3）”

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

查找A产品最后一次进价。在对应单元格中输入“=LOOKUP(1,0/(B2:B9=A13),C2:C9)”

九、模糊查找

根据提供的城市从表中查找该市名的第2列的值。在对应单元格中输入“VLOOKUP("*"&A7&"*",A1:B4,2,0)”

十、匹配查找

根据地址从表中查找所在城市的提成。在对应单元格中输入“=LOOKUP(9^9.FIND(A$3:A$6,A10),B$3:B$6)”

十一、最后一个非空值查找

查找最后一次还款日期。在对应单元格中输入“=LOOKUP(1,0/(B2:B13<>""),$A2:$A13)”

十二、多工作表查找

从各部门中查找员工的基本工资，在哪个表中不一定。

方法1：

在对应单元格中输入“=IFERROR(VLOOKUP(A2,服务!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,综合!A:G,7,0),IFERROR(VLOOKUP(A2,财务!A:G,7,0),IFERROR(VLOOKUP(A2,销售!A:G,7,0),"无此人信息”)))))”

方法2：

在对应单元格中输入“=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"销售";"服务";"人事";"综合";"财务"}&"!a:a"),A2),{"销售";"服务";"人事";"综合";"财务"})&"!a:g"),7,0)

十三、一对多查找

根据产品查找对应的所有供应商。在A2单元格中输入“=B2&COUNTIF(B$1:B2,B2)”;

在B11单元格中输入“=IFERROR(VLOOKUP($A11&COLUMN(A1),$A:$C,3,0),""”

十四、查找销量最大的城市

查找销量最大的城市（数组公式按ctrl+shift+enter三键输入）

在对应单元格输入“{=INDEX(A:A,MAX((MAX(B3:B7)=B3:B7)*ROW(B3:B7)))}”

十五、最接近值查找

根据D4的价格，在B列查找最接近的价格，并返回相对应的日期。（数组公式按ctrl+shift+enter三键输入）

在对应单元格输入“=LOOKUP(1,0/MIN(ABS(B3:B7-D4))=ABS(B3:B7-D4))*ROW(B3:B7),A3:A7)}”

十六、跨多文件查找

文件夹中有N个仓库产品表格，需要在“查询”文件完成查询。

仓库表样式

在查询表中设置公式，根据产品名称从指定文件中sheet1工作表查询入库单价。在对应单元格输入“=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1!a:a")A2),"["&{"仓库1";"仓库2";"仓库3"}&".xlsx]sheet1")&"!a:b"),2,0)”

平时会用到的查找公式基本就这么多了。其中有不少公式需要有一定基础才能看懂，如果不太明白可以先套用，日后慢慢理解。

今天的Excel查找公式就讲到这里了，这些内容对于你的工作或学习有没有帮助呢？如果你有任何问题，记得在评论区留言，小编看到后会第一时间回复。

返回最近一天的销售额

公式：

=LOOKUP(9^9,B:B)

LOOKUP函数常用套路之一，用一个极大的数值9^9，返回查询区域中最后一个数值。

咳咳，猜猜这是啥商品？

返回间隔分钟数

=TEXT(B2-A2,"[m]分钟")

在表示时间的自定义格式中，小时为h，分种是m，秒是s。

外侧加上[ ]，可以显示大于24小时的小时数、或是超过60分种的分钟数。

咳咳，猜猜这是在做啥？

返回销量最高品牌

=INDEX(A2:A7,MATCH(MAX(B2:B7),B2:B7,))

MAX(B2:B7)部分，先用MAX计算出最高销量。

再用MATCH函数以最大值为查找对象，返回在B2：B7单元格区域中的相对位置2。

最后用INDEX函数返回A2:A7单元格区域第2个元素位置的内容。

咳咳，你有木有用过王大锤牌？

合并单元格求和

操作方法：同时选中D2:D10单元格区域，编辑栏输入公式，按Ctrl+回车：

=SUM(C2:C10)-SUM(D3:D10)

SUM(C2:C10)即当前行及以下所有C列数据的和，SUM(D3:D10)是本类别之后所有类别之和，二者相减，得到是本类别的和。

两个日期之间的工作日天数

C2单元格公式：

=NETWORKDAYS.INTL(A2,B2,1,E$2:E$6)

可以理解为：

=NETWORKDAYS.INTL(起始日期,终止日期,周末日,其他节假日)

第三参数使用1，表示周六和周日是周末。

End.

来自：Excel之家ExcelHome

基础知识很枯燥，也很重要。万丈高楼平地起，让我们首先先来了解下基本的概念。

一、公式的定义

Excel中的公式是以“=”号开始，通过使用运算符（如四则运算符、平方、比较运算符、逻辑运算符等）将数据、函数等元素按一定顺序连接在一起，从而实现对工作表的数值执行计算的等式，如图所示。

二、函数的定义

简单来说，函数就是预先定义好的公式。如图下图所示，如对A1:A10的数字进行求和，利

用SUM函数可以轻松汇总，但用常规公式却很麻烦。

使用函数可以简化公式，同时也可以完成公式所不能达到的功能。如下图，查询学生属于哪个班级。这里大家不用纠结函数的用法，在之后的教程中我会详细讲解。

函数为：=VLOOKUP(H3,E2:F11,2,0)

三、常见函数介绍

excel中函数特别多，但是往往常用的我总结了一下，主要用六种函数属于常用函数，而其他函数不好用，不常用，也一般情况下用不到，分别是求和函数SUM，条件函数IF，平均值函数AVERAGE，最大值函数MAX、最小值函数MIN ，排序函数RANK。

今天和新手们分享有关条件求和的内容，高手请忽略。

先来看一组销售数据，是某商场不同品牌电视机的三天销售记录：

现在需要根据G列的品牌，计算其三天的销售总和。

想必有表亲已经想到办法了，既然是按条件求和，就用SUMIF呗：

=SUMIF(B:B,G2,C:C)+SUMIF(B:B,G2,D:D)+SUMIF(B:B,G2,E:E)

公式分别对C、D、E列条件求和，最后相加。

这样看是没有问题的，可是如果需要对30列数据条件求和，那岂不是太费劲了哦。

嗯，还有一种简便一点的公式：

这里用到了数组公式：

=SUM(IF(B$2:B$72=G2,C$2:E$72))

首先用IF函数进行处理，如果B2:B72中等于G2单元格指定的品牌，就返回C2:E72单元格区域中对应行的数值，否则返回逻辑值FLASE，最后使用SUM函数求和。

这里因为执行了数组运算，所以公式编辑完成后，要单击编辑栏，再按住Shift+Ctrl不放，按回车。注意公式两侧的花括号是自动生成的，手工敲入是无效的哦。

这个公式比较简短，但是需要按三键才能输入，有些新人朋友可能一时还找不到感觉，耐心点，多试几次吧。

还有一个不需要按三键输入的公式：

=SUMPRODUCT((B$2:B$72=G2)*C$2:E$72)

首先使用B$2:B$72=G2判断B列是否等于指定的品牌，得到逻辑值TRUE或是FLASE，再用逻辑值与C2:E72的数值相乘，最后使用SUMPRODUCT计算出乘积之和。

本例中，除了使用以上公式之外，如果使用数据透视表汇总，将会更加便捷方便，你会使用吗？

作者：Hoa小熊猫 图文整理：祝洪忠

ExcelHome，微软技术社区联盟成员

1、查找重复内容公式：=IF(COUNTIF(A:A,A2)>1,"重复","")。

2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。

3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。

4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：

=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。

1、求和： =SUM(K2:K56) ——对K2到K56这一区域进行求和；

2、平均数： =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数；

3、排名： =RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名；

4、等级： =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))

5、学期总评： =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩；

6、最高分： =MAX(K2:K56) ——求K2到K56区域（55名学生）的最高分；

7、最低分： =MIN(K2:K56) ——求K2到K56区域（55名学生）的最低分；

8、分数段人数统计：

（1） =COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数；假设把结果存放于K57单元格；

（2） =COUNTIF(K2:K56,">=95")－K57 ——求K2到K56区域95～99.5分的人数；假设把结果存放于K58单元格；

（3）=COUNTIF(K2:K56,">=90")－SUM(K57:K58) ——求K2到K56区域90～94.5分的人数；假设把结果存放于K59单元格；

（4）=COUNTIF(K2:K56,">=85")－SUM(K57:K59) ——求K2到K56区域85～89.5分的人数；假设把结果存放于K60单元格；

（5）=COUNTIF(K2:K56,">=70")－SUM(K57:K60) ——求K2到K56区域70～84.5分的人数；假设把结果存放于K61单元格；

（6）=COUNTIF(K2:K56,">=60")－SUM(K57:K61) ——求K2到K56区域60～69.5分的人数；假设把结果存放于K62单元格；

（7） =COUNTIF(K2:K56,"

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

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）

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