La funzione CERCA.VERT di Excel viene utilizzata per trovare e restituire informazioni da una tabella di dati in base a un valore di ricerca scelto.
In genere, CERCA.VERT richiede che il valore di ricerca sia nella colonna più a sinistra della tabella di dati e che la funzione restituisca un altro campo di dati situato nella stessa riga a destra di questo valore.
Tuttavia, combinando VLOOKUP con la funzione CHOOSE, è possibile creare una formula di ricerca a sinistra che:
- Consente al valore di ricerca di essere da qualsiasi colonna nella tabella di dati
- Restituisce le informazioni che si trovano in qualsiasi colonna a sinistra del valore di ricerca
Nota: Queste istruzioni si applicano alle versioni di Excel 2019, 2016, 2013, 2010 ed Excel per Office 365.
01 del 05Esercitazione: utilizzo di VLOOKUP e SCEGLI le funzioni in una formula di ricerca a sinistra
Per creare la formula di ricerca a sinistra visualizzata nell'immagine di esempio, utilizzare la formula:
= CERCA.VERT ($ D $ 2, SCEGLI ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
In questo esempio, la formula consente di trovare la parte fornita dalle diverse società elencate nella colonna 3 della tabella dei dati.
Il lavoro della funzione CHOOSE nella formula è quello di ingannare VLOOKUP facendogli credere che la colonna 3 sia la colonna 1. Di conseguenza, il nome dell'azienda può essere utilizzato come valore di ricerca per trovare il nome della parte fornita da ciascuna società.
Inserisci i dati dell'esercitazione
- Inserisci l'intestazione Fornitore nella cella D1.
- Inserisci l'intestazione Parte nella cella E1.
- Inserisci la tabella dei dati visualizzati nell'immagine sopra nelle celle Da D4 a F9
- Righe 2 e 3 sono lasciati vuoti per soddisfare i criteri di ricerca e la formula di ricerca a sinistra creata durante questo tutorial
Aprire la finestra di dialogo VLOOKUP
Sebbene sia possibile digitare la formula direttamente nella cella F1 del foglio di lavoro, molte persone hanno difficoltà con la sintassi della formula.
In questo caso, è più sicuro utilizzare la finestra di dialogo CERCA.VERT. Quasi tutte le funzioni di Excel hanno una finestra di dialogo che consente di inserire ciascuno degli argomenti della funzione su una riga separata.
- Clicca sulla cella E2 del foglio di lavoro. E2 è la posizione in cui verranno visualizzati i risultati della formula di ricerca a sinistra.
- Clicca sul formule scheda del nastro.
- Clicca sul Ricerca e riferimento opzione nella barra multifunzione per aprire l'elenco a discesa della funzione.
- Clicca su VLOOKUP nell'elenco per visualizzare la finestra di dialogo della funzione.
Inserimento di argomenti nella finestra di dialogo VLOOKUP
Gli argomenti di una funzione sono i valori utilizzati dalla funzione per calcolare un risultato.
Nella finestra di dialogo di una funzione, il nome di ciascun argomento si trova su una riga separata seguita da un campo in cui immettere un valore.
Immettere i seguenti valori per ciascuno degli argomenti di VLOOKUP sulla riga corretta della finestra di dialogo, come mostrato nell'immagine di accompagnamento.
Il valore di ricerca
Il valore di ricerca è il campo di informazioni utilizzato per cercare l'array di tabelle. CERCA.VERT restituisce un altro campo di dati dalla stessa riga del valore di ricerca.
Questo esempio utilizza un riferimento di cella alla posizione in cui il nome dell'azienda verrà inserito nel foglio di lavoro. Il vantaggio di questo è che rende facile cambiare il nome della società senza modificare la formula.
- Clicca sul valore di ricerca linea nella finestra di dialogo.
- Clicca sulla cella D2 per aggiungere questo riferimento di cella al valore di ricerca linea.
- premi il F4 tasto sulla tastiera per rendere assoluto il riferimento alla cella - $ D 2 $.
Riferimenti di celle assolute
I riferimenti di cella assoluti vengono utilizzati per il valore di ricerca e gli argomenti dell'array di tabelle per evitare errori se la formula di ricerca viene copiata in altre celle del foglio di lavoro.
Immissione della funzione SCEGLI
L'argomento dell'array table è il blocco di dati contigui da cui vengono recuperate informazioni specifiche.
Solitamente, CERCA.VERT cerca solo a destra dell'argomento valore di ricerca per trovare i dati nell'array della tabella. Per farlo sembrare a sinistra, CERCA.VERT deve essere ingannato riorganizzando le colonne nell'array della tabella usando la funzione SCEGLI.
In questa formula, la funzione SCEGLI svolge due compiti:
- Crea un array di tabelle che ha solo due colonne di larghezza (colonne D e F).
- Cambia l'ordine da destra a sinistra delle colonne nell'array della tabella in modo tale che la colonna F sia la prima e la colonna D sia la seconda.
Immissione di funzioni
Quando si inseriscono le funzioni manualmente, ciascuno degli argomenti della funzione deve essere separato da una virgola.
- Nella finestra di dialogo della funzione CERCA.VERT, fare clic su Matrice di tabella linea.
- Digita il seguente SCEGLIERE funzione:SCEGLIERE ({1,2}, $ F: $ F, $ D: D $)
Numero indice colonna
Normalmente, il numero di indice della colonna indica quale colonna dell'array della tabella contiene i dati che si stanno cercando. Tuttavia, in questa formula, fa riferimento all'ordine delle colonne impostato dalla funzione SCEGLI.
La funzione CHOOSE crea un array di tabelle largo due colonne con la colonna F seguita per prima dalla colonna D. Poiché l'informazione ricercata - il nome della parte - si trova nella colonna D, il valore dell'argomento dell'indice della colonna deve essere impostato su 2.
- Clicca sul Col_index_num linea nella finestra di dialogo.
- Digitare un 2 in questa linea.
Ricerca gamma
L'argomento Range_lookup di VLOOKUP è un valore logico (solo TRUE o FALSE) che indica se si desidera che CERCA.VERT sia in grado di trovare una corrispondenza esatta o approssimativa con il valore di ricerca.
- Se VERO o se questo argomento è omesso, CERCA.VERT restituisce una corrispondenza esatta al valore Lookup o, se non viene trovata una corrispondenza esatta, CERCA.VERT restituisce il valore successivo più grande. Perché la formula faccia ciò, i dati nella prima colonna di Table_array devono essere ordinati in ordine crescente.
- Se FALSE, CERCA.VERT utilizza solo una corrispondenza esatta con il valore Lookup_. Se nella prima colonna di Table_array sono presenti due o più valori che corrispondono al valore di ricerca, viene utilizzato il primo valore trovato. Se non viene trovata una corrispondenza esatta, viene restituito un errore # N / D.
In questo tutorial, poiché stiamo cercando un nome di parte particolare, Range_lookup è impostato su falso in modo che solo le corrispondenze esatte vengano restituite dalla formula.
- Clicca sul range_lookup linea nella finestra di dialogo.
- Digita la parola falso in questa riga per indicare che vogliamo VLOOKUP per restituire una corrispondenza esatta per i dati che stiamo cercando.
- Clic ok per completare la formula di ricerca a sinistra e chiudere la finestra di dialogo.
- Poiché non abbiamo ancora inserito il nome dell'azienda nella cella D2, nella cella E2 appare un errore # N / A.
Restituzione dei dati con la formula di ricerca a sinistra
Per trovare quali aziende forniscono quali parti, digitare il nome di una società nella cella D2 e premere il ACCEDERE tasto sulla tastiera.
Il nome della parte viene visualizzato nella cella E2.
- Clicca sulla cella D2 nel tuo foglio di lavoro.
- genere Gadget Plus nella cella D2 e premere il ACCEDERE tasto sulla tastiera.
- Il testo "Gadget" - la parte fornita dalla società Gadget Plus - dovrebbe essere visualizzato nella cella E2.
Verificare ulteriormente la formula di ricerca immettendo altri nomi di società nella cella D2 e il nome della parte corrispondente dovrebbe apparire nella cella E2.
Se nella cella E2 viene visualizzato un messaggio di errore come # N / A, verificare gli errori di ortografia nella cella D2.
04 di 05Creazione di una matrice di tabelle a due colonne
La sintassi per la funzione CHOOSE è:
= SCEGLIERE (Numero_indice, Valore1, Valore2, … Valore254)
La funzione CHOOSE in genere restituisce un valore dall'elenco di valori (da Valore1 a Valore254) in base al numero di indice immesso.
Se il numero indice è 1, la funzione restituisce Valore1 dall'elenco; se il numero indice è 2, la funzione restituisce Valore2 dall'elenco e così via.
Quando vengono immessi più numeri indice, la funzione restituisce più valori nell'ordine desiderato. Ottenendo SCEGLIERE per restituire più valori viene fatto creando un array.
Per inserire una matrice, circondare i numeri immessi con parentesi graffe o parentesi graffe. Due numeri sono inseriti per il numero di indice:{ 1,2 }.
Va notato che SCEGLIERE non è limitato alla creazione di una tabella a due colonne. Includendo un numero aggiuntivo nell'array, ad esempio {1,2,3}, e un intervallo aggiuntivo nell'argomento value, CHOOSE crea una tabella a tre colonne.
Colonne aggiuntive consentono di restituire informazioni diverse con la formula di ricerca a sinistra modificando l'argomento del numero di indice della colonna di VLOOKUP sul numero della colonna contenente le informazioni desiderate.
05 del 05Modifica dell'ordine delle colonne con la funzione SCEGLI
Nella funzione SCEGLI utilizzata in questa formula:
SCEGLIERE ({1,2}, $ F: $ F, $ D: D $)
l'intervallo per la colonna F è elencato prima della colonna D.
Poiché la funzione CHOOSE imposta l'array di tabelle di VLOOKUP (l'origine dei dati per tale funzione), l'ordine delle colonne nella funzione CHOOSE viene passato a CERCA.VERT.
Per quanto riguarda VLOOKUP, l'array di tabelle ha solo due colonne di larghezza con la colonna F a sinistra e la colonna D a destra. Poiché la colonna F contiene il nome dell'azienda che vogliamo cercare, e poiché la colonna D contiene i nomi delle parti, CERCA.VERT può eseguire i suoi doveri di ricerca regolari nella ricerca dei dati che si trovano a sinistra del valore di ricerca.
Di conseguenza, CERCA.VERT può usare il nome dell'azienda per trovare la parte che forniscono.
