網友問到：如下圖，要如何找出各個「項目」裡「機率」大於某個數值的「最大日期」？
其中共有「A、B、C、D」四個項目，下圖是要找出各個項目中機率大於或等於50%的最大日期。
【公式設計與解析】
1. 定義儲存格範圍的名稱
先選取儲存格A1:C24，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期、項目、機率。
2. 找出各個項目裡機率>=50%的最大日期
儲存格F3：
{=OFFSET($A$1,MAX(IF((項目=E3)*(機率>=0.5),ROW(日期),""))-1,0)}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格F3，貼至儲存格F3:F6。
(1) (項目=E3)*(機率>=0.5)
利用雙條件：（其中「*」相當於執行邏輯 AND 運算）
項目=E3：項目的陣列裡和儲存格E3相同者。
機率>=0.5：機率大於或等於50%
運算結果為 TRUE 者傳回「1」；運算結果為 FALSE 者傳回「0」。
(2) IF((項目=E3)*(機率>=0.5),ROW(日期),"")
假如滿足雙條件 (項目=E3) AND (機率>=0.5)，傳其對應日期的列號。
其中 ROW 函數會傳回儲存格列號，例：ROW(A1)=2、ROW(3:3)=3。
(3) MAX(IF((項目=E3)*(機率>=0.5),ROW(日期),""))
利用 MAX 函數找出第(2)式傳回的日期列號中的最大值（5）。
(4) OFFSET($A$1,MAX(IF((項目=E3)*(機率>=0.5),ROW(日期),""))-1,0)
將第(3)式傳回的日期列號最大值，代入 OFFSET 函數再傳回對應的儲存格內容。
公式中的「-1」，乃是因為第(3)式傳回的列號是「絶對位置」，例如第７列傳回７。而 OFFSET 函數中是採用儲存格A1起始的「相對位置」，而且以第 0 列為第一個位置。（對應至「2020/02/25」）
3. 找出各個項目裡機率>=50%的最大日期所對應的數值
儲存格G3：
{=OFFSET($C$1,MAX(IF((項目=E3)*(機率>=0.5),ROW(日期),""))-1,0)}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格F3，貼至儲存格F3:F6。
公式的運作原理同「2. 找出各個項目裡機率>=50%的最大日期」。
【延伸閱讀】
參考：本部落格中其他關於 Excel OFFSET 函數的應用
留言列表