Pwy sy'n rhy ddiog neu heb amser i ddarllen - gwyliwch y fideo. Mae manylion a naws yn y testun isod.
Ffurfio'r broblem
Felly, mae gennym ddau fwrdd - bwrdd archeb и rhestr pris:
Y dasg yw amnewid prisiau o'r rhestr brisiau yn y tabl archebion yn awtomatig, gan ganolbwyntio ar enw'r cynnyrch fel y gallwch gyfrifo'r gost yn ddiweddarach.
Ateb
Yn y set swyddogaeth Excel, o dan y categori Cyfeiriadau ac araeau (Edrych a chyfeirio) mae swyddogaeth VPR (VLOOKUP).Mae'r swyddogaeth hon yn edrych am werth penodol (yn ein hesiampl, dyma'r gair “Afalau”) yng ngholofn fwyaf chwith y tabl penodedig (rhestr brisiau) yn symud o'r top i'r gwaelod ac, ar ôl dod o hyd iddo, yn dangos cynnwys y gell gyfagos (23 rubles). Yn sgematig, gellir cynrychioli gweithrediad y swyddogaeth hon Felly:
Er mwyn hwyluso defnydd pellach o'r swyddogaeth, gwnewch un peth ar unwaith - rhowch eich enw eich hun i'r ystod o gelloedd yn y rhestr brisiau. I wneud hyn, dewiswch holl gelloedd y rhestr brisiau ac eithrio'r “pennawd” (G3: H19), dewiswch o'r ddewislen Mewnosod - Enw - Neilltuo (Rhowch — Enw — Diffiniwch) neu wasg CTRL + F3 a nodwch unrhyw enw (dim bylchau) fel Pris… Yn awr, yn y dyfodol, gallwch ddefnyddio'r enw hwn i gysylltu â'r rhestr brisiau.
Nawr rydym yn defnyddio'r swyddogaeth VPR… Dewiswch y gell lle bydd yn cael ei nodi (D3) ac agorwch y tab Fformiwlâu – Mewnosod Swyddogaeth (Fformiwlâu - Mewnosod Swyddogaeth)… yn y categori Cyfeiriadau ac araeau (Gweld a Chyfeirnod) dod o hyd i'r swyddogaeth VPR (VLOOKUP) ac yn y wasg OK… Bydd ffenestr ar gyfer mewnbynnu dadleuon ar gyfer y swyddogaeth yn ymddangos:
Rydyn ni'n eu llenwi yn eu tro:
- Gwerth dymunol (Gwerth Edrych) – enw'r cynnyrch y dylai'r swyddogaeth ddod o hyd iddo yng ngholofn fwyaf chwith y rhestr brisiau. Yn ein hachos ni, y gair “Afalau” o gell B3.
- Tabl (Arae Tabl) - tabl y cymerwyd y gwerthoedd dymunol uXNUMXbuXNUMX ohono, hynny yw, ein rhestr brisiau. Er gwybodaeth, rydym yn defnyddio ein henw ein hunain “Pris” a roddwyd yn gynharach. Os na wnaethoch chi roi enw, gallwch chi ddewis y tabl, ond peidiwch ag anghofio pwyso'r botwm F4i binio'r cysylltiad ag arwyddion doler, oherwydd fel arall, bydd yn llithro i lawr wrth gopïo ein fformiwla i lawr i weddill y celloedd yng ngholofn D3:D30.
- Rhif_colofn (Rhif mynegai colofn) – rhif cyfresol (nid llythyren!) o'r golofn yn y rhestr brisiau y byddwn yn cymryd gwerthoedd pris ohoni. Mae colofn gyntaf y rhestr brisiau gyda'r enwau wedi ei rhifo 1, felly mae angen y pris o'r golofn 2.
- egwyl_lookup (Amrediad Ystod) – dim ond dau werth y gellir eu nodi yn y maes hwn: GAU neu WIR:
- Os rhoddir gwerth 0 or GORWEDD (GAU), yna mewn gwirionedd mae hyn yn golygu mai dim ond chwilio a ganiateir union gyfatebiaeth, hy os nad yw'r swyddogaeth yn dod o hyd i'r eitem ansafonol a nodir yn y tabl archeb yn y rhestr brisiau (os yw "Coconut" yn cael ei nodi, er enghraifft), bydd yn cynhyrchu'r gwall # N/A (dim data).
- Os rhoddir gwerth 1 or TRUE (CYWIR), yna mae hyn yn golygu eich bod yn caniatáu i'r chwiliad nid am yr union, ond cyfateb yn fras, hy yn achos “cnau coco”, bydd y swyddogaeth yn ceisio dod o hyd i gynnyrch ag enw sydd mor agos â phosibl at “gnau coco” a dychwelyd y pris ar gyfer yr enw hwn. Yn y rhan fwyaf o achosion, gall amnewidiad bras o'r fath chwarae tric ar y defnyddiwr trwy amnewid gwerth y cynnyrch anghywir a oedd yno mewn gwirionedd! Felly ar gyfer y rhan fwyaf o broblemau busnes go iawn, mae'n well peidio â chaniatáu chwilio bras. Yr eithriad yw pan fyddwn yn chwilio am rifau ac nid testun – er enghraifft, wrth gyfrifo Gostyngiadau Cam.
Popeth! Erys i'r wasg OK a chopïwch y swyddogaeth a gofnodwyd i'r golofn gyfan.
# N/A gwallau a'u hattal
swyddogaeth VPR (VLOOKUP) yn dychwelyd #N/A gwall (#D/A) os a:
- Chwilio union wedi'i alluogi (dadl Gwedd cyfwng = 0) ac nid yw yr enw dymunol yn Tabl.
- Chwiliad bras wedi'i gynnwys (Gwedd cyfwng = 1), Ond Tabl, lle mae'r chwiliad yn digwydd heb ei drefnu yn nhrefn esgynnol enwau.
- Mae fformat y gell lle mae gwerth gofynnol yr enw yn dod (er enghraifft, B3 yn ein hachos ni) a fformat celloedd colofn gyntaf (F3: F19) y tabl yn wahanol (er enghraifft, rhifol a thestun ). Mae'r achos hwn yn arbennig o nodweddiadol wrth ddefnyddio codau rhifol (rhifau cyfrif, dynodwyr, dyddiadau, ac ati) yn lle enwau testun. Yn yr achos hwn, gallwch ddefnyddio'r swyddogaethau Ч и TEXT i drosi fformatau data. Bydd yn edrych rhywbeth fel hyn:
=VLOOKUP(TEXT(B3),pris,0)
Gallwch ddarllen mwy am hyn yma.
- Ni all y swyddogaeth ddod o hyd i'r gwerth gofynnol oherwydd bod y cod yn cynnwys bylchau neu nodau anweledig na ellir eu hargraffu (toriadau llinell, ac ati). Yn yr achos hwn, gallwch ddefnyddio swyddogaethau testun Torrwch (TRIM) и PRINT(GLÂN) i gael gwared arnynt:
=VLOOKUP(TRIMSPACES(GLAN(B3)), pris,0)
=VLOOKUP(TRIM(GLAN(B3)); pris; 0)
I atal y neges gwall # N / A (#D/A) mewn achosion lle na all y swyddogaeth ddod o hyd i gydweddiad union, gallwch ddefnyddio'r swyddogaeth IFERROR (IFERROR)… Felly, er enghraifft, mae'r adeiladwaith hwn yn rhyng-gipio unrhyw wallau a gynhyrchir gan y VLOOKUP ac yn eu disodli â sero:
= IFERROR (VLOOKUP (B3, pris, 2, 0), 0)
= IFERROR (VLOOKUP(B3; pris; 2; 0); 0)
PS
Os oes angen i chi dynnu nid un gwerth, ond y set gyfan ar unwaith (os oes sawl un gwahanol), yna bydd yn rhaid i chi siamanu gyda'r fformiwla arae. neu defnyddiwch y nodwedd XLOOKUP newydd o Office 365.
- Fersiwn well o'r swyddogaeth VLOOKUP (VLOOKUP 2).
- Cyfrifo gostyngiadau cam (ystod) yn gyflym gan ddefnyddio'r swyddogaeth VLOOKUP.
- Sut i wneud “VLOOKUP chwith” gan ddefnyddio'r swyddogaethau INDEX a MATCH
- Sut i ddefnyddio'r swyddogaeth VLOOKUP i lenwi'r ffurflenni gyda data o'r rhestr
- Sut i dynnu allan nid y cyntaf, ond yr holl werthoedd o'r tabl ar unwaith
- Mae VLOOKUP2 a VLOOKUP3 yn gweithredu o'r ategyn PLEX