Dæmi um háþróaða uppflettingu: Fjölþætta leit

Í seinni hluta kennslunnar okkar um aðgerðina VPR (VLOOKUP) í Excel munum við greina nokkur dæmi sem hjálpa þér að beina öllu afli VPR til að leysa metnaðarfyllstu Excel verkefnin. Dæmin gera ráð fyrir að þú hafir nú þegar grunnþekkingu á því hvernig þessi eiginleiki virkar. Ef ekki, gætirðu haft áhuga á að byrja á fyrsta hluta þessarar kennslu, sem útskýrir setningafræði og grunnnotkun. VPR. Jæja, við skulum byrja.

• Leitaðu í Excel eftir mörgum forsendum
• Við tökum út 2nd, 3rd, etc. gildin með því að nota VLOOKUP
• Sæktu allar endurtekningar af æskilegu gildi
• XNUMXD leit eftir þekktri röð og dálki
• Notkun margra VLOOKUPs í einni formúlu
• Dýnamísk skipting gagna úr mismunandi töflum

Leitaðu í Excel eftir mörgum forsendum

virka VPR í Excel er mjög öflugt tæki til að framkvæma leit að tilteknu gildi í gagnagrunni. Hins vegar er veruleg takmörkun - setningafræði þess gerir þér kleift að leita að einu gildi. Hvað ef þú vilt leita eftir mörgum skilyrðum? Þú finnur lausnina hér að neðan.

Dæmi 1: Leitaðu eftir 2 mismunandi forsendum

Segjum að við höfum lista yfir pantanir og við viljum finna Magn vöru (Magn.), byggt á tveimur forsendum - Nafn viðskiptavinar (Viðskiptavinur) и Название продукта (Vöru). Málið flækist af því að hver kaupandi pantaði nokkrar tegundir af vörum eins og sjá má af töflunni hér að neðan:

regluleg virkni VPR mun ekki virka í þessari atburðarás vegna þess að það mun skila fyrsta gildinu sem það finnur sem passar við tiltekið uppflettingargildi. Til dæmis, ef þú vilt vita magn vöru sælgæti'pantað af kaupanda Jeremy Hill, skrifaðu eftirfarandi formúlu:

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

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

– þessi formúla mun skila niðurstöðunni 15sem samsvarar vörunni epli, vegna þess að það er fyrsta gildið sem passar.

Það er einföld lausn - búðu til viðbótardálk þar sem hægt er að sameina öll þau skilyrði sem óskað er eftir. Í okkar dæmi eru þetta dálkarnir Nafn viðskiptavinar (Viðskiptavinur) и Название продукта (Vöru). Ekki gleyma því að sameinaður dálkur verður alltaf að vera dálkurinn lengst til vinstri í leitarsviðinu, þar sem það er vinstri dálkurinn sem fallið VPR lítur upp þegar leitað er að virði.

Svo þú bætir aukadálki við töfluna og afritar eftirfarandi formúlu yfir allar frumur hennar: =B2&C2. Ef þú vilt að strengurinn sé læsilegri geturðu aðskilið sameinuðu gildin með bili: =B2&» «&C2. Eftir það geturðu notað eftirfarandi formúlu:

=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;ЛОЖЬ)

Hvar er fruman B1 inniheldur samtengd gildi röksemdafærslunnar lookup_value (uppflettingargildi) og 4 — Rök col_index_numr (dálkanúmer), þ.e. númer dálksins sem inniheldur gögnin sem á að sækja.

Dæmi 2: VLOOKUP eftir tveimur forsendum þar sem töflu er skoðuð á öðru blaði

Ef þú þarft að uppfæra aðaltöfluna (aðaltöflu) með því að bæta við gögnum úr annarri töflunni (uppflettitöflu), sem er staðsett á öðru blaði eða í annarri Excel vinnubók, þá geturðu safnað viðeigandi gildi beint í formúluna sem þú setur inn inn á aðaltöfluna.

Eins og í fyrra dæmi, þá þarftu aukadálk í uppflettitöflunni með samanlögðum gildum. Þessi dálkur verður að vera dálkurinn lengst til vinstri á leitarsviðinu.

Svo formúlan með VPR gæti verið svona:

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

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

Hér innihalda dálkur B og C nöfn viðskiptavina og vöruheiti, í sömu röð, og tengilinn Pantanir!$A&$2:$D$2 skilgreinir töflu til að fletta upp í öðru blaði.

Til að gera formúluna læsilegri geturðu gefið útsýnisviðinu nafn og þá mun formúlan líta mun einfaldari út:

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

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

