2016年8月7日 星期日

Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)

有網友問到:在 Excel 中有一個報名資料總表,如何讓報名結果依梯次顯示在不同工作表中?
如下圖的報名總表,其梯次有三種選項:一月、二月、三月。
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)
如何將資料自動顯示在『一月、二月、三月』不同的工作表中?
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)

【使用樞紐分析表工具】
如果你不想動用公式,而且報名結果不太會變動,建議也可以使用「樞紐分析表」工具來完成。參考以下步驟:
1. 點選[報名總表]工作表中有資料的任一儲存格。
2. 選取[插入/表格]功能表中的「樞紐分析表」選項。
3. 在[建立樞紐分析表]對話框中選取:新工作表。(預設)
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)
產生一個樞紐分析表:
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)
4. 將拖曳『梯次』至「列」欄位,再拖曳『梯次』至「值」欄位
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)
5. 在樞紐分析表中「一月」右側的「10」按二下,即可產生一月的報表。
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)
6. 在二月和三月右側的數字上按二下,即可得二月和三月的報表。
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)

【公式設計與解析】
若要使用公式來篩選想要的資料,則根據原始資料總表,先建立每個欄位的名稱。
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)
因為資料可能會增加,所以假設列1至列100為資料範圍,設定名稱:梯次
梯次→參照到:報名總表!$B$2:$B$100/領域:活頁簿
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)
在[一月]工作表中輸入以下公式:
Excel-將報名資料依梯次顯示在不同工作表中(OFFSET,ROW,陣列公式)
儲存格A2:{=IFERROR(OFFSET(報名總表!$A$1,SMALL(IF(梯次="一月",
ROW(梯次),""),ROW(1:1))-1,COLUMN(A:A)-1,1,1),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{}」。
複製儲存格A2,貼至儲存格A2:D100。
(1) IF(梯次="一月",ROW(梯次),"")
判斷若是梯次儲存格陣列中的內容和「一月」是否相同,若相同,則利用 ROW 函數傳回列號的陣列;否則,傳回空字串("")。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數取出第(1)式中的列號陣列之最小值(ROW(1:1)=1)。若公式向下複製,則 ROW(1:1)=1→ROW(2:2)=2→ ... →ROW(100:100)=100。分別可以取得第 1, 2, ..., 100 個最小值。
(3) OFFSET(報名總表!$A$1,第(2)式-1,COLUMN(A:A)-1,1,1)
利用第(2)式傳回的列號代入 OFFSET 函數取得對應的儲存格。 當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→COLUMN(D:D)=4。
分別可以取得A欄、B欄、C欄、D欄的資料內容。
(4) IFERROR(第(3)式,"")
最後,將如果式子傳回錯誤訊息時,設定以空字串("")顯示。

沒有留言:

張貼留言

好康東東