Paljud inimesed proovivad Exceli andmebaasina kasutada. Ehkki see võib töötada andmebaasina, on mõned andmebaasiprogrammis väga lihtsad ülesanded Excelis üsna keerukad. Üks nendest ülesannetest on kahe loendi sobitamine ühise välja põhjal; seda saab Exceli VLOOKUPi abil hõlpsasti saavutada. Funktsioon VLOOKUP on äärmiselt kasulik, nii et vaadake allpool näidet selle funktsiooni kasutamisest.
Oletame, et teie reisibüroo saadab teile kuu lõpuaruande kõigi teie töötajate käidud kohtade kohta. Aruandes kasutatakse linnanimede asemel lennujaamakoode. Oleks kasulik, kui saaksite lihtsalt koodi asemel sisestada tõelise linna nime.
Internetist leiate ja impordite loendi, mis näitab iga lennujaama koodi linna nime.
Aga kuidas saada seda teavet aruande iga kirje kohta?
- Kasutage funktsiooni VLOOKUP. VLOOKUP tähistab vertikaalset otsingut. Seda saab kasutada igal ajal, kui teil on kõige vasakpoolsemas veerus võtmeväljaga andmete loend.
- Alusta selle funktsiooni
=VLOOKUP(. Funktsiooni kohta abi saamiseks sisestage Ctrl + A. - VLOOKUP vajab nelja parameetrit. Esiteks on algses aruandes linnakood. Selles näites oleks see lahter D4
- Järgmine parameeter on vahemik teie otsingu tabeliga. Tõstke vahemik esile. Vahemiku absoluutseks muutmiseks kasutage kindlasti klahvi F4. (Absoluutsel viitel on nii veeru numbri kui ka rea numbri ees dollarimärk. Valemi kopeerimisel suunatakse viide jätkuvalt I3: J351 suunas.
- Kolmas parameeter ütleb Excelile, millises veerus on linna nimi leitud. Vahemikus I3: J351 on linna nimi veerus 2. Sisestage selle parameetri jaoks 2.
- Neljas parameeter ütleb Exceli jaoks, kas lähedane vaste on korras. Sel juhul see pole nii, nii et sisestage vale.
- Valemi täitmiseks klõpsake nuppu OK. Valemi alla kopeerimiseks lohistage täitekäepidet.
- Kuna sisestasite hoolikalt absoluutsed valemid, saate sihtlinna saamiseks kopeerida veeru E veergu D. Sel juhul on kõik väljumised Torontos asuvast Pearsoni rahvusvahelisest lennujaamast.
Kuigi see näide õnnestus suurepäraselt, tähendab see, et kui vaatajad kasutavad VLOOKUP-i, tähendab see tavaliselt seda, et nad sobivad kokku erinevatest allikatest pärit loenditega. Kui loendid pärinevad erinevatest allikatest, võib alati olla peent erinevusi, mis muudavad loendid raskesti sobitatavaks. Siin on kolm näidet, mis võivad valesti minna ja kuidas neid parandada.
-
Ühes loendis on kriipsud ja teises loendis mitte.
=SUBSTITUTE()Kriipsude eemaldamiseks kasutage funktsiooni. Esmakordsel VLOOKUP-i proovimisel ilmnevad vead puuduvad.Kriipsude eemaldamiseks valemiga kasutage valemit ASENDA. Kasutage 3 argumenti. Esimene argument on väärtust sisaldav lahter. Järgmine argument on tekst, mida soovite muuta. Viimane argument on asendustekst. Sellisel juhul soovite kriipsud tühjaks muuta, nii et valem on
=SUBSTITUTE(A4,"-","").Kirjelduse saamiseks saate selle funktsiooni VLOOKUP-i mähkida.
-
See on peen, kuid väga levinud. Ühes loendis on pärast sisestust tühi tühik. Liigsete tühikute eemaldamiseks kasutage = TRIM (). Kui valemi algselt sisestate, leiate, et kõik vastused on puuduvad. Teate kindlasti, et väärtused on loendis ja valemiga tundub kõik korras.
Üks kontrollitav asi on liikuda otsingu väärtusega lahtrisse. Lahtri muutmisrežiimi viimiseks vajutage klahvi F2. Redigeerimisrežiimis olles näete, et kursor asub lõpustähest ühe tühiku kaugusel. See näitab, et kirjes on tühik.
Probleemi lahendamiseks kasutage funktsiooni TRIM.
=TRIM(D4)eemaldab eesmised tühikud, tagumised tühikud ja asendab kõik sisemised topelt tühikud ühe tühikuga. Sellisel juhul töötab TRIM suurepäraselt tagumise ruumi eemaldamiseks.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)on valem. -
Mainisin saate märkustes boonusenõu: kuidas asendada puuduvate väärtuste tulemus # N / A tühjaga. Kui teie otsinguväärtust otsingutabelis pole, tagastab VLOOKUP tõrke N / A.
See valem kasutab
=ISNA()funktsiooni, et tuvastada, kas valemi tulemus on viga N / A. Kui saate vea, käsib IF-i 2. argument Excelis panna mis tahes soovitud teksti.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP võimaldab teil andmete loendite sobitamisel aega kokku hoida. Võtke aega põhikasutuse õppimiseks ja saate Excelis teha palju võimsamaid ülesandeid.
