Advanced na Mga Halimbawa ng VLOOKUP: Multi-Criteria Search

Sa ikalawang bahagi ng aming tutorial sa function VPR (VLOOKUP) sa Excel, susuriin namin ang ilang mga halimbawa na makakatulong sa iyo na idirekta ang lahat ng kapangyarihan VPR upang malutas ang pinaka-ambisyosong mga gawain sa Excel. Ipinapalagay ng mga halimbawa na mayroon ka nang pangunahing kaalaman sa kung paano gumagana ang feature na ito. Kung hindi, maaaring interesado kang magsimula sa unang bahagi ng tutorial na ito, na nagpapaliwanag sa syntax at pangunahing paggamit. VPR. Well, simulan na natin.

• Maghanap sa Excel sa pamamagitan ng maraming pamantayan
• Kinukuha namin ang mga halaga ng 2nd, 3rd, atbp. gamit ang VLOOKUP
• Kunin ang lahat ng pag-uulit ng nais na halaga
• XNUMXD paghahanap sa pamamagitan ng kilalang row at column
• Paggamit ng maraming VLOOKUP sa isang formula
• Dynamic na pagpapalit ng data mula sa iba't ibang mga talahanayan

Maghanap sa Excel sa pamamagitan ng maraming pamantayan

tungkulin VPR sa Excel ay isang napakalakas na tool para sa pagsasagawa ng mga paghahanap para sa isang partikular na halaga sa isang database. Gayunpaman, mayroong isang makabuluhang limitasyon - pinapayagan ka ng syntax nito na maghanap ng isang halaga lamang. Paano kung gusto mong maghanap ayon sa maraming kundisyon? Malalaman mo ang solusyon sa ibaba.

Halimbawa 1: Maghanap sa pamamagitan ng 2 magkaibang pamantayan

Ipagpalagay na mayroon kaming isang listahan ng mga order at nais naming hanapin Dami ng kalakal (Qty.), batay sa dalawang pamantayan - Pangalan ng kliyente (Customer) at Название продукта (Produkto). Ang bagay ay kumplikado sa pamamagitan ng katotohanan na ang bawat isa sa mga mamimili ay nag-order ng ilang uri ng mga kalakal, tulad ng makikita mula sa talahanayan sa ibaba:

regular na pag-andar VPR ay hindi gagana sa sitwasyong ito dahil ibabalik nito ang unang halaga na nahanap nito na tumutugma sa ibinigay na halaga ng paghahanap. Halimbawa, kung gusto mong malaman ang dami ng isang item Matamis'iniutos ng bumibili Jeremy Hill, isulat ang sumusunod na formula:

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

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

– ibabalik ng formula na ito ang resulta 15naaayon sa produkto mga mansanas, dahil ito ang unang value na tumutugma.

Mayroong isang simpleng workaround – lumikha ng karagdagang column kung saan pagsasamahin ang lahat ng gustong pamantayan. Sa aming halimbawa, ito ang mga column Pangalan ng kliyente (Customer) at Название продукта (Produkto). Huwag kalimutan na ang pinagsamang column ay dapat palaging ang pinakakaliwang column sa hanay ng paghahanap, dahil ito ang kaliwang column kung saan ang function VPR tumitingin kapag naghahanap ng halaga.

Kaya, magdagdag ka ng pantulong na column sa talahanayan at kopyahin ang sumusunod na formula sa lahat ng mga cell nito: =B2&C2. Kung nais mong maging mas nababasa ang string, maaari mong paghiwalayin ang pinagsamang mga halaga sa isang puwang: =B2&» «&C2. Pagkatapos nito, maaari mong gamitin ang sumusunod na formula:

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

Nasaan ang cell B1 naglalaman ng pinagsama-samang halaga ng argumento lookup_value (lookup_value) at 4 – Pangangatwiran col_index_num (column_number), ibig sabihin, ang numero ng column na naglalaman ng data na kukunin.

Halimbawa 2: VLOOKUP sa pamamagitan ng dalawang pamantayan na ang talahanayan ay tinitingnan sa isa pang sheet

