几年前，我就遇到过这种非标准Excel文件；没想到几年过去了，我又遇到了这种奇葩文件。
来看下奇葩。
乍一看，除了文件名后缀是xls，没有什么特别。
看上去一切正常.png
打开试试看：
扩展名不匹配.png
咦？明明是xls格式，怎么打开会有这种奇怪提示？
忽略提示，打开文件，貌似又一切ok了（除了该死的合并单元格和选项上的蓝色扎眼）。
问卷.png
用powerquery处理Excel文件的方法来处理试试看：
不是预期的外部格式.png
哈，出错了……
先看看我们要实现的目标
目标
我们的目标是实现上图中的统计效果。
可能有好奇宝宝要问了，既然有系统了，干嘛还要用powerquery来处理？
原因比较简单：发布问卷的哥们儿脑袋被门夹了，发布了四个一模一样的问卷，于是，他们系统几千几万人的答题结果就分布在这四个问卷里，现在要把四个问卷结果汇总成一个……
那复制粘贴不行么？
可以的……然而有些同样不动脑子的答题人把四个问卷都做了，或者做了其中的若干个，同样的题目答案还不一样。此外，表格中除了有答题人答题的记录，还有没有答题的人的记录……
所以，必须另寻他法。我对powerquery最熟，当然是用powerquery；我估计用python也可以，但是我python不熟，估计要花很多时间才能完成。
我用powerquery花了一个小时完成从数据处理、清洗到最后出结果。中间还有其他事情打断，所以自认为速度还是可以了。
第一步：解决奇葩Excel导入到Powerquery的问题
我很早之前用的是打开另存大法：将这些奇葩Excel文件用网上找的vba代码来另存为xlsx格式，这样处理起来就比较方便了。
这次我准备又另存为xlsx格式时，无意中发现下面这个界面：
windows在另存时提示是html文件格式
既然是html格式，那么就可以按照powerquery处理html格式文件的方式来处理。于是，尝试了以下代码：
= Table.AddColumn(源, "自定义", each Web.Page([Content]))
结果没出错：
webpage.png
试试看展开，寻找有没有需要的数据：
展开表格.png
结果发现数据在每个文件的前两个Table中，其中一个是答题结果，一个是没答题的人。
这样一来，就好办了。
第二步：剔除不必要的部分，保留需要的Table。
从上一步我们可以判断，每个文件的第一个表格是答题数据，第二个表格是没答题的人的数据，第三个Sevice跟我们的任务无关，所以我们只需要保留每个文件的第一个表格就好了。
添加一个索引列，然后用3来除索引，余数为0的就是我们需要的表格。
删除的其他列 = Table.SelectColumns(#"展开的“自定义”",{"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))
获得的结果如下：
获得的数据.png
第三步：继续进行数据清洗与合并
从这一步开始，对数据进行清洗有两种办法：
一种是基于上一步产生的表格中的任意一行包含的数据，进行单独处理，然后建立一个函数，将其应用到所有行。
另外一种是直接合并所有数据，再进行清洗。
两种方法各有利弊：函数法比较优雅，由于是单独处理一份数据，所以运行速度快一些，也不容易遗漏清洗步骤；直接合并后再清洗是对所有数据进行操作，运行速度会慢一些，还可能会漏掉清洗步骤。
我偷懒采取第二种办法（因为写函数虽然运行速度快，但写函数本身需要时间呀）。
点击【Data】列旁边的展开符号，展开所有数据并进行合并。
展开的“Data”= Table.ExpandTableColumn(筛选的行, "Data", Table.ColumnNames(筛选的行[Data]{0}))，
由于默认展开有52列，且PowerQuery会自动把这52列的列名都全部列出，这样会显得很长且有硬编码的弊端，所以我把Table.ExpandTableColumn()函数的第三个参数单独写了下，也就是用【Data】列的任意一个表格的列名来作为这第三个参数。
然后删除第一行的题目信息（我们待会儿会用别的方式单独处理），再提升标题。
删除的顶端行 = Table.Skip(#"展开的“Data”",1),
提升的标题 = Table.PromoteHeaders(删除的顶端行, [PromoteAllScalars=true]),
由于四个table都有刚才我们处理掉的两行，所以我们需要从其他表格里也剔除这两行，办法就是通过筛选。
这里筛选时有个小技巧：由于数据很多，所以我们找一个值很单一的列来剔除不需要的行会更容易。比如我选择了从一个题目的列下面筛选剔除。
筛选的行1 = Table.SelectRows(更改的类型, each ([#"A、25周岁以下（含25周岁）"] <> "1、您的年龄段是？(单选)" and [#"A、25周岁以下（含25周岁）"] <> "A、25周岁以下（含25周岁）")),
但是上一步并没剔除完全部不需要的数据，还有一行汇总的也要剔除。通过观察可以发现，在账号列都是手机号，而汇总行对应的值是一个文本【账号】。这里我直接把账号列变为整数，那么出现文本的行会报错，直接删除错误，就剔除了汇总行了。
更改的类型1 = Table.TransformColumnTypes(筛选的行1,{{"选项_2", Int64.Type}}),
删除的错误 = Table.RemoveRowsWithErrors(更改的类型1, {"选项_2"}),
删除的列 = Table.RemoveColumns(删除的错误,{"选项", "选项_2", "选项_3", "选项_4"})
第四步：解决一个人答了多份问卷的问题
由于四个Excel文件导出时，是按时间现后顺序导出的，而且一般答题人也不会先回答后面的问卷再回头去填写前面的问卷。所以，凡是多次答题的人，我们取其最近一次答题的结果（也就是文件序号最大的那个excel文件中的结果）。
由于要用到excel文件序号，所以我们先获取文件名中的文件序号。并将结果数据格式改为整数，便于后面比大小以决定针对多次答题的人取哪个结果。
= Table.TransformColumns(重命名的列,{"name",each Text.BetweenDelimiters(_,"(",")")})，
= Table.TransformColumnTypes(自定义1,{{"name", Int64.Type}}),
这时针对【账号】列进行分组，不选姓名列是因为可能存在同名同姓。
= Table.Group(删除的列1, {"账号"}, {{"计数", each Table.RowCount(_), type number}, {"all", each _, type table }}),
获得的结果如下：
groupby.png
可以发现，确实有不少人答了两次问卷。
前面已经确认，对于多次答题的人，我们只需要获取ta答过题的excel文件序号中最大的那个就可以了。这个文件序号隐藏在上表中的【all】列，所以，我们要选取【all】列中每一个表格里name值最大的那一行。然后再展开，代码如下：
= Table.TransformColumns(分组的行,{"all",each Table.Max(_,"name")}),
= Table.ExpandRecordColumn(删除的其他列3, "all",Record.FieldNames(删除的其他列3[all]{0})),
对结果进行简单清理。
第五步：对问卷进行处理
上一步我们得到了一个table，如下所示：
待清理的问卷.png
现在我们要从这个table得到选择题的结果（因为问答题的结果我们需要单独处理）。由于我们不需要对单个答题人进行处理，所以我们只需要选中所有选择题的列就行了，删除【姓名】、【账号】等不必要的列。结果如下：
问题及回答.png
我们怎么得到问题选项及其计数呢？我们需要得到一个问题选项及其回答计数的列表。也就是说，要把横着排的问题选项变成竖排，把它们的回答进行计数。计数比较简单，就是数非null的值个数。但是由于这个table包含了几千上万人的答题结果，所以我们需要根据题目选项进行分组计数。
于是，关键在于，我们如何获得所有人答题选项的分组。
我是这样做的：
首先把列标题降级：
= Table.DemoteHeaders(删除的其他列4)
这样可以发现表格其实被分为两部分：第一行是题目选项，剩下的是选项对应的答题结果。因此，我们只要得到两个表格：一个是只有一行内容，但是列名为Column1，Column2……Column44；另一个表格就是所有人答题的结果，列名也是Column1，Column2……Column44。这样，这两个表格就可以通过列名进行关联了。
出于便利考虑，获取有44列、1行的题目选项表格，我们手工在excel表格中构造，然后添加到数据模型就可以了。
选项答案的表格，通过删除列标题降级后的第一行内容得到。然后unpivot所有列：
= Table.UnpivotOtherColumns(删除的顶端行1, {}, "属性", "值"),
= Table.Group(逆透视的列, {"属性"}, {{"计数", each Table.RowCount(_), type number}}),
得到的结果如下：
unpivot.png
由于每一列对应一个选项，所以我们不用担心null值会导致错行。
通过在powerpivot中，将此表格和题目选项表格建立关系，就得到了题目选项和答案的关系。
接下来，我们要将题目选项和题目对应起来，以便题目和选项、选项的答案对应起来。这一步还是在excel中表格中进行构造即可。然后加载到数据模型，建立关系。步骤略。
最后我们从中间步骤提取下问答题的结果即可。也就是在第一次用groupby()函数的上一步提取问答题的结果，将其单独加载到excel表格（不是数据模型，因为无需对它进行进一步处理）。
第六步：获取最后结果
数据模型建好之后，获取最终结果就非常简单了：基于数据模型添加数据透视表，将题目、选项及结果计数拉入数据透视表对应位置，将计数显示为【父行汇总的百分比】，再用条件格式添加数据条显示方式即可。
最后的结果.png
注意，由于我没对题目排序，所以第一题过后就是第十题了。这个问题很好处理：笨一点的办法是复制数据透视表粘贴为表格（而不是保留原格式），再把第十题剪切粘贴到正确的位置。还可以在数据模型中给题目建立序号，也可以实现。
结语
当时处理这个任务时，耗时一个小时左右，但是写这个帖子，断断续续花了差不多三个小时……我也是醉了。
网友评论