Papildu VLOOKUP piemēri: vairāku kritēriju meklēšana

Mūsu apmācības otrajā daļā par funkciju VPR (VLOOKUP) programmā Excel, mēs analizēsim dažus piemērus, kas palīdzēs jums novirzīt visu jaudu VPR vērienīgāko Excel uzdevumu risināšanai. Piemēros tiek pieņemts, ka jums jau ir pamatzināšanas par šīs funkcijas darbību. Ja nē, jūs varētu interesēt sākt ar šīs apmācības pirmo daļu, kurā ir izskaidrota sintakse un pamata lietojums. VPR. Nu, sāksim.

• Meklējiet programmā Excel pēc vairākiem kritērijiem
• Mēs iegūstam 2., 3. utt. vērtības, izmantojot VLOOKUP
• Izgūt visus vajadzīgās vērtības atkārtojumus
• XNUMXD meklēšana pēc zināmas rindas un kolonnas
• Vairāku VLOOKUP izmantošana vienā formulā
• Dinamiska datu aizstāšana no dažādām tabulām

Meklējiet programmā Excel pēc vairākiem kritērijiem

funkcija VPR programmā Excel ir patiešām jaudīgs rīks noteiktas vērtības meklēšanai datu bāzē. Tomēr pastāv ievērojams ierobežojums – tās sintakse ļauj meklēt tikai vienu vērtību. Ko darīt, ja vēlaties meklēt pēc vairākiem nosacījumiem? Tālāk atradīsit risinājumu.

1. piemērs: meklējiet pēc 2 dažādiem kritērijiem

Pieņemsim, ka mums ir pasūtījumu saraksts un mēs vēlamies to atrast Preču daudzums (Daudz.), pamatojoties uz diviem kritērijiem – Klienta vārds (Klients) и Название продукта (Produkts). Lietu sarežģī fakts, ka katrs no pircējiem pasūtīja vairāku veidu preces, kā redzams zemāk esošajā tabulā:

regulāra funkcija VPR nedarbosies šajā scenārijā, jo tas atgriezīs pirmo atrasto vērtību, kas atbilst norādītajai uzmeklēšanas vērtībai. Piemēram, ja vēlaties uzzināt preces daudzumu saldumipasūtījis pircējs Džeremijs Hils, ierakstiet šādu formulu:

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

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

– šī formula atgriezīs rezultātu 15atbilst precei āboli, jo tā ir pirmā vērtība, kas atbilst.

Ir vienkāršs risinājums – izveidojiet papildu kolonnu, kurā apvienot visus vēlamos kritērijus. Mūsu piemērā šīs ir kolonnas Klienta vārds (Klients) и Название продукта (Produkts). Neaizmirstiet, ka sapludinātajai kolonnai vienmēr ir jābūt meklēšanas diapazona galējai kreisajai kolonnai, jo funkcija ir kreisā kolonna. VPR meklē vērtību.

Tātad, jūs pievienojat tabulai papildu kolonnu un kopējiet šādu formulu visās tās šūnās: =B2&C2. Ja vēlaties, lai virkne būtu labāk lasāma, varat atdalīt apvienotās vērtības ar atstarpi: =B2&» «&C2. Pēc tam varat izmantot šādu formulu:

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

Kur ir šūna B1 satur argumenta sasaistīto vērtību lookup_value (uzmeklēšanas_vērtība) un 4 - Arguments col_index_num (kolonnas_numurs), ti, tās kolonnas numurs, kurā ir izgūstamie dati.

2. piemērs: VLOOKUP pēc diviem kritērijiem, tabula tiek skatīta citā lapā

Ja jums ir jāatjaunina galvenā tabula (Galvenā tabula), pievienojot datus no otrās tabulas (uzmeklēšanas tabula), kas atrodas citā lapā vai citā Excel darbgrāmatā, tad jūs varat savākt vajadzīgo vērtību tieši ievietotajā formulā. galvenajā tabulā.

