Excel 不用 VBA，你使用過哪些絕妙的公式？

12-27

曾一度以為Excel寫一個類同篩選的功能只能用VBA實現。後來一個高手幫忙寫了一個絕妙的數組公式實現了想要的功能，分析後頓時讓人覺得腦洞大開。你用過的哪些公式，認為是一絕或是顛覆認知？

=LOOKUP(9E+307,--MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A11234567890)),ROW(INDIRECT("1:"LEN(A1)))))

輸出文本中自左起第一次開始出現的連續數字。
如：

輸出：

輸出：

做dirty work時這個公式能節省大量時間。還能炫技。

LOOKUP(I2,{0,2,5,7,10,15,20},{900,1500,1800,2000,2100,2200,2300})，用來計算對應業績的基本工資，原來用if寫的，後來發現自己蠢斃了

offset+row+column用於數據規整和複製多遍
例如：將一列中的每行複製成三行，順序不變：
=OFFSET($A$1,INT((ROW(A1)-1)/3),COLUMN(A1)-1,1,1)
當然，一般情況下可以簡化成=OFFSET($A$1,INT((ROW(A1)-1)/3),0)

vlookup+iserror+if用於數據查找+無效數據識別
例如：=if(iserror(vlookup(...)..)..)

vlookup+index+match+offset+iserror的嵌套使用，這個用法太多了，我自己也不太熟，也處於臨時用到臨時找的階段....

昨天在網上看到一個計算個人所得稅的公式覺得很巧妙。個人所得稅分7個檔，這神人居然不用if都可以實現，公式的簡便巧妙之處當我看懂之後興奮了好久。不說了直接分享，感受一下！=round(max((d3-3500)*{3;10;20;25;30;35;45}%-5*{0;21;111;201;551;1101;2701},0),2)

沒想到贊還挺多的，由於不是自己想的，就匿了吧！

～～～～！我是裝逼分割線～～～～～
我再來解釋一下為什麼這個公式可行！！已經想出來或者不屑於知道的可以不用往下看了……

這個公式是用同一個收入代入所有稅檔的稅率和速算扣除數，然後取最大的數。
那為什麼最大數就一定是這個收入的應交稅金呢？明明更檔次的稅率更高。其實巧妙之處就在速算扣除數上。

速算扣除數怎麼來的？

（純手工製作，別在意細節……）

先解釋下坐標軸的意義，橫軸代表收入，縱軸代表稅率（1,2,3代表稅檔），這裡還要加一個條件，高稅檔的稅率比低稅檔稅率高。
比如，收入b" 應交稅金就是陰影部分的面積。
如果按照稅法上的定義計算應該是（b-a）*1檔稅率+（b"-b）*2檔稅率
這樣算好麻煩啊，公式也不好設。
於是有了簡化公式：（b"-a）*2檔稅率-速算扣除數

所以速算扣除數就是圖中BCDE的面積……同理3檔的速算扣除數是ACDEFG的面積……

好了，假如我用2檔的收入代入3檔的稅率和速算扣除數會怎樣？
就相當於用Aab"I 這個矩形的面積減掉 ACDEFG的面積，很明顯會比陰影面積小……
同理也可以試試更高稅檔或者低稅檔，陰影面積必然是最大的。

因此……只要是超額累進位稅率，並且稅檔高的稅率高，就可以適用一開始那條公式……（不管分幾檔，有沒有免徵額）

數組函數+Index+Indirect等函數，如果不考慮效率的話，基本可以替代各種SQL語句了。

舉例1，下面函數的使用基本替代了select sum(tot) from ... group by name, month;

舉例2，計算AA產品的總價值，替代select sum(產品數量x產品單價) from ... where 產品編號=『AA』
{=SUM(IF(($B$4:$B$8="AA"),($C$4:$C$8)*($D$4:$D$8),0))}

————————————————————
更多文章關注我的專欄：數據冰山 - 知乎專欄

以前突發奇想，單用函數編寫了一個物料庫存閥值計算器，用多個條件（近xx天銷售數量、到貨天數，物料品類、各項其他係數、是否主營產品等來計算庫存閥值）主計算公式長得絕了。寫完的感悟是：幸虧懂點VBA，不然做死人。

