CERCA.VERT tra due cartelle di lavoro: semplice guida passo dopo passo

Se stai lavorando con più cartelle di lavoro in Excel, potrebbe essere necessario utilizzare la funzione CERCA.VERT per recuperare i dati da una cartella di lavoro a un'altra.

La funzione VLOOKUP cerca un valore specifico in una tabella in una cartella di lavoro separata e restituisce un valore corrispondente dalla stessa riga. La sintassi deve includere un riferimento completo alla seconda cartella di lavoro e al nome del foglio di lavoro.

Ottenere i riferimenti corretti può essere difficile quando si utilizza la funzione per la prima volta. Questo articolo utilizza fogli di calcolo di esempio per mostrarti esattamente come inserire le formule.

Imparerai anche alcuni miglioramenti a VLOOKUP e diverse alternative alla funzione.

Iniziamo!

Sommario

• Cos'è CERCA.VERT?
• Come utilizzare CERCA.VERT tra due cartelle di lavoro
  • Dati di esempio
  • Passaggio 1: tenere aperte entrambe le cartelle di lavoro
  • Passaggio 2: inizia a inserire la formula CERCA.VERT
  • Passaggio 3: come fare riferimento alla seconda cartella di lavoro
• 3 errori comuni di VLOOKUP e come risolverli
  • 1. #N/D Errore
  • 2. #Rif! Errore numero colonna
  • 3. Errore tra virgolette e parentesi quadre
• 4 suggerimenti per la gestione di diverse cartelle di lavoro
• Come aggiornare automaticamente i dati
• Usi avanzati della funzione CERCA.VERT
• 2 principali alternative a CERCA.VERT
  • 1. PARTITA INDICE
  • 2. CERCA X
• Come utilizzare CERCA.VERT su più fogli
  • Passaggio 1: identificare i componenti
  • Passaggio 2: imposta i tuoi dati
  • Passaggio 3: utilizzare la funzione CERCA.VERT
  • (Facoltativo) Passaggio 4: risolvi gli errori #N/D
• Pensieri finali

Cos'è CERCA.VERT?

Supponiamo di avere una cartella di lavoro delle vendite di prodotti e una cartella di lavoro separata con i prezzi di ciascun prodotto. Si desidera accedere ai prezzi dalla seconda cartella di lavoro e utilizzarli nei calcoli nella prima cartella di lavoro.

VLOOKUP è una soluzione per questo compito. Il termine sta per “Vertical Lookup . “

È una funzione integrata che consente di cercare un valore specifico in una colonna e restituire un valore corrispondente in un'altra colonna in base alla posizione.

Può essere utilizzato con:

• un unico foglio di calcolo

• più fogli nella stessa cartella di lavoro

• due cartelle di lavoro di Excel

Se non hai la complessità aggiuntiva di più cartelle di lavoro, dai un'occhiata a questo tutorial sul confronto di due colonne in Excel usando VLOOKUP .

Come utilizzare CERCA.VERT tra due cartelle di lavoro

La funzione VLOOKUP è particolarmente utile quando è necessario cercare dati in più fogli di lavoro o cartelle di lavoro.

I tre passaggi principali per farlo sono:

1. Apri entrambe le cartelle di lavoro.

2. Immettere la formula CERCA.VERT nella prima cartella di lavoro di Excel.

3. Fornire un riferimento completo alla seconda cartella di lavoro.

Dati di esempio

Questo semplice tutorial funziona attraverso esempi basati su dati di esempio. Per proseguire, crea una nuova cartella di lavoro chiamata "Vendite" e inserisci questi dati nelle colonne A e B:

• Mele, 40

• Arance, 30

• Pere, 70

• Banane, 20

Aggiungi una terza colonna per contenere le informazioni sul prezzo:

Crea una seconda cartella di lavoro di Excel chiamata "Prodotto" e rinomina Foglio1 come "Prezzi". Inserisci questi dati nella colonna A e nella colonna B:

• Mele, 1,50

• Arance, 2.30

• Pere, 3.60

• Banane, 2.10

I dati hanno questo aspetto:

Per calcolare le entrate per le vendite nel primo foglio di calcolo, dobbiamo cercare il prezzo nel secondo foglio di calcolo. CERCA.VERT in soccorso!

Passaggio 1: tenere aperte entrambe le cartelle di lavoro

Entrambe le cartelle di lavoro devono essere aperte quando si utilizza la formula CERCA.VERT tra due cartelle di lavoro. Se la seconda cartella di lavoro è chiusa, la funzione restituirà un errore.

Puoi aprirli separatamente o utilizzare il comando "Apri" in Excel per aprirli entrambi contemporaneamente. Esistono due modi per aprire una cartella di lavoro:

• Vai alla scheda File e seleziona "Apri".

• Usa la scorciatoia da tastiera "Ctrl + O" .

Dopo aver aperto entrambe le cartelle di lavoro, puoi passare da una all'altra facendo clic sulla scheda appropriata nella parte inferiore della finestra di Excel.

Passaggio 2: inizia a inserire la formula CERCA.VERT

Ecco la sintassi di base della funzione CERCA.VERT:

=CERCA.VERT(valore_ricerca, matrice_tabella, numero_indice_col, [ricerca_intervallo])

• lookup_value : il valore che vuoi cercare nell'altra cartella di lavoro.

• matrice_tabella : l'intervallo di celle nell'altra cartella di lavoro che contiene i dati che si desidera recuperare.

• col_index_num : il numero di colonna nell'intervallo che contiene i dati che si desidera recuperare.

• range_lookup : quest'ultimo parametro è facoltativo. Se lo imposti su TRUE o lo ometti, CERCA.VERT cercherà una corrispondenza approssimativa. Se lo imposti su FALSE, VLOOKUP cercherà solo una corrispondenza esatta.

Supponiamo che tu voglia cercare il prezzo delle mele nella seconda cartella di lavoro dei nostri dati di esempio. Ecco come si scompone la formula:

• Il lookup_value si trova nella cella A2 della prima cartella di lavoro.

• La matrice della tabella si trova nell'intervallo di celle A1:B5 della seconda cartella di lavoro.

• Il col_index_num è la seconda colonna che contiene il prezzo.

La parte più complicata della formula CERCA.VERT fa riferimento correttamente alla seconda cartella di lavoro.

Passaggio 3: come fare riferimento alla seconda cartella di lavoro

Per completare la formula CERCA.VERT per due cartelle di lavoro, dovrai utilizzare un riferimento completo che includa questi dettagli della seconda cartella di lavoro:

• nome della cartella di lavoro

• nome foglio

• intervallo di celle

Ciò garantisce che Excel sappia esattamente dove trovare i dati che stai cercando. Il riferimento completo viene utilizzato per l'argomento matrice_tabella della funzione CERCA.VERT.

Questi sono i valori per i dati di esempio:

• nome della cartella di lavoro: Product.xlsx

• nome foglio: Prezzi

• intervallo di celle: $A1:$B5

Per cercare il prezzo del primo articolo, inserisci la seguente formula nella cella C2 della prima cartella di lavoro:

=CERCA.VERT(A2; [Prodotto.xlsx]Prezzi!$A$1:$B$5; 2, FALSO)

Questa immagine mostra la funzione in azione nella barra della formula per la prima cella:

Si noti che il nome della cartella di lavoro è racchiuso tra parentesi quadre e il nome del foglio è seguito da un punto esclamativo.

Se il nome del file Excel contiene uno spazio o caratteri speciali, è necessario racchiudere l'intera cartella di lavoro e il foglio tra virgolette singole.

La formula utilizza valori assoluti per l'intervallo di celle (il prefisso $). Ciò consente di copiare la formula nel resto delle righe facendo clic sulla maniglia di riempimento.

Ecco i risultati di CERCA.VERT:

E questo è tutto! Ora sai come utilizzare VLOOKUP in modo efficace. Per aiutarti in caso di errori, abbiamo raccolto tre degli errori VLOOKUP più comuni nella sezione successiva.

3 errori comuni di VLOOKUP e come risolverli

