Konten
Tutorial kecil ini menjelaskan cara membuat fungsi VPR (VLOOKUP) peka huruf besar/kecil, memperlihatkan beberapa rumus lain yang dapat dicari Excel dengan cara peka huruf besar/kecil, dan menunjukkan kekuatan dan kelemahan setiap fungsi.
Saya kira setiap pengguna Excel tahu fungsi apa yang melakukan pencarian vertikal. Itu benar, itu adalah fungsi VPR. Namun, hanya sedikit orang yang tahu itu VPR tidak peka huruf besar/kecil, yaitu karakter huruf kecil dan huruf besar identik untuk itu.
Berikut adalah contoh cepat yang menunjukkan ketidakmampuan VPR mengenali mendaftar. Misalkan dalam sel A1 berisi nilai "tagihan" dan sel A2 – “Tagihan”, rumusnya:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
... akan menghentikan pencariannya pada "tagihan" karena nilai itu berada di urutan pertama dalam daftar, dan mengekstrak nilainya dari sel B1.
Nanti di artikel ini, saya akan menunjukkan cara melakukannya VPR hal - hal sensitif. Selain itu, kita akan mempelajari beberapa fungsi lagi yang dapat melakukan pencarian peka huruf besar/kecil di Excel.
Kita akan mulai dengan yang paling sederhana – BACA (LOOKUP) dan SUMPRODUK (SUMPRODUCT), yang sayangnya memiliki beberapa keterbatasan signifikan. Selanjutnya, kita akan melihat lebih dekat pada formula yang sedikit lebih kompleks INDEKS+MATCH (INDEX+MATCH), yang bekerja dengan sempurna dalam situasi apa pun dan dengan kumpulan data apa pun.
Fungsi VLOOKUP peka terhadap huruf besar-kecil
Seperti yang sudah Anda ketahui, fungsi biasa VPR tidak peka huruf besar/kecil. Namun, ada cara untuk membuatnya peka huruf besar/kecil. Untuk melakukan ini, Anda perlu menambahkan kolom bantu ke tabel, seperti yang ditunjukkan pada contoh berikut.
Misalkan dalam kolom B ada pengidentifikasi produk (Item) dan Anda ingin mengekstrak harga produk dan komentar yang sesuai dari kolom C и D. Masalahnya adalah pengidentifikasi berisi karakter huruf kecil dan huruf besar. Misalnya, nilai sel B4 (001Tvci3u) dan B5 (001Tvci3U) hanya berbeda dalam hal karakter terakhir, u и U masing.
Seperti yang bisa Anda bayangkan, rumus pencarian biasa
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
akan kembali $ 90, karena nilai 001Tvci3u ada dalam rentang pencarian lebih awal dari 001Tvci3U. Tapi bukan itu yang kita butuhkan, bukan?
Untuk mencari dengan fungsi VPR di Excel case sensitif, Anda harus menambahkan kolom pembantu dan mengisi selnya dengan rumus berikut (di mana B adalah kolom pencarian):
=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 ini memecah nilai yang diinginkan menjadi karakter terpisah, mengganti setiap karakter dengan kodenya (misalnya, alih-alih A pada 65, sebagai gantinya a kode 97) dan kemudian menggabungkan kode-kode ini menjadi rangkaian angka yang unik.
Setelah itu, kami menggunakan fungsi sederhana VPR untuk pencarian peka huruf besar/kecil:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Pengoperasian fungsi yang benar VPR peka huruf besar/kecil tergantung pada dua faktor:
- Kolom pembantu harus menjadi kolom paling kiri dalam rentang yang dapat dilihat.
- Nilai yang Anda cari harus berisi kode karakter, bukan nilai sebenarnya.
Cara menggunakan fungsi CODE dengan benar
Rumus yang dimasukkan ke dalam sel kolom bantu mengasumsikan bahwa semua nilai pencarian Anda memiliki jumlah karakter yang sama. Jika tidak, maka Anda perlu mengetahui angka terkecil dan terbesar dan menambahkan fitur sebanyak-banyaknya KESALAHAN (IFERROR) berapa banyak karakter perbedaan antara nilai pencarian terpendek dan terpanjang.
Misalnya, jika nilai pencarian terpendek adalah 3 karakter dan terpanjang adalah 5 karakter, gunakan rumus ini:
=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));"")
Untuk fungsi PSTR (MID) Anda memberikan argumen berikut:
- argumen pertama - teks (teks) adalah teks atau referensi sel yang berisi karakter yang akan diekstraksi (dalam kasus kami ini adalah B2)
- argumen pertama - nomor_mulai (start_position) adalah posisi karakter pertama yang akan diekstraksi. kamu masuk 1 pada fungsi pertama PSTR, 2 – dalam fungsi kedua PSTR dan sebagainya
- argumen pertama - jumlah_karakterchar (number_of_characters) – Menentukan jumlah karakter yang akan diekstrak dari teks. Karena kita hanya membutuhkan 1 karakter setiap saat, di semua fungsi kita menulis 1.
PEMBATASAN: fungsi VPR bukan solusi terbaik untuk pencarian peka huruf besar/kecil di Excel. Pertama, penambahan kolom bantu diperlukan. Kedua, rumus bekerja dengan baik hanya jika datanya homogen, atau jumlah karakter yang tepat dalam nilai yang dicari diketahui. Jika ini bukan kasus Anda, lebih baik menggunakan salah satu solusi yang kami tunjukkan di bawah ini.
Fungsi LOOKUP untuk pencarian peka huruf besar-kecil
fungsi BACA (LOOKUP) terkait VPR, namun sintaksnya memungkinkan pencarian peka huruf besar/kecil tanpa menambahkan kolom bantu. Untuk melakukan ini, gunakan BACA digabungkan dengan fungsi TEPAT (AKURAT).
Jika kita mengambil data dari contoh sebelumnya (tanpa kolom tambahan), maka rumus berikut akan mengatasi tugas:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Pencarian rumus dalam jangkauan A2: A7 kecocokan persis dengan nilai sel F2 peka huruf besar-kecil dan mengembalikan nilai dari kolom B dari baris yang sama.
Seperti VPRfungsi BACA bekerja sama dengan teks dan nilai numerik, seperti yang Anda lihat pada tangkapan layar di bawah ini:
Penting! Agar fungsinya BACA bekerja dengan benar, nilai-nilai di kolom pencarian harus diurutkan dalam urutan menaik, yaitu dari terkecil ke terbesar.
Mari saya jelaskan secara singkat bagaimana fungsi tersebut bekerja TEPAT dalam rumus yang ditunjukkan di atas, karena ini adalah titik kuncinya.
fungsi TEPAT membandingkan dua nilai teks dalam argumen ke-1 dan ke-2 dan mengembalikan TRUE jika keduanya persis sama, atau FALSE jika tidak. Penting bagi kami bahwa fungsinya TEPAT hal - hal sensitif.
Mari kita lihat bagaimana rumus kita bekerja LIHAT+TEPAT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- fungsi TEPAT membandingkan nilai sel F2 dengan semua elemen dalam kolom A (A2:A7). Mengembalikan TRUE jika kecocokan persis ditemukan, jika tidak FALSE.
- Karena Anda memberikan argumen fungsi pertama BACA value TRUE, ini mengekstrak nilai yang sesuai dari kolom yang ditentukan (dalam kasus kami, kolom B) hanya jika kecocokan tepat ditemukan, peka huruf besar-kecil.
Saya harap penjelasan ini jelas dan sekarang Anda memahami ide utamanya. Jika demikian, maka Anda tidak akan mengalami kesulitan dengan fungsi lain yang akan kami analisis lebih lanjut, karena. mereka semua bekerja dengan prinsip yang sama.
PEMBATASAN: Data di kolom pencarian harus diurutkan dalam urutan menaik.
SUMPRODUCT – menemukan nilai teks, peka huruf besar/kecil, tetapi hanya mengembalikan angka
Seperti yang sudah Anda pahami dari judulnya, SUMPRODUK (SUMPRODUCT) adalah fungsi Excel lain yang akan membantu Anda melakukan pencarian peka huruf besar-kecil, tetapi hanya akan mengembalikan nilai numerik. Jika opsi ini tidak cocok untuk Anda, maka Anda dapat segera melanjutkan ke bundel INDEKS+MATCH, yang memberikan solusi untuk kasus apa pun dan untuk semua tipe data.
Pertama, izinkan saya menjelaskan secara singkat sintaks fungsi ini, ini akan membantu Anda lebih memahami rumus peka huruf besar-kecil berikut ini.
fungsi SUMPRODUK mengalikan elemen dari array yang diberikan dan mengembalikan jumlah hasil. Sintaksnya terlihat seperti ini:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Karena kami membutuhkan pencarian peka huruf besar-kecil, kami menggunakan fungsi TEPAT (PERSIS) dari contoh sebelumnya sebagai salah satu pengganda:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Seperti yang Anda ingat, TEPAT membandingkan nilai sel F2 dengan semua elemen dalam kolom A. Mengembalikan TRUE jika kecocokan persis ditemukan, jika tidak FALSE. Dalam operasi matematika, Excel mengambil TRUE sebagai 1, dan SALAH untuk 0Lebih lanjut SUMPRODUK mengalikan angka-angka ini dan menjumlahkan hasilnya.
Nol tidak dihitung karena ketika dikalikan selalu memberi 0. Mari kita lihat lebih dekat apa yang terjadi ketika kecocokan tepat di kolom A ditemukan dan dikembalikan 1… Fungsi SUMPRODUK mengalikan angka di kolom B on 1 dan mengembalikan hasilnya – angka yang sama persis! Ini karena hasil dari produk lainnya adalah nol, dan tidak mempengaruhi jumlah yang dihasilkan.
Sayangnya fungsinya SUMPRODUK tidak dapat bekerja dengan nilai teks dan tanggal karena tidak dapat dikalikan. Dalam hal ini, Anda akan menerima pesan kesalahan #NILAI! (#VALUE!) seperti dalam sel F4 pada gambar di bawah ini:
PEMBATASAN: Mengembalikan hanya nilai numerik.
INDEX + MATCH – pencarian peka huruf besar/kecil untuk semua tipe data
Akhirnya, kami mendekati formula pencarian yang tidak terbatas dan peka huruf besar/kecil yang berfungsi dengan kumpulan data apa pun.
Contoh ini datang terakhir, bukan karena yang terbaik tersisa untuk pencuci mulut, tetapi karena pengetahuan yang diperoleh dari contoh sebelumnya akan membantu Anda memahami formula peka huruf besar-kecil dengan lebih baik dan lebih cepat. INDEKS+MATCH (INDEKS+PERTANDINGAN).
Seperti yang mungkin Anda duga, kombinasi fungsi LEBIH TERBUKTI и INDEKS digunakan di Excel sebagai alternatif yang lebih fleksibel dan kuat untuk VPR. Artikel Menggunakan INDEX dan MATCH alih-alih VLOOKUP akan menjelaskan dengan sempurna bagaimana fungsi-fungsi ini bekerja bersama.
Saya hanya akan merangkum poin-poin penting:
- fungsi LEBIH TERBUKTI (MATCH) mencari nilai dalam rentang tertentu dan mengembalikan posisi relatifnya, yaitu nomor baris dan/atau kolom;
- Selanjutnya, fungsi INDEKS (INDEX) mengembalikan nilai dari kolom dan/atau baris tertentu.
Untuk merumuskan INDEKS+MATCH dapat mencari dengan peka huruf besar-kecil, Anda hanya perlu menambahkan satu fungsi ke dalamnya. Tidak sulit untuk menebak apa itu lagi TEPAT (AKURAT):
=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))
Dalam rumus ini TEPAT bekerja dengan cara yang sama seperti dalam hubungannya dengan fungsi BACA, dan memberikan hasil yang sama:
Perhatikan bahwa rumus INDEKS+MATCH terlampir dalam kurung kurawal adalah rumus array dan Anda harus menyelesaikannya dengan menekan Ctrl + Shift + Enter.
Mengapa INDEX+MATCH solusi terbaik untuk pencarian peka huruf besar/kecil?
Keuntungan utama dari bundel INDEKS и LEBIH TERBUKTI:
- Tidak perlu menambahkan kolom tambahan, tidak seperti VPR.
- Tidak memerlukan kolom pencarian untuk diurutkan, tidak seperti BACA.
- Bekerja dengan semua jenis data – angka, teks, dan tanggal.
Formula ini tampaknya sempurna, bukan? Sebenarnya tidak. Dan itulah kenapa.
Asumsikan bahwa sel di kolom nilai kembalian yang terkait dengan nilai pencarian kosong. Hasil apa yang akan dikembalikan oleh formula? Tidak? Mari kita lihat apa yang sebenarnya dikembalikan oleh rumus:
Ups, rumus mengembalikan nol! Ini mungkin bukan masalah besar jika Anda bekerja dengan nilai teks murni. Namun, jika tabel berisi angka, termasuk nol "nyata", ini menjadi masalah.
Faktanya, semua rumus pencarian lainnya (VLOOKUP, LOOKUP, dan SUMPRODUCT) yang kita bahas sebelumnya berperilaku dengan cara yang sama. Tapi Anda ingin formula yang sempurna, bukan?
Untuk membuat formula peka huruf besar/kecil INDEKS+MATCH sempurna, masukkan ke dalam fungsi IF (IF) yang akan menguji sel dengan nilai kembalian dan mengembalikan hasil kosong jika 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));"")
Dalam rumus ini:
- B adalah kolom dengan nilai balik
- 1+ adalah angka yang mengubah posisi relatif sel yang dikembalikan oleh fungsi LEBIH TERBUKTI, ke alamat sel yang sebenarnya. Misalnya, dalam fungsi kami LEBIH TERBUKTI array pencarian diberikan A2: A7, yaitu, posisi relatif sel A2 akan 1, karena ini adalah yang pertama dalam array. Tapi posisi sebenarnya dari sel A2 pada kolom adalah 2, jadi kami menambahkan 1untuk membuat perbedaan dan memiliki fungsi TIDAK LANGSUNG (INDIRECT) mengambil nilai dari sel yang diinginkan.
Gambar di bawah menunjukkan rumus peka huruf besar-kecil yang dikoreksi INDEKS+MATCH Dalam tindakan. Ini mengembalikan hasil kosong jika sel yang dikembalikan kosong.
Saya menulis ulang rumus menjadi kolom B:Dagar sesuai dengan bilah rumus pada tangkapan layar.
Formula kembali 0jika sel yang dikembalikan berisi nol.
Kalo mau linknya INDEKS и LEBIH TERBUKTI menampilkan beberapa pesan ketika nilai kembalian kosong, Anda dapat menuliskannya dalam tanda kutip terakhir (“”) dari rumus, misalnya seperti ini:
=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.")
