Формула на Excel: Филтриране на стойности във формула на масив -

Съдържание

Обща формула

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

Обобщение

За да филтрирате данни във формула на масив (за изключване или изискване на определени стойности), можете да използвате формула на масив, базирана на функциите IF, MATCH и ISNUMBER.

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

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

където "data" е наименованият диапазон B4: D11, а "filter" е наименуваният диапазон F4: F6.

Забележка: това е формула на масив и трябва да се въведе с control + shift + enter.

Обяснение

  1. Функцията MATCH проверява всички стойности в посочения диапазон "data" спрямо стойностите в "filter"
  2. ISNUMBER преобразува съответстващите стойности в TRUE и несъответстващите стойности в FALSE
  3. IF използва изхода на масива в №2 по-горе, за да филтрира стойности, с изключение на стойностите в "филтър"

Последният масив изглежда така:

(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 по-горе.

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