基于Google Sheets的导师-学员匹配系统构建及公式问题求助

需求回顾 #

• 数据源：导师表含技能、职位、时区等属性；学员表含带权重的意向技能、时区等偏好
• 规则：每位导师最多匹配2名学员
• 输出：以百分比形式呈现匹配相似度

原公式核心问题 #

1. 匹配名额判断逻辑错误：原公式中MAX(FILTER(...))无法准确识别导师剩余匹配名额，应通过分组排名限制前2名
2. 权重计算维度不匹配：直接用SUMPRODUCT混合不同长度的属性数组，未先给导师/学员数据分别加权
3. 未格式化百分比输出：计算结果未转换为百分比格式

修正后的公式方案 #

假设数据结构：

• A列：导师ID（用于分组）
• B-E列：导师属性（技能、职位等）
• G-H列：学员带权重的偏好（意向技能、时区等）

直接使用以下公式生成匹配结果：

=ArrayFormula(
  IF(ROW(A:A)=1,"Match %",
    IF(A:A="","",
      BYROW(A2:E2:H2, LAMBDA(row,
        LET(
          tutor_id, INDEX(row, 1),
          tutor_attrs, INDEX(row, 2):INDEX(row, 5),
          student_prefs, INDEX(row, 7):INDEX(row, 8),
          // 给导师属性和学员偏好分别加权
          weighted_tutor, tutor_attrs * {0.4, 0.3, 0.2, 0.1},
          weighted_student, student_prefs * {0.6, 0.4},
          // 计算带权重的余弦相似度
          sim, SUMPRODUCT(weighted_tutor, weighted_student) / (SQRT(SUMSQ(weighted_tutor)) * SQRT(SUMSQ(weighted_student))),
          // 筛选当前导师的所有匹配相似度，计算当前行排名
          tutor_all_sims, FILTER(
            BYROW(A2:E:H, LAMBDA(r,
              SUMPRODUCT(INDEX(r,2):INDEX(r,5)*{0.4,0.3,0.2,0.1}, INDEX(r,7):INDEX(r,8)*{0.6,0.4}) / (SQRT(SUMSQ(INDEX(r,2):INDEX(r,5)*{0.4,0.3,0.2,0.1})) * SQRT(SUMSQ(INDEX(r,7):INDEX(r,8)*{0.6,0.4})))
            )),
            A2:A = tutor_id
          ),
          rank, RANK(sim, tutor_all_sims, 0),
          // 仅保留前2名匹配结果，格式化为百分比
          IF(rank <= 2, TEXT(sim, "0.00%"), "")
        )
      ))
    )
  )
)

逻辑说明 #

1. 逐行处理数据：用BYROW遍历每一组导师-学员配对数据
2. 加权余弦相似度：先给导师属性、学员偏好分别应用权重，再计算余弦相似度（衡量匹配度的标准）
3. 名额限制：通过FILTER筛选当前导师的所有匹配结果，用RANK排序后仅保留前2名
4. 格式转换：将相似度（0-1区间）转换为百分比格式输出

注意事项 #

• 若你的数据列位置不同，需调整公式中INDEX(row, N)的索引值，确保对应导师属性、学员偏好的列
• 导师ID列需唯一，否则分组筛选会出错
• 可根据需求调整权重数组{0.4,0.3,0.2,0.1}和{0.6,0.4}，权重总和无需为1，余弦相似度会自动归一化

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