Formuła programu Excel: filtruj wartości w formule tablicowej -

Formuła ogólna

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

Podsumowanie

Aby odfiltrować dane w formule tablicowej (aby wykluczyć lub wymagać pewnych wartości), możesz użyć formuły tablicowej opartej na funkcjach JEŻELI, PODAJ.POZYCJĘ i CZY.LICZBA.

W pokazanym przykładzie formuła w H5:

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

gdzie „dane” to nazwany zakres B4: D11, a „filtr” to nazwany zakres F4: F6.

Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.

Wyjaśnienie

  1. Funkcja MATCH porównuje wszystkie wartości z nazwanego zakresu „dane” z wartościami w „filtrze”
  2. ISNUMBER konwertuje dopasowane wartości na TRUE, a niedopasowane wartości na FALSE
  3. JEŻELI używa danych wyjściowych tablicy z punktu 2 powyżej do filtrowania wartości, z wyłączeniem wartości z „filtru”

Ostateczna tablica wygląda następująco:

(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 służy tylko do weryfikacji wyniku.

Filtruj, aby wykluczyć

W komórce H6 formuła została zmodyfikowana, aby wykluczać wartości przy użyciu 1-ISNUMBER, w następujący sposób:

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

To skutecznie odwraca dane wyjściowe tablicy w kroku 2 powyżej.

Interesujące artykuły...