Turinys
Antroje mūsų mokymo programos dalyje apie funkciją VPR (VLOOKUP) programoje „Excel“, išanalizuosime kelis pavyzdžius, kurie padės nukreipti visą galią VPR spręsti ambicingiausias Excel užduotis. Pavyzdžiuose daroma prielaida, kad jau turite pagrindinių žinių apie šios funkcijos veikimą. Jei ne, galbūt norėsite pradėti nuo pirmosios šios pamokos dalies, kurioje paaiškinama sintaksė ir pagrindinis naudojimas. VPR. Na, pradėkime.
„Excel“ ieškokite pagal kelis kriterijus
Funkcija VPR „Excel“ yra tikrai galingas įrankis tam tikros vertės paieškai duomenų bazėje atlikti. Tačiau yra reikšmingas apribojimas – jos sintaksė leidžia ieškoti tik vienos reikšmės. Ką daryti, jei norite ieškoti pagal kelias sąlygas? Sprendimą rasite žemiau.
1 pavyzdys: Ieškokite pagal 2 skirtingus kriterijus
Tarkime, kad turime užsakymų sąrašą ir norime rasti Prekių kiekis (Kit.), remiantis dviem kriterijais – Kliento vardas (Klientas) ir Название продукта (Produktas). Situaciją apsunkina tai, kad kiekvienas pirkėjas užsisakė kelių rūšių prekes, kaip matyti iš toliau pateiktos lentelės:
reguliari funkcija VPR neveiks pagal šį scenarijų, nes grąžins pirmąją rastą reikšmę, atitinkančią nurodytą paieškos reikšmę. Pavyzdžiui, jei norite sužinoti prekės kiekį saldumynaiužsakė pirkėjas Džeremis Hilas, parašykite šią formulę:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– ši formulė grąžins rezultatą 15atitinkantis prekę obuoliai, nes tai pirmoji atitinkanti reikšmė.
Yra paprastas sprendimas – sukurkite papildomą stulpelį, kuriame sujungtumėte visus norimus kriterijus. Mūsų pavyzdyje tai yra stulpeliai Kliento vardas (Klientas) ir Название продукта (Produktas). Nepamirškite, kad sujungtas stulpelis visada turi būti kairiausias paieškos diapazono stulpelis, nes funkcija yra kairiajame stulpelyje. VPR pažvelgia į viršų, kai ieško vertės.
Taigi, į lentelę įtraukite pagalbinį stulpelį ir nukopijuokite šią formulę visuose jos langeliuose: =B2 ir C2. Jei norite, kad eilutė būtų skaitomesnė, kombinuotas reikšmes galite atskirti tarpu: =B2&» «&C2. Po to galite naudoti šią formulę:
=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 yra ląstelė B1 yra sujungta argumento reikšmė lookup_value (paieškos_vertė) ir 4 – Argumentas col_index_num (stulpelio_numeris), ty stulpelio, kuriame yra duomenys, kuriuos reikia gauti, numeris.
2 pavyzdys: VLOOKUP pagal du kriterijus, kai lentelė peržiūrima kitame lape
Jei reikia atnaujinti pagrindinę lentelę (Pagrindinę lentelę) pridedant duomenis iš antrosios lentelės (Paieškos lentelės), esančios kitame lape arba kitoje „Excel“ darbaknygėje, tuomet norimą reikšmę galite surinkti tiesiai į įterptą formulę. į pagrindinę lentelę.
Kaip ir ankstesniame pavyzdyje, peržvalgos lentelėje jums reikės pagalbinio stulpelio su kombinuotomis reikšmėmis. Šis stulpelis turi būti kairysis paieškos diapazono stulpelis.
Taigi formulė su VPR gali buti taip:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Čia B ir C stulpeliuose yra atitinkamai klientų vardai ir produktų pavadinimai bei nuoroda Užsakymai!$A&$2:$D$2 apibrėžia lentelę, kurią reikia ieškoti kitame lape.
Kad formulė būtų lengviau skaitoma, galite suteikti rodinio diapazonui pavadinimą, tada formulė atrodys daug paprastesnė:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Kad formulė veiktų, kairiajame žiūrimos lentelės stulpelyje esančios reikšmės turi būti derinamos lygiai taip pat, kaip ir paieškos kriterijų. Aukščiau esančiame paveikslėlyje mes sujungėme reikšmes u2bu2band, tarp jų įdėjome tarpą, lygiai taip pat, kaip reikia padaryti pirmajame funkcijos argumente (BXNUMX& "" & CXNUMX).
Prisiminti! Funkcija VPR apribotas iki 255 simbolių, jis negali ieškoti reikšmės, kuri yra ilgesnė nei 255 simboliai. Turėkite tai omenyje ir įsitikinkite, kad norimos vertės ilgis neviršija šios ribos.
Sutinku, kad pagalbinės kolonos pridėjimas nėra pats elegantiškiausias ir ne visada priimtinas sprendimas. Tą patį galite padaryti ir be pagalbinio stulpelio, tačiau tam reikės daug sudėtingesnės formulės su funkcijų deriniu RODYKLĖ (INDEKSAS) ir RUNGTYNIŲ (DAUGIAU ATSAKYTA).
Naudodami VLOOKUP išgauname 2, 3 ir tt reikšmes
Jūs jau tai žinote VPR gali grąžinti tik vieną atitinkančią reikšmę, tiksliau, pirmą rastą. O kas, jei ši reikšmė peržiūrimame masyve kartojama kelis kartus ir norite išgauti 2 ar 3 iš jų? O jei visos vertybės? Problema atrodo sudėtinga, bet sprendimas yra!
Tarkime, viename lentelės stulpelyje yra klientų pavadinimai (kliento vardas), o kitame stulpelyje yra jų įsigytos prekės (produktas). Pabandykime surasti 2-ą, 3-ią ir 4-ą prekę, kurią įsigijo konkretus klientas.
Lengviausias būdas yra pridėti pagalbinį stulpelį prieš stulpelį Kliento vardas ir užpildykite jį klientų vardais su kiekvieno vardo pasikartojimo numeriu, pavyzdžiui, Jonas Doe1, Jonas Doe2 ir tt Mes padarysime triuką su numeravimu naudodami funkciją COUNTIF (COUNTIF), atsižvelgiant į tai, kad klientų vardai yra B stulpelyje:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Po to galite naudoti įprastą funkciją VPRrasti reikiamą užsakymą. Pavyzdžiui:
- rasti 2-asis kliento užsakyta prekė Danas Brownas:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - rasti 3-asis kliento užsakyta prekė Danas Brownas:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Tiesą sakant, galite įvesti langelio nuorodą kaip paieškos reikšmę, o ne tekstą, kaip parodyta toliau pateiktame paveikslėlyje:
Jei tik ieškai 2-e kartojimas, galite tai padaryti be pagalbinio stulpelio, sukurdami sudėtingesnę formulę:
=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;ИСТИНА);"")
Šioje formulėje:
- $ 2 $ F – langelis, kuriame yra pirkėjo vardas (jis nepakitęs, atkreipkite dėmesį – nuoroda yra absoliuti);
- $ B $ – stulpelis Kliento vardas;
- Table4 – Jūsų stalas (ši vieta gali būti ir įprastas asortimentas);
- C16 USD – jūsų lentelės arba diapazono pabaigos langelis.
Ši formulė randa tik antrąją atitinkančią reikšmę. Jei reikia išgauti likusius pasikartojimus, naudokite ankstesnį sprendimą.
Jei reikia visų atitikmenų sąrašo – funkcija VPR tai nėra pagalbinė priemonė, nes vienu metu grąžina tik vieną reikšmę – periodą. Bet „Excel“ turi funkciją RODYKLĖ (INDEX), kuri gali lengvai susidoroti su šia užduotimi. Kaip atrodys tokia formulė, sužinosite toliau pateiktame pavyzdyje.
Gaukite visus norimos vertės pasikartojimus
Kaip paminėta aukščiau VPR negali išgauti visų pasikartojančių verčių iš nuskaityto diapazono. Norėdami tai padaryti, jums reikia šiek tiek sudėtingesnės formulės, sudarytos iš kelių „Excel“ funkcijų, pvz RODYKLĖ (INDEKSAS), MAŽOJI (MAŽAS) ir EILĖ (LINE)
Pavyzdžiui, toliau pateikta formulė suranda visus vertės pasikartojimus iš langelio F2 diapazone B2:B16 ir grąžina rezultatą iš tų pačių eilučių C stulpelyje.
{=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));"")}
Įveskite šią masyvo formulę į keletą gretimų langelių, pvz., langelių F4: F8kaip parodyta paveikslėlyje žemiau. Langelių skaičius turi būti lygus ar didesnis už maksimalų galimą ieškomos reikšmės pakartojimų skaičių. Nepamirškite paspausti Ctrl + Shift + Enterkad teisingai įvestumėte masyvo formulę.
Jei norite suprasti, kaip tai veikia, šiek tiek pasinerkime į formulės detales:
Dalis 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – palyginkite reikšmę langelyje F2 su kiekviena iš verčių diapazone B2:B16. Jei rasta atitiktis, tada išraiška STRING(C2:C16)-1 grąžina atitinkamos eilutės numerį (reikšmė -1 leidžia neįtraukti antraštės eilutės). Jei atitikmenų nėra, funkcija IF (IF) grąžina tuščią eilutę.
Funkcijos rezultatas IF (IF) bus toks horizontalus masyvas: {1,"",3,"",5,"","","","","","",12,"","",""}
Dalis 2:
ROW()-3
СТРОКА()-3
Čia funkcija EILĖ (LINE) veikia kaip papildomas skaitiklis. Kadangi formulė nukopijuota į langelius F4:F9, skaičių atimame 3 iš funkcijos rezultato gauti vertę 1 ląstelėje F4 (4 eilutė, atimkite 3), kad gautumėte 2 ląstelėje F5 (5 eilutė, atimti 3) ir pan.
Dalis 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Funkcija MAŽOJI (SMALL) grąžina oi mažiausia reikšmė duomenų masyve. Mūsų atveju, į kurią padėtį (nuo mažiausios) grįžti, lemia funkcija EILĖ (LINE) (žr. 2 dalį). Taigi, ląstelei F4 funkcija MAŽAS ({masyvas},1) Grąžinimas 1-asis (mažiausias) masyvo elementas, t 1. Ląstelėms F5 Grąžinimas 2-asis mažiausias masyvo elementas, tai yra 3Ir tt
Dalis 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 RODYKLĖ (INDEX) tiesiog grąžina konkretaus masyvo langelio reikšmę C2:C16. Ląstelėms F4 funkcija INDEKSS ($C$2:$C$16) grįš obuoliaiDėl F5 funkcija INDEKSS ($C$2:$C$16) grįš saldumynai ir taip toliau.
Dalis 5:
IFERROR()
ЕСЛИОШИБКА()
Galiausiai formulę įdedame į funkcijos vidų IFERROR (IFERROR), nes vargu ar būsite patenkinti klaidos pranešimu #AT (#N/A), jei langelių, į kuriuos nukopijuota formulė, skaičius yra mažesnis nei pasikartojančių reikšmių skaičius peržiūrimame diapazone.
XNUMXD paieška pagal žinomą eilutę ir stulpelį
Atliekant XNUMXD paiešką programoje „Excel“, reikia ieškoti vertės pagal žinomą eilutės ir stulpelio numerį. Kitaip tariant, ištraukiate langelio vertę konkrečios eilutės ir stulpelio sankirtoje.
Taigi, pereikime prie mūsų lentelės ir parašykime formulę su funkcija VPR, kuriame bus pateikta informacija apie kovo mėnesį parduodamų citrinų kainą.
Yra keletas būdų, kaip atlikti XNUMXD paiešką. Peržiūrėkite parinktis ir išsirinkite sau tinkamiausią.
VLOOKUP ir MATCH funkcijos
Galite naudoti daugybę funkcijų VPR (VLOOKUP) ir DAUGIAU ATSKIRTA (MATCH), kad rastumėte reikšmę laukų sankirtoje Название продукта (styga) ir Mėnuo aptariamo masyvo (stulpelis):
=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);ЛОЖЬ)
Aukščiau pateikta formulė yra įprasta funkcija VPR, kuri ieško tikslios reikšmės „Lemons“ atitikties langeliuose A2–A9. Tačiau kadangi nežinote, kuriame stulpelyje yra kovo mėnesio pardavimai, negalėsite nustatyti trečiojo funkcijos argumento stulpelio numerio. VPR. Vietoj to naudojama funkcija DAUGIAU ATSKIRTAnorėdami apibrėžti šį stulpelį.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Išvertus į žmonių kalbą, ši formulė reiškia:
- Ieškome personažų „Mar“ – argumentas lookup_value (paieškos_vertė);
- Žvelgiant į langelius nuo A1 iki I1 – argumentas lookup_masyvas (lookup_masyvas);
- Grąžinama tiksli atitiktis – argumentas atitikimo_tipas (match_type).
Naudojant 0 trečiuoju argumentu jūs sakote funkcijas DAUGIAU ATSKIRTA ieškokite pirmosios vertės, kuri tiksliai atitinka jūsų ieškomą vertę. Tai atitinka vertę FALSE (FALSE) už ketvirtą argumentą VPR.
Taip programoje „Excel“ galite sukurti dvipusės paieškos formulę, dar žinomą kaip dvimatė paieška arba dvikryptė paieška.
SUMPRODUCT funkcija
Funkcija SUMPRODUCT (SUMPRODUCT) grąžina pasirinktų masyvų sandaugų sumą:
=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 ir MATCH funkcijos
Kitame straipsnyje išsamiai paaiškinsiu šias funkcijas, todėl kol kas galite tiesiog nukopijuoti šią formulę:
=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))
Pavadinti diapazonai ir sankryžos operatorius
Jei nemėgstate visų sudėtingų „Excel“ formulių, jums gali patikti šis vaizdinis ir įsimintinas būdas:
- Pasirinkite lentelę, atidarykite skirtuką Formulės (Formulės) ir spustelėkite Sukurti iš pasirinkimo (Sukurti iš pasirinkimo).
- Pažymėkite langelius Viršutinė eilė (aukščiau esančioje eilutėje) ir Kairysis stulpelis (stulpelyje kairėje). „Microsoft Excel“ priskirs pavadinimus diapazonams iš verčių, esančių jūsų skaičiuoklės viršutinėje eilutėje ir kairiajame stulpelyje. Dabar galite ieškoti naudodami šiuos pavadinimus tiesiogiai, nekurdami formulių.
- Bet kuriame tuščiame langelyje parašykite =eilutės_pavadinimas stulpelio_pavadinimas, pavyzdžiui, taip:
= Citrinos kovo mėn
… arba atvirkščiai:
=Mar Lemons
Atminkite, kad eilučių ir stulpelių pavadinimai turi būti atskirti tarpu, kuris šiuo atveju veikia kaip sankryžos operatorius.
Kai įvesite pavadinimą, „Microsoft Excel“ parodys patarimą su atitinkančių pavadinimų sąrašu, kaip ir įvedant formulę.
- spauda įeiti ir patikrinkite rezultatą
Apskritai, kad ir kurį iš aukščiau pateiktų metodų pasirinktumėte, dvimatės paieškos rezultatas bus toks pat:
Kelių VLOOKUP naudojimas vienoje formulėje
Taip atsitinka, kad pagrindinė lentelė ir paieškos lentelė neturi vieno bendro stulpelio ir tai neleidžia naudoti įprastos funkcijos VPR. Tačiau yra ir kita lentelė, kurioje nėra mus dominančios informacijos, bet yra bendras stulpelis su pagrindine lentele ir paieškos lentele.
Pažvelkime į šį pavyzdį. Turime pagrindinę lentelę su stulpeliu SKU (naujas), kur norite pridėti stulpelį su atitinkamomis kainomis iš kitos lentelės. Be to, turime 2 paieškos lenteles. Pirmajame (1 paieškos lentelė) yra atnaujinti skaičiai SKU (naujas) ir produktų pavadinimai, o antrasis (2 paieškos lentelė) – prekių pavadinimai ir seni numeriai SKU (senas).
Norėdami pridėti kainas iš antrosios paieškos lentelės į pagrindinę lentelę, turite atlikti veiksmą, vadinamą dvigubu VPR arba įdėtas VPR.
- Parašykite funkciją VPR, kuri lentelėje suranda produkto pavadinimą 1 paieškos lentelėnaudojant Prekės kodas, kaip norima vertė:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Čia Naujas_SKU – pavadintas diapazonas $A:$B lentelėje 1 paieškos lentelė, 2 – tai B stulpelis, kuriame yra prekių pavadinimai (žr. paveikslėlį aukščiau)
- Parašykite formulę, kad įterptumėte kainas iš lentelės 2 paieškos lentelė remiantis gerai žinomais produktų pavadinimais. Norėdami tai padaryti, įklijuokite anksčiau sukurtą formulę kaip naujos funkcijos paieškos reikšmę VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Čia Kaina – pavadintas diapazonas $A:$C lentelėje 2 paieškos lentelė, 3 C stulpelyje yra kainos.
Žemiau esančiame paveikslėlyje parodytas rezultatas, grąžintas pagal mūsų sukurtą formulę:
Dinaminis duomenų pakeitimas iš skirtingų lentelių naudojant VLOOKUP ir INDIRECT
Pirmiausia išsiaiškinkime, ką turime omenyje sakydami „dinaminis duomenų iš skirtingų lentelių pakeitimas“, kad įsitikintume, jog vienas kitą suprantame teisingai.
Būna situacijų, kai yra keli lapai su to paties formato duomenimis, ir reikia iš tam tikro lapo išgauti reikiamą informaciją, priklausomai nuo reikšmės, kuri įvedama tam tikrame langelyje. Manau, kad tai lengviau paaiškinti pavyzdžiu.
Įsivaizduokite, kad turite kelių regionų pardavimų ataskaitas su tais pačiais produktais ir tuo pačiu formatu. Norite sužinoti konkretaus regiono pardavimo duomenis:
Jei turite tik dvi tokias ataskaitas, galite naudoti gėdingai paprastą formulę su funkcijomis VPR и IF (IF), kad pasirinktumėte norimą ataskaitą ieškoti:
=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 yra langelis, kuriame yra produkto pavadinimas. Atminkite, kad čia naudojame absoliučias nuorodas, kad nekeistume paieškos reikšmės kopijuojant formulę į kitus langelius.
- D3 USD yra langelis su regiono pavadinimu. Mes naudojame absoliučią stulpelio nuorodą ir santykinę eilutės nuorodą, nes planuojame nukopijuoti formulę į kitus to paties stulpelio langelius.
- FL_Sales и CA_Pardavimai – lentelių (arba pavadintų diapazonų), kuriose yra atitinkamos pardavimo ataskaitos, pavadinimai. Žinoma, galite naudoti, pavyzdžiui, įprastus lapų pavadinimus ir langelių diapazono nuorodas „FL lapas“!$A$3:$B$10, tačiau pavadinti diapazonai yra daug patogesni.
Tačiau kai tokių lentelių yra daug, funkcija IF nėra geriausias sprendimas. Vietoj to galite naudoti funkciją NETIESIOGINĖS (NETIESIOGINĖ), kad grąžintumėte norimą paieškos diapazoną.
Kaip tikriausiai žinote, funkcija NETIESIOGINĖS naudojamas norint grąžinti nuorodą, pateiktą teksto eilutėje, o tai yra būtent tai, ko mums dabar reikia. Taigi, aukščiau pateiktoje formulėje išraišką drąsiai pakeiskite funkcija IF susieti su funkcija NETIESIOGINĖS. Čia yra derinys VPR и NETIESIOGINĖS puikiai veikia su:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
kur:
- D $ 2 USD – tai langelis su prekės pavadinimu, jis nepakitęs dėl absoliučios sąsajos.
- D3 USD yra langelis, kuriame yra pirmoji regiono pavadinimo dalis. Mūsų pavyzdyje tai FL.
- _Pardavimai – bendroji visų įvardintų diapazonų ar lentelių pavadinimų dalis. Sujungus su reikšme langelyje D3, jis sudaro visą reikiamo diapazono pavadinimą. Žemiau yra keletas detalių tiems, kurie šią funkciją naudoja naujai NETIESIOGINĖS.
Kaip veikia INDIRECT ir VLOOKUP
Pirmiausia leiskite man priminti funkcijos sintaksę NETIESIOGINĖS (NETIESIOGINĖ):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Pirmasis argumentas gali būti langelio nuoroda (stilius A1 arba R1C1), diapazono pavadinimas arba teksto eilutė. Antrasis argumentas nustato, koks nuorodos stilius yra pirmame argumente:
- A1jei argumentas yra TIKRAS KODAS (TRUE) arba nenurodyta;
- R1C1, Jei FAS E (NETEISINGA).
Mūsų atveju nuoroda turi stilių A1, todėl galite atsisakyti antrojo argumento ir sutelkti dėmesį į pirmąjį.
Taigi grįžkime prie pardavimo ataskaitų. Jei prisimenate, kiekviena ataskaita yra atskira lentelė, esanti atskirame lape. Kad formulė veiktų tinkamai, turite pavadinti lenteles (arba diapazonus), o visi pavadinimai turi turėti bendrą dalį. Pavyzdžiui, taip: CA_Pardavimai, FL_Pardavimai, TX_Pardavimai ir taip toliau. Kaip matote, „_Pardavimai“ yra visuose pavadinimuose.
Funkcija NETIESIOGINĖS sujungia reikšmę D stulpelyje ir teksto eilutę „_Pardavimai“, taip pasakydami VPR kurioje lentelėje ieškoti. Jei langelyje D3 yra reikšmė „FL“, formulė ieškos lentelėje FL_Pardavimai, jei „CA“ – lentelėje CA_Pardavimai ir taip toliau.
Funkcijų rezultatas VPR и NETIESIOGINĖS bus toks:
Jei duomenys yra skirtingose „Excel“ knygose, tada prieš pavadintą diapazoną turite pridėti knygos pavadinimą, pavyzdžiui:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Jei funkcija NETIESIOGINĖS nurodo kitą darbaknygę, ta darbaknygė turi būti atidaryta. Jei jis uždarytas, funkcija praneš apie klaidą. #REF! (#SSYL!).