愛比的新手筆記

從推翻自己所寫的開始

2025-01-17

[Excel] Power Query Lab

寫了一些Lab給新手練習

以下資料都lab瞎扯的假資料, 不用在意合理性
lab1.xlsx和lab2.xlsx請用以下資料另存csv開啟

座號,科目,分數,考試日期
6,國文,96,2025/2/3
3,英文,76,2025/2/5
3,數學,82,2025/2/7
6,數學,71,2025/2/7
10,英文,65,2025/2/7
2,英文,50,2025/2/5
7,數學,96,2025/2/7
6,英文,78,2025/2/3
8,數學,84,2025/2/7
2,自然,61,2025/2/7
3,國文,87,2025/2/3
4,國文,59,2025/2/3
5,英文,55,2025/2/3
9,數學,64,2025/2/7
4,數學,91,2025/2/7
7,自然,94,2025/2/3
6,自然,78,2025/2/3
2,國文,81,2025/2/3
5,自然,65,2025/2/7
2,數學,96,2025/2/7
3,自然,57,2025/2/7
10,自然,72,2025/2/3
10,數學,87,2025/2/7
8,國文,72,2025/2/5
4,自然,55,2025/2/7
9,英文,72,2025/2/3
7,國文,57,2025/2/3
4,英文,93,2025/2/5
9,自然,76,2025/2/3
8,自然,82,2025/2/3
8,英文,97,2025/2/3
1,數學,56,2025/2/7
1,自然,89,2025/2/7
9,國文,74,2025/2/5
1,國文,60,2025/2/3
5,國文,70,2025/2/3
7,英文,67,2025/2/3

以下內容, 自己存lab4.txt:

{5:F05TPBKTWTPAFMG5066604600}
{6:O0050544640004CITIGB6LPCLS56645055666400045644N}
{4:{500:C5S0E0A044405404}}
{4:
:60:C5S0E0A044405404
:65:466440AUD5
:66F:D640004AUD50046000,50
:65:466440CAD5
:66F:C640004CAD560400,00
:65:466440CHF5
:66F:D640004CHF9056400,09
:65:466440EUR5
:66F:D640004EUR5405600,00
:65:466440GBP5
:66F:D640004GBP5000500,00
:65:466440HKD5
:66F:C640004HKD50040600,58
:65:466440JPY5
:66F:C640004JPY600045600,
:65:466440NZD5
:66F:C640004NZD50565000,40
:65:466440SEK5
:66F:D640004SEK506000,57
:65:466440SGD5
:66F:D640004SGD56700,66
:65:466440USD5
:66F:C640004USD6504500,00
:65:466440ZAR5
:66F:D640004ZAR050000,00
-}
{5:{CHK:FAB0555B464B}}
{S:{MAN:00500}}

以下內容, 自己存lab5.txt:

	資料日期: 2025/1/16
	報表名稱: ETF除權息資料


股票代號,股票名稱,年度,評價公告日,評價日,除息日,每單位配發金額(元),停止名簿變更日期起,停止名簿變更日期訖,收益分配基準日,收益分配發放日,現金股利殖利率(%),現金股利殖利率(%)
上市0050,元大台灣50,2024,2024/7/1,2024/6/30,2024/7/16,1,2024/7/18,2024/7/22,2024/7/22,2024/8/9,0.5084,
上市0051,元大中型100,2024,2024/11/1,2024/10/31,2024/11/18,2.46,2024/11/20,2024/11/24,2024/11/24,2024/12/12,3.0635,
上市0052,富邦科技,2024,2024/4/1,2024/3/31,2024/4/18,6.43,2024/4/20,2024/4/24,2024/4/24,2024/5/15,4,
上櫃006201,元大富櫃50,2024,2024/11/1,2024/10/31,2024/11/18,0.75,2024/11/20,2024/11/24,2024/11/24,2024/12/12,3.2397,
上櫃006203,元大MSCI台灣,2024,2024/7/1,2024/6/30,2024/7/16,0.9,2024/7/18,2024/7/22,2024/7/22,2024/8/9,0.9683,
上櫃006204,永豐臺灣加權,2024,2024/10/1,2024/9/30,2024/10/24,0.7,2024/10/26,2024/10/30,2024/10/30,2024/11/19,0.5955,
上櫃006208,富邦台50,2024,2024/11/1,2024/10/31,2024/11/18,0.9,2024/11/20,2024/11/24,2024/11/24,2024/12/12,0.8014,
上櫃00660,元大歐洲50,2020,2020/10/8,2020/9/30,2020/10/28,0.36,2020/10/30,2020/11/3,2020/11/3,2020/11/26,1.6158,
上櫃00679B,元大美債20年,2024,2024/11/1,2024/10/31,2024/11/18,0.34,2024/11/20,2024/11/24,2024/11/24,2024/12/12,1.1676,

	主管:		經辦:

Lab1. 基本概念

  1. 開啟Lab1.xlsx, 停留在有字的儲存格
  2. 按常用/格式化為表格,任選一個格式, 按[確定]
  3. 按表格設計/表格名稱, 改成”成績” , 按enter
  4. 按表格設計/轉換為範圍

Lab2. 查詢來源從同Excel

  1. 開啟Lab2.xlsx, 停留在有字的儲存格
  2. 按資料/[從表格/範圍]
    1. 勾選: 我的表格有標題, 按[確定]進入PowerQuery編輯器
    2. 按右上關閉, 捨棄
    3. 範圍已轉成表格
  3. 按資料/[從表格/範圍]
    1. 點[座號]欄位, 按排序, 可看到資料以座號排序
    2. 點[考試日期], 按 資料類型/日期, 可看到欄位左邊顯示日曆圖示
    3. 點[科目]旁的倒三角按鈕, 選國文, 可看到只剩國文資料
    4. 按右邊[查詢設定] 的[x], 會關閉右邊
    5. 按上方檢視表/查詢設定, 可看到右邊[查詢設定]
    6. 改右邊[查詢設定] /[名稱]為”國文成績”, 按Enter
    7. [查詢設定]列出所有動作的步驟, 點[已排序資料列], 可看當時的資料
    8. 點[分數]欄右邊的按鈕/數字篩選/大於…, 會跳詢問, 按[插入], 會跳對話框, 在大於右邊輸入60, 按[確定]
    9. 點到最後一個步驟看結果
    10. 滑鼠移到[已排序資料列], 按[x], 會跳詢問,按[刪除]
    11. 點到最後一個步驟看結果
    12. 點選科目欄位, 按上方 常用/移除資料行
    13. 點選分數欄位, 按F2, 將欄位名改成”國文”
    14. 左邊是作有查詢的清單, 可[<]縮小, 按[>]打開, 目前只有[國文成績]一項
    15. 按上方常用/關閉並載入
    16. 可看到多一個sheet: 國文成績
  4. 重新整理
    1. 儲存, 關閉, 重開Excel
    2. 出現安全性警告時, 按[啟用]
    3. 按上方資料/查詢與連線, 可看到PowerQuery清單
    4. 選國文成績sheet內第2列之後資料列整列, 按右鍵/刪除
    5. 右邊[查詢與連線]清單內, 在[國文成績]按右鍵/重新整理, 可以看到資料變了
    6. 按上方資料/全部重新整理, 也可以看到資料變了
  5. 修改
    1. 到分數Sheet的表格, 按ctrl+a, 按ctrl+c, 再按ctrl+shift+v, 會看到c欄公式都變成值
    2. 進入PowerQuery視窗:
      • 右邊[查詢與連線]清單內, 在[國文成績]按右鍵/編輯, 即可進入PowerQuery視窗
      • 停留國文成績sheet內的表格, 按上方 查詢/編輯, 也可進入PowerQuery視窗
    3. 左邊查詢按右鍵/重複, 就會複製一份出來
    4. 在國文成績 (2)上按F2, 改名為: 數學成績
    5. fx右邊公式的”國文”, 改成”數學”
    6. 點右邊步驟[已篩選資料列], fx右邊公式的”國文”, 改成”數學”
    7. 以上2步驟也可以按上方 常用/進階編輯器, 將所有”國文”2個字改成”數學”
    8. 按上方常用/關閉並載入的倒三角形, 選[關閉並載入至…], 選[只建立連線], 按確定
    9. 資料載入, 所以不會再出現新Sheet
    10. 在右邊[查詢與連線]清單內, 在[國文成績]按右鍵/[載入至…], 選[只建立連線], 按確定, 會跳詢問, 按[確定]
    11. 國文成績sheet內的表格會消失
  6. 合併
    1. 右邊[查詢與連線]清單內, 在[國文成績]按右鍵/編輯, 即可進入PowerQuery視窗
    2. 用第4項方法在左邊查詢清單產生[自然成績]和[英文成績]
    3. 按左邊清單的自然成績, 按上方 常用/合併查詢的倒三角/將查詢合併為新查詢
    4. 下方選[國文成績], 聯結種類選[完整外部] , 上方座號和下方座號欄位反白, 按確定
      *聯結種類常用有前4項, 各位可都試試
    5. 出現[合併1]編輯畫面
    6. 用前2步方法, 合併[數學成績]和[英文成績]
    7. 按[國文成績]欄位右邊按鈕, 取消勾選[使用原始資料行名稱], 會展開成績
    8. 用前2步方法, 展開[數學成績]和[英文成績]
    9. 按上方新增資料行/條件資料行, 新資料行名稱輸入[年度], 輸入如下圖:
    10. 按ctrl同時點選[考試日期]~[考試日期.3]等4個欄位, 在欄位名上按右鍵/移除資料行
    11. 用前2步方法建立[學號]處理[座號]~[座號.3]欄位
    12. 選取年度欄位, 按上方 轉換/日期/年/年
    13. 按ctrl選取學號和年度欄位, 按上方 轉換/移動/到開頭
    14. 按上方常用/關閉並載入的倒三角/關閉並載入至…, 選只建立連線, 按確定
    15. 將國文成績sheet名改成[成績], 停在該sheet, 在右邊[合併1]按右鍵/載入至…, 選表格, 選[目前工作表的儲存格], 清掉內容, 用滑鼠點一下A1, 按確定

 

  1. 樞紐
    1. 到分數Sheet, 按上方 資料/[從表格/範圍], 進入PowerQuery
    2. 點此次於左方新產生的查詢, 按F2更名”樞紐”
    3. 選取 [考試日期] 欄位, 按F2, 改名”年度”, 按上方 轉換/日期/年/年
    4. 選取 [科目] 欄位, 按上方 轉換/樞紐資料行, 選擇[分數], 即產生與[合併1]一樣結果表格, 但由於日期先處理了, 所以同座號且同年度會在同一筆
    5. 按shift將4個科目欄位選取, 按上方 轉換/取消資料行樞紐, 就會將4個科目當作值放入[屬性]欄
  2. 附加
    1. 在左邊”樞紐”按右邊/參考, 會新產生一個查詢, 按F2更名”附加”
    2. 選[值]欄, 按F2更名[國文]
    3. [屬性]欄位篩選[英文]
    4. 刪除屬性欄位
    5. 按常用/附加查詢, 選[國文成績], 按確定
      • 或選三(含)個以上的資料表, 將要附加資料表新增>>到右邊, 按確定
    6. 附加結果會增加資料列, 雙方[座號]與[國文]欄位名相同, 資料會放同一個欄位, 而年度和考試日期因欄位名不同, 放不同欄位
  3. 統計
    1. 到左邊清單選[樞紐], 按右鍵/參考
    2. 按ctrl選取年度和座號欄位, 按上方 常用/分組依據, 選進階], 設定如下圖, 按確定
    3. 按總分右邊倒三角, 選遞減排序
    4. 按上方新增資料行/索引資料行旁倒三角/從1, 即可產生名次

