Power Query的使用-交叉比對合併資料

要製作競賽成績登錄表單，假設50位學生參賽，14位評審，2個比賽項目。

1-兩個表格-「學生資料」和「評分教師」

將 Excel 資料表轉成 Table（表格）

點選資料範圍 → Ctrl + T

「我的表格有標題」打勾

欄位上方「表格設計」→ 修改「表格名稱」

將表格名稱改為「學生資料」和「評分教師」（若沒修改，就只會顯示表格1、表格2、...）

  

2-選取「學生資料」表格

上方欄位「資料」→「從表格/範圍」

（「資料」-->「取得資料」-->「從EXCEL活頁簿」，匯入後點選多重項目，再「轉換資料」，接著用「合併查詢」更快速～）

3- 上方欄位「新增資料行」→「自訂資料行」

4-新增資料行名稱「比對」

自訂資料行公式填入「1」

右下方按「確定」

5-上方「常用」→「關閉並載入」

6-步驟2-5，對『評分教師』再做一次

7-完成後會增加表格：

學生名單：修課學生資料。

評分教師：評分老師名單。

評分項目：1-口頭報告。2-壁報展示。

學生名單_1：Power Query整理後的學生名單。

評分教師_1：Power Query整理後的教師名單。

評分項目_1：Power Query整理後的評分項目。

8-移到「學生資料Power Query整理」的那個工作表

上方「查詢」→「合併」

9-點選要比對的表格與項目

先點選「比格1」的比對，會變成綠色底。

先點選「比格2」的比對，也會變成綠色底。

連接種類保持預設不用動。

按「確定」

10-展開教師資料

最右邊會多一欄：表格2

表格2右邊有一個雙引號，點選雙引號。

取消「使用原始欄名作為前置詞」。（這一項不要勾選）

按「確定」

刪除「比對」欄位，留下要的結果。

上方「常用」→「關閉並載入」

Excel 就會產生一張新工作表「合併1」。

 

11-如果有50位學生，14位教師，最後會有：50 × 14 = 700 列的完整評分資料。

開一個新工作表，複製貼上值。

右方新增欄位「成績」。

12-若是要增加「項目」，例如：「評分項目」

步驟2-10再做一次

如果有50位學生，14位教師，2個評分項目，最後會有：50 × 14 × 2= 1400 列的完整評分資料。

13-登錄成績後，利用樞紐分析，彙整最後成績。