Kung kailangan mong i-update ang pangunahing talahanayan (Main table) sa pamamagitan ng pagdaragdag ng data mula sa pangalawang talahanayan (Lookup table), na matatagpuan sa isa pang sheet o sa isa pang Excel workbook, pagkatapos ay maaari mong kolektahin ang nais na halaga nang direkta sa formula na iyong ipinasok sa pangunahing mesa.

Tulad ng sa nakaraang halimbawa, kakailanganin mo ng isang auxiliary column sa Lookup table na may pinagsamang value. Ang column na ito ay dapat ang pinakakaliwang column sa hanay ng paghahanap.

Kaya ang formula na may VPR maaaring ganito:

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

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

Dito, ang mga column B at C ay naglalaman ng mga pangalan ng customer at mga pangalan ng produkto, ayon sa pagkakabanggit, at ang link Mga order!$A&$2:$D$2 tumutukoy sa isang talahanayan na hahanapin sa isa pang sheet.

Upang gawing mas nababasa ang formula, maaari mong bigyan ng pangalan ang hanay ng view, at pagkatapos ay magiging mas simple ang formula:

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

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

Upang gumana ang formula, ang mga halaga sa pinakakaliwang column ng talahanayan na iyong tinitingnan ay dapat na pagsamahin sa eksaktong parehong paraan tulad ng sa pamantayan sa paghahanap. Sa figure sa itaas, pinagsama namin ang mga value na u2bu2band ay naglagay ng puwang sa pagitan nila, sa parehong paraan na kailangan mong gawin sa unang argumento ng function (BXNUMX& “” & CXNUMX).

Tandaan! tungkulin VPR limitado sa 255 character, hindi ito makakahanap ng value na higit sa 255 character ang haba. Isaisip ito at tiyaking hindi lalampas sa limitasyong ito ang haba ng gustong halaga.

Sumasang-ayon ako na ang pagdaragdag ng isang auxiliary column ay hindi ang pinaka-eleganteng at hindi palaging katanggap-tanggap na solusyon. Magagawa mo ang pareho nang wala ang column ng helper, ngunit mangangailangan iyon ng mas kumplikadong formula na may kumbinasyon ng mga function INDEX (INDEX) at MATCH (MAS EXPOSED).

Kinukuha namin ang mga halaga ng 2nd, 3rd, atbp. gamit ang VLOOKUP

Alam mo na yan VPR maaaring magbalik lamang ng isang katumbas na halaga, mas tiyak, ang unang nahanap. Ngunit paano kung ang halagang ito ay paulit-ulit nang maraming beses sa tiningnang array, at gusto mong kunin ang ika-2 o ika-3 ng mga ito? Paano kung lahat ng halaga? Ang problema ay tila kumplikado, ngunit ang solusyon ay umiiral!

Ipagpalagay na ang isang column ng talahanayan ay naglalaman ng mga pangalan ng mga customer (Pangalan ng Customer), at ang isa pang column ay naglalaman ng mga produkto (Produkto) na binili nila. Subukan nating hanapin ang ika-2, ika-3 at ika-4 na item na binili ng isang partikular na customer.

Ang pinakamadaling paraan ay ang magdagdag ng pantulong na column bago ang column Pangalan ng Customer at punan ito ng mga pangalan ng customer na may numero ng pag-uulit ng bawat pangalan, halimbawa, John Doe1, John Doe2 atbp. Gagawin namin ang lansihin sa pagnunumero gamit ang function COUNTIF (COUNTIF), dahil ang mga pangalan ng customer ay nasa column B:

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

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

Pagkatapos nito, maaari mong gamitin ang normal na function VPRupang mahanap ang kinakailangang order. Halimbawa:

• Mahanap 2-ika item na inorder ng customer Dan Brown:

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

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

• Mahanap 3-ika item na inorder ng customer Dan Brown:

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

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

Sa katunayan, maaari kang maglagay ng cell reference bilang ang lookup value sa halip na text, tulad ng ipinapakita sa sumusunod na figure:

Kung hinahanap mo lang 2-e pag-uulit, magagawa mo ito nang walang pantulong na haligi sa pamamagitan ng paglikha ng isang mas kumplikadong formula:

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

Sa formula na ito:

