多列问卷答案按年龄组与选项分组统计的Google Sheets实现

问题背景 #

现有问卷响应数据表：

• B列：受访者年龄组（如青少年/成人）
• W至Z列：同一问题的多个可选答案（列与选项无固定对应关系，每人可选择1-4个选项）

需按年龄组+选项分组统计各选项的选择数量。已知单列答案时的QUERY语句为：

=QUERY(B:Z; "SELECT B, Z, COUNT(Z) GROUP BY B, Z LABEL B 'Age Group', Z 'Variant', COUNT(Z) 'Quantity'"; 1)

修改后的QUERY语句 #

由于W-Z列是同问题的多选项，需先将多列数据逆透视为「年龄组+选项」的二维结构，再进行分组统计。最终语句如下：

=QUERY(
  {B:B,W:W;B:B,X:X;B:B,Y:Y;B:B,Z:Z},
  "SELECT Col1, Col2, COUNT(Col2) 
   WHERE Col2 IS NOT NULL 
   GROUP BY Col1, Col2 
   LABEL Col1 'Age Group', Col2 'Variant', COUNT(Col2) 'Quantity'",
  1
)

语句说明 #

1. 构造联合数组：{B:B,W:W;B:B,X:X;B:B,Y:Y;B:B,Z:Z}将每一列选项（W/X/Y/Z）与对应的B列年龄组逐一配对，把多列结构转换为统一的「年龄组-选项」二维表，保留所有有效选项。
2. 过滤空值：WHERE Col2 IS NOT NULL排除未选择的空单元格，避免统计无效数据。
3. 分组统计：SELECT Col1, Col2, COUNT(Col2) GROUP BY Col1, Col2按年龄组（Col1）和选项（Col2）分组，统计每个组合的出现次数。
4. 重命名表头：LABEL语句将默认的列名称替换为更直观的业务名称。

示例验证 #

输入表格 #

预期输出表格 #

使用修改后的语句可直接得到上述预期结果。

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