陣列公式~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) 之說明。
結果如下
留言
張貼留言