Zitsanzo Zapamwamba za VLOOKUP: Kusaka kwamitundu ingapo

Mu gawo lachiwiri la phunziro lathu pa ntchitoyi VPR (VLOOKUP) mu Excel, tisanthula zitsanzo zingapo zomwe zingakuthandizeni kuwongolera mphamvu zonse VPR kuthetsa ntchito zolakalaka kwambiri za Excel. Zitsanzozo zikuganiza kuti muli ndi chidziwitso choyambirira cha momwe ntchitoyi imagwirira ntchito. Ngati sichoncho, mungakonde kuyamba ndi gawo loyamba la phunziroli, lomwe limafotokoza kalembedwe kake ndi kagwiritsidwe ntchito koyambira. VPR. Chabwino, tiyeni tiyambe.

• Sakani mu Excel ndi njira zingapo
• Timachotsa 2nd, 3rd, etc. pogwiritsa ntchito VLOOKUP
• Bweretsani kubwereza konse kwa mtengo womwe mukufuna
• Kusaka kwa XNUMXD kodziwika ndi mzere ndi mzere
• Kugwiritsa ntchito ma VLOOKUP angapo munjira imodzi
• Kusintha kwamphamvu kwa data kuchokera kumatebulo osiyanasiyana

Sakani mu Excel ndi njira zingapo

ntchito VPR mu Excel ndi chida champhamvu kwambiri pofufuza zamtengo wapatali mu database. Komabe, pali malire akuluakulu - mawu ake amakulolani kuti mufufuze mtengo umodzi wokha. Nanga bwanji ngati mukufuna kusaka motengera zinthu zingapo? Mudzapeza yankho pansipa.

Chitsanzo 1: Sakani ndi njira ziwiri zosiyana

Tiyerekeze kuti tili ndi mndandanda wamaoda ndipo tikufuna kupeza Kuchuluka kwa katundu (Qty.), kutengera njira ziwiri - Dzina la kasitomala (Kasitomala) ndi Dzina la mankhwala (Katundu). Nkhaniyi ndi yovuta chifukwa chakuti aliyense wa ogula anaitanitsa mitundu ingapo ya katundu, monga tikuonera pa tebulo ili m'munsimu:

ntchito yanthawi zonse VPR sichingagwire ntchito munjira iyi chifukwa ibweza mtengo woyamba womwe wapeza womwe ukugwirizana ndi mtengo wowonera. Mwachitsanzo, ngati mukufuna kudziwa kuchuluka kwa chinthu Maswiti'olamulidwa ndi wogula Jeremy Hill, lembani ndondomeko iyi:

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

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

- chilinganizo ichi chidzabwezera zotsatira 15zogwirizana ndi mankhwala Maapulo, chifukwa ndi mtengo woyamba wofanana.

Pali njira yosavuta yopangira - pangani ndime yowonjezerapo kuti muphatikize zonse zomwe mukufuna. Mu chitsanzo chathu, awa ndi mizati Dzina la kasitomala (Kasitomala) ndi Dzina la mankhwala (Katundu). Musaiwale kuti gawo lophatikizidwa liyenera kukhala kumanzere kwenikweni pazosaka, popeza ndilamanzere komwe ntchitoyi ndi VPR amayang'ana mmwamba pofufuza mtengo.

Chifukwa chake, mumawonjezera gawo lothandizira patebulo ndikutengera fomula ili pamaselo ake onse: =B2&C2. Ngati mukufuna kuti chingwecho chiziwerengedwa bwino, mutha kulekanitsa mfundo zophatikizika ndi malo: =B2&»«&C2. Pambuyo pake, mutha kugwiritsa ntchito njira iyi:

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

Selo ili kuti B1 ili ndi mtengo wolumikizidwa wa mtsutso kumachika (lookup_value) ndi 4 - Kukangana nambala_index_nambala (nambala_gawo), mwachitsanzo, nambala yagawo lomwe lili ndi data yomwe iyenera kubwezedwa.

Chitsanzo 2: VLOOKUP ndi njira ziwiri zomwe tebulo likuwonedwa papepala lina

Ngati mukufuna kusintha tebulo lalikulu (Main table) powonjezera deta kuchokera patebulo lachiwiri (Table Lookup), lomwe lili pa pepala lina kapena buku lina la Excel, ndiye kuti mukhoza kusonkhanitsa mtengo womwe mukufuna mwachindunji mu ndondomeko yomwe mumayika. ku main table.

