Awọn akoonu
Ikẹkọ kekere yii ṣe alaye bi o ṣe le ṣe iṣẹ kan VPR (VLOOKUP) ifarabalẹ, fihan ọpọlọpọ awọn agbekalẹ miiran ti Excel le wa ni ọna ti o ni imọran, ati tọka si awọn agbara ati ailagbara ti iṣẹ kọọkan.
Mo gboju pe gbogbo olumulo Excel mọ kini iṣẹ ti n ṣe wiwa inaro. Iyẹn tọ, o jẹ iṣẹ kan VPR. Sibẹsibẹ, diẹ eniyan mọ pe VPR kii ṣe ifarabalẹ ọran, ie awọn ohun kikọ isalẹ ati nla jẹ aami fun rẹ.
Eyi ni apẹẹrẹ iyara ti n ṣe afihan ailagbara naa VPR da Forukọsilẹ. Ṣebi ninu sẹẹli kan A1 ni iye "owo" ati sẹẹli naa A2 - "Bill", agbekalẹ:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… yoo da wiwa rẹ duro lori “owo” niwọn igba ti iye yẹn wa ni akọkọ ninu atokọ naa, ati yọ iye jade kuro ninu sẹẹli naa B1.
Nigbamii ninu nkan yii, Emi yoo fihan ọ bi o ṣe le ṣe VPR ifura ipo Ọrọ. Ni afikun, a yoo kọ awọn iṣẹ diẹ diẹ sii ti o le ṣe awọn iwadii ifarabalẹ ni Excel.
A yoo bẹrẹ pẹlu rọrun julọ - Wo (WO) ati IKU (SUMPRODUCTS), eyiti, laanu, ni ọpọlọpọ awọn idiwọn pataki. Nigbamii ti, a yoo ṣe akiyesi diẹ si agbekalẹ diẹ ti o ni idiwọn diẹ sii INDEX + Baramu (INDEX + MATCH), eyiti o ṣiṣẹ laisi abawọn ni eyikeyi ipo ati pẹlu eyikeyi dataset.
Iṣẹ VLOOKUP jẹ kókó
Bi o ti mọ tẹlẹ, iṣẹ deede VPR ni irú insensitive. Sibẹsibẹ, ọna kan wa lati jẹ ki o ni itara. Lati ṣe eyi, o nilo lati ṣafikun iwe iranlọwọ si tabili, bi a ṣe han ninu apẹẹrẹ atẹle.
Sawon ni a iwe B awọn idamọ ọja wa (Nkan) ati pe o fẹ jade idiyele ọja naa ati asọye ti o baamu lati awọn ọwọn C и D. Iṣoro naa ni pe awọn oludamọ ni awọn lẹta kekere ati awọn lẹta nla ninu. Fun apẹẹrẹ, awọn iye sẹẹli B4 (001Tvci3u) ati B5 (001Tvci3U) yatọ nikan ni ọran ti ohun kikọ ti o kẹhin, u и U lẹsẹsẹ.
Bi o ṣe le fojuinu, agbekalẹ wiwa deede
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
yoo pada $ 90, niwon iye 001Tvci3u jẹ ninu awọn àwárí ibiti o sẹyìn ju 001Tvci3U. Ṣugbọn iyẹn kii ṣe ohun ti a nilo, ṣe?
Lati wa pẹlu iṣẹ kan VPR ni ifarabalẹ ọran Excel, iwọ yoo ni lati ṣafikun iwe oluranlọwọ ki o kun awọn sẹẹli rẹ pẹlu agbekalẹ atẹle (nibiti B jẹ iwe wiwa):
=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));"")
Ilana yii fọ iye ti o fẹ sinu awọn ohun kikọ lọtọ, rọpo ohun kikọ kọọkan pẹlu koodu rẹ (fun apẹẹrẹ, dipo A ni 65, dipo a koodu 97) ati lẹhinna daapọ awọn koodu wọnyi sinu okun alailẹgbẹ ti awọn nọmba.
Lẹhin iyẹn, a lo iṣẹ ti o rọrun VPR fun wiwa kókó ọran:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Ṣiṣe deede ti iṣẹ naa VPR ifamọ ọran da lori awọn nkan meji:
- Ọwọ̀n olùrànlọ́wọ́ gbọ́dọ̀ jẹ́ ọwọ̀n òsì jùlọ ní ibi tí a lè rí.
- Iye ti o n wa gbọdọ ni koodu kikọ kan ninu dipo iye gidi.
Bii o ṣe le lo iṣẹ CODE ni deede
Awọn agbekalẹ ti a fi sii sinu awọn sẹẹli ti ọwọn oluranlọwọ dawọle pe gbogbo awọn iye wiwa rẹ ni nọmba awọn ohun kikọ kanna. Ti kii ba ṣe bẹ, lẹhinna o nilo lati mọ awọn nọmba ti o kere julọ ati ti o tobi julọ ati ṣafikun bi ọpọlọpọ awọn ẹya IFEROR (IFERROR) awọn ohun kikọ melo ni iyatọ laarin iye wiwa kukuru ati gunjulo julọ.
Fun apẹẹrẹ, ti iye wiwa ti o kuru ju jẹ awọn ohun kikọ 3 ti o gun julọ jẹ awọn kikọ 5, lo agbekalẹ yii:
=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));"")
Fun iṣẹ PSTR (MID) O pese awọn ariyanjiyan wọnyi:
- 1st ariyanjiyan - ọrọ (ọrọ) jẹ ọrọ tabi itọkasi sẹẹli ti o ni awọn kikọ ti o ni lati jade (ninu ọran tiwa o jẹ B2)
- 2st ariyanjiyan - ibere_num (ibẹrẹ_ipo) jẹ ipo akọkọ ti awọn ohun kikọ wọnyẹn lati fa jade. o wọle 1 ni akọkọ iṣẹ PSTR, 2 - ni iṣẹ keji PSTR ati be be lo
- 3st ariyanjiyan - nọmba_chars (nọmba_of_characters) - Sọ nọmba awọn ohun kikọ lati jade lati ọrọ naa. Niwọn igba ti a nilo ohun kikọ 1 nikan ni gbogbo igba, ni gbogbo awọn iṣẹ a kọ 1.
Awọn iṣiro: iṣẹ VPR kii ṣe ojutu ti o dara julọ fun awọn iwadii ifarabalẹ ọran ni Excel. Ni akọkọ, afikun ti ọwọn iranlọwọ ni a nilo. Ni ẹẹkeji, agbekalẹ naa ṣe iṣẹ ti o dara nikan ti data ba jẹ isokan, tabi nọmba gangan ti awọn ohun kikọ ninu awọn iye wiwa ni a mọ. Ti eyi kii ṣe ọran rẹ, o dara lati lo ọkan ninu awọn ojutu ti a fihan ni isalẹ.
Iṣẹ LOOKUP fun wiwa ifarabalẹ ọran
iṣẹ Wo (LOOKUP) jẹmọ VPR, sibẹsibẹ sintasi rẹ ngbanilaaye fun awọn iwadii ifarabalẹ ọran laisi fifi iwe iranlọwọ kun. Lati ṣe eyi, lo Wo ni idapo pelu iṣẹ OWO (GANKAN).
Ti a ba gba data lati apẹẹrẹ ti tẹlẹ (laisi iwe iranlọwọ), lẹhinna agbekalẹ atẹle yoo koju iṣẹ naa:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Awọn wiwa agbekalẹ ni iwọn A2: A7. baramu gangan pẹlu iye sẹẹli F2 ifarabalẹ ọran ati da iye pada lati iwe B ti ila kanna.
bi VPRiṣẹ Wo ṣiṣẹ bakanna pẹlu ọrọ ati awọn iye nọmba, bi o ti le rii ninu sikirinifoto ni isalẹ:
Pataki! Ni ibere fun iṣẹ naa Wo ṣiṣẹ bi o ti tọ, awọn iye ti o wa ninu iwe wiwa yẹ ki o jẹ lẹsẹsẹ ni ọna ti o ga, ie lati kere julọ si tobi julọ.
Jẹ ki n ṣe alaye ni ṣoki bi iṣẹ naa ṣe n ṣiṣẹ OWO ninu agbekalẹ ti o han loke, nitori eyi ni aaye bọtini.
iṣẹ OWO Ṣe afiwe awọn iye ọrọ ọrọ meji ni 1st ati 2nd awọn ariyanjiyan ati da pada TÒÓTỌ ti wọn ba jẹ deede kanna, tabi FALSE ti wọn ko ba ṣe bẹ. O ṣe pataki fun wa pe iṣẹ naa OWO ifura ipo Ọrọ.
Jẹ ki a wo bi agbekalẹ wa ṣe n ṣiṣẹ WO+TÒÓTỌ:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- iṣẹ OWO wé cell iye F2 pẹlu gbogbo awọn eroja ni a iwe A (A2:A7). Pada TÒÓTỌ ti a ba ri baramu gangan, bibẹẹkọ IRO.
- Niwon o fun akọkọ ariyanjiyan iṣẹ Wo iye TÒÓTỌ, o jade awọn ti o baamu iye lati awọn iwe pàtó kan (ninu ti wa, iwe B) nikan ti o ba ti ohun gangan baramu ti wa ni ri, irú kókó.
Mo nireti pe alaye yii jẹ kedere ati bayi o loye imọran akọkọ. Ti o ba jẹ bẹ, lẹhinna o kii yoo ni awọn iṣoro eyikeyi pẹlu awọn iṣẹ miiran ti a yoo ṣe itupalẹ siwaju sii, nitori. gbogbo wọn ṣiṣẹ lori ilana kanna.
Awọn iṣiro: Awọn data ti o wa ninu iwe wiwa gbọdọ wa ni lẹsẹsẹ ni ọna ti nlọ.
SUMPRODUCT – wa awọn iye ọrọ, ifarabalẹ ọran, ṣugbọn awọn nọmba nikan da pada
Bi o ti loye tẹlẹ lati akọle, IKU (SUMPRODUCT) jẹ iṣẹ Excel miiran ti yoo ṣe iranlọwọ fun ọ lati ṣe wiwa ti o ni imọlara, ṣugbọn yoo da awọn iye nọmba pada nikan. Ti aṣayan yii ko ba fun ọ, lẹhinna o le tẹsiwaju lẹsẹkẹsẹ si lapapo INDEX + Baramu, eyi ti o funni ni ojutu fun eyikeyi ọran ati fun eyikeyi iru data.
Ni akọkọ, jẹ ki n ṣe alaye ni ṣoki sintasi ti iṣẹ yii, eyi yoo ran ọ lọwọ lati ni oye daradara agbekalẹ ti o ni imọlara ti o tẹle.
iṣẹ IKU isodipupo awọn eroja ti awọn ti fi fun orun ati ki o pada ni apao ti awọn esi. Awọn sintasi wulẹ bi yi:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Niwọn igba ti a nilo wiwa ti o ni imọlara, a lo iṣẹ naa OWO (EXACT) lati apẹẹrẹ ti tẹlẹ bi ọkan ninu awọn isodipupo:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Bi o ranti, OWO wé cell iye F2 pẹlu gbogbo awọn eroja ni a iwe A. Pada TÒÓTỌ ti a ba ri baramu gangan, bibẹẹkọ IRO. Ni awọn iṣẹ ṣiṣe mathematiki, Excel gba TÒÓTỌ bi 1, ati ERO fun 0Siwaju sii IKU isodipupo awọn nọmba wọnyi ati akopọ awọn esi.
A ko ka awọn odo nitori pe nigba ti wọn ba pọ si wọn nigbagbogbo fun 0. Jẹ ká ya a jo wo ni ohun ti o ṣẹlẹ nigbati ohun gangan baramu ni a iwe A ri ati ki o pada 1… Iṣẹ IKU isodipupo awọn nọmba ninu awọn iwe B on 1 ati ki o pada esi - gangan nọmba kanna! Eyi jẹ nitori awọn abajade ti awọn ọja miiran jẹ odo, ati pe wọn ko ni ipa lori iye abajade.
Laanu iṣẹ naa IKU ko le ṣiṣẹ pẹlu awọn iye ọrọ ati awọn ọjọ bi wọn ko ṣe le pọ si. Ni idi eyi, iwọ yoo gba ifiranṣẹ aṣiṣe # IYE! (#Iye!) bi ninu sẹẹli F4 ninu aworan ni isalẹ:
Awọn iṣiro: Pada awọn iye nomba nikan.
INDEX + MATCH – wiwa aibikita ọran fun iru data eyikeyi
Lakotan, a wa nitosi ilana wiwa ti ko ni opin ati ọran ti o ṣiṣẹ pẹlu eto data eyikeyi.
Apeere yii wa ni ikẹhin, kii ṣe nitori pe o dara julọ ti o fi silẹ fun desaati, ṣugbọn nitori pe imọ ti o gba lati awọn apẹẹrẹ ti tẹlẹ yoo ṣe iranlọwọ fun ọ lati ni oye agbekalẹ ti o ni imọran ti o dara julọ ati yiyara. INDEX + Baramu (INDEX+MATCH).
Bi o ṣe le ṣe akiyesi, apapọ awọn iṣẹ Die fara han и INDEX lo ninu tayo bi a diẹ rọ ati awọn alagbara yiyan fun VPR. Nkan naa Lilo INDEX ati MATCH dipo VLOOKUP yoo ṣe alaye ni pipe bi awọn iṣẹ wọnyi ṣe n ṣiṣẹ papọ.
Emi yoo kan tun awọn aaye pataki pada:
- iṣẹ Die fara han (MATCH) n wa iye kan ni aaye ti a fun ati da ipo ibatan rẹ pada, iyẹn ni, kana ati/tabi nọmba ọwọn;
- Nigbamii, iṣẹ naa INDEX (INDEX) da iye pada lati ọwọn ti a ti sọtọ ati/tabi kana.
Si agbekalẹ INDEX + Baramu le ṣawari ọran-ni ifarabalẹ, iwọ nikan nilo lati ṣafikun iṣẹ kan si i. Ko ṣoro lati gboju le won ohun ti o jẹ lẹẹkansi OWO (GAN):
=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))
Ninu agbekalẹ yii OWO ṣiṣẹ ni ọna kanna bi ni apapo pẹlu iṣẹ naa Wo, ati pe o funni ni abajade kanna:
Ṣe akiyesi pe agbekalẹ naa INDEX + Baramu paade ni awọn àmúró iṣupọ jẹ ilana agbekalẹ ati pe o gbọdọ pari nipa titẹ Konturolu + Yi lọ yi bọ + Tẹ.
Kini idi ti INDEX+MATCH jẹ ojutu ti o dara julọ fun wiwa ti o ni imọlara ọran?
Awọn anfani akọkọ ti lapapo INDEX и Die fara han:
- Ko nilo fifi iwe iranlọwọ kun, ko dabi VPR.
- Ko nilo iwe wiwa lati to lẹsẹsẹ, ko dabi Wo.
- Ṣiṣẹ pẹlu gbogbo awọn orisi ti data - awọn nọmba, ọrọ ati awọn ọjọ.
Ilana yii dabi pipe, ṣe kii ṣe bẹẹ? Lootọ, kii ṣe bẹẹ. Ati idi eyi.
Ro pe sẹẹli ti o wa ninu iwe iye ipadabọ ti o ni nkan ṣe pẹlu iye wiwa ti ṣofo. Abajade wo ni agbekalẹ yoo pada? Rara? Jẹ ki a wo kini agbekalẹ naa yoo da pada:
Bẹẹni, agbekalẹ naa da odo pada! Eyi le ma jẹ iṣoro nla ti o ba n ṣiṣẹ pẹlu awọn iye ọrọ mimọ. Sibẹsibẹ, ti tabili ba ni awọn nọmba, pẹlu awọn odo "gidi", eyi di iṣoro kan.
Ni otitọ, gbogbo awọn agbekalẹ wiwa miiran (VLOOKUP, LOOKUP, ati SUMPRODUCT) ti a jiroro tẹlẹ ṣe huwa ni ọna kanna. Ṣugbọn o fẹ agbekalẹ pipe, otun?
Lati ṣe agbekalẹ ọran ifarabalẹ INDEX + Baramu pipe, fi si iṣẹ kan IF (IF) ti yoo ṣe idanwo sẹẹli kan pẹlu iye ipadabọ ati da abajade ofo pada ti o ba ṣofo:
=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));"")
Ninu agbekalẹ yii:
- B ni a iwe pẹlu pada iye
- 1+ jẹ nọmba ti o yi ipo ibatan ti sẹẹli pada nipasẹ iṣẹ naa Die fara han, si adirẹsi gidi ti sẹẹli naa. Fun apẹẹrẹ, ninu iṣẹ wa Die fara han search orun fun A2: A7., iyẹn ni, ipo ibatan ti sẹẹli naa A2 yio 1, nitori o jẹ akọkọ ọkan ninu awọn orun. Ṣugbọn ipo gangan ti sẹẹli naa A2 ninu iwe ni 2, nitorina a ṣe afikun 1lati ṣe iyatọ ati lati ni iṣẹ naa TỌN (INDIRECT) gba iye pada lati inu sẹẹli ti o fẹ.
Awọn aworan ti o wa ni isalẹ ṣe afihan agbekalẹ ti a ṣe atunṣe ọran-kókó INDEX + Baramu Ni iṣe. O da esi ofo pada ti sẹẹli ti o pada ba ṣofo.
Mo tun ṣe agbekalẹ sinu awọn ọwọn B:Dlati baamu ọpa agbekalẹ lori sikirinifoto.
Fọọmu pada 0ti o ba ti pada cell ni odo.
Ti o ba fẹ ọna asopọ INDEX и Die fara han ṣafihan ifiranṣẹ diẹ nigbati iye ipadabọ ba ṣofo, o le kọ sinu awọn agbasọ ọrọ ti o kẹhin (“”) ti agbekalẹ, fun apẹẹrẹ, bii eyi:
=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.")
