Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

Phunziro laling'ono ili likufotokoza momwe mungapangire ntchito VPR (VLOOKUP) imayang'ana pamilandu, ikuwonetsa njira zina zingapo zomwe Excel ingafufuze m'njira yokhudzidwa, ndikuwonetsa mphamvu ndi zofooka za ntchito iliyonse.

Ndikuganiza kuti wogwiritsa ntchito aliyense wa Excel amadziwa ntchito yomwe imagwira ntchito yosaka molunjika. Ndiko kulondola, ndi ntchito VPR. Komabe, anthu ochepa amadziwa zimenezo VPR sichimakhudzidwa ndi nkhani, mwachitsanzo, zilembo zapansi ndi zazikulu ndizofanana.

Nachi chitsanzo chofulumira chosonyeza kulephera VPR kuzindikira kaundula. Tiyerekeze mu selo A1 lili ndi mtengo "bilu" ndi selo A2 - "Bill", chilinganizo:

=VLOOKUP("Bill",A1:A10,2)

=ВПР("Bill";A1:A10;2)

… adzasiya kusaka kwake pa “bilu” popeza mtengowo umabwera koyamba pamndandanda, ndikuchotsa mtengowo mu cell B1.

Kenako m'nkhaniyi, ndikuwonetsani momwe mungachitire VPR zotengera makulidwe azilembo. Kuphatikiza apo, tiphunziranso ntchito zina zingapo zomwe zitha kusaka movutikira mu Excel.

Tiyamba ndi zosavuta - Onani (LOOKUP) ndi SUMPRODUCT (SUMPRODUCT), yomwe, mwatsoka, ili ndi malire angapo. Kenako, tiyang'ana mozama fomula yovuta kwambiri INDEX+MATCH (INDEX+MATCH), yomwe imagwira ntchito mosalakwitsa muzochitika zilizonse komanso ndi data iliyonse.

  • VLOOKUP yosamva nkhani - imafunikira gawo lothandizira
  • Ntchito ya LOOKUP yokhudzidwa ndi nkhani - imafuna kuti deta isanjidwe
  • SUMPRODUCT - imabweretsa manambala okha
  • INDEX+MATCH - kusaka mosavutikira kwa mitundu yonse ya data

Ntchito ya VLOOKUP imakhala yovuta kwambiri

Monga mukudziwa kale, ntchito mwachizolowezi VPR ndizovuta kumva. Komabe, pali njira yopangira kuti ikhale yovuta. Kuti muchite izi, muyenera kuwonjezera gawo lothandizira patebulo, monga momwe tawonetsera pachitsanzo chotsatirachi.

Tiyerekeze mu ndime B pali zozindikiritsa zinthu (Chinthu) ndipo mukufuna kuchotsa mtengo wazinthuzo ndi ndemanga zofananira m'mizati C и D. Vuto ndiloti zozindikiritsa zimakhala ndi zilembo zazing'ono komanso zazikulu. Mwachitsanzo, ma cell values B4 (001Tvci3u) ndi B5 (001Tvci3U) zimasiyana kokha pankhani ya munthu womaliza, u и U motero.

Monga momwe mungaganizire, njira yofufuzira mwachizolowezi

=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)

=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)

adzabwerera $ 90, popeza mtengo 001Tvci3u ili pakusaka kale kuposa 001Tvci3U. Koma si zimene timafunikira, si choncho?

Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

Kusaka ndi ntchito VPR mu Excel nkhani yovuta, muyenera kuwonjezera gawo lothandizira ndikudzaza ma cell ake ndi fomula ili (pomwe B ndiye gawo loyang'ana):

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")

=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")

Fomula iyi imaphwanya mtengo womwe mukufuna kukhala zilembo zosiyana, m'malo mwa munthu aliyense ndi code yake (mwachitsanzo, m'malo mwa A pa 65, pa a code 97) ndikuphatikiza manambalawa kukhala mndandanda wapadera wa manambala.

Pambuyo pake, timagwiritsa ntchito ntchito yosavuta VPR pakusaka kwanthawi yayitali:

=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)

=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)

Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

Kuchita bwino kwa ntchitoyi VPR nkhani-sensitive zimadalira zinthu ziwiri:

  1. Mzere wothandizira uyenera kukhala wakumanzere kwambiri pazowoneka.
  2. Mtengo womwe mukuyang'ana uyenera kukhala ndi zilembo m'malo mwa mtengo weniweni.

