У меня есть книга 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
и других подобных функций, для которых условие должно быть задано в виде строки, если это возможно.)