Alle geheimen van de Excel VERT.ZOEKEN-functie voor het vinden van gegevens in een tabel en het extraheren ervan naar een andere

2024 Auteur: Malcolm Clapton | [email protected]. Laatst gewijzigd: 2023-12-17 04:06

Na het lezen van het artikel leert u niet alleen hoe u gegevens in een Excel-spreadsheet kunt vinden en deze in een ander kunt extraheren, maar ook technieken die kunnen worden gebruikt in combinatie met de functie VERT. ZOEKEN.

Wanneer u in Excel werkt, is het vaak nodig om gegevens in de ene tabel te vinden en deze in een andere te extraheren. Als u nog steeds niet weet hoe u dit moet doen, leert u na het lezen van het artikel niet alleen hoe u dit moet doen, maar ontdekt u ook onder welke omstandigheden u de maximale prestaties uit het systeem kunt persen. De meeste van de zeer effectieve technieken die in combinatie met de functie VERT. ZOEKEN moeten worden gebruikt, worden overwogen.

Zelfs als u de functie VERT. ZOEKEN al jaren gebruikt, zal dit artikel met grote waarschijnlijkheid nuttig voor u zijn en u niet onverschillig laten. Als IT-specialist en vervolgens hoofd in IT, gebruik ik bijvoorbeeld VLOOKUP al 15 jaar, maar ik ben er pas in geslaagd om met alle nuances om te gaan, toen ik mensen op professionele basis Excel begon te leren.

VERT. ZOEKEN is een afkorting voor vverticaal NSinspectie. Evenzo VERT. ZOEKEN - Verticaal ZOEKEN. De naam van de functie geeft ons al een hint dat het in de rijen van de tabel zoekt (verticaal - de rijen herhalen en de kolom repareren), en niet in de kolommen (horizontaal - de kolommen herhalen en de rij repareren). Opgemerkt moet worden dat VERT. ZOEKEN een zus heeft - een lelijk eendje dat nooit een zwaan zal worden - dit is de HORIZ. ZOEKEN-functie. HORIZ. ZOEKEN, in tegenstelling tot VERT. ZOEKEN, voert horizontale zoekopdrachten uit, maar het concept van Excel (en inderdaad het concept van gegevensorganisatie) houdt in dat uw tabellen minder kolommen en veel meer rijen hebben. Daarom moeten we veel vaker op rijen zoeken dan op kolommen. Gebruik je de HLO-functie te vaak in Excel, dan heb je waarschijnlijk iets niet begrepen in dit leven.

Syntaxis

De functie VERT. ZOEKEN heeft vier parameters:

= VERT. ZOEKEN (;; [;]), hier:

- de gewenste waarde (zelden) of een verwijzing naar een cel die de gewenste waarde bevat (in de overgrote meerderheid van de gevallen);

- verwijzing naar een celbereik (tweedimensionale array), in de EERSTE (!) kolom waarvan de parameterwaarde wordt gezocht;

- kolomnummer in het bereik waaruit de waarde wordt geretourneerd;

- dit is een zeer belangrijke parameter die antwoord geeft op de vraag of de eerste kolom van het bereik in oplopende volgorde is gesorteerd. Als de array is gesorteerd, specificeren we de waarde TRUE of 1, anders - FALSE of 0. Als deze parameter wordt weggelaten, wordt deze standaard ingesteld op 1.

Ik wed dat veel van degenen die de functie VERT. ZOEKEN als schilferig kennen, zich na het lezen van de beschrijving van de vierde parameter misschien ongemakkelijk voelen, omdat ze gewend zijn om het in een iets andere vorm te zien: meestal hebben ze het over een exacte overeenkomst wanneer zoeken (FALSE of 0) of een bereikscan (TRUE of 1).

Nu moet je je inspannen en de volgende alinea meerdere keren lezen totdat je de betekenis voelt van wat er tot het einde werd gezegd. Elk woord is daar belangrijk. Voorbeelden helpen je erachter te komen.

Hoe werkt de VERT. ZOEKEN formule precies?

• Formuletype I. Als de laatste parameter wordt weggelaten of gespecificeerd gelijk aan 1, dan gaat VERT. ZOEKEN ervan uit dat de eerste kolom in oplopende volgorde is gesorteerd, zodat het zoeken stopt bij de rij die gaat onmiddellijk vooraf aan de regel die de waarde bevat die groter is dan de gewenste … Als een dergelijke tekenreeks niet wordt gevonden, wordt de laatste rij van het bereik geretourneerd.

  

