2015年9月30日 星期三

Excel-每筆分二列摘要資料(陣列公式,OFFSET,SMALL,ROW,INT)

有網友問到:如下圖的 Excel 資料表(右圖),如何將「報名」欄為「Yes」者取出摘要成左圖的結果,其中每個學員的 Email 和電話分成二列呈現。
為了說明方便,選取F欄中所有「報名」欄的內容,定義名稱:報名。

【公式設計與解析】
1. 列出學員姓名
儲存格A2:{=IFERROR(OFFSET($E$1,SMALL(IF(報名="Yes",ROW(報名),FALSE),
INT(ROW(2:2)/2))-1,0,1,1),"")}
此為陣列公式,輸入完成按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
(1) SMALL(IF(報名="Yes",ROW(報名),FALSE)
在陣列公式中,當「報名」欄位為「Yes」時傳回「報名」儲存格陣列的列號,否則傳回「FALSE」。
(2) INT(ROW(2:2)/2))
當公式向下複製時會產生 0, 0, 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, ...。
(3) OFFSET($E$1, 第(1)式, 第(2)式, 0,1,1)
將第(1)式和第(2)式代入 OFFSET 函式,取得對應的儲存格內容。
(4) IFERROR(OFFSET($E$1, 第(1)式, 第(2)式 , 0,1,1),"")
將第 (3) 式傳回值為錯誤訊息者,以空白顯示。
2. 列出學員Email
儲存格B2:{=IFERROR(OFFSET($G$1,SMALL(IF(報名="Yes",ROW(報名),FALSE),
INT(ROW(2:2)/2))-1,0,1,1),"")}
和儲存格A2公式的說明相仿。
3. 列出學員電話
儲存格B3:{=IFERROR(OFFSET($H$1,SMALL(IF(報名="Yes",ROW(報名),FALSE),
INT(ROW(2:2)/2))-1,0,1,1),"")}
和儲存格A2公式的說明相仿。
複製儲存格A2:B3,往下各列貼上。

沒有留言:

張貼留言

檢視其他文章

好康東東