Formula generică
=FILTER(data,(header="a")+(header="b"))
rezumat
Pentru a filtra coloanele, furnizați o matrice orizontală pentru argumentul include. În exemplul prezentat, formula din I5 este:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Rezultatul este un set filtrat de date care conține doar coloanele A, C și E din datele sursă.
Explicaţie
Deși FILTER este mai frecvent utilizat pentru a filtra rânduri, puteți filtra și coloane, trucul este să furnizați o matrice cu același număr de coloane ca datele sursă. În acest exemplu, construim matricea de care avem nevoie cu logică booleană, numită și algebră booleană.
În algebra booleană, multiplicarea corespunde logicii AND, iar adunarea corespunde logicii OR. În exemplul prezentat, folosim algebră booleană cu logică OR (adiție) pentru a viza doar coloanele A, C și E astfel:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
După evaluarea fiecărei expresii, avem trei matrice de valori TRUE / FALSE:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Operația matematică (adăugarea) convertește valorile TRUE și FALSE în 1s și 0s, deci vă puteți gândi la operație astfel:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
În cele din urmă, avem o singură matrice orizontală de 1 și 0:
(1,0,1,0,1,0)
care este livrat direct la funcția FILTER ca argument include:
=FILTER(B5:G12,(1,0,1,0,1,0))
Observați că există 6 coloane în datele sursă și 6 valori în matrice, toate fie 1, fie 0. FILTER folosește această matrice ca filtru pentru a include doar coloanele 1, 3 și 5 din datele sursă. Coloanele 2, 4 și 6 sunt eliminate. Cu alte cuvinte, singurele coloane care supraviețuiesc sunt asociate cu 1s.
Cu funcția MATCH
Aplicarea logicii SAU cu adăugare, așa cum se arată mai sus, funcționează bine, dar nu se scalează bine și face imposibilă utilizarea unei game de valori dintr-o foaie de lucru ca criterii. Ca alternativă, puteți utiliza funcția MATCH împreună cu funcția ISNUMBER astfel pentru a construi argumentul include mai eficient:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Funcția MATCH este configurată pentru a căuta toate anteturile de coloană din constanta matricei ("a", "c", "e") așa cum se arată. O facem astfel, astfel încât rezultatul din MATCH să aibă dimensiuni compatibile cu datele sursă, care conține 6 coloane. Observați, de asemenea, că al treilea argument din MATCH este setat ca zero pentru a forța o potrivire exactă.
După executarea MATCH, returnează o matrice ca aceasta:
(1,#N/A,2,#N/A,3,#N/A)
Această matrice merge direct în ISNUMBER, care returnează o altă matrice:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Ca mai sus, această matrice este orizontală și conține 6 valori separate prin virgule. FILTER folosește tabloul pentru a elimina coloanele 2, 4 și 6.
Cu o gamă
Deoarece anteturile de coloană sunt deja pe foaia de lucru în intervalul I4: K4, formula de mai sus poate fi ușor adaptată pentru a utiliza intervalul direct astfel:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Intervalul I4: K4 este evaluat ca ("a", "c", "e") și se comportă la fel ca constanta matricei din formula de mai sus.