Tāpat kā iepriekšējā piemērā, uzmeklēšanas tabulā būs nepieciešama papildu kolonna ar kombinētajām vērtībām. Šai kolonnai ir jābūt meklēšanas diapazona vistālāk kreisajā pusē.

Tātad formula ar VPR varētu būt šādi:

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

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

Šeit B un C kolonnās ir attiecīgi klientu vārdi un produktu nosaukumi, kā arī saite Pasūtījumi!$A&$2:$D$2 definē tabulu, ko meklēt citā lapā.

Lai padarītu formulu lasāmāku, skata diapazonam varat piešķirt nosaukumu, un tad formula izskatīsies daudz vienkāršāka:

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

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

Lai formula darbotos, skatītās tabulas kreisajā malā esošās vērtības ir jāapvieno tieši tādā pašā veidā kā meklēšanas kritērijos. Iepriekš redzamajā attēlā mēs apvienojām vērtības u2bu2band, starp tām ievietojām atstarpi, tāpat kā tas jādara pirmajā funkcijas argumentā (BXNUMX& “” & CXNUMX).

Atcerieties! funkcija VPR ierobežots līdz 255 rakstzīmēm, tas nevar meklēt vērtību, kas ir garāka par 255 rakstzīmēm. Paturiet to prātā un pārliecinieties, ka vēlamās vērtības garums nepārsniedz šo robežu.

Piekrītu, ka palīgkolonnas pievienošana nav elegantākais un ne vienmēr pieņemamākais risinājums. To pašu var izdarīt arī bez palīga kolonnas, taču tam būtu nepieciešama daudz sarežģītāka formula ar funkciju kombināciju INDEX (INDEKSS) un MATCH (VAIRĀK IZKLĀTA).

Mēs iegūstam 2., 3. utt. vērtības, izmantojot VLOOKUP

Jūs to jau zināt VPR var atgriezt tikai vienu atbilstošu vērtību, precīzāk, pirmo atrasto. Bet ko darīt, ja šī vērtība tiek atkārtota vairākas reizes skatītajā masīvā un vēlaties iegūt 2. vai 3. no tām? Ko darīt, ja visas vērtības? Problēma šķiet sarežģīta, bet risinājums pastāv!

Pieņemsim, ka vienā tabulas kolonnā ir norādīti klientu vārdi (klienta nosaukums), bet otrā kolonnā ir norādīti produkti (Produkts), ko viņi iegādājās. Mēģināsim atrast 2., 3. un 4. preci, ko iegādājies konkrētais klients.

Vienkāršākais veids ir pirms kolonnas pievienot papildu kolonnu Klienta vārds un aizpildiet to ar klientu vārdiem ar katra vārda atkārtojuma numuru, piemēram, Džons Dū 1, Džons Dū 2 uc Mēs veiksim triku ar numerāciju, izmantojot funkciju COUNTIF (COUNTIF), ņemot vērā, ka klientu vārdi ir B slejā:

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

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

Pēc tam varat izmantot parasto funkciju VPRlai atrastu vajadzīgo pasūtījumu. Piemēram:

• atrast 2 klienta pasūtītā prece Dan Brown:

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

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

• atrast 3 klienta pasūtītā prece Dan Brown:

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

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

Faktiski kā uzmeklēšanas vērtību varat ievadīt šūnas atsauci, nevis tekstu, kā parādīts nākamajā attēlā:

Ja jūs tikai meklējat 2-e atkārtojumu, varat to izdarīt bez papildu kolonnas, izveidojot sarežģītāku formulu:

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

Šajā formulā:

• 2 ASV dolāri – šūna, kurā ir pircēja vārds (tas ir nemainīgs, ņemiet vērā – saite ir absolūta);
• $ B $ - kolonna Klienta vārds;
• Table4 – Jūsu galds (šī vieta var būt arī parastais klāsts);
• C16 $ – jūsu tabulas vai diapazona beigu šūna.

