Formula generica
=FILTER(list1,COUNTIF(list2,list1))
Sommario
Per confrontare due elenchi ed estrarre valori comuni, è possibile utilizzare una formula basata sulle funzioni FILTER e COUNTIF. Nell'esempio mostrato, la formula in F5 è:
=FILTER(list1,COUNTIF(list2,list1))
dove list1 (B5: B15) e list2 (D5: D13) sono intervalli denominati. Il risultato, i valori visualizzati in entrambi gli elenchi, rientra nell'intervallo F5: F11.
Spiegazione
La funzione FILTER accetta un array di valori e un argomento "include" che filtra l'array in base a un'espressione o un valore logico.
In questo caso, l'array viene fornito come intervallo denominato "list1", che contiene tutti i valori in B5: B15. L' argomento include viene fornito dalla funzione COUNTIF, che è nidificata all'interno di FILTER:
=FILTER(list1,COUNTIF(list2,list1))
COUNTIF viene impostato con list2 come intervallo e list1 come criteri . Poiché forniamo CONTA.SE undici valori di criteri, CONTA.SE restituisce undici risultati in un array come questo:
(1;1;0;1;0;1;0;1;0;1;1)
Notare che gli 1 corrispondono agli elementi in list2 che appaiono in list1.
Questo array viene fornito direttamente alla funzione FILTER come argomento "include":
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
La funzione FILTER filtra list1 utilizzando i valori forniti da COUNTIF. I valori associati a zero vengono rimossi; altri valori vengono conservati.
Il risultato finale è un array di valori che esistono in entrambi gli elenchi, che ricade nell'intervallo F5: F11.
Logica estesa
Nella formula sopra, usiamo i risultati grezzi di COUNTIF come filtro. Questo funziona perché Excel valuta qualsiasi valore diverso da zero come VERO e zero come FALSO. Se CONTA.SE restituisce un conteggio maggiore di 1, il filtro continuerà a funzionare correttamente.
Per forzare esplicitamente i risultati VERO e FALSO, puoi utilizzare "> 0" in questo modo:
=FILTER(list1,COUNTIF(list2,list1)>0)
Rimuovi i duplicati o ordina
Per rimuovere i duplicati, basta annidare la formula all'interno della funzione UNIQUE:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Per ordinare i risultati, annidare nella funzione SORT:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Valori di elenco mancanti da list2
Per visualizzare i valori in list1 mancanti da list2, puoi invertire la logica in questo modo:
=FILTER(list1,COUNTIF(list2,list1)=0)