Til að formúlan virki verða gildin í dálknum lengst til vinstri í töflunni sem þú ert að skoða að vera sameinuð á nákvæmlega sama hátt og í leitarskilyrðunum. Á myndinni hér að ofan sameinuðum við gildin u2bu2band og settum bil á milli þeirra, á sama hátt og þú þarft að gera í fyrstu röksemdum fallsins (BXNUMX& “” & CXNUMX).

Mundu! virka VPR takmarkað við 255 stafi getur það ekki leitað að gildi sem er meira en 255 stafir að lengd. Hafðu þetta í huga og vertu viss um að lengd æskilegs gildis fari ekki yfir þessi mörk.

Ég er sammála því að það að bæta við aukadálki er ekki glæsilegasta og ekki alltaf ásættanlega lausnin. Þú getur gert það sama án hjálparsúlunnar, en það myndi krefjast miklu flóknari formúlu með blöndu af aðgerðum INDEX (VÍSITALA) og MATCH (MEIRA FYRIR).

Við tökum út 2nd, 3rd, etc. gildin með því að nota VLOOKUP

Þú veist það þegar VPR getur aðeins skilað einu samsvarandi gildi, nánar tiltekið, því fyrsta sem fannst. En hvað ef þetta gildi er endurtekið nokkrum sinnum í skoðaðu fylkinu og þú vilt draga út annað eða þriðja þeirra? Hvað ef öll gildi? Vandamálið virðist flókið, en lausnin er til!

Segjum sem svo að annar dálkur töflunnar innihaldi nöfn viðskiptavina (nafn viðskiptavinar) og hinn dálkurinn innihaldi vörurnar (vara) sem þeir keyptu. Við skulum reyna að finna 2., 3. og 4. hlutinn sem tiltekinn viðskiptavinur keypti.

Auðveldasta leiðin er að bæta aukadálki á undan dálknum Nafn viðskiptavinar og fylltu það með nöfnum viðskiptavina með endurtekningarnúmeri hvers nafns, til dæmis, John Doe 1, John Doe 2 osfrv. Við munum gera bragðið með því að númera með því að nota fallið COUNTIF (COUNTIF), í ljósi þess að nöfn viðskiptavina eru í dálki B:

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

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

Eftir það geturðu notað venjulega aðgerðina VPRtil að finna nauðsynlega pöntun. Til dæmis:

• finna 2-þ hlutur sem viðskiptavinurinn pantaði Dan Brown:

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

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

• finna 3-þ hlutur sem viðskiptavinurinn pantaði Dan Brown:

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

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

Reyndar geturðu slegið inn frumutilvísun sem uppflettingargildi í stað texta, eins og sýnt er á eftirfarandi mynd:

Ef þú ert aðeins að leita að 2-e endurtekningu, þú getur gert það án hjálparsúlunnar með því að búa til flóknari formúlu:

=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;ИСТИНА);"")

Í þessari formúlu:

• $F$2 - reit sem inniheldur nafn kaupanda (það er óbreytt, vinsamlegast athugið - hlekkurinn er alger);
• $ B $ – dálkur Nafn viðskiptavinar;
• Table4 – Borðið þitt (þessi staður getur líka verið venjulegur svið);
• $ C16 – lokahólf töflunnar eða sviðsins.

Þessi formúla finnur aðeins annað samsvarandi gildi. Ef þú þarft að draga út endurtekningarnar sem eftir eru skaltu nota fyrri lausnina.

Ef þú þarft lista yfir allar samsvörun - aðgerðin VPR þetta er ekki hjálpartæki, þar sem það skilar aðeins einu gildi í einu - punktur. En Excel hefur aðgerð INDEX (INDEX), sem getur auðveldlega tekist á við þetta verkefni. Hvernig slík formúla mun líta út, munt þú læra í eftirfarandi dæmi.

Sæktu allar endurtekningar af æskilegu gildi

Eins og áður sagði VPR getur ekki dregið út öll tvítekin gildi úr skannaða sviðinu. Til að gera þetta þarftu aðeins flóknari formúlu, sem samanstendur af nokkrum Excel aðgerðum, svo sem INDEX (VÍSITALA), SMALL (LÍTILL) og UMFERÐ (LINE)

Til dæmis finnur formúlan hér að neðan allar endurtekningar á gildinu úr reit F2 á bilinu B2:B16 og skilar niðurstöðunni úr sömu línum í dálki 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));"")}

