Trova valori mancanti

A volte è necessario confrontare due elenchi in Excel per verificare se un valore dell’elenco A è presente nell’elenco B. Ad esempio, hai un elenco di prodotti e vuoi controllare se i tuoi articoli compaiono anche nell’elenco fornito dal tuo fornitore. Di seguito ti proponiamo tre metodi: scegli quello che preferisci.

Trova valori mancanti con CONFRONTA, VAL.NON.DISP e SE
Trova valori mancanti con CERCA.VERT, VAL.NON.DISP e SE
Trova valori mancanti con CONTA.SE e SE

Trova valori mancanti con CONFRONTA, VAL.NON.DISP e SE

Per verificare se tutti i prodotti del tuo elenco esistono nell'elenco del fornitore, come mostrato nello screenshot precedente, puoi innanzitutto utilizzare la funzione CONFRONTA per individuare la posizione di un prodotto del tuo elenco (valore dell’elenco A) all’interno dell’elenco del fornitore (elenco B). La funzione CONFRONTA restituirà l’errore #N/D quando un prodotto non viene trovato. A quel punto, puoi passare il risultato alla funzione VAL.NON.DISP per trasformare gli errori #N/D in VERO, segnalando così che quei prodotti sono mancanti. Infine, la funzione SE ti fornirà il risultato desiderato.

Sintassi generica

=IF(ISNA(MATCH()))«lookup_value»,lookup_range,0)),«Missing»,«Found»)

√ Nota: puoi sostituire «Mancante» e «Trovato» con qualsiasi valore desideri.

Per verificare se tutti i prodotti del tuo elenco esistono nell'elenco del fornitore, copia o inserisci la formula seguente nella cella H6 e premi Invioper ottenere il risultato:

=IF(ISNA(MATCH()))30002,$B$6:$B$10,0)),«Missing»,«Found»)

Oppure, utilizza Un riferimento di cella per rendere la formula dinamica:

=IF(ISNA(MATCH()))G6,$B$6:$B$10,0)),«Missing»,«Found»)

√ Nota: i simboli del dollaro ($) indicano riferimenti assoluti, il che significa che l’intervallo_di_ricerca nella formula non cambierà quando sposti o copi la formula in altre celle. Tuttavia, non sono stati aggiunti simboli del dollaro al valore_da_cercare, poiché si desidera che rimanga dinamico. Dopo aver inserito la formula, trascina il quadratino di riempimento verso il basso per applicarla alle celle sottostanti.

Spiegazione della formula

Di seguito viene utilizzata come esempio la formula seguente:

=IF()ISNA()MATCH(G8,$B$6:$B$10,0)),«Missing»,«Found»)

• MATCH(G8,$B$6:$B$10,0): Il tipo di corrispondenza 0 obbliga la funzione MATCH a restituire un valore numerico che indica la posizione della prima corrispondenza di 3004 — il valore contenuto nella cella G8 — all'interno dell'array $B$6:$B$10. Tuttavia, in questo caso, MATCH non è riuscita a trovare il valore nell'array di ricerca e restituirà pertanto l'errore #N/D.
• ISNA()MATCH(G8,$B$6:$B$10,0))=ISNA()#N/D):La funzione ISNA verifica se un valore corrisponde all’errore «#N/D». In tal caso, restituisce VERO; altrimenti, restituisce FALSO. Pertanto, questa formula ISNA restituirà VERO.
• SE()ISNA()MATCH(G8,$B$6:$B$10,0)),«Mancante»,«Trovato») = SE(VERO,«Mancante»,«Trovato»):La funzione SE restituisce «Mancante» se il risultato del confronto effettuato da ISNA e MATCH è VERO; in caso contrario, restituisce «Trovato». Di conseguenza, la formula restituirà Mancante.

Trova i valori mancanti con VLOOKUP, ISNA e IF

Per verificare se tutti i prodotti del tuo elenco sono presenti anche in quello del fornitore, puoi sostituire la funzione MATCH indicata sopra con VLOOKUP: funziona in modo analogo, restituendo l’errore #N/D quando un valore non è presente nell’altro elenco, ossia quando risulta mancante.

Sintassi generica

=IF(ISNA(VLOOKUP()))«lookup_value»,lookup_range,1,FALSE)),«Missing»,«Found»)

√ Nota: puoi sostituire «Mancante» e «Trovato» con qualsiasi valore desideri.

Per verificare se tutti i prodotti presenti nel tuo elenco esistono anche nell’elenco del fornitore, copia o inserisci la formula seguente nella cella H6 e premi Invioper ottenere il risultato:

=IF(ISNA(VLOOKUP()))30002,$B$6:$B$10,1,FALSO)),«Missing»,«Found»)

