☰

不使用 VBA 计算指定范围内的单元格数量

tag-icon • tag-icon microsoft-excel worksheet-function
不使用 VBA 计算指定范围内的单元格数量

我有一组不相交的 9 个单元格： A1,B3,C5,D7,E11,F13,G17,H19,I23。我已指派一名姓名对这些细胞： 我的精选

我有一个简单的公式来随机选择其中一个单元格中的值：

=SMALL(MyPicks,RANDBETWEEN(1,9))

公式有效：

在此处输入图片描述

然而每次我改变单元格数量时命名范围，我必须回去改变9在公式中！所以我决定“修复”公式：

第一次尝试：

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)))

这似乎有效。然而COUNTA()仅计算具有值或空值的单元格。它会忽略完全为空的单元格。因此...........

第二次尝试：

=SMALL(MyPicks,RANDBETWEEN(1,COUNTA(MyPicks)+COUNTBLANK(MyPicks)))

遗憾的是，这会产生 #VALUE! 错误，因为COUNTBLANK()不适用于不相交的范围。所以............

第三次尝试：

我创建了一个小型的 VBA UDF：

Public Function nCount(r As Range) As Long
    nCount = r.Count
End Function

使用这个 UDF 解决了问题。然后我发现我的客户有宏恐惧症，解决方案立即被拒绝了。

是否有可能计算出命名范围没有 VBA？

答案1

这个小美女怎么样：

=SUM(FREQUENCY(MyPicks,MyPicks))

因此，整个公式将是：

=SMALL(MyPicks,RANDBETWEEN(1,SUM(FREQUENCY(MyPicks,MyPicks))))

编辑：正如 barry houdini 指出的那样，该SMALL函数将仅返回数值。在这种情况下，SUM(FREQUENCY())解决方案就有点过了。相反，只需使用

=SMALL(MyPicks,RANDBETWEEN(1,COUNT(MyPicks)))

答案2

如果每个不相交的范围都由单个单元格组成，则以下公式将返回范围内的单元格数，无论内容是什么，即使它们是空白的，我认为这就是您想要的：

=LEN(CELL("address",MyPicks))-LEN(SUBSTITUTE(CELL("address",MyPicks),",",""))+1

相关内容