但是,用"陣列公式"方法,可以做出同樣效果.
當分類是Z3時,
最大=40 (C11/D11 公式)
最小=35 (C15/D15 公式) 不理空白, C14/D15 為處理空白
公式上{ } 不是直接輸入的, 而是在輸入公式 如 =MAX(IF(B2:B9=”Z3”,C2:C9,0)) 後,
按著鍵盤的"CTRL", "SHIFT"然後按ENTER.
這個稱為"陣列公式", 下面會再詳細一點講解.
相同最大值, 最先,第二,最後位置,
解說:
A. MAX IF: 根據特定條件找出範圍內最大值
上面的公式可分為IF 和MAX兩部份,
{=MAX(IF(B2:B9=”Z3”,C2:C9,0))}
第1步: IF部份, 陣列公式會把上方IF(B2:B9...,C2:C9,0)的公式,
化為下圖紅圈下的數行公式 (E2到E9, F為E欄公式結果).
把BC兩欄位置1對1對地放上每一行.
第2行:IF(B2="Z3",C2,0)
第3行:IF(B3="Z3",C3,0)
...
第9行:IF(B9="Z3",C9,0)
而普通的IF (沒有 { } 下),只能回傳1個數值,即紅圈內其中一行.
G欄顯示每行條件 B(行數)="Z3" 得出的"真/假"值
(下圖G1上 ?是代表 代入第幾行的意思)
第2步: MAX部份 E11公式,
就是在上方IF回傳的多個值(E2到E9) 找出最大的, 即40
在輸入公式時,如果不按下CTRL+SHIFT,只按下ENTER,
因為IF會不能處理多於一個輸入位置 B2:B9, 所以會回傳#VALUE!錯誤
B. MAXIF: 據特定條件找出 最大值 的 最後出現位置
為簡單起見,這個公式用C13得出來的最大值
跟MAXIF一樣分為兩部份, IF 和 MAX
{=MAX(IF((B2:B9="Z3")*(C2:C9=C13),ROW(C2:C9),0))}
第1步: IF部份, 通過陣列公式, 分拆成數行IF(紅圈)
先解說一下IF條件下 * 的用途,以第1行IF為例, *把兩個條件(B2="Z3", C2=40)結合,
等同於IF(AND(條件1,條件2),回傳1,回傳2)用法.
當兩個條件同時成立, IF就會回傳"回傳1", 如果其中一個條件或全部不成立,就回傳"回傳2"
下圖 I欄 為 條件1的結果, H欄為經過 條件1篩選後 條件2 的結果
AND在陣列公式時不能發揮作用,所以要用上*.
ROW(C7)回傳當行的行數, 不可只放ROW(),
因為要回傳的不是陣列公式所在行數,而是每一行數據的行數.
第2步: 在上方IF回傳的多個值(F2到F9) 找出最大的, 即是除0以外的7.
C. MAXIF: 據特定條件找出 最大值 的 最先/第二次出現位置
當有兩個最大值同時出現,
C3 , C6和C7 放同一個最大值40, 得出來的行數也是7, 因為7比3和6大.
找出最先行數 (見下圖)
{=MIN(IF((B2:B9="Z3")*(C2:C9=C13),ROW(C2:C9),99999))}
把IF回傳和MAX反轉, 0轉成99999, MAX轉成MIN,
可得出最先行數: 3.
找出第2最先行數:{=SMALL(IF((B2:B9="Z3")*(C2:C9=C13),ROW(C2:C9),99999),2)}
利用最先行數的方法, 再用SMALL(數組,第幾位) 公式找出第2小值: 6{=SMALL(IF((B2:B9="Z3")*(C2:C9=C13),ROW(C2:C9),99999),3)}
SMALL(數組,3) 這樣的話會回傳7
D. MAXIF: 據特定條件找出 最大值的 最後出現位置的 相關項目
在B部份公式上加上INDEX公式,用B部份得出來的行數,找出對應格子
{=INDEX(A1:A9,MAX(IF((B2:B9="Z3")*(C2:C9=C11),ROW(C2:C9),0)))}
MAX(IF()) 回傳位置 (第7行).
變成以下公式:=INDEX(A1:A9,7)Index 回傳A1:A9 下第7格資料, 即"D"
E. MINIF: 根據特定條件找出範圍內最小值跟MAXIF一樣分為兩部份, IF 和 MIN
{=MIN(IF(B2:B9="Z3",C2:C9,99999))}
第1步: IF部份, 通過陣列公式, 分拆成數行IF(紅圈) (詳看A MAX IF部份陣列公式解說)
不過跟MAXIF上的IF有一點不同的是, 如果不是Z3, 就回傳的是一個實數99999.
第2步: MIN部份,先看Z3,有4個值: 37,35,空白(0),40
最少的為空白0, 所以結果得出是0
而且 Z1/Z2/Z4的IF回傳出來的都是99999,一定比0大,
所以總括來說,整個公式會回傳Z3的0
99999只是一個任意的大數, 無論是用99, 一或用999999,只要比所有的大就可.
如果把99999設為-5, 那麼Z1/Z2/Z4的值比Z3最小的0還小, 那就找不出想要的Z的結果
F. MINIF: 根據特定條件找出範圍內最小值, 不算空白
{=MIN(IF((B2:B9="Z3")*(NOT(ISBLANK(C2:C9))),C2:C9,99999))}
第1步: 陣列公式把公式分拆成數行IF的公式 (E/F欄).
參考 B.MAXIF 位置的做法, 用*把兩個IF條件 B?="Z3" 和 ISBLANK(C?) 連起.
NOT(ISBLANK()) 用來檢查格子是否 "不是空白".
當兩個條件同時成位時回傳C?的內容,否則回傳99999.
第6行雖然是分類是"Z3", 因為QTY是空白,所以最後也回傳了99999
第2步: MIN部份 把IF得出來的 999999,37,35,40中找出最小的, 最後找出 35.
G. MINIF: 最小值位置
{=MAX(IF((B2:B9="Z3")*(C2:C9=C15),ROW(C2:C9),0))}
方法同B部份 "MAXIF 最大值位置" 一樣
第1步: IF部份,陣列公式把IF公式化為數行(E欄)
每一個IF公式下, B?="Z3" (I欄) 和 C?=35 (H欄) 兩個條件通過 *號 結合 .
當兩個條件同時成立時,就回傳ROW(C?),否則回傳0. (?為行數)
第2步:MAX部份,把IF找出來的所有結果(F2:F9) 的最大值抽出(E11位置公式),
最後的行數抽出
如果想找出最先的行數, 可以參考一下C部份的方法:
本篇最新更新: 2012-09-24
沒有留言:
發佈留言