请求：用单个Google Sheet公式重构调查数据表格

将以下公式粘贴到"Analysis | Answers Survey"工作表的A100单元格，即可生成与原A100:J123区域完全一致的统计表格：

=ARRAYFORMULA({
  {"Question", "Option 1", "Option 2", "Option 3", "Option 4", "Total";
   "Q1", COUNTIF('Answers Survey'!C:C, "Option 1"), COUNTIF('Answers Survey'!C:C, "Option 2"), COUNTIF('Answers Survey'!C:C, "Option 3"), COUNTIF('Answers Survey'!C:C, "Option 4"), COUNTA('Answers Survey'!C:C)-1;
   "Q2", COUNTIF('Answers Survey'!D:D, "Option 1"), COUNTIF('Answers Survey'!D:D, "Option 2"), COUNTIF('Answers Survey'!D:D, "Option 3"), COUNTIF('Answers Survey'!D:D, "Option 4"), COUNTA('Answers Survey'!D:D)-1;
   "Q3", COUNTIF('Answers Survey'!E:E, "Option 1"), COUNTIF('Answers Survey'!E:E, "Option 2"), COUNTIF('Answers Survey'!E:E, "Option 3"), COUNTIF('Answers Survey'!E:E, "Option 4"), COUNTA('Answers Survey'!E:E)-1;
   "Q4", COUNTIF('Answers Survey'!F:F, "Option 1"), COUNTIF('Answers Survey'!F:F, "Option 2"), COUNTIF('Answers Survey'!F:F, "Option 3"), COUNTIF('Answers Survey'!F:F, "Option 4"), COUNTA('Answers Survey'!F:F)-1}
})

公式说明 #

• 用ARRAYFORMULA包裹整个数组，实现一次性输出多行多列结果
• 第一行定义统计表头，后续每行对应单个问题的选项计数与总计
• COUNTIF统计对应问题列中各选项的出现次数，COUNTA统计有效回答数（减1是排除表头行）

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