Excel 数据整理工具 ——PowerQuery
大家好，我是處理數(shù)據(jù)的小爽鴨~
之前的文章，我們講過(guò)項(xiàng)目部領(lǐng)導(dǎo)老葉為了方便分析，需要將如下圖的左表轉(zhuǎn)化處理成右表。
前面我們介紹過(guò) Word 替換法，還有 Textsplit 函數(shù)的方法。詳情戳文末鏈接。
不過(guò)，相信很多小伙伴都知道，PowerQuery 也是 Excel 專門(mén)用做數(shù)據(jù)清洗的利器。
所以，今天小爽就來(lái)帶大家來(lái)看看這個(gè)案例的 PQ 解法。
適用版本：Excel2016 以上（WPS 不適用）
難度系數(shù)：???
戳戳手：該案例屬于 PQ 中等難度，需要使用到一些 M 函數(shù)，大家重在學(xué)習(xí)思路。
我們先將數(shù)據(jù)導(dǎo)入到 PQ 編輯器中。
選中數(shù)據(jù)區(qū)域，在【數(shù)據(jù)】選項(xiàng)卡下，單擊【來(lái)自表格 / 區(qū)域】，【確定】。
1、拆分到行
如果只有一列需要拆分，那好辦。
我們直接用拆分列，拆分到行。
如下圖：
單列就拆分好了。
但是，我們需要拆分到行的有兩列。怎么辦?
那，分別拆分，然后將所需列合并？
分別拆開(kāi)。
然后再合并。
可以是可以，但是如果待拆分的列有多列，還要一個(gè)個(gè)拆分，然后拼接轉(zhuǎn)表，這顯然并不是個(gè)好的選擇。
2、合并列
接下來(lái)，我們來(lái)看看另外的做法。
具體思路：
利用 Table.CombineColumns 將需要處理的列合并；
合并后的每一行，進(jìn)行循環(huán) (List.Transform) 拆分逗號(hào) Text.Split；
處理后再轉(zhuǎn)表處理 (Table.FromColumns)；
最后展開(kāi)表 (Table.ExpandTableColumn)。
看不懂，沒(méi)關(guān)系，下面有詳細(xì)步驟 ↓
具體步驟：
單擊 fx 新建步驟。
? 獲取待拆分的列名的列表 name
= List.Skip (Table.ColumnNames 源))
Table.ColumnNames (源)，是用來(lái)獲取表格標(biāo)題的函數(shù)。也就是 {"姓名","參與完結(jié)項(xiàng)目","業(yè)績(jī)?cè)u(píng)分"}。
List.Skip 表示跳過(guò)幾個(gè)，第二參數(shù)不寫(xiě)默認(rèn)為 1，所以跳過(guò) 1 個(gè)，也就是 {"參與完結(jié)項(xiàng)目","業(yè)績(jī)?cè)u(píng)分"}。
這樣的好處是，后面新增列，也能夠獲取最新待拆分的列名形成的 list。
將步驟命名為 name。
? 合并待拆的列
單擊 fx 新建步驟。
公式欄中輸入：
= Table.CombineColumns 源,name,each _,"a")
=Table.CombineColumns (表，需要合并的列名形成的列表，合并的方式，新列名)
案例中我們需要合并的列名，也就是第一步的 name。
= Table.CombineColumns 源,//表 name, //需要合并的列名形成的 list each _, //合并的處理，先不做處理 "a" //合并后的列名稱
? 循環(huán)合并處理，將文本按照逗號(hào)拆分
修改 Table.CombineColumns 第三參數(shù)合并處理
= Table.CombineColumns 源, name, each List.Transform (_,(x)=Text.Split (x,",")), "a")
如下圖所示。
這里主要是針對(duì)合并處理 的每一行，循環(huán)進(jìn)行拆分。
List.Transform ( _, //合并列每一行形成的 list。 (x)=Text.Split (x,",") //將每個(gè)數(shù)據(jù)按照逗號(hào)進(jìn)行拆分 )
? 拆分后的數(shù)據(jù)，按照列進(jìn)行轉(zhuǎn)表
將 Table.CombineColumns 第三參數(shù)拆分后的列表按照列轉(zhuǎn)表。
= Table.CombineColumns 源,name, each Table.FromColumns List.Transform (_,(x)=Text.Split (x,",")), name), "a")
將拆分后的 lists，使用 Table.FromColumns 轉(zhuǎn)換成表。
=Table.FromColums (lists, 對(duì)應(yīng)標(biāo)題)
按列轉(zhuǎn)表后對(duì)應(yīng)的標(biāo)題，就是 name。
這一步命名為合并。
? 展開(kāi)表格
最后一步，把表擴(kuò)展開(kāi)就可以。
單擊展開(kāi)按鈕，取消勾選【使用原始列名作為前綴】，單擊【確定】按鈕。
直接展開(kāi)，參數(shù)是寫(xiě)死的。展開(kāi)的列名其實(shí)也就是 name。
所以，我們將公式改成如下圖所示。這一步可以命名為結(jié)果。
= Table.ExpandTableColumn ( 合并 , "a",name)
最終的 M 函數(shù)：
let
源 = Excel.CurrentWorkbook (){[Name="表 1"]}[Content],
name = List.Skip (Table.ColumnNames (源)),
合并 = Table.CombineColumns (
源,
name,
each
Table.FromColumns (
List.Transform (_,(x)=>Text.Split (x,",")), name),
"a"),
結(jié)果 = Table.ExpandTableColumn (合并, "a",name)
in
結(jié)果3、最后的話
本文講解的是 PQ 解法，該方法涉及多個(gè)函數(shù)。
▋STEP01 獲取待拆分的列名的列表
? Table.ColumnNames 可以獲取表格的標(biāo)題
? List.Skip 可以跳過(guò)指定個(gè)數(shù)
= List.Skip (Table.ColumnNames(源)
▋STEP02 合并指定列，先不做處理
Table.CombineColumns (表，name，each _，新列名)
▋STEP03 Table.CombineColumns 的第三參數(shù)處理
? 循環(huán)拆分
List.Transform+Text.Split
? 按列轉(zhuǎn)表
Table.FromColumns
▋STEP04 利用 Table.EnpandTableColumn 展開(kāi)合并的列
第二參數(shù)拆分的列名，不要寫(xiě)死。
關(guān)于該案例的其他做法，請(qǐng)戳：文章。
將數(shù)據(jù)表轉(zhuǎn)化后，老葉就可以通過(guò)數(shù)據(jù)透視表進(jìn)行進(jìn)一步的分析啦。
如下圖：
比如上個(gè)季度，每個(gè)項(xiàng)目成員參與的項(xiàng)目數(shù)，總分是多少；每個(gè)項(xiàng)目有多少人參加，參與成員有誰(shuí)。
也可以對(duì)數(shù)據(jù)進(jìn)行可視化。
從整個(gè)案例中，我們可以看到整個(gè)數(shù)據(jù)的過(guò)程。
數(shù)據(jù)錄入-數(shù)據(jù)清洗-數(shù)據(jù)分析-數(shù)據(jù)可視化-[數(shù)據(jù)匯報(bào)]
不同過(guò)程，所需要掌握的 Excel 知識(shí)點(diǎn)有所不同。
? 數(shù)據(jù)錄入
數(shù)據(jù)驗(yàn)證 / 單元格格式/...
? 數(shù)據(jù)清洗
函數(shù) / VBA / PowerQuery
? 數(shù)據(jù)分析
函數(shù) / 數(shù)據(jù)透視表 / PowerPivot
? 數(shù)據(jù)可視化
圖表 / 表格美化等
本文來(lái)自微信公眾號(hào)：秋葉 Excel （ID：excel100），作者：小爽
廣告聲明：文內(nèi)含有的對(duì)外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，所有文章均包含本聲明。
總結(jié)
以上是生活随笔為你收集整理的Excel 数据整理工具 ——PowerQuery的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 部分需联网操作，三星 Galaxy S2
- 下一篇： 索尼确认出席 CES 2024，和本田合