Excel VLOOKUP - Članki TechTV

Kazalo

Veliko ljudi poskuša uporabiti Excel kot zbirko podatkov. Čeprav lahko deluje kot baza podatkov, so nekatere naloge, ki bi bile v programu baz podatkov zelo enostavne, v Excelu dokaj zapletene. Ena od teh nalog je ujemanje dveh seznamov na podlagi skupnega polja; to je mogoče enostavno doseči z uporabo programa Excel VLOOKUP. Funkcija VLOOKUP se vam bo zdela zelo koristna, zato si oglejte primer, kdaj in kako uporabljati to funkcijo spodaj.

Recimo, da vam vaša turistična agencija pošlje mesečno poročilo o vseh krajih, ki so jih potovali vaši zaposleni. Poročilo namesto imen mest uporablja letališke kode. Koristno bi bilo, če bi namesto kode zlahka vpisali pravo ime mesta.

V internetu najdete in uvozite seznam z imenom mesta za vsako kodo letališča.

Kako pa dobite te podatke o vsakem zapisu v poročilu?

  1. Uporabite funkcijo VLOOKUP. VLOOKUP je kratica za „Vertical Lookup“. Kadar koli imate seznam podatkov s ključnim poljem v skrajnem levem stolpcu, ga lahko kadar koli uporabite.
  2. Začnite vnašati funkcije =VLOOKUP(. Za pomoč pri funkciji vnesite Ctrl + A.
  3. VLOOKUP potrebuje štiri parametre. Najprej je koda mesta v prvotnem poročilu. V tem primeru bi bila to celica D4
  4. Naslednji parameter je obseg z vašo iskalno tabelo. Označite obseg. Uporabite F4, da bo razpon absolutni. (Absolutna referenca ima znak za dolar pred številko stolpca in številko vrstice. Ko je formula kopirana, bo referenca še naprej usmerjena proti I3: J351.
  5. Tretji parameter pove Excelu, v katerem stolpcu je ime mesta. V območju I3: J351 je ime mesta v stolpcu 2. Vnesite 2 za ta parameter.
  6. Četrti parameter pove Excelu, ali je ujemanje »blizu« v redu. V tem primeru ni, zato vnesite False.
  7. Kliknite V redu, da dokončate formulo. Povlecite ročico za polnjenje, da kopirate formulo navzdol.
  8. Ker ste natančno vnesli absolutne formule, lahko stolpec E kopirate v stolpec D, da dobite ciljno mesto. V tem primeru so vsi odhodi z mednarodnega letališča Pearson v Torontu.

Čeprav se je ta primer odlično izkazal, ko gledalci uporabljajo VLOOKUP, to običajno pomeni, da se ujemajo s seznami, ki so prišli iz različnih virov. Kadar seznami prihajajo iz različnih virov, lahko vedno obstajajo neznatne razlike, zaradi katerih se seznami težko ujemajo. Tu so trije primeri, kaj lahko gre narobe in kako jih popraviti.

  1. Na enem seznamu so pomišljaji, na drugem pa ne. S =SUBSTITUTE()funkcijo odstranite pomišljaje. Ko prvič preizkusite VLOOKUP, se prikažejo napake N / A.

    Če želite odstraniti pomišljaje s formulo, uporabite formulo NAMESTA. Uporabite 3 argumente. Prvi argument je celica, ki vsebuje vrednost. Naslednji argument je besedilo, ki bi ga radi spremenili. Zadnji argument je nadomestno besedilo. V tem primeru želite pomišljaje spremeniti v nič, zato je formula =SUBSTITUTE(A4,"-","").

    To funkcijo lahko zavijete v VLOOKUP, da dobite opis.

  2. Ta je prefinjen, a zelo pogost. Na enem seznamu je po vnosu prazen presledek. Uporabite = TRIM (), da odstranite odvečne prostore. Ko na začetku vnesete formulo, ugotovite, da so vsi odgovori napake N / A. Zagotovo veste, da so vrednosti na seznamu in je s formulo vse videti v redu.

    Standardno je preveriti, če se premaknete v celico z vrednostjo iskanja. Pritisnite F2, da celico preklopite v način urejanja. Ko ste v načinu urejanja, lahko vidite, da se kazalka nahaja en presledek od zadnje črke. To pomeni, da je v vnosu zadnji prostor.

    Za rešitev težave uporabite funkcijo TRIM. =TRIM(D4)bo odstranil vodilne presledke, zadnje presledke in vse notranje dvojne presledke zamenjal z enim presledkom. V tem primeru TRIM popolnoma deluje, da odstrani zadnji prostor. =VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)je formula.

  3. V opombah o oddaji sem omenil bonus nasvet: kako zamenjati rezultat # N / A za manjkajoče vrednosti s praznim. Če vaše iskalne vrednosti ni v iskalni tabeli, bo VLOOKUP vrnil N / A napako.

    Ta formula uporablja =ISNA()funkcijo za zaznavanje, ali je rezultat formule napaka N / A. Če dobite napako, bo drugi argument funkcije IF naročil Excelu, naj vnese poljubno besedilo.

    =IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))

VLOOKUP vam omogoča prihranek časa pri ujemanju seznamov podatkov. Vzemite si čas za učenje osnovne uporabe in v Excelu boste lahko opravljali veliko močnejša opravila.

Zanimive Članki...