Enghreifftiau VLOOKUP Uwch: Chwiliad Aml-Feini Prawf

Yn ail ran ein tiwtorial ar y swyddogaeth VPR (VLOOKUP) yn Excel, byddwn yn dadansoddi ychydig o enghreifftiau a fydd yn eich helpu i gyfeirio'r holl bŵer VPR i ddatrys y tasgau Excel mwyaf uchelgeisiol. Mae'r enghreifftiau'n cymryd yn ganiataol bod gennych eisoes wybodaeth sylfaenol am sut mae'r nodwedd hon yn gweithio. Os na, efallai y byddai gennych ddiddordeb mewn dechrau gyda rhan gyntaf y tiwtorial hwn, sy'n esbonio'r gystrawen a'r defnydd sylfaenol. VPR. Wel, gadewch i ni ddechrau.

• Chwiliwch yn Excel yn ôl meini prawf lluosog
• Rydym yn echdynnu'r gwerthoedd 2il, 3ydd, ac ati gan ddefnyddio VLOOKUP
• Adalw pob ailadroddiad o'r gwerth dymunol
• Chwiliad XNUMXD yn ôl rhes a cholofn hysbys
• Defnyddio VLOOKUP lluosog mewn un fformiwla
• Amnewid deinamig o ddata o dablau gwahanol

Chwiliwch yn Excel yn ôl meini prawf lluosog

swyddogaeth VPR yn Excel yn arf pwerus iawn ar gyfer perfformio chwiliadau am werth penodol mewn cronfa ddata. Fodd bynnag, mae cyfyngiad sylweddol - mae ei gystrawen yn caniatáu ichi chwilio am un gwerth yn unig. Beth os ydych am chwilio yn ôl amodau lluosog? Fe welwch yr ateb isod.

Enghraifft 1: Chwilio yn ôl 2 faen prawf gwahanol

Tybiwch fod gennym restr o orchmynion ac rydym am ddod o hyd iddynt Nifer y nwyddau (Qty.), yn seiliedig ar ddau faen prawf - Enw cleient (Cwsmer) и Enw'r cynnyrch (Cynnyrch). Mae'r mater yn cael ei gymhlethu gan y ffaith bod pob un o'r prynwyr wedi archebu sawl math o nwyddau, fel y gwelir yn y tabl isod:

swyddogaeth rheolaidd VPR ni fydd yn gweithio yn y senario hwn oherwydd bydd yn dychwelyd y gwerth cyntaf y mae'n ei ddarganfod sy'n cyfateb i'r gwerth chwilio a roddwyd. Er enghraifft, os ydych am wybod faint o eitem melysion'archebu gan y prynwr Jeremy Hill, ysgrifennwch y fformiwla ganlynol:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

– bydd y fformiwla hon yn dychwelyd y canlyniad 15sy'n cyfateb i'r cynnyrch afalau, oherwydd dyma'r gwerth cyntaf sy'n cyfateb.

Mae yna ateb syml - crëwch golofn ychwanegol i gyfuno'r holl feini prawf dymunol ynddi. Yn ein hesiampl ni, dyma'r colofnau Enw cleient (Cwsmer) и Enw'r cynnyrch (Cynnyrch). Peidiwch ag anghofio bod yn rhaid i'r golofn gyfun fod y golofn ar y chwith yn yr ystod chwilio bob amser, gan mai'r golofn chwith y mae'r ffwythiant VPR yn edrych i fyny wrth chwilio am werth.

Felly, rydych chi'n ychwanegu colofn ategol at y tabl ac yn copïo'r fformiwla ganlynol dros ei holl gelloedd: =B2&C2. Os ydych chi am i'r llinyn fod yn fwy darllenadwy, gallwch wahanu'r gwerthoedd cyfun gyda bwlch: =B2&» «&C2. Ar ôl hynny, gallwch ddefnyddio'r fformiwla ganlynol:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

Ble mae'r gell B1 yn cynnwys gwerth cydgynhwysol y ddadl lookup_value (lookup_value) a 4 - Dadl col_index_num (column_number), hy rhif y golofn sy'n cynnwys y data i'w hadalw.

Enghraifft 2: VLOOKUP fesul dau faen prawf gyda'r tabl yn cael ei weld ar ddalen arall

Os oes angen i chi ddiweddaru'r prif dabl (Prif dabl) trwy ychwanegu data o'r ail dabl (Tabl edrych), sydd wedi'i leoli ar ddalen arall neu mewn llyfr gwaith Excel arall, yna gallwch chi gasglu'r gwerth a ddymunir yn uniongyrchol yn y fformiwla rydych chi'n ei fewnosod i mewn i'r prif fwrdd.