Lab3. 查詢來源從資料夾

  1. 開新Excel
  2. 按上方資料/取得資料/從檔案/從資料夾, 選Lab3目錄, 按開啟
  3. 按合併旁的倒三角/合併與轉換資料
  4. 選左邊”分數”, 可預覽資料, 按確定, 進入PowerQuery畫面
  5. 已有新查詢, 也已自動將資料合併
  6. 按上方常用/關閉並載入

Lab4. 處理文字檔1

  1. 開新Excel, 按上方 資料/[從文字/CSV], 選擇Lab4.txt
  2. 分隔符號選[固定寬度], 輸入”5,6,12,15”, 按轉換資料, 會將每列第5碼的字放第1欄, 第6碼~11放第2欄, 第12~14碼放第3欄, 第15碼之後放第4欄
    * 每列位置頭一個字是第0碼, 次一字是第1碼, 以此類推…
  3. 按Column2欄右邊倒三角, 在搜尋輸入 “640004”, 按確定
  4. Column2欄名上按右鍵/移除
  5. Column4欄名上按右鍵/取代值, 輸入如下圖, 按確定
  6. 和第5項相同, 選Column1欄名, 按上方 轉換/取代值, 將D取代為空值, 將C取代為-號
  7. 按Ctrl, 按順序選取Column1和Column4, 按上方 轉換/合併資料行, 新資料行名稱: Amount
  8. Amount欄名上按右鍵/變更類型/小數
  9. Column3欄名按一下, 按F2, 更名: “CUR”, 按Enter, 即完成資料處理
  10. 按上方 常用/進階編輯器, 點一下內容文字, 按Ctrl+A, Ctrl+C, 按win+r, 輸入notepad, 按Enter, 按Ctrl+V把資料貼上記事本, 回到進階編輯器, 按取消
  11. 點選右邊[查詢設定]清單內的[已合併資料行], 點Amount欄名, 按F2, 更名AMT
  12. 點選右邊[查詢設定]清單內的[已變更類型2], 會出現錯誤: Expression.Error: 找不到資料表的資料行‘Amount’。
  13. 將錯誤上方公式內的Amount, 改成AMT, 即可修正
  14. 到記事本內容按Ctrl+A, Ctrl+C, 按上方 常用/進階編輯器, 點一下內容文字, 按Ctrl+V把資料貼上, 按確定, 即可恢復原來結果
  15. 按關閉並載入即完成

