Daptar eusi
Tutorial saeutik ieu ngajelaskeun kumaha carana ngadamel fungsi VPR (VLOOKUP) case-sensitive, nembongkeun sababaraha rumus sejen nu Excel tiasa milarian dina ragam hal-sensitip, sarta nunjuk kaluar kaunggulan jeung kalemahan unggal fungsi.
Kuring nyangka unggal pangguna Excel terang naon fungsina milarian nangtung. Éta leres, éta fungsi VPR. Sanajan kitu, sababaraha urang nyaho éta VPR henteu sénsitip kana hurup leutik, nyaéta hurup leutik sareng ageung idéntik pikeun éta.
Ieu conto gancang anu nunjukkeun henteu mampuh VPR ngakuan ngadaptar. Anggap dina sél A1 ngandung nilai "tagihan" jeung sél A2 - "Bill", rumus:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... bakal ngeureunkeun pamilarianna dina "tagihan" sabab nilai éta mimitina dina daptar, sareng nimba nilaina tina sél B1.
Engké dina tulisan ieu, kuring bakal nunjukkeun anjeun kumaha ngalakukeunana VPR sénsitip kana ukuran hurup. Salaku tambahan, urang bakal diajar sababaraha deui fungsi anu tiasa ngalaksanakeun pamilarian sénsitip-sénsitip dina Excel.
Urang mimitian ku anu pangbasajanna - Témbongkeun (LOOKUP) jeung SUMPRODUCT (SUMPRODUCT), anu, hanjakalna, gaduh sababaraha watesan anu signifikan. Salajengna, urang bakal nyandak katingal ngadeukeutan dina rumus rada leuwih kompleks INDEKS + tanding (INDEX + MATCH), anu tiasa dianggo sacara sampurna dina kaayaan naon waé sareng sareng set data naon waé.
Fungsi VLOOKUP sensitip kana hurup leutik
Sakumaha anjeun parantos terang, fungsi biasa VPR nyaeta case insensitive. Nanging, aya cara pikeun ngajantenkeun sensitipitas. Jang ngalampahkeun ieu, anjeun kedah nambihan kolom bantu kana tabél, sapertos anu dipidangkeun dina conto di handap ieu.
Anggap dina kolom B aya identifier produk (Item) sareng anjeun hoyong nimba harga produk sareng koméntar anu saluyu tina kolom C и D. Masalahna nyaéta yén identifier ngandung hurup leutik sareng hurup ageung. Contona, nilai sél B4 (001Tvci3u) jeung B5 (001Tvci3U) béda ngan dina kasus karakter panungtungan, u и U mungguh.
Sakumaha anjeun tiasa bayangkeun, rumus milarian anu biasa
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
bakal mulang $ 90, saprak nilai 001Tvci3u aya dina rentang pilarian saméméhna ti 001Tvci3U. Tapi éta sanés anu urang peryogikeun, sanés?
Pikeun milarian sareng fungsi VPR dina kasus sénsitip Excel, anjeun kedah nambihan kolom pembantu sareng eusian sélna ku rumus ieu (dimana B nyaéta kolom milarian):
=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));"")
Rumus ieu ngarobih nilai anu dipikahoyong kana karakter anu misah, ngagentos unggal karakter sareng kode na (contona, tinimbang A di 65, tibatan a kode 97) lajeng ngagabungkeun Konci ieu jadi string unik tina angka.
Sanggeus éta, urang ngagunakeun fungsi basajan VPR pikeun panéangan sénsitip leutik:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Operasi ditangtoskeun tina fungsi VPR case-sensitive gumantung kana dua faktor:
- Kolom helper kedah janten kolom paling kenca dina rentang anu tiasa ditingali.
- Nilai nu Anjeun keur pilari kedah ngandung kode karakter tinimbang nilai nyata.
Kumaha ngagunakeun fungsi CODE leres
Rumus anu diselapkeun kana sél kolom bantu nganggap yén sadaya nilai pamilarian anjeun gaduh jumlah karakter anu sami. Upami henteu, maka anjeun kedah terang nomer pangleutikna sareng panggedéna sareng tambahkeun seueur fitur IFERROR (IFERROR) sabaraha karakter nya éta selisih nilai searched shortest jeung pangpanjangna.
Contona, upami nilai pilarian paling pondok nyaéta 3 karakter sareng pangpanjangna 5 karakter, paké rumus ieu:
=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));"")
Pikeun fungsi PSTR (MID) Anjeun nyadiakeun argumen di handap ieu:
- Argumen 1 - naskah (téks) nyaéta téks atanapi rujukan sél anu ngandung karakter anu bakal diékstrak (dina hal urang nyaéta B2)
- Argumen 2 - start_num (start_position) nyaéta posisi anu mimitina tina karakter-karakter éta pikeun diekstrak. anjeun asupkeun 1 dina fungsi munggaran PSTR, 2 - dina fungsi kadua PSTR jsb
- Argumen 3 - num_chars (number_of_characters) - Nangtukeun jumlah karakter pikeun nimba tina téks. Kusabab urang ngan butuh 1 karakter sepanjang waktos, dina sagala fungsi urang nulis 1.
watesan: fungsi VPR sanes solusi anu pangsaéna pikeun milarian sénsitip-sénsitip dina Excel. Kahiji, perlu pikeun nambahkeun hiji kolom bantu. Kadua, rumus ngalakukeun padamelan anu saé upami datana homogen, atanapi jumlah pasti karakter dina nilai anu dipilarian dipikanyaho. Upami ieu sanés masalah anjeun, langkung saé nganggo salah sahiji solusi anu kami nunjukkeun di handap.
Fungsi LOOKUP pikeun milarian sénsitip hurup
fungsi Témbongkeun (LOOKUP) patali VPR, tapi sintaksisna ngamungkinkeun pikeun milarian sénsitip-sénsitip tanpa nambihan kolom bantu. Jang ngalampahkeun ieu, make Témbongkeun digabungkeun jeung fungsi ATAS (TEPAT).
Upami urang nyandak data tina conto sateuacana (tanpa kolom bantu), maka rumus di handap ieu bakal tiasa dilaksanakeun:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Pilarian rumus dina rentang A2:A7 cocok persis jeung nilai sél F2 case sénsitip sarta mulih nilai ti kolom B baris sarua.
siga VPRfungsi Témbongkeun dianggo sami sareng téks sareng nilai numerik, sapertos anu anjeun tingali dina layar di handap ieu:
Penting! Dina raraga fungsi Témbongkeun didamel leres, nilai-nilai dina kolom lookup kedah diurutkeun dina urutan naek, nyaéta ti pangleutikna ka panggedena.
Hayu atuh sakeudeung ngajelaskeun kumaha fungsi jalan ATAS dina rumus ditémbongkeun di luhur, sakumaha ieu titik konci.
fungsi ATAS ngabandingkeun dua nilai téks dina argumen ka-1 sareng ka-2 sareng uih deui BENER upami aranjeunna persis sami, atanapi PALSU upami henteu. Kadé pikeun urang éta fungsi ATAS sénsitip kana ukuran hurup.
Hayu urang tingali kumaha rumus urang jalan TINGGALKEUN + PASTI:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- fungsi ATAS ngabandingkeun nilai sél F2 kalawan sakabeh elemen dina kolom A (A2:A7). Mulih BENER lamun hiji patandingan pasti kapanggih, disebutkeun PALSU.
- Kusabab anjeun masihan argumen fungsi munggaran Témbongkeun nilai BENER, eta extracts nilai pakait tina kolom dieusian (dina hal urang, kolom B) ngan lamun hiji patandingan pasti kapanggih, hal sénsitip.
Kuring miharep katerangan ieu jelas tur ayeuna anjeun ngartos gagasan utama. Upami kitu, maka anjeun moal ngagaduhan kasusah sareng fungsi anu sanés anu bakal urang analisa salajengna, sabab. aranjeunna sadayana dianggo dina prinsip anu sami.
watesan: Data dina kolom lookup kudu diurutkeun dina urutan naek.
SUMPRODUCT - mendakan nilai téks, sénsitip hurup, tapi ngan ukur nomer
Sakumaha anjeun parantos ngartos tina judulna, SUMPRODUCT (SUMPRODUCT) mangrupikeun fungsi Excel anu sanés anu bakal ngabantosan anjeun milarian sénsitip-sénsitip, tapi ngan ukur ngabalikeun nilai numerik. Upami pilihan ieu henteu cocog sareng anjeun, maka anjeun tiasa langsung teraskeun kana bungkusan INDEKS + tanding, anu masihan solusi pikeun kasus naon waé sareng pikeun jinis data naon waé.
Kahiji, hayu atuh ngajelaskeun sakeudeung sintaksis fungsi ieu, ieu bakal nulungan anjeun hadé ngartos rumus hal-sensitip handap.
fungsi SUMPRODUCT multiplies elemen tina arrays dibikeun tur mulih jumlah hasil. Sintaksisna sapertos kieu:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Kusabab urang peryogi pamilarian sénsitip-sénsitip, kami nganggo fungsina ATAS (TEPAT) ti conto saméméhna salaku salah sahiji multipliers:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Nalika anjeun émut, ATAS ngabandingkeun nilai sél F2 kalawan sakabeh elemen dina kolom A. Mulih BENER lamun hiji patandingan pasti kapanggih, disebutkeun PALSU. Dina operasi matematik, Excel nyandak BENER salaku 1, jeung PALSU keur 0Salajengna SUMPRODUCT kalikeun angka ieu sarta sums hasilna.
Nol henteu diitung sabab nalika dikalikeun aranjeunna salawasna masihan 0. Hayu urang nyandak katingal ngadeukeutan dina naon anu lumangsung nalika hiji patandingan pasti dina kolom A kapanggih sarta balik 1… Fungsi SUMPRODUCT ngalikeun angka dina kolom B on 1 sarta mulih hasilna - persis jumlah anu sarua! Ieu kusabab hasil tina produk sejenna nyaeta nol, sarta aranjeunna henteu mangaruhan jumlah hasilna.
Hanjakal fungsina SUMPRODUCT teu tiasa dianggo sareng nilai téks sareng kaping sabab teu tiasa dikalikeun. Dina hal ieu, anjeun bakal nampa pesen kasalahan #Nilai! (#VALUE!) sakumaha dina sél F4 dina gambar di handap ieu:
watesan: Mulih ukur nilai numerik.
INDEX + MATCH - milarian sénsitip-sénsitip pikeun sagala jinis data
Tungtungna, urang caket kana rumus milarian anu henteu terbatas sareng sénsitip-sénsitip anu tiasa dianggo sareng set data naon waé.
Conto ieu asalna panungtungan, lain sabab pangalusna ditinggalkeun pikeun dessert, tapi kusabab pangaweruh nu diala ti conto saméméhna bakal nulungan anjeun ngartos rumus hal-sénsitip hadé tur gancang. INDEKS + tanding (INDEX+PASANGAN).
Salaku meureun anjeun ditebak, kombinasi fungsi LEUWIH DIBUKA и Indéks dipaké dina Excel salaku alternatif leuwih fleksibel jeung kuat pikeun VPR. Artikel Ngagunakeun INDEX jeung MATCH tinimbang VLOOKUP bakal sampurna ngajelaskeun kumaha fungsi ieu gawé bareng.
Kuring ngan bakal recap titik konci:
- fungsi LEUWIH DIBUKA (PERTANDINGAN) néangan nilai dina rentang dibikeun tur mulih posisi relatif na, nyaeta, baris jeung / atawa nomer kolom;
- Salajengna, fungsi Indéks (INDEX) ngabalikeun nilai tina kolom sareng / atanapi baris anu ditangtukeun.
Pikeun rumus INDEKS + tanding tiasa milarian sacara sénsitip, anjeun ngan ukur kedah nambihan hiji fungsi. Teu hésé nebak naon deui ATAS (TEPAT):
=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))
Dina rumus ieu ATAS jalan dina cara nu sarua salaku ditéang jeung fungsi Témbongkeun, sareng masihan hasil anu sami:
Catet yén rumus INDEKS + tanding Ditutupan ku kurung keriting mangrupikeun rumus Asép Sunandar Sunarya sareng anjeun kedah ngalengkepan ku mencét Ctrl + Shift + Lebetkeun.
Naha INDEX + MATCH mangrupikeun solusi anu pangsaéna pikeun milarian sénsitip-sénsitip?
Kaunggulan utama kebat Indéks и LEUWIH DIBUKA:
- Teu merlukeun nambahkeun hiji kolom bantu, teu saperti VPR.
- Teu merlukeun kolom pilarian diurutkeun, teu kawas Témbongkeun.
- Gawéna sareng sadaya jinis data - nomer, téks sareng kaping.
Rumus ieu sigana sampurna, sanés? Sabenerna henteu. Sareng sababna.
Anggap sél dina kolom nilai balik pakait sareng nilai lookup kosong. Naon hasilna bakal balik rumus? Henteu? Hayu urang tingali naon rumus sabenerna mulih:
Aduh, rumusna mulih nol! Ieu moal janten masalah anu ageung upami anjeun damel sareng nilai téks murni. Sanajan kitu, lamun tabél ngandung angka, kaasup "real" enol, ieu janten masalah.
Kanyataanna, sagala rumus lookup séjén (VLOOKUP, LOOKUP, sarta SUMPRODUCT) urang bahas saméméhna kalakuanana cara nu sarua. Tapi anjeun hoyong rumus anu sampurna, henteu?
Pikeun nyieun rumus case sensitive INDEKS + tanding sampurna, nempatkeun eta dina fungsi hiji IF (IF) anu bakal nguji sél kalayan nilai mulang sareng ngabalikeun hasil kosong upami kosong:
=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));"")
Dina rumus ieu:
- B mangrupa kolom kalawan nilai balik
- 1+ mangrupa angka nu ngarobah posisi relatif sél balik ku fungsi LEUWIH DIBUKA, ka alamat nyata sél. Contona, dina fungsi urang LEUWIH DIBUKA Asép Sunandar Sunarya pilarian dibikeun A2:A7, nyaeta, posisi relatif sél A2 bakal 1, sabab éta anu munggaran dina susunan. Tapi posisi sabenerna sél A2 dina kolom téh 2, sangkan nambahan 1pikeun nyieun bédana jeung boga fungsi TEU LANGSUNG (TIDAK LANGSUNG) nyandak nilai tina sél anu dipikahoyong.
Gambar-gambar di handap ieu nunjukkeun rumus sénsitip-huruf anu dilereskeun INDEKS + tanding Dina aksi. Ieu mulih hasil kosong lamun sél balik kosong.
Kuring rewrote rumus kana kolom B: Dpikeun nyocogkeun bar rumus dina screenshot.
Formula mulang 0lamun sél balik ngandung nol.
Upami anjeun hoyong link Indéks и LEUWIH DIBUKA ditampilkeun sababaraha pesen nalika nilai mulang kosong, anjeun tiasa nyerat dina tanda petik terakhir ("") rumus, contona, sapertos kieu:
=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.")
