幾年前驻售，我就遇到過(guò)這種非標(biāo)準(zhǔn)Excel文件露久；沒(méi)想到幾年過(guò)去了，我又遇到了這種奇葩文件欺栗。

來(lái)看下奇葩毫痕。
乍一看征峦，除了文件名后綴是xls，沒(méi)有什么特別消请。

看上去一切正常.png

打開(kāi)試試看：

擴(kuò)展名不匹配.png

問(wèn)卷.png

用powerquery處理Excel文件的方法來(lái)處理試試看：

不是預(yù)期的外部格式.png

先看看我們要實(shí)現(xiàn)的目標(biāo)

目標(biāo)

我們的目標(biāo)是實(shí)現(xiàn)上圖中的統(tǒng)計(jì)效果。
可能有好奇寶寶要問(wèn)了需频，既然有系統(tǒng)了丁眼，干嘛還要用powerquery來(lái)處理？
原因比較簡(jiǎn)單：發(fā)布問(wèn)卷的哥們兒腦袋被門(mén)夾了昭殉，發(fā)布了四個(gè)一模一樣的問(wèn)卷户盯，于是，他們系統(tǒng)幾千幾萬(wàn)人的答題結(jié)果就分布在這四個(gè)問(wèn)卷里饲化，現(xiàn)在要把四個(gè)問(wèn)卷結(jié)果匯總成一個(gè)……

那復(fù)制粘貼不行么莽鸭？
可以的……然而有些同樣不動(dòng)腦子的答題人把四個(gè)問(wèn)卷都做了，或者做了其中的若干個(gè)吃靠，同樣的題目答案還不一樣硫眨。此外，表格中除了有答題人答題的記錄巢块，還有沒(méi)有答題的人的記錄……

所以礁阁，必須另尋他法。我對(duì)powerquery最熟族奢，當(dāng)然是用powerquery姥闭；我估計(jì)用python也可以，但是我python不熟越走，估計(jì)要花很多時(shí)間才能完成棚品。

我用powerquery花了一個(gè)小時(shí)完成從數(shù)據(jù)處理、清洗到最后出結(jié)果廊敌。中間還有其他事情打斷铜跑，所以自認(rèn)為速度還是可以了。

第一步：解決奇葩Excel導(dǎo)入到Powerquery的問(wèn)題

我很早之前用的是打開(kāi)另存大法：將這些奇葩Excel文件用網(wǎng)上找的vba代碼來(lái)另存為xlsx格式骡澈，這樣處理起來(lái)就比較方便了锅纺。

這次我準(zhǔn)備又另存為xlsx格式時(shí)，無(wú)意中發(fā)現(xiàn)下面這個(gè)界面：

windows在另存時(shí)提示是html文件格式

既然是html格式肋殴，那么就可以按照powerquery處理html格式文件的方式來(lái)處理囤锉。于是坦弟，嘗試了以下代碼：

  = Table.AddColumn(源, "自定義", each Web.Page([Content]))

結(jié)果沒(méi)出錯(cuò)：

webpage.png

試試看展開(kāi)，尋找有沒(méi)有需要的數(shù)據(jù)：

展開(kāi)表格.png

第二步：剔除不必要的部分修陡，保留需要的Table沧侥。

從上一步我們可以判斷，每個(gè)文件的第一個(gè)表格是答題數(shù)據(jù)魄鸦，第二個(gè)表格是沒(méi)答題的人的數(shù)據(jù)宴杀，第三個(gè)Service跟我們的任務(wù)無(wú)關(guān)，所以我們只需要保留每個(gè)文件的第一個(gè)表格就好了拾因。
添加一個(gè)索引列旺罢，然后用3來(lái)除索引，余數(shù)為0的就是我們需要的表格绢记。

