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

Зміст

Загальна формула

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

Резюме

Щоб відфільтрувати дані у формулі масиву (щоб виключити або вимагати певних значень), ви можете використовувати формулу масиву на основі функцій IF, MATCH та ISNUMBER.

У наведеному прикладі формула в H5:

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

де "дані" - іменований діапазон B4: D11, а "фільтр" - іменований діапазон F4: F6.

Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.

Пояснення

  1. Функція MATCH перевіряє всі значення в названому діапазоні "дані" щодо значень у "фільтрі"
  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 вище.

Цікаві статті...