Paragraf
Tutorial cilik iki nerangake carane nggawe fungsi VPR (VLOOKUP), nuduhake sawetara rumus liyane sing bisa ditelusuri Excel kanthi cara sing sensitif huruf cilik, lan nuduhake kekuwatan lan kelemahane saben fungsi.
Aku guess saben pangguna Excel ngerti apa fungsi nindakake telusuran vertikal. Sing bener, iku fungsi VPR. Nanging, sawetara wong ngerti VPR ora sensitif huruf cilik, yaiku karakter huruf cilik lan huruf gedhe sing padha.
Iki minangka conto cepet sing nuduhake ketidakmampuan VPR ngenali register. Upaminipun ing sel A1 ngemot nilai "tagihan" lan sel A2 - "Bill", rumus:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... bakal mungkasi panelusuran ing "tagihan" amarga nilai kasebut pisanan ing dhaptar, lan extract nilai saka sel B1.
Mengko ing artikel iki, aku bakal nuduhake sampeyan carane nindakake VPR huruf kapital digatekke. Kajaba iku, kita bakal sinau sawetara fungsi liyane sing bisa nindakake panelusuran cilik-sensitif ing Excel.
Kita bakal miwiti karo sing paling gampang - Deleng (LOOKUP) lan SUMPRODUCT (SUMPRODUCT), sing, sayangé, duwe sawetara watesan sing signifikan. Sabanjure, kita bakal nliti rumus sing rada rumit INDEX+PERTANDINGAN (INDEX + MATCH), sing bisa digunakake kanthi sampurna ing kahanan apa wae lan karo dataset apa wae.
Fungsi VLOOKUP sensitif huruf cilik
Minangka sampeyan wis ngerti, fungsi biasanipun VPR iku case insensitive. Nanging, ana cara kanggo nggawe sensitif huruf cilik. Kanggo nindakake iki, sampeyan kudu nambah kolom tambahan ing tabel, kaya sing ditampilake ing conto ing ngisor iki.
Upaminipun ing kolom B ana pengenal produk (Item) lan sampeyan pengin ngekstrak rega produk lan komentar sing cocog saka kolom C и D. Masalahe yaiku pengenal ngemot karakter huruf cilik lan huruf gedhe. Contone, nilai sel B4 (001Tvci3u) lan B5 (001Tvci3U) beda-beda mung ing kasus karakter pungkasan, u и U mungguh.
Nalika sampeyan bisa mbayangno, rumus panelusuran biasanipun
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
bakal bali $ 90, wiwit regane 001Tvci3u ana ing jangkoan telusuran luwih awal tinimbang 001Tvci3U. Nanging dudu sing kita butuhake, ta?
Kanggo nggoleki kanthi fungsi VPR ing Excel cilik sing sensitif, sampeyan kudu nambah kolom helper lan isi sel kasebut kanthi rumus ing ngisor iki (ing ngendi B minangka kolom goleki):
=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 iki ngilangi nilai sing dikarepake dadi karakter sing kapisah, ngganti saben karakter nganggo kode (contone, tinimbang A ing 65, tinimbang a kode 97) banjur gabungke kode iki dadi senar unik saka nomer.
Sawisé iku, kita nggunakake fungsi prasaja VPR kanggo telusuran sensitif huruf cilik:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Operasi sing tepat saka fungsi VPR case-sensitive gumantung ing rong faktor:
- Kolom helper kudu dadi kolom paling kiwa ing kisaran sing bisa dideleng.
- Nilai sing sampeyan goleki kudu ngemot kode karakter tinimbang nilai nyata.
Cara nggunakake fungsi KODE kanthi bener
Rumus sing dilebokake ing sel kolom tambahan nganggep yen kabeh nilai telusuran sampeyan duwe jumlah karakter sing padha. Yen ora, sampeyan kudu ngerti nomer paling cilik lan paling gedhe lan nambah akeh fitur IFERROR (IFERROR) pinten karakter punika prabédan antarane paling cendhak lan paling dawa ditelusuri nilai.
Contone, yen nilai telusuran paling cendhak yaiku 3 karakter lan paling dawa 5 karakter, gunakake rumus iki:
=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));"")
Kanggo fungsi PSTR (MID) Sampeyan nyedhiyakake argumen ing ngisor iki:
- Argumentasi 1 - teks (teks) minangka referensi teks utawa sel sing ngemot karakter sing bakal diekstrak (ing kasus kita yaiku B2)
- Argumentasi 2 - nomer_wiwitan (start_position) iku posisi pisanan saka karakter sing bakal diekstrak. sampeyan mlebu 1 ing fungsi pisanan PSTR, 2 - ing fungsi kapindho PSTR etc.
- Argumentasi 3 - angka_karakter (number_of_characters) - Nemtokake jumlah karakter sing bakal dijupuk saka teks. Awit kita mung perlu 1 karakter kabeh wektu, ing kabeh fungsi kita nulis 1.
Watesan: fungsi VPR dudu solusi sing paling apik kanggo telusuran sing sensitif huruf cilik ing Excel. Kaping pisanan, tambahan kolom tambahan dibutuhake. Kapindho, rumus kasebut bisa ditindakake mung yen data kasebut homogen, utawa jumlah karakter sing tepat ing nilai sing digoleki dikenal. Yen iki dudu kasus sampeyan, luwih becik nggunakake salah sawijining solusi sing dituduhake ing ngisor iki.
Fungsi LOOKUP kanggo telusuran sensitif huruf cilik
fungsi Deleng (LOOKUP) gegandhengan VPR, nanging sintaks kasebut ngidini telusuran sing sensitif huruf cilik tanpa nambah kolom tambahan. Kanggo nindakake iki, gunakake Deleng digabungake karo fungsi PASTI (TEPAT).
Yen kita njupuk data saka conto sadurunge (tanpa kolom tambahan), rumus ing ngisor iki bakal ngrampungake tugas:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Panelusuran rumus ing kisaran A2:A7 pas karo nilai sel F2 cilik sensitif lan ngasilake nilai saka kolom B baris padha.
Kaya VPRfungsi Deleng kerjane padha karo nilai teks lan angka, kaya sing sampeyan deleng ing gambar ing ngisor iki:
Penting! Supaya kanggo fungsi Deleng makarya kanthi bener, nilai ing kolom goleki kudu diurutake kanthi urutan munggah, yaiku saka sing paling cilik nganti paling gedhe.
Ayo kula nerangake sedhela carane fungsi bisa PASTI ing rumus kapacak ing ndhuwur, minangka iki titik tombol.
fungsi PASTI mbandhingake rong nilai teks ing argumen 1 lan 2 lan ngasilake TRUE yen padha persis, utawa PALSU yen ora. Iku penting kanggo kita sing fungsi PASTI huruf kapital digatekke.
Ayo ndeleng carane rumus kita bisa VIEW + 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 PASTI mbandhingake nilai sel F2 karo kabeh unsur ing kolom A (A2:A7). Ngasilake TRUE yen cocog pas ketemu, digunakake PALSU.
- Wiwit sampeyan menehi argumen fungsi pisanan Deleng Nilai TRUE, iku mbeber nilai cocog saka kolom kasebut (ing kasus kita, kolom B) mung yen cocog pas ketemu, cilik sensitif.
Muga-muga panjelasan iki jelas lan saiki sampeyan ngerti ide utama. Yen mangkono, sampeyan ora bakal duwe masalah karo fungsi liyane sing bakal dianalisis luwih lanjut, amarga. kabeh padha makarya ing prinsip padha.
Watesan: Data ing kolom goleki kudu diurutake kanthi urutan munggah.
SUMPRODUCT - nemokake nilai teks, sensitif huruf cilik, nanging mung ngasilake angka
Kaya sing wis dingerteni saka judhul, SUMPRODUCT (SUMPRODUCT) minangka fungsi Excel liyane sing bakal mbantu sampeyan nggoleki sing sensitif huruf cilik, nanging mung bakal ngasilake angka. Yen pilihan iki ora cocog karo sampeyan, sampeyan bisa langsung nerusake menyang bundel INDEX+PERTANDINGAN, sing menehi solusi kanggo kasus apa wae lan kanggo jinis data apa wae.
Kaping pisanan, aku bakal nerangake kanthi ringkes sintaks fungsi iki, iki bakal mbantu sampeyan luwih ngerti rumus sensitif huruf cilik ing ngisor iki.
fungsi SUMPRODUCT multiplies unsur susunan diwenehi lan ngasilake jumlah asil. Sintaks katon kaya iki:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Amarga kita butuh telusuran cilik, kita nggunakake fungsi kasebut PASTI (EXACT) saka conto sadurunge minangka salah siji saka multipliers:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Nalika sampeyan elinga, PASTI mbandhingake nilai sel F2 karo kabeh unsur ing kolom A. Ngasilake TRUE yen cocog pas ketemu, digunakake PALSU. Ing operasi matematika, Excel njupuk TRUE minangka 1, lan PALSU kanggo 0Luwih SUMPRODUCT multiplies nomer iki lan jumlah asil.
Zeros ora dietung amarga nalika pingan tansah menehi 0. Ayo goleki kanthi luwih rinci babagan apa sing kedadeyan nalika cocog ing kolom A ketemu lan bali 1… Fungsi SUMPRODUCT multiplies nomer ing kolom B on 1 lan ngasilake asil - persis nomer sing padha! Iki amarga asil produk liyane nol, lan padha ora mengaruhi jumlah asil.
Sayange fungsi SUMPRODUCT ora bisa digunakake karo nilai teks lan tanggal amarga ora bisa tikel. Ing kasus iki, sampeyan bakal nampa pesen kesalahan #WARNA! (#VALUE!) kaya ing sel F4 ing gambar ngisor iki:
Watesan: Ngasilake mung angka numerik.
INDEX + MATCH - telusuran sensitif huruf cilik kanggo jinis data apa wae
Pungkasan, kita cedhak karo rumus telusuran tanpa watesan lan sensitif cilik sing bisa digunakake karo set data apa wae.
Conto iki pungkasan, ora amarga sing paling apik ditinggalake kanggo panganan cuci mulut, nanging amarga kawruh sing dipikolehi saka conto sadurunge bakal mbantu sampeyan ngerti rumus sensitif cilik sing luwih apik lan luwih cepet. INDEX+PERTANDINGAN (INDEX+PERTANDINGAN).
Sing mbokmenawa guessed, kombinasi fungsi LUWIH DIBUKA и INDEKS digunakake ing Excel minangka alternatif sing luwih fleksibel lan kuat kanggo VPR. Artikel Nggunakake INDEX lan MATCH tinimbang VLOOKUP bakal nerangake carane fungsi kasebut bisa bebarengan.
Aku mung bakal recap poin utama:
- fungsi LUWIH DIBUKA (MATCH) nggoleki nilai ing sawetara tartamtu lan ngasilake posisi relatif, yaiku, nomer baris lan / utawa kolom;
- Sabanjure, fungsi INDEKS (INDEX) ngasilake nilai saka kolom lan/utawa baris sing ditemtokake.
Kanggo rumus INDEX+PERTANDINGAN bisa nelusuri cilik-sensitif, sampeyan mung kudu nambah siji fungsi. Ora angel ditebak apa maneh PASTI (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))
Ing rumus iki PASTI dianggo ing cara sing padha magepokan karo fungsi Deleng, lan menehi asil sing padha:
Elinga yen rumus INDEX+PERTANDINGAN terlampir ing kurung kriting minangka rumus array lan sampeyan kudu ngrampungake kanthi mencet Ctrl + Shift + Ketik.
Napa INDEX+MATCH minangka solusi sing paling apik kanggo telusuran sing sensitif huruf cilik?
Kaluwihan utama bundle INDEKS и LUWIH DIBUKA:
- Ora mbutuhake nambah kolom tambahan, ora kaya VPR.
- Ora mbutuhake kolom telusuran diurutake, ora kaya Deleng.
- Bisa digunakake karo kabeh jinis data - nomer, teks lan tanggal.
Rumus iki katon sampurna, ta? Bener, ora. Lan mulane.
Nganggep yen sel ing kolom nilai bali sing digandhengake karo nilai goleki kosong. Apa asil rumus bakal bali? ora? Ayo ndeleng apa rumus sing bener ngasilake:
Oops, rumus ngasilake nol! Iki bisa uga ora dadi masalah gedhe yen sampeyan nggarap nilai teks murni. Nanging, yen tabel ngemot nomer, kalebu "nyata" nul, iki dadi masalah.
Nyatane, kabeh rumus golek liyane (VLOOKUP, LOOKUP, lan SUMPRODUCT) sing kita rembugan sadurunge tumindak kanthi cara sing padha. Nanging sampeyan pengin formula sing sampurna, ta?
Kanggo nggawe rumus cilik sensitif INDEX+PERTANDINGAN sampurna, sijine iku ing fungsi IF (IF) sing bakal nyoba sel kanthi nilai bali lan ngasilake asil kosong yen 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));"")
Ing rumus iki:
- B punika kolom karo nilai bali
- 1+ iku nomer sing ngowahi posisi relatif saka sel bali dening fungsi LUWIH DIBUKA, menyang alamat asli sel. Contone, ing fungsi kita LUWIH DIBUKA larik telusuran diwenehi A2:A7, yaiku, posisi relatif saka sel A2 bakal 1, amarga iku sing pisanan ing array. Nanging posisi sel sing nyata A2 ing kolom punika 2, supaya kita nambah 1kanggo nggawe prabédan lan duwe fungsi LANGSUNG (TIDAK LANGSUNG) njupuk nilai saka sel sing dikarepake.
Gambar ing ngisor iki nuduhake rumus sensitif huruf cilik sing didandani INDEX+PERTANDINGAN Ing tumindak. Iki ngasilake asil kosong yen sel bali kosong.
Aku rewrote rumus menyang kolom B: Dkanggo pas karo garis rumus ing gambar.
Formula bali 0yen sel bali ngandhut nul.
Yen sampeyan pengin link INDEKS и LUWIH DIBUKA ditampilake sawetara pesen nalika nilai bali kosong, sampeyan bisa nulis ing kuotasi pungkasan ("") rumus, contone, kaya iki:
=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.")