Sláðu inn þessa fylkisformúlu inn í margar aðliggjandi frumur, eins og frumurnar F4: F8eins og sýnt er á myndinni hér að neðan. Fjöldi frumna verður að vera jafn eða meiri en hámarksfjöldi endurtekningar á gildi sem leitað er að. Ekki gleyma að smella Ctrl + Shift + Sláðu inntil að slá inn fylkisformúluna rétt.

Ef þú hefur áhuga á að skilja hvernig það virkar, skulum við kafa aðeins í smáatriði formúlunnar:

Hluti 1:

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

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

$F$2=B2:B16 - berðu saman gildið í reit F2 við hvert gildi á bilinu B2:B16. Ef samsvörun finnst, þá tjáningin STRING(C2:C16)-1 skilar númeri samsvarandi línu (gildi -1 gerir þér kleift að hafa ekki hauslínuna með). Ef það eru engar samsvörun, aðgerðin IF (IF) skilar tómum streng.

Niðurstaða virkni IF (EF) það verður svo lárétt fylki: {1,"",3,"",5,"","","","","","",12,"","",""}

Hluti 2:

ROW()-3

СТРОКА()-3

Hér er aðgerðin UMFERÐ (LINE) virkar sem aukateljari. Þar sem formúlan er afrituð í reiti F4:F9, drögum við töluna frá 3 frá fall niðurstöðu til að fá gildi 1 í klefanum F4 (lína 4, draga 3 frá) til að fá 2 í klefanum F5 (lína 5, draga 3 frá) og svo framvegis.

Hluti 3:

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

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

virka SMALL (LITT) skilar n-ó minnsta gildið í gagnafylkingunni. Í okkar tilviki ræðst hvaða staða (frá þeirri minnstu) sem á að skila af fallinu UMFERÐ (LINE) (sjá 2. hluta). Svo, fyrir klefa F4 virka SMALL({fylki},1) Skilaréttur 1-þ (minnsta) fylkisþáttur, þ.e 1. Fyrir klefi F5 Skilaréttur 2-þ minnsti þátturinn í fylkinu, það er 3O.fl.

Hluti 4:

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))

virka INDEX (INDEX) skilar einfaldlega gildi tiltekins hólfs í fylki C2:C16. Fyrir klefi F4 virka INDEX($C$2:$C$16) kem aftur eplifyrir F5 virka INDEX($C$2:$C$16) kem aftur sælgæti' og svo framvegis.

Hluti 5:

IFERROR()

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

Að lokum setjum við formúluna inn í fallið IFERROR (IFERROR), vegna þess að ólíklegt er að þú sért ánægður með villuboðin #AT (#N/A) ef fjöldi frumna sem formúlan er afrituð í er minni en fjöldi tvítekinna gilda á bilinu sem verið er að skoða.

XNUMXD leit eftir þekktri röð og dálki

Að framkvæma XNUMXD leit í Excel felur í sér að leita að gildi eftir þekktri röð og dálknúmeri. Með öðrum orðum, þú ert að draga út frumugildið á mótum ákveðinnar línu og dálks.

Svo skulum við snúa okkur að töflunni okkar og skrifa formúlu með falli VPR, sem mun finna upplýsingar um kostnað við sítrónur sem seldar eru í mars.

Það eru nokkrar leiðir til að framkvæma XNUMXD leit. Skoðaðu valkostina og veldu þann sem hentar þér best.

VLOOKUP og MATCH aðgerðir

Þú getur notað fullt af aðgerðum VPR (VLOOKUP) og FYRIR MEIRA (MATCH) til að finna gildið á mótum reitanna Название продукта (strengur) og Mánuður (dálkur) viðkomandi fylkis:

=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);ЛОЖЬ)

Formúlan hér að ofan er venjulegt fall VPR, sem leitar að nákvæmri samsvörun gildisins „Sítrónur“ í hólfum A2 til A9. En þar sem þú veist ekki í hvaða dálki marssölurnar eru, muntu ekki geta stillt dálknúmerið fyrir þriðju fallviðfangið. VPR. Þess í stað er aðgerðin notuð FYRIR MEIRAað skilgreina þennan dálk.

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

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

Þýtt á mannamál þýðir þessi formúla:

• Við erum að leita að persónunum "Mar" - rök lookup_value (uppflettingargildi);
• Horft í reiti frá A1 til I1 – rök leit_fylki (uppflettifylki);
• Skilar nákvæmri samsvörun – rök samsvörun_gerð (samsvörun_gerð).

Notkun 0 í þriðju röksemdinni segirðu aðgerðir FYRIR MEIRA leitaðu að fyrsta gildinu sem passar nákvæmlega við gildið sem þú ert að leita að. Þetta jafngildir verðmæti RANGT (FALSK) fyrir fjórðu rökin VPR.

