手機站首頁散文詩歌雜文隨筆日記小小說

散文網 » 科技 »學習 » Excel之PowerQuery 處理橫向課程表

Excel之PowerQuery 處理橫向課程表

2022-09-29 11:37 作者:孫興華zz 0人讀過 | 我要投稿

網友提問：

網友通過郵件把表格原始樣式和想做好的樣式發(fā)給我了，評論區(qū)可以下載

原始：

想要的樣子：

因為列特別多，所以沒辦法完全截圖給大家，需要練習的小伙伴可以評論區(qū)下載Excel工作簿練習。

先說聲抱歉，您的問題我解決不了，原因是PowerQuery有一個規(guī)則，他的列名（字段名）不能重復。當您星期一顯示1、2、3、4、5...（節(jié)課），星期二就不能再顯示1、2、3、4、5...

我可以做一個和您一樣的表，但是表頭做不了。我做成下面的樣子。綠表是我的，藍表是您的要求，我只能把您的表頭放在第一行上才能實現您的效果。

本文章的內容應用不廣泛，只寫文章不錄課了。

下面說一下操作方法：

1、導入數據到PowerQuery

這一步，切記，要從原始數據第一列第一個單元格開始按住鼠標拖動到最后一列最后一個單元格松手，然后再點【數據】~【來自工作表】，因為原表有合并單元格。

2、第一列的標題可以修改一下，因為原始單元格是如下圖所示，不改也行但是寫代碼麻煩

我們雙擊表頭第1個，把它改成教師，這樣就會自動新生成一個步驟，如下圖

3、跳過第一行，因為第一行數據沒有出現在目標表上

添加步驟，代碼如下

4、降標題，操作順序【主頁】~【將標題作為第一行】

會自動生成兩個步驟，如下圖

5、對【更改的類型1】這張表轉置，用按鈕或代碼都行

6、將轉置表Column1這列所有是"列"開頭的都替換為null

7、使用Table.Group第五參數分組（原理詳見PowerQuery107集合集課程）

8、獲取分組這張表中的三個部分

紅色框為首行，藍色框為末行，黃色框為中間

接下來分三步：

9、給中間這張表中的每個Table插入星期X

因為網友要求在星期X后面增加一列，寫上大課間

于是我在每個Table第0行插入一條記錄，記錄您可以用公式生成，但是我覺得此案例沒必要

所以直接寫的記錄，其中星期X引用每張Table中【Column1】列的第0號下標的值

10、選中數據列，【主頁】~【刪除列】~【刪除其它列】

11、展開數據列，依次點擊如下圖

它會自動生成一個步驟，并生成代碼如下

= Table.ExpandTableColumn(刪除的其他列, "數據", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})

但是這個代碼只是中間表修改后的代碼，我們還要連接上首行和末行的表，所以代碼這樣寫

= 首行 & Table.ExpandTableColumn(刪除的其他列, "數據", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}) & 末行

12、將展開的表再轉置回來

13、提升標題

【主頁】~【將第一行用作標題】，會自動生成如下兩個步驟

14、再次提升標題

【主頁】~【將第一行用作標題】，會自動再生成如下兩個步驟

15、修改每個人的Table

按Column1列進行分組，使用Table.Group第5參數，公式中x代表數據列的每張表，

將每張表按列轉成列表，遍歷每個列，將下標1連接下標2的數據，

但是最后一列是數值，會報錯，所以我們用try函數，當出現錯誤時，我們只取這個列的第0號下標的數據

16、刪除其它列

選中數據列，【主頁】~【刪除列】~【刪除其它列】

17、展開數據，步驟如下圖

結果：

18、現在數據都沒問題了，缺個表頭那行

其實我們要的就是轉置表1的第二行數據做表頭，修改一下第一個和最后一個單元格內容即可

總有網友發(fā)消息問我，步驟中的表還要繼續(xù)用行不行，那有什么不行的？咱們不是經常這么做嗎？

思考步驟：

跳過轉置表第1行：Table.Skip(轉置表1,1)

取上面結果表的第1行：Table.FirstN(Table.Skip(轉置表1,1),1)

把上面結果按列轉列表：Table.ToColumns(Table.FirstN(Table.Skip(轉置表1,1),1))?

取上面結果的列表中從第2個到倒數第2個元素：List.Range( Table.ToColumns(Table.FirstN(Table.Skip(轉置表1,1),1)) , 1 , List.Count(Table.ToColumns(Table.FirstN(Table.Skip(轉置表1,1),1)))-2 )

給這個列表最前面加上教師，最后面加上課時合計：Table.FromColumns( {{"教師"}} & List.Range( Table.ToColumns(Table.FirstN(Table.Skip(轉置表1,1),1)) , 1 , List.Count(Table.ToColumns(Table.FirstN(Table.Skip(轉置表1,1),1)))-2 ) & {{"課時合計"}}? )

現在生成了表頭這張表