2018年6月1日 星期五

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

在 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)式內容。)

沒有留言:

張貼留言

檢視其他文章

好康東東