添加变量时命名函数出现Filter Mismatch错误求助

问题背景 #

我正在构建职场仪表板，自动推荐最优录入课程：

• 仪表板复选框对应数据页多选下拉列表，匹配上的课程优先推荐
• 无匹配项时推荐排名前20%的课程（数值越低排名越优）
• 希望加入课程页「已完成」复选框逻辑，排除已完成课程，但始终报错：Filter has mismatched range sizes. Expected row count: 999. column count: 1. Actual row count: 1, column count: 1.

修正后的完整公式 #

=LET(
  -- 1. 定义所有原始数据范围，确保行数完全一致
  courseIDsAll, priorityColumn,
  dataCellsAll, SDGcolumn,
  dataRowsAll, returncolumns,
  completed, completedcolumn,
  
  -- 2. 过滤排除已完成的课程，所有范围用同一个条件确保行数一致
  keep, completed <> TRUE,
  courseIDs, IFERROR(FILTER(courseIDsAll, keep), ""),
  dataCells, IFERROR(FILTER(dataCellsAll, keep), ""),
  dataRows, IFERROR(FILTER(dataRowsAll, keep), ""),
  
  -- 3. 原有的missing逻辑不变
  missing, FILTER(sdglist, sdglist<>"", checkColumn<>TRUE),
  
  -- 4. 仅当过滤后有课程时才计算分数，避免空值报错
  scores, IF(COUNTA(courseIDs)=0, "", BYROW(dataCells, LAMBDA(cell,
    SUM(MAP(missing, LAMBDA(opt,
      N(REGEXMATCH(","&REGEXREPLACE(cell,"\s",""), ","&REGEXREPLACE(opt,"\s","")))
    )))
  ))),
  
  -- 5. 计算过滤后课程的排名
  ranks, IF(COUNTA(courseIDs)=0, "", BYROW(courseIDs, LAMBDA(id, PERCENTRANK(courseIDs, id)))),
  
  -- 6. 核心推荐逻辑，基于过滤后的数据集
  IF(COUNTA(courseIDs)=0, "所有课程已完成",
    LET(
      maxScore, IFERROR(MAX(scores), 0),
      IF(maxScore>0,
        LET(
          tiedIDs, FILTER(courseIDs, scores=maxScore),
          bestID, MIN(tiedIDs),
          bestIndex, XMATCH(bestID, courseIDs, 0),
          INDEX(dataRows, bestIndex, 0)
        ),
        LET(
          topIDs, FILTER(courseIDs, ranks<=0.2),
          IF(COUNTA(topIDs)=0, "无匹配SDG或高优先级课程",
            LET(
              bestTopID, MIN(topIDs),
              bestTopIndex, XMATCH(bestTopID, courseIDs, 0),
              INDEX(dataRows, bestTopIndex, 0)
            )
          )
        )
      )
    )
  )
)

关键改动说明 #

• 统一过滤逻辑：所有需要的数据集（courseIDs、dataCells、dataRows）用同一个keep条件过滤，确保行数完全一致，从根源避免范围不匹配。
• 空值容错处理：用IFERROR和COUNTA判断过滤后是否有数据，避免后续函数（如MAX、PERCENTRANK）在空数据集上报错。
• 修正变量名：统一变量拼写（比如原尝试中的courseIDS改为courseIDs），避免引用未定义变量。
• 基于过滤后数据计算排名：ranks使用过滤后的courseIDs，确保排名仅针对未完成课程，逻辑更准确。

之前尝试的错误原因分析 #

1. 顶部过滤的问题：

   • 原始范围（如priorityColumn和completedcolumn）行数不一致，导致FILTER返回结果行数不匹配。
   • 未处理过滤后空数据集的情况，当所有课程都已完成时，FILTER返回空值，后续计算触发范围不匹配。
   • 变量名拼写错误导致引用失效。

2. 底部过滤的问题：

   • ranks基于原始courseIDs计算，而keep是原始范围的条件，过滤后的topIDs行数与原始courseIDs不一致，后续XMATCH找索引时，索引对应原始数据，但dataRows已被过滤，导致范围不匹配。

验证步骤 #

• 检查所有原始范围（priorityColumn、SDGcolumn、returncolumns、completedcolumn）的行数是否完全相同，比如都是$B$2:$B$1000这类连续且行数一致的范围。
• 确认completedcolumn的单元格都是布尔值（TRUE/FALSE），无空白或文本值。
• 单独用FILTER(priorityColumn, completedcolumn<>TRUE)验证过滤结果的行数和内容是否符合预期。

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