隔列求和： SUMPRODUCT((MOD(COLUMN(合計區域),相隔列數+1)=MOD(COLUMN(啟始列),相隔列數+1))*合計區域)

個人感覺，Excel能用會vlookup,hlookup,indirect,match,len,sumif,sumifs,if這幾個函數，能熟練組合起來，加上會用篩選和數據透視表就基本夠應付日常操作了，vba主要是處理重複工作，與函數並不矛盾，反而通過與單元格中的函數合作可以使其發揮更有效的作用。

如果說顛覆認知，我覺得衝擊力最大的還是cells函數，通過這個函數可以自動更新excel的文件信息（比如所在的文件夾地址）到一個單元格，原本一直以為取地址要手動輸入或vba調用，第一次看到這個函數的時候徹底震驚了。

希望能和大家進一步討論

excle中如何把一個數字保留到小數點0 0.25 0.5 0.75幾個檔？ - Microsoft Excel
石頭的回答。

個人最歡這個，化繁為簡。

寫一個06年用公式做的一個excel點名抽獎的東西：

先祭圖，gif不動請戳 http://bigtran.com/wp-content/uploads/2014/11/%E6%8A%BD%E5%A5%96%E5%8A%A8%E7%94%BB.gif

大致原理就是rand函數在 excel狀態改變的時候，產生的數值也隨之變化。比如 A1單元格 =rand()，選中其他單元格，按住delete鍵，a1的數字就開始跳動了。

用到的兩個主要的excel函數組合：
1）計算學號對應的組別

IF(OR(INT(RIGHT(C2))=result!$B$1*2-1,INT(RIGHT(C2))=INT(RIGHT(result!$B$1*2))),1,0)

2）根據行號，顯示對應的同學姓名

VLOOKUP(INT(RAND()*COUNTIF(source!E2:E41,0)+1),source!A2:E41,2)

各函數公式的詳細說明參見：基於Excel的點名程序（不用編碼的抽獎程序）
excel源文件下載：http://www.bigtran.com/wp-content/uploads/files/Lucky_Draw_V1.02_Program.xls
word詳細說明文檔下載：http://www.bigtran.com/wp-content/uploads/files/Lucky_Draw_V1.02_Document.doc

PS. 06年還在用office2003， @趙欣回答中說的vlookup之前要排序的問題確實存在。

就醬~

round(iferror(vlookup(a1,e:f,match(b1，c:d,0),0),0)％％,2)

配合絕對引用和相對引用。

挨個說一下，round沒什麼，但最後那兩個％％就是除以10000的意思。這麼做除了減少鍵盤敲擊數之外，更重要的是不用數幾個0（round到億位，8個0，就夠數一陣的了，而且錯了毀所有，且不好檢查）。

iferror，忽略錯誤值，我這裡把錯誤值變成了0，方便加總。

vlookup沒什麼可說的，只需要注意修改引用條件，別掉褲子就行。

match的好處就是不用數在第幾列，不然數錯又毀所有。

此外，還推薦sumif。雖然這個函數很普通，但我有一天突然發現可以用於多條件查找~立刻就覺得牛逼了。

vlookup只能查找單一條件的數值，但要是有兩個條件怎麼辦呢？有兩個方法。

我不推薦的那個是把兩個條件用連接變成一個條件，再用vlookup。不推薦的原因是麻煩，需要輔助列。

推薦用sumifs，多條件，只要確保你選擇的條件足夠唯一就行了。那怎麼確定是否唯一呢。用countifs數一數，而且這兩個公式幾乎一樣，所以寫了一個另一個可以直接抄。

如何把一列重複的名字精簡成不重複的？可以用數組公式，不過本屌絲記不住…我用pivot table呀，一下子就出來了…

還有就是subtotal，與filter配合，解決好多問題。

跨表縱向查找
=VLOOKUP(A:A,Sheet2!A:B,2,0) 【謝謝 @嬴詩的指正】
我就是靠著這個公式，從窗口調入機關的。

