最近,，我一直在研究這種常見(jiàn)的申請(qǐng)表,、登記表類(lèi)的表格轉(zhuǎn)換問(wèn)題，通常來(lái)說(shuō),，這種登記表的內(nèi)容排列上是沒(méi)有嚴(yán)格的規(guī)律的,，但是，每一項(xiàng)內(nèi)容的位置是固定的,，同時(shí),，一般這種表都是需要批量處理的，不僅可能是多個(gè)表,，還可能是多個(gè)工作簿,，這時(shí)，通過(guò)函數(shù)公式的方法基本不可能,。
最早的時(shí)候,，我考慮用VBA，于是寫(xiě)了專(zhuān)門(mén)的可配置化的導(dǎo)入程序,，也就是說(shuō),，只要用戶填寫(xiě)了哪些單元格導(dǎo)入到匯總表的哪些列，然后就可以批量地導(dǎo)入多個(gè)文件,，現(xiàn)在我考慮使用Excel2016的新功能（2010或2013可以到微軟官方下載插件）Power Query來(lái)直接實(shí)現(xiàn),。初步成果如下：
大海：以前得用VBA的這個(gè)事情，現(xiàn)在其實(shí)可以用Power Query來(lái)實(shí)現(xiàn)了,。
小勤：?。∧翘昧?。怎么做呢,？
大海：其實(shí)思路差不多，就是先找到源數(shù)據(jù)表（格式表）需要導(dǎo)入的數(shù)據(jù)與目標(biāo)表（規(guī)范明細(xì)表）的關(guān)系,，然后把源表的數(shù)據(jù)放到目標(biāo)表里,。咱們先從這個(gè)簡(jiǎn)單的例子開(kāi)始，今天先實(shí)現(xiàn)一個(gè)表格的轉(zhuǎn)換,，后面咱們?cè)谥饾u擴(kuò)展到多表的,、映射關(guān)系可配置的方式。
Step-01：從工作簿獲取數(shù)據(jù)到PQ
Step-02：為避免數(shù)據(jù)類(lèi)型轉(zhuǎn)換錯(cuò)誤,，刪掉PQ自動(dòng)添加的“更改的類(lèi)型”步驟
結(jié)果如下：
顯然,，其中有很多合并單元格的內(nèi)容被識(shí)別成了null,，這些我們都可以不管它，只要知道需要提取的信息固定在什么位置就好了,，比如姓名“大?！痹凇癈olumn2”的第“2”行（索引為1），所以,，參考《理解PQ里的數(shù)據(jù)結(jié)構(gòu)（二,、行列引用）》的方法，只要讀取這個(gè)表里的{1}[Column2]就可以得到姓名……
匯總對(duì)應(yīng)關(guān)系如下表所示：
Step-03：修改生成的代碼以完成轉(zhuǎn)換
【以下內(nèi)容需要一點(diǎn)兒Power Query的前期基礎(chǔ)知識(shí),，相關(guān)訓(xùn)練材料可到網(wǎng)盤(pán)下載：】
原來(lái)的代碼是這樣的（這里“源”行代碼可能與您實(shí)際操作內(nèi)容不同,，因?yàn)橐呀?jīng)修改了工作簿動(dòng)態(tài)接入路徑，與本文主體操作內(nèi)容無(wú)關(guān),，若希望了解該內(nèi)容,，請(qǐng)參考《結(jié)合CELL函數(shù)實(shí)現(xiàn)數(shù)據(jù)源的動(dòng)態(tài)化》）：
修改后代碼如下：
其中主要修改內(nèi)容如下：
1、改個(gè)名稱(chēng)：原代碼中生成的名稱(chēng)太長(zhǎng),，為后面寫(xiě)起來(lái)方便,，將“VIP登記表_Sheet”修改為“s”（這種修改經(jīng)常用）；
2,、構(gòu)造新的表（table）：增加圖中藍(lán)色背景代碼
d = #table(
{'姓名','年齡','性別','公眾號(hào)','興趣','電話','郵箱'},
{{s[Column2]{1},s[Column4]{1},s[Column6]{1},s[Column2]{2},
s[Column4]{2},s[Column6]{2},s[Column2]{3}}}
)
這句代碼的含義就是直接用關(guān)鍵字#table構(gòu)造表,，語(yǔ)法很簡(jiǎn)單，就是先給標(biāo)題名稱(chēng)列表,，然后再給各行數(shù)據(jù)列表組成的一個(gè)列表（列表嵌套）,，具體語(yǔ)法如下：
#table({標(biāo)題},
{{第1行數(shù)據(jù)},
{第2行數(shù)據(jù)},
…})
再簡(jiǎn)化一點(diǎn)兒用具體數(shù)據(jù)舉個(gè)小栗子：
#table( {'姓名','年齡'},
{{'大海','100'},
{'小勤','18'}} )
就會(huì)得到以下內(nèi)容的表：
小勤：理解了，這樣標(biāo)題和內(nèi)容都明顯意義對(duì)應(yīng)的啊,?？雌饋?lái)真是不難嘢，比寫(xiě)VBA好多了,。呵呵
大海：嗯。當(dāng)然啦,，如果用VBA做的話,，可以做得更加靈活，只是學(xué)VBA所需要投入的精力要更加大而已,。
PQ-綜合實(shí)戰(zhàn)：格式化表單轉(zhuǎn)數(shù)據(jù)明細(xì)之2：多表批量轉(zhuǎn)換匯總
原創(chuàng) 大海 Excel到PowerBI
大海：有了《自定義函數(shù)》的基礎(chǔ),，現(xiàn)在可以開(kāi)始多個(gè)格式化表單數(shù)據(jù)的轉(zhuǎn)換匯總了。
小勤：嗯,。我剛試了一下,，好簡(jiǎn)單，原來(lái)那個(gè)《單個(gè)格式表轉(zhuǎn)換》里讀數(shù)據(jù)的代碼是這樣的：
只要加上自定義函數(shù)名和將s用作參數(shù)就好了,，你看：
大海：嗯,，不錯(cuò),。
小勤：不過(guò)原來(lái)那個(gè)操作是針對(duì)單表的，所以前面的“導(dǎo)航”步驟直接進(jìn)到了具體的表,，所以刪了那些步驟重新做了,。
Step-01：在步驟中刪除“導(dǎo)航”以后的所有步驟
Step-02：篩選需要轉(zhuǎn)換的格式化表格
Step-03：在高級(jí)編輯器里把剛才改好的自定義函數(shù)放到let后面:
trans=(s)=>
#table(
{'姓名','年齡','性別','公眾號(hào)','興趣','電話','郵箱'},
{{s[Column2]{1},s[Column4]{1},s[Column6]{1},s[Column2]{2},
s[Column4]{2},s[Column6]{2},s[Column2]{3}}}
),
Step-04：添加自定義列，用自定義函數(shù)實(shí)現(xiàn)表格的轉(zhuǎn)換
Step-05：展開(kāi)自定義列
Step-06：刪掉不必要的列
小勤：搞定啦,。哈哈,。
大海：厲害！
小勤：不過(guò)我在想怎么樣實(shí)現(xiàn)配置性的操作方法,，就是想導(dǎo)入哪些數(shù)據(jù),，用戶就填一個(gè)配置表，就像你在《單個(gè)格式表轉(zhuǎn)換》里提到的用VBA做的那個(gè)類(lèi)似效果,。
大海：嗯,。那個(gè)稍微復(fù)雜一點(diǎn)點(diǎn)。下次咱們一起來(lái)看看,。
小勤：好的,。
總的來(lái)說(shuō)，通過(guò)Power Query的方法可以很好的完成相應(yīng)的數(shù)據(jù)轉(zhuǎn)換和提取,，而只需要學(xué)習(xí)一些Power Query的基礎(chǔ)知識(shí),，相對(duì)于VBA的整體代碼和學(xué)習(xí)周期來(lái)說(shuō)，更加適合廣大Excel用戶應(yīng)用,。
歡迎關(guān)注【Excel到PowerBI】
我是大海,，微軟認(rèn)證Excel專(zhuān)家，企業(yè)簽約Power BI顧問(wèn)
讓我們一起學(xué)習(xí),，共同進(jìn)步,！