Šī formula atrod tikai otro atbilstošo vērtību. Ja nepieciešams iegūt atlikušos atkārtojumus, izmantojiet iepriekšējo risinājumu.

Ja nepieciešams visu atbilstību saraksts – funkcija VPR tas nav palīgs, jo tas vienlaikus atgriež tikai vienu vērtību – periodu. Bet Excel ir funkcija INDEX (INDEKSS), kas var viegli tikt galā ar šo uzdevumu. Kā izskatīsies šāda formula, jūs uzzināsit nākamajā piemērā.

Izgūt visus vajadzīgās vērtības atkārtojumus

Kā iepriekš minēts VPR nevar izvilkt visas dublētās vērtības no skenētā diapazona. Lai to izdarītu, ir nepieciešama nedaudz sarežģītāka formula, kas sastāv no vairākām Excel funkcijām, piemēram, INDEX (INDEX), SMALL (MAZĀ) un ROW (LINE)

Piemēram, tālāk sniegtā formula atrod visus vērtības atkārtojumus no šūnas F2 diapazonā B2:B16 un atgriež rezultātu no tām pašām rindām kolonnā 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));"")}

Ievadiet šo masīva formulu vairākās blakus esošajās šūnās, piemēram, šūnās F4: F8kā parādīts zemāk esošajā attēlā. Šūnu skaitam jābūt vienādam ar vai lielākam par meklētās vērtības maksimālo iespējamo atkārtojumu skaitu. Neaizmirstiet noklikšķināt Ctrl + Shift + Enterlai pareizi ievadītu masīva formulu.

Ja vēlaties saprast, kā tā darbojas, iedziļināsimies formulas detaļās:

Daļa 1:

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

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

$F$2=B2:B16 – salīdziniet vērtību šūnā F2 ar katru vērtību diapazonā B2:B16. Ja tiek atrasta atbilstība, tad izteiksme STRING(C2:C16)-1 atgriež atbilstošās rindas numuru (vērtību -1 ļauj neiekļaut galvenes rindiņu). Ja nav atbilstības, funkcija IF (IF) atgriež tukšu virkni.

Funkcijas rezultāts IF (JA) būs šāds horizontāls masīvs: {1,"",3,"",5,"","","","","","",12,"","",""}

Daļa 2:

ROW()-3

СТРОКА()-3

Šeit ir funkcija ROW (LINE) darbojas kā papildu skaitītājs. Tā kā formula ir kopēta šūnās F4:F9, mēs atņemam skaitli 3 no funkcijas rezultāta, lai iegūtu vērtību 1 šūnā F4 (4. rinda, atņem 3), lai iegūtu 2 šūnā F5 (5. rinda, atņem 3) un tā tālāk.

Daļa 3:

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

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

funkcija SMALL (SMALL) atgriežas n-ak mazākā vērtība datu masīvā. Mūsu gadījumā to, uz kuru pozīciju (no mazākās) atgriezties, nosaka funkcija ROW (LINE) (skat. 2. daļu). Tātad, par šūnu F4 funkcija MAZS({masīvs},1) Atgriež 1 (mazākais) masīva elements, ti 1. Šūnai F5 Atgriež 2 mazākais elements masīvā, tas ir 3, Uc

Daļa 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))

funkcija INDEX (INDEX) vienkārši atgriež konkrētas masīva šūnas vērtību C2:C16. Šūnai F4 funkcija INDEKSS($C$2:$C$16) atgriezīsies āboliPar F5 funkcija INDEKSS($C$2:$C$16) atgriezīsies saldumi un tā tālāk.

Daļa 5:

IFERROR()

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

Visbeidzot, mēs ievietojam formulu funkcijas iekšpusē IFERROR (IFERROR), jo jūs, visticamāk, nebūsiet apmierināts ar kļūdas ziņojumu # AT (#N/A), ja šūnu skaits, kurās tiek kopēta formula, ir mazāks par vērtību dublikātu skaitu skatītajā diapazonā.

