陣列公式~EXCEL(很難)

最近有個朋友問我，要如何在一堆資料裡面找所有A之中的最小值，

例如在表中我要找表中的類別C，然後再找出C之中數量的最小值，

有類別C是 C200(216)，C400(514)，C600(890)，則最小值是C200(216)，

我所知道的函數裡面有COUNTIF，SUMIF，但是就是沒有MAXIF，MINIF這些函數，

要達到MAXIF和MINIF的效果的話，就需要用到陣列公式

第一步：先定義資料表

選取儲存格A1:D10，按一下 Ctrl+Shift+F3 鍵，選【頂端列】，

EXCEL就會自動定義：項目、類別、編號、數量。

第二步：

儲存格G6輸入：=MIN(IF(B2:B10=G2,D2:D10,FALSE))

輸入完之後先別急著按ENTER或是離開公式，這時候按一下 Ctrl+Shift+F3 鍵，

公式就會自己變成{=MIN(IF(B2:B10=G2,D2:D10,FALSE))}，

{}不能手動直接KEY進去，一定要按快捷建生成，

這樣就完成了，所謂的陣列公式，如下圖。

解決了朋友的問題之後，再解決下一個問題，第2個問題雖然感覺上很容易懂的敘述，

但是不用VBA巨集只靠函數方式完成真的還是需要想想，第2個問題是，

找到最小數值216之後，還要顯示最小數值的類別的所有數值，

也就是說216是屬於類別C，就要另外顯示類別C的所有數量(216、514、890)，

這也是要使用到陣列公式，做法如下

儲存格H2：{=IFERROR(INDEX(資料,SMALL(IF(類別=$G$2,項目,FALSE),ROW(1:1))+1,3),"")}

儲存格I2：{=IFERROR(INDEX(資料,SMALL(IF(類別=$G$2,項目,FALSE),ROW(1:1))+1,4),"")}

填完公式之後把H2和I2選起來，往下拉大約10格欄位左右即可，如下圖

當變換G2裡面的類別，旁邊也會一起變動，如下圖

【感想】

要在不使用巨集的情況下完成很有挑戰性，缺點是當EXCEL裡面的公式欄位越多，

開啟EXCEL就會越久，甚至切換頁面的時候也會LAG，

不過經過這次我又了解EXCEL的另外一面，真的很神奇，學也學不完，

要感謝我的朋友出的難題。

-------------------------------------------------------------------------------------

【應用範例】

「國文>80且英文>75者的數學最高分」和「五科均及格者的英文最高分」

公式：

事前工作：先定義

選取儲存格B2:F26，按一下 Ctrl+Shift+F3 鍵，定義名稱：國文、英文、數學、社會、自然。

(A)找出國文>80且英文>75者的數學最高分

儲存格H2：{=MAX((國文>80)*(英文>75)*數學)}

解說：

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

國文>80：在陣列公式中產生國文>80 的 True/False 陣列。

英文>75：在陣列公式中產生英文>75 的 True/False 陣列。

(國文>80)*(英文>75)：其中的「*」執行 AND 邏輯運算，二者均為 True 時，結果才為 True。

(國文>80)*(英文>75)*數學)：第二個「*」為執行「乘法」算術運算。而當 True/False 陣列執行算術運算時 True 視為 1、False 視為 0。本式子的結果會得符合二個條件者的數學成績陣列，不會任一個條件者的數學成績會被視為 0。

透過 MAX 函數從上式的數學陣列中取出最大值。

(B)找出五科均及格者的英文最高分

儲存格H5：{=MAX((國文>=60)*(英文>=60)*(數學>=60)*(社會>=60)*(自然>=60)*英文)}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵。

原理同 (1) 之說明。

結果如下