網友根據這篇文章:Excel-表格的轉換(OFFSET,SUMPRODUCT,ROW),問到想要如下圖中由B表格查詢A表格,該如何處理?

Excel-表格的轉換(OFFSET,SMALL,COLUMN,ROW)

 

【公式設計與解析析】

儲存格E3:{=IFERROR(OFFSET($A$1,SMALL(IF($B$3:$B$18=$D3,ROW
($B$3:$B$18),""),COLUMN(A:A))-1,0),"")}

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

複製儲存格E3,貼至儲存格E3:H12。

 

(1) IF($B$3:$B$18=$D3,ROW($B$3:$B$18),"")

在陣列公式中,判斷儲存格B3:B18範圍和儲存格D3是否相同。若是,則傳回儲存格的『列號』;若否,則傳回空字串("")。

 

(2) SMALL(第(1)式,COLUMN(A:A))

SMALL 函數中代入第(1)式,傳回第(1)式所得『列號』的最小值。當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→ ... →COLUMN(D:D)=4,可取得第 1, 2, 3 ,4 的最小值 。

 

(3) OFFSET($A$1,第(2)式-1,0)

OFFSET 函數中代入第(2)式傳回的列號,即可取得A欄中的儲存格內容。公式中的「-1」是因為儲存格的列是由1起始(1,2,3,...),而 OFFSET 函數中的第 2 個參數要代入第幾列,是由 0 開始。

 

(4) IFERROR(第(3)式,"")

因為在第(1)式中如果是傳回空字串時,會導致公式出現錯誤訊息。因此,使用 IFERROR 函數將錯誤訊息以空字串顯示(看到空白儲存格)。

arrow
arrow
    全站熱搜

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