Al die geheime van die Excel VLOOKUP-funksie om data in 'n tabel te vind en dit in 'n ander te onttrek

2024 Outeur: Malcolm Clapton | [email protected]. Laas verander: 2023-12-17 03:44

Nadat u die artikel gelees het, sal u nie net leer hoe om data in 'n Excel-sigblad te vind en dit in 'n ander te onttrek nie, maar ook tegnieke wat saam met die VLOOKUP-funksie gebruik kan word.

Wanneer u in Excel werk, is dit baie dikwels nodig om data in een tabel te vind en dit in 'n ander te onttrek. As jy nog nie weet hoe om dit te doen nie, sal jy, nadat jy die artikel gelees het, nie net leer hoe om dit te doen nie, maar ook uitvind onder watter omstandighede jy die maksimum werkverrigting uit die stelsel kan druk. Die meeste van die baie effektiewe tegnieke wat in samewerking met die VLOOKUP-funksie gebruik moet word, word oorweeg.

Selfs as jy die VLOOKUP-funksie al jare gebruik, sal hierdie artikel met 'n hoë mate van waarskynlikheid vir jou nuttig wees en jou nie onverskillig laat nie. As 'n IT-spesialis, en dan 'n hoof in IT, gebruik ek byvoorbeeld VLOOKUP al 15 jaar, maar ek het nou eers reggekry om al die nuanses te hanteer, toe ek begin om mense Excel op 'n professionele basis te leer.

VLOOKUP is 'n afkorting vir vvertikaal NSinspeksie. Net so, VLOOKUP - Vertikale OPSOEK. Die einste naam van die funksie dui vir ons daarop dat dit in die rye van die tabel soek (vertikaal - herhaal oor die rye en maak die kolom reg), en nie in die kolomme nie (horisontaal - herhaal oor die kolomme en maak die ry reg). Daar moet kennis geneem word dat VLOOKUP 'n suster het - 'n lelike eendjie wat nooit 'n swaan sal word nie - dit is die HLOOKUP-funksie. HLOOKUP, in teenstelling met VLOOKUP, voer horisontale soektogte uit, maar die konsep van Excel (en inderdaad die konsep van data-organisasie) impliseer dat jou tabelle minder kolomme en baie meer rye het. Daarom moet ons baie keer meer gereeld volgens rye soek as volgens kolomme. As jy die HLO-funksie te dikwels in Excel gebruik, dan is dit waarskynlik dat jy nie iets in hierdie lewe verstaan het nie.

Sintaksis

Die VLOOKUP-funksie het vier parameters:

= VLOOKUP (;; [;]), hier:

- die verlangde waarde (selde) of 'n verwysing na 'n sel wat die verlangde waarde bevat (die oorgrote meerderheid gevalle);

- verwysing na 'n reeks selle (tweedimensionele skikking), in die EERSTE (!) kolom waarvan die parameterwaarde gesoek sal word;

- kolomnommer in die reeks waaruit die waarde teruggestuur sal word;

- dit is 'n baie belangrike parameter wat die vraag beantwoord of die eerste kolom van die reeks in stygende volgorde gesorteer is. As die skikking gesorteer is, spesifiseer ons die waarde WAAR of 1, anders - ONWAAR of 0. As hierdie parameter weggelaat word, is dit verstek na 1.

Ek wed dat baie van diegene wat die VLOOKUP ken as afskilferig funksioneer, nadat hulle die beskrywing van die vierde parameter gelees het, hulle dalk ongemaklik kan voel, aangesien hulle gewoond is om dit in 'n effens ander vorm te sien: gewoonlik praat hulle van 'n presiese pasmaat wanneer soek (ONWAAR of 0) of 'n reeksskandering (WAAR of 1).

Nou moet jy jou inspan en die volgende paragraaf verskeie kere lees totdat jy die betekenis voel van wat tot die einde gesê is. Elke woord is belangrik daar. Voorbeelde sal jou help om dit uit te vind.

Hoe presies werk die VLOOKUP-formule?

• Formule tipe I. As die laaste parameter weggelaat of gelyk aan 1 gespesifiseer word, neem die VLOOKUP aan dat die eerste kolom in stygende volgorde gesorteer is, dus stop die soektog by die ry wat gaan onmiddellik voor die reël wat die waarde groter as die verlangde een bevat … As geen so 'n string gevind word nie, word die laaste ry van die reeks teruggestuur.

  

