網友問到:如下的 Excel 資料表,如何從表格中找出最大值,並且傳回其欄、列名稱?

如下圖,欄標題:甲、乙、…、癸,列標題:子、丑、…、亥。假設資料中的數值,其中沒有重複的內容。

Excel-查詢表格最大值傳並回欄列標題(OFFSET,COLUMN,ROW,MAX)

 

【公式設計與解析】

選取儲存格B2:K13,定義名稱:DATA。

1. 找出最大值

儲存格M2:=MAX(B2:K13)

 

2. 找出欄標題

{=OFFSET(B1,0,MAX((DATA=M2)*COLUMN(DATA))-2)}

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

MAX((DATA=M2)*COLUMN(DATA)):利用陣列公式,找出最大值的「欄號」(B=2, C=3, … ,  J=10, K=11)。

DATA=M2會產生 TRUE/FALSE 陣列,再乘以 COLUMN(DATA) 後,只有最大值所在欄會傳回其欄號,其餘均傳回 0。利用 MAX 函數取出這個最大值的欄號(非欄標題)。

OFFSET(B1,0,MAX((DATA=M2)*COLUMN(DATA))-2):利用上式傳回的欄號找出對應的儲存格內容。

 

3. 找出列標題

{=OFFSET(A2,MAX((DATA=M2)*ROW(DATA))-2,0)}

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

MAX((DATA=M2)*ROW(DATA)))):利用陣列公式,找出最大值的「列號」。

DATA=M2會產生 TRUE/FALSE 陣列,再乘以 ROW(DATA) 後,只有最大值所在列會傳回其列號,其餘均傳回 0。利用 MAX 函數取出這個最大值的列號(非列標題)。

OFFSET(A2,MAX((DATA=M2)*ROW(DATA))-2,0):利用上式傳回的列號找出對應的儲存格內容。

arrow
arrow
    全站熱搜

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