Ora che hai imparato a utilizzare CERCA.VERT da un'altra cartella di lavoro, potresti riscontrare alcuni errori comuni.

Ecco i tre errori più frequenti e come risolverli.

1. #N/D Errore

L'errore #N/D si verifica quando il valore di ricerca non viene trovato nell'array della tabella. Per correggere questo errore, puoi provare quanto segue:

• Controllare l'ortografia del valore di ricerca.

• Assicurarsi che il valore di ricerca sia nella prima colonna dell'array della tabella.

• Verificare che il valore di ricerca non sia formattato come testo nei due fogli.

• Assicurarsi che l'argomento range_lookup sia impostato su FALSE per trovare una corrispondenza esatta.

L'esempio precedente non è riuscito a trovare un valore corrispondente per Ciliegie nella seconda cartella di lavoro.

2. #Rif! Errore numero colonna

Il # Rif! L'errore si verifica quando l'argomento col_index_num è maggiore del numero di colonne nell'array della tabella.

Per correggere questo errore, verificare che l'argomento col_index_num sia minore o uguale al numero di colonne nella tabella di origine.

Nell'immagine seguente, col_index_num è stato impostato su 3 ma ci sono solo due colonne nell'intervallo di riferimento.

3. Errore tra virgolette e parentesi quadre

L'errore di virgolette e parentesi quadre si verifica quando si esegue CERCA.VERT con un valore contenente virgolette o parentesi quadre.

Per correggere questo errore, puoi provare quanto segue:

• Usa le doppie virgolette attorno al valore di ricerca.

• Utilizzare la funzione CHAR(34) per inserire virgolette doppie.

• Utilizzare la funzione IFERROR per restituire una cella vuota se il valore di ricerca contiene virgolette o parentesi quadre.

Una volta che hai familiarità con questi errori, correggerli è semplice! Per aiutarti ulteriormente a diventare un professionista VLOOKUP, abbiamo fornito quattro suggerimenti per la gestione di diverse cartelle di lavoro nella sezione successiva.

4 suggerimenti per la gestione di diverse cartelle di lavoro

Quando si lavora con più cartelle di lavoro, è importante tenere traccia di quale cartella di lavoro contiene quali dati.

Ecco i nostri quattro migliori consigli per mantenere la tua sanità mentale:

1. Usa le convenzioni di denominazione per aiutarti a ricordare quali fogli di calcolo Excel contengono quali dati.

2. Utilizza riferimenti assoluti nelle tue formule per assicurarti che l'intervallo di ricerca faccia riferimento alle celle corrette anche se sposti o copi la formula in un'altra posizione o foglio Excel.

3. Utilizzare la funzione IFERROR per gestire eventuali errori #N/D che possono verificarsi se i valori di ricerca non vengono trovati nei dati di origine.

4. Prendi in considerazione la possibilità di bloccare le celle nella cartella di lavoro di riferimento in modo che non possano essere sovrascritte.

Come aggiornare automaticamente i dati

Quando si lavora con più file Excel, l' aggiornamento manuale dei dati può richiedere molto tempo .

Se desideri che la tua formula CERCA.VERT si aggiorni automaticamente quando aggiungi o rimuovi dati dalle altre cartelle di lavoro, puoi utilizzare la funzione INDIRETTO.

Ecco come:

• Nella formula CERCA.VERT, sostituisci l'argomento matrice tabella con la funzione INDIRETTO.

• Nella funzione INDIRETTO utilizzare un riferimento alla cella che contiene la matrice della tabella nell'altra cartella di lavoro.

Per raggiungere questo obiettivo con le nostre cartelle di lavoro di esempio, segui questi passaggi.

1. Utilizzare una cella nella colonna D per inserire il riferimento al foglio "Prezzi" della cartella di lavoro "Prodotto" e l'intervallo di celle A1:B5 in questo modo: [Prodotto.xlsx]Prezzi!A:B

2. Nella cella C2 della cartella di lavoro "Vendite", utilizzare questa formula: =CERCA.VERT(A2, INDIRETTO($D$1), 2, FALSO)

