在 Excel 中有一個訂購清單(下圖左),如何轉換為如下圖右的格式?(原本直式的清單,轉變為表格式的清單)

Excel-將直式的清單轉變為表格式的清單(OFFSET,MATCH,COUNTIF)

 

【公式設計與解析】

選取儲存格A1:A25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:訂購者。

儲存格E2:=IF(COLUMN(A:A)<=COUNTIF(訂購者,$D2),OFFSET($B$1,
MATCH($D2,訂購者,0)+COLUMN(A:A)-1,0),"")

選取儲存格E2,複製到儲存格E2:H9。

(1) MATCH($D2,訂購者,0)

找尋儲存格D2,在訂購者陣列中的第一個位置。(傳回一個數值)

(2) OFFSET($B$1,MATCH($D2,訂購者,0)+COLUMN(A:A)-1,0)

將第(1)式傳回的數值代入 OFFSET 函數傳回對應的儲存格內容。當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

(3) COLUMN(A:A)<=COUNTIF(訂購者,$D2)

判斷COLUMN(A:A)=1是否小於或等於儲存格D2在訂購者陣列中的數量。當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

(4) IF(COLUMN(A:A)<=COUNTIF(訂購者,$D2),第(2)式,"")

用以在顯示超過儲存格D2在訂購者陣列中的數量時,顯示空白。(否則,顯示第(2)式內容。)

arrow
arrow
    文章標籤
    Excel OFFSET MATCH COUNTIF
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

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