++++++++++++
有人問這個公式怎麼用，我簡單說一下。
如下圖，一個xls文件中有Sheet1和Sheet2兩個表。分別是：
【Sheet1】：【二班的學號與姓名】；
【Sheet2】：【一二三班的學號與成績】。

現在我們的目的是：製作一份只包含【二班學生的學號、姓名、成績】的表格。也就是要求我們在【Sheet1】中的C列填上成績。

但是，成績所在的表格，不僅有二班學生的，還有一班和三班的。而且因為有的學生沒有參加考試，【sheet2】中缺少一部分二班學生。所以不可能靠直接複製粘貼的方式把成績列粘貼到【Sheet1】中。

我們的方法是：在【Sheet1】的【C3】輸入公式【=VLOOKUP(A:A,Sheet2!A:B,2,0) 】，再拖曳填充整個C列就好了。

就公式的每一部分，分別介紹：
【=VLOOKUP(】公式的開頭部分，沒什麼好說的，記得用英文標點。
【A:A,】表示以Sheet1的A列為關鍵字查找。就是張三的學號，拖曳填充後下面不變。
【Sheet2!A:B】為在Sheet2中的查找範圍，不考慮速度，越大越好，我自己常用的就是【Sheet2!A:Z】。記得要包含查找目標列。
【2,】表示返回第二列的數據，就是Sheet2中的【成績】列。
【0)】在很多介紹中，這個位置應該填false，但是我用總是報錯，填0就行了。

PS：如果是2003版的話，要將兩表都安關鍵數據那一列進行升序排序再用這個公式，否則有時會報錯。

之前公司給甲方做一個問卷調研，上萬份問卷的數據，我設計了excel表格來讓同事錄入，之後用了一些公式來處理數據：

問卷有效性判斷：=IF(OR(F2&>0,L2&>0,AND(SUM($T2:$X2)&>0,SUM($Z2:$AC2)&>0),CY2&>2,AND(AF2=1,SUM(AH2:AM2)=0)),"無效","有效")
文字回答的提取：{=IF(ISNA(MATCH(1,(CODE(驅蚊!$AA6:$AB6)&>10000)*1,0)),0,INDIRECT("驅蚊!"ADDRESS(ROW(驅蚊!AA6),MATCH(1,(CODE(驅蚊!$AA6:$AB6)&>10000)*1,0)+26)))}
文字回答的語義分析：=IF(COUNT(FIND({"安全";"火";"燃";"危險";"小孩"},$A4)),1,0)
問卷不同年齡層比例的控制：{=SUM(((驅蚊!$D$2:$D$5997=2))*((驅蚊!$B$2:$B$5997=$I2)))/$U2}

後來做媒體監控，用公式來應對CSM中播放時段變化：

播放時間點界定：{=ADDRESS(8,(MATCH(MIN(ABS(B7:INDIRECT(C46)-11)),ABS(B7:INDIRECT(C46)-11),0)),4)}
播放時間點的引用：{=ADDRESS((ROW(A56)-48),(MATCH(MIN(ABS($B$7:INDIRECT($C$46)-22.5)),ABS($B$7:INDIRECT($C$46)-22.5),0)),4)}
最後計算出當期收視率：=IF(COUNTIF(INDIRECT(B56):INDIRECT(C56),">=0")=0,"@",SUM(INDIRECT(B56):INDIRECT(C56))/COUNTIF(INDIRECT(B56):INDIRECT(C56),">=0"))

悲劇的是，很長時間不用，很多公式都看不懂了

怎麼都回答vlookup？我來個冷門的

networkday（startday， endday， holidaylist）
只要你有一個假日列表，這個函數立馬返回兩個日期之間的工作日天數

意義多大做債的金融狗們就不我再說了吧……

回答自己的提問。在此介紹兩種功能的公式，及其組合運用。

一、篩選功能的數組公式實現功能為：不限文本與數字根據自定義條件篩選查找某列數據。篩選結果按序排列，隨自定義條件的改變動態變換篩選內容。（可以跨頁表動態篩選）

