Daudzi cilvēki mēģina izmantot programmu Excel kā datu bāzi. Lai gan tas var darboties kā datu bāze, daži no uzdevumiem, kas būtu ļoti viegli datu bāzes programmā, programmā Excel ir diezgan sarežģīti. Viens no šiem uzdevumiem ir divu sarakstu saskaņošana, pamatojoties uz kopēju lauku; to var viegli paveikt, izmantojot programmu Excel VLOOKUP. Funkcija VLOOKUP būs ļoti noderīga, tāpēc skatiet piemēru, kad un kā lietot šo funkciju.
Pieņemsim, ka jūsu ceļojumu aģentūra jums nosūta mēneša beigu pārskatu par visām vietām, kuras jūsu darbinieki ir apceļojuši. Pārskatā pilsētu nosaukumu vietā tiek izmantoti lidostas kodi. Būtu noderīgi, ja jūs vienkārši varētu ievadīt īsto pilsētas nosaukumu, nevis tikai kodu.
Internetā atrodat un importējat sarakstu ar katra lidostas koda pilsētas nosaukumu.
Bet kā jūs iegūstat šo informāciju par katru pārskata ierakstu?
- Izmantojiet funkciju VLOOKUP. VLOOKUP nozīmē “vertikālā meklēšana”. To var izmantot jebkurā laikā, ja jums ir datu saraksts ar atslēgas lauku kolonnas kreisajā pusē.
- Sāciet rakstīt funkciju
=VLOOKUP(,. Ierakstiet Ctrl + A, lai saņemtu palīdzību saistībā ar funkciju. - VLOOKUP nepieciešami četri parametri. Pirmais ir pilsētas kods sākotnējā ziņojumā. Šajā piemērā tā būtu šūna D4
- Nākamais parametrs ir diapazons ar jūsu uzmeklēšanas tabulu. Iezīmējiet diapazonu. Noteikti izmantojiet F4, lai diapazons būtu absolūts. (Absolūtajai atsaucei ir dolāra zīme gan pirms kolonnas numura, gan rindas numura. Kad formula ir nokopēta, atsauce turpinās norādīt uz I3: J351.
- 3. parametrs norāda programmai Excel, kurā kolonnā ir atrodams pilsētas nosaukums. I3: J351 diapazonā pilsētas nosaukums ir 2. slejā. Ievadiet 2 šim parametram.
- Ceturtais parametrs norāda programmai Excel, vai “aizvērt” atbilst. Šajā gadījumā tā nav, tāpēc ievadiet False.
- Noklikšķiniet uz Labi, lai aizpildītu formulu. Velciet aizpildīšanas rokturi, lai kopētu formulu uz leju.
- Tā kā jūs rūpīgi ievadījāt absolūtās formulas, varat nokopēt E kolonnu D slejā, lai iegūtu mērķa pilsētu. Šajā gadījumā visas izlidošanas notiek no Pīrsonas Starptautiskās lidostas Toronto.
Lai gan šis piemērs ir izdevies lieliski, kad skatītāji izmanto VLOOKUP, tas parasti nozīmē, ka viņi pieskaņo sarakstus, kas nāk no dažādiem avotiem. Ja saraksti nāk no dažādiem avotiem, vienmēr var būt smalkas atšķirības, kuru dēļ saraksti ir grūti saskaņojami. Šeit ir trīs piemēri tam, kas var noiet greizi un kā tos labot.
-
Vienā sarakstā ir domuzīmes, bet otrā sarakstā nav. Izmantojiet
=SUBSTITUTE()funkciju, lai noņemtu domuzīmes. Pirmo reizi izmēģinot VLOOKUP, jūs saņemsit kļūdas N / A.Lai noņemtu domuzīmes ar formulu, izmantojiet formulu SUBSTITŪTS. Izmantojiet 3 argumentus. Pirmais arguments ir šūna, kurā ir vērtība. Nākamais arguments ir teksts, kuru vēlaties mainīt. Pēdējais arguments ir aizstāšanas teksts. Šajā gadījumā jūs vēlaties mainīt domuzīmes uz neko, tāpēc formula ir
=SUBSTITUTE(A4,"-","").Jūs varat ietīt šo funkciju VLOOKUP, lai iegūtu aprakstu.
-
Šis ir smalks, bet ļoti izplatīts. Vienā sarakstā pēc ieraksta ir tukša vieta. Izmantojiet = TRIM (), lai noņemtu liekās atstarpes. Sākotnēji ievadot formulu, jūs atradīsit, ka visas atbildes nav kļūdas. Jūs droši zināt, ka vērtības ir sarakstā, un ar formulu viss izskatās labi.
Viena standarta pārbaudāmā lieta ir pārvietošanās uz šūnu ar uzmeklēšanas vērtību. Nospiediet F2, lai šūnu iestatītu rediģēšanas režīmā. Kad esat rediģēšanas režīmā, varat redzēt, ka kursors atrodas vienas atstarpes attālumā no pēdējā burta. Tas norāda, ka ierakstā ir atstarpe.
Lai atrisinātu problēmu, izmantojiet funkciju TRIM.
=TRIM(D4)noņems vadošās atstarpes, pēdējās atstarpes un aizstās visas iekšējās dubultās atstarpes ar vienu atstarpi. Šajā gadījumā TRIM lieliski darbojas, lai noņemtu aizmugures vietu.=VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)ir formula. -
Izrādes piezīmēs es pieminēju bonusa padomu: kā trūkstošo vērtību # N / A rezultātu aizstāt ar tukšu. Ja uzmeklēšanas vērtība nav uzmeklēšanas tabulā, funkcija VLOOKUP atgriezīs kļūdu N / A.
Šī formula izmanto
=ISNA()funkciju, lai noteiktu, vai formulas rezultāts ir kļūda N / A. Ja tiek parādīta kļūda, funkcijas IF otrais arguments liks Excel ievietot jebkuru vēlamo tekstu.=IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))
VLOOKUP ļauj ietaupīt laiku, saskaņojot datu sarakstus. Veltiet laiku, lai uzzinātu pamata lietojumu, un programmā Excel varēsiet veikt daudz jaudīgākus uzdevumus.
