首先說明戳玫，在人人都有智能手機(jī)府阀、還有騰訊問卷這樣好用的免費(fèi)工具的時(shí)代寞蚌，還使用傳統(tǒng)的word+電子郵件的方式來做問卷調(diào)研，是一件比較讓人遺憾的事情截珍。
遺憾的是，我最近就遇到這么一個(gè)例子：要處理522份word版的調(diào)研問卷诞丽。
然而不遺憾的是，也收獲了一次難得的經(jīng)歷，是為記。
一靶瘸、為什么不直接用powerquery讀word表格润匙，而用vba芋簿？
原因在于word問卷里，個(gè)人信息是一個(gè)非常復(fù)雜的表格：
而調(diào)研題目有單選、多選叨叙、量表和問答題等純文字格式。如果是純文本或純表格爽醋，都好辦，兩個(gè)混雜在一起送讲，我就沒轍绒瘦。
二该酗、思路
盡管答卷很復(fù)雜， 但是所有問卷的結(jié)構(gòu)都是相同，所以是有規(guī)律的辱匿，有規(guī)律就好辦昨忆。
基本思路是用vba把所有word合并成一個(gè)文檔繁疤，然后把該文檔內(nèi)容復(fù)制到excel鳖昌，再用power query來把合并起來的問卷再單獨(dú)分割成一份一份的進(jìn)行處理。
三、合并所有word文件
我自己不怎么懂vba吱七，直接貼網(wǎng)上找的合并同一個(gè)文件夾下的vba代碼：
Sub MergeDocs()
Dim rng As Range
Dim MainDoc As Document
Dim strFile As String, strFolder As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Pick folder"
.AllowMultiSelect = False
If .Show Then
strFolder = .SelectedItems(1) & Application.PathSeparator
Else
Exit Sub
End If
End With
Set MainDoc = Documents.Add
strFile = Dir$(strFolder & "*.doc") ' can change to .docx
Do Until strFile = ""
Set rng = MainDoc.Range
rng.Collapse wdCollapseEnd
rng.InsertFile strFolder & strFile
strFile = Dir$()
Loop
MsgBox ("Files are merged")
lbl_Exit:
Exit Sub
End Sub
由于發(fā)來的word是按照單位文件夾存放的，因此需要把所有文件夾下的word文件弄到一個(gè)單獨(dú)的文件夾下微峰。可以用windows資源管理器搜索.doc后綴的文件彼城，復(fù)制粘貼到新文件夾。考慮到windows自帶搜索很慢泼返，我直接用everything搜索工具來把所有問卷放到一個(gè)文件夾下。
有522個(gè)文件要合并，而且這個(gè)vba其實(shí)是很簡(jiǎn)單的代碼蝇闭，沒有狀態(tài)信息呻率，所以運(yùn)行vba宏之后，word就像程序崩潰的樣子呻引，界面一片白礼仗。我等了四十多分鐘，還沒見結(jié)果逻悠，真以為word死了元践，強(qiáng)行結(jié)束后發(fā)現(xiàn)，已經(jīng)合并了四百多個(gè)了蹂风。郁悶得要死卢厂。只好分兩次合并，兩次粘貼惠啄。
每個(gè)單獨(dú)的問卷有8頁(yè)慎恒，522個(gè)文檔就有四千多頁(yè)，盡管我16G內(nèi)存撵渡，打開之后分頁(yè)還是要分若干分鐘融柬，復(fù)制一下也要很久才反應(yīng)過來，粘貼到excel里也要若干分鐘趋距。雖然我沒細(xì)算時(shí)間粒氧，但從合并開始到最后把所有問卷內(nèi)容粘貼到excel里邊，耗去的時(shí)間至少有兩三個(gè)小時(shí)节腐。算是最耗時(shí)的部份之一外盯。
弄到excel里就好辦了，直接用powerquery連接翼雀。
四饱苟、powerquery處理過程
用word打開這個(gè)4000多頁(yè)的文檔，復(fù)制狼渊、粘貼箱熬，都巨慢，但是用powerquery連接后處理起來卻輕松異常，不得不佩服微軟城须。
言歸正傳蚤认，由于我不會(huì)貼出問卷內(nèi)容，代碼其實(shí)也沒啥用處糕伐，而且由于其中有題目信息砰琢，所以更不能貼出來以免引起麻煩。只說大體思路赤炒。
把所有問卷加載到powerquery之后的第一步氯析，就是添加索引列，正是靠索引才能把問卷再一個(gè)一個(gè)還原莺褒。
然后就是單獨(dú)處理個(gè)人信息部分掩缓，再單獨(dú)處理常規(guī)選擇題，再單獨(dú)處理量表題遵岩，再單獨(dú)處理問答題你辣。在整個(gè)處理過程中，不能將答題人信息搞丟尘执。
所以我把每個(gè)人的問卷處理成一條記錄舍哄，也就是生成了一個(gè)超級(jí)寬的表。具體用到的技術(shù)就是查找包含“個(gè)人信息”誊锭、“調(diào)查問題”和“問答題”所在行的索引號(hào)表悬，因?yàn)槊糠輪柧砭褪怯蛇@兩部分構(gòu)成的。如果查不到這兩個(gè)關(guān)鍵字丧靡，則返回null蟆沫。下一步要利用向下填充的方式將查出來的索引號(hào)覆蓋掉null值。這樣每一部分都有一個(gè)相同的索引號(hào)了温治，這時(shí)這個(gè)索引號(hào)就變成問卷ID了饭庞。
接著用分組的方式，按照上面找出來的索引號(hào)分組熬荆，這樣所有問卷都被還原成了包含三個(gè)子表的記錄舟山。
接下來分別對(duì)問卷進(jìn)行三個(gè)層面的處理：一是問題層面的處理，二是問卷層面的處理卤恳，三是把問卷按照題目來分組進(jìn)行處理累盗。基本思路是建立函數(shù)突琳。
首先是個(gè)人信息的處理若债。建立函數(shù)的方式其實(shí)就是展開任意一份問卷的個(gè)人信息子表格，對(duì)其進(jìn)行處理本今。最關(guān)鍵的一步是用轉(zhuǎn)置（transpose）進(jìn)行行列顛倒拆座，讓個(gè)人信息變成一行記錄。此外冠息，由于個(gè)人信息表格其實(shí)是六個(gè)列將字段和字段值分成了兩組挪凑，所以要把這兩組記錄用和并列的方式合成一組之后再轉(zhuǎn)置。然后把第一步輸入的表格用x代替逛艰，構(gòu)造函數(shù)躏碳。
接下來處理調(diào)查問題。展開任意一個(gè)問卷散怖，對(duì)調(diào)查問題的表格實(shí)行轉(zhuǎn)置操作菇绵，暫時(shí)不用對(duì)題目進(jìn)行細(xì)操作，因?yàn)槲覀兊哪康闹皇且獦?gòu)造一個(gè)橫排的問卷镇眷。
函數(shù)構(gòu)造好之后咬最，代入每一份問卷。然后再把每一份問卷的三個(gè)子表用Record.Combine函數(shù)來構(gòu)造新表欠动，使三個(gè)子表能形成一條記錄永乌，并且這條記錄包含個(gè)人信息、選擇題和問答題的題目具伍、選項(xiàng)及答案翅雏。這樣所有答題結(jié)果都變成了一行一行的記錄。
接下來人芽，選中所有題目望几，然后選擇“逆透視其他列”，這樣就將每道題的答題人信息和對(duì)應(yīng)的答案形成了一一對(duì)應(yīng)的關(guān)系萤厅。然后按照題目分組橄抹，這樣就把所有題目的答題人信息和答題結(jié)果封裝起來了。
這時(shí)就可以對(duì)題目和選項(xiàng)及答案進(jìn)行精細(xì)操作：關(guān)鍵點(diǎn)也在于要把豎直排列的題目和答案變成橫排祈坠『δ耄考慮到題目都超級(jí)長(zhǎng)，我就直接用第1題赦拘、第2題來代替了慌随。選項(xiàng)用新增列的方式實(shí)現(xiàn)，列名用字母表示選項(xiàng)躺同。這里需要注意的是阁猜，選項(xiàng)最多的那道題有多少個(gè)選項(xiàng)，就要添加多少列蹋艺。然后針對(duì)新添加的選項(xiàng)列剃袍，對(duì)答案進(jìn)行檢查：如果答案包含對(duì)應(yīng)的列名字母，就記錄為“Y”或"是"或其他符號(hào)捎谨，能標(biāo)記每道題的答案對(duì)應(yīng)了哪些列的列名字母就ok民效，這樣答案就被轉(zhuǎn)化成列了憔维。處理完畢之后依樣畫葫蘆把第一步引入的題目表格用x代替，構(gòu)造函數(shù)畏邢。這時(shí)暫時(shí)不用考慮量表題业扒。
用上一步構(gòu)造的函數(shù)，對(duì)所有題目進(jìn)行處理舒萎。因?yàn)檎{(diào)查報(bào)告要針對(duì)每一題進(jìn)行統(tǒng)計(jì)程储、繪制圖表，所以臂寝，必須將所有題目單獨(dú)拆分成數(shù)據(jù)模型章鲤。這時(shí)就有兩個(gè)選擇：一是在一個(gè)excel文件里構(gòu)建所有題目的模型；二是一個(gè)題目就用一個(gè)excel文件咆贬。我選擇了后一種方式败徊。原因在于，數(shù)據(jù)模型建立好之后掏缎，我要統(tǒng)計(jì)每個(gè)選項(xiàng)的選擇人數(shù)集嵌，就得為每個(gè)選項(xiàng)建一個(gè)度量值。題目中選項(xiàng)最多的有12個(gè)御毅。相同的操作要在每一個(gè)模型上進(jìn)行根欧，非常繁雜。而采用第二種方式的好處是端蛆，我只需要改變每一個(gè)excel文件里powerquery生成的查詢中的題目篩選凤粗，就能快速?gòu)牡谝活}切換到第二題，這樣大大節(jié)省了步驟今豆。
不過方法二要求對(duì)單獨(dú)的數(shù)據(jù)模型考慮周密嫌拣，否則后期修改就又是體力活兒：我忘記為選項(xiàng)E設(shè)置度量值了，于是呆躲，我重新打開了幾十個(gè)excel异逐，為每個(gè)excel的模型添加選項(xiàng)E的度量值，然后再拖入該模型里邊的若干個(gè)數(shù)據(jù)透視表中插掂。
由于我在每個(gè)模型中灰瞻，根據(jù)人員信息表中的人員屬性維度，為每一個(gè)維度添加了數(shù)據(jù)透視表辅甥，這樣一來酝润，就可以做很精細(xì)的分析。換了以前璃弄，要看某個(gè)選項(xiàng)男女選擇的差異要销，將會(huì)是一件很復(fù)雜的事情。
至此夏块，處理基本完畢疏咐，剩下的就是根據(jù)統(tǒng)計(jì)匯總的數(shù)據(jù)透視表作圖纤掸，略過。
五浑塞、題外話
想起很多年前茁肠，線上答題不方便的時(shí)候，都是用的紙質(zhì)問卷答題缩举，然后找人手工一個(gè)一個(gè)錄入到excel或spss中處理，相當(dāng)耗時(shí)匹颤；后來用了電子版問卷仅孩，但坑爹的是采取的是這個(gè)例子中的word答題方式，處理起來仍然耗時(shí)耗力印蓖。在現(xiàn)在人人有手機(jī)辽慕，各種線上調(diào)研系統(tǒng)百花齊放的情況下，再也不要采取原始的方式進(jìn)行調(diào)研赦肃，害人害己溅蛉，勞民傷財(cái)。這個(gè)例子只是在既成事實(shí)的基礎(chǔ)上嘗試把繁重的體力活兒盡可能縮短到幾個(gè)小時(shí)以內(nèi)他宛，沒有任何推廣和借鑒的價(jià)值船侧。