刪除的其他列 = Table.SelectColumns(#"展開(kāi)的“自定義”",{"Name", "Caption", "Source", "Data"}),
已添加索引 = Table.AddIndexColumn(刪除的其他列, "索引", 0, 1),
已添加自定義1 = Table.AddColumn(已添加索引, "取模", each if Number.Mod([索引],3)=0 then true  else false),
篩選的行 = Table.SelectRows(已添加自定義1, each ([取模] = true))

獲得的結(jié)果如下：

獲得的數(shù)據(jù).png

第三步：繼續(xù)進(jìn)行數(shù)據(jù)清洗與合并

從這一步開(kāi)始扁达，對(duì)數(shù)據(jù)進(jìn)行清洗有兩種辦法：
一種是基于上一步產(chǎn)生的表格中的任意一行包含的數(shù)據(jù)，進(jìn)行單獨(dú)處理蠢熄，然后建立一個(gè)函數(shù)跪解，將其應(yīng)用到所有行。
另外一種是直接合并所有數(shù)據(jù)签孔，再進(jìn)行清洗叉讥。
兩種方法各有利弊：函數(shù)法比較優(yōu)雅，由于是單獨(dú)處理一份數(shù)據(jù)饥追，所以運(yùn)行速度快一些图仓，也不容易遺漏清洗步驟；直接合并后再清洗是對(duì)所有數(shù)據(jù)進(jìn)行操作但绕，運(yùn)行速度會(huì)慢一些救崔，還可能會(huì)漏掉清洗步驟。

我偷懶采取第二種辦法（因?yàn)閷?xiě)函數(shù)雖然運(yùn)行速度快捏顺，但寫(xiě)函數(shù)本身需要時(shí)間呀）帚豪。
點(diǎn)擊【Data】列旁邊的展開(kāi)符號(hào)，展開(kāi)所有數(shù)據(jù)并進(jìn)行合并草丧。

展開(kāi)的“Data”= Table.ExpandTableColumn(篩選的行, "Data", Table.ColumnNames(篩選的行[Data]{0}))狸臣，

由于默認(rèn)展開(kāi)有52列，且PowerQuery會(huì)自動(dòng)把這52列的列名都全部列出昌执，這樣會(huì)顯得很長(zhǎng)且有硬編碼的弊端烛亦，所以我把Table.ExpandTableColumn()函數(shù)的第三個(gè)參數(shù)單獨(dú)寫(xiě)了下诈泼，也就是用【Data】列的任意一個(gè)表格的列名來(lái)作為這第三個(gè)參數(shù)。

然后刪除第一行的題目信息（我們待會(huì)兒會(huì)用別的方式單獨(dú)處理）煤禽，再提升標(biāo)題铐达。

刪除的頂端行 = Table.Skip(#"展開(kāi)的“Data”",1),
提升的標(biāo)題 = Table.PromoteHeaders(刪除的頂端行, [PromoteAllScalars=true]),

由于四個(gè)table都有剛才我們處理掉的兩行，所以我們需要從其他表格里也剔除這兩行檬果，辦法就是通過(guò)篩選瓮孙。
這里篩選時(shí)有個(gè)小技巧：由于數(shù)據(jù)很多，所以我們找一個(gè)值很單一的列來(lái)剔除不需要的行會(huì)更容易选脊。比如我選擇了從一個(gè)題目的列下面篩選剔除杭抠。

篩選的行1 = Table.SelectRows(更改的類型, each ([#"A、25周歲以下（含25周歲）"] <> "1恳啥、您的年齡段是偏灿？(單選)" and [#"A、25周歲以下（含25周歲）"] <> "A钝的、25周歲以下（含25周歲）")),

但是上一步并沒(méi)剔除完全部不需要的數(shù)據(jù)翁垂，還有一行匯總的也要剔除。通過(guò)觀察可以發(fā)現(xiàn)硝桩，在賬號(hào)列都是手機(jī)號(hào)沿猜，而匯總行對(duì)應(yīng)的值是一個(gè)文本【賬號(hào)】。這里我直接把賬號(hào)列變?yōu)檎麛?shù)碗脊，那么出現(xiàn)文本的行會(huì)報(bào)錯(cuò)邢疙，直接刪除錯(cuò)誤，就剔除了匯總行了望薄。

更改的類型1 = Table.TransformColumnTypes(篩選的行1,{{"選項(xiàng)_2", Int64.Type}}),
刪除的錯(cuò)誤 = Table.RemoveRowsWithErrors(更改的類型1, {"選項(xiàng)_2"}),
刪除的列 = Table.RemoveColumns(刪除的錯(cuò)誤,{"選項(xiàng)", "選項(xiàng)_2", "選項(xiàng)_3", "選項(xiàng)_4"})

第四步：解決一個(gè)人答了多份問(wèn)卷的問(wèn)題

由于四個(gè)Excel文件導(dǎo)出時(shí)疟游，是按時(shí)間現(xiàn)后順序?qū)С龅模乙话愦痤}人也不會(huì)先回答后面的問(wèn)卷再回頭去填寫(xiě)前面的問(wèn)卷痕支。所以颁虐，凡是多次答題的人，我們?nèi)∑渥罱淮未痤}的結(jié)果（也就是文件序號(hào)最大的那個(gè)excel文件中的結(jié)果）卧须。

