Tutorial ini akan menunjukkan cara melakukan pencarian case-sensitive di Excel menggunakan dua metode berbeda
Metode 1 – Fungsi LOOKUP
Fungsi PENCARIAN
Fungsi LOOKUP digunakan untuk mencari kecocokan perkiraan untuk nilai dalam kolom dan mengembalikan nilai yang sesuai dari kolom lain
Pencarian peka huruf besar kecil
Dengan menggabungkan LOOKUP dan EXACT, kita dapat membuat rumus pencarian case-sensitive yang mengembalikan nilai yang sesuai untuk pencarian case-sensitive kita. Mari kita telusuri sebuah contoh
Kami memiliki daftar item dan harganya yang sesuai (perhatikan bahwa ID Item bersifat unik peka huruf besar/kecil)
Misalkan kita diminta memberi harga untuk suatu item menggunakan ID Item-nya seperti itu
Untuk melakukannya, kita bisa menggunakan LOOKUP dan EXACT dalam rumus seperti itu
=LOOKUP(TRUE,EXACT(<Lookup range>,<lookup value>),<results range>)=LOOKUP(TRUE,EXACT($B$2:$B$7,$E$2),$C$2:$C$7)Keterbatasan. agar metode ini berfungsi, nilai harus diurutkan dalam urutan menurun
Bagaimana cara kerja rumusnya?
Fungsi EXACT memeriksa ID Item di E2 (nilai pencarian) terhadap nilai di B2. B7 (rentang pencarian) dan mengembalikan BENAR jika ada kecocokan persis. Kemudian fungsi LOOKUP mengembalikan kecocokan yang sesuai di C2. C7 (rentang hasil) saat EXACT bersarang mengembalikan BENAR
Metode 2 – Fungsi SUMPRODUCT
Fungsi SUMPRODUK
Fungsi SUMPRODUCT digunakan untuk mengalikan array angka, menjumlahkan array yang dihasilkan
SUMPRODUCT peka huruf besar-kecil
Berbeda dengan metode LOOKUP, nilai tidak perlu diurutkan agar bisa bekerja. Kita masih perlu mengkombinasikan EXACT dalam sebuah formula untuk mendapatkan hasil seperti itu
=SUMPRODUCT(--(EXACT(<lookup value>,<lookup range>)),<results range>)=SUMPRODUCT(--(EXACT($E$2,$B$2:$B$7)),$C$2:$C$7)Keterbatasan. Metode SUMPRODUCT hanya akan berfungsi jika nilai yang dikembalikan (bukan nilai pencarian) adalah numerik
Bagaimana cara kerja rumusnya?
Seperti metode LOOKUP, fungsi EXACT berkaitan dengan menemukan kecocokan peka huruf besar-kecil dan mengembalikan BENAR ketika ada kecocokan persis atau FALSE jika tidak. “–” (dikenal sebagai double unary) mengubah TRUE menjadi 1 dan FALSE menjadi 0. Ini pada dasarnya membuat larik pertama untuk SUMPRODUCT untuk dikalikan dengan larik hasil kami
Artikel ini menunjukkan rumus yang mengekstrak nilai unik dari kolom juga mempertimbangkan karakter atas dan bawah (peka huruf besar/kecil)
Daftar isi
Definisi saya tentang nilai unik adalah nilai yang hanya ada sekali dalam rentang sel. Gambar di bawah menunjukkan daftar di kolom B, beberapa dari nilai ini memiliki duplikat
Daftar di kolom D hanya berisi nilai yang unik, Aa dan BB hanya ada sekali dalam daftar, semua nilai lainnya memiliki duplikat
Rumus mengekstrak CC, bb, aa dan Cc karena hanya ada satu kali di kolom B
Rumus array di sel D3
=INDEKS($B$3. $B$15, KECIL(IF(MMULT(EXACT($B$3. $B$15, TRANSPOS(B$3. $B$15))*1, BARIS($B$3. $B$15)^0)=1, COCOK(BARIS($B$3. $B$15), ROW($B$3. $B$15)), ""),ROWS($A$1. A1)))
Jika Anda mencari daftar unik yang berbeda, baca posting ini.
Posting ini menjelaskan cara melakukan VLOOKUP peka huruf besar-kecil dan mengembalikan banyak nilai
Artikel yang direkomendasikan
Pencarian peka huruf besar-kecil dan mengembalikan beberapa nilai
Rumus larik di sel F5 mengembalikan nilai yang berdekatan dari kolom C di mana nilai di kolom B cocok dengan […]
Pastikan Anda membaca artikel berikut jika Anda ingin mengekstraksi nilai duplikat
Artikel yang direkomendasikan
Ekstrak daftar duplikat dari kolom
Rumus larik di sel C2 mengekstrak nilai duplikat dari kolom A. Hanya satu duplikat dari setiap nilai yang ditampilkan […]
1. 1 Cara memasukkan rumus array
- Salin (Ctrl + c) rumus di atas
- Tekan dua kali dengan tombol kiri mouse pada sel C2
- Rekatkan (Ctrl + v) ke sel C2
- Tekan dan tahan CTRL + SHIFT secara bersamaan
- Tekan Enter sekali
- Lepaskan semua kunci
Rumus Anda sekarang terlihat seperti ini. {=array_formula}
Jangan masukkan tanda kurung kurawal, tanda kurung itu muncul secara otomatis
Artikel yang direkomendasikan
Panduan pemula untuk rumus array Excel
Rumus array memungkinkan Anda melakukan perhitungan lanjutan yang tidak mungkin dilakukan dengan rumus biasa
1. 2 Menjelaskan rumus array di sel D3
Anda dapat dengan mudah mengikuti jika Anda mendapatkan file terlampir dan memilih sel D3. Lalu buka tab "Rumus" pada pita dan tekan dengan tombol kiri mouse pada tombol "Evaluasi Rumus".
Tekan dengan tombol kiri mouse pada tombol "Evaluate" yang ditunjukkan pada gambar di atas untuk pindah ke langkah berikutnya
Langkah 1 - Periksa apakah nilai peka huruf besar-kecil
Fungsi EXACT adalah fungsi peka huruf besar/kecil yang memungkinkan Anda membandingkan nilai. Jika mereka cocok EXACT mengembalikan TRUE, jika tidak FALSE
TEPAT($B$3. $B$15,TRANSPOS(B$3. $B$15))*1
Jika kita menggunakan TRANSPOSE, kita dapat membandingkan nilai satu sama lain untuk membuat array, dalam satu perhitungan. Gambar berikut menunjukkan array ini sebagai tabel indeks, saya telah menyorot sel yang cocok dengan warna hijau. Contoh, sel C3 menampilkan hasil perbandingan antara nilai di sel C2 dan B3. Karena nilainya sama, mereka harus cocok dan rumusnya mengembalikan 1 dan disorot hijau
Sekarang jelas bahwa nilai Aa memiliki duplikat karena sel J3 juga disorot hijau
Luar biasa bahwa Excel memungkinkan Anda melakukan perhitungan rumit dalam satu sel
Langkah 2 - Fungsi MMULT memungkinkan Anda menjumlahkan nilai berdasarkan kolom atau baris
Fungsi MMULT seperti fungsi SUMPRODUCT tetapi pada steroid, izinkan saya menjelaskannya. SUMPRODUCT memungkinkan Anda mengalikan lalu menjumlahkan nilai, hasilnya adalah nilai tunggal
MMULT memungkinkan Anda mengalikan dan menjumlahkan nilai berdasarkan kolom atau baris, hasilnya adalah array
MMULT(EXACT($B$3. $B$15, TRANSPOS(B$3. $B$15))*1, BARIS($B$3. $B$15)^0) dimasukkan di kolom P, lihat gambar di bawah
Sekarang mudah menemukan nilai unik di tabel indeks, jika kolom P berisi 1, nilai yang sesuai di kolom B harus unik
Langkah 3 - Periksa apakah nilai dalam array sama dengan 1 dan jika ya kembalikan nomor baris yang sesuai
Kolom Q menunjukkan nomor baris relatif yang sesuai jika nilai dalam kolom P sama dengan 1
JIKA(MMULT(EXACT($B$3. $B$15, TRANSPOS(B$3. $B$15))*1, BARIS($B$3. $B$15)^0)=1, COCOK(BARIS($B$3. $B$15), ROW($B$3. $B$15)), "")
mengembalikan array ini. {"";2;"";"";
Langkah 4 - Saring nomor baris terkecil ke-k
KECIL(JIKA(MMULT(EXACT($B$3. $B$15, TRANSPOS(B$3. $B$15))*1, BARIS($B$3. $B$15)^0)=1, COCOK(BARIS($B$3. $B$15), ROW($B$3. $B$15)), ""),ROWS($A$1. A1))
menjadi
KECIL({"";2;"";""; "";"";7; "";9;"";11; "";""},ROWS($A$1. A1))
menjadi
KECIL({"";2;"";""; "";"";7; "";9;"";11; "";""},1)
dan mengembalikan 2
Langkah 5 - Mengembalikan nilai berdasarkan koordinat
INDEX($B$3. $B$15, KECIL(IF(MMULT(EXACT($B$3. $B$15, TRANSPOS(B$3. $B$15))*1, BARIS($B$3. $B$15)^0)=1, COCOK(BARIS($B$3. $B$15), ROW($B$3. $B$15)), ""),ROWS($A$1. A1)))
menjadi
INDEX($B$3. $B$15, 2)
menjadi
INDEX({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"; "AA"; "Cc"; "EE"
dan mengembalikan "CC" di sel D3
2. Ekstrak daftar unik peka huruf besar kecil dari kolom - Excel 365
Rumus di sel D3
Rumus berikut adalah untuk pengguna Excel 365
=LET(z, B3. B15, FILTER(z, MMULT(EXACT(z, TRANSPOSE(z))*1, ROW(z)^0)=1))
2. 1 Menjelaskan rumus
Langkah 1 - Ubah array vertikal menjadi horizontal
Fungsi TRANSPOSE memungkinkan Anda mengonversi rentang vertikal ke rentang horizontal, atau sebaliknya. Ini juga berfungsi dengan array
TRANSPOS (B3. B15)
menjadi
TRANSPOSE({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"; "AA"; "Cc"; "EE"
dan kembali
{"Aa", "CC", "AA", "BB", "BB", "EE", "bb", "Aa", "aa", "AA", "Cc", "EE", "
Langkah 2 - Periksa apakah nilai cocok (peka huruf besar/kecil)
Fungsi EXACT memungkinkan Anda untuk memeriksa apakah dua nilai persis sama, mengembalikan BENAR atau SALAH. Fungsi EXACT juga mempertimbangkan huruf besar dan kecil
TEPAT (B3. B15, TRANSPOS (B3. B15))
menjadi
EXACT({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"; "AA"; "Cc"; "EE"
dan kembali
{BENAR, SALAH, SALAH, SALAH, SALAH, SALAH, SALAH, BENAR, SALAH, SALAH, SALAH, SALAH, SALAH;
Gambar di atas menunjukkan larik dalam rentang sel C3. O15. Itu membandingkan setiap nilai satu sama lain dan jika ada dua atau lebih BENAR, nilainya memiliki duplikat
Langkah 3 - Kalikan dengan 1
Fungsi MMULT tidak dapat bekerja dengan nilai boolean (TRUE atau FALSE), namun ada solusinya. Mereka memiliki persamaan numerik yang bekerja dengan fungsi MMULT
BENAR = 1
SALAH = 0 (nol)
TEPAT (B3. B15, TRANSPOS (B3. B15))*1
menjadi
{BENAR, SALAH, SALAH, SALAH, SALAH, SALAH, SALAH, BENAR, SALAH, SALAH, SALAH, SALAH, SALAH;
dan kembali
{1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0;
Langkah 4 - Buat urutan 1
Fungsi ROW mengembalikan angka yang mewakili nomor baris berdasarkan referensi sel. Jika referensi sel menunjuk ke beberapa sel, array angka dikembalikan
BARIS (B3. B15)^0
menjadi
{3;
dan mengembalikan {1;
Langkah 5 - Jumlahkan nilai berdasarkan baris
Fungsi MMULT menghitung produk matriks dari dua array, ini dapat digunakan untuk menjumlahkan nilai baik berdasarkan kolom atau baris, hasilnya adalah array
MMULT(TEPAT(B3. B15, TRANSPOS (B3. B15))*1, BARIS(B3. B15)^0)
menjadi
MMULT({1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0; 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0
dan mengembalikan {2;
Gambar di atas menunjukkan larik di kolom P
Langkah 6 - Periksa apakah unik
Nilai yang mengembalikan 1 hanya ada sekali, dua atau lebih berarti duplikat
MMULT(TEPAT(B3. B15, TRANSPOS (B3. B15))*1, BARIS(B3. B15)^0)=1
menjadi
{2;
dan mengembalikan {FALSE;
Langkah 7 - Filter nilai unik
Fungsi FILTER memungkinkan Anda mengekstrak nilai/baris berdasarkan kondisi atau kriteria
FILTER(B3. B15, MMULT(TEPAT(B3. B15, TRANSPOS (B3. B15))*1, BARIS(B3. B15)^0)=1)
menjadi
FILTER({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"; "AA"; "Cc"; "EE"
dan mengembalikan {"CC";
Langkah 8 - Persingkat formula
Fungsi LET menetapkan nama untuk hasil perhitungan. Ini dapat mempersingkat formula secara signifikan dan membuatnya berjalan lebih cepat
BIARKAN(nama1, nama_nilai1, perhitungan_atau_nama2, [nama_nilai2, perhitungan_atau_nama3. ])
BIARKAN(z, B3. B15, FILTER(z, MMULT(EXACT(z, TRANSPOSE(z))*1, ROW(z)^0)=1))
Dapatkan file Excel
Ekstrak-daftar-unik-peka huruf besar-kecil. xlsx
Kategori nilai unik
5 cara mudah untuk mengekstrak Unique Distinct Values
Pertama, izinkan saya menjelaskan perbedaan antara nilai unik dan nilai berbeda unik, penting bagi Anda untuk mengetahui perbedaannya […]
Ekstrak nilai unik dari dua kolom
Saya membaca artikel Menggabungkan Daftar Ke Daftar Nilai Berbeda di CPearson. Artikel tersebut menjelaskan kode yang Anda […]
Filter nilai unik dan urutkan berdasarkan tanggal yang berdekatan
Rumus di sel F3 mengekstrak nilai unik dari kolom C, rumus di sel F3 mengekstrak tanggal yang sesuai […]
Cantumkan semua baris unik yang berbeda pada bulan tertentu
Artikel ini mendemonstrasikan rumus yang mengekstrak catatan/baris berbeda yang unik untuk bulan tertentu yang ditentukan dalam sel C2, lihat […]
Temukan min dan maks nilai numerik unik dan duplikat
Daftar Isi Cara Menemukan Angka Duplikat Terbesar Cara Menemukan Angka Duplikat Terbesar - Excel 365 […]
Filter nilai unik dari rentang sel
Nilai unik adalah nilai yang muncul hanya sekali dalam rentang sel. Inilah yang akan saya tunjukkan di blog ini […]
Buat angka berdasarkan rentang numerik
Gambar di atas menunjukkan rumus larik di sel B3 yang menghitung angka berdasarkan rentang numerik di sel […]
Jumlahkan angka berdasarkan nilai unik yang sesuai
Rumus di sel E14 menambahkan angka dari kolom C jika nilai yang sesuai di kolom B unik […]
Filter string unik dari rentang sel
Posting blog ini menjelaskan cara membuat daftar kata unik dari rentang sel. Kata-kata unik semuanya […]
Filter nilai unik yang diurutkan dari A hingga Z
Nilai unik adalah nilai yang hanya ada satu kali dalam daftar. Daftar berbeda yang unik berisi semua nilai sel […]
Fungsi dalam artikel ini
INDEKSKECILJIKAMMULTTEPATTRANSPOSEROWMATCHROWSLETFILTER
Lebih dari 1300 rumus Excel
kategori rumus Excel
kategori Excel
Halaman rumah
Artikel terupdate terbaru
Fungsi Excel
Lebih dari 300 fungsi Excel dengan informasi mendetail termasuk sintaks, argumen, nilai kembalian, dan contoh untuk sebagian besar fungsi yang digunakan dalam rumus Excel
Formula Excel
Lebih dari 1300 formula disusun dalam subkategori
Tabel Excel
Tabel Excel menyederhanakan pekerjaan Anda dengan data, menambah atau menghapus data, memfilter, menjumlahkan, menyortir, meningkatkan keterbacaan menggunakan pemformatan sel, referensi sel, rumus, dan lainnya
Filter Lanjutan
Memungkinkan Anda memfilter data berdasarkan nilai yang dipilih , teks tertentu, atau kriteria lainnya. Ini juga memungkinkan Anda memfilter data yang ada atau memindahkan nilai yang difilter ke lokasi baru
Validasi data
Memungkinkan Anda mengontrol apa yang dapat diketik pengguna ke dalam sel. Ini memungkinkan Anda menentukan kondisi dan menampilkan pesan khusus jika data yang dimasukkan tidak valid
Daftar Turun
Memungkinkan pengguna bekerja lebih efisien dengan menampilkan daftar yang nilainya dapat dipilih pengguna. Ini memungkinkan Anda mengontrol apa yang ditampilkan dalam daftar dan lebih cepat daripada mengetik ke dalam sel
Rentang bernama
Memungkinkan Anda menamai satu sel atau lebih, ini memudahkan untuk menemukan sel menggunakan kotak Nama, membaca dan memahami rumus yang berisi nama, bukan referensi sel
Pemecah Excel
Excel Solver adalah add-in gratis yang menggunakan sel objektif, batasan berdasarkan rumus pada lembar kerja untuk melakukan analisis bagaimana-jika dan masalah keputusan lainnya seperti permutasi dan kombinasi
Bagan
Fitur Excel yang memungkinkan Anda memvisualisasikan data dalam grafik
Pemformatan Bersyarat
Memformat sel atau nilai sel berdasarkan kondisi atau kriteria, ada beberapa alat Pemformatan Bersyarat bawaan yang dapat Anda gunakan atau gunakan rumus pemformatan bersyarat yang dibuat khusus
Tabel pivot
Memungkinkan Anda dengan cepat meringkas sejumlah besar data dengan cara yang sangat ramah pengguna. Fitur Excel yang kuat ini memungkinkan Anda menganalisis, mengatur, dan mengkategorikan data penting secara efisien
VBA
VBA adalah singkatan dari Visual Basic for Applications dan merupakan bahasa pemrograman komputer yang dikembangkan oleh Microsoft, memungkinkan Anda untuk mengotomatiskan tugas yang memakan waktu dan membuat fungsi khusus
Makro
Program atau subrutin bawaan VBA yang dapat dibuat oleh siapa saja. Gunakan perekam makro untuk membuat makro VBA Anda sendiri dengan cepat