學不完．教不停．用不盡

網友問到：如下圖，要如何找出各個「項目」裡「機率」大於某個數值的「最大日期」？

其中共有「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 函數的應用