二、HYPERLINK函數R1C1樣式嵌套MATCH函數查找引用數據的公式實現功能為：查找引用數據並且同步動態創建引用源鏈接。（可以跨頁表動態超鏈）

以上兩種組合就可以根據篩選條件的變換生成顯示相應數據的動態報表，並且能動態追蹤數據源。

以下示例：

（公式示例圖1）中設定A3:A16區域為品類。設定B3:B16區域為品類銷量的數據，區域數據中可以包含零值、空值。

一、D3:D16區域

在D3填入了以下數組公式後下拉複製至D3:D16區域，公式說明：篩選B列數值大於0的品類，在D列按序排列結果。

=IF(ROW(A1)&>SUM(N(INDIRECT("$B$3:$B$16")&>0)),"",INDEX(INDIRECT("$A$3:$A$16"),SMALL(IF(INDIRECT("$B$3:$B$16")&>0,ROW(INDIRECT("$B$3:$B$16"))-2,"0"),ROW(A1))))

再次感謝三年半前熱心網友tyxh0916的幫忙寫出了篩選功能的數組公式！

二、E3:E16區域

在E3填入以下公式後下拉複製至E3:E16區域。公式說明：查找引用D列對應A列品名的銷量數據並創建動態源數據超鏈。

=IF($D3="","",HYPERLINK("#R"MATCH($D3,INDIRECT("$A:$A"),)"C"MATCH(E$2,INDIRECT("$A$2:$B$2"),),INDIRECT(ADDRESS(MATCH($D3,INDIRECT("$A:$A"),),MATCH(E$2,INDIRECT("$A$2:$B$2"),),2,,))))

（公式示例圖1）公式一、公式二數據結果展示

三、組合運用

（報表示例圖2）跨頁表超鏈的公式寫法與目錄邏輯說明示例

（報表示例圖3）一級目錄設定與二級目錄公式寫法示例

（報表示例圖4）跨頁表動態條件篩選公式寫法示例

示例報表下載地址：http://vdisk.weibo.com/s/Ay6nU8GZsazg

大家都好牛逼，相比之下我的就顯得太簡單了，不過這是剛入門的我兩小時的心血，小小的曬一下，高人不要取笑我啊，首先還是感謝 @雨聲敲敲推薦的書《你早該這麼玩excel》看後茅塞頓開，開啟我的excel之旅。我這個圖表基本效果就是只需要輸入每日每單的相關信息，比方說日期，單號，部門，負責人，品名，數量默認為1，不是得話直接修改，因為日期，部門負責人大部分是都是固定的，所以頻繁輸入的只有單號和品名了，輸入完畢直接顯示當日每項付款方式的金額以及總額和當月累計總額，還有每位員工的個人銷量和提成，正在學習如何製成圖表，比較容易看懂，現在上圖，歡迎各位高手指點一二
最先設置的是參數表，裡面是每種產品的單價，分類和默認銷量，還有日期對應的星期幾

然後就是每天要輸入的源數據表

這是源數據表，為了方便輸入便將需要輸入的格子移到一起，方便輸入，空白處都是需要輸入的，不過因為有二維火系統，所以基本就是複製粘貼就好了，然後按照要求輸入信息就可以瞬間生成以下幾份表格
首先是：兩個部門每日的匯總，各項付款方式的匯總和總額的匯總，然後一個月的下來自動生成當月匯總

其次就是每種產品的銷售情況以及百分比，

還有每位員工的提成的計算，可以計算每個部門每個人員每個產品的銷量和提成和總提成
基本就是如此，還準備進行更加完善的設置和修改，雖然做了那麼多，但是店裡並不准備採用，不管那麼多了，反正我開心就好，哈哈哈哈

=index("sheet1A:A",match("sheet2A:A","sheet1 B:B",0))

假設excel有兩個工作頁 sheet1 和 sheet2，
其中sheet1 A列有學生姓名n個，B列有他們對應的學號（唯一不重複）n個，現在sheet2 A列有雜亂無序的學號m個，這m個學號包含有之前sheet1中的學號以及新出現的未知學號。

