有網友問到:在 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)式,"")

最後,將如果式子傳回錯誤訊息時,設定以空字串("")顯示。

    全站熱搜

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