contents
Ti tutoryèl sa a eksplike kijan pou fè yon fonksyon VPR (VLOOKUP) ka-sansib, montre plizyè lòt fòmil ke Excel ka chèche nan yon fason-sansib, epi li montre fòs ak feblès nan chak fonksyon.
Mwen devine chak itilizatè Excel konnen ki fonksyon fè rechèch vètikal. Se vre, se yon fonksyon VPR. Sepandan, kèk moun konnen sa VPR se pa ka sansib, sa vle di karaktè yo pi ba ak majiskil yo idantik pou li.
Isit la se yon egzanp rapid demontre enkapasite a VPR rekonèt enskri. Sipoze nan yon selil A1 gen valè "bill" ak selil la A2 - "Bill", fòmil:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… ap sispann rechèch li sou "bòdwo" depi valè sa a vini an premye nan lis la, epi ekstrè valè a nan selil la B1.
Pita nan atik sa a, mwen pral montre w kouman fè VPR ka sansib. Anplis de sa, nou pral aprann kèk plis fonksyon ki ka fè rechèch ka-sansib nan Excel.
Nou pral kòmanse ak pi senp la - VIEW (CHACHE) ak SUMPRODUCT (SUMPRODUCT), ki, malerezman, gen plizyè limit enpòtan. Apre sa, nou pral pran yon gade pi pre nan fòmil la yon ti kras pi konplèks ENDEX+MATCH (INDEX+MATCH), ki travay parfètman nan nenpòt sitiyasyon ak ak nenpòt seri done.
Fonksyon VLOOKUP se ka sansib
Kòm ou deja konnen, fonksyon an nòmal VPR se ka ensansib. Sepandan, gen yon fason pou fè li ka sansib. Pou fè sa, ou bezwen ajoute yon kolòn oksilyè nan tablo a, jan yo montre nan egzanp sa a.
Sipoze nan yon kolòn B gen idantifyan pwodwi (Atik) epi ou vle ekstrè pri a nan pwodwi a ak kòmantè ki koresponn lan nan kolòn yo C и D. Pwoblèm lan se ke idantifyan yo genyen tou de karaktè miniskil ak majiskil. Pou egzanp, valè selil yo B4 (001Tvci3u) ak B5 (001Tvci3U) diferan sèlman nan ka dènye karaktè a, u и U respektivman.
Kòm ou ka imajine, fòmil rechèch abityèl la
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
ap retounen $ 90, depi valè a 001Tvci3u se nan seri rechèch la pi bonè pase 001Tvci3U. Men, se pa sa nou bezwen, pa vre?
Pou fè rechèch ak yon fonksyon VPR nan Excel ka sansib, w ap oblije ajoute yon kolòn asistan epi ranpli selil li yo ak fòmil sa a (kote B se kolòn rechèch la):
=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));"")
Fòmil sa a kraze valè vle a an karaktè separe, ranplase chak karaktè ak kòd li yo (pa egzanp, olye pou yo A nan 65, olye de sa a kòd 97) epi answit konbine kòd sa yo nan yon seri nimewo inik.
Apre sa, nou itilize yon fonksyon senp VPR pou rechèch ki sansib:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Bon operasyon nan fonksyon an VPR ka-sansib depann de de faktè:
- Kolòn asistan an dwe kolòn ki pi goch la nan seri ki ka wè a.
- Valè w ap chèche a dwe genyen yon kòd karaktè olye de valè reyèl la.
Kouman pou itilize fonksyon KÒD la kòrèkteman
Fòmil la mete nan selil yo nan kolòn oksilyè a sipoze ke tout valè rechèch ou yo gen menm kantite karaktè. Si ou pa, Lè sa a, ou bezwen konnen nimewo ki pi piti ak pi gwo epi ajoute kòm anpil karakteristik SI ERREUR (IFERROR) konbyen karaktè se diferans ki genyen ant valè ki pi kout ak pi long rechèch la.
Pa egzanp, si valè rechèch ki pi kout la se 3 karaktè ak pi long la se 5 karaktè, sèvi ak fòmil sa a:
=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));"")
Pou fonksyon PSTR (MID) Ou bay agiman sa yo:
- 1ye agiman - tèks (tèks) se tèks oswa referans selil ki gen karaktè yo dwe ekstrè (nan ka nou an li se B2)
- 2ye agiman - kòmanse_num (start_position) se pozisyon premye nan karaktè sa yo ki dwe ekstrè. ou antre 1 nan premye fonksyon an PSTR, 2 - nan dezyèm fonksyon an PSTR elatriye.
- 3ye agiman - num_chars (number_of_characters) – Espesifye kantite karaktè yo retire nan tèks la. Piske nou sèlman bezwen 1 karaktè tout tan, nan tout fonksyon nou ekri 1.
LIMITASYON: fonksyon VPR se pa pi bon solisyon pou rechèch ka-sansib nan Excel. Premyèman, adisyon a nan yon kolòn oksilyè obligatwa. Dezyèmman, fòmil la fè yon bon travay sèlman si done yo omojèn, oswa yo konnen kantite egzak karaktè nan valè rechèch yo. Si sa a se pa ka ou, li pi bon yo sèvi ak youn nan solisyon yo ke nou montre anba a.
Fonksyon LOOKUP pou rechèch ki sansib
fonksyon VIEW (CHACHE) ki gen rapò VPR, sepandan sentaks li yo pèmèt pou rechèch ka-sansib san ajoute yon kolòn oksilyè. Pou fè sa, sèvi ak VIEW konbine avèk fonksyon an EXACT (EXACT).
Si nou pran done ki soti nan egzanp anvan an (san yon kolòn oksilyè), Lè sa a, fòmil sa a pral fè fas ak travay la:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Fòmil rechèch nan ranje A2: A7 matche egzak ak valè selilè F2 ka sansib epi li retounen valè nan kolòn B nan menm ranje a.
Klike sou 'jèm' VPRfonksyon VIEW travay egalman ak tèks ak valè nimerik, jan ou ka wè nan ekran ki anba a:
Enpòtan! Nan lòd pou fonksyon an VIEW te travay kòrèkteman, valè yo nan kolòn rechèch la ta dwe klase nan lòd monte, sa vle di soti nan pi piti a pi gwo.
Kite m 'yon ti tan eksplike kijan fonksyon an fonksyone EXACT nan fòmil yo montre pi wo a, paske sa a se pwen kle a.
fonksyon EXACT konpare de valè tèks yo nan 1ye ak 2yèm agiman yo epi li retounen VRE si yo egzakteman menm, oswa FO si yo pa. Li enpòtan pou nou ke fonksyon an EXACT ka sansib.
Ann wè ki jan fòmil nou an ap travay WE+EXACT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- fonksyon EXACT konpare valè selilè F2 ak tout eleman nan yon kolòn A (A2:A7). Retounen TRUE si yo jwenn yon matche egzak, sinon FALSE.
- Depi ou bay agiman an premye fonksyon VIEW valè VRE, li ekstrè valè ki koresponn lan nan kolòn espesifye a (nan ka nou an, kolòn B) sèlman si yo jwenn yon matche egzak, ki sansib.
Mwen espere ke eksplikasyon sa a te klè e kounye a ou konprann lide prensipal la. Si se konsa, Lè sa a, ou pa pral gen okenn difikilte ak lòt fonksyon ke nou pral analize pi lwen, paske. yo tout travay sou menm prensip la.
LIMITASYON: Done yo nan kolòn rechèch la dwe klase nan lòd monte.
SUMPRODUCT – jwenn valè tèks yo, yo di majiskil, men li retounen nimewo sèlman
Kòm ou te deja konprann nan tit la, SUMPRODUCT (SUMPRODUCT) se yon lòt fonksyon Excel ki pral ede w fè yon rechèch ka-sansib, men li pral sèlman retounen valè nimerik. Si opsyon sa a pa adapte ou, ou ka imedyatman ale nan pake a ENDEX+MATCH, ki bay yon solisyon pou nenpòt ka ak pou nenpòt kalite done.
Premyèman, kite m eksplike yon ti tan sentaks fonksyon sa a, sa ap ede ou pi byen konprann fòmil ka-sansib ki swiv la.
fonksyon SUMPRODUCT miltipliye eleman yo nan etalaj yo bay yo epi retounen sòm rezilta yo. Sentaks la sanble sa a:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Depi nou bezwen yon rechèch ki sansib pou ka, nou itilize fonksyon an EXACT (EXACT) nan egzanp anvan an kòm youn nan miltiplikatè yo:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Kòm ou sonje, EXACT konpare valè selilè F2 ak tout eleman nan yon kolòn A. Retounen TRUE si yo jwenn yon matche egzak, sinon FALSE. Nan operasyon matematik, Excel pran VRÈ kòm 1, ak FALSE pou 0Pli lwen SUMPRODUCT miltipliye nimewo sa yo epi sòm rezilta yo.
Zewo yo pa konte paske lè miltipliye yo toujou bay 0. Ann pran yon gade pi pre nan sa k ap pase lè yon match egzak nan yon kolòn A jwenn epi retounen 1… Fonksyon SUMPRODUCT miltipliye nimewo ki nan kolòn nan B on 1 epi li retounen rezilta a - egzakteman menm nimewo a! Sa a se paske rezilta yo nan lòt pwodwi yo se zewo, epi yo pa afekte sòm ki kapab lakòz.
Malerezman fonksyon an SUMPRODUCT pa ka travay ak valè tèks ak dat paske yo pa ka miltipliye. Nan ka sa a, ou pral resevwa yon mesaj erè #VALUE! (#VALÈ!) tankou nan yon selil F4 nan foto ki anba a:
LIMITASYON: Retounen sèlman valè nimerik.
INDEX + MATCH - rechèch ki sansib pou nenpòt kalite done
Finalman, nou pre yon fòmil rechèch san limit ak ka-sansib ki travay ak nenpòt seri done.
Egzanp sa a vini an dènye, se pa paske se pi bon an rete pou desè, men paske konesans yo te genyen nan egzanp anvan yo pral ede w konprann fòmil la ka-sansib pi byen ak pi vit. ENDEX+MATCH (ENDEX+MATCH).
Kòm ou pwobableman dvine, konbinezon an nan fonksyon PLIS Ekspoze и Main Index itilize nan Excel kòm yon altènatif pi fleksib ak pwisan pou VPR. Atik la Sèvi ak INDEX ak MATCH olye de VLOOKUP pral parfe eksplike kijan fonksyon sa yo travay ansanm.
Mwen pral jis rezime pwen kle yo:
- fonksyon PLIS Ekspoze (MATCH) chèche yon valè nan yon seri yo bay epi li retounen pozisyon relatif li, sa vle di, nimewo ranje ak/oswa kolòn;
- Apre sa, fonksyon an Main Index (INDEX) retounen yon valè ki soti nan yon kolòn espesifik ak/oswa ranje.
Pou fòmil ENDEX+MATCH te kapab fè rechèch nan ka-sansib, ou sèlman bezwen ajoute yon fonksyon nan li. Li pa difisil pou devine kisa li ye ankò EXACT (EXACT):
=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))
Nan fòmil sa a EXACT travay menm jan an konjonksyon avèk fonksyon an VIEW, epi li bay menm rezilta a:
Remake byen ke fòmil la ENDEX+MATCH ki fèmen nan aparèy òtopedik se yon fòmil etalaj epi ou dwe ranpli li pa peze Ctrl + Shift + Mete.
Poukisa INDEX+MATCH se pi bon solisyon pou rechèch ki sansib pou ka?
Avantaj prensipal yo nan pake a Main Index и PLIS Ekspoze:
- Pa mande pou ajoute yon kolòn oksilyè, kontrèman ak VPR.
- Pa mande pou kolòn rechèch la dwe klase, kontrèman ak VIEW.
- Travay ak tout kalite done - nimewo, tèks ak dat.
Fòmil sa a sanble pafè, pa vre? Aktyèlman, se pa sa. Epi se poutèt sa.
Sipoze ke selil ki nan kolòn valè retounen ki asosye ak valè rechèch la vid. Ki rezilta fòmil la ap retounen? Non? Ann wè kisa fòmil la retounen aktyèlman:
Oops, fòmil la retounen zewo! Sa a ka pa yon gwo pwoblèm si w ap travay ak valè tèks pi. Sepandan, si tablo a gen nimewo, ki gen ladan zewo "reyèl", sa a vin yon pwoblèm.
An reyalite, tout lòt fòmil rechèch yo (VLOOKUP, LOOKUP, ak SUMPRODUCT) nou te diskite pi bonè konpòte yo menm jan an. Men, ou vle fòmil pafè a, dwa?
Pou fè yon fòmil ka sansib ENDEX+MATCH pafè, mete l nan yon fonksyon IF (SI) ki pral teste yon selil ki gen yon valè retounen epi retounen yon rezilta vid si li vid:
=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));"")
Nan fòmil sa a:
- B se yon kolòn ki gen valè retounen
- 1+ se yon nimewo ki vire pozisyon relatif selil la retounen nan fonksyon an PLIS Ekspoze, nan adrès reyèl la nan selil la. Pou egzanp, nan fonksyon nou an PLIS Ekspoze etalaj rechèch yo bay A2: A7, se sa ki, pozisyon relatif selil la A2 ap 1, paske se premye nan etalaj la. Men, pozisyon aktyèl la nan selil la A2 nan kolòn nan se 2, se konsa nou ajoute 1fè diferans lan ak gen fonksyon an ENDRÈK (ENDIRÈK) rekipere valè a nan selil ou vle a.
Foto ki pi ba yo montre fòmil korije ki sansib pou majiskil yo ENDEX+MATCH Nan aksyon. Li retounen yon rezilta vid si selil ki retounen a vid.
Mwen reekri fòmil la an kolòn B:Danfòm ba fòmil la sou ekran an.
Fòmil retounen 0si selil ki retounen a gen zewo.
Siw vle link lan Main Index и PLIS Ekspoze montre kèk mesaj lè valè retounen an vid, ou ka ekri li nan dènye quotes ("") fòmil la, pou egzanp, tankou sa a:
=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.")