Lab5. 處理文字檔2

  1. 開新Excel, 按上方 資料/[從文字/CSV], 選擇Lab5.txt, 按轉換資料, 進入PowerQuery畫面
  2. 如果有的話, 指到右邊[查詢設定]清單內的[已變更類型], 按X
  3. 按上方常用/移除資料列/移除頂端資料列, 輸入”4”, 按Enter
  4. 按上方常用/移除資料列/移除頂端資料列, 輸入”2”, 按Enter
  5. 按上方檔案/選項及設定/查詢選項/全域/資料載入, 類型偵測 選[永不偵測…], 按確定
  6. 按上方常用/使用第一個資料列作為標頭, 此時所有欄位類型不會改變
  7. 按上方常用/使用第一個資料列作為標頭旁的倒三角/以標頭作為第一列
  8. 按上方檔案/選項及設定/查詢選項/全域/資料載入, 類型偵測 選[一律偵測…], 按確定, 此功能會影響效能, 如果電腦太慢, 建議選[永不偵測…]
  9. 按上方常用/使用第一個資料列作為標頭, [查詢設定]清單會再多一項[已變更類型]
  10. 按上方 常用/分割資料行/依字元數, 設定如下圖, 按確定
  11. 按ctrl點選[每單位配發金額(元) ]和[現金股利殖利率(%)]欄位, 按上方 新增資料行/標準/乘, 會產生2欄相乘的[乘法]欄位
  12. 按ctrl點選[乘法],[每單位配發金額(元) ]和[現金股利殖利率(%)]欄位, 按上方 新增資料行/統計資料/最小值, 會產生[最小值]欄位
  13. 按上方新增資料行/自訂資料行, 在右邊可用的資料行, 點2下[乘法], 則自訂資料行公式會出現”= [乘法] “, 在後面輸入”-“, 再點2下[最小值], 即” =[乘法]-[最小值]”, 按確定, 即產生自訂欄位按上方 常用/關閉並載入

Taiwan is a country. 臺灣是我的國家

標籤雲

系列文章

本頁段落