由于要用到excel文件序號(hào)另绩，所以我們先獲取文件名中的文件序號(hào)。并將結(jié)果數(shù)據(jù)格式改為整數(shù)花嘶，便于后面比大小以決定針對(duì)多次答題的人取哪個(gè)結(jié)果笋籽。

= Table.TransformColumns(重命名的列,{"name",each Text.BetweenDelimiters(_,"(",")")})，
= Table.TransformColumnTypes(自定義1,{{"name", Int64.Type}}),

這時(shí)針對(duì)【賬號(hào)】列進(jìn)行分組椭员，不選姓名列是因?yàn)榭赡艽嬖谕铡?/p>

= Table.Group(刪除的列1, {"賬號(hào)"}, {{"計(jì)數(shù)", each Table.RowCount(_), type number}, {"all", each _, type table }}),

獲得的結(jié)果如下：

groupby.png

可以發(fā)現(xiàn)车海，確實(shí)有不少人答了兩次問(wèn)卷。

前面已經(jīng)確認(rèn)隘击，對(duì)于多次答題的人侍芝，我們只需要獲取ta答過(guò)題的excel文件序號(hào)中最大的那個(gè)就可以了研铆。這個(gè)文件序號(hào)隱藏在上表中的【all】列，所以州叠，我們要選取【all】列中每一個(gè)表格里name值最大的那一行棵红。然后再展開(kāi)，代碼如下：

= Table.TransformColumns(分組的行,{"all",each Table.Max(_,"name")}),
= Table.ExpandRecordColumn(刪除的其他列3, "all",Record.FieldNames(刪除的其他列3[all]{0})),

對(duì)結(jié)果進(jìn)行簡(jiǎn)單清理咧栗。

第五步：對(duì)問(wèn)卷進(jìn)行處理

上一步我們得到了一個(gè)table逆甜，如下所示：

待清理的問(wèn)卷.png

現(xiàn)在我們要從這個(gè)table得到選擇題的結(jié)果（因?yàn)閱?wèn)答題的結(jié)果我們需要單獨(dú)處理）。由于我們不需要對(duì)單個(gè)答題人進(jìn)行處理致板，所以我們只需要選中所有選擇題的列就行了交煞，刪除【姓名】、【賬號(hào)】等不必要的列可岂。結(jié)果如下：

問(wèn)題及回答.png

于是，關(guān)鍵在于侈沪，我們?nèi)绾潍@得所有人答題選項(xiàng)的分組揭璃。
我是這樣做的：
首先把列標(biāo)題降級(jí)：

= Table.DemoteHeaders(刪除的其他列4)

