Формула Excel: значения фильтра в формуле массива -

Общая формула

(=IF(ISNUMBER(MATCH(data,filter,0)),data))

Резюме

Чтобы отфильтровать данные в формуле массива (чтобы исключить или потребовать определенные значения), вы можете использовать формулу массива, основанную на функциях ЕСЛИ, ПОИСКПОЗ и ЕЧИСЛО.

В показанном примере формула в H5:

(=COUNT(IF(ISNUMBER(MATCH(data,filter,0)),data)))

где «данные» - это именованный диапазон B4: D11, а «фильтр» - именованный диапазон F4: F6.

Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter.

Объяснение

  1. Функция ПОИСКПОЗ проверяет все значения в названном диапазоне «данные» на соответствие значениям в «фильтре».
  2. ISNUMBER преобразует совпадающие значения в ИСТИНА, а несовпадающие значения в ЛОЖЬ.
  3. IF использует выходные данные массива в # 2 выше для фильтрации значений, исключая значения в "filter"

Окончательный массив выглядит так:

(1, FALSE, 3; FALSE, 4, FALSE; FALSE, FALSE, FALSE; 1, FALSE, 3; FALSE, FALSE, FALSE; 3, FALSE, FALSE; FALSE, 4, FALSE; FALSE, FALSE, 4)

COUNT используется только для проверки результата.

Фильтр для исключения

В ячейке H6 формула была изменена, чтобы исключить значения с использованием 1-ISNUMBER, например:

=COUNT(IF(1-ISNUMBER(MATCH(data,filter,0)),data))

Это эффективно меняет вывод массива на шаге 2 выше.

Интересные статьи...