• Formule II. As die laaste parameter as 0 gespesifiseer word, dan skandeer die VLOOKUP die eerste kolom van die skikking opeenvolgend en stop die soektog onmiddellik wanneer die eerste presiese passing met die parameter gevind word, anders is die # N / A (# N / A) foutkode word terugbesorg.

  

Formules werkvloei

VPR tipe I

VPR tipe II

Gevolge vir formules van die vorm I

1. Formules kan gebruik word om waardes oor reekse te versprei.
2. As die eerste kolom duplikaatwaardes bevat en korrek gesorteer is, sal die laaste van die rye met duplikaatwaardes teruggestuur word.
3. As jy soek vir 'n waarde wat natuurlik groter is as wat die eerste kolom kan bevat, dan kan jy maklik die laaste ry van die tabel vind, wat nogal waardevol kan wees.
4. Hierdie aansig sal die # N / A-fout slegs terugstuur as dit nie 'n waarde kleiner as of gelyk aan die verlangde een vind nie.
5. Dit is nogal moeilik om te verstaan dat die formule die verkeerde waardes gee as u skikking nie gesorteer is nie.

Gevolge vir formules van tipe II

As die gewenste waarde verskeie kere in die eerste kolom van die skikking voorkom, sal die formule die eerste ry kies vir daaropvolgende dataherwinning.

VLOOKUP prestasie

Jy het die klimaks van die artikel bereik. Dit wil voorkom, wel, wat is die verskil as ek nul of een as die laaste parameter spesifiseer? Basies dui almal natuurlik nul aan, aangesien dit redelik prakties is: jy hoef nie bekommerd te wees oor die sortering van die eerste kolom van die skikking nie, jy kan dadelik sien of die waarde gevind is of nie. Maar as jy 'n paar duisend VLOOKUP-formules op jou blad het, sal jy agterkom dat VLOOKUP II stadig is. Terselfdertyd begin almal gewoonlik dink:

• Ek benodig 'n kragtiger rekenaar;
• Ek het 'n vinniger formule nodig, byvoorbeeld, baie mense weet van INDEX + MATCH, wat kwansuis vinniger is met 'n skamele 5-10%.

En min mense dink dat sodra jy VLOOKUP van die tipe I begin gebruik en verseker dat die eerste kolom op enige manier gesorteer word, die spoed van VLOOKUP 57 keer sal toeneem. Ek skryf in woorde - SEWE EN VYFTIG KEER! Nie 57% nie, maar 5 700%. Ek het hierdie feit redelik betroubaar nagegaan.

Die geheim van sulke vinnige werk lê in die feit dat 'n uiters doeltreffende soekalgoritme op 'n gesorteerde skikking toegepas kan word, wat binêre soektog genoem word (halveringsmetode, digotomiemetode). So VLOOKUP van tipe I pas dit toe, en VLOOKUP van tipe II soektogte sonder enige optimalisering. Dieselfde geld vir die MATCH-funksie, wat 'n soortgelyke parameter insluit, en die LOOKUP-funksie, wat net op gesorteerde skikkings werk en in Excel ingesluit is vir versoenbaarheid met Lotus 1-2-3.

Nadele van die formule

Die nadele van VLOOKUP is duidelik: eerstens soek dit slegs in die eerste kolom van die gespesifiseerde skikking, en tweedens net regs van hierdie kolom. En soos jy verstaan, kan dit heel moontlik gebeur dat die kolom wat die nodige inligting bevat aan die linkerkant van die kolom sal wees waarin ons gaan kyk. Die reeds genoemde kombinasie van formules INDEX + MATCH is sonder hierdie nadeel, wat dit die mees buigsame oplossing maak om data uit tabelle te onttrek in vergelyking met VLOOKUP (VLOOKUP).

Sommige aspekte van die toepassing van die formule in die werklike lewe

Reeks soek

'n Klassieke illustrasie van 'n reekssoektog is die taak om 'n afslag volgens bestellinggrootte te bepaal.

Soek vir teksstringe

Natuurlik soek VLOOKUP nie net vir syfers nie, maar ook vir teks. Daar moet in gedagte gehou word dat die formule nie tussen die geval van karakters onderskei nie. As jy jokertekens gebruik, kan jy 'n vaag soektog organiseer. Daar is twee jokertekens: "?" - vervang enige karakter in 'n teksstring, "*" - vervang enige aantal karakters.

Veg ruimtes

Die vraag word dikwels geopper hoe om die probleem van ekstra spasies op te los wanneer jy soek. As die opsoektabel nog van hulle skoongemaak kan word, is die eerste parameter van die VLOOKUP-formule nie altyd aan jou nie. Daarom, as die risiko bestaan om die selle met ekstra spasies te verstop, kan jy die TRIM-funksie gebruik om dit skoon te maak.