這樣可以發(fā)現(xiàn)表格其實(shí)被分為兩部分：第一行是題目選項(xiàng)，剩下的是選項(xiàng)對(duì)應(yīng)的答題結(jié)果亭罪。因此瘦馍，我們只要得到兩個(gè)表格：一個(gè)是只有一行內(nèi)容，但是列名為Column1应役，Column2……Column44情组；另一個(gè)表格就是所有人答題的結(jié)果，列名也是Column1箩祥，Column2……Column44院崇。這樣，這兩個(gè)表格就可以通過(guò)列名進(jìn)行關(guān)聯(lián)了袍祖。

出于便利考慮底瓣，獲取有44列、1行的題目選項(xiàng)表格蕉陋，我們手工在excel表格中構(gòu)造濒持，然后添加到數(shù)據(jù)模型就可以了键耕。

選項(xiàng)答案的表格，通過(guò)刪除列標(biāo)題降級(jí)后的第一行內(nèi)容得到柑营。然后unpivot所有列：

= Table.UnpivotOtherColumns(刪除的頂端行1, {}, "屬性", "值"),
= Table.Group(逆透視的列, {"屬性"}, {{"計(jì)數(shù)", each Table.RowCount(_), type number}}),

得到的結(jié)果如下：

unpivot.png

由于每一列對(duì)應(yīng)一個(gè)選項(xiàng)屈雄，所以我們不用擔(dān)心null值會(huì)導(dǎo)致錯(cuò)行。
通過(guò)在powerpivot中官套，將此表格和題目選項(xiàng)表格建立關(guān)系酒奶，就得到了題目選項(xiàng)和答案的關(guān)系。

接下來(lái)奶赔，我們要將題目選項(xiàng)和題目對(duì)應(yīng)起來(lái)惋嚎，以便題目和選項(xiàng)、選項(xiàng)的答案對(duì)應(yīng)起來(lái)站刑。這一步還是在excel中表格中進(jìn)行構(gòu)造即可另伍。然后加載到數(shù)據(jù)模型，建立關(guān)系绞旅。步驟略摆尝。

最后我們從中間步驟提取下問(wèn)答題的結(jié)果即可。也就是在第一次用groupby()函數(shù)的上一步提取問(wèn)答題的結(jié)果因悲，將其單獨(dú)加載到excel表格（不是數(shù)據(jù)模型堕汞，因?yàn)闊o(wú)需對(duì)它進(jìn)行進(jìn)一步處理）。

第六步：獲取最后結(jié)果

數(shù)據(jù)模型建好之后晃琳，獲取最終結(jié)果就非常簡(jiǎn)單了：基于數(shù)據(jù)模型添加數(shù)據(jù)透視表讯检，將題目、選項(xiàng)及結(jié)果計(jì)數(shù)拉入數(shù)據(jù)透視表對(duì)應(yīng)位置卫旱，將計(jì)數(shù)顯示為【父行匯總的百分比】人灼，再用條件格式添加數(shù)據(jù)條顯示方式即可。

最后的結(jié)果.png

注意顾翼，由于我沒(méi)對(duì)題目排序挡毅，所以第一題過(guò)后就是第十題了。這個(gè)問(wèn)題很好處理：笨一點(diǎn)的辦法是復(fù)制數(shù)據(jù)透視表粘貼為表格（而不是保留原格式）暴构，再把第十題剪切粘貼到正確的位置跪呈。還可以在數(shù)據(jù)模型中給題目建立序號(hào)，也可以實(shí)現(xiàn)取逾。

結(jié)語(yǔ)

當(dāng)時(shí)處理這個(gè)任務(wù)時(shí)耗绿，耗時(shí)一個(gè)小時(shí)左右，但是寫(xiě)這個(gè)帖子砾隅，斷斷續(xù)續(xù)花了差不多三個(gè)小時(shí)……我也是醉了误阻。