Wie te lui is of geen tijd heeft om te lezen – bekijk de video. Details en nuances staan in de onderstaande tekst.
Formulering van het probleem
We hebben dus twee tabellen - bestel tafel и prijslijst:
De taak is om prijzen uit de prijslijst automatisch te vervangen door de tabel met bestellingen, waarbij de nadruk ligt op de naam van het product, zodat u later de kosten kunt berekenen.
Oplossing
In de Excel-functieset, onder de categorie Referenties en arrays (Zoeken en referentie) er is een functie VPR (VERT.ZOEKEN).Deze functie zoekt naar een bepaalde waarde (in ons voorbeeld is dit het woord "Appels") in de meest linkse kolom van de gespecificeerde tabel (prijslijst) die van boven naar beneden beweegt en, nadat deze is gevonden, de inhoud van de aangrenzende cel weergeeft (23 roebel). Schematisch kan de werking van deze functie dus worden weergegeven:
Om de functie nog gemakkelijker te kunnen gebruiken, doet u één ding tegelijk: geef het cellenbereik in de prijslijst uw eigen naam. Selecteer hiervoor alle cellen van de prijslijst behalve de “header” (G3: H19), selecteer uit het menu Invoegen – Naam – Toewijzen (Invoegen — Naam — Definiëren) of druk CTRL + F3 en voer een naam in (geen spaties) zoals Prijs… Nu, in de toekomst, kunt u deze naam gebruiken om naar de prijslijst te linken.
Nu gebruiken we de functie VPR… Selecteer de cel waar het zal worden ingevoerd (D3) en open het tabblad Formules – Functie-invoeging (Formules — Functie invoegen)... In de categorie Referenties en arrays (Zoeken en referentie) vind de functie VPR (VERT.ZOEKEN) en druk op OK… Er verschijnt een venster voor het invoeren van argumenten voor de functie:
We vullen ze achtereenvolgens in:
- Gewenste waarde (Opzoekwaarde) – de naam van het product dat de functie moet vinden in de meest linkse kolom van de prijslijst. In ons geval het woord "Appels" uit cel B3.
- tafel (Tabelmatrix) – een tabel waaruit de gewenste waarden zijn genomen, dat wil zeggen onze prijslijst. Ter referentie gebruiken we onze eigen naam "Prijs" die eerder is gegeven. Als je geen naam hebt gegeven, kun je gewoon de tafel selecteren, maar vergeet niet op de knop te drukken F4om de link met dollartekens vast te pinnen, omdat het anders naar beneden schuift bij het kopiëren van onze formule naar de rest van de cellen in kolom D3:D30.
- Kolomnummer (Kolom indexnummer) – serienummer (geen letter!) Van de kolom in de prijslijst waaruit we prijswaarden halen. De eerste kolom van de prijslijst met de namen is genummerd 1, daarom hebben we de prijs nodig uit de kolom genummerd 2.
- interval_opzoeken (Bereik opzoeken) – in dit veld kunnen slechts twee waarden worden ingevuld: FALSE of TRUE:
- Als een waarde is ingevoerd 0 or LIEGEN (ONWAAR), dan betekent dit in feite dat alleen zoeken is toegestaan exacte overeenkomst, dat wil zeggen als de functie het niet-standaard artikel dat is gespecificeerd in de besteltabel in de prijslijst niet vindt (als bijvoorbeeld "Kokosnoot" is ingevoerd), zal het de #N/A (geen gegevens) fout genereren.
- Als een waarde is ingevoerd 1 or TRUE (WAAR), dan betekent dit dat u de zoekopdracht niet toestaat op de exacte, maar geschatte overeenkomst, dwz in het geval van "kokos", zal de functie proberen een product te vinden met een naam die zo dicht mogelijk bij "kokos" ligt en de prijs voor deze naam teruggeven. In de meeste gevallen kan een dergelijke vervanging bij benadering een truc voor de gebruiker uithalen door de waarde van het verkeerde product dat er daadwerkelijk was te vervangen! Dus voor de meeste echte zakelijke problemen is zoeken bij benadering het beste niet toe te staan. De uitzondering is wanneer we op zoek zijn naar getallen en niet naar tekst, bijvoorbeeld bij het berekenen van Stappenkortingen.
Alles! Het blijft om op te drukken OK en kopieer de ingevoerde functie naar de hele kolom.
# Nvt fouten en hun onderdrukking
Functie VPR (VERT.ZOEKEN) retourneert #N.v.t. fout (#N.v.t.) als een:
- Exact zoeken ingeschakeld (argument Intervalweergave = 0) en de gewenste naam staat er niet in tafel.
- Grof zoeken inbegrepen (Intervalweergave = 1) doel tafel, waarin de zoekopdracht plaatsvindt, wordt niet in oplopende volgorde van namen gesorteerd.
- Het formaat van de cel waar de vereiste waarde van de naam vandaan komt (bijvoorbeeld B3 in ons geval) en het formaat van de cellen van de eerste kolom (F3: F19) van de tabel zijn verschillend (bijvoorbeeld numeriek en tekst ). Dit geval is vooral typisch bij het gebruik van numerieke codes (rekeningnummers, identificatiegegevens, datums, enz.) in plaats van tekstnamen. In dit geval kunt u de functies Ч и TEKST om gegevensformaten te converteren. Het zal er ongeveer zo uitzien:
=VERT.ZOEKEN(TEKST(B3),prijs,0)
Hier kun je meer over lezen.
- De functie kan de vereiste waarde niet vinden omdat de code spaties of onzichtbare niet-afdrukbare tekens bevat (regeleinden, enz.). In dit geval kunt u tekstfuncties gebruiken TRIM (TRIM) и BEDRUK(SCHOON) om ze te verwijderen:
=VERT.ZOEKEN(TRIMSPATIES(SCHOON(B3)),prijs,0)
=VLOOKUP(TRIM(CLEAN(B3));prijs;0)
Om de foutmelding te onderdrukken # N / A (#N.v.t.) in gevallen waarin de functie geen exacte overeenkomst kan vinden, kunt u de functie gebruiken IFFOUT (ALS FOUT)... Dus deze constructie onderschept bijvoorbeeld alle fouten die door de VERT.ZOEKEN worden gegenereerd en vervangt ze door nullen:
= IFERROR (VERT.ZOEKEN (B3, prijs, 2, 0), 0)
= IFERROR (VERT.ZOEKEN (B3; prijs; 2; 0); 0)
PS
Als je niet één waarde, maar de hele set in één keer wilt extraheren (als er meerdere verschillende zijn), dan zul je moeten sjamaniseren met de matrixformule. of gebruik de nieuwe XLOOKUP-functie van Office 365.
- Een verbeterde versie van de functie VERT.ZOEKEN (VERT.ZOEKEN 2).
- Snelle berekening van stap(bereik)kortingen met de functie VERT.ZOEKEN.
- Een "linker VERT.ZOEKEN" maken met behulp van de INDEX- en MATCH-functies
- De functie VERT.ZOEKEN gebruiken om de formulieren in te vullen met gegevens uit de lijst
- Hoe u niet de eerste, maar alle waarden in één keer uit de tabel haalt
- VLOOKUP2 en VLOOKUP3 functies van de PLEX add-on