• $F$2 – isang cell na naglalaman ng pangalan ng mamimili (ito ay hindi nagbabago, pakitandaan – ang link ay ganap);
• $B $ – hanay Pangalan ng Customer;
• Table4 – Ang iyong mesa (maaari ding regular na hanay ang lugar na ito);
• $ C16 – ang end cell ng iyong table o range.

Nahanap lang ng formula na ito ang pangalawang katumbas na halaga. Kung kailangan mong kunin ang natitirang mga pag-uulit, gamitin ang nakaraang solusyon.

Kung kailangan mo ng isang listahan ng lahat ng mga tugma - ang function VPR ito ay hindi isang katulong, dahil ito ay nagbabalik lamang ng isang halaga sa isang pagkakataon - panahon. Ngunit may function ang Excel INDEX (INDEX), na madaling makayanan ang gawaing ito. Kung ano ang magiging hitsura ng naturang formula, matututunan mo sa sumusunod na halimbawa.

Kunin ang lahat ng pag-uulit ng nais na halaga

Tulad ng nabanggit sa itaas VPR hindi ma-extract ang lahat ng duplicate na value mula sa na-scan na hanay. Upang gawin ito, kailangan mo ng bahagyang mas kumplikadong formula, na binubuo ng ilang mga function ng Excel, tulad ng INDEX (INDEX), Maliit (MALIIT) at ROW (LINE)

Halimbawa, hinahanap ng formula sa ibaba ang lahat ng pag-uulit ng value mula sa cell F2 sa hanay na B2:B16 at ibinabalik ang resulta mula sa parehong mga hilera sa column 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));"")}

Ilagay ang array formula na ito sa maraming katabing mga cell, gaya ng mga cell F4: F8tulad ng ipinapakita sa figure sa ibaba. Ang bilang ng mga cell ay dapat na katumbas o mas malaki kaysa sa maximum na posibleng bilang ng mga pag-uulit ng hinanap na halaga. Huwag kalimutang i-click Ctrl + Shift + Enterupang maipasok nang tama ang array formula.

Kung interesado kang maunawaan kung paano ito gumagana, tingnan natin nang kaunti ang mga detalye ng formula:

Ang bahagi 1:

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

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

$F$2=B2:B16 – ihambing ang halaga sa cell F2 sa bawat isa sa mga halaga sa hanay na B2:B16. Kung ang isang tugma ay natagpuan, pagkatapos ay ang expression STRING(C2:C16)-1 ibinabalik ang bilang ng kaukulang linya (value -1 nagbibigay-daan sa iyong hindi isama ang linya ng header). Kung walang mga tugma, ang function IF (IF) ay nagbabalik ng walang laman na string.

Resulta ng function IF (KUNG) magkakaroon ng ganitong pahalang na hanay: {1,"",3,"",5,"","","","","","",12,"","",""}

Ang bahagi 2:

ROW()-3

СТРОКА()-3

Narito ang function ROW (LINE) ay gumaganap bilang isang karagdagang counter. Dahil ang formula ay kinopya sa mga cell F4:F9, ibinabawas namin ang numero 3 mula sa resulta ng function upang makakuha ng halaga 1 sa selda F4 (linya 4, ibawas 3) para makuha 2 sa selda F5 (linya 5, ibawas 3) at iba pa.

Ang bahagi 3:

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

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

tungkulin Maliit (MALIIT) ay nagbabalik n-naku ang pinakamaliit na halaga sa array ng data. Sa aming kaso, kung aling posisyon (mula sa pinakamaliit) ang ibabalik ay tinutukoy ng function ROW (LINE) (tingnan ang Bahagi 2). Kaya, para sa isang cell F4 tungkulin MALIIT({array},1) Babalik 1-ika (pinakamaliit) elemento ng array, ibig sabihin 1. Para sa cell F5 Babalik 2-ika ang pinakamaliit na elemento sa array, iyon ay 3, Atbp

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

tungkulin INDEX (INDEX) ay nagbabalik lamang ng halaga ng isang partikular na cell sa isang array C2:C16. Para sa cell F4 tungkulin INDEX($C$2:$C$16) babalik mga mansanaspara F5 tungkulin INDEX($C$2:$C$16) babalik Matamis' at iba pa.

Ang bahagi 5:

