Yaliyomo
- Tafuta katika Excel kwa vigezo vingi
- Tunatoa maadili ya 2, 3, nk kwa kutumia VLOOKUP
- Rejesha marudio yote ya thamani inayotakiwa
- Utafutaji wa XNUMXD kwa safu mlalo na safu inayojulikana
- Kutumia VLOOKUP nyingi katika fomula moja
- Ubadilishaji unaobadilika wa data kutoka kwa majedwali tofauti kwa kutumia VLOOKUP na INDIRECT
Katika sehemu ya pili ya mafunzo yetu juu ya kazi VPR (VLOOKUP) katika Excel, tutachambua mifano michache ambayo itakusaidia kuelekeza nguvu zote VPR kutatua kazi kubwa zaidi za Excel. Mifano inadhania kuwa tayari una ujuzi wa kimsingi wa jinsi kipengele hiki kinavyofanya kazi. Ikiwa sivyo, unaweza kutaka kuanza na sehemu ya kwanza ya mafunzo haya, ambayo inaelezea sintaksia na matumizi ya kimsingi. VPR. Naam, tuanze.
Tafuta katika Excel kwa vigezo vingi
kazi VPR katika Excel ni zana yenye nguvu sana ya kufanya utafutaji wa thamani fulani katika hifadhidata. Hata hivyo, kuna upungufu mkubwa - syntax yake inakuwezesha kutafuta thamani moja tu. Je, ikiwa unataka kutafuta kwa kutumia hali nyingi? Utapata suluhisho hapa chini.
Mfano 1: Tafuta kwa vigezo 2 tofauti
Tuseme tuna orodha ya maagizo na tunataka kupata Wingi wa bidhaa (Qty.), kwa kuzingatia vigezo viwili - Jina la mteja (Mteja) na Название продукта (Bidhaa). Jambo hilo ni ngumu na ukweli kwamba kila mmoja wa wanunuzi aliamuru aina kadhaa za bidhaa, kama inavyoonekana kutoka kwa jedwali hapa chini:
kazi ya kawaida VPR haitafanya kazi katika hali hii kwa sababu itarudisha thamani ya kwanza itakayopata inayolingana na thamani iliyotolewa ya kuangalia. Kwa mfano, ikiwa unataka kujua idadi ya kitu Pipi'iliyoagizwa na mnunuzi Jeremy Hill, andika fomula ifuatayo:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- formula hii itarudisha matokeo 15sambamba na bidhaa apples, kwa sababu ni thamani ya kwanza inayolingana.
Kuna kazi rahisi - unda safu ya ziada ambayo kuchanganya vigezo vyote vinavyohitajika. Katika mfano wetu, hizi ni nguzo Jina la mteja (Mteja) na Название продукта (Bidhaa). Usisahau kwamba safu wima iliyounganishwa lazima iwe safu ya kushoto kabisa katika safu ya utafutaji, kwa kuwa ni safu wima ya kushoto ambayo chaguo la kukokotoa. VPR huangalia juu wakati wa kutafuta thamani.
Kwa hivyo, unaongeza safu-saidizi kwenye jedwali na unakili fomula ifuatayo juu ya seli zake zote: =B2&C2. Ikiwa unataka kamba isomeke zaidi, unaweza kutenganisha maadili yaliyounganishwa na nafasi: =B2&» «&C2. Baada ya hayo, unaweza kutumia formula ifuatayo:
=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;ЛОЖЬ)
Seli iko wapi B1 ina thamani iliyoambatanishwa ya hoja kupakua_value (lokup_value) na 4 - Hoja col_index_num (nambari_ya_safu), yaani, nambari ya safu iliyo na data itakayorejeshwa.
Mfano 2: VLOOKUP kwa vigezo viwili huku jedwali likitazamwa kwenye laha nyingine
Ikiwa unahitaji kusasisha jedwali kuu (Jedwali kuu) kwa kuongeza data kutoka kwa jedwali la pili (Jedwali la Kuangalia), ambalo liko kwenye karatasi nyingine au kwenye kitabu kingine cha kazi cha Excel, basi unaweza kukusanya thamani inayotaka moja kwa moja kwenye fomula unayoingiza. kwenye meza kuu.
Kama katika mfano uliopita, utahitaji safu-saidizi kwenye jedwali la Kutafuta na maadili yaliyounganishwa. Safu wima hii lazima iwe safu wima ya kushoto kabisa katika safu ya utafutaji.
Kwa hivyo formula na VPR inaweza kuwa kama hii:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Hapa, safu wima B na C zina majina ya wateja na majina ya bidhaa, mtawalia, na kiungo Maagizo!$A&$2:$D$2 inafafanua jedwali la kutazama kwenye laha nyingine.
Ili kufanya fomula isomeke zaidi, unaweza kuipa safu ya kutazama jina, na kisha fomula itaonekana rahisi zaidi:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Ili fomula ifanye kazi, maadili katika safu wima ya kushoto kabisa ya jedwali unayotazama lazima yaunganishwe kwa njia sawa na katika vigezo vya utaftaji. Katika takwimu hapo juu, tuliunganisha maadili u2bu2band kuweka nafasi kati yao, kwa njia ile ile unahitaji kufanya katika hoja ya kwanza ya chaguo la kukokotoa (BXNUMX& "" & CXNUMX).
Kumbuka! kazi VPR kikomo kwa herufi 255, haiwezi kutafuta thamani ambayo ina urefu wa zaidi ya vibambo 255. Kumbuka hili na uhakikishe kuwa urefu wa thamani inayotakiwa hauzidi kikomo hiki.
Ninakubali kwamba kuongeza safu-msaidizi sio suluhisho la kifahari zaidi na sio linalokubalika kila wakati. Unaweza kufanya vivyo hivyo bila safu ya msaidizi, lakini hiyo itahitaji fomula ngumu zaidi na mchanganyiko wa kazi INDEX (INDEX) na mECHI (IMEFICHUKA ZAIDI).
Tunatoa maadili ya 2, 3, nk kwa kutumia VLOOKUP
Tayari unajua hiyo VPR inaweza kurudisha thamani moja tu inayolingana, kwa usahihi zaidi, ile ya kwanza iliyopatikana. Lakini vipi ikiwa thamani hii inarudiwa mara kadhaa katika safu inayotazamwa, na unataka kutoa ya 2 au 3 kati yao? Nini kama maadili yote? Tatizo linaonekana kuwa gumu, lakini suluhisho lipo!
Tuseme safu wima moja ya jedwali ina majina ya wateja (Jina la Mteja), na safu nyingine ina bidhaa (Bidhaa) ambazo walinunua. Hebu tujaribu kutafuta bidhaa za 2, 3 na 4 zilizonunuliwa na mteja fulani.
Njia rahisi ni kuongeza safu msaidizi kabla ya safu jina la mteja na ujaze na majina ya wateja na nambari ya marudio ya kila jina, kwa mfano, John Doe 1, John Doe 2 nk. Tutafanya hila kwa kuhesabu kwa kutumia chaguo la kukokotoa COUNTIF (COUNTIF), ikizingatiwa kuwa majina ya wateja yako kwenye safu B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Baada ya hapo unaweza kutumia kazi ya kawaida VPRkupata utaratibu unaohitajika. Kwa mfano:
- Kupata 2-th bidhaa iliyoagizwa na mteja Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Kupata 3-th bidhaa iliyoagizwa na mteja Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Kwa kweli, unaweza kuingiza rejeleo la seli kama thamani ya utafutaji badala ya maandishi, kama inavyoonyeshwa kwenye takwimu ifuatayo:
Ikiwa unatafuta tu 2-e kurudia, unaweza kuifanya bila safu msaidizi kwa kuunda fomula ngumu zaidi:
=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;ИСТИНА);"")
Katika fomula hii:
- $F$2 - kiini kilicho na jina la mnunuzi (haijabadilika, tafadhali kumbuka - kiungo ni kabisa);
- $ B $ - safu jina la mteja;
- Jedwali4 - Jedwali lako (mahali hapa pia kunaweza kuwa safu ya kawaida);
- $ C16 - seli ya mwisho ya jedwali au safu yako.
Fomula hii hupata tu thamani ya pili inayolingana. Ikiwa unahitaji kutoa marudio yaliyobaki, tumia suluhisho la awali.
Ikiwa unahitaji orodha ya mechi zote - kazi VPR hii sio msaidizi, kwani inarudisha thamani moja tu kwa wakati - kipindi. Lakini Excel ina kazi INDEX (INDEX), ambayo inaweza kukabiliana na kazi hii kwa urahisi. Jinsi formula kama hiyo itaonekana, utajifunza katika mfano ufuatao.
Rejesha marudio yote ya thamani inayotakiwa
Kama ilivyoelezwa hapo juu VPR haiwezi kutoa thamani zote zilizorudiwa kutoka kwa safu iliyochanganuliwa. Ili kufanya hivyo, unahitaji formula ngumu zaidi, iliyoundwa na kazi kadhaa za Excel, kama vile INDEX (INDEX), NDOGO (NDOGO) na ROW (LINE)
Kwa mfano, fomula iliyo hapa chini hupata marudio yote ya thamani kutoka kisanduku F2 katika masafa B2:B16 na kurejesha matokeo kutoka kwa safu mlalo sawa katika safu wima 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));"")}
Weka fomula hii ya mkusanyiko katika visanduku vingi vilivyo karibu, kama vile seli F4: F8kama inavyoonyeshwa kwenye takwimu hapa chini. Idadi ya seli lazima iwe sawa na au kubwa kuliko idadi ya juu iwezekanavyo ya marudio ya thamani iliyotafutwa. Usisahau kubofya Ctrl + Shift + Ingizaili kuingiza fomula ya safu kwa usahihi.
Ikiwa una nia ya kuelewa jinsi inavyofanya kazi, wacha tuzame maelezo ya fomula kidogo:
Sehemu 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - Linganisha thamani katika seli F2 na kila moja ya maadili katika masafa B2:B16. Ikiwa mechi inapatikana, basi usemi STRING(C2:C16)-1 inarudisha nambari ya safu inayolingana (thamani -1 hukuruhusu kutojumuisha mstari wa kichwa). Ikiwa hakuna mechi, kazi IF (IF) inarudisha kamba tupu.
Matokeo ya kazi IF (IF) kutakuwa na safu mlalo kama hii: {1,"",3,"",5,"","","","","","",12,"","",""}
Sehemu 2:
ROW()-3
СТРОКА()-3
Hapa kazi ROW (LINE) hufanya kama kihesabu cha ziada. Kwa kuwa fomula inakiliwa kwenye seli F4:F9, tunaondoa nambari 3 kutoka kwa matokeo ya kazi kupata thamani 1 katika seli F4 (mstari wa 4, toa 3) kupata 2 katika seli F5 (mstari wa 5, toa 3) na kadhalika.
Sehemu 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
kazi NDOGO (NDOGO) inarudi n-oh thamani ndogo zaidi katika safu ya data. Kwa upande wetu, ni nafasi gani (kutoka ndogo) kurudi imedhamiriwa na kazi ROW (LINE) (tazama Sehemu ya 2). Kwa hivyo, kwa seli F4 kazi NDOGO({safu},1) Anarudi 1-th (smallest) kipengele cha safu, yaani 1. Kwa seli F5 Anarudi 2-th kipengele kidogo katika safu, yaani 3, Nk
Sehemu 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))
kazi INDEX (INDEX) hurejesha tu thamani ya kisanduku mahususi katika safu C2:C16. Kwa seli F4 kazi INDEX($C$2:$C$16) itarudi appleskwa F5 kazi INDEX($C$2:$C$16) itarudi Pipi' na kadhalika.
Sehemu 5:
IFERROR()
ЕСЛИОШИБКА()
Hatimaye, tunaweka formula ndani ya kazi IFERRO (IFERROR), kwa sababu kuna uwezekano wa kufurahishwa na ujumbe wa hitilafu #KATIKA (#N/A) ikiwa idadi ya visanduku ambamo fomula imenakiliwa ni chini ya idadi ya thamani rudufu katika safu inayotazamwa.
Utafutaji wa XNUMXD kwa safu mlalo na safu inayojulikana
Kufanya utafutaji wa XNUMXD katika Excel kunahusisha kutafuta thamani kwa safu mlalo na nambari ya safu inayojulikana. Kwa maneno mengine, unatoa thamani ya seli kwenye makutano ya safu mlalo na safu mahususi.
Kwa hivyo, wacha tugeuke kwenye meza yetu na tuandike fomula iliyo na kazi VPR, ambayo itapata habari kuhusu gharama ya mandimu kuuzwa mwezi Machi.
Kuna njia kadhaa za kutafuta XNUMXD. Angalia chaguzi na uchague ile inayokufaa zaidi.
VLOOKUP na vitendaji vya MATCH
Unaweza kutumia rundo la vitendaji VPR (VLOOKUP) na ZAIDI WAZI (MATCH) ili kupata thamani kwenye makutano ya uga Название продукта (kamba) na mwezi (safu) ya safu inayohusika:
=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);ЛОЖЬ)
Fomula iliyo hapo juu ni kazi ya kawaida VPR, ambayo hutafuta inayolingana kabisa na thamani ya "Ndimu" katika seli A2 hadi A9. Lakini kwa kuwa hujui ni safu ipi ambayo mauzo ya Machi yamo, hutaweza kuweka nambari ya safu wima kwa hoja ya tatu ya utendakazi. VPR. Badala yake, kazi hutumiwa ZAIDI WAZIkufafanua safu hii.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Ikitafsiriwa katika lugha ya binadamu, fomula hii ina maana:
- Tunatafuta wahusika "Mar" - hoja kupakua_value (thamani_ya_kutazama);
- Kuangalia katika seli kutoka A1 hadi I1 - hoja safu_ya_tafuta (safu_ya_kuangalia);
- Kurejesha mechi halisi - hoja linganisha_aina (aina_ya_linganisha).
Kutumia 0 katika hoja ya tatu, unasema kazi ZAIDI WAZI tafuta thamani ya kwanza inayolingana kabisa na thamani unayotafuta. Hii ni sawa na thamani UONGO (UONGO) kwa hoja ya nne VPR.
Hivi ndivyo unavyoweza kuunda fomula ya utafutaji ya njia mbili katika Excel, pia inajulikana kama utafutaji wa pande mbili au utafutaji wa pande mbili.
Chaguo za kukokotoa za SUMPRODUCT
kazi SUMPRODUCT (SUMPRODUCT) hurejesha jumla ya bidhaa za safu zilizochaguliwa:
=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)
Vitendaji vya INDEX na MATCH
Katika makala inayofuata nitaelezea kazi hizi kwa undani, kwa hivyo kwa sasa unaweza kunakili fomula hii:
=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))
Masafa yaliyotajwa na opereta ya makutano
Ikiwa hauko katika fomula zote ngumu za Excel, unaweza kupenda njia hii ya kuona na ya kukumbukwa:
- Chagua meza, fungua kichupo Aina (Mfumo) na ubofye Unda kutoka kwa Uteuzi (Unda kutoka kwa uteuzi).
- Angalia sanduku Safu ya juu (kwenye mstari hapo juu) na Safu wima ya kushoto (kwenye safu upande wa kushoto). Microsoft Excel itaweka majina kwa safu kutoka kwa thamani katika safu mlalo ya juu na safu wima ya kushoto ya lahajedwali yako. Sasa unaweza kutafuta kwa kutumia majina haya moja kwa moja bila kuunda fomula.
- Katika seli yoyote tupu, andika =safu_jina_jina la safu wima, kwa mfano kama hii:
=Ndimu Mar
... au kinyume chake:
=Ndimu za Mar
Kumbuka kwamba majina ya safu mlalo na safu lazima yatenganishwe na nafasi, ambayo katika kesi hii inafanya kazi kama opereta wa makutano.
Unapoingiza jina, Microsoft Excel itaonyesha kidokezo chenye orodha ya majina yanayolingana, kama vile unapoingiza fomula.
- Vyombo vya habari kuingia na angalia matokeo
Kwa ujumla, yoyote kati ya njia zilizo hapo juu utakazochagua, matokeo ya utaftaji wa pande mbili yatakuwa sawa:
Kutumia VLOOKUP nyingi katika fomula moja
Inatokea kwamba meza kuu na meza ya kuangalia hazina safu moja kwa pamoja, na hii inakuzuia kutumia kazi ya kawaida. VPR. Hata hivyo, kuna meza nyingine ambayo haina habari tunayopendezwa nayo, lakini ina safu ya kawaida na meza kuu na meza ya kuangalia.
Hebu tuangalie mfano ufuatao. Tuna meza kuu na safu SKU (mpya), ambapo unataka kuongeza safu wima na bei zinazolingana kutoka kwa jedwali lingine. Kwa kuongeza, tunayo meza 2 za kuangalia. Ya kwanza (Tafuta jedwali 1) ina nambari zilizosasishwa SKU (mpya) na majina ya bidhaa, na ya pili (Jedwali la Kuangalia 2) - majina ya bidhaa na nambari za zamani SKU (zamani).
Ili kuongeza bei kutoka kwa jedwali la pili la uchunguzi hadi jedwali kuu, ni lazima utekeleze kitendo kinachojulikana kama double VPR au kiota VPR.
- Andika kipengele VPR, ambayo hupata jina la bidhaa kwenye jedwali Jedwali la kuangalia 1kutumia SKU, kama thamani inayotakiwa:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Huu SKU_Mpya - safu iliyopewa jina $A:$B katika meza Jedwali la kuangalia 1, 2 - hii ni safu B, ambayo ina majina ya bidhaa (tazama picha hapo juu)
- Andika fomula ya kuingiza bei kutoka kwa jedwali Jedwali la kuangalia 2 kulingana na majina ya bidhaa zinazojulikana. Ili kufanya hivyo, bandika fomula uliyounda mapema kama thamani ya utafutaji ya chaguo la kukokotoa mpya VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Huu Bei - safu iliyopewa jina $A:$C katika meza Jedwali la kuangalia 2, 3 ni safu C yenye bei.
Kielelezo hapa chini kinaonyesha matokeo yaliyorejeshwa na fomula tuliyounda:
Ubadilishaji unaobadilika wa data kutoka kwa majedwali tofauti kwa kutumia VLOOKUP na INDIRECT
Kwanza, hebu tufafanue tunachomaanisha kwa usemi "Ubadilishaji wa data kutoka kwa jedwali tofauti" ili kuhakikisha kuwa tunaelewana kwa usahihi.
Kuna hali wakati kuna karatasi kadhaa zilizo na data ya muundo sawa, na inahitajika kutoa habari muhimu kutoka kwa karatasi fulani, kulingana na thamani ambayo imeingizwa kwenye seli fulani. Nadhani ni rahisi kuelezea hii kwa mfano.
Fikiria kuwa una ripoti za mauzo kwa maeneo kadhaa yenye bidhaa sawa na katika muundo sawa. Unataka kupata takwimu za mauzo kwa eneo mahususi:
Ikiwa una ripoti mbili tu kama hizo, basi unaweza kutumia fomula rahisi ya kufedhehesha iliyo na vitendaji VPR и IF (IF) ili kuchagua ripoti unayotaka kutafuta:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Ambapo:
- $D$2 ni seli iliyo na jina la bidhaa. Kumbuka kuwa tunatumia marejeleo kamili hapa ili kuepuka kubadilisha thamani ya utafutaji wakati wa kunakili fomula kwenye visanduku vingine.
- $D3 ni seli yenye jina la eneo. Tunatumia marejeleo kamili ya safu wima na marejeleo ya safu mlalo jamaa kwa sababu tunapanga kunakili fomula kwenye visanduku vingine katika safu wima sawa.
- FL_Sales и CA_Mauzo - majina ya jedwali (au safu zilizotajwa) ambazo zina ripoti zinazolingana za mauzo. Unaweza, bila shaka, kutumia majina ya kawaida ya laha na marejeleo ya safu ya seli, kwa mfano 'FL Laha'!$A$3:$B$10, lakini safu zilizotajwa zinafaa zaidi.
Walakini, wakati kuna meza nyingi kama hizo, kazi IF sio suluhisho bora. Badala yake, unaweza kutumia kazi INDIRECT (INDIRECT) kurudisha safu ya utafutaji inayotaka.
Kama unavyojua, kazi INDIRECT inatumika kurudisha kiunga kilichotolewa na mfuatano wa maandishi, ambayo ndiyo hasa tunayohitaji sasa. Kwa hivyo, kwa ujasiri badilisha katika fomula iliyo hapo juu usemi na chaguo la kukokotoa IF kuunganishwa na kazi INDIRECT. Hapa kuna mchanganyiko VPR и INDIRECT inafanya kazi vizuri na:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Ambapo:
- $D$2 - hii ni seli iliyo na jina la bidhaa, haijabadilishwa kwa sababu ya kiungo kabisa.
- $D3 ni seli iliyo na sehemu ya kwanza ya jina la eneo. Katika mfano wetu, hii FL.
- _Mauzo - sehemu ya kawaida ya jina la safu au jedwali zote zilizotajwa. Inapojumuishwa na thamani katika kisanduku D3, huunda jina linalostahiki kikamilifu la fungu linalohitajika. Hapo chini kuna maelezo kadhaa kwa wale ambao ni wapya kwenye chaguo la kukokotoa INDIRECT.
Jinsi INDIRECT na VLOOKUP hufanya kazi
Kwanza, wacha nikukumbushe syntax ya chaguo la kukokotoa INDIRECT (INDIRECT):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Hoja ya kwanza inaweza kuwa rejeleo la seli (mtindo wa A1 au R1C1), jina la safu, au mfuatano wa maandishi. Hoja ya pili huamua ni mtindo gani wa kiunga uliomo katika hoja ya kwanza:
- A1kama hoja ni KANUNI YA KWELI (TRUE) au haijabainishwa;
- R1C1, Kama FAS E (UONGO).
Kwa upande wetu, kiungo kina mtindo A1, ili uweze kuacha hoja ya pili na kuzingatia ya kwanza.
Kwa hivyo, turudi kwenye ripoti zetu za mauzo. Ikiwa unakumbuka, basi kila ripoti ni meza tofauti iliyo kwenye karatasi tofauti. Ili fomula ifanye kazi kwa usahihi, lazima utaje jedwali zako (au safu), na majina yote lazima yawe na sehemu ya kawaida. Kwa mfano, kama hii: CA_Mauzo, FL_Mauzo, TX_Mauzo Nakadhalika. Kama unavyoona, "_Mauzo" yapo katika majina yote.
kazi INDIRECT huunganisha thamani katika safu wima D na mfuatano wa maandishi "_Mauzo", na hivyo kusema VPR katika jedwali gani utafute. Ikiwa kiini D3 kina thamani "FL", fomula itafuta meza FL_Mauzo, ikiwa "CA" - kwenye meza CA_Mauzo na kadhalika.
Matokeo ya kazi VPR и INDIRECT itakuwa hivi:
Ikiwa data iko katika vitabu tofauti vya Excel, basi unahitaji kuongeza jina la kitabu kabla ya safu iliyotajwa, kwa mfano:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Ikiwa kazi INDIRECT inahusu kitabu kingine cha kazi, kitabu hicho lazima kiwe wazi. Ikiwa imefungwa, chaguo la kukokotoa litaripoti hitilafu. #REF! (#SYL!).