Excel-formel: Filtrera värden i matrisformel

Generisk formel

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

Sammanfattning

För att filtrera en data i en matrisformel (för att utesluta eller kräva vissa värden) kan du använda en matrisformel baserad på funktionerna IF, MATCH och ISNUMBER.

I exemplet som visas, formeln i H5:

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

där "data" är det namngivna området B4: D11 och "filter" är det namngivna området F4: F6.

Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.

Förklaring

  1. MATCH-funktionen kontrollerar alla värden i det angivna intervallet "data" mot värden i "filter"
  2. ISNUMBER konverterar matchade värden till SANT och icke-matchade värden till FALSE
  3. IF använder array-utdata i # 2 ovan för att filtrera värden, exklusive värden i "filter"

Den slutliga matrisen ser ut så här:

(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 används bara för att verifiera resultatet.

Filtrera för att utesluta

I cell H6 har formeln modifierats för att utesluta värden med 1-ISNUMBER så:

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

Detta reverserar effektivt matrisutmatningen i steg 2 ovan.

Intressanta artiklar...