Как посчитать количество ячеек в нескольких листах, значения которых находятся в заданном диапазоне?

Question

У меня есть книга Excel с многочисленными таблицами, содержащими такие данные, как:

    'DATA_1'                        'DATA_2'                        'DATA_3'        
    A       B                       A       B                       A       B       
1   value1     1.6              1   value1     0.8              1   value1     2.0  
2   value2     2.5              2   value2     2.2              2   value2     0.5  
3   value3     3.4              3   value3     3.1              3   value3     3.2  

Существует еще одна рабочая таблица, которая называется SUMMARY , перед всеми остальными, которая выглядит следующим образом:

    'SUMMARY'                       
    A       B       C       D       
5           min     max     count   
6   value1     1.0     1.8          
7   value2     1.5     2.5          
8   value3     2.0     3.0          

Строки с 1 по 4 которые не показаны, пусты и скрыты.

Как вы, возможно, уже догадались, я сейчас пытаюсь правильно заполнить count столбцов (D). Он должен содержать количество всех value? значения по всему DATA_? листы, которые находятся в соответствующем диапазоне от min до max пределов (значение, равное одному из пределов, рассматривается в диапазоне). Так что я ожидаю от данных образца:

    D       
5   count   
6        1  
7        2  
8        0  

---

До сих пор у меня есть пользовательская функция для получения массива имен рабочих листов, которая отлично работает:

Function SHEET_NAMES() As Variant
    ' returns names of all sheets as an array
    Dim index As Long, retArray() As String
    Application.Volatile True

    ReDim retArray(ThisWorkbook.Sheets.Count)
    For index = 1& To ThisWorkbook.Sheets.Count
        retArray(index) = ThisWorkbook.Sheets.Item(index).Name
    Next index
    SHEET_NAMES = retArray
End Function

Более того, я могу успешно получить доступ к данным value1 (репрезентативно) во всех рабочих листах с помощью {=N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))} .

Я могу проверить значения value1 относительно их min пределов с помощью {=N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))>=B6)} . Когда эта функция массива охватывает 4 ячейки, это приводит к:

 0       1       0       1  

Я могу проверить значения value1 против их max пределов с помощью {=N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))<=C6)} . Когда эта функция массива охватывает 4 ячейки, это приводит к:

 1       1       1       0  

Наконец, я также могу проверить ячейки на числовые значения с помощью {=N(ISNUMBER(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES()))))} . Когда эта функция массива охватывает 4 ячейки, это приводит к:

 0       1       1       1  

0 прибывает из SUMMARY рабочего листа, ячейка B1 которого пуста, как уже упоминалось.

Теперь моя идея состоит в том, чтобы наконец использовать функцию SUMPRODUCT и получить отдельные счетчики: {=SUMPRODUCT(N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))>=B6);N(N(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))<=C6);N(ISNUMBER(INDIRECT(ADDRESS(ROW(B1);COLUMN(B1);;;SHEET_NAMES())))))} .
Однако, хотя я ожидаю 1 = (0 * 1 * 0) + (1 * 1 * 1) + (0 * 1 * 1) + (1 * 0 * 1) , это всегда странно приводит к 0 . Так не могли бы вы сказать мне, что я здесь делаю не так?

(Я бы предпочел решение без использования COUNTIF , COUNTIFS , SUMIF , SUMIFS и других подобных функций, для которых условие должно быть задано в виде строки, если это возможно.)

Answer 1

Измените свой UDF, чтобы он выглядел так:

Function SHEET_NAMES() As Variant
    ' returns names of all sheets as an array
    Dim index As Long, retArray() As String, i As Long
    index = 1
    i = 1
    Application.Volatile True

    ReDim retArray(1 To ThisWorkbook.Sheets.Count - 1)
    Do While index <= ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(index).Name <> "SUMMARY" Then
            retArray(i) = ThisWorkbook.Worksheets(index).Name
            i = i + 1
        End If
        index = index + 1
    Loop
    SHEET_NAMES = retArray
End Function

Единственное действительно существенное изменение заключается в том, что я сделал так, чтобы он игнорировал РЕЗЮМЕ лист.

А затем используйте эту формулу массива, адаптированную отсюда (подтвердите нажатием Ctrl + Shift + Enter):

=SUM(IF(N(INDIRECT(SHEET_NAMES()&"!B"&ROW()-5))>=B6,IF(N(INDIRECT(SHEET_NAMES()&"!B"&ROW()-5))<=C6,1,0),0))

Обратите внимание, что для того, чтобы это работало, ваши данные в РЕЗЮМЕ должны начинаться со строки 6 (как и ваш образец). Если этого не произойдет, вам придется настроить ROW()-5 бит, чтобы он равнялся 1 в первой строке. Это должно работать так, потому что это то, что увеличивает строку, когда вы автоматически заполняете формулу вниз.

Изменить: Адресация ваших комментариев, используйте эту формулу. Он может использоваться с вашим исходным UDF и использует ADDRESS() для создания ссылок.

=SUM(IFERROR(IF(N(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1),,,SHEET_NAMES())))>=B6,IF(N(INDIRECT(ADDRESS(ROW(B1),COLUMN(B1),,,SHEET_NAMES())))<=C6,1,0),0),0))