小編分享10大Excel神公式，它們是：

快速記錄輸入內(nèi)容的時間

銷售業(yè)績大比武

按姓名對科目排序號

按部門對人員排序號

按合并單元格排序號（1）

按合并單元格排序號（2）

把內(nèi)容按類別合并在同一單元格內(nèi)（2016版本）

按國籍統(tǒng)計獲獎人數(shù)

技巧點撥：公式=COUNTA(B2:B7)-SUM(C3:C7)

按合并單元格求和

操作方法：

?先選擇"D2:D11"單元格區(qū)域；

?在編輯欄中輸入公式【=SUM(C2:C11)-SUM(D3:D11)】

?最后,，按【Ctrl+Enter】

統(tǒng)計中獎人數(shù)

操作方法：

?單擊"C2"單元格,；

?在編輯欄中輸入公式【=LEN(B2)-LEN(SUBSTITUTE(B2,"，",""))+1】

?雙擊鼠標(biāo),，快速填充,。

鳴謝：若喜歡本文，請多多點贊,、分享哦！

關(guān)注小編,，請點擊右上角,，天天有驚喜！,！

謝邀,！這9個讓人目瞪口呆的Excel神奇公式，如果你覺得不厲害,，算我耍流氓,！

多數(shù)職場人士，用的最多的函數(shù)公式是SUM,、SUMIF,、VLOOKUP、OFFSET等,，但是我要給你說的這幾個函數(shù)公式,，用的不多，但是當(dāng)你用到這些功能的時候,，卻幾乎沒有可替代的選擇,，因此也十分重要。

01 - 一鍵創(chuàng)建圖片鏈接

這個公式我在《excel中有和圖片名稱一樣的數(shù)據(jù),，怎么匹配把圖片做批量超鏈接,？》問題中詳細(xì)講解過，我們可以使用這個公式批量建立圖片超鏈接,。

在B2單元格中寫入公式=HYPERLINK("D:\重要文件\Desktop\Pic\"&A2&".jpg",A2),，然后向下填充。

公式的效果就是,，我們點擊B列照片的鏈接,，就可以直接打開對應(yīng)的照片。

02 - 實現(xiàn)QQ聊天模式

可以在Excel界面設(shè)置一個鏈接,，點擊鏈接就可打開QQ對話框,，當(dāng)然你也可以將其他軟件連接到Excel界面中，這樣就相當(dāng)于把Excel變成了一個控制臺了！

如圖所示,，登陸QQ之后,，在B3單元格中輸入 =HYPERLINK("tencent://Message/?websiteName=qzone.qq.com&Menu=yes&Uin="&A3,"點擊聯(lián)系我")，然后單擊【聯(lián)系我吧】即可實現(xiàn)QQ交流,。

（圖片來自：雷哥Office,，已獲得授權(quán)）

03 - 模擬迷你圖公式

經(jīng)常有朋友問我，Excel版本過低,，無法使用迷你圖,，怎么破？REPT函數(shù)可以幫到你,。

REPT函數(shù)的的含義是：一次性輸入多個重復(fù)的相同符號,。利用這個特性，我們可以用來模擬圖表,。函數(shù)的語法非常簡單=REPT（text,，number_times)，翻譯出來就是=Rept函數(shù)格式（“符號”,，位數(shù)）,。

如圖，在C2單元格中輸入公式=REPT("▍",B2/10),，公式的意思就是將▍符號按照銷量進(jìn)行重復(fù),，其疊加起來就會形成類似柱形圖的式樣。

04 - 讓Excel成為翻譯器

你想過在Excel中批量翻譯文章么,？

Excel可以通過公式調(diào)用任何第三方翻譯網(wǎng)址,，實現(xiàn)批量翻譯，我們來看一下調(diào)用有道詞典進(jìn)行翻譯的效果吧,！

在B2單元格寫入公式=FILTERXML(WEBSERVICE("http://fanyi.youdao.com/translate?&i="&A2&"&doctype=xml&version"),"http://translation"),，然后將其向下復(fù)制填充。

可以看到,，無論是翻譯中文,、還是翻譯英文，通過這個公式都能實現(xiàn)秒翻,！

05 - 合并單元格批量求和

由于合并單元格破壞了表格的聯(lián)系性,，在合并單元格中無法進(jìn)行公式的復(fù)制填充，但是通過Ctrl+Enter組合鍵輸入公式,，就可以批量求解合并單元格之和,。

