Tutorial ini akan menunjukkan cara melakukan pencarian case-sensitive di Excel menggunakan dua metode berbeda Show
Metode 1 – Fungsi LOOKUPFungsi PENCARIANFungsi LOOKUP digunakan untuk mencari kecocokan perkiraan untuk nilai dalam kolom dan mengembalikan nilai yang sesuai dari kolom lain Pencarian peka huruf besar kecilDengan 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 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 SUMPRODUCTFungsi SUMPRODUKFungsi SUMPRODUCT digunakan untuk mengalikan array angka, menjumlahkan array yang dihasilkan SUMPRODUCT peka huruf besar-kecilBerbeda dengan metode LOOKUP, nilai tidak perlu diurutkan agar bisa bekerja. Kita masih perlu mengkombinasikan EXACT dalam sebuah formula untuk mendapatkan hasil seperti itu 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 isiDefinisi 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 Pastikan Anda membaca artikel berikut jika Anda ingin mengekstraksi nilai duplikat Artikel yang direkomendasikan Ekstrak daftar duplikat dari kolom 1. 1 Cara memasukkan rumus array
Rumus Anda sekarang terlihat seperti ini. {=array_formula} Artikel yang direkomendasikan Panduan pemula untuk rumus array Excel 1. 2 Menjelaskan rumus array di sel D3Anda 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-kecilFungsi 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 barisFungsi 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 sesuaiKolom 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-kKECIL(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 koordinatINDEX($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 365Rumus 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 rumusLangkah 1 - Ubah array vertikal menjadi horizontalFungsi 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 1Fungsi MMULT tidak dapat bekerja dengan nilai boolean (TRUE atau FALSE), namun ada solusinya. Mereka memiliki persamaan numerik yang bekerja dengan fungsi MMULT BENAR = 1 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 1Fungsi 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 barisFungsi 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 unikNilai 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 unikFungsi 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 formulaFungsi 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 ExcelEkstrak-daftar-unik-peka huruf besar-kecil. xlsx Kategori nilai unik5 cara mudah untuk mengekstrak Unique Distinct Values Ekstrak nilai unik dari dua kolom Filter nilai unik dan urutkan berdasarkan tanggal yang berdekatan Cantumkan semua baris unik yang berbeda pada bulan tertentu Temukan min dan maks nilai numerik unik dan duplikat Filter nilai unik dari rentang sel Buat angka berdasarkan rentang numerik Jumlahkan angka berdasarkan nilai unik yang sesuai Filter string unik dari rentang sel Filter nilai unik yang diurutkan dari A hingga Z Fungsi dalam artikel iniINDEKSKECILJIKAMMULTTEPATTRANSPOSEROWMATCHROWSLETFILTER Lebih dari 1300 rumus Excel kategori rumus Excelkategori ExcelHalaman 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 Apakah kasus formula unikJika Anda memiliki daftar data di Google Spreadsheet, Anda dapat dengan mudah menghitung nilai unik dengan fungsi COUNTUNIQUE. Namun, fungsi ini peka huruf besar/kecil dan mungkin tidak memberikan hasil yang diinginkan.
Bagaimana Anda membuat case unik tidak sensitif?Batasan tidak sensitif huruf besar-kecil dalam database oracle . Create an unique index on the field. create unique index Tambahkan batasan unik pada bidang sebagai. ALTER TABLE person ADD CONSTRAINT person_name_unique UNIQUE(LOWER(first_name),LOWER(last_name)); Bagaimana cara mengabaikan kasusSelain itu, fungsi REGEXMATCH peka terhadap huruf besar-kecil. Namun kita dapat menggunakan pengubah (? i) di REGEXMATCH untuk mengabaikan sensitivitas huruf besar-kecil di Google Spreadsheet.
Apakah kasus fungsi unikFungsi UNIQUE peka huruf besar/kecil , sehingga akan mengembalikan hasil yang berbeda jika teks menggunakan huruf besar berbeda. |
Google sheets unique case tidak sensitif
Pos Terkait
Copyright © 2024 kemunculan Inc.
