Contents
- Bincika a cikin Excel ta ma'auni da yawa
- Muna fitar da darajoji na 2, 3, da sauransu ta amfani da VLOOKUP
- Maido duk maimaita ƙimar da ake so
- Binciken XNUMXD ta sanannen layi da shafi
- Amfani da VLOOKUPs da yawa a cikin tsari ɗaya
- Canjin canjin bayanai daga tebur daban-daban ta amfani da VLOOKUP da INDIRECT
A kashi na biyu na koyarwarmu kan aikin VPR (VLOOKUP) a cikin Excel, za mu bincika ƴan misalan da za su taimaka muku jagorantar duk iko VPR don magance mafi yawan ayyuka na Excel. Misalan suna ɗauka cewa kun riga kun sami ainihin ilimin yadda wannan fasalin ke aiki. Idan ba haka ba, kuna iya sha'awar farawa da ɓangaren farko na wannan koyawa, wanda ke bayyana ma'auni da kuma yadda ake amfani da shi. VPR. To, bari mu fara.
Bincika a cikin Excel ta ma'auni da yawa
aiki VPR a cikin Excel kayan aiki ne mai ƙarfi na gaske don yin bincike don takamaiman ƙima a cikin bayanan bayanai. Duk da haka, akwai ƙayyadaddun ƙayyadaddun ƙayyadaddun ƙayyadaddun ƙayyadaddun tsarinsa yana ba ku damar bincika ƙima ɗaya kawai. Idan kuna son bincika ta yanayi da yawa fa? Za ku sami mafita a kasa.
Misali 1: Bincika ta ma'auni 2 daban-daban
A ce muna da jerin umarni kuma muna so mu nemo Yawan kaya (Qty.), bisa ga ma'auni guda biyu - Sunan abokin ciniki (Customer) и Sunan samfurin (samfurin). Al'amarin yana da rikitarwa ta gaskiyar cewa kowane mai siye ya ba da umarnin nau'ikan kayayyaki da yawa, kamar yadda ake iya gani daga teburin da ke ƙasa:
aiki na yau da kullun VPR ba zai yi aiki a cikin wannan yanayin ba saboda zai dawo da ƙimar farko da ya gano wanda yayi daidai da ƙimar binciken da aka bayar. Misali, idan kana son sanin adadin abu Sweets'mai siye ya umarta Jeremy Hill, rubuta wannan dabara:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– wannan dabara zai mayar da sakamakon 15daidai da samfurin apples, domin ita ce darajar farko da ta yi daidai.
Akwai sauƙi mai sauƙi - ƙirƙirar ƙarin ginshiƙi wanda za'a haɗa duk ka'idodin da ake so. A cikin misalinmu, waɗannan su ne ginshiƙai Sunan abokin ciniki (Customer) и Sunan samfurin (samfurin). Kar a manta cewa ginshiƙin da aka haɗa dole ne ya kasance koyaushe ya kasance ginshiƙin hagu a cikin kewayon bincike, tunda ginshiƙin hagu ne aikin ke aiki. VPR duba sama lokacin neman ƙima.
Don haka, kun ƙara ginshiƙi na taimako zuwa teburin kuma ku kwafi wannan dabara akan duk sel ɗin sa: = B2&C2. Idan kana son kirtani ta zama abin karantawa, za ka iya raba haɗe-haɗen dabi'u tare da sarari: =B2&» «&C2. Bayan haka, zaku iya amfani da dabarar da ke gaba:
=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;ЛОЖЬ)
Ina tantanin halitta B1 ya ƙunshi ƙaƙƙarfan ƙimar hujja dubawa (lokup_darajar) da 4 – Hujja col_index_num (column_number), watau lambar ginshiƙi mai ɗauke da bayanan da za a dawo dasu.
Misali 2: VLOOKUP ta ma'auni biyu tare da kallon tebur akan wani takardar
Idan kana buƙatar sabunta babban tebur (Main Tebur) ta ƙara bayanai daga tebur na biyu (Lookup table), wanda ke kan wani takarda ko a cikin wani littafin aikin Excel, to, zaku iya tattara ƙimar da ake so kai tsaye a cikin dabarar da kuka saka. cikin babban teburi.
Kamar yadda yake a cikin misalin da ya gabata, kuna buƙatar ginshiƙi na taimako a cikin Teburin Nemo tare da haɗakar dabi'u. Dole ne wannan shafi ya zama ginshiƙin hagu a cikin kewayon bincike.
Don haka tsari tare da VPR zai iya zama kamar haka:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Anan, ginshiƙan B da C sun ƙunshi sunayen abokin ciniki da sunayen samfur, bi da bi, da hanyar haɗin gwiwa Umarni!$A&$2:$D$2 yana bayyana tebur don duba cikin wani takardar.
Don yin dabarar da za a iya karantawa, zaku iya ba da ra'ayi suna, sannan tsarin zai yi kama da sauƙi:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Don dabarar ta yi aiki, ƙimar da ke cikin ginshiƙin hagu na teburin da kuke kallo dole ne a haɗa su daidai daidai da ma'aunin bincike. A cikin adadi da ke sama, mun haɗu da dabi'u u2bu2band sanya sarari a tsakanin su, kamar yadda kuke buƙatar yin a cikin hujja na farko na aikin (BXNUMX & "" & CXNUMX).
Tuna! aiki VPR iyakance ga haruffa 255, ba zai iya bincika ƙimar da ta wuce tsayin haruffa 255 ba. Yi la'akari da wannan kuma tabbatar da cewa tsawon ƙimar da ake so bai wuce wannan iyaka ba.
Na yarda cewa ƙara ginshiƙi na taimako ba shine mafi kyawun kyan gani ba kuma ba koyaushe abin karɓa bane. Hakanan zaka iya yin haka ba tare da ginshiƙin mai taimako ba, amma wannan zai buƙaci tsari mai rikitarwa mai yawa tare da haɗin ayyuka INDEX (INDEX) da MATCH (MORE WALLAHI).
Muna fitar da darajoji na 2, 3, da sauransu ta amfani da VLOOKUP
Kun riga kun san hakan VPR zai iya dawo da ƙima guda ɗaya kawai, mafi daidai, na farko da aka samo. Amma menene idan an maimaita wannan ƙimar sau da yawa a cikin tsararrun da aka gani, kuma kuna son cire 2nd ko 3rd daga cikinsu? Idan duk darajar fa? Matsalar tana da wahala, amma akwai mafita!
A ce wani ginshiƙi na tebur ya ƙunshi sunayen abokan ciniki (Sunan Abokin Ciniki), ɗayan kuma yana ɗauke da samfuran (Kayayyakin) da suka saya. Mu yi kokarin nemo abubuwa na 2, 3 da 4 da wani abokin ciniki ya saya.
Hanya mafi sauƙi ita ce ƙara ginshiƙi na taimako kafin ginshiƙi sunan abokin ciniki kuma cika shi da sunayen abokan ciniki tare da lambar maimaita kowane suna, misali, John Doe1, John Doe2 da sauransu. Za mu yi dabara tare da lambobi ta amfani da aikin COUNTIF (COUNTIF), da aka ba cewa sunayen abokan ciniki suna cikin shafi B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Bayan haka zaka iya amfani da aikin al'ada VPRdon nemo odar da ake bukata. Misali:
- Find 2- ta abun da abokin ciniki ya umarta Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Find 3- ta abun da abokin ciniki ya umarta Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
A zahiri, zaku iya shigar da bayanin tantanin halitta azaman ƙimar bincike maimakon rubutu, kamar yadda aka nuna a adadi mai zuwa:
Idan kawai kuna nema 2-e maimaitawa, zaku iya yin shi ba tare da ginshiƙin taimako ba ta hanyar ƙirƙirar tsari mai rikitarwa:
=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;ИСТИНА);"")
A cikin wannan tsari:
- $F$2 - tantanin halitta dauke da sunan mai siye (ba a canzawa, don Allah a lura - hanyar haɗin kai cikakke ne);
- $ B$ - shafi sunan abokin ciniki;
- Table4 - Teburin ku (wannan wurin kuma na iya zama kewayon yau da kullun);
- $C16 – Tantanin halitta na ƙarshen tebur ko kewayon ku.
Wannan dabarar tana samun ƙimar daidai ta biyu kawai. Idan kana buƙatar cire sauran maimaitawar, yi amfani da maganin da ya gabata.
Idan kana buƙatar jerin duk matches - aikin VPR wannan ba mataimaki ba ne, tun da yake mayar da ƙima ɗaya kawai a lokaci guda - lokaci. Amma Excel yana da aiki INDEX (INDEX), wanda zai iya jure wa wannan aikin cikin sauƙi. Yadda irin wannan tsari zai kasance, za ku koyi a cikin misali mai zuwa.
Maido duk maimaita ƙimar da ake so
Kamar yadda aka ambata a sama VPR ba zai iya cire duk kwafin ƙima daga kewayon da aka bincika ba. Don yin wannan, kuna buƙatar ƙayyadaddun ƙayyadaddun tsari, wanda ya ƙunshi ayyuka da yawa na Excel, kamar INDEX (INDEX), KYAUTATA (KARAMIN) da Row (LINE)
Misali, dabarar da ke ƙasa tana samun duk maimaita ƙimar daga tantanin halitta F2 a cikin kewayon B2:B16 kuma yana dawo da sakamakon daga layuka iri ɗaya a shafi na 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));"")}
Shigar da wannan tsarin tsararru cikin sel masu yawa kusa, kamar sel f4: f8kamar yadda aka nuna a hoton da ke ƙasa. Dole ne adadin ƙwayoyin sel ya zama daidai ko girma fiye da matsakaicin yiwuwar adadin maimaita ƙimar da aka nema. Kar a manta da dannawa Ctrl + Shigar + Shigardon shigar da tsarin tsarawa daidai.
Idan kuna sha'awar fahimtar yadda yake aiki, bari mu nutse cikin cikakkun bayanai na dabarar:
Part 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - kwatanta ƙimar cikin tantanin halitta F2 tare da kowane ƙimar da ke cikin kewayon B2: B16. Idan an sami ashana, sai a yi magana STRING(C2:C16)-1 yana mayar da adadin layin da ya dace (darajar -1 yana ba ku damar haɗa layin taken). Idan babu matches, aikin IF (IF) ya dawo da fanko kirtani.
Sakamakon aiki IF (IF) za a sami irin wannan jeri na kwance: {1,"",3,"",5,"","","","","","",12,"","",""}
Part 2:
ROW()-3
СТРОКА()-3
Anan aikin Row (LINE) yana aiki azaman ƙarin ƙira. Tunda ana kwafin dabarar cikin sel F4:F9, muna cire lambar 3 daga sakamakon aiki don samun ƙima 1 a cikin tantanin halitta F4 (layi na 4, ragi na 3) don samun 2 a cikin tantanin halitta F5 (layi na 5, ragi na 3) da sauransu.
Part 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
aiki KYAUTATA (SANNAN) ya dawo n-oh mafi ƙarancin ƙima a cikin tsararrun bayanai. A cikin yanayinmu, wane matsayi (daga mafi ƙanƙanta) don dawowa yana ƙaddara ta hanyar aikin Row (LINE) (duba Sashe na 2). Don haka, ga tantanin halitta F4 aiki KARAMIN ({array},1) dawo 1- ta (ƙaramin) tsararrun kashi, watau 1. Don tantanin halitta F5 dawo 2- ta mafi ƙanƙanta kashi a cikin tsararru, wato 3, Da dai sauransu
Part 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))
aiki INDEX (INDEX) kawai yana mayar da ƙimar takamaiman tantanin halitta a cikin tsararru ku: ku2. Don tantanin halitta F4 aiki INDEX($C$2:$C$16) zai dawo applesMa F5 aiki INDEX($C$2:$C$16) zai dawo Sweets' da sauransu.
Part 5:
IFERROR()
ЕСЛИОШИБКА()
A ƙarshe, mun sanya dabarar a cikin aikin IFEROR (IFERROR), saboda da wuya ka gamsu da saƙon kuskure #AT (#N/A) idan adadin sel ɗin da aka kwafi dabarar a cikin su bai kai adadin kwafin dabi'u a cikin kewayon da ake kallo ba.
Binciken XNUMXD ta sanannen layi da shafi
Yin bincike na XNUMXD a cikin Excel ya haɗa da neman ƙima ta hanyar sanannen layi da lambar shafi. A wasu kalmomi, kuna ciro ƙimar tantanin halitta a tsakar wani layi da ginshiƙi.
Don haka, bari mu juya zuwa teburin mu mu rubuta dabara mai aiki VPR, wanda zai sami bayanai game da farashin lemun tsami da ake sayarwa a watan Maris.
Akwai hanyoyi da yawa don yin bincike na XNUMXD. Duba zaɓuɓɓukan kuma zaɓi wanda ya fi dacewa da ku.
Ayyukan VLOOKUP da MATCH
Kuna iya amfani da tarin ayyuka VPR (VLOOKUP) da MORE BAYYANA (MATCH) don nemo kimar a mahadar filayen Sunan samfurin (zaure) kuma Watan (column) na tsararrun da ake tambaya:
=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);ЛОЖЬ)
Tsarin da ke sama aiki ne na yau da kullun VPR, wanda ke neman daidai madaidaicin darajar “Lemons” a cikin sel A2 zuwa A9. Amma tun da ba ku san wane shafi na tallace-tallace na Maris ke ciki ba, ba za ku iya saita lambar shafi don hujjar aiki ta uku ba. VPR. Maimakon haka, ana amfani da aikin MORE BAYYANAdon ayyana wannan shafi.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Fassara zuwa harshen ɗan adam, wannan dabarar tana nufin:
- Muna neman haruffan "Mar" - gardama dubawa (lokup_darajar);
- Neman sel daga A1 zuwa I1 - hujja duba_array (duba_array);
- Koma daidai daidai - hujja nau'in matches (match_type).
Amfani 0 a cikin hujja ta uku, kun ce ayyuka MORE BAYYANA nemo kimar farko wadda ta yi daidai da darajar da kuke nema. Wannan yayi daidai da ƙimar KARYA (KARYA) ga hujja ta hudu VPR.
Wannan shine yadda zaku iya ƙirƙirar dabarar bincike ta hanyoyi biyu a cikin Excel, wanda kuma aka sani da bincike mai girma biyu ko bincike bidirectional.
Ayyukan SUMPRODUCT
aiki SUMPRODUCT (SUMPRODUCT) yana mayar da jimillar samfuran da aka zaɓa:
=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)
Ayyukan INDEX da MATCH
A cikin labarin na gaba zan yi bayanin waɗannan ayyuka dalla-dalla, don haka a yanzu zaku iya kwafi wannan dabarar:
=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))
Sunan jeri da ma'aikacin haɗin gwiwa
Idan ba ku cikin duk waɗannan ƙayyadaddun dabarun Excel, kuna iya son wannan hanyar gani da abin tunawa:
- Zaɓi tebur, buɗe shafin dabarbari (Formulas) kuma danna Ƙirƙiri daga Zaɓi (ƙirƙira daga zaɓi).
- Duba akwatunan Babban layi (a kan layin da ke sama) da Rukunin hagu (a cikin shafi na hagu). Microsoft Excel zai sanya sunaye zuwa jeri daga ƙimar da ke cikin layi na sama da shafi na hagu na maƙunsar ku. Yanzu zaku iya bincika ta amfani da waɗannan sunaye kai tsaye ba tare da ƙirƙirar dabaru ba.
- A cikin kowane tantanin halitta mara komai, rubuta = layin_suna shafi_suna, misali kamar haka:
= Lemun tsami Mar
… ko akasin haka:
=Mar Lemo
Ka tuna cewa jeri da sunayen ginshiƙi dole ne a raba su da sarari, wanda a wannan yanayin yana aiki kamar ma'aikacin haɗin gwiwa.
Lokacin shigar da suna, Microsoft Excel zai nuna kayan aiki tare da jerin sunayen da suka dace, kamar lokacin da kuka shigar da dabara.
- latsa Shigar kuma duba sakamakon
Gabaɗaya, kowace irin waɗannan hanyoyin da kuka zaɓa, sakamakon bincike mai girma biyu zai zama iri ɗaya:
Amfani da VLOOKUPs da yawa a cikin tsari ɗaya
Yana faruwa cewa babban tebur da tebur ɗin dubawa ba su da shafi guda ɗaya ɗaya, kuma wannan yana hana ku yin amfani da aikin da aka saba. VPR. Duk da haka, akwai wani tebur wanda ba ya ƙunshi bayanin da muke sha'awar, amma yana da ginshiƙi na gama gari tare da babban tebur da teburin dubawa.
Bari mu kalli misali na gaba. Muna da babban tebur mai shafi SKU (sabo), inda kake son ƙara ginshiƙi tare da madaidaicin farashin daga wani tebur. Bugu da kari, muna da tebur duba 2. Na farko (Tebur na duba 1) ya ƙunshi sabunta lambobi SKU (sabo) da sunayen samfur, da na biyu (Tebur Duba 2) - sunayen samfur da tsoffin lambobi SKU (tsohuwar).
Don ƙara farashin daga tebur dubawa na biyu zuwa babban tebur, dole ne ku yi wani aiki da aka sani da ninki biyu VPR ko gida VPR.
- Rubuta aiki VPR, wanda ya samo sunan samfurin a cikin tebur Teburin dubawa 1ta yin amfani da SKU, kamar yadda ake so darajar:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)nan Sabon_SKU – mai suna kewayon $A:$B a cikin tebur Teburin dubawa 1, 2 – wannan shafi ne B, wanda ya ƙunshi sunayen kayan (duba hoton da ke sama)
- Rubuta dabara don saka farashin daga tebur Teburin dubawa 2 dangane da sanannun samfuran sunayen. Don yin wannan, liƙa dabarar da kuka ƙirƙira a baya azaman ƙimar neman sabon aikin VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)nan price – mai suna kewayon $A:$C a cikin tebur Teburin dubawa 2, 3 shafi ne C dauke da farashi.
Hoton da ke ƙasa yana nuna sakamakon da aka dawo da tsarin da muka ƙirƙira:
Canjin canjin bayanai daga tebur daban-daban ta amfani da VLOOKUP da INDIRECT
Da farko, bari mu fayyace abin da muke nufi da furcin nan “Musanya bayanai daga teburi daban-daban” don tabbatar da fahimtar juna daidai.
Akwai yanayi lokacin da akwai zanen gado da yawa tare da bayanai iri ɗaya, kuma wajibi ne a fitar da mahimman bayanai daga wani takarda, dangane da ƙimar da aka shigar a cikin tantanin halitta. Ina tsammanin yana da sauƙi a bayyana wannan tare da misali.
Yi tunanin cewa kuna da rahotannin tallace-tallace don yankuna da yawa tare da samfurori iri ɗaya kuma a cikin tsari iri ɗaya. Kuna son nemo alkaluman tallace-tallace na takamaiman yanki:
Idan kuna da irin waɗannan rahotanni guda biyu kawai, to zaku iya amfani da tsari mai sauƙi mara kunya tare da ayyuka VPR и IF (IF) don zaɓar rahoton da ake so don bincika:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
inda:
- $D $2 cell ne mai dauke da sunan samfurin. Lura cewa muna amfani da cikakkiyar nassoshi anan don guje wa canza ƙimar dubawa lokacin kwafin dabara zuwa wasu sel.
- $D3 cell ne mai sunan yankin. Muna amfani da cikakkiyar ma'anar shafi da ma'anar layin dangi saboda muna shirin kwafin dabarar zuwa wasu sel a cikin ginshiƙi ɗaya.
- FL_Sales и CA_Sales - sunayen tebur (ko jeri mai suna) waɗanda ke ɗauke da rahotannin tallace-tallace masu dacewa. Kuna iya, ba shakka, amfani da sunayen takardar da aka saba da nassoshi kewayon tantanin halitta, misali 'FL Sheet'!$A$3:$B$10, amma jeri mai suna sun fi dacewa.
Duk da haka, lokacin da akwai da yawa irin wannan tebur, aikin IF ba shine mafi kyawun mafita ba. Madadin haka, zaku iya amfani da aikin GASKIYA (INDIRECT) don mayar da kewayon bincike da ake so.
Kamar yadda ka sani, aikin GASKIYA ana amfani da shi don dawo da hanyar haɗin da aka bayar ta hanyar rubutu, wanda shine ainihin abin da muke buƙata yanzu. Don haka, da gaba gaɗi maye gurbin a cikin dabarar da ke sama kalmar tare da aikin IF don haɗi tare da aiki GASKIYA. Ga hadin kai VPR и GASKIYA yana aiki da kyau tare da:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
inda:
- $D $2 - wannan tantanin halitta ne tare da sunan samfurin, ba ya canzawa saboda cikakkiyar hanyar haɗi.
- $D3 ita ce tantanin halitta mai ɗauke da sashin farko na sunan yankin. A cikin misalinmu, wannan FL.
- _Saidai - ɓangaren gama gari na sunan duk jeri ko tebur mai suna. Lokacin da aka haɗa tare da ƙima a cikin tantanin halitta D3, yana samar da cikakken sunan kewayon da ake buƙata. A ƙasa akwai wasu cikakkun bayanai ga waɗanda sababbi ne ga aikin GASKIYA.
Yadda INDIRECT da VLOOKUP ke aiki
Da farko, bari in tunatar da ku tsarin tsarin aikin GASKIYA (KA KYAU):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Hujja ta farko na iya zama bayanin tantanin halitta (Salon A1 ko R1C1), sunan kewayon, ko sigar rubutu. Hujja ta biyu ta tantance wane salon hanyar haɗin gwiwa ke ƙunshe a hujjar farko:
- A1idan hujjar ita ce KODA GASKIYA (GASKIYA) ko ba a kayyade ba;
- Saukewa: R1C1, idan FAS E (KARYA).
A cikin yanayinmu, hanyar haɗi tana da salon A1, don haka za ku iya barin hujja ta biyu kuma ku mai da hankali kan na farko.
Don haka bari mu koma kan rahoton tallace-tallacenmu. Idan kun tuna, to kowane rahoto wani tebur ne daban wanda ke kan takarda daban. Domin dabarar ta yi aiki daidai, dole ne ku sanya sunayen tebur ɗinku (ko jeri), kuma duk sunaye dole ne su kasance da ɓangaren gama gari. Misali, kamar haka: CA_Sales, FL_Sales, TX_Sales da sauransu. Kamar yadda kake gani, "_Sales" yana nan a duk sunaye.
aiki GASKIYA yana haɗa ƙima a cikin ginshiƙi D da kuma rubutun rubutun "_Sales", don haka faɗa VPR a cikin wane tebur don bincika. Idan cell D3 ya ƙunshi darajar "FL", tsarin zai bincika tebur FL_Sales, idan "CA" - a cikin tebur CA_Sales da sauransu.
Sakamakon ayyukan VPR и GASKIYA zai kasance kamar haka:
Idan bayanan suna cikin littattafan Excel daban-daban, to kuna buƙatar ƙara sunan littafin kafin kewayon mai suna, misali:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Idan aikin GASKIYA yana nufin wani littafin aiki, dole ne littafin aikin ya kasance a buɗe. Idan an rufe, aikin zai ba da rahoton kuskure. #REF! (#SSYL!).