XNUMXD meklēšana pēc zināmas rindas un kolonnas

XNUMXD meklēšanas veikšana programmā Excel ietver vērtības meklēšanu pēc zināmas rindas un kolonnas numura. Citiem vārdiem sakot, jūs iegūstat šūnas vērtību konkrētas rindas un kolonnas krustpunktā.

Tātad, pievērsīsimies mūsu tabulai un uzrakstīsim formulu ar funkciju VPR, kurā tiks atrasta informācija par martā pārdoto citronu izmaksām.

Ir vairāki veidi, kā veikt XNUMXD meklēšanu. Apskatiet iespējas un izvēlieties sev piemērotāko.

VLOOKUP un MATCH funkcijas

Varat izmantot virkni funkciju VPR (VLOOKUP) un VAIRĀK ATKLĀTA (MATCH), lai atrastu vērtību lauku krustpunktā Название продукта (string) un MĒNESĪ attiecīgā masīva (kolonna):

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

Iepriekš minētā formula ir regulāra funkcija VPR, kas šūnās A2–A9 meklē precīzu atbilstību vērtībai “Citroni”. Bet, tā kā jūs nezināt, kurā kolonnā ir marta pārdošanas apjomi, nevarēsiet iestatīt kolonnas numuru trešās funkcijas argumentam. VPR. Tā vietā tiek izmantota funkcija VAIRĀK ATKLĀTAlai definētu šo kolonnu.

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

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

Tulkojumā cilvēku valodā šī formula nozīmē:

• Meklējam tēlus “Mar” – arguments lookup_value (meklēšanas_vērtība);
• Skatoties šūnās no A1 līdz I1 – arguments uzmeklēšanas_masīvs (meklēšanas_masīvs);
• Precīzās atbilstības atgriešana — arguments atbilstības_veids (atbilstības_veids).

Izmantojot 0 trešajā argumentā jūs sakāt funkcijas VAIRĀK ATKLĀTA meklējiet pirmo vērtību, kas precīzi atbilst jūsu meklētajai vērtībai. Tas ir līdzvērtīgs vērtībai FALSE (FALSE) par ceturto argumentu VPR.

Tādā veidā programmā Excel varat izveidot divvirzienu meklēšanas formulu, kas pazīstama arī kā divdimensiju meklēšana vai divvirzienu meklēšana.

SUMPRODUCT funkcija

funkcija SUMPRODUCT (SUMPRODUCT) atgriež atlasīto masīvu produktu summu:

=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 un MATCH funkcijas

Nākamajā rakstā es detalizēti izskaidrošu šīs funkcijas, tāpēc pagaidām varat vienkārši nokopēt šo formulu:

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

Nosauktie diapazoni un krustojuma operators

Ja jums nepatīk visas šīs sarežģītās Excel formulas, jums varētu patikt šis vizuālais un neaizmirstamais veids:

1. Atlasiet tabulu, atveriet cilni Formulas (Formulas) un noklikšķiniet uz Izveidot no atlases (Izveidot no atlases).
2. Atzīmējiet izvēles rūtiņas Augšējā rinda (rindiņā augstāk) un Kreisā kolonna (kolonnā pa kreisi). Microsoft Excel piešķirs nosaukumus diapazoniem no vērtībām jūsu izklājlapas augšējā rindā un kreisajā kolonnā. Tagad varat meklēt, izmantojot šos nosaukumus tieši, neveidojot formulas.
3. Jebkurā tukšā šūnā ierakstiet =rindas_nosaukums kolonnas_nosaukums, piemēram, šādi:

   =Citroni mar

   … vai otrādi:

   =Mar Lemons

   Atcerieties, ka rindu un kolonnu nosaukumi ir jāatdala ar atstarpi, kas šajā gadījumā darbojas kā krustojuma operators.

