有網友問到：在 Excel 中有一個報名資料總表，如何讓報名結果依梯次顯示在不同工作表中？
如下圖的報名總表，其梯次有三種選項：一月、二月、三月。
如何將資料自動顯示在『一月、二月、三月』不同的工作表中？
【使用樞紐分析表工具】
如果你不想動用公式，而且報名結果不太會變動，建議也可以使用「樞紐分析表」工具來完成。參考以下步驟：
1. 點選[報名總表]工作表中有資料的任一儲存格。
2. 選取[插入／表格]功能表中的「樞紐分析表」選項。
3. 在[建立樞紐分析表]對話框中選取：新工作表。(預設)
產生一個樞紐分析表：
4. 將拖曳『梯次』至「列」欄位，再拖曳『梯次』至「值」欄位
5. 在樞紐分析表中「一月」右側的「10」按二下，即可產生一月的報表。
6. 在二月和三月右側的數字上按二下，即可得二月和三月的報表。
【公式設計與解析】
若要使用公式來篩選想要的資料，則根據原始資料總表，先建立每個欄位的名稱。
因為資料可能會增加，所以假設列1至列100為資料範圍，設定名稱：梯次
梯次→參照到：報名總表!$B$2:$B$100／領域：活頁簿
在[一月]工作表中輸入以下公式：
儲存格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)式,"")
最後，將如果式子傳回錯誤訊息時，設定以空字串("")顯示。
留言列表