• Formule II. Als de laatste parameter is opgegeven als 0, scant VERT. ZOEKEN de eerste kolom van de array opeenvolgend en stopt het zoeken onmiddellijk wanneer de eerste exacte overeenkomst met de parameter wordt gevonden, anders de # N / A (# N / A) foutcode wordt teruggestuurd.

  

Formules-workflows

VPR type I

VPR type II

Gevolgen voor formules van de vorm I

1. Formules kunnen worden gebruikt om waarden over bereiken te verdelen.
2. Als de eerste kolom dubbele waarden bevat en correct is gesorteerd, wordt de laatste van de rijen met dubbele waarden geretourneerd.
3. Als u zoekt naar een waarde die duidelijk groter is dan de eerste kolom kan bevatten, kunt u gemakkelijk de laatste rij van de tabel vinden, die behoorlijk waardevol kan zijn.
4. Deze weergave retourneert de fout # N / A alleen als er geen waarde wordt gevonden die kleiner is dan of gelijk is aan de gewenste waarde.
5. Het is nogal moeilijk te begrijpen dat de formule de verkeerde waarden retourneert als uw array niet is gesorteerd.

Gevolgen voor formules van type II

Als de gewenste waarde meerdere keren voorkomt in de eerste kolom van de matrix, selecteert de formule de eerste rij voor het later ophalen van gegevens.

VERT. ZOEKEN-prestaties

Je hebt de climax van het artikel bereikt. Het lijkt erop dat, nou ja, wat is het verschil als ik nul of één opgeef als de laatste parameter? In principe geeft iedereen natuurlijk nul aan, aangezien dit best praktisch is: je hoeft je geen zorgen te maken over het sorteren van de eerste kolom van de array, je kunt meteen zien of de waarde is gevonden of niet. Maar als je enkele duizenden VERT. ZOEKEN-formules op je blad hebt staan, zul je merken dat VERT. ZOEKEN II traag is. Tegelijkertijd begint meestal iedereen te denken:

• Ik heb een krachtigere computer nodig;
• Ik heb bijvoorbeeld een snellere formule nodig, veel mensen kennen INDEX + MATCH, die zogenaamd sneller is met een magere 5-10%.

En weinig mensen denken dat zodra u VERT. ZOEKEN van het type I gaat gebruiken en ervoor zorgt dat de eerste kolom op welke manier dan ook wordt gesorteerd, de snelheid van VERT. ZOEKEN 57 keer zal toenemen. Ik schrijf in woorden - ZEVENVIJFTIG KEER! Geen 57%, maar 5.700%. Ik heb dit feit vrij betrouwbaar gecontroleerd.

Het geheim van zo'n snel werk ligt in het feit dat op een gesorteerde array een uiterst efficiënt zoekalgoritme kan worden toegepast, dat binair zoeken wordt genoemd (halving-methode, dichotomie-methode). Dus VERT. ZOEKEN van type I past het toe, en VERT. ZOEKEN van type II zoekt zonder enige optimalisatie. Hetzelfde geldt voor de MATCH-functie, die een vergelijkbare parameter bevat, en de LOOKUP-functie, die alleen werkt op gesorteerde arrays en is opgenomen in Excel voor compatibiliteit met Lotus 1-2-3.

Nadelen van de formule

De nadelen van VERT. ZOEKEN zijn duidelijk: ten eerste zoekt het alleen in de eerste kolom van de opgegeven array en ten tweede alleen rechts van deze kolom. En zoals u begrijpt, kan het gebeuren dat de kolom met de benodigde informatie links staat van de kolom waarin we gaan zoeken. De reeds genoemde combinatie van formules INDEX + MATCH heeft dit nadeel niet, waardoor het de meest flexibele oplossing is voor het extraheren van gegevens uit tabellen in vergelijking met VERT. ZOEKEN (VERT. ZOEKEN).

Enkele aspecten van het toepassen van de formule in het echte leven

Bereik zoeken

Een klassiek voorbeeld van een bereikonderzoek is het bepalen van een korting op ordergrootte.

Zoeken naar tekenreeksen

Natuurlijk zoekt VERT. ZOEKEN niet alleen naar cijfers, maar ook naar tekst. Houd er rekening mee dat de formule geen onderscheid maakt tussen het geval van karakters. Als u jokertekens gebruikt, kunt u een fuzzy-zoekopdracht organiseren. Er zijn twee jokertekens: "?" - vervangt een willekeurig teken in een tekenreeks, "*" - vervangt een willekeurig aantal tekens.

Vechtruimtes

