學不完．教不停．用不盡

本篇要在 Excel 裡根據系統匯出的成績資料來做統計分析表（如下圖），藉此說明 Excel 在資料處理上的使用。基本上只使用了三個公式和排序工具的操作。

當取得一個系統匯出的成績表，是以班級為單位，每個班級一個區塊。（如下圖）

我的做法是：

１. 將原始資料中的「科目」列和「總平均」列取出。

儲存格A1：=OFFSET(原始!$A$2,(INT(ROW(1:1)/2))*18,COLUMN(A:A))

2. 透過排序的操作，將所有科目集合在一起。

依第一欄排序的動作會將有文字和沒文字的儲存格分開來，如此便可取得將科文集合在一起。

3. 將所有科目內容集合在一個儲存格。

儲存格A41：=TEXTJOIN(",",,A1:V38)

運用 TEXTJOIN 函數將所有儲存格內容串接，並且以「,」分隔。

4. 將所有科目分在同一列的多個儲存格。

儲存格A44：=TEXTSPLIT(A41,",")

運用 TEXTSPLIT 函數將所有儲存格內容依「,」分隔符號分別放置多個儲存格。

5. 將所有儲存格轉成同一欄。

儲存格A47：=TRANSPOSE(A44#)

運用 TRANSPOSE 函數將轉置所有儲存格。

6. 取出所有科目的唯一值。

儲存格A=UNIQUE(A47#)

以上四個步驟可以合併為：

公式：=UNIQUE(TRANSPOSE(TEXTSPLIT(TEXTJOIN(",",,A1:V38),",")))

7.透過排序工具的操作取得所有班級的名稱。

8.建立成績總表

複製所有科目為科目欄位，複製所有班級為欄標題，輸入公式：

=IFERROR(OFFSET($A$1,MATCH(AA$1,$A$1:$A$76,0),MATCH($Z2,INDIRECT(AA$1),0)),"")

【參考資料】

 UNIQUE 函數參考微軟提供的說明：UNIQUE 函數

TEXTSPLIT 函數參考微軟提供的說明：TEXTSPLIT 函數

TEXTJOIN 函數參考微軟提供的說明：TEXTJOIN 函數

TRANSPOSE 函數參考微軟提供的說明：TRANSPOSE 函數

OFFSET 函數參考微軟提供的說明：OFFSET 函數

INDIRECT 函數參考微軟提供的說明：INDIRECT 函數

MATCH 函數參考微軟提供的說明：MATCH 函數

INT 函數參考微軟提供的說明：INT 函數

ROW 函數參考微軟提供的說明：ROW 函數

COLUMN 函數參考微軟提供的說明：COLUMN 函數

IFERROR 函數參考微軟提供的說明：IFERROR 函數