У меня есть книга Excel с многочисленными таблицами, некоторые из которых содержат числовое значение в ячейке A1:
'DATA_1' 'DATA_2' 'DATA_3'
A A A
1 1.6 1 -0.8 1
Я хочу определить количество ячеек A1 по одной формуле во всей книге, содержащей данные, поэтому в приведенном выше примере должно быть 2 потому что ячейка на последнем листе пуста.
Поскольку число рабочих листов может различаться, я использую пользовательскую функцию (UDF), которая возвращает массив, содержащий имена всех листов:
Function SHEET_NAMES() As Variant
' returns names of all sheets as an array
Dim idx As Long, retArray() As String
Application.Volatile True
With ThisWorkbook.Sheets
ReDim retArray(.Count)
For idx = 1& To .Count
retArray(idx) = .Item(idx).Name
Next idx
SHEET_NAMES() = retArray
End With
End Function
Этот UDF работает нормально, формула {=SHEET_NAMES()} возвращает ожидаемый массив ({"DATA_1","DATA_2","DATA_3"}). (Обратите внимание, что UDF необходимо рассматривать как недоступные для редактирования.)
Моя идея теперь использовать UDF вместе с функциями ADDRESS() и INDIRECT() для ссылки на ячейки A1 всех доступных рабочих листов динамически использовать ISNUMBER() для проверки того, присутствует ли действительное число, преобразование логическое значение FALSE/TRUE , чтобы 0 по N() и , наконец, использовать SUM() , чтобы суммировать все 0 как это:
{=SUM(N(ISNUMBER(INDIRECT(ADDRESS(ROW(A1);COLUMN(A1);;;SHEET_NAMES())))))}
Однако результат всегда равен 0 , даже если ни один лист не содержит пустую ячейку A1 .
Я обнаружил, что функция ADDRESS() возвращает правильный массив строк, представляющих ссылки на все ячейки A1 , который является {"DATA_1!$A$1","DATA_2!$A$1","DATA_3!$A$1"} .
INDIRECT() возвращает {#VALUE!,#VALUE!,#VALUE!} поскольку он, очевидно, не поддерживает массивы, однако контейнерная функция ISNUMBER() поддерживает массивы, так что, похоже, это правильно выполняет итерацию по элементам массива, и в результате получается {TRUE,TRUE,FALSE} .
Функция N() выполняет преобразование намеченным способом, следовательно, это приводит к {1,1,0} .
Но окончательный SUM() всегда приводит к 0 , независимо от того, сколько листов существует и содержат ли некоторые или все из них действительные числа в ячейке A1 . (Точно так же, как sidenote: если я введу формулу как функцию, не являющуюся массивом, результат зависит только от первого рабочего листа.)
Я попытался использовать NOT(ISBLANK()) и NOT(ISERROR()) вместо ISNUMBER() , и я попытался изменить SUM(N(ISNUMBER())) на SUM(COUNT()) , но без успеха (все результат 0 , кроме NOT(ISBLANK()) который дает 3).
(Также я даже пытался заменить ROW(A1) и COLUMN(A1) на ROWS($A$1:A1) и COLUMNS($A$1:A1) соответственно, так как это должно решить проблему INDIRECT() связанную с массивами. согласно некоторым веб-страницам, но без изменений.)
Не могли бы вы сказать мне, что я делаю не так, и как преодолеть ошибку, не теряя гибкости?
(Если это возможно, я бы предпочел решение без использования COUNTIF() или SUMIF() , ни каких - либо других функций , которые требуют определенного условия , чтобы дать в виде строки, чтобы сохранить портативность.)