Vaak wordt de vraag gesteld hoe het probleem van extra spaties bij het zoeken op te lossen. Als de opzoektabel er nog steeds van kan worden gewist, is de eerste parameter van de VERT. ZOEKEN-formule niet altijd aan jou. Daarom, als het risico van verstopping van de cellen met extra spaties aanwezig is, kunt u de TRIM-functie gebruiken om deze te wissen.

Ander gegevensformaat

Als de eerste parameter van de functie VERT. ZOEKEN verwijst naar een cel die een getal bevat, maar die als tekst in de cel is opgeslagen, en de eerste kolom van de array bevat getallen in het juiste formaat, dan zal de zoekopdracht mislukken. De tegenovergestelde situatie is ook mogelijk. Het probleem kan eenvoudig worden opgelost door parameter 1 in het vereiste formaat te vertalen:

= VERT. ZOEKEN (−− D7; Producten! $ A $ 2: $ C $ 5; 3; 0) - als D7 tekst bevat en de tabel getallen bevat;

= VERT. ZOEKEN (D7 & ""); Producten $ A $ 2: $ C $ 5; 3; 0) - en omgekeerd.

Overigens kun je tekst op meerdere manieren tegelijk in een getal vertalen, kies:

• Dubbele ontkenning -D7.
• Vermenigvuldiging met één D7 * 1.
• Nul bijtelling D7 + 0.
• Verhogen tot de eerste macht D7 ^ 1.

Het converteren van een getal naar tekst gebeurt door middel van aaneenschakeling met een lege tekenreeks, waardoor Excel het gegevenstype moet converteren.

Hoe # N / A. te onderdrukken

Dit is erg handig om te doen met de IFERROR-functie.

Bijvoorbeeld: = IFERROR (VERT. ZOEKEN (D7; Producten! $ A $ 2: $ C $ 5; 3; 0); "").

Als VERT. ZOEKEN de foutcode # N / A retourneert, zal IFERROR deze onderscheppen en parameter 2 vervangen (in dit geval een lege tekenreeks), en als er geen fout optreedt, zal deze functie doen alsof deze helemaal niet bestaat, maar er is alleen VERT. ZOEKEN dat het normale resultaat heeft geretourneerd.

Array

Vaak vergeten ze de referentie van de array absoluut te maken, en bij het uitrekken "zweeft" de array. Vergeet niet om $ A $ 2 te gebruiken: $ C $ 5 in plaats van A2: C5.

Het is een goed idee om de referentiearray op een apart blad van de werkmap te plaatsen. Het komt niet onder de voeten en het zal veiliger zijn.

Een nog beter idee zou zijn om deze array als een benoemd bereik te declareren.

Veel gebruikers gebruiken bij het specificeren van een array een constructie zoals A:C, waarbij hele kolommen worden gespecificeerd. Deze aanpak heeft bestaansrecht, aangezien u niet hoeft bij te houden dat uw array alle vereiste strings bevat. Als u rijen toevoegt aan het blad met de originele array, hoeft het bereik dat is opgegeven als A:C niet te worden aangepast. Natuurlijk dwingt deze syntactische constructie Excel om iets meer werk te doen dan het precies specificeren van het bereik, maar deze overhead kan worden verwaarloosd. We hebben het over honderdsten van een seconde.

Nou, op de rand van genie - om de array in de vorm te rangschikken.

De COLUMN-functie gebruiken om de kolom op te geven die moet worden geëxtraheerd

Als de tabel waarin u gegevens ophaalt met VERT. ZOEKEN dezelfde structuur heeft als de opzoektabel, maar simpelweg minder rijen bevat, kunt u de functie KOLOM () in VERT. ZOEKEN gebruiken om automatisch het aantal op te halen kolommen te berekenen. In dit geval zijn alle VERT. ZOEKEN-formules hetzelfde (aangepast voor de eerste parameter, die automatisch verandert)! Merk op dat de eerste parameter een absolute kolomcoördinaat heeft.

Een samengestelde sleutel maken met & "|" &

Als het nodig wordt om op meerdere kolommen tegelijk te zoeken, dan is het nodig om een samengestelde sleutel voor de zoekopdracht te maken. Als de geretourneerde waarde niet tekstueel was (zoals het geval is met het veld "Code"), maar numeriek, dan zou de handiger SOMMEN-formule hiervoor geschikt zijn en zou de samengestelde kolomsleutel helemaal niet vereist zijn.

Dit is mijn eerste artikel voor een Lifehacker. Als je het leuk vond, nodig ik je uit om te bezoeken, en lees ook graag in de commentaren over je geheimen van het gebruik van de VERT. ZOEKEN-functie en dergelijke. Bedankt.:)