Подсчет ячеек в именованном диапазоне без VBA

Question

У меня есть непересекающийся набор из 9 ячеек: A1, B3, C5, D7, E11, F13, G17, H19, I23. Я назначил имя этим ячейкам: MyPicks

У меня есть простая формула для случайного выбора значения в одной из этих ячеек:

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

формула работает:

Однако каждый раз, когда я изменяю количество ячеек в именованном диапазоне, я должен возвращаться и менять 9 в формуле! Поэтому я решил "исправить" формулу:

Первая попытка:

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

Это оказалось работать. Однако COUNTA() считает только ячейки со значениями или Null. Он игнорирует абсолютно пустые клетки. Так...........

Вторая попытка:

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

К сожалению, это приводит к # ЗНАЧЕНИЮ! ошибка, потому что COUNTBLANK() не работает на непересекающихся диапазонах. Так............

Третья попытка:

Я создал небольшой VBA UDF:

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

Использование этого UDF решило проблему. Затем я обнаружил, что мой клиент был макрофобным, и решение было немедленно отклонено.

Можно ли подсчитать общее количество ячеек в именованном диапазоне без VBA?

Answer 1

Как насчет этой маленькой красавицы:

=SUM(FREQUENCY(MyPicks,MyPicks))

Следовательно, вся ваша формула будет:

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

---

РЕДАКТИРОВАТЬ: Как указал Барри Гудини, функция SMALL будет возвращать только числовые значения. В этом случае решение SUM(FREQUENCY()) является излишним. Вместо этого просто используйте

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

Answer 2

Если каждый из непересекающихся диапазонов состоит из одной ячейки, следующая формула будет возвращать количество ячеек в диапазоне, независимо от того, какое содержимое, или даже если они пустые, что, я думаю, именно то, что вам нужно:

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