Excel如何统计含逗号分隔数字与区间的单元格内数字总数量

方案1：Excel 365 / 2021 及以上版本（原生公式） #

目标数据在C10单元格时，直接使用以下公式：

=LET(
clean_str, SUBSTITUTE(TRIM(C10)," ",""),
arr, TEXTSPLIT(clean_str, ","),
SUM(
BYROW(arr, LAMBDA(x,
IF(ISNUMBER(FIND("-",x)),
--TEXTAFTER(x,"-") - --TEXTBEFORE(x,"-") + 1,
1
)
))
)

公式逻辑说明 #

• 先清理字符串：移除首尾空格、删除所有中间空格，避免格式不统一导致的错误
• 按逗号拆分字符串，得到所有独立的数字/区间项数组
• 遍历每个项：如果包含-则判定为区间，用右区间值减左区间值+1得到区间包含的数字总数；如果是单个数字则计为1
• 所有项的计数求和即为最终结果
  测试用例输入1-5, 8, 10, 15时，输出结果为8，完全符合需求

方案2：低版本Excel兼容方案（VBA自定义函数） #

如果你的Excel版本不支持LET、TEXTSPLIT等新函数，可通过自定义函数实现：

1. 按下Alt+F11打开VBA编辑器，依次点击「插入」→「模块」
2. 在模块编辑区粘贴以下代码：

Function CountRangeNums(target As Range) As Long
    Dim clean_str As String
    Dim item_arr As Variant
    Dim tmp_arr As Variant
    Dim i As Long
    ' 清理字符串
    clean_str = Replace(Trim(target.Value), " ", "")
    ' 按逗号拆分
    item_arr = Split(clean_str, ",")
    ' 遍历计数
    For i = LBound(item_arr) To UBound(item_arr)
        If InStr(item_arr(i), "-") > 0 Then
            tmp_arr = Split(item_arr(i), "-")
            CountRangeNums = CountRangeNums + CLng(tmp_arr(1)) - CLng(tmp_arr(0)) + 1
        Else
            CountRangeNums = CountRangeNums + 1
        End If
    Next
End Function

3. 关闭VBA编辑器回到Excel界面，在需要输出结果的单元格输入=CountRangeNums(C10)即可得到统计值。

注意事项 #

• 请确保区间格式为「小数字-大数字」的合法格式，否则公式会返回错误
• 如果你的分隔符不是逗号，可自行修改公式/代码中的拆分分隔符即可适配

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