3. Copia la formula verso il basso (i riferimenti assoluti assicurano che la cella con il foglio di calcolo venga preservata).

Queste azioni assicurano che le modifiche nel foglio originale vengano aggiornate automaticamente nelle celle collegate.

Questa immagine mostra la funzione INDIRETTO combinata con CERCA.VERT:

Si noti che questo esempio utilizza l'intervallo "A:B" per fare riferimento alle due colonne. Ciò significa che quando aggiungi più prodotti e prezzi, la formula continuerà a funzionare nella cartella di lavoro corrente.

Ora che hai avuto un assaggio del potere di VLOOKUP, diamo un'occhiata ad alcuni usi avanzati della funzione nella sezione!

Usi avanzati della funzione CERCA.VERT

Gli esempi in questo articolo restituiscono una singola corrispondenza per ogni valore di ricerca. Se vuoi lavorare con più corrispondenze da una cartella di lavoro diversa, puoi invece utilizzare una formula di matrice.

Puoi anche combinare CERCA.VERT con altre funzioni. Ad esempio, puoi incorporarlo all'interno di una funzione IF per ulteriori analisi complesse.

L'analisi complessa include tecniche come test chi-quadrato e test T per campioni appaiati. Ecco un esempio di utilizzo del test chi-quadrato in Excel:

Gli utenti avanzati sfruttano VLOOKUP per confrontare i set di dati e identificare le mancate corrispondenze. Troverai altri modi in questo articolo per trovare discrepanze in Excel .

Ma cosa succede se non si desidera utilizzare CERCA.VERT per qualsiasi motivo? Sei fortunato! Ci sono alcune alternative, che è ciò che tratteremo nella prossima sezione.

2 principali alternative a CERCA.VERT

Quando si tratta di cercare valori in fogli diversi, CERCA.VERT è spesso la funzione di riferimento. Tuttavia, ci sono alcune alternative che possono essere utili in determinate situazioni.

I due più comuni sono:

• PARTITA INDICE

• CERCA X

1. PARTITA INDICE

INDEX e MATCH è una potente combinazione di due funzioni che possono essere utilizzate per cercare i valori in una tabella. A differenza di VLOOKUP, che richiede che il valore di ricerca si trovi nella colonna più a sinistra della tabella, INDEX MATCH può cercare i valori in qualsiasi colonna.

Ecco come funziona:

1. La funzione INDICE restituisce un valore da un intervallo specificato in base a un numero di riga e colonna.

2. La funzione CONFRONTA restituisce la posizione di un valore di ricerca in un intervallo specificato.

Per utilizzare INDEX MATCH, è necessario specificare l'intervallo in cui si desidera cercare, la riga in cui si desidera trovare il valore di ricerca e la colonna in cui si desidera restituire il risultato.

Ecco una formula di esempio:

=INDICE(IntervalloTabella,CONFRONTA(ValoreRicerca,IntervalloRicerca,0),IndiceColonna)

Per utilizzare questo con i fogli di calcolo di esempio, questa è la formula:

=INDICE([Prodotto.xlsx]Prezzi!$B$1:$B$5, CONFRONTA(A2, [Prodotto.xlsx]Prezzi!$A$1:$A$5, 0))

Questo si scompone come:

• La funzione CONFRONTA cerca il valore nella cella A2 (nome prodotto) all'interno dell'intervallo A1:A5 nel foglio "Prezzi" della cartella di lavoro "Prodotto".

• La funzione INDICE restituisce un valore compreso nell'intervallo B1:B5 nel foglio "Prezzi" della cartella di lavoro "Prodotto", in base al numero di riga fornito dalla funzione CONFRONTA.

Questa immagine mostra la formula in azione:

2. CERCA X

CERCA.X è una funzione più recente introdotta in Excel 365 . È simile a VLOOKUP, ma con alcune funzionalità aggiuntive.

Uno dei maggiori vantaggi di CERCA.X è che può cercare i valori a sinistra della colonna di ricerca.

Ecco come funziona:

1. La funzione CERCA.X restituisce un valore da un intervallo specificato in base a un valore di ricerca.

2. È possibile specificare se si desidera una corrispondenza esatta o una corrispondenza approssimativa.

3. Puoi anche specificare cosa restituire se non viene trovata alcuna corrispondenza.

Per utilizzare CERCA.X, è necessario specificare il valore di ricerca, l'intervallo in cui eseguire la ricerca e la colonna in cui si desidera restituire il risultato.

Ecco una formula di esempio:

=CERCA.X(ValoreRicerca;IntervalloRicerca;IntervalloRitorno;”Non trovato”,0)

Per utilizzare questo con i fogli di calcolo di esempio, questa è la formula:

=CERCA.X(A2; [Prodotto.xlsx]Prezzi!$A$1:$A$5; [Prodotto.xlsx]Prezzi!$B$1:$B$5)

Questo si scompone come:

• A2: Il valore che vuoi cercare (nome del prodotto).

• [Prodotto.xlsx]Prezzi!$A$1:$A$5: l'intervallo in cui si desidera cercare il valore di ricerca (nomi dei prodotti nel foglio "Prezzi" della cartella di lavoro "Prodotto").

• [Prodotto.xlsx]Prezzi!$B$1:$B$5: l'intervallo che contiene i valori restituiti (prezzi nel foglio "Prezzi" della cartella di lavoro "Prodotto").

Questa è la formula in azione:

Come utilizzare CERCA.VERT su più fogli

Se è necessario eseguire una CERCA.VERT tra due cartelle di lavoro, potrebbe essere necessario utilizzare anche CERCA.VERT su più fogli.

Fortunatamente, il processo per farlo è molto simile all'esecuzione di una CERCA.VERT tra due cartelle di lavoro.

Ci sono quattro passaggi.

Passaggio 1: identificare i componenti

Anziché includere l'array della tabella come faresti per un foglio, dovrai includere anche il nome del foglio.

Ad esempio, se si desidera cercare un valore nell'intervallo A2:B6 nel foglio Jan della cartella di lavoro Sales_reports.xlsx, la formula sarà simile a questa:

=CERCA.VERT(A2;[Rapporti_vendite.xlsx]Gen!$A$2:$B$6;2;FALSO)

Passaggio 2: imposta i tuoi dati

Assicurati che i tuoi dati siano organizzati in modo da facilitare la consultazione su più fogli.

Potresti prendere in considerazione la possibilità di creare schede diverse per dati di riferimento specifici.

Passaggio 3: utilizzare la funzione CERCA.VERT

Una volta impostati i dati, puoi utilizzare la funzione CERCA.VERT per cercare e recuperare i dati da altri fogli.

Assicurati di includere il nome del foglio nella tua formula, come mostrato sopra.

(Facoltativo) Passaggio 4: risolvi gli errori #N/D

Se riscontri errori #N/D quando utilizzi CERCA.VERT su più fogli, ricontrolla la formula per assicurarti di aver fatto riferimento correttamente a tutti i fogli e gli intervalli necessari.

Potresti anche voler controllare che stai eseguendo una corrispondenza esatta, piuttosto che una ricerca per intervallo.

Pensieri finali

Hai imparato a utilizzare CERCA.VERT per cercare e recuperare dati su due fogli di calcolo. Questa funzione versatile semplifica la ricerca dei valori in una cartella di lavoro in base ai valori corrispondenti in un'altra.

La potenza della funzione VLOOKUP si estende ben oltre i singoli fogli di lavoro, raggiungendo diverse cartelle di lavoro per collegare e analizzare i dati.

Padroneggiare questa funzione è un passo significativo per diventare esperti in Excel, poiché apre nuove possibilità per la gestione e l'interpretazione dei dati.

Incorporando funzioni come INDIRECT, INDEX-MATCH o XLOOKUP, puoi migliorare ulteriormente la flessibilità e l'efficacia delle tue ricerche di dati.

Continua a esercitarti ed esplorare le vaste capacità di Excel; questa è solo la punta dell'iceberg. Buona caccia ai dati!