Oppure, utilizza Un riferimento di cella per rendere la formula dinamica:

=IF(ISNA(VLOOKUP()))G6,$B$6:$B$10,1,FALSO)),«Missing»,«Found»)

√ Nota: I simboli del dollaro ($) indicano riferimenti assoluti, il che significa che l’intervallo_di_ricercanella formula non cambierà quando sposti o copi la formula in altre celle. Tuttavia, non sono stati aggiunti simboli del dollaro al valore_da_cercarepoiché si desidera che sia dinamico. Dopo aver inserito la formula, trascina il quadratino di riempimento verso il basso per applicarla alle celle sottostanti.

Spiegazione della formula

Di seguito viene utilizzata come esempio la formula seguente:

=IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),«Missing»,«Found»)

• CERCA.VERT(G8,$B$6:$B$10,1,FALSO): Il parametro intervallo_cerca impostato su FALSO obbliga la funzione CERCA.VERT a cercare e restituire il valore che corrisponde esattamente a 3004, ossia il valore contenuto nella cella G8. Se il valore da cercare ()3004) è presente nella 1ª colonna dell'intervallo $B$6:$B$10, CERCA.VERT restituirà tale valore; in caso contrario, restituirà l'errore #N/D. In questo caso, poiché 3004 non è presente nell'intervallo, il risultato sarà #N/D.
• ISNA()CERCA.VERT(G8,$B$6:$B$10,1,FALSO))=ISNA()#N/D):La funzione ISNA verifica se un valore corrisponde all'errore «#N/D». In tal caso, restituisce VERO; altrimenti, restituisce FALSO. Pertanto, questa formula ISNA restituirà VERO.
• SE()ISNA()CERCA.VERT(G8,$B$6:$B$10,1,FALSO)),«Mancante»,«Trovato») = SE(VERO,«Mancante»,«Trovato»):La funzione SE restituisce «Mancante» se il confronto effettuato da ISNA e CERCA.VERT dà come risultato VERO; in caso contrario, restituisce «Trovato». Pertanto, la formula restituirà Mancante.

Trova i valori mancanti con COUNTIF e IF

Per verificare se tutti i prodotti presenti nel tuo elenco esistono anche nell’elenco del fornitore, puoi utilizzare una formula più semplice con le funzioni COUNTIF e IF. La formula sfrutta il fatto che Excel considera qualsiasi numero diverso da zero (0) come VERO. Pertanto, se un valore esiste in un altro elenco, la funzione COUNTIF restituirà il conteggio delle sue occorrenze in tale elenco e IF interpreterà tale numero come VERO; se invece il valore non esiste nell’elenco, COUNTIF restituirà 0 e IF lo interpreterà come FALSO.

Sintassi generica

=IF(COUNTIF())«lookup_range»,lookup_value),«Found»,«Missing»)

√ Nota: Puoi modificare i valori «Found» e «Missing» con quelli che preferisci.

Per verificare se tutti i prodotti presenti nel tuo elenco esistono anche nell’elenco del fornitore, copia o inserisci la formula seguente nella cella H6 e premi Invioper ottenere il risultato:

=IF(COUNTIF())$B$6:$B$10,30002),«Found»,«Missing»)

Oppure, utilizza Un riferimento di cella per rendere la formula dinamica:

=IF(COUNTIF())$B$6:$B$10,G6),«Found»,«Missing»)

√ Nota: I simboli del dollaro ($) indicano riferimenti assoluti, il che significa che l’intervallo_di_ricercanella formula non cambierà quando sposti o copi la formula in altre celle. Tuttavia, non sono stati aggiunti simboli del dollaro al valore_da_cercarepoiché si desidera che sia dinamico. Dopo aver inserito la formula, trascina il quadratino di riempimento verso il basso per applicarla alle celle sottostanti.

Spiegazione della formula

Di seguito viene utilizzata come esempio la formula seguente:

=IF()COUNTIF($B$6:$B$10,G8),«Found»,«Missing»)

• CONTA.SE($B$6:$B$10,G8): La funzione CONTA.SE conta quante volte 3004, il valore contenuto nella cella G8, compare nell'intervallo $B$6:$B$10. Dato che 3004 non è presente nell'intervallo, il risultato sarà 0.
• SE()CONTA.SE($B$6:$B$10,G8),«Trovato»,«Mancante») = SE(0,«Trovato»,«Mancante»): La funzione SE interpreta il valore 0 come FALSO. Di conseguenza, la formula restituisce Mancante, ovvero il valore da mostrare quando il primo argomento è FALSO.

Funzioni correlate

Formule correlate