如何验证单单元格内多数字/数值区间中是否存在指定值

问题背景 #

需要统计指定值（如示例中的数字7）在一列单元格中的出现次数，每个单元格包含多个独立数字或数值区间：

• 元素以逗号（,）分隔
• 区间用短横线（-）表示（如5-9）
• 单元格内的数字与区间无重叠
  要求不拆分单元格到其他位置，现有公式仅支持单个区间的单元格，无法处理多元素场景。

适用Excel 365/2021的简洁公式 #

假设指定值存于B1，目标数据列为A:A，使用以下公式（Excel 365自动支持数组运算，无需手动按组合键）：

=SUM(BYROW(A:A, LAMBDA(cell, 
    IF(cell="", 0, 
        SUM(--(BYROW(TEXTSPLIT(cell, ","), LAMBDA(item, 
            LET(trimmed, TRIM(item),
                IF(ISNUMBER(SEARCH("-", trimmed)),
                    ISBETWEEN($B$1, VALUE(LEFT(trimmed, SEARCH("-", trimmed)-1)), VALUE(RIGHT(trimmed, LEN(trimmed)-SEARCH("-", trimmed)))),
                    VALUE(trimmed)=$B$1
                )
            )
        )))
    )
)))

公式逻辑拆解

1. BYROW(A:A, LAMBDA(cell, ...))：遍历数据列的每个单元格
2. TEXTSPLIT(cell, ",")：在内存中将单元格内容按逗号拆分为独立元素（不修改原单元格）
3. BYROW(..., LAMBDA(item, ...))：遍历每个拆分后的元素：
   • 用TRIM清除元素前后空格，避免格式干扰
   • 若元素包含-，用ISBETWEEN判断指定值是否落在区间内
   • 若为单个数字，直接判断是否与指定值相等
4. SUM(--(...))：将布尔结果（TRUE/FALSE）转为数值（1/0）并求和，得到当前单元格的匹配次数
5. 外层SUM：汇总所有单元格的匹配次数，得到最终统计结果

适用旧版Excel的兼容公式 #

如果使用不支持TEXTSPLIT和BYROW的旧版Excel，可使用以下数组公式（需按Ctrl+Shift+Enter确认）：

=SUMPRODUCT(--(ISNUMBER(MATCH($B$1, 
    IFERROR(
        VALUE(TRIM(MID(SUBSTITUTE(A:A, ",", REPT(" ", 99)), (ROW(INDIRECT("1:"&LEN(A:A)-LEN(SUBSTITUTE(A:A, ",", ""))+1))-1)*99+1, 99))),
        IFERROR(
            VALUE(LEFT(TRIM(MID(SUBSTITUTE(A:A, ",", REPT(" ", 99)), (ROW(INDIRECT("1:"&LEN(A:A)-LEN(SUBSTITUTE(A:A, ",", ""))+1))-1)*99+1, 99)), SEARCH("-", TRIM(MID(SUBSTITUTE(A:A, ",", REPT(" ", 99)), (ROW(INDIRECT("1:"&LEN(A:A)-LEN(SUBSTITUTE(A:A, ",", ""))+1))-1)*99+1, 99))-1)),
            VALUE(RIGHT(TRIM(MID(SUBSTITUTE(A:A, ",", REPT(" ", 99)), (ROW(INDIRECT("1:"&LEN(A:A)-LEN(SUBSTITUTE(A:A, ",", ""))+1))-1)*99+1, 99)), LEN(TRIM(MID(SUBSTITUTE(A:A, ",", REPT(" ", 99)), (ROW(INDIRECT("1:"&LEN(A:A)-LEN(SUBSTITUTE(A:A, ",", ""))+1))-1)*99+1, 99))-SEARCH("-", TRIM(MID(SUBSTITUTE(A:A, ",", REPT(" ", 99)), (ROW(INDIRECT("1:"&LEN(A:A)-LEN(SUBSTITUTE(A:A, ",", ""))+1))-1)*99+1, 99))))
        )
    ), 0)
)))

兼容公式逻辑

通过SUBSTITUTE+MID模拟拆分单元格元素，用IFERROR区分单个数字和区间，最终通过MATCH判断指定值是否匹配，SUMPRODUCT汇总结果。

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