Vispārēja formula
=FILTER(data,(header="a")+(header="b"))
Kopsavilkums
Lai filtrētu kolonnas, norādiet horizontālo masīvu argumentam include. Parādītajā piemērā formula I5 ir šāda:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Rezultāts ir filtrēta datu kopa, kurā no avota datiem ir tikai A, C un E kolonnas.
Paskaidrojums
Lai gan FILTER tiek biežāk izmantots rindu filtrēšanai, jūs varat filtrēt arī kolonnas, triks ir piegādāt masīvu ar tādu pašu kolonnu skaitu kā avota dati. Šajā piemērā mēs izveidojam nepieciešamo masīvu ar Būla loģiku, sauktu arī par Būla algebru.
Būla algebrā reizināšana atbilst AND loģikai un pievienošana atbilst OR loģikai. Parādītajā piemērā mēs izmantojam Būla algebru ar OR loģiku (papildinājumu), lai mērķētu tikai uz šādām kolonnām A, C un E:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
Pēc katras izteiksmes novērtēšanas mums ir trīs TRUE / FALSE vērtību masīvi:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Matemātiskā operācija (papildinājums) TRUE un FALSE vērtības pārvērš par 1s un 0s, lai jūs varētu domāt par šo darbību šādi:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
Galu galā mums ir viens horizontāls masīvs 1s un 0s:
(1,0,1,0,1,0)
kas tiek piegādāts tieši funkcijai FILTER kā arguments arguments:
=FILTER(B5:G12,(1,0,1,0,1,0))
Ievērojiet, ka avota datos ir 6 kolonnas un masīvā 6 vērtības, visas - 1 vai 0. FILTER izmanto šo masīvu kā filtru, lai no avota datiem iekļautu tikai 1., 3. un 5. kolonnu. 2., 4. un 6. kolonna tiek noņemta. Citiem vārdiem sakot, vienīgās kolonnas, kas izdzīvo, ir saistītas ar 1.
Ar funkciju MATCH
VAI loģikas lietošana ar pievienošanu, kā parādīts iepriekš, darbojas labi, taču tā nav pietiekami mērogojama un padara neiespējamu kā kritēriju izmantot vērtību diapazonu no darblapas. Kā alternatīvu jūs varat izmantot funkciju MATCH kopā ar šādu funkciju ISNUMBER, lai efektīvāk izveidotu argumentu include:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Funkcija MATCH ir konfigurēta, lai meklētu visas kolonnu galvenes masīva konstante ("a", "c", "e"), kā parādīts. Mēs to darām tā, lai MATCH rezultātam būtu dimensijas, kas ir saderīgas ar avota datiem, kas satur 6 kolonnas. Ievērojiet arī to, ka trešais MATCH arguments ir iestatīts uz nulli, lai piespiestu precīzu atbilstību.
Pēc MATCH palaišanas tas atgriež šādu masīvu:
(1,#N/A,2,#N/A,3,#N/A)
Šis masīvs nonāk tieši ISNUMBER, kas atgriež citu masīvu:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Tāpat kā iepriekš, šis masīvs ir horizontāls, un tajā ir 6 vērtības, atdalītas ar komatiem. FILTER izmanto masīvu, lai noņemtu 2., 4. un 6. kolonnu.
Ar diapazonu
Tā kā kolonnu galvenes jau atrodas darblapā diapazonā I4: K4, iepriekš minēto formulu var viegli pielāgot, lai diapazonu izmantotu tieši šādi:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Diapazons I4: K4 tiek novērtēts kā ("a", "c", "e") un darbojas tāpat kā masīva konstante iepriekšminētajā formulā.
