Yaliyomo
Mafunzo haya madogo yanaelezea jinsi ya kutengeneza kitendakazi VPR (VLOOKUP) nyeti kwa ukubwa, huonyesha fomula zingine kadhaa ambazo Excel inaweza kutafuta katika hali nyeti, na kuashiria ubora na udhaifu wa kila chaguo la kukokotoa.
Nadhani kila mtumiaji wa Excel anajua ni kazi gani hufanya utaftaji wima. Hiyo ni kweli, ni kazi VPR. Hata hivyo, watu wachache wanajua hilo VPR si nyeti kwa herufi kubwa, yaani herufi za herufi ndogo na kubwa zinafanana nayo.
Hapa kuna mfano wa haraka unaoonyesha kutoweza VPR kutambua rejista. Tuseme katika seli A1 ina thamani "bili" na seli A2 - "Bill", fomula:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… itasimamisha utafutaji wake kwenye "bili" kwa kuwa thamani hiyo huja kwanza kwenye orodha, na kutoa thamani kutoka kwa kisanduku B1.
Baadaye katika makala hii, nitakuonyesha jinsi ya kufanya VPR kesi nyeti. Kwa kuongeza, tutajifunza vipengele vichache zaidi vinavyoweza kufanya utafutaji unaozingatia kesi katika Excel.
Tutaanza na rahisi zaidi - View (LOOKUP) na SUMPRODUCT (SUMPRODUCT), ambayo, kwa bahati mbaya, ina vikwazo kadhaa muhimu. Ifuatayo, tutaangalia kwa karibu fomula ngumu zaidi INDEX+MATCH (INDEX+MATCH), ambayo hufanya kazi bila dosari katika hali yoyote na mkusanyiko wowote wa data.
Chaguo za kukokotoa za VLOOKUP ni nyeti kwa ukubwa
Kama unavyojua tayari, kazi ya kawaida VPR halijalishi. Walakini, kuna njia ya kuifanya kesi iwe nyeti. Ili kufanya hivyo, unahitaji kuongeza safu ya msaidizi kwenye meza, kama inavyoonyeshwa katika mfano ufuatao.
Tuseme katika safu B kuna vitambulishi vya bidhaa (Kitu) na unataka kutoa bei ya bidhaa na maoni yanayolingana kutoka kwa safuwima. C и D. Shida ni kwamba vitambulishi vina herufi ndogo na kubwa. Kwa mfano, maadili ya seli B4 (001Tvci3u) na B5 (001Tvci3U) hutofautiana tu katika kesi ya mhusika wa mwisho, u и U mtiririko huo.
Kama unaweza kufikiria, formula ya kawaida ya utafutaji
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
itarudi $ 90, kwa kuwa thamani 001Tvci3u iko katika safu ya utafutaji mapema zaidi 001Tvci3U. Lakini hilo silo tunalohitaji, sivyo?
Ili kutafuta na chaguo la kukokotoa VPR katika kesi nyeti ya Excel, itabidi uongeze safu ya msaidizi na ujaze seli zake na fomula ifuatayo (ambapo B ndio safu wima ya utafutaji):
=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 hii huvunja thamani inayotakiwa kuwa herufi tofauti, hubadilisha kila herufi na msimbo wake (kwa mfano, badala ya A kwa 65, badala yake a 97) na kisha kuchanganya misimbo hii katika mfuatano wa kipekee wa nambari.
Baada ya hayo, tunatumia kazi rahisi VPR kwa utafutaji nyeti wa kesi:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Uendeshaji sahihi wa kazi VPR unyeti wa kesi hutegemea mambo mawili:
- Safu wima ya msaidizi lazima iwe safu wima ya kushoto kabisa katika safu inayoweza kutazamwa.
- Thamani unayotafuta lazima iwe na msimbo wa herufi badala ya thamani halisi.
Jinsi ya kutumia kazi ya CODE kwa usahihi
Fomula iliyoingizwa kwenye seli za safu-saidizi inadhania kwamba thamani zako zote za utafutaji zina idadi sawa ya wahusika. Ikiwa sivyo, basi unahitaji kujua nambari ndogo na kubwa zaidi na uongeze vipengele vingi IFERRO (IFERROR) ni herufi ngapi tofauti kati ya thamani fupi na ndefu zaidi iliyotafutwa.
Kwa mfano, ikiwa thamani fupi ya utafutaji ni vibambo 3 na ndefu zaidi ni vibambo 5, tumia fomula hii:
=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));"")
Kwa kazi PSTR (MID) Unatoa hoja zifuatazo:
- Hoja ya kwanza - Nakala (maandishi) ni maandishi au kumbukumbu ya seli iliyo na herufi zitakazotolewa (kwa upande wetu ni B2)
- Hoja ya kwanza - nambari_ya_kuanza (start_position) ni nafasi ya wahusika wa kwanza kati ya hizo kutolewa. unaingia 1 katika kazi ya kwanza PSTR, 2 - katika kazi ya pili PSTR nk
- Hoja ya kwanza - nambari_za_herufi (idadi_ya_wahusika) - Hubainisha idadi ya herufi za kutoa kutoka kwa maandishi. Kwa kuwa tunahitaji herufi 1 tu kila wakati, katika kazi zote tunaandika 1.
Upungufu: kazi VPR sio suluhisho bora kwa utafutaji unaozingatia kesi katika Excel. Kwanza, nyongeza ya safu ya msaidizi inahitajika. Pili, fomula hufanya kazi nzuri tu ikiwa data ni sawa, au idadi kamili ya wahusika katika maadili yaliyotafutwa inajulikana. Ikiwa hii sio kesi yako, ni bora kutumia moja ya suluhisho ambazo tunaonyesha hapa chini.
Chaguo za kukokotoa za LOOKUP kwa utafutaji nyeti wa kesi
kazi View (LOOKUP) inayohusiana VPR, hata hivyo sintaksia yake inaruhusu utafutaji nyeti kwa kesi bila kuongeza safu-saidizi. Ili kufanya hivyo, tumia View pamoja na kazi SURA (HASA).
Ikiwa tutachukua data kutoka kwa mfano uliopita (bila safu msaidizi), basi fomula ifuatayo itashughulikia kazi hiyo:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Utafutaji wa fomula katika anuwai A2: A7 inayolingana kabisa na thamani ya seli F2 kesi nyeti na hurejesha thamani kutoka safu wima B ya safu mlalo sawa.
kama VPRkazi View inafanya kazi sawa na maandishi na nambari, kama unavyoona kwenye picha ya skrini hapa chini:
Muhimu! Ili kwa utendaji View ilifanya kazi kwa usahihi, maadili katika safu ya utafutaji yanapaswa kupangwa kwa utaratibu wa kupanda, yaani kutoka ndogo hadi kubwa zaidi.
Acha nieleze kwa ufupi jinsi kazi inavyofanya kazi SURA katika fomula iliyoonyeshwa hapo juu, kwani hii ndio jambo kuu.
kazi SURA inalinganisha thamani za maandishi katika hoja ya 1 na ya 2 na kurejesha TRUE ikiwa zinafanana kabisa, au FALSE ikiwa sivyo. Ni muhimu kwetu kwamba kazi SURA kesi nyeti.
Wacha tuone jinsi fomula yetu inavyofanya kazi ANGALIA+HASA:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- kazi SURA inalinganisha thamani ya seli F2 na vipengele vyote katika safu A (A2:A7). Hurejesha TRUE ikiwa inayolingana kabisa itapatikana, vinginevyo FALSE.
- Kwa kuwa unatoa hoja ya kwanza ya kazi View thamani TRUE, inatoa thamani inayolingana kutoka kwa safu wima iliyobainishwa (kwa upande wetu, safu wima B) ikiwa tu inayolingana kabisa itapatikana, nyeti kwa herufi.
Natumai maelezo haya yalikuwa wazi na sasa unaelewa wazo kuu. Ikiwa ndivyo, basi hutakuwa na matatizo yoyote na kazi nyingine ambazo tutachambua zaidi, kwa sababu. wote hufanya kazi kwa kanuni moja.
Upungufu: Data katika safu wima ya utafutaji lazima ipangwe kwa mpangilio wa kupanda.
SUMPRODUCT - hupata thamani za maandishi, nyeti kwa herufi, lakini huleta nambari pekee
Kama ulivyoelewa tayari kutoka kwa kichwa, SUMPRODUCT (SUMPRODUCT) ni chaguo jingine la kukokotoa la Excel ambalo litakusaidia kufanya utafutaji nyeti, lakini litarejesha nambari za nambari pekee. Ikiwa chaguo hili halikufaa, basi unaweza kuendelea mara moja kwenye kifungu INDEX+MATCH, ambayo inatoa suluhisho kwa kesi yoyote na kwa aina yoyote ya data.
Kwanza, wacha nieleze kwa ufupi sintaksia ya chaguo hili la kukokotoa, hii itakusaidia kuelewa vyema fomula nyeti ya kesi inayofuata.
kazi SUMPRODUCT huzidisha vipengele vya safu ulizopewa na kurudisha jumla ya matokeo. Syntax inaonekana kama hii:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Kwa kuwa tunahitaji utafutaji unaozingatia kesi, tunatumia chaguo hili SURA (EXACT) kutoka kwa mfano uliopita kama mojawapo ya vizidishi:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Kama unakumbuka, SURA inalinganisha thamani ya seli F2 na vipengele vyote katika safu A. Hurejesha TRUE ikiwa inayolingana kabisa itapatikana, vinginevyo FALSE. Katika shughuli za hisabati, Excel inachukua TRUE kama 1, na UONGO kwa 0Zaidi SUMPRODUCT huzidisha nambari hizi na kujumlisha matokeo.
Sifuri hazihesabiwi kwa sababu zikizidishwa huwa wanatoa 0. Wacha tuangalie kwa karibu kile kinachotokea wakati mechi halisi kwenye safu A kupatikana na kurudi 1… Kazi SUMPRODUCT huzidisha nambari kwenye safu B on 1 na kurudisha matokeo - nambari sawa kabisa! Hii ni kwa sababu matokeo ya bidhaa zingine ni sifuri, na haziathiri jumla inayotokana.
Kwa bahati mbaya kazi SUMPRODUCT haiwezi kufanya kazi na maadili ya maandishi na tarehe kwani haziwezi kuzidishwa. Katika kesi hii, utapokea ujumbe wa makosa #THAMANI! (#VALUE!) kama katika seli F4 kwenye picha hapa chini:
Upungufu: Hurejesha nambari za nambari pekee.
INDEX + MATCH - utafutaji unaozingatia kesi kwa aina yoyote ya data
Hatimaye, tunakaribia fomula ya utafutaji isiyo na kikomo na nyeti sana ambayo inafanya kazi na seti yoyote ya data.
Mfano huu unakuja mwisho, sio kwa sababu bora zaidi imesalia kwa dessert, lakini kwa sababu ujuzi uliopatikana kutoka kwa mifano ya awali utakusaidia kuelewa fomula nyeti ya kesi bora na kwa kasi zaidi. INDEX+MATCH (INDEX+MECHI).
Kama labda ulivyokisia, mchanganyiko wa kazi ZAIDI WAZI и INDEX kutumika katika Excel kama mbadala rahisi zaidi na yenye nguvu ya VPR. Makala ya Kutumia INDEX na MATCH badala ya VLOOKUP yataeleza kikamilifu jinsi vipengele hivi vinavyofanya kazi pamoja.
Nitarudia tu mambo muhimu:
- kazi ZAIDI WAZI (MATCH) hutafuta thamani katika safu fulani na kurudisha nafasi yake linganishi, yaani, safu mlalo na/au nambari ya safu wima;
- Ifuatayo, kazi INDEX (INDEX) hurejesha thamani kutoka kwa safu wima maalum na/au safu mlalo.
Kwa formula INDEX+MATCH inaweza kutafuta kesi kwa umakini, unahitaji tu kuongeza kitendakazi kimoja kwake. Si vigumu kukisia ni nini tena SURA (HASA):
=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))
Katika fomula hii SURA inafanya kazi kwa njia sawa na kwa kushirikiana na chaguo la kukokotoa View, na inatoa matokeo sawa:
Kumbuka kwamba formula INDEX+MATCH iliyoambatanishwa katika braces curly ni fomula ya safu na lazima ukamilishe kwa kubonyeza Ctrl + Shift + Ingiza.
Kwa nini INDEX+MATCH ndio suluhu bora zaidi kwa utafutaji unaozingatia hali?
Faida kuu za kifungu INDEX и ZAIDI WAZI:
- Haihitaji kuongeza safu wima msaidizi, tofauti VPR.
- Haihitaji safu wima ya utafutaji kupangwa, tofauti View.
- Inafanya kazi na aina zote za data - nambari, maandishi na tarehe.
Fomula hii inaonekana kuwa kamili, sivyo? Kwa kweli, sivyo. Na ndiyo maana.
Chukulia kuwa kisanduku katika safu wima ya thamani ya kurejesha inayohusishwa na thamani ya utafutaji ni tupu. Je, formula itarejea matokeo gani? Hapana? Wacha tuone ni nini formula inarudi:
Lo, fomula inarudisha sufuri! Hili linaweza lisiwe tatizo kubwa ikiwa unafanya kazi na maadili safi ya maandishi. Hata hivyo, ikiwa meza ina nambari, ikiwa ni pamoja na zero "halisi", hii inakuwa tatizo.
Kwa hakika, fomula nyingine zote za utafutaji (VLOOKUP, LOOKUP, na SUMPRODUCT) tulizojadili hapo awali zinatenda kwa njia sawa. Lakini unataka formula kamili, sawa?
Kufanya kesi ya fomula kuwa nyeti INDEX+MATCH kamili, kuiweka katika utendaji IF (IF) ambayo itajaribu kisanduku chenye thamani ya kurudi na kurudisha matokeo tupu ikiwa ni tupu:
=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));"")
Katika fomula hii:
- B ni safu iliyo na maadili ya kurudi
- 1+ ni nambari inayogeuza nafasi ya jamaa ya seli iliyorejeshwa na chaguo la kukokotoa ZAIDI WAZI, kwa anwani halisi ya seli. Kwa mfano, katika kazi yetu ZAIDI WAZI safu ya utafutaji imetolewa A2: A7, yaani, nafasi ya jamaa ya seli A2 mapenzi 1, kwa sababu ni ya kwanza katika safu. Lakini msimamo halisi wa seli A2 katika safu ni 2, kwa hivyo tunaongeza 1kufanya tofauti na kuwa na kazi INDIRECT (INDIRECT) ilipata thamani kutoka kwa seli inayotaka.
Picha hapa chini zinaonyesha fomula iliyosahihishwa ambayo ni nyeti sana INDEX+MATCH Katika vitendo. Hurejesha matokeo tupu ikiwa kisanduku kilichorejeshwa ni tupu.
Niliandika tena fomula katika safu wima B:Dili kutoshea upau wa fomula kwenye picha ya skrini.
Mfumo unarudi 0ikiwa seli iliyorejeshwa ina sifuri.
Ikiwa unataka kiungo INDEX и ZAIDI WAZI ilionyesha ujumbe fulani wakati thamani ya kurudi ni tupu, unaweza kuiandika katika nukuu za mwisho (“”) za fomula, kwa mfano, kama hii:
=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.")
