學校的同仁遇到這樣的問題：一般由系統轉出的 Excel 資料表如下圖這樣，其中一個人的資料分成不同列且數量不一，如果要將每一個人的資料再利用 Word 的合併列印功能來輸出成其他文件，每個學生要印在一張報表上，很顯然這個表格無法直接套用在合併列印當為資料檔。
你可能希望轉換成以下的表格，才能在 Word 文件中做為合併列印的資料表：
公式如何設計呢？
首先要注意到資料表中必須要有一個唯一值，例如本例中的「學號」。接著要將重覆的學生資料去除，留下每人一筆。
1. 選取所有資料的儲存格。
2. 按一下[插入／表格]功能表中的「表格」。
3. 在[建立表格]對話框中，按下[確定]按鈕，將儲存格範圍轉換為表格。
4. 點選[設計／工具]功能表中的「移除重覆」按鈕。
5. 在[移除重覆]對話框中，取消勾選「科目、成績」選項，按下[確定]按鈕。
Excel 會告訴你有多少資料被移除：
在已經去除重覆資料的表格上，再按一下「轉換為範圍」，即可去除表格回到儲存格範圍。
或是選取資料後，按一下[資料／資料工具]功能表中的「移除重複」：
只勾選：學號、姓名，按下[確定]按鈕。
接著來設定公式：
選取有學號的資料範圍，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：學號。
(1) 抓取科目
儲存格H2：=IF(OFFSET($A$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0)=$F2,
OFFSET($C$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0),"")
公式看來有點複雜，細部剖開來看：
MATCH($F2,學號,0)：利用 MATCH 函數找出儲存格F2的內容在「學號」儲存格範圍的位置，傳回代表第幾個的數字。
INT((COLUMN(A:A)-1)/2)：因為 COLUMN(A:A)=1，若向右複製公式時，會自動產生 COLUMN(B:BA)=2、COLUMN(C:CA)=3、…。將 COLUMN(A:A)-1 除以 2，再代入 INT 函數，會在H欄傳回 0、J欄傳回 1、L欄傳回 2、… 。
OFFSET($C$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0)：將以上二式代入 OFFSET 函數可以查詢到以儲存格C2為開始的相對位置之儲存格，即會傳回儲存格C2，若向右複製公式時，會在H欄傳回儲存格C2、J欄傳回儲存格C3、L欄傳回儲存格C4、… 。
同理，判斷公式：OFFSET($A$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0)=$F2的結果是否和對應的學號(儲存格F2)是否相同，如果一樣則代表同一學生，所以要顯示科目內容，如果不一樣則代表不同學生，所以顯示空白。
(2) 抓取成績
儲存格I2：=IF(OFFSET($A$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0)=$F2,OFFSET($D$2,MATCH($F2,學號,0)-1+INT((COLUMN(A:A)-1)/2),0),"")
公式改變很小，只是將(1)的 OFFSET($C$2 改成 (2)的 OFFSET($D$2。
最後複製儲存格H2:I2，貼至其他資料範圍，本例為儲存格H2:S32。
【相關文章】
留言列表