Formula generica
=FILTER(data,(header="a")+(header="b"))
Sommario
Per filtrare le colonne, fornire una matrice orizzontale per l'argomento include. Nell'esempio mostrato, la formula in I5 è:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Il risultato è un set di dati filtrato che contiene solo le colonne A, C ed E dei dati di origine.
Spiegazione
Sebbene FILTER sia più comunemente usato per filtrare le righe, puoi anche filtrare le colonne, il trucco è fornire un array con lo stesso numero di colonne dei dati di origine. In questo esempio, costruiamo l'array di cui abbiamo bisogno con la logica booleana, chiamata anche algebra booleana.
Nell'algebra booleana, la moltiplicazione corrisponde alla logica AND e l'addizione corrisponde alla logica OR. Nell'esempio mostrato, stiamo usando l'algebra booleana con logica OR (aggiunta) per indirizzare solo le colonne A, C ed E in questo modo:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
Dopo che ogni espressione è stata valutata, abbiamo tre array di valori VERO / FALSO:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
L'operazione matematica (addizione) converte i valori VERO e FALSO in 1 e 0, quindi puoi pensare all'operazione in questo modo:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
Alla fine, abbiamo un singolo array orizzontale di 1 e 0:
(1,0,1,0,1,0)
che viene consegnato direttamente alla funzione FILTER come argomento include:
=FILTER(B5:G12,(1,0,1,0,1,0))
Notare che ci sono 6 colonne nei dati di origine e 6 valori nell'array, tutti 1 o 0. FILTER utilizza questo array come filtro per includere solo le colonne 1, 3 e 5 dai dati di origine. Le colonne 2, 4 e 6 vengono rimosse. In altre parole, le uniche colonne che sopravvivono sono associate a 1.
Con la funzione MATCH
L'applicazione della logica OR con l'aggiunta come mostrato sopra funziona bene, ma non si adatta bene e rende impossibile utilizzare un intervallo di valori da un foglio di lavoro come criteri. In alternativa, puoi usare la funzione MATCH insieme alla funzione ISNUMBER come questa per costruire l'argomento include in modo più efficiente:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
La funzione CONFRONTA è configurata per cercare tutte le intestazioni di colonna nella costante di matrice ("a", "c", "e") come mostrato. Lo facciamo in questo modo in modo che il risultato di MATCH abbia dimensioni compatibili con i dati di origine, che contengono 6 colonne. Si noti inoltre che il terzo argomento in MATCH è impostato come zero per forzare una corrispondenza esatta.
Dopo l'esecuzione di MATCH, restituisce un array come questo:
(1,#N/A,2,#N/A,3,#N/A)
Questo array va direttamente in ISNUMBER, che restituisce un altro array:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Come sopra, questo array è orizzontale e contiene 6 valori separati da virgole. FILTER utilizza l'array per rimuovere le colonne 2, 4 e 6.
Con una gamma
Poiché le intestazioni di colonna sono già nel foglio di lavoro nell'intervallo I4: K4, la formula sopra può essere facilmente adattata per utilizzare l'intervallo direttamente in questo modo:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
L'intervallo I4: K4 viene valutato come ("a", "c", "e") e si comporta esattamente come la costante di matrice nella formula sopra.