Þannig er hægt að búa til tvíhliða leitarformúlu í Excel, einnig þekkt sem tvívídd leit eða tvíátta leit.

SUMPRODUCT aðgerð

virka SUMPRODUCT (SUMPRODUCT) skilar summu afurða valinna fylkinga:

=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)

INDEX og MATCH aðgerðir

Í næstu grein mun ég útskýra þessar aðgerðir í smáatriðum, svo í bili geturðu bara afritað þessa formúlu:

=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))

Nafngreind svið og rekstraraðili gatnamóta

Ef þú hefur ekki áhuga á öllum þessum flóknu Excel formúlum gætirðu líkað við þessa sjónrænu og eftirminnilegu leið:

1. Veldu töfluna, opnaðu flipann Formúlur (Formúlur) og smelltu Búðu til úr vali (Búa til úr vali).
2. Athugaðu kassana Efsta röð (á línunni fyrir ofan) og Vinstri dálkur (í dálknum til vinstri). Microsoft Excel mun úthluta nöfnum á sviðin frá gildunum í efstu röðinni og vinstri dálknum á töflureikninum þínum. Nú geturðu leitað með þessum nöfnum beint án þess að búa til formúlur.
3. Skrifaðu í hvaða tóma reit sem er =röð_nafn dálksnafntd svona:

   =Sítrónur Mar

   … eða öfugt:

   =Mar Sítrónur

   Mundu að línu- og dálknöfnin verða að vera aðskilin með bili, sem í þessu tilfelli virkar eins og gatnamótaaðgerð.

Þegar þú slærð inn nafn mun Microsoft Excel sýna tól með lista yfir samsvarandi nöfn, alveg eins og þegar þú slærð inn formúlu.

4. Press Sláðu inn og athugaðu niðurstöðuna

Almennt séð, hvor af ofangreindum aðferðum sem þú velur, verður niðurstaða tvívíddar leitar sú sama:

Notkun margra VLOOKUPs í einni formúlu

Það kemur fyrir að aðaltaflan og uppflettingartaflan eiga ekki einn dálk sameiginlegan og það kemur í veg fyrir að þú notir venjulega aðgerðina VPR. Hins vegar er önnur tafla sem inniheldur ekki þær upplýsingar sem við höfum áhuga á en hefur sameiginlegan dálk með aðaltöflunni og uppflettitöflunni.

Við skulum líta á eftirfarandi dæmi. Við höfum aðaltöflu með dálki SKU (nýtt), þar sem þú vilt bæta við dálki með samsvarandi verði úr annarri töflu. Auk þess erum við með 2 uppflettitöflur. Sú fyrsta (uppflettitafla 1) inniheldur uppfærðar tölur SKU (nýtt) og vöruheiti, og annað (uppflettitafla 2) – vöruheiti og gömul númer SKU (gamalt).

Til að bæta verði úr annarri uppflettitöflu við aðaltöfluna verður þú að framkvæma aðgerð sem kallast tvöfalt VPR eða hreiður VPR.

1. Skrifaðu fall VPR, sem finnur vöruheitið í töflunni Uppflettitöflu 1með SKU, sem æskilegt gildi:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   Hér New_SKU - nefnt svið $A:$B í töflunni Uppflettitöflu 1, 2 – þetta er dálkur B, sem inniheldur nöfn vörunnar (sjá myndina að ofan)

2. Skrifaðu formúlu til að setja inn verð úr töflu Uppflettitöflu 2 byggt á þekktum vöruheitum. Til að gera þetta skaltu líma formúluna sem þú bjóst til áður sem uppflettingargildi fyrir nýju fallið VPR:

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

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

   Hér Verð - nefnt svið $A:$C í töflunni Uppflettitöflu 2, 3 er dálkur C sem inniheldur verð.

Myndin hér að neðan sýnir niðurstöðuna sem skilað er af formúlunni sem við bjuggum til:

Dýnamísk skipting gagna úr mismunandi töflum með því að nota VLOOKUP og INDIRECT

Í fyrsta lagi skulum við skýra hvað við áttum við með orðatiltækinu „Kvikt skipting gagna úr mismunandi töflum“ til að tryggja að við skiljum hvert annað rétt.

Það eru aðstæður þegar það eru nokkur blöð með gögnum af sama sniði og nauðsynlegt er að draga nauðsynlegar upplýsingar úr ákveðnu blaði, allt eftir gildinu sem er slegið inn í tiltekinn reit. Ég held að það sé auðveldara að útskýra þetta með dæmi.