IFERROR()

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

Sa wakas, inilalagay namin ang formula sa loob ng function IFERROR (IFERROR), dahil malamang na hindi ka nasisiyahan sa mensahe ng error #AT (#N/A) kung ang bilang ng mga cell kung saan kinopya ang formula ay mas mababa sa bilang ng mga duplicate na halaga sa hanay na tinitingnan.

XNUMXD paghahanap sa pamamagitan ng kilalang row at column

Ang pagsasagawa ng XNUMXD na paghahanap sa Excel ay nagsasangkot ng paghahanap para sa isang halaga sa pamamagitan ng isang kilalang numero ng row at column. Sa madaling salita, kinukuha mo ang halaga ng cell sa intersection ng isang partikular na row at column.

Kaya, lumiko tayo sa aming talahanayan at magsulat ng isang formula na may isang function VPR, na makakahanap ng impormasyon tungkol sa halaga ng mga limon na ibinebenta noong Marso.

Mayroong ilang mga paraan upang magsagawa ng XNUMXD na paghahanap. Tingnan ang mga opsyon at piliin ang isa na pinakaangkop sa iyo.

VLOOKUP at MATCH function

Maaari kang gumamit ng isang grupo ng mga function VPR (VLOOKUP) at MAS EXPOSED (MATCH) upang mahanap ang halaga sa intersection ng mga field Название продукта (string) at buwan (column) ng array na pinag-uusapan:

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

Ang formula sa itaas ay isang regular na function VPR, na naghahanap ng eksaktong tugma ng value na "Lemons" sa mga cell A2 hanggang A9. Ngunit dahil hindi mo alam kung saang column ang mga benta sa Marso, hindi mo magagawang itakda ang numero ng column para sa argumento ng ikatlong function. VPR. Sa halip, ginagamit ang function MAS EXPOSEDpara tukuyin ang column na ito.

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

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

Isinalin sa wika ng tao, ang pormula na ito ay nangangahulugang:

• Hinahanap namin ang mga character na "Mar" - argumento lookup_value (lookup_value);
• Naghahanap sa mga cell mula A1 hanggang I1 - argumento lookup_array (lookup_array);
• Nagbabalik ng eksaktong tugma - argumento match_type (match_type).

paggamit 0 sa ikatlong argumento, sinasabi mo ang mga function MAS EXPOSED hanapin ang unang halaga na eksaktong tumutugma sa halaga na iyong hinahanap. Ito ay katumbas ng halaga FALSE (MALI) para sa ikaapat na argumento VPR.

Ito ay kung paano ka makakagawa ng two-way na formula sa paghahanap sa Excel, na kilala rin bilang two-dimensional na paghahanap o bidirectional na paghahanap.

SUMPRODUCT function

tungkulin SUMPRODUCT (SUMPRODUCT) ibinabalik ang kabuuan ng mga produkto ng mga napiling array:

=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 at MATCH function

Sa susunod na artikulo ipapaliwanag ko nang detalyado ang mga pag-andar na ito, kaya sa ngayon maaari mo lamang kopyahin ang formula na ito:

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

Mga pinangalanang hanay at ang intersection operator

Kung wala ka sa lahat ng kumplikadong formula ng Excel na iyon, maaaring gusto mo ang visual at di malilimutang paraan na ito:

1. Piliin ang talahanayan, buksan ang tab Formula (Mga Formula) at i-click Lumikha mula sa Pinili (Gumawa mula sa pagpili).
2. Lagyan ng tsek ang mga kahon Nangungunang hilera (sa linya sa itaas) at Kaliwang haligi (sa column sa kaliwa). Ang Microsoft Excel ay magtatalaga ng mga pangalan sa mga hanay mula sa mga halaga sa itaas na hilera at kaliwang column ng iyong spreadsheet. Ngayon ay maaari kang maghanap gamit ang mga pangalang ito nang direkta nang hindi gumagawa ng mga formula.
3. Sa anumang walang laman na cell, sumulat =row_name column_name, halimbawa tulad nito:

   =Lemons Mar

   … o kabaliktaran:

   =Mar Lemons

   Tandaan na ang mga pangalan ng row at column ay dapat na pinaghihiwalay ng isang puwang, na sa kasong ito ay gumagana tulad ng intersection operator.

