E圖表述
您好,這裡是“E圖表述”為您講述的Excel各種知識。
學會Index+Small+Row的“萬金油”函數
1、提取不重複的值
還記得我們第二次數組教學篇中的Small+Row函數的返回值嗎?如果忘記了,建議還是先點下面的鏈接看看。如果你還記得,可以忽略下方的鏈接繼續讀。
千呼萬喚,“萬金油”函數終於登場了。這是一份模擬數據,
如果我們現在需要統計各銷售員的銷售數量,常規來說,如果有了銷售員的明細,我們用Sumif函數就可以很方便地彙總出來銷售數量,但是我們可以看出,銷售員的姓名是無序而且重複的,如果不借助輔助列,我們就需要用到數組函數來提取銷售員姓名的唯一值了。
函數:單元格A12
{=IFERROR(INDEX($B$2:$B$8,SMALL(IF(MATCH($B$2:$B$8,$B$2:$B$8,0)=ROW($B$2:$B$8)-1,ROW($B$2:$B$8)-1),ROW(A1))),"")}
函數說明:
利用Match函數找到第一次出現的值的行號,形成一個數組數據,再利用Small函數順序的找到出現的行號,用Index函數引用姓名,最後用Iferror函數屏蔽錯誤值。
函數運行過程:
2、提取重複值
上面我們提取了不同姓名的銷售員,那麼我們如果要提取相同姓名的銷售員的銷售記錄又該如何呢?
函數:單元格A12
{=IFERROR(INDEX(A$2:A$8,SMALL(IF(COUNTIF($B$2:$B$8,$B$2:$B$8)=2,ROW($A$2:$A$8)-1),ROW(A1))),"")}
函數說明:
我們將第一例中的Match部分,替換上Countif函數,如果出現兩次就是我們需要的記錄了,其餘函數部分同上例。
函數運行過程:
3、提取滿足條件的明細
還是上面的數據,這次我們來提取銷售員“趙”,賣的“中”碼貨物的明細。
函數:單元格A13
{=IFERROR(INDEX(A$2:A$8,SMALL(IF(($B$2:$B$8=$B$11)*($C$2:$C$8=$C$11),ROW($A$2:$A$8)-1,99^9),ROW(A1))),"")}
函數說明:
和上例不同的地方是,我們唯一改動的就是條件部分,用兩個條件相乘,我們要知道邏輯值是可以參與計算的,True=1、False=0,所以函數中的兩個條件相乘就形成了由0.1組成的數組數據。剩下的運算道理和第一例完全一樣。
函數運行過程:
作者雲:
強大的“萬金油”函數,其實也就是這些主要的功能了。本來還想舉幾個例子,但是除了條件變換了,其他的內容都是一樣的,而且有的例子沒有必要非使用“萬金油”函數,巧妙的使用其它函數結合數組數據一樣可以達到效果。