如圖要在C列求解各地銷量之和，選中C2：C13區(qū)域,，然后輸入公式=SUM(B2:$B$13)-SUM(C3:$C$14),，接下來按Ctrl+Enter組合鍵批量輸入公式,，即可求解合并單元格之和。

06 - 一次性查找多個數(shù)值

我們都會用VLOOKUP函數(shù)進(jìn)行一對一查詢匹配,，那么一對多的查詢你會么,？

如圖要想實現(xiàn)一對多查找，需要在原始數(shù)據(jù)中添加一個輔助列,，如B列,，即對重復(fù)的查找項進(jìn)行區(qū)分。

在如圖B2單元格中寫入公式=IFERROR(VLOOKUP($A$2&(ROW()-1),表1[[輔助列]:[開戶行]],2,0),""),，根據(jù)A2中公司名稱的不同,，就可實現(xiàn)一對多查找。

07 - 輕松判定是否重復(fù)

判斷表格中的數(shù)值是否重復(fù),，有很多方法,，使用函數(shù)進(jìn)行判斷是可以實現(xiàn)自動化判斷的方法。

如圖所示,，在B2單元格輸入公式=IF(COUNTIF([開戶行],[@開戶行])=1,"不重復(fù)","重復(fù)")，即可自動判斷A列輸入的數(shù)值是否有重復(fù),。

（注意：這里我用的是智能表格,，公式會自動轉(zhuǎn)化成結(jié)構(gòu)化引用）

08 - 求不重復(fù)數(shù)值的個數(shù)精典公式

比如某列數(shù)值中是產(chǎn)品名稱，但是每個產(chǎn)品名稱都重復(fù)的,，如何統(tǒng)計產(chǎn)品名的類別數(shù)量（即不重復(fù)產(chǎn)品的數(shù)量）,，如果你不知道這個公式，你將走很多彎路,。

這是一個非常重要的公式,，一定要記得=SUMPRODUCT(1/COUNTIF($A$2:$A$12,$A$2:$A$12))，簡化記憶就是=SUMPRODUCT(1/COUNTIF(數(shù)據(jù)區(qū)域,數(shù)據(jù)區(qū)域))

09 - 萬能查找公式

學(xué)會此公式,，幾乎能搞定任何數(shù)據(jù)查詢匹配,，這是一個萬能公式。

我們知道VLOOKUP在查找匹配方面很厲害,，但是它也有弱點,，比如逆向查找、多條件查找等,。而LOOKUP函數(shù),，有一個萬能公式，能夠輕松搞定高級查詢功能,。

這個萬能公式就是：=LOOKUP(1,0/(條件1*條件2),返回值區(qū)域)

這個公式幾乎能完成所有類型的查詢匹配,，如圖所示，我們需要在D9單元格根據(jù)姓名和工號查找對應(yīng)的職務(wù),，這屬于多條件查找,，套用公式則很簡單=LOOKUP(1,0/((B2:B6=B9)*(A2:A6=C9)),E2:E6)

這些公式,，大部分Excel用戶都不會，如果你掌握了,，你就超越了你身邊90%的人,，你說厲害不厲害。

「精進(jìn)Excel」系頭條簽約作者,，關(guān)注我,，如果任意點開三篇文章，沒有你想要的知識,，算我耍流氓,！

學(xué)好Excel真真真的太重要了，工作效率蹭蹭的有木有,，分享一份excel公式大全~

更多財稅職場學(xué)習(xí)資訊,，關(guān)注秀財網(wǎng)

今天給大家分享幾個Excel中超好用的公式：

同比增長率公式

如下圖所示，B列是本年累計,，C列是去年同期累計,，要求計算同比增長率。

公示：E2

=(B2-C2)/IF(C2>0,C2,-C2)

多條件求和,、計數(shù)公式

Sumproduct(條件1*條件2*條件3...數(shù)據(jù)區(qū)域)

示例：統(tǒng)計A產(chǎn)品3月的銷售合計

=SUMPRODUCT((MONTH(A3:A9)=3)*(B3:B9="A")*C3:C9)

注:和sumifs相比速度雖然慢了點,，但Sumproduct可以對數(shù)組進(jìn)行處理后再設(shè)置條件，同時也可以對文本型數(shù)字進(jìn)行計算,，而Sumifs函數(shù)則不可,。

計算不重復(fù)值個數(shù)的公式

=SUMPRODUCT(1/COUNTIF(區(qū)域,區(qū)域))

示例：統(tǒng)計B列的客戶數(shù)量

=SUMPRODUCT(1/COUNTIF(B2:B19,B2:B19))

提成比率計算