Fel yn yr enghraifft flaenorol, bydd angen colofn ategol yn y tabl Chwilio gyda'r gwerthoedd cyfun. Rhaid mai'r golofn hon yw'r golofn fwyaf chwith yn yr ystod chwilio.

Felly y fformiwla gyda VPR gallai fod fel hyn:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

Yma, mae colofnau B ac C yn cynnwys enwau cwsmeriaid ac enwau cynnyrch, yn y drefn honno, a'r ddolen Archebion!$A&$2:$D$2 yn diffinio tabl i edrych i fyny mewn taflen arall.

I wneud y fformiwla yn fwy darllenadwy, gallwch chi roi enw i'r ystod golygfa, ac yna bydd y fformiwla'n edrych yn llawer symlach:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

Er mwyn i'r fformiwla weithio, rhaid cyfuno'r gwerthoedd yng ngholofn fwyaf chwith y tabl rydych chi'n edrych arno yn union yr un ffordd ag yn y meini prawf chwilio. Yn y ffigur uchod, fe wnaethom gyfuno'r gwerthoedd u2bu2band rhoi gofod rhyngddynt, yn yr un modd y mae angen i chi ei wneud yn y ddadl gyntaf o'r swyddogaeth (BXNUMX & “” & CXNUMX).

Cofiwch! swyddogaeth VPR yn gyfyngedig i 255 nod, ni all chwilio am werth sy'n fwy na 255 nod o hyd. Cadwch hyn mewn cof a gwnewch yn siŵr nad yw hyd y gwerth a ddymunir yn fwy na'r terfyn hwn.

Cytunaf nad ychwanegu colofn ategol yw'r ateb mwyaf cain ac nid yw bob amser yn dderbyniol. Gallwch wneud yr un peth heb y golofn helpwr, ond byddai hynny'n gofyn am fformiwla lawer mwy cymhleth gyda chyfuniad o swyddogaethau MYNEGAI (MYNEGAI) a MATCH (MWY YN DDIOGEL).

Rydym yn echdynnu'r gwerthoedd 2il, 3ydd, ac ati gan ddefnyddio VLOOKUP

Rydych chi eisoes yn gwybod hynny VPR yn gallu dychwelyd dim ond un gwerth cyfatebol, yn fwy manwl gywir, yr un cyntaf a ddarganfuwyd. Ond beth os yw'r gwerth hwn yn cael ei ailadrodd sawl gwaith yn yr arae a welwyd, a'ch bod am dynnu'r 2il neu'r 3ydd ohonynt? Beth os yw pob gwerth? Mae'r broblem yn ymddangos yn gymhleth, ond mae'r ateb yn bodoli!

Tybiwch fod un golofn o'r tabl yn cynnwys enwau'r cwsmeriaid (Enw Cwsmer), ac mae'r golofn arall yn cynnwys y cynhyrchion (Cynnyrch) a brynwyd ganddynt. Gadewch i ni geisio dod o hyd i'r 2il, 3ydd a 4ydd eitemau a brynwyd gan gwsmer penodol.

Y ffordd hawsaf yw ychwanegu colofn ategol cyn y golofn enw cwsmer a'i lenwi ag enwau cwsmeriaid gyda rhif ailadrodd pob enw, er enghraifft, John Doe1, John Doe2 ac ati Byddwn yn gwneud y tric gyda rhifo gan ddefnyddio'r ffwythiant COUNTIF (COUNTIF), o gofio bod enwau'r cwsmeriaid yng ngholofn B:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

Ar ôl hynny gallwch chi ddefnyddio'r swyddogaeth arferol VPRi ddod o hyd i'r gorchymyn gofynnol. Er enghraifft:

• Dod o hyd i 2-ed eitem a archebwyd gan y cwsmer Dan Brown:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• Dod o hyd i 3-ed eitem a archebwyd gan y cwsmer Dan Brown:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

Mewn gwirionedd, gallwch chi nodi cyfeirnod cell fel y gwerth chwilio yn lle testun, fel y dangosir yn y ffigur canlynol:

Os mai dim ond chwilio am 2-e ailadrodd, gallwch ei wneud heb y golofn ategol trwy greu fformiwla fwy cymhleth:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

Yn y fformiwla hon:

• $ F $ 2 – cell sy'n cynnwys enw'r prynwr (nid yw wedi newid, nodwch – mae'r ddolen yn absoliwt);
• $B$ - colofn enw cwsmer;
• Table4 – Eich bwrdd (gall y lle hwn hefyd fod yn ystod reolaidd);
• $ C16 – cell ddiwedd eich bwrdd neu ystod.

Mae'r fformiwla hon yn dod o hyd i'r ail werth cyfatebol yn unig. Os oes angen i chi dynnu'r ailadroddiadau sy'n weddill, defnyddiwch yr ateb blaenorol.

Os oes angen rhestr o'r holl gemau arnoch chi - y swyddogaeth VPR nid yw hwn yn gynorthwyydd, gan mai dim ond un gwerth y mae'n ei ddychwelyd ar y tro - cyfnod. Ond mae gan Excel swyddogaeth MYNEGAI (MYNEGAI), sy'n gallu ymdopi â'r dasg hon yn hawdd. Sut olwg fydd ar fformiwla o'r fath, byddwch yn dysgu yn yr enghraifft ganlynol.

Adalw pob ailadroddiad o'r gwerth dymunol

Fel y crybwyllwyd uchod VPR ni all echdynnu'r holl werthoedd dyblyg o'r ystod wedi'i sganio. I wneud hyn, mae angen fformiwla ychydig yn fwy cymhleth arnoch, sy'n cynnwys sawl swyddogaeth Excel, megis MYNEGAI (MYNEGAI), BACH (BACH) a ROW (LLINELL)

Er enghraifft, mae'r fformiwla isod yn canfod pob ailadroddiad o'r gwerth o gell F2 yn yr ystod B2: B16 ac yn dychwelyd y canlyniad o'r un rhesi yng ngholofn C.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

Rhowch y fformiwla arae hon i mewn i gelloedd cyfagos lluosog, fel y celloedd Dd4: F8fel y dangosir yn y ffigur isod. Rhaid i nifer y celloedd fod yn hafal i neu'n fwy na'r nifer mwyaf posibl o ailadroddiadau o'r gwerth a chwiliwyd. Peidiwch ag anghofio clicio Ctrl + Shift + Enteri fynd i mewn i'r fformiwla arae yn gywir.

Os oes gennych ddiddordeb mewn deall sut mae'n gweithio, gadewch i ni blymio i fanylion y fformiwla ychydig:

1 Rhan:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 – cymharwch y gwerth yng nghell F2 â phob un o'r gwerthoedd yn yr ystod B2:B16. Os canfyddir cyfatebiaeth, yna'r mynegiad STRING(C2:C16)-1 yn dychwelyd rhif y llinell gyfatebol (gwerth -1 yn caniatáu ichi beidio â chynnwys y llinell pennawd). Os nad oes cyfatebiaeth, y swyddogaeth IF (IF) yn dychwelyd llinyn gwag.

Canlyniad swyddogaeth IF (IF) bydd arae llorweddol o'r fath: {1,"",3,"",5,"","","","","","",12,"","",""}

2 Rhan:

ROW()-3

СТРОКА()-3

Yma y swyddogaeth ROW (LLINELL) yn gweithredu fel cownter ychwanegol. Gan fod y fformiwla'n cael ei chopïo i gelloedd F4:F9, rydyn ni'n tynnu'r rhif 3 o ganlyniad swyddogaeth i gael gwerth 1 yn y gell F4 (llinell 4, tynnu 3) i gael 2 yn y gell F5 (llinell 5, tynnu 3) ac ati.

3 Rhan:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

swyddogaeth BACH (BACH) yn dychwelyd n-oh y gwerth lleiaf yn yr arae ddata. Yn ein hachos ni, pa safle (o'r lleiaf) i ddychwelyd sy'n cael ei bennu gan y swyddogaeth ROW (LLINELL) (gweler Rhan 2). Felly, am gell F4 swyddogaeth BACH({array},1) Ffurflenni 1-ed (lleiaf) arae elfen, h.y 1. Ar gyfer cell F5 Ffurflenni 2-ed yr elfen leiaf yn yr arae, hynny yw 3, Ac ati

4 Rhan:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

swyddogaeth MYNEGAI (MYNEGAI) yn syml yn dychwelyd gwerth cell benodol mewn arae C2:C16. Ar gyfer cell F4 swyddogaeth MYNEGAI($C$2:$C$16) yn dychwelyd afalauAm F5 swyddogaeth MYNEGAI($C$2:$C$16) yn dychwelyd melysion' ac yn y blaen.

5 Rhan:

IFERROR()

ЕСЛИОШИБКА()

Yn olaf, rydyn ni'n rhoi'r fformiwla y tu mewn i'r swyddogaeth IFERROR (IFERROR), oherwydd mae'n annhebygol y byddwch yn fodlon ar y neges gwall #AT (#N/A) os yw nifer y celloedd y mae'r fformiwla wedi'i chopïo iddynt yn llai na nifer y gwerthoedd dyblyg yn yr ystod sy'n cael ei gweld.

Chwiliad XNUMXD yn ôl rhes a cholofn hysbys

Mae perfformio chwiliad XNUMXD yn Excel yn golygu chwilio am werth yn ôl rhif rhes a cholofn hysbys. Mewn geiriau eraill, rydych chi'n echdynnu gwerth y gell ar groesffordd rhes a cholofn benodol.

Felly, gadewch i ni droi at ein tabl ac ysgrifennu fformiwla gyda swyddogaeth VPR, a fydd yn dod o hyd i wybodaeth am gost lemonau a werthwyd ym mis Mawrth.

Mae yna sawl ffordd i berfformio chwiliad XNUMXD. Edrychwch ar yr opsiynau a dewiswch yr un sydd fwyaf addas i chi.

Swyddogaethau VLOOKUP a MATCH

Gallwch ddefnyddio criw o swyddogaethau VPR (VLOOKUP) a MWY AMLWG (MATCH) i ddarganfod y gwerth ar groesffordd y meysydd Enw'r cynnyrch (llinyn) a Mis (colofn) yr arae dan sylw:

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

Mae'r fformiwla uchod yn swyddogaeth reolaidd VPR, sy'n edrych am union gyfatebiad o'r gwerth “Lemonau” yng nghelloedd A2 trwy A9. Ond gan nad ydych chi'n gwybod ym mha golofn y mae gwerthiannau mis Mawrth, ni fyddwch yn gallu gosod rhif y golofn ar gyfer dadl y drydedd swyddogaeth. VPR. Yn lle hynny, defnyddir y swyddogaeth MWY AMLWGi ddiffinio'r golofn hon.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

Wedi'i chyfieithu i iaith ddynol, mae'r fformiwla hon yn golygu:

• Rydym yn chwilio am y cymeriadau “Mar” – dadl lookup_value (lookup_value);
• Edrych mewn celloedd o A1 i I1 – dadl chwilio_array (lookup_array);
• Dychwelyd cyfatebiaeth union - dadl math_mathau (match_match).

Defnyddio 0 yn y drydedd ddadl, rydych yn dweud swyddogaethau MWY AMLWG edrychwch am y gwerth cyntaf sy'n cyfateb yn union i'r gwerth rydych chi'n chwilio amdano. Mae hyn yn cyfateb i'r gwerth Anghywir (GAU) ar gyfer y bedwaredd ddadl VPR.

Dyma sut y gallwch chi greu fformiwla chwilio dwy ffordd yn Excel, a elwir hefyd yn chwiliad dau ddimensiwn neu chwiliad deugyfeiriadol.

Swyddogaeth SUMPRODUCT

swyddogaeth SUMPRODUCT (SUMPRODUCT) yn dychwelyd swm cynhyrchion yr araeau a ddewiswyd:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

MYNEGAI a swyddogaethau MATCH

Yn yr erthygl nesaf byddaf yn esbonio'r swyddogaethau hyn yn fanwl, felly am y tro gallwch chi gopïo'r fformiwla hon:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

Ystodau a enwir a'r gweithredwr croestoriad

Os nad ydych chi'n rhan o'r holl fformiwlâu Excel cymhleth hynny, efallai yr hoffech chi'r ffordd weledol a chofiadwy hon:

1. Dewiswch y tabl, agorwch y tab Fformiwlâu (Fformiwlâu) a chliciwch Creu o Ddethol (Creu o ddetholiad).
2. Gwiriwch y blychau Rhes uchaf (ar y llinell uchod) a Y golofn chwith (yn y golofn ar y chwith). Bydd Microsoft Excel yn aseinio enwau i'r ystodau o'r gwerthoedd yn rhes uchaf a cholofn chwith eich taenlen. Nawr gallwch chi chwilio gan ddefnyddio'r enwau hyn yn uniongyrchol heb greu fformiwlâu.
3. Mewn unrhyw gell wag, ysgrifennwch = rhes_enw colofn_enw, er enghraifft fel hyn:

   = Lemoniaid Mar

   … neu i'r gwrthwyneb:

   = Mar Lemons

   Cofiwch fod yn rhaid i'r enwau rhes a cholofn gael eu gwahanu gan ofod, sydd yn yr achos hwn yn gweithio fel y gweithredwr croestoriad.

Pan fyddwch chi'n nodi enw, bydd Microsoft Excel yn dangos cyngor gyda rhestr o enwau cyfatebol, yn union fel pan fyddwch chi'n nodi fformiwla.

4. Pwyswch Rhowch a gwirio'r canlyniad

Yn gyffredinol, pa un bynnag o'r dulliau uchod a ddewiswch, bydd canlyniad chwiliad dau-ddimensiwn yr un peth:

Defnyddio VLOOKUP lluosog mewn un fformiwla

Mae'n digwydd nad oes gan y prif dabl a'r tabl chwilio un golofn yn gyffredin, ac mae hyn yn eich atal rhag defnyddio'r swyddogaeth arferol VPR. Fodd bynnag, mae tabl arall nad yw'n cynnwys y wybodaeth y mae gennym ddiddordeb ynddi, ond mae ganddo golofn gyffredin gyda'r prif dabl a'r tabl chwilio.

Gadewch i ni edrych ar yr enghraifft ganlynol. Mae gennym Brif fwrdd gyda cholofn SKU (newydd), lle rydych chi am ychwanegu colofn gyda'r prisiau cyfatebol o dabl arall. Yn ogystal, mae gennym 2 dabl chwilio. Mae'r un cyntaf (Tabl Edrych 1) yn cynnwys rhifau wedi'u diweddaru SKU (newydd) ac enwau cynnyrch, a'r ail (Tabl edrych 2) – enwau cynnyrch a hen rifau SKU (hen).

I ychwanegu prisiau o'r ail dabl chwilio i'r prif dabl, rhaid i chi berfformio gweithred a elwir yn ddwbl VPR neu nythu VPR.

1. Ysgrifennwch ffwythiant VPR, sy'n dod o hyd i enw'r cynnyrch yn y tabl Edrych tabl 1defnyddio SKU, fel y gwerth dymunol:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   Yma Newydd_SKU – ystod a enwir $A:$B yn y bwrdd Edrych tabl 1, 2 – colofn B yw hon, sy’n cynnwys enwau’r nwyddau (gweler y llun uchod)

2. Ysgrifennwch fformiwla i fewnosod prisiau o dabl Edrych tabl 2 yn seiliedig ar enwau cynnyrch adnabyddus. I wneud hyn, gludwch y fformiwla a grëwyd gennych yn gynharach fel y gwerth chwilio ar gyfer y swyddogaeth newydd VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   Yma Pris – ystod a enwir $ A: $ C. yn y bwrdd Edrych tabl 2, 3 yw colofn C yn cynnwys prisiau.

Mae’r ffigur isod yn dangos y canlyniad a ddychwelwyd gan y fformiwla a grëwyd gennym:

Amnewid deinamig o ddata o wahanol dablau gan ddefnyddio VLOOKUP ac INDIRECT

Yn gyntaf, gadewch i ni egluro'r hyn a olygwn wrth yr ymadrodd “Amnewid deinamig data o wahanol dablau” i wneud yn siŵr ein bod yn deall ein gilydd yn gywir.

Mae yna sefyllfaoedd pan fo sawl dalen gyda data o'r un fformat, ac mae angen tynnu'r wybodaeth angenrheidiol o ddalen benodol, yn dibynnu ar y gwerth sy'n cael ei nodi mewn cell benodol. Rwy'n meddwl ei bod yn haws esbonio hyn gydag enghraifft.

Dychmygwch fod gennych adroddiadau gwerthiant ar gyfer sawl rhanbarth gyda'r un cynhyrchion ac yn yr un fformat. Rydych chi eisiau dod o hyd i ffigurau gwerthiant ar gyfer rhanbarth penodol:

Os mai dim ond dau adroddiad o'r fath sydd gennych, yna gallwch ddefnyddio fformiwla warthus o syml gyda swyddogaethau VPR и IF (IF) i ddewis yr adroddiad dymunol i'w chwilio:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

ble:

• $D$2 yn gell sy'n cynnwys enw'r cynnyrch. Sylwch ein bod yn defnyddio cyfeiriadau absoliwt yma i osgoi newid y gwerth am-edrych wrth gopïo'r fformiwla i gelloedd eraill.
• $ D3 yn gell ag enw'r rhanbarth. Rydym yn defnyddio cyfeirnod colofn absoliwt a chyfeirnod rhes cymharol oherwydd ein bod yn bwriadu copïo'r fformiwla i gelloedd eraill yn yr un golofn.
• FL_Sales и CA_Sales – enwau'r tablau (neu'r ystodau a enwir) sy'n cynnwys yr adroddiadau gwerthu cyfatebol. Gallwch, wrth gwrs, ddefnyddio'r enwau dalennau arferol a chyfeiriadau ystod celloedd, er enghraifft 'Taflen FL'!$A$3:$B$10, ond mae ystodau a enwir yn llawer mwy cyfleus.

Fodd bynnag, pan fo llawer o dablau o'r fath, y swyddogaeth IF nid dyma'r ateb gorau. Yn lle hynny, gallwch ddefnyddio'r swyddogaeth INDIRECT (INDIRECT) i ddychwelyd yr ystod chwilio a ddymunir.

Fel y gwyddoch yn ôl pob tebyg, y swyddogaeth INDIRECT yn cael ei ddefnyddio i ddychwelyd dolen a roddir gan linyn testun, sef yr union beth sydd ei angen arnom nawr. Felly, yn eofn disodli yn y fformiwla uchod y mynegiant gyda'r swyddogaeth IF i gysylltu â swyddogaeth INDIRECT. Dyma gyfuniad VPR и INDIRECT yn gweithio'n wych gyda:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

ble:

• $D$2 – cell yw hon gydag enw'r cynnyrch, nid yw wedi newid oherwydd y cyswllt absoliwt.
• $ D3 yw'r gell sy'n cynnwys rhan gyntaf enw'r rhanbarth. Yn ein hesiampl, hyn FL.
• _Gwerthiant – rhan gyffredin enw pob ystod neu dabl a enwir. O'i gyfuno â'r gwerth yng nghell D3, mae'n ffurfio enw cwbl gymwys yr ystod ofynnol. Isod mae rhai manylion ar gyfer y rhai sy'n newydd i'r swyddogaeth INDIRECT.

Sut mae INDIRECT a VLOOKUP yn gweithio

Yn gyntaf, gadewch imi eich atgoffa cystrawen y swyddogaeth INDIRECT (INDIRECT):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

Gall y ddadl gyntaf fod yn gyfeirnod cell (arddull A1 neu R1C1), yn enw amrediad, neu'n llinyn testun. Mae'r ail ddadl yn pennu pa arddull cyswllt a gynhwysir yn y ddadl gyntaf:

• A1os yw'r ddadl CÔD GWIR (TRUE) neu heb ei nodi;
• R1C1, Os FAS E (GAU).

Yn ein hachos ni, mae gan y ddolen yr arddull A1, felly gallwch chi adael yr ail ddadl allan a chanolbwyntio ar y gyntaf.

Felly gadewch i ni fynd yn ôl at ein hadroddiadau gwerthu. Os cofiwch, yna mae pob adroddiad yn dabl ar wahân wedi'i leoli ar ddalen ar wahân. Er mwyn i'r fformiwla weithio'n gywir, rhaid i chi enwi eich tablau (neu ystodau), a rhaid i bob enw gael rhan gyffredin. Er enghraifft, fel hyn: CA_Sales, FL_Sales, TX_Gwerthiant ac yn y blaen. Fel y gwelwch, mae “_Sales” yn bresennol yn yr holl enwau.

swyddogaeth INDIRECT yn cysylltu'r gwerth yng ngholofn D a'r llinyn testun “_Sales”, a thrwy hynny yn dweud VPR ym mha fwrdd i chwilio. Os yw cell D3 yn cynnwys y gwerth “FL”, bydd y fformiwla yn chwilio'r tabl FL_Sales, os “CA” – yn y tabl CA_Sales ac yn y blaen.

Canlyniad y swyddogaethau VPR и INDIRECT fydd y canlynol:

Os yw'r data wedi'i leoli mewn gwahanol lyfrau Excel, yna mae angen i chi ychwanegu enw'r llyfr cyn yr ystod a enwir, er enghraifft:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

Os yw'r swyddogaeth INDIRECT yn cyfeirio at lyfr gwaith arall, rhaid i'r llyfr gwaith hwnnw fod yn agored. Os caiff ei gau, bydd y swyddogaeth yn adrodd am wall. #REF! (#SSYL!).