Momwe mungagwiritsire ntchito ntchito ya CODE moyenera

Fomula yomwe imayikidwa m'maselo a gawo lothandizira imaganiza kuti kusaka kwanu konse kuli ndi zilembo zofanana. Ngati sichoncho, ndiye kuti muyenera kudziwa ziwerengero zing'onozing'ono komanso zazikulu ndikuwonjezera zambiri IFERROR (IFERROR) ndi zilembo zingati zomwe zimasiyana pakati pa mtengo wofufuzidwa wamfupi kwambiri ndi wautali kwambiri.

Mwachitsanzo, ngati kusaka kwakanthawi kochepa ndi zilembo 3 ndipo yayitali kwambiri ndi zilembo 5, gwiritsani ntchito njira iyi:

=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")

=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")

Za ntchito Zithunzi za PSTR (MID) Mumapereka mfundo zotsatirazi:

  • Mtsutso woyamba - lemba (zolemba) ndi mawu kapena cell reference yomwe ili ndi zilembo zomwe ziyenera kuchotsedwa (kwa ife ndi B2)
  • Mtsutso woyamba - chiyambi_nambala (start_position) ndi malo omwe ali oyamba mwa zilembozo kuti achotsedwe. inu kulowa 1 mu ntchito yoyamba Zithunzi za PSTR, 2 - mu ntchito yachiwiri Zithunzi za PSTR etc.
  • Mtsutso woyamba - nambala_chars (chiwerengero_cha_characters) - Imatchula kuchuluka kwa zilembo kuti zichotse m'mawuwo. Popeza timangofunika khalidwe la 1 nthawi zonse, muzochita zonse zomwe timalemba 1.

ZOCHITA: ntchito VPR silo yankho labwino kwambiri pakufufuza movutikira mu Excel. Choyamba, kuwonjezera pa gawo lothandizira ndikofunikira. Kachiwiri, fomulayi imagwira ntchito bwino pokhapokha ngati deta ili yofanana, kapena chiwerengero chenicheni cha zilembo zomwe zafufuzidwa zimadziwika. Ngati izi siziri zanu, ndi bwino kugwiritsa ntchito imodzi mwamayankho omwe tikuwonetsa pansipa.

Ntchito ya LOOKUP pakufufuza kovutirapo

ntchito Onani (LOOKUP) zokhudzana VPR, komabe mafotokozedwe ake amalola kusaka kokhala ndi vuto popanda kuwonjezera gawo lothandizira. Kuti muchite izi, gwiritsani ntchito Onani kuphatikiza ndi ntchito CHITSANZO (CHONCHO).

Ngati titenga deta kuchokera ku chitsanzo cham'mbuyomu (popanda gawo lothandizira), ndiye kuti njira yotsatirayi ithana ndi ntchitoyi:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

Kusaka kwa formula m'njira zosiyanasiyana A2:A7 kufanana kwenikweni ndi mtengo wa cell F2 kukhudzidwa kwamilandu ndikubweza mtengo kuchokera pagawo B la mzere womwewo.

ngati VPRntchito Onani imagwira ntchito mofanana ndi malemba ndi manambala, monga mukuwonera pazithunzi pansipa:

Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

Zofunika! Kuti mugwiritse ntchito Onani atagwira ntchito moyenera, zikhalidwe zomwe zili mugawo loyang'ana ziyenera kusanjidwa mokwera, mwachitsanzo, kuchokera ku zazing'ono mpaka zazikulu.

Ndiloleni ndifotokoze mwachidule momwe ntchitoyi imagwirira ntchito CHITSANZO mu ndondomeko yomwe yasonyezedwa pamwambapa, chifukwa iyi ndi mfundo yaikulu.

ntchito CHITSANZO amafanizira mfundo ziwiri zomwe zili muzokambirana 1 ndi 2 ndikubwezeretsa TRUE ngati zili zofanana ndendende, kapena FALSE ngati sizili choncho. Ndikofunikira kwa ife kuti ntchitoyo CHITSANZO zotengera makulidwe azilembo.

Tiyeni tiwone momwe fomula yathu imagwirira ntchito ONANI+CHENENI:

=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)

=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)

  • ntchito CHITSANZO kuyerekeza mtengo wa cell F2 ndi zinthu zonse pamndandanda A (A2:A7). Kubwezera TRUE ngati chofanana ndendende chapezeka, apo ayi FALSE.
  • Popeza mumapereka mkangano woyamba Onani mtengo TRUE, imachotsa mtengo womwewo kuchokera pamndandanda womwe watchulidwa (kwa ife, gawo B) pokhapokha ngati kufanana kwenikweni kwapezeka, kukhudzidwa ndi vuto.