=VLOOKUP(B3,$C$12:$E$21,3)

以上就是今天分享給大家的excel中常用的公式啦，多用幾次就會比較熟了哦,。

員工考勤表公式

1,、本月工作日天數(shù)(AG列)

=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)

2、調(diào)休天數(shù)公式(AI列)

=COUNTIF(B9:AE9,"調(diào)")

以上就是今天分享給大家的幾個公式啦,，多用用就熟練了哦,。

謝謝！非常高興回答這個問題,！要說Excel的神公式其實有很多,，但要說發(fā)揮強(qiáng)大的函數(shù)還是應(yīng)該算嵌套一起混合用的！

下面介紹一個通用的神技巧：每個行業(yè)都要去做成本,、預(yù)算評估分析,，那何不妨用excel來做呢？

昨天我恰巧分享了一個關(guān)于用excel制作銷售,、成本預(yù)算分析的例子,，用到一個大的函數(shù)：Trend函數(shù)，其中括號中嵌套了兩個小函數(shù)：MATCH函數(shù),、OFFSET函數(shù),。

先來看看題目：現(xiàn)在我知道產(chǎn)品不同數(shù)量下的成本是什么,，想知道50個產(chǎn)品的成本是多少？

具體操作來看看視頻：

希望通過分享,，大家能夠很好的應(yīng)用這個神技巧,！

更多的辦公軟件應(yīng)用技巧可以關(guān)注我的頭條號；大家留言,、討論,！

如果想了解如何把控心態(tài)及工作生方式的文章、聊聊人生,，可以關(guān)注公眾號：睡前讀書十分鐘（點擊微信添加朋友——公眾號——睡前讀書十分鐘）,，定期分享心理指導(dǎo)！

要說Excel中的神公式,，我今天介紹的3條公式,，基本能夠解決多數(shù)人的80%以上的大麻煩。有人也許會想這些公式是不是很高級很難,，其實不然,，這都是些常見的函數(shù)，只要我們理解了思路,，是可以輕松寫出來的,。

一、一條公式快速從身份證號碼里提取性別信息

HR常常需要從身份證號碼中提取出每個人的性別信息,，而我們都知道身份證號碼的長度往往有2種，一種是18位的,，另一種則是15位,。其中18位身份證號碼的倒數(shù)第二位數(shù)字代表一個人的性別（偶數(shù)代表女，奇數(shù)代表男）,；而15位身份證號碼則是最后一位數(shù)字代表性別信息,，同樣也是偶數(shù)代表女生，奇數(shù)代表男生,。我們?nèi)绾慰焖俚貙⒋硇詣e的數(shù)字提取出來并得出性別信息呢,？

思路：

1.提取代表性別的數(shù)字

=LEFT(RIGHT(B2,1+(LEN(B2)=18)*1))

我們知道：要么是倒數(shù)第二位的數(shù)字（身份證號碼為18位），要么是倒數(shù)第一位（15位身份證號碼）的數(shù)字代表性別,，因此我該提取1位還是2位數(shù)字,，是由身份證號碼的長度決定的。因此我們可以得到公式（(LEN(B2)=18)*1是將邏輯值true或者false轉(zhuǎn)化為1或者0）：

=RIGHT(B2,1+(LEN(B2)=18)*1)

然后我們再在上述公式得到的結(jié)果的基礎(chǔ)之上,，往左邊取一位即可得到代表性別的數(shù)字了,。

2.根據(jù)得到的數(shù)字判斷其奇偶性從而返回性別信息

=MOD(LEFT(RIGHT(B2,1+(LEN(B2)=18)*1)),2)

Mod返回的結(jié)果為0或者1，偶數(shù)時返回為0,，奇數(shù)時返回1,。接下來我們通過if函數(shù)返回性別信息即可,。

=IF(MOD(LEFT(RIGHT(B2,1+(LEN(B2)=18)*1)),2),"男","女")

動畫演示：

二、簡單好用的一條公式批量返回查詢結(jié)果

要我說,，沒有比這個更好的Excel公式了,。如下圖所示，我們需要將右側(cè)表格中的學(xué)生成績快速地返回到左側(cè)表中,，該如何快速地完成這個任務(wù)呢,？

沒錯，很多朋友想到了vlookup函數(shù),，這的確是一個非常棒的函數(shù),，尤其是搭配match函數(shù)一起使用時。現(xiàn)在我們就用經(jīng)典實用的vlookup+match組合來搞定它,。

