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

其中共有「A、B、C、D」四個項目,下圖是要找出各個項目中機率大於或等於50%的最大日期。

Excel-找出各個項目裡機率大於某個數值的最大日期(OFFSET,ROW)

 

【公式設計與解析】

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」。

Excel-找出各個項目裡機率大於某個數值的最大日期(OFFSET,ROW)

 

(2) IF((項目=E3)*(機率>=0.5),ROW(日期),"")

假如滿足雙條件 (項目=E3) AND (機率>=0.5),傳其對應日期的列號。

其中 ROW 函數會傳回儲存格列號,例:ROW(A1)=2、ROW(3:3)=3。

Excel-找出各個項目裡機率大於某個數值的最大日期(OFFSET,ROW)

 

(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)式傳回的列號是「絶對位置」,例如第7列傳回7。而 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 函數的應用

學不完.教不停.用不盡文章列表

arrow
arrow

    文章標籤

    Excel OFFSET ROW

    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()