Ndikukhulupirira kuti kufotokoza kumeneku kunali komveka ndipo tsopano mukumvetsa lingaliro lalikulu. Ngati ndi choncho, ndiye kuti simudzakhala ndi zovuta ndi ntchito zina zomwe tidzapendanso, chifukwa. onse amagwira ntchito pa mfundo imodzi.

ZOCHITA: Zomwe zili mugawo loyang'ana ziyenera kusanjidwa mokwera.

SUMPRODUCT - imapeza zolemba, zovuta kwambiri, koma imabweretsa manambala okha

Monga mwamvetsetsa kale pamutuwu, SUMPRODUCT (SUMPRODUCT) ndi ntchito ina ya Excel yomwe ingakuthandizeni kusaka movutikira, koma imangobweretsa manambala. Ngati izi sizikugwirizana ndi inu, ndiye kuti mutha kupita pamtolo nthawi yomweyo INDEX+MATCH, yomwe imapereka yankho pazochitika zilizonse komanso zamtundu uliwonse wa data.

Choyamba, ndiroleni ndikufotokozereni mwachidule kalembedwe ka ntchitoyi, izi zikuthandizani kumvetsetsa bwino kalembedwe kamene kamatsatira.

ntchito SUMPRODUCT imachulukitsa zinthu zomwe zaperekedwa ndikubwezeretsanso kuchuluka kwa zotsatira. Syntax ikuwoneka motere:

SUMPRODUCT(array1,[array2],[array3],...)

СУММПРОИЗВ(массив1;[массив2];[массив3];…)

Popeza tikufuna kusaka movutikira, timagwiritsa ntchito ntchitoyi CHITSANZO (EXACT) kuchokera ku chitsanzo cham'mbuyomu ngati chimodzi mwazochulukitsa:

=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))

=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))

Monga momwe mukukumbukira, CHITSANZO kuyerekeza mtengo wa cell F2 ndi zinthu zonse pamndandanda A. Kubwezera TRUE ngati chofanana ndendende chapezeka, apo ayi FALSE. Pamasamu, Excel imatenga TRUE ngati 1, ndi ZABODZA za 0Komanso SUMPRODUCT kuchulukitsa manambala awa ndikuwerengera zotsatira.

Zero samawerengedwa chifukwa akachulukitsa amapereka nthawi zonse 0. Tiyeni tione mwatsatanetsatane zimene zimachitika pamene machesi yeniyeni mu ndime A anapeza ndipo anabwerera 1… Ntchito SUMPRODUCT kuchulukitsa nambala muzanja B on 1 ndikubwezera zotsatira - nambala yomweyo! Izi ndichifukwa choti zotsatira zazinthu zina ndi ziro, ndipo sizikhudza kuchuluka kwake.

Mwatsoka ntchito SUMPRODUCT sangagwire ntchito ndi malembedwe komanso masiku chifukwa sangathe kuchulukitsidwa. Pankhaniyi, mudzalandira uthenga wolakwika #VALUE! (#VALUE!) ngati m'chipinda F4 pachithunzi pansipa:

Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

ZOCHITA: Imabwezera manambala okha.

INDEX + MATCH - kusaka mosavutikira kwamtundu uliwonse wa data

Pomaliza, tatsala pang'ono kupeza njira yosakira yopanda malire komanso yovuta kwambiri yomwe imagwira ntchito ndi seti iliyonse ya data.

Chitsanzochi chimakhala chomaliza, osati chifukwa chabwino kwambiri chatsalira ku mchere, koma chifukwa chidziwitso chomwe mwapeza kuchokera ku zitsanzo zam'mbuyomu chidzakuthandizani kumvetsetsa bwino komanso mofulumira. INDEX+MATCH (INDEX+MATCH).

Monga momwe mungaganizire, kuphatikiza kwa ntchito ZAMBIRI ZOVUTIKA и INDEX amagwiritsidwa ntchito mu Excel ngati njira yosinthika komanso yamphamvu VPR. Nkhaniyo Kugwiritsa ntchito INDEX ndi MATCH m'malo mwa VLOOKUP ifotokoza bwino momwe ntchitozi zimagwirira ntchito limodzi.