思路：懂點vlookup函數(shù)的朋友都知道,，完成這個公式并不難，難的是用一條公式完成,。他們知道：vlookup函數(shù)共計4個參數(shù),，在這個例子第一個參數(shù)和第二個參數(shù)都固定不變，注意引用即可,，第四個參數(shù)為0,，無需變動，唯一麻煩的是第三個參數(shù),，每寫一列都要更改一次?，F(xiàn)在我們用match函數(shù)去自動返回A表中的每一列在B表中的位置，公式如下：

=match(B$1,$M$1:$S$1,0)

當(dāng)公式向右復(fù)制時,，Excel會自動查找每一科成績在B表中的位置,。然后我們在套上基礎(chǔ)的vlookup函數(shù)即可。操作步驟：選中A2:G39,在編輯欄輸入公式：

=VLOOKUP($A2,$M$2:$S$39,MATCH(B$1,$M$1:$S$1,),)

三,、一條公式快速搞定個人所得稅計算

計算每個人的個人所得稅是咱們財務(wù)人員或者HR必不可少的工作技能,。那么如何快速搞定個人所得稅呢？個人所得稅的征收規(guī)定是這樣的：你的稅前工資交過扣除五險一金后,，工資額大于3500就需要交納個人所得稅了,。具體的稅率表如下：

具體的計算方式如下：

應(yīng)納稅所得額 = 稅前工資收入金額 － 五險一金(個人繳納部分) － 起征點(3500元)
應(yīng)納稅額 = 應(yīng)納稅所得額 x 稅率 － 速算扣除數(shù)

如下圖所示：

這里的稅前工資是扣除了五險一金以后的。這里我們用其減掉3500即可得到應(yīng)繳納所得稅額,。然后根據(jù)所得稅額來計算咱們每一個人的個人所得稅,。

=B2-3500

我們先根據(jù)規(guī)定制作如下圖的一個表格：

然后根據(jù)這個表格通過vlookup函數(shù)或者lookup函數(shù)返回其對應(yīng)的稅率，然后乘以應(yīng)繳納所得稅額,，最后在通過vlookup函數(shù)或者lookup函數(shù)返回其對應(yīng)的速算扣除數(shù)即可得到所得稅：

稅率：=LOOKUP(B2-3500,$F$2:$G$8)或者=VLOOKUP(B3-3500,$F$2:$G$8,2)

速算扣除數(shù)：=VLOOKUP(B3-3500,$F$2:$H$8,3)或者=LOOKUP(B2-3500,$F$2:$H$8)

個人所得稅公式為：

=LOOKUP(B2-3500,$F$2:$G$8)*(B2-3500)-LOOKUP(B2-3500,$F$2:$H$8)

或者

=VLOOKUP(B3-3500,$F$2:$G$8,2)*(B3-3500)-VLOOKUP(B3-3500,$F$2:$H$8,3)

然而,，收入不到稅點時，Excel會返回錯誤值,，因此我們需要在外層嵌套一個iferror函數(shù),，如下：

=IFERROR(LOOKUP(B2-3500,$F$2:$G$8)*(B2-3500)-LOOKUP(B2-3500,$F$2:$H$8),"")

或者

=IFERROR(VLOOKUP(B3-3500,$F$2:$G$8,2)*(B3-3500)-VLOOKUP(B3-3500,$F$2:$H$8,3),"")

我是胡定祥,，酷愛Excel。頭條號：傲看今朝,。自由撰稿人,，辦公室er.酷愛Excel，一個有兩把“刷子”的胖子,。歡迎關(guān)注我,，有任何問題，十分歡迎大家在評論區(qū)留言,。

我說一個吧=IF(SUM(ISNUMBER(FIND(“關(guān)鍵字”,A2))*1),"包含","不包含")

這是查找關(guān)鍵字的公式,，我截個圖，方便大家理解：

因為是數(shù)組公式需要三鍵結(jié)束,，Shift+Ctrl+Enter

你截圖里提取數(shù)字的公式,，可以換一種思路，復(fù)制到word里面,，把文字,、字母替換成空，更快,。

神公式?jīng)]怎么見過,，平時上班遇到問題，直接百度,，找思路就夠了,。

公式也不是越復(fù)雜越好，我一直覺得太復(fù)雜反而可能不是最好的方法,，而且容易亂,，要找找其他邏輯關(guān)系和方法了。當(dāng)然復(fù)雜公式能寫出來也是很厲害了,，說明掌握的很好，同時給小白一種不明覺厲的感覺,。

歡迎關(guān)注我的頭條號,，如果有excel方面的問題，可以私信交流,，為你答疑解惑,。