Kapag naglagay ka ng pangalan, magpapakita ang Microsoft Excel ng tooltip na may listahan ng mga katugmang pangalan, tulad ng kapag naglagay ka ng formula.

4. pindutin Magpasok at suriin ang resulta

Sa pangkalahatan, alinman sa mga pamamaraan sa itaas ang pipiliin mo, ang resulta ng isang two-dimensional na paghahanap ay magiging pareho:

Paggamit ng maraming VLOOKUP sa isang formula

Nangyayari na ang pangunahing talahanayan at ang lookup na talahanayan ay walang iisang column na magkapareho, at pinipigilan ka nitong gamitin ang karaniwang function. VPR. Gayunpaman, may isa pang talahanayan na hindi naglalaman ng impormasyong interesado kami, ngunit may karaniwang column na may pangunahing talahanayan at talahanayan ng paghahanap.

Tingnan natin ang sumusunod na halimbawa. Mayroon kaming Main table na may column SKU (bago), kung saan mo gustong magdagdag ng column na may mga kaukulang presyo mula sa isa pang talahanayan. Bilang karagdagan, mayroon kaming 2 lookup table. Ang una (Lookup table 1) ay naglalaman ng mga na-update na numero SKU (bago) at mga pangalan ng produkto, at ang pangalawa (Lookup table 2) – mga pangalan ng produkto at lumang numero SKU (luma).

Upang magdagdag ng mga presyo mula sa pangalawang lookup table sa pangunahing talahanayan, dapat kang magsagawa ng pagkilos na kilala bilang double VPR o nakapugad VPR.

1. Sumulat ng isang function VPR, na hinahanap ang pangalan ng produkto sa talahanayan Talahanayan ng paghahanap 1paggamit SKU, bilang ang nais na halaga:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   dito Bagong_SKU – pinangalanang saklaw $A:$B sa mesa Talahanayan ng paghahanap 1, 2 – ito ang column B, na naglalaman ng mga pangalan ng mga kalakal (tingnan ang larawan sa itaas)

2. Sumulat ng isang formula upang magpasok ng mga presyo mula sa isang talahanayan Talahanayan ng paghahanap 2 batay sa mga kilalang pangalan ng produkto. Upang gawin ito, i-paste ang formula na iyong ginawa kanina bilang ang lookup value para sa bagong function VPR:

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

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

   dito presyo – pinangalanang saklaw $A:$C sa mesa Talahanayan ng paghahanap 2, 3 ay ang column C na naglalaman ng mga presyo.

Ang figure sa ibaba ay nagpapakita ng resulta na ibinalik ng formula na aming nilikha:

Dynamic na pagpapalit ng data mula sa iba't ibang talahanayan gamit ang VLOOKUP at INDIRECT

Una, linawin natin kung ano ang ibig sabihin ng expression na "Dynamic na pagpapalit ng data mula sa iba't ibang mga talahanayan" upang matiyak na nagkakaintindihan tayo nang tama.

May mga sitwasyon kung kailan mayroong ilang mga sheet na may data ng parehong format, at kinakailangan upang kunin ang kinakailangang impormasyon mula sa isang tiyak na sheet, depende sa halaga na ipinasok sa isang naibigay na cell. Sa tingin ko mas madaling ipaliwanag ito sa isang halimbawa.

Isipin na mayroon kang mga ulat sa pagbebenta para sa ilang mga rehiyon na may parehong mga produkto at sa parehong format. Gusto mong makahanap ng mga numero ng benta para sa isang partikular na rehiyon:

Kung mayroon ka lamang dalawang ganoong ulat, maaari kang gumamit ng isang kahiya-hiyang simpleng formula na may mga function VPR и IF (KUNG) upang piliin ang nais na ulat na hahanapin:

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

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

Saan:

• $D $2 ay isang cell na naglalaman ng pangalan ng produkto. Tandaan na gumagamit kami ng mga ganap na sanggunian dito upang maiwasan ang pagbabago ng halaga ng paghahanap kapag kinokopya ang formula sa ibang mga cell.
• $D3 ay isang cell na may pangalan ng rehiyon. Gumagamit kami ng absolute column reference at relative row reference dahil plano naming kopyahin ang formula sa ibang mga cell sa parehong column.
• FL_Sales и CA_Sales – ang mga pangalan ng mga talahanayan (o pinangalanang mga hanay) na naglalaman ng kaukulang mga ulat sa pagbebenta. Maaari mong, siyempre, gamitin ang karaniwang mga pangalan ng sheet at mga sanggunian sa hanay ng cell, halimbawa 'FL Sheet'!$A$3:$B$10, ngunit ang mga pinangalanang hanay ay mas maginhawa.

Gayunpaman, kapag mayroong maraming ganoong mga talahanayan, ang function IF ay hindi ang pinakamahusay na solusyon. Sa halip, maaari mong gamitin ang function HINDI DIREKTA (INDIRECT) upang ibalik ang nais na hanay ng paghahanap.

Tulad ng alam mo, ang pag-andar HINDI DIREKTA ay ginagamit upang ibalik ang isang link na ibinigay ng isang text string, na kung ano mismo ang kailangan natin ngayon. Kaya, matapang na palitan sa formula sa itaas ang expression na may function IF mag-link sa function HINDI DIREKTA. Narito ang isang kumbinasyon VPR и HINDI DIREKTA mahusay na gumagana sa:

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

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

Saan:

• $D $2 – ito ay isang cell na may pangalan ng produkto, ito ay hindi nagbabago dahil sa ganap na link.
• $D3 ay ang cell na naglalaman ng unang bahagi ng pangalan ng rehiyon. Sa aming halimbawa, ito FL.
• _Mga benta – ang karaniwang bahagi ng pangalan ng lahat ng pinangalanang hanay o talahanayan. Kapag pinagsama sa halaga sa cell D3, bumubuo ito ng ganap na kwalipikadong pangalan ng kinakailangang hanay. Nasa ibaba ang ilang detalye para sa mga bago sa function HINDI DIREKTA.

Paano gumagana ang INDIRECT at VLOOKUP

Una, hayaan mong ipaalala ko sa iyo ang syntax ng function HINDI DIREKTA (INDIRECT):

INDIRECT(ref_text,[a1])

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

Ang unang argument ay maaaring isang cell reference (A1 o R1C1 style), isang pangalan ng range, o isang text string. Tinutukoy ng pangalawang argumento kung anong istilo ng link ang nakapaloob sa unang argumento:

• A1kung ang argumento ay TUNAY NA CODE (TRUE) o hindi tinukoy;
• R1C1, Kung FBILANG E (MALI).

Sa aming kaso, ang link ay may istilo A1, upang maaari mong iwanan ang pangalawang argumento at tumuon sa una.

Kaya't bumalik tayo sa aming mga ulat sa pagbebenta. Kung naaalala mo, ang bawat ulat ay isang hiwalay na talahanayan na matatagpuan sa isang hiwalay na sheet. Para gumana nang tama ang formula, dapat mong pangalanan ang iyong mga talahanayan (o mga hanay), at ang lahat ng mga pangalan ay dapat may isang karaniwang bahagi. Halimbawa, tulad nito: CA_Sales, FL_Sales, TX_Sales at iba pa. Tulad ng nakikita mo, ang "_Sales" ay naroroon sa lahat ng mga pangalan.

tungkulin HINDI DIREKTA ikinokonekta ang halaga sa column D at ang text string na "_Sales", sa gayon ay nagsasabi VPR kung saang talahanayan hahanapin. Kung ang cell D3 ay naglalaman ng value na "FL", hahanapin ng formula ang talahanayan FL_Sales, kung “CA” – sa talahanayan CA_Sales at iba pa.

Ang resulta ng mga function VPR и HINDI DIREKTA ay ang mga sumusunod:

Kung ang data ay matatagpuan sa iba't ibang mga aklat ng Excel, kailangan mong idagdag ang pangalan ng aklat bago ang pinangalanang hanay, halimbawa:

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

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

Kung ang function HINDI DIREKTA ay tumutukoy sa isa pang workbook, dapat na bukas ang workbook na iyon. Kung ito ay sarado, ang function ay mag-uulat ng isang error. #REF! (#SSYL!).