Verskillende dataformaat

As die eerste parameter van die VLOOKUP-funksie verwys na 'n sel wat 'n nommer bevat, maar wat in die sel as teks gestoor word, en die eerste kolom van die skikking bevat getalle in die korrekte formaat, dan sal die soektog misluk. Die teenoorgestelde situasie is ook moontlik. Die probleem kan maklik opgelos word deur parameter 1 in die vereiste formaat te vertaal:

= VLOOKUP (−− D7; Produkte! $ A $ 2: $ C $ 5; 3; 0) - as D7 teks bevat en die tabel syfers bevat;

= VLOOKUP (D7 & ""); Produkte $ A $ 2: $ C $ 5; 3; 0) - en omgekeerd.

Terloops, jy kan teks in 'n nommer op verskeie maniere gelyktydig vertaal, kies:

• Dubbele ontkenning -D7.
• Vermenigvuldiging met een D7 * 1.
• Nul-optelling D7 + 0.
• Verhoog tot die eerste mag D7 ^ 1.

Die omskakeling van 'n getal na teks word gedoen deur aaneenskakeling met 'n leë string, wat Excel dwing om die datatipe om te skakel.

Hoe om # N/A te onderdruk

Dit is baie gerieflik om met die IFERROR-funksie te doen.

Byvoorbeeld: = IFERROR (VLOOKUP (D7; Produkte! $ A $ 2: $ C $ 5; 3; 0); "").

As VLOOKUP die foutkode # N / A terugstuur, sal IFERROR dit onderskep en parameter 2 vervang (in hierdie geval 'n leë string), en as geen fout voorkom nie, sal hierdie funksie voorgee dat dit glad nie bestaan nie, maar daar is net VLOOKUP wat normale resultaat opgelewer het.

Skikking

Dikwels vergeet hulle om die verwysing van die skikking absoluut te maak, en wanneer hulle rek, "sweef" die skikking. Onthou om $ A $ 2: $ C $ 5 in plaas van A2: C5 te gebruik.

Dit is 'n goeie idee om die verwysingsskikking op 'n aparte vel van die werkboek te plaas. Dit kom nie onder die voete nie, en dit sal veiliger wees.

Nog 'n beter idee sou wees om hierdie skikking as 'n genoemde reeks te verklaar.

Baie gebruikers, wanneer hulle 'n skikking spesifiseer, gebruik 'n konstruksie soos A: C, wat hele kolomme spesifiseer. Hierdie benadering het 'n bestaansreg, aangesien jy gered word om tred te hou met die feit dat jou skikking al die vereiste stringe insluit. As jy rye by die vel voeg met die oorspronklike skikking, dan hoef die reeks gespesifiseer as A: C nie aangepas te word nie. Natuurlik dwing hierdie sintaktiese konstruk Excel om 'n bietjie meer werk te doen as om die reeks presies te spesifiseer, maar hierdie bokoste kan afgeskeep word. Ons praat van honderdstes van 'n sekonde.

Wel, op die rand van genie - om die skikking in die vorm te rangskik.

Gebruik die COLUMN-funksie om die kolom te spesifiseer om te onttrek

As die tabel waarin jy data ophaal met VLOOKUP dieselfde struktuur as die opsoektabel het, maar eenvoudig minder rye bevat, dan kan jy die KOLOM () funksie in VLOOKUP gebruik om outomaties die getalle van die kolomme wat herwin moet word, te bereken. In hierdie geval sal alle VLOOKUP-formules dieselfde wees (aangepas vir die eerste parameter, wat outomaties verander)! Let daarop dat die eerste parameter 'n absolute kolomkoördinaat het.

Skep 'n saamgestelde sleutel met & "|" &

As dit nodig word om deur verskeie kolomme op dieselfde tyd te soek, dan is dit nodig om 'n saamgestelde sleutel vir die soektog te maak. As die terugkeerwaarde nie tekstueel was nie (soos die geval is met die "Kode"-veld), maar numeries, dan sal die geriefliker SUMIFS-formule hiervoor geskik wees en die saamgestelde kolomsleutel sal glad nie nodig wees nie.

Dit is my eerste artikel vir 'n Lifehacker. As jy daarvan gehou het, nooi ek jou om te besoek, en lees ook graag in die kommentaar oor jou geheime van die gebruik van die VLOOKUP-funksie en dies meer. Dankie.:)