2014年12月24日 星期三

Excel-將資料由垂直方向轉水平方向呈現(OFFSET,MATCH)

學校的同仁遇到這樣的問題:一般由系統轉出的 Excel 資料表如下圖這樣,其中一個人的資料分成不同列且數量不一,如果要將每一個人的資料再利用 Word 的合併列印功能來輸出成其他文件,每個學生要印在一張報表上,很顯然這個表格無法直接套用在合併列印當為資料檔。

你可能希望轉換成以下的表格,才能在 Word 文件中做為合併列印的資料表:

公式如何設計呢?

首先要注意到資料表中必須要有一個唯一值,例如本例中的「學號」。接著要將重覆的學生資料去除,留下每人一筆。

1. 選取所有資料的儲存格。

2. 按一下[插入/表格]功能表中的「表格」。

3. 在[建立表格]對話框中,按下[確定]按鈕,將儲存格範圍轉換為表格。

4. 點選[設計/工具]功能表中的「移除重覆」按鈕。

5. 在[移除重覆]對話框中,取消勾選「科目、成績」選項,按下[確定]按鈕。

Excel 會告訴你有多少資料被移除:

在已經去除重覆資料的表格上,再按一下「轉換為範圍」,即可去除表格回到儲存格範圍。

或是選取資料後,按一下[資料/資料工具]功能表中的「移除重複」:

image[2]

只勾選:學號、姓名,按下[確定]按鈕。

image[5]

接著來設定公式:

選取有學號的資料範圍,按一下  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。

沒有留言:

張貼留言

檢視其他文章

好康東東