Molte persone cercano di utilizzare Excel come database. Sebbene possa funzionare come un database, alcune delle attività che sarebbero molto facili in un programma di database sono piuttosto complesse in Excel. Una di queste attività è la corrispondenza di due elenchi in base a un campo comune; questo può essere facilmente ottenuto utilizzando Excel CERCA.VERT. Troverai che la funzione CERCA.VERT è estremamente utile, quindi controlla un esempio di quando e come utilizzare questa funzione di seguito.
Supponiamo che la tua agenzia di viaggi ti invii un report di fine mese di tutti i luoghi che i tuoi dipendenti hanno viaggiato. Il rapporto utilizza i codici degli aeroporti invece dei nomi delle città. Sarebbe utile se potessi inserire facilmente il vero nome della città invece del solo codice.
Su Internet, trovi e importi un elenco che mostra il nome della città per ogni codice aeroporto.
Ma come si ottengono queste informazioni su ogni record nel report?
- Utilizzare la funzione CERCA.VERT. CERCA.VERT è l'acronimo di "Ricerca verticale". Può essere utilizzato ogni volta che si dispone di un elenco di dati con il campo chiave nella colonna più a sinistra.
- Inizia a digitare la funzione
=VLOOKUP(,. Digita Ctrl + A per ottenere aiuto con la funzione. - CERCA.VERT richiede quattro parametri. Il primo è il codice della città nel rapporto originale. In questo esempio, sarebbe la cella D4
- Il parametro successivo è l'intervallo con la tabella di ricerca. Evidenzia la gamma. Assicurati di usare F4 per rendere l'intervallo assoluto. (Un riferimento assoluto ha un segno di dollaro prima sia del numero di colonna che del numero di riga. Quando la formula viene copiata, il riferimento continuerà a puntare verso I3: J351.
- Il terzo parametro indica a Excel in quale colonna si trova il nome della città. Nell'intervallo I3: J351, il nome della città si trova nella colonna 2. Immettere 2 per questo parametro.
- Il quarto parametro indica a Excel se una corrispondenza "chiusa" è corretta. In questo caso, non lo è, quindi inserisci False.
- Fare clic su OK per completare la formula. Trascina la maniglia di riempimento per copiare la formula verso il basso.
- Poiché hai inserito con cura formule assolute, puoi copiare la colonna E nella colonna D per ottenere la città di destinazione. In questo caso, tutte le partenze sono dall'aeroporto internazionale Pearson di Toronto.
Sebbene questo esempio abbia funzionato perfettamente, quando i visualizzatori utilizzano CERCA.VERT, di solito significa che stanno abbinando elenchi provenienti da fonti diverse. Quando gli elenchi provengono da fonti diverse, possono sempre esserci sottili differenze che rendono difficili gli elenchi. Ecco tre esempi di cosa può andare storto e come correggerli.
-
Un elenco ha trattini e l'altro no. Usa la
=SUBSTITUTE()funzione per rimuovere i trattini. La prima volta che proverai CERCA.VERT, otterrai errori N / D.Per rimuovere i trattini con una formula, utilizza la formula SOSTITUISCI. Usa 3 argomenti. Il primo argomento è la cella contenente il valore. L'argomento successivo è il testo che desideri modificare. L'argomento finale è il testo sostitutivo. In questo caso, vuoi cambiare i trattini in niente, quindi la formula è
=SUBSTITUTE(A4,"-","").Puoi racchiudere quella funzione in CERCA.VERT per ottenere la descrizione.
-
Questo è sottile, ma molto comune. Un elenco ha uno spazio vuoto finale dopo la voce. Usa = TRIM () per rimuovere gli spazi in eccesso. Quando si immette inizialmente la formula, si scopre che tutte le risposte sono errori N / A. Sai per certo che i valori sono nell'elenco e tutto sembra a posto con la formula.
Una cosa standard da controllare è passare alla cella con il valore di ricerca. Premere F2 per mettere la cella in modalità Modifica. Una volta in modalità di modifica, puoi vedere che il cursore si trova a uno spazio di distanza dalla lettera finale. Ciò indica che è presente uno spazio finale nella voce.
Per risolvere il problema, utilizzare la funzione TRIM.
=TRIM(D4)rimuoverà gli spazi iniziali, finali e sostituirà eventuali spazi doppi interni con uno spazio singolo. In questo caso, TRIM funziona perfettamente per rimuovere lo spazio finale.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)è la formula. -
Ho menzionato un suggerimento bonus nelle note dello spettacolo: come sostituire il risultato # N / A per i valori mancanti con uno spazio. Se il valore di ricerca non è nella tabella di ricerca, CERCA.VERT restituirà un errore N / D.
Questa formula utilizza la
=ISNA()funzione per rilevare se il risultato della formula è un errore N / D. Se ottieni l'errore, il secondo argomento nella funzione SE dirà a Excel di inserire il testo che desideri.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
CERCA.VERT consente di risparmiare tempo quando si abbinano elenchi di dati. Prenditi il tempo per imparare l'uso di base e sarai in grado di svolgere attività molto più potenti in Excel.
