贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到:在 Excel 的工作表中有個類別和次數的清單(下圖左),如何根據這個清單列出各類的最大值(下圖右)?

Excel-找出清單中同類項目的最大值(陣列公式)

 

【公式設計與解析】

儲存格E2:{=MAX(IF(A2:A20=D2,B2:B20,FALSE))}

這是陣列公式,輸入完成按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。

複製儲存格E2,貼至儲存格E2:E7。

IF(A2:A20=D2,B2:B20,FALSE):在陣列公式中,判斷儲存格A2:A20的內容是否和儲存格D2相同。如果是則傳回對應的B欄儲存格,否則傳回 FALSE

Excel-找出清單中同類項目的最大值(陣列公式)

MAX(IF(A2:A20=D2,B2:B20,FALSE)):在陣列公式中,根據傳回對應的B欄儲存格,再以 MAX 函數取出其中的最大值。

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

網友想要根據下圖中的 Excel 資料清單,補足連續分鐘數缺漏的部分(下圖左有底線位置即有缺漏),下圖右紅色文字即為補上的分鐘數,該如何處理這樣的問題?

在不連續的時間序列中補足分鐘數(ROUND,OFFSET,MATCH,陣列公式)

 

【公式設計與解析】

 

1. 產生連續的分鐘數

儲存格G3:=G2+1/24/60

因為在 Excel 中一天以「1」來表示,所以每一分鐘表示為「1/24/60」。

複製儲存格G3,貼至儲存格G3:G101。(假設要產生100分鐘的資料)

 

2. 資料查表

儲存格H2:{=IFERROR(OFFSET($C$2,MATCH(ROUND(G2,5),
ROUND($B$2:$B$101,5),0)-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel自動產生「{}」。

因為時間轉換出來的數值太精密,所以在使用 MATCH 函數時無法正確比對,因此要調整一下,只取到小數點第5位。

(1) ROUND(G2,5)

將儲存格G2的數值取小數點5位。

(2) ROUND($B$2:$B$101,5)

在陣列公式中,將儲存格B2:B101的數值全部取小數點5位。

(3) MATCH(ROUND(G2,5),ROUND($B$2:$B$101,5),0)-1,0)

在儲存格B2:B101中比對和儲存格G2相同者,傳回其列號。

(4) OFFSET($C$2,第(3)式,0)

利用 MATCH 函數傳回的列號代入 OFFSET 函數,查詢對應的儲存格內容。

(5) IFFERROR(OFFSET($C$2,第(3)式,0),"")

利用 IFFERROR 函數,將傳回錯誤訊息的儲存格轉換為空字串。(何時會出現錯誤呢?在原始資料清單中缺漏的秒數,將會查詢不到任何資料,即會傳回錯誤訊息。)

同理:

儲存格I2:{=IFERROR(OFFSET($D$2,MATCH(ROUND(G2,5),
ROUND($B$2:$B$101,5),0)-1,0),"")}

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

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