Ievadot nosaukumu, programma Microsoft Excel parādīs rīka padomu ar atbilstošu nosaukumu sarakstu, tāpat kā ievadot formulu.

4. prese ienākt un pārbaudiet rezultātu

Kopumā, lai kuru no iepriekš minētajām metodēm izvēlētos, divdimensiju meklēšanas rezultāts būs tāds pats:

Vairāku VLOOKUP izmantošana vienā formulā

Gadās, ka galvenajai tabulai un uzmeklēšanas tabulai nav vienas kopīgas kolonnas, un tas neļauj izmantot parasto funkciju VPR. Tomēr ir vēl viena tabula, kas nesatur mūs interesējošo informāciju, bet tai ir kopīga kolonna ar galveno tabulu un uzmeklēšanas tabulu.

Apskatīsim tālāk sniegto piemēru. Mums ir galvenā tabula ar kolonnu SKU (jauns), kur vēlaties pievienot kolonnu ar atbilstošajām cenām no citas tabulas. Turklāt mums ir 2 uzmeklēšanas tabulas. Pirmajā (1. uzmeklēšanas tabula) ir atjaunināti skaitļi SKU (jauns) un produktu nosaukumi, bet otrais (2. uzmeklēšanas tabula) – preču nosaukumi un vecie numuri SKU (vecais).

Lai pievienotu cenas no otrās uzmeklēšanas tabulas galvenajai tabulai, jāveic darbība, kas pazīstama kā dubultā VPR vai ligzdotas VPR.

1. Uzrakstiet funkciju VPR, kas atrod produkta nosaukumu tabulā Uzmeklēšanas tabula 1izmantojot SKU, kā vēlamo vērtību:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   Šeit Jauns_SKU - nosaukts diapazons $A:$B tabulā Uzmeklēšanas tabula 1, 2 – šī ir B kolonna, kurā ir preču nosaukumi (skat. attēlu augstāk)

2. Uzrakstiet formulu, lai ievietotu cenas no tabulas Uzmeklēšanas tabula 2 pamatojoties uz labi zināmiem produktu nosaukumiem. Lai to izdarītu, ielīmējiet iepriekš izveidoto formulu kā jaunās funkcijas uzmeklēšanas vērtību VPR:

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

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

   Šeit Сena - nosaukts diapazons $A:$C tabulā Uzmeklēšanas tabula 2, 3 ir C kolonna, kurā ir cenas.

Zemāk redzamajā attēlā parādīts rezultāts, kas atgriezts pēc mūsu izveidotās formulas:

Dinamiska datu aizstāšana no dažādām tabulām, izmantojot VLOOKUP un INDIRECT

Vispirms noskaidrosim, ko mēs saprotam ar izteicienu “datu dinamiska aizstāšana no dažādām tabulām”, lai pārliecinātos, ka saprotam viens otru pareizi.

Pastāv situācijas, kad ir vairākas lapas ar viena formāta datiem, un ir nepieciešams iegūt nepieciešamo informāciju no noteiktas lapas atkarībā no vērtības, kas tiek ievadīta dotajā šūnā. Es domāju, ka to ir vieglāk izskaidrot ar piemēru.

Iedomājieties, ka jums ir pārdošanas pārskati par vairākiem reģioniem ar vienādiem produktiem un vienā formātā. Jūs vēlaties atrast pārdošanas rādītājus konkrētam reģionam:

Ja jums ir tikai divi šādi pārskati, varat izmantot apkaunojoši vienkāršu formulu ar funkcijām VPR и IF (IF), lai atlasītu vajadzīgo pārskatu, lai meklētu:

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

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

kur:

