你见过哪些惊为天人的Excel公式？

看不出来的瘦子 2018-04-09 12:00:00

多数职场人士，用的最多的函数公式是SUM、SUMIF、VLOOKUP、OFFSET等，但是我要给你说的这几个函数公式，用的不多，但是当你用到这些功能的时候，却几乎没有可替代的选择，因此也十分重要。

001

公式实现QQ聊天

可以在Excel界面设置一个链接，点击链接就可打开QQ对话框，当然你也可以将其他软件连接到Excel界面中，这样就相当于把Excel变成了一个控制台了！

如图所示，登陆QQ之后，在B3单元格中输入

=HYPERLINK("tencent://Message/?websiteName=qzone.qq.com&Menu=yes&Uin="&A3,"点击联系我")，然后单击【联系我吧】即可实现QQ交流。

（图片来自：雷哥Office，已获得授权）

002

模拟迷你图公式

经常有朋友问我，Excel版本过低，无法使用迷你图，怎么破？REPT函数可以帮到你。

REPT函数的的含义是：一次性输入多个重复的相同符号。利用这个特性，我们可以用来模拟图表。函数的语法非常简单=REPT（text，number_times)，翻译出来就是=Rept函数格式（“符号”，位数）。

如图，在C2单元格中输入公式=REPT("▍",B2/10)，公式的意思就是将▍符号按照销量进行重复，其叠加起来就会形成类似柱形图的式样。

003

让Excel成为翻译器

你想过在Excel中批量翻译文章么？

Excel可以通过公式调用任何第三方翻译网址，实现批量翻译，我们来看一下调用有道词典进行翻译的效果吧！

在B2单元格写入公式

=FILTERXML(WEBSERVICE("http://fanyi.youdao.com/translate?&i="&A2&"&doctype=xml&version"),"//translation")，然后将其向下复制填充。

可以看到，无论是翻译中文、还是翻译英文，通过这个公式都能实现秒翻！

004

合并单元格批量求和

由于合并单元格破坏了表格的联系性，在合并单元格中无法进行公式的复制填充，但是通过Ctrl+Enter组合键输入公式，就可以批量求解合并单元格之和。

如图要在C列求解各地销量之和，选中C2：C13区域，然后输入公式=SUM(B2:$B$13)-SUM(C3:$C$14)，接下来按Ctrl+Enter组合键批量输入公式，即可求解合并单元格之和。

005

一次性查找多个数值

我们都会用VLOOKUP函数进行一对一查询匹配，那么一对多的查询你会么？

如图要想实现一对多查找，需要在原始数据中添加一个辅助列，如B列，即对重复的查找项进行区分。

在如图B2单元格中写入公式=IFERROR(VLOOKUP($A$2&(ROW()-1),表1[[辅助列]:[开户行]],2,0),"")，根据A2中公司名称的不同，就可实现一对多查找。

006

求不重复数值的个数精典公式

比如某列数值中是产品名称，但是每个产品名称都重复的，如何统计产品名的类别数量（即不重复产品的数量），如果你不知道这个公式，你将走很多弯路。

这是一个非常重要的公式，一定要记得

=SUMPRODUCT(1/COUNTIF($A$2:$A$12,$A$2:$A$12))，简化记忆就是

=SUMPRODUCT(1/COUNTIF(数据区域,数据区域))

007

万能查找公式

学会此公式，几乎能搞定任何数据查询匹配，这是一个万能公式。

我们知道VLOOKUP在查找匹配方面很厉害，但是它也有弱点，比如逆向查找、多条件查找等。而LOOKUP函数，有一个万能公式，能够轻松搞定高级查询功能。

这个万能公式就是：=LOOKUP(1,0/(条件1*条件2),返回值区域)

这个公式几乎能完成所有类型的查询匹配，如图所示，我们需要在D9单元格根据姓名和工号查找对应的职务，这属于多条件查找，套用公式则很简单=LOOKUP(1,0/((B2:B6=B9)*(A2:A6=C9)),E2:E6)

这些公式，大部分Excel用户都不会，如果你掌握了，你就超越了你身边90%的人，你说厉害不厉害。