Monga m'chitsanzo cham'mbuyomu, mudzafunika gawo lothandizira patebulo la Lookup ndi zikhalidwe zophatikizidwa. Dengali liyenera kukhala lamanzere kwambiri pazosaka.

Kenako formula ndi VPR zitha kukhala chonchi:

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

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

Apa, magawo B ndi C ali ndi mayina a makasitomala ndi mayina azinthu, motsatana, ndi ulalo Maoda!$A&$2:$D$2 imatanthauzira tebulo kuti muyang'ane papepala lina.

Kuti fomulayo iwerengeke, mutha kupatsa mawonedwewo dzina, ndiyeno fomulayo idzawoneka yosavuta:

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

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

Kuti fomulayo igwire ntchito, zikhalidwe zomwe zili kumanzere kwa tebulo lomwe mukuyang'ana ziyenera kuphatikizidwa chimodzimodzi monga momwe mukufunira. Pachithunzi pamwambapa, tidaphatikiza mfundo za u2bu2band kuyika danga pakati pawo, momwemonso muyenera kuchitira pakukangana koyamba kwa ntchitoyi (BXNUMX& "" & CXNUMX).

Kumbukirani! ntchito VPR zochepera zilembo 255, sizingafufuze mtengo womwe ndi wautali kuposa zilembo 255. Kumbukirani izi ndipo onetsetsani kuti kutalika kwa mtengo womwe mukufuna sikudutsa malire awa.

Ndikuvomereza kuti kuwonjezera gawo lothandizira si njira yabwino kwambiri komanso yovomerezeka nthawi zonse. Mutha kuchitanso chimodzimodzi popanda gawo lothandizira, koma izi zingafune fomula yovuta kwambiri yokhala ndi ntchito zambiri INDEX (INDEX) ndi GANIZANI (ZAMBIRI ZOVUTIKA).

Timachotsa 2nd, 3rd, etc. pogwiritsa ntchito VLOOKUP

Inu mukudziwa kale zimenezo VPR ikhoza kubweza mtengo umodzi wokha wofananira, ndendende, wopezeka woyamba. Koma bwanji ngati mtengowu ukubwerezedwa kangapo pagulu lowonedwa, ndipo mukufuna kuchotsa 2 kapena 3 mwa iwo? Bwanji ngati mfundo zonse? Vutoli likuwoneka lovuta, koma yankho lilipo!

Tiyerekeze kuti gawo limodzi la tebulo lili ndi mayina a makasitomala (Dzina la Makasitomala), ndipo gawo lina lili ndi zinthu (Zogulitsa) zomwe adagula. Tiyeni tiyese kupeza zinthu za 2, 3 ndi 4 zogulidwa ndi kasitomala wopatsidwa.

Njira yosavuta ndiyo kuwonjezera gawo lothandizira patsogolo pa gawoli dzina la kasitomala ndikudzaza ndi mayina a kasitomala ndi nambala yobwereza ya dzina lililonse, mwachitsanzo, John Doe1, John Doe2 etc. Tidzachita chinyengo ndi manambala pogwiritsa ntchito ntchitoyi COUNTIF (COUNTIF), popeza mayina a kasitomala ali mugawo B:

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

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

Pambuyo pake, mutha kugwiritsa ntchito yamba VPRkuti mupeze dongosolo lofunikira. Mwachitsanzo:

• Pezani 2 ndi katundu wolamulidwa ndi kasitomala Dan Brown:

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

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

• Pezani 3 ndi katundu wolamulidwa ndi kasitomala Dan Brown:

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

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

M'malo mwake, mutha kuyika zolozera zamaselo ngati mtengo woyang'ana m'malo mwa mawu, monga zikuwonekera pachithunzichi:

Ngati mukungofuna 2-e kubwereza, mutha kuchita popanda gawo lothandizira pakupanga fomula yovuta kwambiri:

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

Mu formula iyi:

• $F$2 - cell yomwe ili ndi dzina la wogula (siyinasinthidwe, chonde dziwani - ulalo ndi mtheradi);
• $B $ - gawo dzina la kasitomala;
• Table4 - Gome lanu (malo ano akhozanso kukhala osiyanasiyana);
• $C16 - gawo lomaliza la tebulo lanu kapena gawo lanu.

