Formula generica
=FILTER(list1,COUNTIF(list2,list1))
Sommario
Per filtrare i dati per estrarre i valori corrispondenti in due elenchi, è possibile utilizzare la funzione FILTER e la funzione COUNTIF o COUNTIFS. Nell'esempio mostrato, la formula in F5 è:
=FILTER(list1,COUNTIF(list2,list1))
dove list1 (B5: B16) e list2 (D5: D14) sono intervalli denominati. Il risultato restituito da FILTER include solo i valori in list1 che appaiono in list2 .
Nota: FILTER è una nuova funzione di matrice dinamica in Excel 365.
Spiegazione
Questa formula si basa sulla funzione FILTER per recuperare i dati in base a un test logico costruito con la funzione COUNTIF:
=FILTER(list1,COUNTIF(list2,list1))
lavorando dall'interno verso l'esterno, la funzione CONTA.SE viene utilizzata per creare il filtro effettivo:
COUNTIF(list2,list1)
Si noti che stiamo usando list2 come argomento dell'intervallo e list1 come argomento dei criteri. In altre parole, chiediamo a COUNTIF di contare tutti i valori in list1 che appaiono in list2. Poiché stiamo fornendo COUNTIF più valori per i criteri, otteniamo un array con più risultati:
(1;1;0;1;0;1;0;0;1;0;1;1)
Notare che l'array contiene 12 conteggi, uno per ogni valore in list1 . Un valore zero indica un valore in list1 che non si trova in list2 . Qualsiasi altro numero positivo indica un valore in list1 che si trova in list2 . Questo array viene restituito direttamente alla funzione FILTER come argomento include:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
La funzione di filtro utilizza l'array come filtro. Qualsiasi valore in lista1 associato a uno zero viene rimosso, mentre qualsiasi valore associato a un numero positivo sopravvive.
Il risultato è una matrice di 7 valori corrispondenti che si estendono nell'intervallo F5: F11. Se i dati cambiano, FILTER ricalcola e restituisce un nuovo elenco di valori corrispondenti in base ai nuovi dati.
Valori non corrispondenti
Per estrarre valori non corrispondenti da list1 (cioè valori in list1 che non compaiono in list2 ) puoi aggiungere la funzione NOT alla formula in questo modo:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
La funzione NOT inverte effettivamente il risultato da CONTA.SE: qualsiasi numero diverso da zero diventa FALSO e qualsiasi valore zero diventa VERO. Il risultato è un elenco dei valori in list1 che non sono presenti in list2 .
Con INDEX
È possibile creare una formula per estrarre i valori corrispondenti senza la funzione FILTRO, ma la formula è più complessa. Un'opzione è usare la funzione INDICE in una formula come questa:
La formula in G5, copiata in basso è:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO, tranne che in Excel 365.
Il nucleo di questa formula è la funzione INDICE, che riceve list1 come argomento della matrice. La maggior parte della formula rimanente calcola semplicemente il numero di riga da utilizzare per la corrispondenza dei valori. Questa espressione genera un elenco di numeri di riga relativi:
ROW(list1)-ROW(INDEX(list1,1,1))+1
che restituisce un array di 12 numeri che rappresentano le righe in list1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Questi sono filtrati con la funzione IF e la stessa logica usata sopra in FILTER, basata sulla funzione COUNTIF:
COUNTIF(list2,list1) // find matching values
L'array risultante ha questo aspetto:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Questo array viene consegnato direttamente alla funzione SMALL, che viene utilizzata per recuperare il numero di riga corrispondente successivo mentre la formula viene copiata nella colonna. Il valore k per SMALL (pensa nth) viene calcolato con un intervallo in espansione:
ROWS($G$5:G5) // incrementing value for k
La funzione SE.ERRORE viene utilizzata per intercettare gli errori che si verificano quando la formula viene copiata ed esaurisce i valori corrispondenti. Per un altro esempio di questa idea, vedi questa formula.