這個函數可以把sheet2中雜亂無序的學號一一匹配上相對應的學生姓名，並且會對新出現的學號報錯，做一個篩選按鈕就可以瞬間找出sheet2中新出現的學號。

配合抓取函數
=mid(A:A,n,m) 選定A列，從A列每個單元格里第n個字元開始抓取m個字元。

感覺能處理很多跨工作表或者工作簿的無序數據匹配篩選問題^_^ 適用領域還挺廣的。

=IF(OR($B$3="",$B$2="",$B5="",C$2=""),"",IF(AND(COUNTIF(原始數據!$6:$6,$B$3)&<=2,COUNTIF(原始數據!$6:$6,$B$2)&<=2),COUNTIFS(INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)15":"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)10015),$A5,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)15":"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)10015),C$1),IF(AND(COUNTIF(原始數據!$6:$61,$B$3)&<=2,COUNTIF(原始數據!$6:$6,$B$2)&>2),COUNTIFS(INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)15":"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)10015),$A5,INDIRECT("原始數據!"HLOOKUP(HLOOKUP(C$2,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)7":"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0)+COUNTIF(原始數據!$6:$6,$B$2)-1,原始數據!$2:$3,2,0)8),2,0),原始數據!$2:$3,2,0)15":"HLOOKUP(HLOOKUP(C$2,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)7":"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0)+COUNTIF(原始數據!$6:$6,$B$2)-1,原始數據!$2:$3,2,0)8),2,0),原始數據!$2:$3,2,0)10015),1),IF(AND(COUNTIF(原始數據!$6:$6,$B$3)&>2,COUNTIF(原始數據!$6:$6,$B$2)&<=2),COUNTIFS(INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B5,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)7":"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0)+COUNTIF(原始數據!$6:$6,$B$3)-1,原始數據!$2:$3,2,0)8),2,0),原始數據!$2:$3,2,0)15":"HLOOKUP(HLOOKUP($B5,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)7":"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0)+COUNTIF(原始數據!$6:$6,$B$3)-1,原始數據!$2:$3,2,0)8),2,0),原始數據!$2:$3,2,0)10015),1,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)15":"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)10015),C$1),COUNTIFS(INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B5,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)7":"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0)+COUNTIF(原始數據!$6:$6,$B$3)-1,原始數據!$2:$3,2,0)8),2,0),原始數據!$2:$3,2,0)15":"HLOOKUP(HLOOKUP($B5,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)7":"HLOOKUP(HLOOKUP($B$3,原始數據!$6:$8,3,0)+COUNTIF(原始數據!$6:$6,$B$3)-1,原始數據!$2:$3,2,0)8),2,0),原始數據!$2:$3,2,0)10015),1,INDIRECT("原始數據!"HLOOKUP(HLOOKUP(C$2,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)7":"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0)+COUNTIF(原始數據!$6:$6,$B$2)-1,原始數據!$2:$3,2,0)8),2,0),原始數據!$2:$3,2,0)15":"HLOOKUP(HLOOKUP(C$2,INDIRECT("原始數據!"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0),原始數據!$2:$3,2,0)7":"HLOOKUP(HLOOKUP($B$2,原始數據!$6:$8,3,0)+COUNTIF(原始數據!$6:$6,$B$2)-1,原始數據!$2:$3,2,0)8),2,0),原始數據!$2:$3,2,0)10015),1)))))

大致是對接一個問卷系統的統計公式，一個公式要搞定單選、多選、交叉，而且每道題的選項是不確定的，還有文本輸入的選項。

當年背單詞的時候，一直背不進「CS」區間內（正著背不過C，倒著背不過S），於是想了個笨辦法把單詞表打亂：新增一列，用rand函數生成一列隨機數，然後按照此列排序，就得到一份亂序的單詞表。

=SUM(--TEXT(MID(A1,ROW(INDIRECT("A1:A"LEN(A1))),1), "0;;;!0"))

將A1單元格中所有的數字求和. 比如A1中的內容為 "6啊5zg+1" , 則返回結果為12(即6+5+1). 這在統計班裡同學信息時經常用到, 尤其對於處理不按要求錄入的同學的信息最有價值.