如何在Excel中将学生周度分组表转为同组次数统计交叉引用透视表

前期准备 #

首先确认原始数据为规范一维表，包含姓名、周次、分组编号三列，每一行对应一名学生某一周的分组结果，无空值、无重复记录。如果原始表是每行对应一名学生、每列对应每周分组的宽表，先通过「数据」选项卡下的「逆透视列」功能转换为一维表。

方案1：Power Query生成（适合人数多、数据量大的场景） #

• 选中原始数据区域，点击「数据」→「从表格/区域」，将数据加载到Power Query编辑器
• 右键点击左侧查询面板中的当前查询，选择「复制」，再右键粘贴得到完全相同的配对查询，可命名为「配对表」
• 回到原查询，点击「合并查询」，选择与「配对表」合并，匹配规则勾选周次相等、分组编号相等，连接类型选择「内部」
• 点击合并后新增的列右上角的展开按钮，仅勾选「姓名」字段展开，此时会得到两列姓名，分别重命名为「姓名1」和「姓名2」
• 可选操作：添加筛选规则，保留「姓名1>姓名2」的行，可避免A-B、B-A的重复配对，生成三角矩阵；如果需要全对称的完整矩阵可跳过此步骤
• 点击「关闭并上载」，将处理好的配对数据导出到Excel工作表
• 选中导出的配对数据，点击「插入」→「数据透视表」，字段设置如下：
  • 行区域：拖入「姓名1」
  • 列区域：拖入「姓名2」
  • 值区域：拖入任意非空字段（例如「周次」），值汇总方式设置为「计数」
• 右键点击透视表的空值单元格，选择「值字段设置」→「显示方式」，将空值设置为显示0，即得到最终的同组次数交叉表。

方案2：公式快速计算（适合人数少的场景） #

直接在空白区域第一行输入所有学生姓名作为列标题，第一列输入所有学生姓名作为行标题，在交叉单元格输入如下公式即可：
=SUMPRODUCT((原始表[周次]=原始表[周次])*(原始表[分组编号]=原始表[分组编号])*(原始表[姓名]=A$1)*(原始表[姓名]=$B2))
如果不需要统计学生和自己的同组次数（默认结果等于总周数），可以补充判断条件：
=SUMPRODUCT((原始表[周次]=原始表[周次])*(原始表[分组编号]=原始表[分组编号])*(原始表[姓名]=A$1)*(原始表[姓名]=$B2)*(原始表[姓名]<>A$1))
注意根据实际数据调整单元格引用的绝对/相对位置，公式下拉、右拖填充即可得到全部结果。

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