記一次vba+word+excel+powerbi處理問卷調研結果的經歷

首先說明，在人人都有智慧手機、還有騰訊問卷這樣好用的免費工具的時代，還使用傳統的word+電子郵件的方式來做問卷調研，是一件比較讓人遺憾的事情。

遺憾的是，我最近就遇到這麼一個例子：要處理522份word版的調研問卷。

然而不遺憾的是，也收穫了一次難得的經歷，是為記。

一、為什麼不直接用powerquery讀word表格，而用vba？

原因在於word問卷裡，個人資訊是一個非常複雜的表格：

而調研題目有單選、多選、量表和問答題等純文字格式。如果是純文字或純表格，都好辦，兩個混雜在一起，我就沒轍。

二、思路

儘管答卷很複雜， 但是所有問卷的結構都是相同，所以是有規律的，有規律就好辦。
基本思路是用vba把所有word合併成一個文件，然後把該文件內容複製到excel，再用power query來把合併起來的問卷再單獨分割成一份一份的進行處理。

三、合併所有word檔案

我自己不怎麼懂vba，直接貼網上找的合併同一個資料夾下的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

由於發來的word是按照單位資料夾存放的，因此需要把所有資料夾下的word檔案弄到一個單獨的資料夾下。可以用windows資源管理器搜尋.doc字尾的檔案，複製貼上到新資料夾。考慮到windows自帶搜尋很慢，我直接用everything搜尋工具來把所有問卷放到一個資料夾下。

有522個檔案要合併，而且這個vba其實是很簡單的程式碼，沒有狀態資訊，所以執行vba巨集之後，word就像程式崩潰的樣子，介面一片白。我等了四十多分鐘，還沒見結果，真以為word死了，強行結束後發現，已經合併了四百多個了。鬱悶得要死。只好分兩次合併，兩次貼上。

每個單獨的問卷有8頁，522個文件就有四千多頁，儘管我16G記憶體，開啟之後分頁還是要分若干分鐘，複製一下也要很久才反應過來，貼上到excel裡也要若干分鐘。雖然我沒細算時間，但從合併開始到最後把所有問卷內容貼上到excel裡邊，耗去的時間至少有兩三個小時。算是最耗時的部份之一。

弄到excel裡就好辦了，直接用powerquery連線。

四、powerquery處理過程

用word開啟這個4000多頁的文件，複製、貼上，都巨慢，但是用powerquery連線後處理起來卻輕鬆異常，不得不佩服微軟。

言歸正傳，由於我不會貼出問卷內容，程式碼其實也沒啥用處，而且由於其中有題目資訊，所以更不能貼出來以免引起麻煩。只說大體思路。

把所有問卷載入到powerquery之後的第一步，就是新增索引列，正是靠索引才能把問卷再一個一個還原。

然後就是單獨處理個人資訊部分，再單獨處理常規選擇題，再單獨處理量表題，再單獨處理問答題。在整個處理過程中，不能將答題人資訊搞丟。

所以我把每個人的問卷處理成一條記錄，也就是生成了一個超級寬的表。具體用到的技術就是查詢包含“個人資訊”、“調查問題”和“問答題”所在行的索引號，因為每份問卷就是由這兩部分構成的。如果查不到這兩個關鍵字，則返回null。下一步要利用向下填充的方式將查出來的索引號覆蓋掉null值。這樣每一部分都有一個相同的索引號了，這時這個索引號就變成問卷ID了。

接著用分組的方式，按照上面找出來的索引號分組，這樣所有問卷都被還原成了包含三個子表的記錄。

接下來分別對問卷進行三個層面的處理：一是問題層面的處理，二是問卷層面的處理，三是把問卷按照題目來分組進行處理。基本思路是建立函式。

首先是個人資訊的處理。建立函式的方式其實就是展開任意一份問卷的個人資訊子表格，對其進行處理。最關鍵的一步是用轉置（transpose）進行行列顛倒，讓個人資訊變成一行記錄。此外，由於個人資訊表格其實是六個列將欄位和欄位值分成了兩組，所以要把這兩組記錄用和並列的方式合成一組之後再轉置。然後把第一步輸入的表格用x代替，建構函式。

接下來處理調查問題。展開任意一個問卷，對調查問題的表格實行轉置操作，暫時不用對題目進行細操作，因為我們的目的只是要構造一個橫排的問卷。

函式構造好之後，代入每一份問卷。然後再把每一份問卷的三個子表用Record.Combine函式來構造新表，使三個子表能形成一條記錄，並且這條記錄包含個人資訊、選擇題和問答題的題目、選項及答案。這樣所有答題結果都變成了一行一行的記錄。

接下來，選中所有題目，然後選擇“逆透視其他列”，這樣就將每道題的答題人資訊和對應的答案形成了一一對應的關係。然後按照題目分組，這樣就把所有題目的答題人資訊和答題結果封裝起來了。

這時就可以對題目和選項及答案進行精細操作：關鍵點也在於要把豎直排列的題目和答案變成橫排。考慮到題目都超級長，我就直接用第1題、第2題來代替了。選項用新增列的方式實現，列名用字母表示選項。這裡需要注意的是，選項最多的那道題有多少個選項，就要新增多少列。然後針對新新增的選項列，對答案進行檢查：如果答案包含對應的列名字母，就記錄為“Y”或"是"或其他符號，能標記每道題的答案對應了哪些列的列名字母就ok，這樣答案就被轉化成列了。處理完畢之後依樣畫葫蘆把第一步引入的題目表格用x代替，建構函式。這時暫時不用考慮量表題。

用上一步構造的函式，對所有題目進行處理。因為調查報告要針對每一題進行統計、繪製圖表，所以，必須將所有題目單獨拆分成資料模型。這時就有兩個選擇：一是在一個excel檔案裡構建所有題目的模型；二是一個題目就用一個excel檔案。我選擇了後一種方式。原因在於，資料模型建立好之後，我要統計每個選項的選擇人數，就得為每個選項建一個度量值。題目中選項最多的有12個。相同的操作要在每一個模型上進行，非常繁雜。而採用第二種方式的好處是，我只需要改變每一個excel檔案裡powerquery生成的查詢中的題目篩選，就能快速從第一題切換到第二題，這樣大大節省了步驟。

不過方法二要求對單獨的資料模型考慮周密，否則後期修改就又是體力活兒：我忘記為選項E設定度量值了，於是，我重新開啟了幾十個excel，為每個excel的模型新增選項E的度量值，然後再拖入該模型裡邊的若干個資料透視表中。

由於我在每個模型中，根據人員資訊表中的人員屬性維度，為每一個維度新增了資料透視表，這樣一來，就可以做很精細的分析。換了以前，要看某個選項男女選擇的差異，將會是一件很複雜的事情。

至此，處理基本完畢，剩下的就是根據統計彙總的資料透視表作圖，略過。

五、題外話

想起很多年前，線上答題不方便的時候，都是用的紙質問卷答題，然後找人手工一個一個錄入到excel或spss中處理，相當耗時；後來用了電子版問卷，但坑爹的是採取的是這個例子中的word答題方式，處理起來仍然耗時耗力。在現在人人有手機，各種線上調研系統百花齊放的情況下，再也不要採取原始的方式進行調研，害人害己，勞民傷財。這個例子只是在既成事實的基礎上嘗試把繁重的體力活兒儘可能縮短到幾個小時以內，沒有任何推廣和借鑑的價值。