Fomula iyi imapeza mtengo wachiwiri wofananira. Ngati mukufuna kuchotsa kubwereza kotsalira, gwiritsani ntchito njira yapitayi.

Ngati mukufuna mndandanda wa machesi onse - ntchito VPR uyu si wothandizira, chifukwa amangobwezera mtengo umodzi panthawi - nthawi. Koma Excel ili ndi ntchito yake INDEX (INDEX), yomwe imatha kuthana ndi ntchitoyi mosavuta. Momwe ndondomeko yotereyi idzawonekere, mudzaphunzira mu chitsanzo chotsatirachi.

Bweretsani kubwereza konse kwa mtengo womwe mukufuna

Monga tanena kale VPR sangatulutse zikhalidwe zonse zobwereza kuchokera pamasinthidwe. Kuti muchite izi, mufunika fomula yovuta kwambiri, yopangidwa ndi ntchito zingapo za Excel, monga INDEX (INDEX), Zochepa (ZANG'ONO) ndi Row (LINE)

Mwachitsanzo, ndondomeko ili m'munsiyi imapeza kubwereza konse kwa mtengo kuchokera ku selo F2 mumtundu B2:B16 ndi kubwezera zotsatira kuchokera ku mizere yofanana ndi 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));"")}

Lowetsani fomulayi m'maselo angapo oyandikana, monga ma cell f4:f8monga momwe tawonera m'chithunzichi. Chiwerengero cha ma cell chikuyenera kukhala chofanana kapena kupitilira kuchuluka komwe kungathe kubwereza zomwe zafufuzidwa. Osayiwala kudina Ctrl + Shift + Lowanikuti mulowetse ndondomeko yoyenera.

Ngati mukufuna kudziwa momwe imagwirira ntchito, tiyeni tilowe mu tsatanetsatane wa fomula pang'ono:

Chigawo 1:

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

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

$F$2=B2:B16 - yerekezerani mtengo mu cell F2 ndi chilichonse chomwe chili mugulu B2:B16. Ngati machesi apezeka, ndiye mawuwo MTIMA(C2:C16)-1 imabweretsanso nambala ya mzere wofananira (value -1 amakulolani kuti musaphatikizepo mzere wamutu). Ngati palibe machesi, ntchito IF (IF) imabweretsa chingwe chopanda kanthu.

Zotsatira zantchito IF (IF) padzakhala mizere yopingasa: {1,"",3,"",5,"","","","","","",12,"","",""}

Chigawo 2:

ROW()-3

СТРОКА()-3

Apa ntchito Row (LINE) imakhala ngati kauntala yowonjezera. Popeza ndondomekoyi imakopera m'maselo F4: F9, timachotsa nambala 3 kuchokera pazotsatira zantchito kuti mupeze mtengo 1 mu cell F4 (mzere 4, chotsani 3) kuti mupeze 2 mu cell F5 (mzere 5, chotsani 3) ndi zina zotero.

Chigawo 3:

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

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

ntchito Zochepa (Yang'ono) kubwerera n-uwu mtengo wocheperako pagulu la data. Kwa ife, malo omwe (kuchokera kuzing'ono kwambiri) kubwerera amatsimikiziridwa ndi ntchitoyo Row (LINE) (onani Gawo 2). Kotero, kwa selo F4 ntchito ACHANG'ORO({akagulu},1) akadzabweranso 1 ndi (smallest) array element, mwachitsanzo 1. Za cell F5 akadzabweranso 2 ndi chinthu chaching'ono kwambiri pamndandanda, ndicho 3, Ndi zina zotero.

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

ntchito INDEX (INDEX) imangobweza mtengo wa selo linalake pamndandanda c2: c16. Za cell F4 ntchito INDEX($C$2:$C$16) adzabwerera Maapulopakuti F5 ntchito INDEX($C$2:$C$16) adzabwerera Maswiti' ndi zina zotero.

Chigawo 5:

IFERROR()

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

Pomaliza, timayika fomula mkati mwa ntchitoyo IFERROR (IFERROR), chifukwa simungasangalale ndi uthenga wolakwika #AT (#N/A) ngati chiwerengero cha maselo omwe fomula imakoperayo ndi yocheperapo kuposa chiwerengero cha mipangidwe yobwereza mumtundu womwe ukuwonedwa.

Kusaka kwa XNUMXD kodziwika ndi mzere ndi mzere

Kuchita kusaka kwa XNUMXD ku Excel kumaphatikizapo kufunafuna mtengo ndi mzere wodziwika ndi nambala yazambiri. Mwa kuyankhula kwina, mukuchotsa mtengo wa selo pa mphambano ya mzere wina ndi mzere.

Chifukwa chake, tiyeni titembenukire ku tebulo lathu ndikulemba fomula yokhala ndi ntchito VPR, yomwe idzapeza zambiri za mtengo wa mandimu ogulitsidwa mu March.

Pali njira zingapo zosaka XNUMXD. Onani zomwe mungasankhe ndikusankha zomwe zikuyenerani inu bwino.

VLOOKUP ndi MATCH ntchito

Mutha kugwiritsa ntchito mitundu ingapo VPR (VLOOKUP) ndi ZAMBIRI ZOVUTIKA (MATCH) kuti mupeze mtengo pamzere wa minda Dzina la mankhwala (chingwe) ndi mwezi (gawo) la mndandanda womwe ukufunsidwa:

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

Njira yomwe ili pamwambapa ndi ntchito yokhazikika VPR, yomwe imayang'ana kufanana kwenikweni kwa mtengo wa "Mandimu" m'maselo A2 mpaka A9. Koma popeza simukudziwa kuti malonda a Marichi ali pati, simungathe kuyika nambala yazagawo pagawo lachitatu. VPR. M'malo mwake, imagwiritsidwa ntchito ZAMBIRI ZOVUTIKAkufotokoza ndime iyi.

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

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

Kutanthauziridwa m'chinenero cha anthu, ndondomekoyi imatanthauza:

• Tikuyang'ana otchulidwa "Mar" - kukangana kumachika (kuyang'ana_mtengo);
• Kuyang'ana m'maselo kuchokera ku A1 kupita ku I1 - kukangana lookup_array (kuyang'ana_magulu);
• Kubwezera zomwe zimafanana - kukangana match_mtundu (match_mtundu).

kugwiritsa 0 mu mkangano wachitatu, inu mukuti ntchito ZAMBIRI ZOVUTIKA yang'anani mtengo woyamba womwe umagwirizana ndendende ndi mtengo womwe mukuyang'ana. Izi ndizofanana ndi mtengo ZONYENGA (ZABODZA) pa mkangano wachinayi VPR.

Umu ndi momwe mungapangire njira yofufuzira yanjira ziwiri mu Excel, yomwe imadziwikanso ngati kusaka kwa mbali ziwiri kapena kusaka kwapawiri.

SUMPRODUCT ntchito

ntchito SUMPRODUCT (SUMPRODUCT) imabweretsanso kuchuluka kwazinthu zomwe zasankhidwa:

=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 ndi MATCH ntchito

M'nkhani yotsatira ndifotokoza izi mwatsatanetsatane, kotero mutha kungotengera fomula iyi:

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

Masiyana otchulidwa ndi woyendetsa mphambano

Ngati simuli mumitundu yonse yovuta ya Excel, mungakonde njira yowoneka bwino komanso yosaiwalika:

1. Sankhani tebulo, tsegulani tabu Mafomu (Mafomu) ndikudina Pangani kuchokera ku Selection (Pangani kuchokera pazosankha).
2. Chongani mabokosi Mzere wapamwamba (pa mzere pamwambapa) ndi Danga lakumanzere (mugawo kumanzere). Microsoft Excel ipereka mayina kumagulu kuchokera pamzere wapamwamba ndi gawo lakumanzere la spreadsheet yanu. Tsopano mutha kusaka pogwiritsa ntchito mayinawa mwachindunji osapanga mafomu.
3. Mu selo iliyonse yopanda kanthu, lembani =row_name column_name, mwachitsanzo monga chonchi:

   =Mandimu Mar

   ... kapena mosemphanitsa:

   =Mar Ndimu

   Kumbukirani kuti mayina a mzere ndi magawo ayenera kulekanitsidwa ndi danga, lomwe pamenepa limagwira ntchito ngati woyendetsa mphambano.

Mukalowetsa dzina, Microsoft Excel iwonetsa chida chokhala ndi mndandanda wa mayina ofananira, monga momwe mukulembera fomula.

4. Press Lowani ndipo fufuzani zotsatira

Nthawi zambiri, njira iliyonse yomwe ili pamwambapa yomwe mungasankhe, zotsatira zakusaka kwamitundu iwiri zidzakhala zofanana:

Kugwiritsa ntchito ma VLOOKUP angapo munjira imodzi

Zimachitika kuti tebulo lalikulu ndi tebulo loyang'ana zilibe gawo limodzi lofanana, ndipo izi zimakulepheretsani kugwiritsa ntchito ntchito yanthawi zonse. VPR. Komabe, pali tebulo lina lomwe liribe zomwe timakonda, koma lili ndi gawo lofanana ndi tebulo lalikulu ndi tebulo loyang'ana.

Tiyeni tione chitsanzo chotsatirachi. Tili ndi tebulo lalikulu lokhala ndi mzati SKU (yatsopano), komwe mukufuna kuwonjezera ndime yokhala ndi mitengo yofananira kuchokera patebulo lina. Kuphatikiza apo, tili ndi matebulo oyang'ana 2. Yoyamba (Kuyang'ana tebulo 1) ili ndi manambala osinthidwa SKU (yatsopano) ndi mayina azinthu, ndipo chachiwiri (Kuyang'ana tebulo 2) - mayina azinthu ndi manambala akale SKU (kale).

Kuti muwonjezere mitengo kuchokera patebulo loyang'ana lachiwiri kupita patebulo lalikulu, muyenera kuchita zomwe zimadziwika kuti pawiri VPR kapena zisa VPR.

1. Lembani ntchito VPR, yomwe imapeza dzina la malonda patebulo Onani tebulo 1ntchito SKU, monga mtengo wofunidwa:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   apa Chatsopano_SKU - dzina losiyana $A:$B mu tebulo Onani tebulo 1, 2 - iyi ndi gawo B, lomwe lili ndi mayina azinthu (onani chithunzi pamwambapa)

2. Lembani chilinganizo choyika mitengo kuchokera patebulo Onani tebulo 2 zochokera mayina odziwika mankhwala. Kuti muchite izi, ikani fomula yomwe mudapanga kale ngati mtengo wakusaka kwa ntchito yatsopanoyo VPR:

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

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

   apa Price - dzina losiyana $A:$C mu tebulo Onani tebulo 2, 3 ndi gawo C lomwe lili ndi mitengo.

Chithunzi chomwe chili pansipa chikuwonetsa zotsatira zomwe zabwezedwa ndi fomula yomwe tidapanga:

Kusintha kwamphamvu kwa data kuchokera pamagome osiyanasiyana pogwiritsa ntchito VLOOKUP ndi INDIRECT

Choyamba, tiyeni tifotokoze momveka bwino zomwe tikutanthauza ndi mawu akuti "Kusintha kwamphamvu kwa data kuchokera kumatebulo osiyanasiyana" kuti tiwonetsetse kuti timamvetsetsana bwino.

Pali zochitika pamene pali mapepala angapo omwe ali ndi deta yamtundu womwewo, ndipo m'pofunika kuchotsa zofunikira kuchokera papepala linalake, malingana ndi mtengo umene walowa mu selo lopatsidwa. Ndikuganiza kuti ndizosavuta kufotokoza izi ndi chitsanzo.

Tangoganizani kuti muli ndi malipoti amalonda a zigawo zingapo zomwe zili ndi zinthu zomwezo komanso zamtundu womwewo. Mukufuna kupeza ziwerengero zogulitsa zadera linalake:

Ngati muli ndi malipoti awiri okha, mutha kugwiritsa ntchito njira yosavuta yochititsa manyazi yokhala ndi magwiridwe antchito VPR и IF (IF) kusankha lipoti lomwe mukufuna kuti mufufuze:

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

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

Kumene:

• $D $2 ndi selo lomwe lili ndi dzina la chinthucho. Zindikirani kuti timagwiritsa ntchito maumboni apa kuti tipewe kusintha mtengo wowonera pokopera fomula kumaselo ena.
• $D3 ndi cell yokhala ndi dzina la dera. Tikugwiritsa ntchito zolozera m'zambiri ndi zolozera pamzere wachibale chifukwa tikufuna kukopera fomulayo kumaselo ena omwe ali mugawo lomwelo.
• FL_Sales и CA_Zogulitsa - mayina a matebulo (kapena mayina) omwe ali ndi malipoti ogwirizana nawo. Mukhoza, ndithudi, kugwiritsa ntchito mayina a mapepala achizolowezi ndi maumboni amtundu wa ma cell, mwachitsanzo 'FL Mapepala'!$A$3:$B$10, koma mitundu yotchulidwa ndiyosavuta kwambiri.

Komabe, pakakhala matebulo ambiri otere, ntchitoyo IF si njira yabwino yothetsera. M'malo mwake, mutha kugwiritsa ntchito KUMALO (INDIRECT) kuti mubweze masanjidwe omwe mukufuna.

Monga mukudziwa, ntchito KUMALO amagwiritsidwa ntchito kubweza ulalo woperekedwa ndi chingwe chalemba, chomwe ndi chomwe tikufuna tsopano. Chifukwa chake, sinthani molimba mtima mawu omwe ali pamwambawa ndi ntchitoyo IF kugwirizana ndi ntchito KUMALO. Apa pali kuphatikiza VPR и KUMALO imagwira ntchito bwino ndi:

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

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

Kumene:

• $D $2 - iyi ndi selo yokhala ndi dzina la mankhwala, sichisintha chifukwa cha chiyanjano chenicheni.
• $D3 ndi selo lomwe lili ndi gawo loyamba la dzina la dera. Mu chitsanzo chathu, izi FL.
• _Zogulitsa - gawo lodziwika bwino lamitundu yonse yotchulidwa kapena matebulo. Ikaphatikizidwa ndi mtengo mu selo D3, imapanga dzina loyenerera lamtundu wofunikira. M'munsimu muli tsatanetsatane wa omwe ali atsopano ku ntchitoyi KUMALO.

Momwe INDIRECT ndi VLOOKUP zimagwirira ntchito

Choyamba, ndiloleni ndikukumbutseni kalembedwe ka ntchitoyi KUMALO (INDIRECT):

INDIRECT(ref_text,[a1])

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

Mtsutso woyamba ukhoza kukhala kalembedwe ka selo (kalembedwe ka A1 kapena R1C1), dzina losiyanasiyana, kapena chingwe cholemba. Mtsutso wachiwiri umatsimikizira mtundu wa ulalo womwe uli mumtsutso woyamba:

• A1ngati mkangano uli KODI YOONA (ZOONA) kapena sizinatchulidwe;
• Mtengo wa R1C1, ngati FAS E (ZABODZA).

Kwa ife, ulalo uli ndi kalembedwe A1, kotero mutha kusiya mkangano wachiwiri ndikungoyang'ana koyamba.

Ndiye tiyeni tibwererenso kumalipoti athu ogulitsa. Ngati mukukumbukira, ndiye kuti lipoti lililonse ndi tebulo lapadera lomwe lili pa pepala losiyana. Kuti fomulayo igwire bwino ntchito, muyenera kutchula matebulo anu (kapena mindandanda), ndipo mayina onse ayenera kukhala ndi gawo limodzi. Mwachitsanzo, monga chonchi: CA_Zogulitsa, FL_Sales, TX_Zogulitsa ndi zina zotero. Monga mukuwonera, "_Sales" ilipo m'maina onse.

ntchito KUMALO amalumikiza mtengo mu gawo D ndi zingwe zolembedwa "_Sales", potero amauza VPR patebulo losakasaka. Ngati selo D3 ili ndi mtengo "FL", ndondomekoyi idzafufuza tebulo FL_Sales, ngati "CA" - patebulo CA_Zogulitsa ndi zina zotero.

Zotsatira za ntchito VPR и KUMALO zikhala izi:

Ngati zambirizo zili m'mabuku osiyanasiyana a Excel, ndiye kuti muyenera kuwonjezera dzina la bukhulo pamaso pa mndandanda womwe watchulidwa, mwachitsanzo:

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

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

Ngati ntchito KUMALO likunena za buku lina lantchito, bukulo liyenera kutsegulidwa. Ngati chatsekedwa, ntchitoyi idzanena zolakwika. #REF! (#SYL!).