Combinando ExcelVLOOKUP funzione con ilCOLONNA funzione possiamo creare una formula di ricerca che consente di restituire più valori da una singola riga di un database o una tabella di dati.
01 di 10Restituisce più valori con Excel CERCA.VERT
Seguendo i passaggi elencati di seguito crea la formula di ricerca vista nell'immagine sopra che restituirà più valori da un singolo record di dati.
La formula di ricerca richiede che il COLONNA la funzione è nidificata all'interno di VLOOKUP. L'annidamento di una funzione implica l'inserimento della seconda funzione come uno degli argomenti per la prima funzione.
In questo tutorial, il COLONNA la funzione sarà inserita come indice della colonna numero argomento per VLOOKUP. L'ultimo passaggio dell'esercitazione consiste nel copiare la formula di ricerca in colonne aggiuntive per recuperare valori aggiuntivi per la parte scelta.
Inserisci i dati dell'esercitazione
Il primo passo del tutorial è inserire i dati in un foglio di lavoro Excel. Per seguire i passaggi del tutorial inserisci i dati mostrati nell'immagine sopra nelle seguenti celle:
- Inserisci la fascia più alta di dati in celle D1 a G1.
- Inserire il secondo intervallo in celle D4 a G10.
Verranno inseriti i criteri di ricerca e la formula di ricerca creati durante questo tutorial riga 2 del foglio di lavoro.
Il tutorial non include la formattazione vista nell'immagine, ma ciò non influirà sul funzionamento della formula di ricerca. Informazioni sulle opzioni di formattazione simili a quelle viste sopra sono disponibili in questo tutorial di formattazione Excel di base.
03 di 10Creazione di un intervallo con nome per la tabella dati
Un intervallo denominato è un modo semplice per fare riferimento a un intervallo di dati in una formula. Piuttosto che digitare i riferimenti di cella per i dati, puoi semplicemente digitare il nome dell'intervallo.
Un secondo vantaggio per l'utilizzo di un intervallo denominato è che i riferimenti di cella per questo intervallo non cambiano mai anche quando la formula viene copiata in altre celle del foglio di lavoro. I nomi degli intervalli sono, quindi, un'alternativa all'utilizzo di riferimenti di celle assoluti per evitare errori durante la copia delle formule.
Il nome dell'intervallo non include le intestazioni o i nomi dei campi per i dati (riga 4) ma solo i dati stessi.
- Evidenziare celle D5 a G10 nel foglio di lavoro per selezionarli.
- Clicca sul Casella del nome situato sopra colonna A.
- genere tavolo nel Casella del nome.
- premi il accedere tasto sulla tastiera.
- Celle D5 a G10 ora hanno il nome dell'intervallo di tavolo. Useremo il nome per il VLOOKUP matrice di tabella argomento più avanti nel tutorial.
Apertura della finestra di dialogo VLOOKUP
Sebbene sia possibile digitare semplicemente la formula di ricerca direttamente in una cella di un foglio di lavoro, molte persone trovano difficile mantenere la sintassi corretta, soprattutto per una formula complessa come quella che stiamo usando in questo tutorial.
Un'alternativa, in questo caso, è usare il VLOOKUP la finestra di dialogo. Quasi tutte le funzioni di Excel hanno una finestra di dialogo che consente di inserire ciascuno degli argomenti della funzione su una riga separata.
Passi tutorial
- Clicca su cella E2 del foglio di lavoro: la posizione in cui verranno visualizzati i risultati della formula di ricerca bidimensionale.
- Clicca sul Scheda Formule del nastro.
- Clicca sul Ricerca e riferimento opzione nel nastro per aprire il menu a discesa della funzione.
- Clicca su VLOOKUP nella lista per aprire la finestra di dialogo della funzione.
Inserimento dell'argomento del valore di ricerca
Normalmente, il consultare valore corrisponde a un campo di dati nella prima colonna della tabella di dati. Nel nostro esempio, il consultare valore si riferisce al nome della parte hardware su cui vogliamo trovare le informazioni. I tipi di dati consentiti per consultare valore sono dati di testo, valori logici, numeri e riferimenti di celle.
Riferimenti di celle assolute
Normalmente, quando le formule vengono copiate in Excel, i riferimenti di cella cambiano per riflettere la loro nuova posizione. Se ciò accade, D2, il riferimento di cella per il consultare valore, cambierà quando la formula viene copiata creando errori in celle F2 e G2.
I riferimenti alle celle assolute non cambiano quando vengono copiate le formule.
Per prevenire gli errori, convertiremo il riferimento di cella D2 in un riferimento di cella assoluto. I riferimenti di cella assoluti vengono creati premendo il tasto F4 tasto sulla tastiera. In questo modo si aggiungono segni di dollaro attorno al riferimento di cella come $ D $ 2.
- Clicca sul valore di ricerca linea nella finestra di dialogo.
- Clicca su cellula D2 per aggiungere questo riferimento di cella al valore di ricerca line - questa è la cella in cui digiterà il nome della parte su cui stiamo cercando informazioni
- Senza spostare il punto di inserimento, premere il tasto F4 tasto sulla tastiera da convertire D2 nel riferimento di cella assoluto $ D $ 2.
- Lasciare il VLOOKUP finestra di dialogo della funzione aperta per il passaggio successivo nel tutorial.
Immissione dell'Array della tabella
L'array di tabelle è la tabella di dati che la formula di ricerca cerca per trovare le informazioni che vogliamo.La matrice della tabella deve contenere almeno due colonne di dati.
La prima colonna contiene l'argomento valore di ricerca (passaggio precedente nell'esercitazione), mentre la seconda colonna verrà ricercata dalla formula di ricerca per trovare le informazioni da noi specificate.
L'argomento dell'array table deve essere inserito come intervallo contenente i riferimenti di cella per la tabella di dati o come nome di intervallo.
- Clicca sul matrice di tabella linea nella finestra di dialogo.
- genere tavolo per inserire il nome dell'intervallo per questo argomento.
- Lasciare il VLOOKUP finestra di dialogo della funzione aperta per il passaggio successivo nel tutorial.
Annidamento della funzione COLUMN
Normalmente VLOOKUP restituisce solo i dati da una colonna di una tabella di dati e questa colonna è impostata da numero indice colonna discussione.
In questo esempio, tuttavia, abbiamo tre colonne per le quali desideriamo restituire i dati, quindi abbiamo bisogno di un modo per modificare facilmente il numero indice colonna senza modificare la nostra formula di ricerca - questo è dove il COLONNA la funzione entra
Inserendolo come il numero indice colonna argomento, cambierà quando viene copiata la formula di ricerca cella D2 a celle E2 e F2 più avanti nel tutorial.
Funzioni di nidificazione
Il COLONNA la funzione, quindi, agisce come VLOOKUP'S numero indice colonna discussione; questo si ottiene nidificando il COLONNA funzione all'interno di VLOOKUP nel Col_index_num linea della finestra di dialogo.
Immissione manuale della funzione COLUMN
Quando si annidano le funzioni, Excel non ci consente di aprire la finestra di dialogo della seconda funzione per inserire i suoi argomenti. Il COLONNA la funzione, quindi, deve essere inserita manualmente nel Col_index_num linea. Il COLONNA la funzione ha solo un argomento - il Riferimento argomento che è un riferimento di cella.
Scelta dell'argomento di riferimento della funzione COLUMN
Il COLONNA il lavoro della funzione è di restituire il numero della colonna data come Riferimento discussione. In altre parole, converte la lettera della colonna in un numero con colonna A essendo la prima colonna, colonna B il secondo e così via.
Dal primo campo di dati, quello che vogliamo restituito è il prezzo dell'articolo, che è nella seconda colonna della tabella dei dati. Possiamo scegliere il riferimento di cella per ogni cella in colonna B come il Riferimento Argomento per ottenere il numero 2 per il Col_index_num discussione.
- Nel VLOOKUP finestra di dialogo della funzione, fare clic su Col_index_num linea.
- Digita il nome della funzione colonna seguito da un parentesi rotonda aperta.
- Clicca su cellula B1 nel foglio di lavoro per inserire quel riferimento di cella come Riferimento discussione.
- Digitare un staffa rotonda di chiusura per completare il COLONNA funzione
- Lasciare il VLOOKUP finestra di dialogo della funzione aperta per il passaggio successivo nel tutorial.
Immissione dell'interrogazione Ricerca intervallo di VLOOKUP
VLOOKUP'S range_lookup argomento è un valore logico (VERO o FALSE) che indica se vuoi VLOOKUP per trovare una corrispondenza esatta o approssimativa con il Valore di ricerca.
- Se VERO o se questo argomento è omesso: VLOOKUP restituisce una corrispondenza esatta con Valore di ricercaOppure, se non viene trovata una corrispondenza esatta, VLOOKUP restituisce il successivo valore più grande. Per la formula per fare ciò, i dati nella prima colonna di Matrice di tabella deve essere ordinato in ordine crescente.
- Se FALSE: VLOOKUP userà solo una corrispondenza esatta con il Valore di ricerca. Se ci sono due o più valori nella prima colonna di Matrice di tabella che corrisponde al valore di ricerca, viene utilizzato il primo valore trovato. Se non viene trovata una corrispondenza esatta, a #N / A errore viene restituito.
In questo tutorial, poiché stiamo cercando informazioni specifiche su un particolare articolo hardware, imposteremo range_lookup uguale a falso.
- Clicca sul range_lookup linea nella finestra di dialogo.
- Digita la parola falso in questa linea per indicare che vogliamo VLOOKUP per restituire una corrispondenza esatta per i dati che stiamo cercando.
- Clic ok per completare la formula di ricerca e chiudere la finestra di dialogo.
- Poiché non abbiamo ancora inserito i criteri di ricerca in cellula D2 un # N / A errore sarà presente a cella E2.
- Questo errore verrà corretto quando aggiungeremo i criteri di ricerca nell'ultimo passaggio del tutorial.
Copia della formula di ricerca
La formula di ricerca ha lo scopo di recuperare i dati da più colonne della tabella di dati contemporaneamente. Per fare ciò, la formula di ricerca deve risiedere in tutti i campi da cui vogliamo le informazioni.
In questo tutorial vogliamo che recuperi i dati da colonne 2, 3, e 4 della tabella dei dati - questo è il prezzo, il numero di parte e il nome del fornitore quando inseriamo un nome parziale come Valore di ricerca.
Poiché i dati sono disposti in uno schema regolare nel foglio di lavoro, possiamo copiare la formula di ricerca in cella E2 a celle F2 e G2.
Quando la formula viene copiata, Excel aggiornerà il relativo riferimento di cella nel file COLONNA funzione (cellula B1) per riflettere la nuova posizione della formula.
Inoltre, Excel non cambia il riferimento assoluto alle celle $ D $ 2 e l'intervallo denominato tavolo come viene copiata la formula.
C'è più di un modo per copiare i dati in Excel, ma probabilmente il modo più semplice è usare il Riempi la maniglia.
- Clicca su cella E2, dove si trova la formula di ricerca, per renderla la cella attiva.
- Posiziona il puntatore del mouse sopra il quadrato nero nell'angolo in basso a destra - il puntatore diventerà un segno più per indicare la maniglia di riempimento.
- Fare clic con il tasto sinistro del mouse e trascina la maniglia di riempimento attraverso a cella G2.
- pubblicazione il pulsante del mouse e cella F3 dovrebbe contenere la formula di ricerca bidimensionale.
- Se fatto correttamente, celle F2 e G2 dovrebbe ora contenere anche il #N / A errore che è presente in cella E2.
Immissione dei criteri di ricerca
Una volta che la formula di ricerca è stata copiata nelle celle richieste, può essere utilizzata per recuperare le informazioni dalla tabella dei dati. Per fare ciò, digitare il nome dell'oggetto desideri recuperare nel Valore di ricerca cellula (D2) e premere il tasto accedere tasto sulla tastiera.
Una volta terminato, ogni cella contenente la formula di ricerca dovrebbe contenere una parte di dati diversa sull'elemento hardware che si sta cercando.
- Clicca su cella D2 nel foglio di lavoro.
- genere widget in cella D2 e premere il accedere tasto sulla tastiera.
- Le seguenti informazioni dovrebbero essere visualizzate in celle E2 a G2.
- E2: $ 14,76 - il prezzo di un widget
- F2: PN-98769 - il numero di parte per un widget
- G2: Widgets Inc. - il nome del fornitore per i widget
- Prova il VLOOKUP formula array ulteriormente digitando il nome di altre parti in cella D2 e osservando i risultati in celle E2 a G2
