2015年12月11日 星期五

Excel-取出每列有數值最左(右)欄的內容及對應的欄位(INDEX,COLUMN)

網友有興趣想要找出在 Excel 中的資料表(如下圖)中,每列最左欄或最右欄的數值及其對應的欄位名稱,該如何處理?
一、取出每列有數值最左欄的內容及對應的欄位
image
1. 使用陣列公式
(1) 最左欄數值
儲存格G2:{=INDEX(A2:F2,MIN(IF(A2:F2<>"",COLUMN(A2:F2))))}
或 儲存格G2:{=INDEX(A2:F2,MIN((A2:F2<>"")*COLUMN(A2:F2)))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
(2) 對應的欄位
儲存格H2:{=INDEX($A$1:$F$1,MIN(IF(A2:F2<>"",COLUMN(A2:F2))))}
或 儲存格H2:{=INDEX($A$1:$F$1,MIN((A2:F2<>"")*COLUMN(A2:F2)))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
複製儲存格G2:H2,往下各列貼上。

2. 使用 SUMPRODUCT 函數
(1) 最左欄數值
儲存格G2:=INDEX(A2:F2,SUMPRODUCT(SMALL((A2:F2<>"")*COLUMN(A2:F2),
COLUMN(F3)-SUM(1*(A2:F2<>""))+1)))
(2) 對應的欄位
儲存格H2:=INDEX(A1:F1,SUMPRODUCT(SMALL((A2:F2<>"")*COLUMN(A2:F2),
COLUMN(F3)-SUM(1*(A2:F2<>""))+1)))

二、取出每列有數值最右欄的內容及對應的欄位
image
1. 使用陣列公式
(1) 最右欄數值
儲存格G2:{=INDEX(A2:F2,MAX(IF(A2:F2<>"",COLUMN(A2:F2))))}
或 儲存格G2:{=INDEX(A2:F2,MAX((A2:F2<>"")*COLUMN(A2:F2)))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。
(2) 對應的欄位
儲存格H2:{=INDEX($A$1:$F$1,MAX(IF(A2:F2<>"",COLUMN(A2:F2))))}
或 儲存格H2:{=INDEX($A$1:$F$1,MAX((A2:F2<>"")*COLUMN(A2:F2)))}
輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。

2. 使用 SUMPRODUCT 函數
(1) 最右欄數值
儲存格G2:=INDEX(A2:F2,SUMPRODUCT(MAX((A2:F2<>"")*COLUMN(A2:F2))))
(2) 對應的欄位
儲存格H2:=INDEX($A$1:$F$1,SUMPRODUCT(MAX((A2:F2<>"")*
COLUMN(A2:F2))))

沒有留言:

張貼留言

好康東東