如何在Google Sheets中按平均位置排名生成排序后的唯一姓名列表

需求说明 #

现有一张记录一周内活动参与人员的Google Sheets表格，需完成以下操作：

• 按人员在每日列中的位置计算平均排名
• 生成按平均排名排序的唯一姓名列表
• 以此分析人员偏好的到场时段（上午/下午/晚上）

示例表格 #

实现步骤 #

1. 提取唯一姓名 #

用公式提取表格中所有不重复的人员姓名，假设每日数据范围为A2:C6：

=UNIQUE(FLATTEN(A2:C6))

2. 计算单日报排名 #

行号即为当日排名（行号越小排名越靠前），以Monday列（A列）为例，计算姓名X的排名：

=MATCH(X, A:A, 0)

若人员当日未参与，可将排名设为当日参与总人数+1（或按需设为较大值）。

3. 计算平均排名 #

对每个人员的每日排名取平均值，假设唯一姓名在E列（E2为目标姓名）：

=AVERAGE(IFERROR(MATCH(E2,A:A,0), COUNTA(A:A)+1), IFERROR(MATCH(E2,B:B,0), COUNTA(B:B)+1), IFERROR(MATCH(E2,C:C,0), COUNTA(C:C)+1))

IFERROR用于处理未参与日期的空值，确保平均计算有效。

4. 按平均排名排序 #

将唯一姓名与平均排名关联后排序：

=SORT(HSTACK(UNIQUE(FLATTEN(A2:C6)), ARRAYFORMULA(AVERAGE(IFERROR(MATCH(UNIQUE(FLATTEN(A2:C6)),A:A,0), COUNTA(A:A)+1), IFERROR(MATCH(UNIQUE(FLATTEN(A2:C6)),B:B,0), COUNTA(B:B)+1), IFERROR(MATCH(UNIQUE(FLATTEN(A2:C6)),C:C,0), COUNTA(C:C)+1)))), 2, TRUE)

时段偏好分析 #

• 平均排名越靠前，说明人员越倾向于上午时段到场
• 平均排名居中，偏好下午时段
• 平均排名靠后，偏好晚上时段

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