Ndingobwerezanso mfundo zazikulu:

  • ntchito ZAMBIRI ZOVUTIKA (MATCH) amafufuza mtengo mumsinkhu woperekedwa ndi kubweza malo ake ogwirizana, ndiye kuti, mzere ndi/kapena nambala ya chigawo;
  • Kenako, ntchito INDEX (INDEX) imabweretsa mtengo kuchokera pagawo lodziwika ndi/kapena mzere.

Ku formula INDEX+MATCH mutha kusaka mosamalitsa, muyenera kuwonjezera ntchito imodzi yokha. Sizovuta kulingalira kuti ndi chiyaninso CHITSANZO (NDEMENE):

=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))

=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))

Mu njira iyi CHITSANZO imagwira ntchito mofanana ndi momwe imagwirira ntchito Onani, ndipo amapereka zotsatira zomwezo:

Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

Onani kuti chilinganizo INDEX+MATCH Chotsekeredwa muzitsulo zopotana ndi njira yotsatizana ndipo muyenera kumalizitsa mwa kukanikiza Ctrl + Shift + Lowani.

Chifukwa chiyani INDEX+MATCH ili yankho labwino kwambiri pakufufuza movutikira?

Ubwino waukulu wa mtolo INDEX и ZAMBIRI ZOVUTIKA:

  1. Sipafunika kuwonjezera gawo lothandizira, mosiyana VPR.
  2. Sichifuna kuti tsamba losakira lisanjidwe, mosiyana Onani.
  3. Imagwira ndi mitundu yonse ya data - manambala, zolemba ndi masiku.

Njira imeneyi ikuwoneka ngati yabwino, sichoncho? Kwenikweni, sichoncho. Ndi chifukwa chake.

Yerekezerani kuti selo lomwe lili mugawo la mtengo wobwerera lomwe limalumikizidwa ndi mtengo wakusaka lilibe kanthu. Kodi fomulayo ibweretsa zotsatira zotani? Ayi? Tiyeni tiwone chomwe fomula imabwerera:

Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

O, fomula ikubweza ziro! Izi sizingakhale vuto lalikulu ngati mukugwira ntchito ndi zolemba zenizeni. Komabe, ngati tebulo lili ndi manambala, kuphatikizapo zero "zenizeni", izi zimakhala zovuta.

M'malo mwake, mafomula ena onse (VLOOKUP, LOOKUP, ndi SUMPRODUCT) omwe tidakambirana m'mbuyomu amachita chimodzimodzi. Koma mukufuna fomula yabwino, sichoncho?

Kupangitsa kuti fomula ikhale yovuta INDEX+MATCH wangwiro, ikani mu ntchito IF (IF) yomwe idzayesa selo ndi mtengo wobwezera ndikubwezera chopanda kanthu ngati chilibe kanthu:

=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")

=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")

Mu formula iyi:

  • B ndi mzati wokhala ndi zobwereza
  • 1+ ndi nambala yomwe imatembenuza malo achibale a selo yobwezedwa ndi ntchitoyi ZAMBIRI ZOVUTIKA, ku adiresi yeniyeni ya selo. Mwachitsanzo, mu ntchito yathu ZAMBIRI ZOVUTIKA kufufuza kwaperekedwa A2:A7, ndiko kuti, malo ogwirizana a selo A2 nditero 1, chifukwa ndiye woyamba pagulu. Koma malo enieni a selo A2 mu khola ndi 2, kotero tikuwonjezera 1kupanga kusiyana ndi kukhala ndi ntchito KUMALO (INDIRECT) adabweza mtengo kuchokera mu cell yomwe mukufuna.

Zithunzi zili m'munsizi zikuwonetsa njira yowongoleredwa yokhudzana ndi vuto INDEX+MATCH Mukuchita. Imabweza chotsatira chopanda kanthu ngati selo yobwezedwa ilibe kanthu.

Ndinalembanso fomula kukhala mizati B:Dkuti mugwirizane ndi formula bar pa skrini.

Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

Kubwerera kwa formula 0ngati selo yobwerera ili ndi ziro.

Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

Ngati mukufuna link INDEX и ZAMBIRI ZOVUTIKA adawonetsa uthenga wina pomwe mtengo wobwezera ulibe, mutha kuulemba m'mawu omaliza ("") a chilinganizo, mwachitsanzo, motere:

=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")

=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")

Njira 4 Zopangira Mlandu wa VLOOKUP Kukhala Wovuta mu Excel

Siyani Mumakonda