Applicazione e definizione di questa funzione di Excel
Il VLOOKUP è una funzione di Excel con cui l'utente può cercare e valutare il contenuto della tabella. Questa funzione è disponibile nelle versioni di Excel 2007 per Windows e Mac.
Cos'è il CERCA.VERT?
I possibili usi di CERCA.VERT sono da spiegare qui usando un esempio: In questo sei un grande appassionato di letteratura e hai quindi creato il tuo foglio di calcolo Excel in cui puoi ordinare con cura i libri che hai raccolto. Ogni opera è inserita con informazioni sulle seguenti categorie:
-
autore
-
titolo
-
Numero di pagina
-
Anno di pubblicazione
Ora vorresti dare a un amico un consiglio per un libro da portare con te al tuo prossimo incontro. Sfortunatamente, puoi pensare solo all'autore, non al titolo del libro. È qui che entra in gioco CERCA.VERT, in quanto può utilizzare questo valore di input per estrarre le informazioni che stai cercando in un colpo solo.
Come viene utilizzato VLOOKUP?
Prima ancora di pensare alla formulazione delle formule, occorre stabilire dove si troveranno successivamente il campo di input e i vari campi di output. Per fare ciò, ha senso creare una tabella separata che inizialmente è vuota e quindi lascia spazio alle informazioni menzionate. Se si progetta questa nuova tabella in base all'esempio della tabella esistente, si avrà in seguito un vantaggio in termini di risparmio di tempo.
Su questa base, la formula CERCA.VERT può essere creata manualmente o generata automaticamente da Excel. Per i principianti, vale la pena utilizzare quest'ultimo approccio per conoscere gradualmente la struttura e l'effetto della formula. Per fare ciò, nella scheda "Formule" è selezionato il pulsante "Funzione di inserimento". Il VLOOKUP è nascosto nella finestra che si apre. Dopo la conferma si apre nuovamente una finestra in cui è possibile compilare i quattro parametri della formula. Questi sono:
-
Criterio di ricerca
-
matrice
-
Indice di colonna
-
Riferimento_area
La bozza grezza della formula si presenta quindi così:
= CERCA.VERT (criterio di ricerca, matrice, indice di colonna, collegamento_intervallo)
e in una possibile applicazione come questa:
= CERCA.VERT (H3; A3: E40; 5)
Criterio di ricerca
Affinché la funzione sappia quale valore deve essere utilizzato come punto di partenza, la riga che è stata selezionata come campo di immissione due passaggi prima viene annotata nel campo "Criterio di ricerca". Nel nostro esempio, viene inserito il nome dell'autore del libro "Phillip Pulmann". Ciò rende la formula flessibile e non deve essere modificata di nuovo non appena il valore immesso cambia.
matrice
Il campo di immissione "Matrice" descrive la tabella in cui si trovano le informazioni da emettere. Questa speciale matrice contiene quindi anche le colonne per il titolo del libro, il numero di pagina e l'anno di pubblicazione.
La matrice viene selezionata completamente una volta senza i titoli dal margine in alto a sinistra al margine in basso a destra. In questo modo Excel sa quale contenuto deve essere preso in considerazione durante la valutazione.
Indice di colonna
Il campo di input per "l'indice di colonna" richiede all'utente di definire la colonna della matrice in cui è elencato solo il valore cercato. L'assegnazione delle colonne è numerata cronologicamente. Ciò significa che la prima colonna della tabella riceve il valore 1, la seconda il valore 2, ecc. Nel nostro esempio, ciò corrisponde all'indice di colonna 1 per l'autore, all'indice di colonna 2 per il titolo, all'indice di colonna 3 per il numero di pagina e indice di colonna 4 per l'anno di pubblicazione.
Per mantenere la tabella il più flessibile possibile, è possibile collegare l'intestazione della colonna al posto del numero. Ciò ha il vantaggio che la formula può essere trasferita senza problemi anche ad altre righe, poiché l'intestazione della colonna può essere adattata in modo flessibile di volta in volta.
Attenzione: Il VLOOKUP legge la matrice da sinistra a destra, motivo per cui l'indice di colonna deve essere posizionato a destra della colonna per il criterio di ricerca per essere preso in considerazione dalla funzione!
Riferimento_area
Il parametro "Range_Lookup" completa la formula VLOOKUP specificando la precisione con cui viene valutata la tabella. Tuttavia, differisce dai componenti della formula precedentemente menzionati perché è facoltativo. Se viene immesso il valore 0 per "errato", Excel cerca solo il valore specificato come criterio di ricerca. Con il valore 1 per "vero", invece, la ricerca continua per valori ovvi se non è stato possibile trovare il valore esatto.
La specifica di questo parametro è facoltativa perché il valore 1 è impostato per impostazione predefinita. Questa impostazione sarà utile in seguito nella CERCA.VERT avanzata con più criteri di ricerca.
la fusione
Non appena tutti i parametri necessari sono stati impostati, VLOOKUP può essere utilizzato. Dopo aver inserito il criterio di ricerca e confermato la funzione, il valore che stai cercando appare nella riga che è stata definita come campo di output.
Nel nostro esempio viene ora visualizzato il titolo del libro “The Golden Compass”, che corrisponde all'autore inserito. Per scoprire rapidamente il numero di pagina e l'anno di pubblicazione, non è necessario fare altro che trascinare la formula CERCA.VERT esistente nelle celle successive. Questo è così facile perché l'indice di colonna di CERCA.VERT è stato collegato all'intestazione di colonna della prima tabella e anche la seconda tabella è strutturata nello stesso ordine.
Nel caso in cui le tabelle dovessero differire tra loro o si verificasse un errore nonostante tutto, la formula CERCA.VERT può essere modificata anche manualmente. Per fare ciò, la penultima cifra per l'indice di colonna deve essere abbinata alla colonna del nuovo valore da emettere.
CERCA.VERT con più criteri di ricerca
Abbastanza spesso capita che un solo criterio di ricerca non sia sufficiente per valutare con precisione una grande tabella Excel. Quindi ha senso eseguire VLOOKUP con diversi criteri di ricerca. Per fare ciò, la formula esistente deve essere integrata con una funzione SE aggiuntiva. In questo modo, durante la domanda, possono essere presi in considerazione fino a otto diversi criteri di ricerca.
CERCA.VERT in più fogli di calcolo Excel
Se il criterio di ricerca può essere trovato non solo in una tabella, ma eventualmente anche in un'altra, la formula CERCA.VERT può essere adattata di conseguenza. Per questo, sia una funzione if che una funzione ISERROR devono essere posizionate davanti alla formula esistente. Per questo sono necessari cinque parametri:
-
Criterio di ricerca
-
Matrice1 e Matrice 2
-
Indice di colonna1 e indice di colonna2
Il risultato è simile a questo:
= SE (VAL.ERRORE (CERCA.VERT (criterio di ricerca, matrice1, indice-colonna1, 0));
CERCA.VERT (criterio di ricerca; matrice2; indice colonna2,0); CERCA.VERT (criterio di ricerca; matrice1; indice colonna1;))
e in una possibile applicazione come questa:
= SE (VAL.ERRORE (CERCA.VERT (MI5, A5: SI9,2, 0)), CERCA.VERT (MI5, A13: SI17,2, 0), CERCA.VERT (MI5, A5: SI9,2, 0))
Il criterio di ricerca serve per inserire il valore che si vuole cercare nelle due tabelle. Matrix1 e Matrix2 definiscono le rispettive aree delle celle delle due tabelle. L'indice di colonna1 e l'indice di colonna2 vengono utilizzati per definire in modo più dettagliato quali colonne delle rispettive tabelle devono essere cercate.
Se il valore che stai cercando è presente in entrambe le tabelle, Excel restituirà il risultato dalla prima tabella. Tuttavia, se il valore non viene trovato in nessuna delle due tabelle, viene visualizzato un messaggio di errore. Il vantaggio della formula è che le due liste non devono avere la stessa struttura o avere la stessa dimensione.
Assegna valori alle categorie usando CERCA.VERT
Una funzione aggiuntiva di CERCA.VERT consente di dividere automaticamente i valori elencati in lettere e predicati di tua scelta. Nel nostro esempio precedente, dovrebbe essere inserita una colonna di tabella aggiuntiva per il tipo di libro. I libri con una lunghezza fino a 50 pagine dovrebbero rientrare nel genere del racconto, mentre i libri da 51 a 150 pagine sono assegnati alla novella e da 151 pagine al romanzo. Per renderlo possibile, non è richiesta alcuna formula aggiuntiva nel VLOOKUP, solo l'uso delle parentesi graffe "{}". La formula finita si presenta così:
= CERCA.VERT (B1; {1. "Racconto breve"; 51. "Novella"; 151. "Romanzo"}; 2)
Il contenuto delle parentesi graffe indica una matrice che definisce l'area di un rispettivo tipo di libro. L'assegnazione della lunghezza del lato al genere appropriato è quindi contenuta all'interno delle parentesi graffe. La formula utilizza coppie di valori, ciascuna separata da un punto. La matrice {1. "Racconto"; 51. "Novella"; 151. "Romanzo"} si legge come segue:
"Da 1 mostra un racconto, da 51 mostra una novella, da 151 mostra un romanzo."
Questa matrice può essere facilmente adattata a diversi compiti. Ciò riguarda da un lato la dimensione e il numero delle matrici, nonché la loro designazione. Quindi è possibile emettere stringhe o numeri come risultato invece di singole lettere. Tutto quello che devi fare è regolare le lettere nella formula.
CERCA.VERT su più fogli di lavoro
Un'altra funzione di CERCA.VERT consente ai suoi utenti di collegare contenuti che si trovano su fogli di calcolo diversi. Per il nostro esempio, questa opzione può essere utile quando le informazioni vengono prima ordinate in fogli di lavoro diversi e poi aggiornate in una tabella di riepilogo.
Immagina che, oltre ai tuoi libri, elenchi anche i tuoi film raccolti in un foglio di calcolo Excel. Quindi combini entrambe le raccolte in un'unica grande tabella.
Il vantaggio di questa procedura risiede non solo nell'ordine aumentato, ma anche nell'evitare potenziali errori. Se vuoi creare una nuova voce o aggiornarne una esistente, non devi cercare nella tabella grande, ma puoi invece accedere a quelle più piccole. I valori vengono poi trasferiti automaticamente alla tabella riepilogativa Excel. Ciò rende superflua la riscrittura nella grande tabella, che al massimo evita una mossa sfortunata e un successivo concatenamento di messaggi di errore.
Che aspetto ha la formula?
Questa funzione è resa nuovamente possibile inserendo un'altra formula. Mentre la ricerca con più criteri richiedeva una formula SE aggiuntiva, lavorare con più fogli di lavoro richiede una formula INDIRETTA. Ciò consente di specificare un intervallo da un altro foglio di calcolo per la matrice CERCA.VERT.
= CERCA.VERT (criterio di ricerca; INDIRETTO (matrice); indice di colonna; collegamento_intervallo)
Attenzione: Questa formula funziona solo se le singole tabelle nei vari fogli hanno gli stessi nomi delle intestazioni di colonna della tabella generale. Intere tabelle possono essere nominate nel "Campo Nome" in alto a sinistra sopra la griglia delle celle. Le tabelle già nominate possono essere visualizzate con la combinazione di tasti Ctrl + F3.
Gestire i messaggi di errore emergenti
Lavorare con tabelle Excel collegate può portare a problemi indesiderati. Ciò include in particolare l'output di valori errati. Nel caso in cui venga emesso il valore 0 errato, c'è un piccolo problema nelle impostazioni di Excel, che può essere risolto rapidamente.
Il messaggio di errore comune #NV, invece, è una funzione voluta di VLOOKUP, che indica all'utente che il valore richiesto non è disponibile. Questa nota può essere progettata in modo diverso con l'aiuto di una formula.
CERCA.VERT - una panoramica
VLOOKUP è un'utile funzione di Excel che può essere utilizzata per cercare e valutare le tabelle. I suoi vantaggi sono evidenti nella sua applicazione facile da usare e flessibile. In questo modo, chiunque lavori regolarmente con le tabelle Excel può beneficiare della funzione. Che si tratti del collezionista privato che crea i propri piccoli tavoli, o della grande azienda che elabora set di dati significativamente più consistenti.
Se, d'altra parte, hai ancora richieste senza risposta che VLOOKUP non è riuscito a soddisfare, puoi aspettarti un'opzione Excel aggiuntiva: Microsoft offre agli utenti di Excel 365 il nuovo XLOOKUP dall'inizio del 2022-2023. Questo si basa sulle competenze di CERCA.VERT e le integra con funzioni aggiuntive, a volte anche più semplici. Pertanto, a questo punto si apre anche una nuova routine nella valutazione dei dati.