• D $ 2 USD ir šūna, kurā ir produkta nosaukums. Ņemiet vērā, ka mēs šeit izmantojam absolūtās atsauces, lai izvairītos no uzmeklēšanas vērtības maiņas, kopējot formulu uz citām šūnām.
• D3 $ ir šūna ar reģiona nosaukumu. Mēs izmantojam absolūtās kolonnas atsauces un relatīvās rindas atsauces, jo mēs plānojam kopēt formulu uz citām šūnām tajā pašā kolonnā.
• FL_Sales и CA_Pārdošana – to tabulu (vai nosaukto diapazonu) nosaukumi, kas satur atbilstošos pārdošanas pārskatus. Protams, varat izmantot, piemēram, parastos lapu nosaukumus un šūnu diapazona atsauces “FL lapa”!$A$3:$B$10, taču nosauktie diapazoni ir daudz ērtāki.

Tomēr, ja ir daudz šādu tabulu, funkcija IF nav labākais risinājums. Tā vietā varat izmantot funkciju NETIEŠAIS (NETIEŠAIS), lai atgrieztu vajadzīgo meklēšanas diapazonu.

Kā jūs droši vien zināt, funkcija NETIEŠAIS tiek izmantots, lai atgrieztu saiti, ko sniedz teksta virkne, un tas ir tieši tas, kas mums tagad ir vajadzīgs. Tāpēc iepriekš minētajā formulā izteiksmi droši aizstāj ar funkciju IF saistīt ar funkciju NETIEŠAIS. Šeit ir kombinācija VPR и NETIEŠAIS lieliski darbojas ar:

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

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

kur:

• D $ 2 USD – šī ir šūna ar preces nosaukumu, tā ir nemainīga absolūtās saites dēļ.
• D3 $ ir šūna, kas satur reģiona nosaukuma pirmo daļu. Mūsu piemērā šis FL.
• _Pārdošana – visu nosaukto diapazonu vai tabulu nosaukuma kopīgā daļa. Apvienojot to ar vērtību šūnā D3, tas veido vajadzīgā diapazona pilnībā kvalificētu nosaukumu. Tālāk ir sniegta sīkāka informācija tiem, kas šo funkciju vēl nav izmantojuši NETIEŠAIS.

Kā darbojas INDIRECT un VLOOKUP

Pirmkārt, ļaujiet man atgādināt funkcijas sintaksi NETIEŠAIS (NETIEŠI):

INDIRECT(ref_text,[a1])

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

Pirmais arguments var būt šūnas atsauce (stils A1 vai R1C1), diapazona nosaukums vai teksta virkne. Otrais arguments nosaka, kāds saites stils ir ietverts pirmajā argumentā:

• A1ja arguments ir PATIESAIS KODS (TRUE) vai nav norādīts;
• R1C1, Ja FAS E (FALSE).

Mūsu gadījumā saitei ir stils A1, tāpēc varat izlaist otro argumentu un koncentrēties uz pirmo.

Tātad atgriezīsimies pie mūsu pārdošanas pārskatiem. Ja atceraties, tad katrs pārskats ir atsevišķa tabula, kas atrodas uz atsevišķas lapas. Lai formula darbotos pareizi, jums ir jānosauc tabulas (vai diapazoni), un visiem nosaukumiem ir jābūt kopējai daļai. Piemēram, šādi: CA_Pārdošana, FL_Pārdošana, TX_Pārdošana un tā tālāk. Kā redzat, "_Sales" ir visos nosaukumos.

funkcija NETIEŠAIS savieno vērtību kolonnā D un teksta virkni “_Sales”, tādējādi norādot VPR kurā tabulā meklēt. Ja šūnā D3 ir vērtība “FL”, formula meklēs tabulā FL_Pārdošana, ja “CA” – tabulā CA_Pārdošana un tā tālāk.

Funkciju rezultāts VPR и NETIEŠAIS būs šādi:

Ja dati atrodas dažādās Excel grāmatās, pirms nosauktā diapazona jāpievieno grāmatas nosaukums, piemēram:

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

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

Ja funkcija NETIEŠAIS attiecas uz citu darbgrāmatu, šai darbgrāmatai ir jābūt atvērtai. Ja tas ir aizvērts, funkcija ziņos par kļūdu. #REF! (#SSYL!).