2016年9月2日 星期五

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

網友問到:如下的 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):利用上式傳回的列號找出對應的儲存格內容。

沒有留言:

張貼留言

好康東東