Wie te lui is of nie tyd het om te lees nie – kyk na die video. Besonderhede en nuanses is in die teks hieronder.
Formulering van die probleem
So, ons het twee tafels – bestel tafel и pryslys:
Die taak is om pryse van die pryslys outomaties in die tabel van bestellings te vervang, met die fokus op die naam van die produk sodat jy later die koste kan bereken.
Oplossing
In die Excel-funksiestel, onder die kategorie Verwysings en skikkings (Soek en verwys) daar is 'n funksie VPR (VERSOEKING).Hierdie funksie soek na 'n gegewe waarde (in ons voorbeeld is dit die woord "Appels") in die mees linkse kolom van die gespesifiseerde tabel (pryslys) wat van bo na onder beweeg en, nadat dit gevind is, vertoon die inhoud van die aangrensende sel (23 roebels) .Skematies kan die werking van hierdie funksie voorgestel word Dus:
Om die funksie verder te gebruik, doen een ding op een slag – gee die reeks selle in die pryslys jou eie naam. Om dit te doen, kies al die selle van die pryslys behalwe vir die "header" (G3: H19), kies uit die spyskaart Voeg in – Naam – Ken toe (Voeg in — Naam — Definieer) of druk CTRL + F3 en voer enige naam in (geen spasies) soos Prys… Nou, in die toekoms, kan jy hierdie naam gebruik om na die pryslys te skakel.
Nou gebruik ons die funksie VPR… Kies die sel waar dit ingevoer gaan word (D3) en maak die oortjie oop Formules – Funksie-invoeging (Formules – Voeg funksie in)… In die kategorie Verwysings en skikkings (Opsoek en verwysing) vind die funksie VPR (VERSOEKING) en druk OK… 'n Venster vir die invoer van argumente vir die funksie sal verskyn:
Ons vul hulle om die beurt in:
- Gewenste waarde (Opsoekwaarde) – die naam van die produk wat die funksie in die linkerkolom van die pryslys moet vind. In ons geval, die woord "Appels" van sel B3.
- Tabel (Tabelskikking) – 'n tabel waaruit die verlangde waardes uXNUMXbuXNUMXbare geneem is, dit is ons pryslys. Vir verwysing gebruik ons ons eie naam "Prys" wat vroeër gegee is. As jy nie 'n naam gegee het nie, kan jy net die tabel kies, maar moenie vergeet om die knoppie te druk nie F4om die skakel met dollartekens vas te pen, want anders sal dit afgly wanneer ons formule na die res van die selle in kolom D3:D30 kopieer.
- Kolom_nommer (Kolomindeksnommer) – reeksnommer (nie 'n letter nie!) Van die kolom in die pryslys waaruit ons pryswaardes sal neem. Die eerste kolom van die pryslys met die name is 1 genommer, daarom benodig ons die prys van die kolom nommer 2.
- interval_soek (Reeksopsoek) - slegs twee waardes kan in hierdie veld ingevoer word: ONWAAR of WAAR:
- As 'n waarde ingevoer word 0 or LIEG (ONWAAR), dan beteken dit in werklikheid dat slegs soektog toegelaat word presiese pasmaat, dws as die funksie nie die nie-standaarditem wat in die besteltabel in die pryslys gespesifiseer is, vind nie (as “Coconut” byvoorbeeld ingevoer word), sal dit die #N/A (geen data) fout genereer.
- As 'n waarde ingevoer word 1 or WAAR (WAAR), dan beteken dit dat jy die soektog nie vir die presiese toelaat nie, maar benaderde wedstryd, dit wil sê in die geval van "klapper", sal die funksie probeer om 'n produk te vind met 'n naam wat so na as moontlik aan "klapper" is en die prys vir hierdie naam terugstuur. In die meeste gevalle kan so 'n benaderde vervanging 'n truuk op die gebruiker speel deur die waarde van die verkeerde produk wat werklik daar was, te vervang! So vir die meeste werklike besigheidsprobleme, is benaderde soektog die beste om nie toe te laat nie. Die uitsondering is wanneer ons na syfers soek en nie teks nie – byvoorbeeld wanneer ons Stapafslag bereken.
Alles! Dit bly om te druk OK en kopieer die ingevoerde funksie na die hele kolom.
# N / A foute en hul onderdrukking
funksie VPR (VERSOEKING) gee #Nvt fout (#Nvt) As 'n:
- Presiese soektog geaktiveer (argument Intervalaansig = 0) en die gewenste naam is nie in nie Tabel.
- Growwe soektog ingesluit (Intervalaansig = 1), maar Tabel, waarin die soektog plaasvind, word nie in stygende volgorde van name gesorteer nie.
- Die formaat van die sel waar die vereiste waarde van die naam vandaan kom (byvoorbeeld, B3 in ons geval) en die formaat van die selle van die eerste kolom (F3: F19) van die tabel verskil (byvoorbeeld, numeries en teks ). Hierdie geval is veral tipies wanneer numeriese kodes (rekeningnommers, identifiseerders, datums, ens.) in plaas van teksname gebruik word. In hierdie geval kan jy die funksies gebruik Ч и TEKS om dataformate om te skakel. Dit sal so iets lyk:
=OPSOEK(TEKS(B3);prys;0)
Jy kan meer hieroor lees.
- Die funksie kan nie die vereiste waarde vind nie, want die kode bevat spasies of onsigbare nie-drukbare karakters (reëlbreuke, ens.). In hierdie geval kan jy teksfunksies gebruik TRIM (TRIM) и DRUK(SKOON) om hulle te verwyder:
=VLOOKUP(TRIMSPACES(SKOON(B3)),prys,0)
=VLOOKUP(TRIM(SKOON(B3));prys;0)
Om die foutboodskap te onderdruk # N / A (#Nvt) in gevalle waar die funksie nie 'n presiese passing kan vind nie, kan jy die funksie gebruik IFERROR (IFERROR)… So, byvoorbeeld, onderskep hierdie konstruksie enige foute wat deur die VLOOKUP gegenereer word en vervang dit met nulle:
= IFERROR (VLOOKUP (B3, prys, 2, 0), 0)
= IFERROR (VLOOKUP (B3; prys; 2; 0); 0)
PS
As jy nie een waarde moet onttrek nie, maar die hele stel gelyktydig (as daar verskeie verskillendes is), sal jy met die skikkingsformule moet shamaniseer. of gebruik die nuwe XLOOKUP-funksie van Office 365.
- 'n Verbeterde weergawe van die VLOOKUP-funksie (VLOOKUP 2).
- Vinnige berekening van stap (reeks) afslag met behulp van die VLOOKUP-funksie.
- Hoe om 'n "linker VLOOKUP" te maak deur die INDEX- en MATCH-funksies te gebruik
- Hoe om die VLOOKUP-funksie te gebruik om die vorms met data uit die lys in te vul
- Hoe om nie die eerste nie, maar al die waardes gelyktydig uit die tabel te haal
- VLOOKUP2 en VLOOKUP3 funksies vanaf die PLEX-byvoeging