網友有興趣想要找出在 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))))

arrow
arrow
    全站熱搜

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