技术咨询：如何对Excel调查数据进行转换处理？

嘿，我来帮你搞定Excel里的调查数据转换！这类需求在数据分析里超常见，我把最常用的操作场景和方法整理给你，都是实战里用得顺手的：

一、宽格式转长格式（逆透视，最常用） #

很多调查数据会做成宽格式：一列是受访者ID，后面每一列对应一个问题的选项。但这种格式很难做统计分析，转成「ID-问题-答案」的长格式才是正确打开方式，用Power Query是最优解：

• 选中你的数据区域（记得包含表头）
• 点击「数据」选项卡 → 「从表格/区域」（弹出对话框时确认「我的表格有标题」）
• 进入Power Query编辑器后，选中不需要转换的列（比如受访者ID、姓名这类唯一标识列）
• 右键选中的列 → 选择「逆透视其他列」
• 这时候你会得到三列：保留的标识列、「属性」（原问题的列标题）、「值」（对应受访者的答案）
• 右键重命名「属性」和「值」为「问题」和「答案」，最后点击「关闭并上载」，转换好的表格就出现在新工作表里了

如果数据量极小，也可以用公式凑活，但不推荐大数量：比如用INDEX+MATCH组合遍历问题和答案，操作繁琐容易出错，还是Power Query香。

二、拆分合并的多选答案 #

有些调查里受访者会勾选多个选项，结果存在一个单元格里（比如「篮球,足球,羽毛球」），要拆分成多行或多列的话：

拆分成多列： #

• 选中目标列 → 点击「数据」选项卡 → 「分列」
• 选择「分隔符号」→ 下一步，勾选对应的分隔符（逗号、分号、空格等）→ 完成即可拆分

拆分成多行： #

还是Power Query效率最高：

• 把数据导入Power Query编辑器
• 选中要拆分的列 → 「转换」选项卡 → 「拆分列」→ 「按分隔符」
• 选择对应的分隔符，然后在「高级选项」里选「拆分为行」→ 确定
• 关闭并上载，就能得到每个选项单独一行的结果

三、统一答案格式 #

调查里经常会出现答案表述不统一的情况，比如「是」「YES」「对」都是同一个意思，要统一成标准格式：

• 用IF函数批量处理：比如=IF(OR(A2="YES",A2="对",A2="是的"),"是",A2)，下拉填充即可
• 或者用快捷键「Ctrl+H」打开「查找和替换」，批量替换不同表述
• 更灵活的是用Power Query的「替换值」功能，能一次性添加多组替换规则，还能预览效果

四、提取特定信息 #

如果单元格里混杂了问题描述和答案（比如「Q1-您的年龄：28岁」），要提取里面的数字或关键信息：

• 要是用的Excel 365或2021版本，直接用TEXTAFTER函数：=TEXTAFTER(A2,"：")，再用VALUE转成数字：=VALUE(TEXTAFTER(A2,"："))
• 旧版本Excel可以用MID+FIND组合：=VALUE(MID(A2,FIND("：",A2)+1,LEN(A2)-FIND("：",A2)))

内容的提问来源于stack exchange，提问作者Niels Bosma