VBA中CountIfs在命名区域统计返回全0的问题求助

问题概述 #

编写自定义直方图VBA宏时，执行CountIfs统计区间频率无报错，但返回结果全为0。调试确认目标区域VYBER包含有效数值（如0,138、0,324），区间数组BIN的区间设置正确（如0到0,1、0,1到0,2等），核心怀疑是区域设置导致的逗号/小数点格式冲突。

相关代码片段 #

'declaration of range "VYBER" contains various numbers ususaly in range +-0,5
Set WS = Sheets(ActiveSheet.Name)
Set BUNKA = WS.Range(WS.Cells(RADEK, 8), WS.Cells(RADEK, 8))
Set VYBER = WS.Range(BUNKA, BUNKA.End(xlToRight))
USL = WS.Range(WS.Cells(RADEK, 2), WS.Cells(RADEK, 2)).Value
LSL = WS.Range(WS.Cells(RADEK, 3), WS.Cells(RADEK, 3)).Value

'BINs declaration as array, USL and LSL are upper and lower limits taken from cells
Dim BIN() As Variant
ReDim BIN(1 To 23)

BINWIDTH = (USL - LSL) / 10
BIN(1) = -100
BIN(2) = 2 * LSL
BIN(23) = 100

For i = 3 To 22
    BIN(i) = Round(BIN(i - 1) + BINWIDTH, 1)
Next i

'Frequency/YAXIS
Dim YAXIS() As Variant
ReDim YAXIS(1 To 22)

For i = 1 To 22
    YAXIS(i) = Application.WorksheetFunction.CountIfs(VYBER, ">=" & BIN(i), VYBER, "<" & BIN(i + 1))
Next i

调试信息 #

调试代码 #

Debug.Print VYBER.Address, BIN(i), BIN(i + 1), YAXIS(i)

调试输出 #

$H$9:$AU$9    -100          -1             0 
$H$9:$AU$9    -1            -0,9           0 
$H$9:$AU$9    -0,9          -0,8           0 
$H$9:$AU$9    -0,8          -0,7           0 
$H$9:$AU$9    -0,7          -0,6           0 
$H$9:$AU$9    -0,6          -0,5           0 
$H$9:$AU$9    -0,5          -0,4           0 
$H$9:$AU$9    -0,4          -0,3           0 
$H$9:$AU$9    -0,3          -0,2           0 
$H$9:$AU$9    -0,2          -0,1           0 
$H$9:$AU$9    -0,1           0             0 
$H$9:$AU$9     0             0,1           0 
$H$9:$AU$9     0,1           0,2           0 
$H$9:$AU$9     0,2           0,3           0 
$H$9:$AU$9     0,3           0,4           0 
$H$9:$AU$9     0,4           0,5           0 
$H$9:$AU$9     0,5           0,6           0 
$H$9:$AU$9     0,6           0,7           0 
$H$9:$AU$9     0,7           0,8           0 
$H$9:$AU$9     0,8           0,9           0 
$H$9:$AU$9     0,9           1             0 
$H$9:$AU$9     1             100           0 

VYBER区域数值 #

0,138 0,324 0,156 0,132 0,13 0,263 0,185 0,27 0,147 0,16 0,187 0,284 0,204 0,239 0,172 0,157 0,269 0,228 0,283 0,22 0,151 0,322 0,287 0,317 0,32 0,242 0,27 0,225 0,202 0,207 0,242 0,222 0,322 0,277 0,225 0,297 0,296 0,168 0,207 0,247

原因分析 #

核心问题是区域设置的数值格式冲突：

• 系统区域设置使用逗号作为小数分隔符，VBA将BIN(i)转成字符串时生成带逗号的格式（如0,1）。
• Excel的CountIfs函数内部仅识别英文小数点作为小数分隔符，不受系统区域设置影响。当条件字符串为">=0,1"时，函数无法正确解析为数值条件，导致所有数值无法匹配，返回0。

解决方案 #

提供两种可靠的解决方法：

方法1：修正CountIfs的条件字符串格式 #

将BIN数值转成带英文小数点的字符串后传入CountIfs：

For i = 1 To 22
    '把逗号替换成小数点，生成符合CountIfs要求的条件字符串
    Dim lowerCond As String, upperCond As String
    lowerCond = ">=" & Replace(CStr(BIN(i)), ",", ".")
    upperCond = "<" & Replace(CStr(BIN(i + 1)), ",", ".")
    YAXIS(i) = Application.WorksheetFunction.CountIfs(VYBER, lowerCond, VYBER, upperCond)
Next i

方法2：手动遍历计数（避免区域设置依赖） #

直接遍历VYBER的每个单元格，用数值比较判断是否在区间内，完全绕开字符串解析问题：

For i = 1 To 22
    Dim cnt As Long
    cnt = 0
    Dim cell As Range
    For Each cell In VYBER
        '直接用数值比较，不受逗号/小数点格式影响
        If cell.Value >= BIN(i) And cell.Value < BIN(i + 1) Then
            cnt = cnt + 1
        End If
    Next cell
    YAXIS(i) = cnt
Next i

验证 #

修改代码后重新调试，YAXIS数组应能正确统计对应区间的数值数量。例如，0,138会落在0,1到0,2的区间，对应YAXIS(13)会有非零计数。

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