Ímyndaðu þér að þú sért með söluskýrslur fyrir nokkur svæði með sömu vörur og á sama sniði. Þú vilt finna sölutölur fyrir tiltekið svæði:

Ef þú hefur aðeins tvær slíkar skýrslur, þá geturðu notað skammarlega einfalda formúlu með föllum VPR и IF (IF) til að velja skýrsluna sem þú vilt leita í:

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

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

hvar:

• $D$2 er reit sem inniheldur heiti vörunnar. Athugaðu að við notum algerar tilvísanir hér til að forðast að breyta uppflettigildinu þegar formúlan er afrituð í aðrar hólf.
• $D3 er klefi með nafni svæðisins. Við erum að nota algera dálkatilvísun og hlutfallslega línutilvísun vegna þess að við ætlum að afrita formúluna í aðrar frumur í sama dálki.
• FL_Sales и CA_Sala – nöfnin á töflunum (eða nefndum sviðum) sem innihalda samsvarandi söluskýrslur. Þú getur að sjálfsögðu notað venjuleg blaðnöfn og tilvísanir í hólfsvið, til dæmis 'FL Sheet'!$A$3:$B$10, en nefnd svið eru miklu þægilegri.

Hins vegar, þegar það eru margar slíkar töflur, aðgerðin IF er ekki besta lausnin. Í staðinn geturðu notað aðgerðina ÓBEIN (ÓBEIN) til að skila tilætluðu leitarsviði.

Eins og þú veist líklega, aðgerðin ÓBEIN er notað til að skila hlekk sem gefinn er með textastreng, sem er nákvæmlega það sem við þurfum núna. Svo skaltu djarflega skiptu út tjáningunni í formúlunni hér að ofan með fallinu IF að tengja við virkni ÓBEIN. Hér er samsetning VPR и ÓBEIN virkar frábærlega með:

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

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

hvar:

• $D$2 – þetta er klefi með nafni vörunnar, það er óbreytt vegna algerrar tengingar.
• $D3 er reitinn sem inniheldur fyrsta hluta svæðisheitisins. Í okkar dæmi, þetta FL.
• _Sala – sameiginlegur hluti nafnsins á öllum nefndum sviðum eða töflum. Þegar það er sameinað gildinu í reit D3 myndar það fullgilt heiti tilskilins sviðs. Hér að neðan eru nokkrar upplýsingar fyrir þá sem eru nýir í aðgerðinni ÓBEIN.

Hvernig INDIRECT og VLOOKUP virka

Fyrst, leyfðu mér að minna þig á setningafræði fallsins ÓBEIN (ÓBEIN):

INDIRECT(ref_text,[a1])

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

Fyrstu rökin geta verið frumutilvísun (A1 eða R1C1 stíll), sviðsheiti eða textastrengur. Önnur röksemdin ákvarðar hvaða tengistíll er í fyrstu röksemdinni:

• A1ef rökin eru SANN KÓÐI (TRUE) eða ekki tilgreint;
• R1C1, Ef FAS E (RANGT).

Í okkar tilviki hefur hlekkurinn stílinn A1, svo þú getur sleppt seinni röksemdinni og einbeitt þér að þeim fyrri.

Svo skulum við snúa aftur að söluskýrslum okkar. Ef þú manst, þá er hver skýrsla sérstök tafla sem staðsett er á sérstöku blaði. Til að formúlan virki rétt verður þú að nefna töflurnar þínar (eða svið) og öll nöfn verða að hafa sameiginlegan hluta. Til dæmis, svona: CA_Sala, FL_Sala, TX_Sala og svo framvegis. Eins og þú sérð er „_Sales“ til staðar í öllum nöfnunum.

virka ÓBEIN tengir gildið í dálki D og textastrenginn „_Sales“ og segir þar með VPR í hvaða töflu á að leita. Ef reit D3 inniheldur gildið „FL“ mun formúlan leita í töflunni FL_Sala, ef „CA“ – í töflunni CA_Sala og svo framvegis.

Niðurstaða aðgerðanna VPR и ÓBEIN verður eftirfarandi:

Ef gögnin eru staðsett í mismunandi Excel bókum, þá þarftu að bæta nafni bókarinnar á undan nefndu sviðinu, til dæmis:

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

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

Ef aðgerðin ÓBEIN vísar til annarrar vinnubókar, sú vinnubók verður að vera opin. Ef það er lokað mun aðgerðin tilkynna villu. #REF! (#SSYL!).