记一次vba+word+excel+powerbi处理问卷调研结

首先说明，在人人都有智能手机、还有腾讯问卷这样好用的免费工具的时代，还使用传统的word+电子邮件的方式来做问卷调研，是一件比较让人遗憾的事情。

遗憾的是，我最近就遇到这么一个例子：要处理522份word版的调研问卷。

然而不遗憾的是，也收获了一次难得的经历，是为记。

一、为什么不直接用powerquery读word表格，而用vba？

原因在于word问卷里，个人信息是一个非常复杂的表格：

复杂的个人信息表格.png

而调研题目有单选、多选、量表和问答题等纯文字格式。如果是纯文本或纯表格，都好办，两个混杂在一起，我就没辙。

二、思路

尽管答卷很复杂， 但是所有问卷的结构都是相同，所以是有规律的，有规律就好办。
基本思路是用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答题方式，处理起来仍然耗时耗力。在现在人人有手机，各种线上调研系统百花齐放的情况下，再也不要采取原始的方式进行调研，害人害己，劳民伤财。这个例子只是在既成事实的基础上尝试把繁重的体力活儿尽可能缩短到几个小时以内，没有任何推广和借鉴的价值。