兽医研究Excel数据跨列校验：求适配LAMBDA/BYROW的CROSS-CHECK公式

针对6500个鸟类病例、多类别多诊断列的交叉校验需求，以下是适配Microsoft 365动态数组功能的公式，可一次性生成所有行的校验结果，无需手动回车，完整覆盖4种校验状态：

核心公式（单类别+对应诊断列） #

假设CATEGORY1在B列，DIAGNOSIS1:DIAGNOSIS3在C:E列，在F2单元格输入以下公式（自动填充所有行）：

=BYROW(B2:E6501,LAMBDA(r,LET(
    cat, INDEX(r, 1),
    diag_range, INDEX(r, 2, 1):INDEX(r, 4, 1),
    has_y, OR(diag_range="Y"),
    IF(cat="N", IF(has_y, 3, 0), IF(has_y, 1, 2))
)))

公式逻辑说明 #

1. BYROW+LAMBDA：遍历每一行的CATEGORY和诊断列区域，自动生成所有行的结果，解决手动回车的问题
2. LET定义变量：简化公式结构，提升可读性
   • cat：提取当前行的CATEGORY1值（Y/N）
   • diag_range：提取当前行的DIAGNOSIS1-3列区域
   • has_y：用OR函数判断诊断列中是否至少有一个"Y"
3. 四层状态判断：
   • 0：CATEGORY=N且诊断全N → IF(cat="N", IF(has_y, 3, 0), ...)
   • 1：CATEGORY=Y且至少一个诊断Y → IF(cat="Y", IF(has_y, 1, 2))
   • 2：CATEGORY=Y但诊断全N → 对应IF(has_y, 1, 2)中的2
   • 3：CATEGORY=N但至少一个诊断Y → 对应IF(cat="N", IF(has_y, 3, 0))中的3

批量多类别扩展方案 #

针对27个类别、200+诊断列的场景，可自定义复用函数提升效率：

1. 打开名称管理器（公式选项卡→名称管理器），新建名称CrossCheck，引用位置输入：

=LAMBDA(cat_col, diag_cols, BYROW(HSTACK(cat_col, diag_cols), LAMBDA(r,
    LET(
        cat, INDEX(r, 1),
        diag_range, INDEX(r, 2, 1):INDEX(r, COLUMNS(diag_cols)+1, 1),
        has_y, OR(diag_range="Y"),
        IF(cat="N", IF(has_y, 3, 0), IF(has_y, 1, 2))
    )
)))

2. 使用时直接调用函数，比如：
   • 校验CATEGORY1（B列）和DIAGNOSIS1-3（C:E）：=CrossCheck(B:B, C:E)
   • 校验CATEGORY2（F列）和DIAGNOSIS4-10（G:M）：=CrossCheck(F:F, G:M)

替代简化写法（字符串拼接版） #

如果偏好更简洁的写法，可通过字符串拼接实现判断：

=BYROW(B2:B6501&C2:E6501, LAMBDA(x,
    LET(
        cat, LEFT(x, 1),
        diags, RIGHT(x, 3),
        IF(cat="N", IF(ISNUMBER(SEARCH("Y", diags)), 3, 0), IF(ISNUMBER(SEARCH("Y", diags)), 1, 2))
    )
))

通过LEFT提取CATEGORY值，RIGHT提取诊断列的Y/N组合，SEARCH判断是否存在"Y"，逻辑与核心公式一致。

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