Ambil nilai pencocokan pertama dalam sel terhadap daftar
Misalkan Anda memiliki daftar kata kunci, untuk mengambil kata kunci pertama yang muncul di sel tertentu, sementara sel berisi beberapa nilai lain, Anda harus menggunakan INDEKS dan MATCH rumus dengan bantuan AGREGAT dan CARI fungsi.
Bagaimana cara mengambil nilai pencocokan pertama dalam sel terhadap daftar?
Untuk mendapatkan kata kunci pertama yang cocok dalam sel dengan daftar Kata Kunci seperti tabel yang ditunjukkan di atas, itu berarti bahwa kita perlu melakukan pencocokan isi daripada pencocokan persis. Untuk melakukannya, Anda dapat menggunakan fungsi SEARCH untuk memasukkan posisi kata kunci yang muncul di sel dalam angka numerik ke dalam fungsi AGGREGATE. Kemudian AGGREGATE akan mendapatkan angka terkecil dengan mengaturnya function_num ke 15 dan argumen ref2 ke 1. Kemudian gunakan MATCH untuk menemukan nilai terkecil pertama dan masukkan nomor posisi ke INDEX untuk mengambil nilai pada posisi itu.
Sintaks umum
=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))
Catatan: Ini adalah rumus array yang mengharuskan Anda memasukkan dengan Ctrl + perubahan + Enter.
- kata kunci_rng: Rentang sel dengan kata kunci.
- pencarian_sel: Sel yang akan dicari jika berisi kata kunci.
Untuk mengambil kembali kata kunci yang cocok pertama kali muncul di sel B5 terhadap kolom Kata Kunci, silakan salin atau masukkan rumus di bawah ini di sel C5, dan tekan Ctrl + perubahan + Enter untuk mendapatkan hasil:
=INDEKS($ E $ 5: $ E $ 7,MATCH(AGGREGAT(15,6,CARI($ E $ 5: $ E $ 7,B5),1),CARI($ E $ 5: $ E $ 7,B5),0))
Catatan: Tanda dolar ($) di atas menunjukkan referensi absolut, yang berarti kata kunci_rng dalam rumus tidak akan berubah saat Anda memindahkan atau menyalin rumus ke sel lain. Namun, tidak ada tanda dolar yang ditambahkan ke pencarian_sel karena Anda ingin menjadi dinamis. Setelah memasukkan rumus, seret gagang isian ke bawah untuk menerapkan rumus ke sel di bawah ini.
Penjelasan rumus
=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))
- CARI($E$5:$E$7,B5): Fungsi SEARCH mengembalikan posisi setiap kata kunci dari rentang $ E $ 5: $ E $ 7 ketika ditemukan dalam angka numerik, dan #NILAI! kesalahan jika tidak ditemukan. Hasilnya akan menjadi array seperti ini: {15;11;#NILAI!}.
- AGREGAT(15,6,CARI($E$5:$E$7,B5), 1) = AGREGAT(15,6,{15;11;#NILAI!}, 1): Fungsi AGGREGATE dengan a fungsi_jumlah dari 15 dan pilihan 6 akan mengembalikan nilai terkecil dalam array sesuai dengan ref2 argumen 1, sambil mengabaikan nilai kesalahan. Jadi, cuplikannya akan kembali 11.
- COCOK(AGREGAT(15,6,CARI($E$5:$E$7,B5), 1),CARI($E$5:$E$7,B5), 0) = COCOK(11,{15;11;#NILAI!}, 0): Grafik kecocokan_tipe 0 memaksa fungsi MATCH untuk melakukan pencocokan tepat dan mengembalikan posisi 11 dalam susunan {15;11;#NILAI!}. Jadi, fungsinya akan kembali 2.
- INDEKS($E$5:$E$7,COCOK(AGREGAT(15,6,CARI($E$5:$E$7,B5), 1),CARI($E$5:$E$7,B5), 0)) = INDEKS($E$5:$E$7,2): Fungsi INDEX kemudian mengembalikan 2nd nilai dalam kisaran $ E $ 5: $ E $ 7, Yang bbb.
Ucapan
- Jika tidak ada kata kunci dalam sel, a #JUMLAH! kesalahan akan dikembalikan.
- Rumusnya tidak peka huruf besar-kecil. Untuk melakukan pencocokan peka huruf besar-kecil, Anda cukup mengganti fungsi SEARCH dengan MENCARI.
Fungsi terkait
Fungsi INDEX Excel mengembalikan nilai yang ditampilkan berdasarkan posisi tertentu dari rentang atau larik.
Fungsi Excel MATCH mencari nilai tertentu dalam rentang sel, dan mengembalikan posisi relatif dari nilai tersebut.
Di Excel, fungsi SEARCH dapat membantu Anda menemukan posisi karakter atau substring tertentu dari string teks yang diberikan seperti gambar berikut yang ditampilkan. Tutorial ini, saya akan memperkenalkan cara menggunakan fungsi SEARCH ini di Excel.
Fungsi Excel AGGREGATE mengembalikan agregat perhitungan seperti SUM, COUNT, SMALLL dan seterusnya dengan opsi untuk mengabaikan kesalahan dan baris tersembunyi.
Rumus Terkait
Ambil nilai daftar pertama dari sel
Untuk mengambil kata kunci pertama yang terdaftar dalam rentang dari sel tertentu, sementara sel berisi salah satu dari beberapa nilai, Anda harus menggunakan rumus array yang agak rumit dengan fungsi INDEX, MATCH, ISNUMBER, dan SEARCH.
Pencocokan persis dengan INDEX dan MATCH
Jika Anda perlu mengetahui informasi yang tercantum di Excel tentang produk, film, atau orang tertentu, dll., Anda harus memanfaatkan kombinasi fungsi INDEX dan MATCH dengan baik.
Periksa apakah sel berisi teks tertentu
Di sini tutorial memberikan beberapa rumus untuk memeriksa apakah sel berisi teks tertentu dan mengembalikan TRUE dan FALSE seperti gambar di bawah ini yang ditampilkan, dan menjelaskan argumen dan cara kerja rumus.
Periksa apakah sel berisi banyak hal
Misalkan di Excel, ada daftar nilai di kolom E, Anda ingin memeriksa apakah sel di kolom B berisi semua nilai di kolom E, dan mengembalikan TRUE atau FALSE seperti gambar di bawah ini. Di sini, di tutorial ini, ini memberikan rumus untuk menyelesaikan tugas ini.
Periksa apakah sel berisi salah satu dari banyak hal
Tutorial ini menyediakan rumus untuk memeriksa apakah sel berisi salah satu dari beberapa nilai di Excel, dan menjelaskan argumen dalam rumus dan cara kerja rumus.
Alat Produktivitas Kantor Terbaik
Kutools for Excel - Membantu Anda Menonjol Dari Kerumunan
Kutools for Excel Membanggakan Lebih dari 300 Fitur, Memastikan Apa yang Anda Butuhkan Hanya Dengan Sekali Klik...
Tab Office - Aktifkan Pembacaan dan Pengeditan dengan Tab di Microsoft Office (termasuk Excel)
- Satu detik untuk beralih di antara lusinan dokumen terbuka!
- Kurangi ratusan klik mouse untuk Anda setiap hari, ucapkan selamat tinggal pada tangan mouse.
- Meningkatkan produktivitas Anda sebesar 50% saat melihat dan mengedit banyak dokumen.
- Menghadirkan Tab Efisien ke Office (termasuk Excel), Sama Seperti Chrome, Edge, dan Firefox.
