9个Excel神奇公式

9个Excel神奇公式
谢邀！这9个让⼈⽬瞪⼝呆的Excel神奇公式，如果你觉得不厉害，算我耍流氓！
多数职场⼈⼠，⽤的最多的函数公式是SUM、SUMIF、VLOOKUP、OFFSET等，但是我要给你说的这⼏个函数公式，⽤的不多，但是当你⽤到这些功能的时候，却⼏乎没有可替代的选择，因此也⼗分重要。

01 - ⼀键创建图⽚链接
这个公式我在《excel中有和图⽚名称⼀样的数据，怎么匹配把图⽚做批量超链接？》问题中详细讲解过，我们可以使⽤这个公式批量建⽴图⽚超链接。

在B2单元格中写⼊公式=HYPERLINK('D:\重要⽂件\Desktop\Pic\'&A2&'.jpg',A2)，然后向下填充。

公式的效果就是，我们点击B列照⽚的链接，就可以直接打开对应的照⽚。

02 - 实现QQ聊天模式
可以在Excel界⾯设置⼀个链接，点击链接就可打开QQ对话框，当然你也可以将其他软件连接到Excel界⾯中，这样就相当于把Excel变成了⼀个控制台了！
如图所⽰，登陆QQ之后，在B3单元格中输⼊ =HYPERLINK('tencent://Message/? websiteName=&Menu=yes&Uin='&A3,'点击联系我')，然后单击【联系我吧】即可实现QQ交流。

（图⽚来⾃：雷哥Office，已获得授权）
03 - 模拟迷你图公式
经常有朋友问我，Excel版本过低，⽆法使⽤迷你图，怎么破？REPT函数可以帮到你。

REPT函数的的含义是：⼀次性输⼊多个重复的相同符号。

利⽤这个特性，我们可以⽤来模拟图
表。

函数的语法⾮常简单=REPT（text，number_times)，翻译出来就是=Rept函数格式（“符号”，位数）。

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

04 - 让Excel成为翻译器
你想过在Excel中批量翻译⽂章么？
Excel可以通过公式调⽤任何第三⽅翻译⽹址，实现批量翻译，我们来看⼀下调⽤有道词典进⾏翻译的效果吧！
在B2单元格写⼊公式=FILTERXML(WEBSERVICE('/translate?
&i='&A2&'&doctype=xml&version'),'//translation')，然后将其向下复制填充。

可以看到，⽆论是翻译中⽂、还是翻译英⽂，通过这个公式都能实现秒翻！
05 - 合并单元格批量求和
由于合并单元格破坏了表格的联系性，在合并单元格中⽆法进⾏公式的复制填充，但是通过Ctrl Enter组合键输⼊公式，就可以批量求解合并单元格之和。

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

06 - ⼀次性查找多个数值
我们都会⽤VLOOKUP函数进⾏⼀对⼀查询匹配，那么⼀对多的查询你会么？
如图要想实现⼀对多查找，需要在原始数据中添加⼀个辅助列，如B列，即对重复的查找项进⾏区分。

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

07 - 轻松判定是否重复
判断表格中的数值是否重复，有很多⽅法，使⽤函数进⾏判断是可以实现⾃动化判断的⽅法。

如图所⽰，在B2单元格输⼊公式=IF(COUNTIF([开户⾏],[@开户⾏])=1,'不重复','重复')，即可⾃动判断A列输⼊的数值是否有重复。

（注意：这⾥我⽤的是智能表格，公式会⾃动转化成结构化引⽤）
08 - 求不重复数值的个数精典公式
⽐如某列数值中是产品名称，但是每个产品名称都重复的，如何统计产品名的类别数量（即不重复产品的数量），如果你不知道这个公式，你将⾛很多弯路。

这是⼀个⾮常重要的公式，⼀定要记得
=SUMPRODUCT(1/COUNTIF($A$2:$A$12,$A$2:$A$12))，简化记忆就是
=SUMPRODUCT(1/COUNTIF(数据区域,数据区域))
09 - 万能查找公式
学会此公式，⼏乎能搞定任何数据查询匹配，这是⼀个万能公式。

我们知道VLOOKUP在查找匹配⽅⾯很厉害，但是它也有弱点，⽐如逆向查找、多条件查找等。

⽽LOOKUP函数，有⼀个万能公式，能够轻松搞定⾼级查询功能。

这个万能公式就是：=LOOKUP(1,0/(条件1*条件2),返回值区域)
这个公式⼏乎能完成所有类型的查询匹配，如图所⽰，我们需要在D9单元格根据姓名和⼯号查找对应的职务，这属于多条件查找，套⽤公式则很简单=LOOKUP(1,0/((B2:B6=B9)*
(A2:A6=C9)),E2:E6)
这些公式，⼤部分Excel⽤户都不会，如果你掌握了，你就超越了你⾝边90%的⼈，你说厉害不厉害。

「精进Excel」系头条签约作者，关注我，如果任意点开三篇⽂章，没有你想要的知
识，算我耍流氓！。