Indeks excel cocok dengan beberapa kriteria beberapa hasil

Cari 1 Nilai, Kembalikan Banyak Kecocokan

Table of Contents Show

• Cari 1 Nilai, Kembalikan Banyak Kecocokan
• Dalam video di bawah ini saya menunjukkan kepada Anda 2 metode berbeda yang mengembalikan banyak kecocokan
• Metode #1. INDEKS dan AGREGAT
• Metode #2. TEXTJOIN
• Bagaimana Anda menggunakan pencocokan INDEX dengan banyak hasil?
• Bagaimana cara mengembalikan beberapa nilai berdasarkan beberapa kriteria di Excel?
• Bagaimana Anda menggunakan pencocokan INDEX dengan banyak kriteria di Excel?
• Bisakah Anda melakukan pencocokan INDEX dengan 2 kriteria?

Rumus VLOOKUP dan INDEX & MATCH sangat bagus untuk mencari nilai dalam tabel data besar dan mengembalikan hasil dari kolom yang berdekatan

Tetapi bagaimana Anda bisa mengembalikan banyak hasil?

Bagaimana jika nilai pencarian Anda tidak unik?

Rumus standar selalu menghasilkan kecocokan pertama

Anda ingin memiliki daftar semua kecocokan dan Anda ingin memilikinya secara dinamis

Dalam video di bawah ini saya menunjukkan kepada Anda 2 metode berbeda yang mengembalikan banyak kecocokan

Metode 1 menggunakan fungsi INDEX dan AGREGAT. Ini sedikit lebih rumit untuk disiapkan, tetapi saya menjelaskan semua langkah secara mendetail dalam video. Ini adalah rumus array tetapi tidak memerlukan CSE (control + shift + enter)

Metode 2 menggunakan fungsi TEXTJOIN. Ini adalah fungsi yang relatif baru di Excel 2016, Office 365. Ini cukup sederhana untuk disiapkan tetapi ini adalah rumus larik yang membutuhkan CSE

Metode #1. INDEKS dan AGREGAT

(Menempatkan semua jawaban yang dikembalikan dalam sel terpisah)

Pro

• Bekerja dengan versi Excel apa pun
• Tidak memerlukan penggunaan CTRL-Shift-Enter untuk membuat rumus array

Kontra

• Lebih kompleks dari Metode #2

Metode #2. TEXTJOIN

(Menempatkan semua jawaban yang dikembalikan dalam satu sel sebagai daftar yang dibatasi)

Pro

• Lebih sederhana dari Metode #1

Kontra

• Memerlukan Excel 2016 dan Office 365
• Membutuhkan penggunaan CTRL-Shift-Enter untuk membuat rumus array

Objektif. Minta pengguna memilih nama Divisi dan gunakan Divisi yang dipilih untuk menampilkan daftar Aplikasi terkait

Di tabel kami, nama Divisi yang dipilih harus mengembalikan daftar seperti berikut

UtilityProductivityGameAccordBlendFightrrMisty WashSleopsHackrrTwenty20WenCalKryptisPerino

Metode #1 – INDEKS dan AGREGAT

Metode ini akan menggunakan fungsi INDEX dengan fungsi AGGREGATE untuk menemukan Aplikasi terkait untuk Divisi yang dipilih dan menyusun hasilnya ke dalam daftar baru. Kami juga akan mengintegrasikan pengujian IF untuk menekan secara visual kesalahan apa pun yang mungkin muncul saat item yang dikembalikan tidak sepenuhnya memenuhi area daftar hasil

Untuk memulai, pilih sel G4 dan masuk ke Divisi "Game"

CATATAN. Kami dapat menyediakan pengguna dengan daftar drop-down untuk memudahkan proses pemilihan Divisi, tetapi untuk kesederhanaan, kami akan meng-hardcode nama Divisi. Untuk tutorial membuat daftar dropdown unik dari daftar multi-nilai yang ada, klik link di bawah ini

Unggul. Ekstrak item unik untuk daftar drop-down validasi data dinamis

Fungsi paling umum yang digunakan orang saat menemukan item dalam daftar Excel adalah VLOOKUP. Jika Anda memerlukan penyegaran tentang penggunaan VLOOKUP, klik tautan di bawah ini

VLOOKUP Excel. Dasar-dasar VLOOKUP dan HLOOKUP dijelaskan dengan contoh

Masalah dengan menggunakan VLOOKUP dalam skenario ini adalah bahwa VLOOKUP akan selalu berhenti pada item pertama yang cocok dalam daftar pencarian. Jika kita sedang mencari “Game”, VLOOKUP akan selalu berhenti di sel A7

.

Kami ingin membuat daftar tempat kemunculan pertama Aplikasi "Game" ditempatkan di sel G5;

Kami akan menggunakan fungsi INDEX dan AGGREGATE untuk membuat daftar ini. Jika Anda memerlukan penyegaran tentang penggunaan INDEX (dan MATCH), klik tautan di bawah ini

Cara menggunakan Excel INDEX MATCH (cara yang benar)

Pilih sel G5 dan mulai dengan membuat fungsi INDEX

=INDEX(array, row_num, [column_num])

Fungsi INDEX memiliki parameter berikut

• Larik = sel tempat item diekstraksi dan dikembalikan sebagai jawaban
• Row_num = posisi "atas dan bawah" dalam daftar untuk memindahkan untuk mengekstrak data
• Column_num = posisi "kiri ke kanan" dalam daftar untuk memindahkan untuk mengekstrak data

Kami ingin mengekstrak nama Aplikasi dari sel B5. B14

=INDEX($B$5:$B$14

Kami ingin mengembalikan Aplikasi bernama "Fightrr" dari posisi ke-3 dalam daftar Aplikasi, jadi sebagai ujian kami akan mengkodekan nomor "3"

=INDEX($B$5:$B$14,3)

Jika kita mengisi rumus ke bawah sel di kolom "G", Aplikasi bernama "Fighter" muncul berulang kali, perilaku seperti hasil VLOOKUP sebelumnya. Kita perlu menemukan cara agar nilai pengembalian row_num berubah dari "3" menjadi "4" menjadi "5" menjadi "7". Kita tidak bisa begitu saja menambah nilai parameter baris-num sebanyak 1 setiap kali kita mengulang rumus;

Kami akan menggunakan fungsi AGGREGATE untuk menghasilkan daftar baris (mis. e. posisi) di mana Divisi yang dipilih ("Permainan") ditemukan. Keuntungan menggunakan fungsi AGGREGATE adalah dapat menerima banyak jawaban tanpa menggunakan CTRL-Shift-Enter

Fungsi AGREGAT memiliki parameter berikut

• Function_num = angka yang sesuai dengan fungsi dalam daftar AGREGAT

Kami akan menggunakan fungsi KECIL (nomor 15)

• Opsi = angka yang sesuai dengan perilaku untuk menangani kesalahan, data tersembunyi, dan fungsi AGREGAT dan SUBTOTAL lainnya saat digabungkan dengan data

Kami akan menggunakan opsi #3 untuk mengabaikan semua masalah lainnya

• Array = nilai yang akan digabungkan
  Kami akan memilih sel A5. A14
• [k] = nilai opsional saat menggunakan fungsi pemilihan, seperti KECIL atau BESAR
  Kami akan menyimpan parameter ini untuk nanti

TIP. Untuk fokus pada satu masalah pada satu waktu, kami akan membuat fungsi AGREGAT ke samping di kolom "H". Setelah fungsi AGGREGATE bekerja sesuai keinginan kita, kita akan melipat logika AGGREGATE ke dalam fungsi INDEX

Pilih sel H5 dan masukkan rumus berikut

=AGGREGATE(15,3,$A$5:$A$14)

Jika kita menyorot parameter array dan menekan F9, kita akan melihat bahwa array yang disediakan mengembalikan kata-kata yang terletak di sel A5. A14

.

Tujuan kami bukan untuk menemukan kata terkecil; . Untuk melakukan ini, kami akan menguji setiap sel dalam larik untuk melihat apakah cocok dengan Divisi yang dipilih. Ubah array sebagai berikut

=AGGREGATE(15,3,($A$5:$A$14=$G$4))

Jika kita menyorot parameter array dan menekan F9, kita akan melihat hasil pengujian berikut

.

Masalahnya di sini adalah Excel menginterpretasikan respons FALSE sebagai 0 (nol) dan respons BENAR sebagai 1 (satu). Jika kita menggunakan fungsi SMALL untuk penemuan, 0 akan dipilih terlebih dahulu. Kami ingin mengonversi respons SALAH menjadi kesalahan dan respons BENAR ke nomor posisi dalam daftar (mis. e. 3, 4, 5, 7). Untuk melakukan ini, kami akan membagi array dengan sendirinya

=AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)))

Karena 0 ÷ 0 = ERROR dan 1 ÷ 1 = 1, kita mendapatkan daftar respons berikut

.

Untuk mengubah 1 menjadi angka posisi, kita akan mengalikan setiap 1 dengan nomor baris yang sesuai

=AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14)))

Ini akan memberikan tanggapan berikut

.

Karena kami tidak memulai daftar kami di baris 1, posisi kami di daftar diimbangi; . Karena tajuk untuk tabel ini ada di baris 4, kami akan mengurangi nilai posisi baris tajuk dari daftar jawaban sebelumnya

=AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4))

Ini akan memberikan tanggapan berikut

.

Sekarang saatnya untuk mengulang kembali dan menggunakan opsi [k] (parameter ke-4) dari fungsi AGGREGATE. Kami ingin fungsi SMALL bertambah satu untuk setiap salinan fungsi AGGREGATE. Ini akan dilakukan dengan trik golok menghitung sel dalam pemilihan rentang yang terus meningkat

=AGGREGATE(15,3,(($A$5:$A$14=$G$4)/($A$5:$A$14=$G$4)*ROW($A$5:$A$14))-ROW($A$4),N)

Dalam hal ini, "N" akan menjadi fungsi berikut

ROWS($F$5:F5)

Saat digunakan pada fungsi AGGREGATE pertama, hasilnya adalah 1. Dengan menjangkar awal rentang ke sel $F$5 sebagai referensi absolut dan meninggalkan akhir rentang F5 sebagai referensi relatif, rentang akan mengubah ukurannya saat akhir rentang bergerak lebih jauh dari awal rentang

CellFunctionRange Tinggi (dalam sel)H5ROWS($F$5. F5)1H6ROWS($F$5. F6)2H7ROWS($F$5. F7)3H8ROWS($F$5. F8)4

Rumus dengan penghitung baris cerdas akan muncul sebagai berikut

=INDEX($B$5:$B$14

Sekarang salin rumus (tanpa tanda sama dengan) dari sel H5 dan rekatkan ke dalam parameter row_num dari fungsi INDEX di sel G5. Rumus yang diperbarui akan muncul sebagai berikut

Formula asli

=INDEX($B$5:$B$14

Formula yang diperbarui

=INDEX($B$5:$B$14

Isi rumus ke bawah sampai sel G5. G14. Kami sekarang memiliki daftar Aplikasi terkait ke Divisi yang dipilih serta kesalahan ketika daftar pengembalian kami lebih pendek dari panjang daftar maksimum yang kami harapkan

Kami ingin menyembunyikan kesalahan

Cara sederhana (baca “malas”) untuk menyembunyikan kesalahan adalah menyarangkan seluruh fungsi INDEX/AGGREGATE dalam fungsi IFERROR seperti berikut

=INDEX($B$5:$B$14

Masalahnya adalah IFERROR harus sepenuhnya memproses fungsi INDEX/AGGREGATE untuk menentukan apakah kesalahan dihasilkan. Ini dapat menyebabkan banyak siklus CPU yang terbuang saat membuat daftar panjang Aplikasi terkait

Taktik yang lebih baik adalah menggunakan pernyataan IF untuk menghitung berapa kali Divisi yang dipilih muncul dalam daftar Divisi dan kemudian membandingkannya dengan panjang daftar Aplikasi saat ini. Jika panjang daftar Aplikasi melebihi jumlah Divisi yang dipilih muncul di daftar Divisi asli, fungsi IF tidak akan menjalankan rumus INDEX/AGGREGAT

Untuk memberikan fungsi IF sesuatu untuk dibandingkan, pilih sel F4 dan masukkan rumus "pembantu" berikut

=INDEX($B$5:$B$14

Fungsi IF yang diperbarui akan melakukan pengujian berikut

=INDEX($B$5:$B$14

Rumus ini akan menguji rentang yang terus berkembang yang dimulai di sel F5 untuk menentukan apakah tinggi rentang melebihi nilai yang diberikan oleh sel pembantu F4. Rumus yang diperbarui akan muncul sebagai berikut

=INDEX($B$5:$B$14

Karena "Game" memiliki 4 entri di kolom "A", ketika kita sampai ke iterasi ke-5, fungsi ROWS pada fungsi IF akan menghasilkan 5. Tes menjadi "5<=4" yang menghasilkan kondisi FALSE, sehingga menampilkan teks kosong yang diberikan oleh dua tanda kutip ganda

BONUS

Jika Anda tidak ingin memiliki rumus "pembantu" di sel F4, Anda dapat melipat logika COUNTIF ke dalam fungsi IF. Rumus yang diperbarui akan muncul sebagai berikut

=INDEX($B$5:$B$14

CATATAN. Saat COUNTIF berada di sel "pembantu", kami tidak membuat referensi apa pun menjadi absolut karena kami tidak mengulangi rumus di sel lain mana pun. Jika kita melipat logika COUNTIF ke dalam fungsi IF, kita perlu membuat referensi menjadi absolut karena sifat rumus yang berulang di kolom "G"

Satu-satunya kelemahan dari konsolidasi ini adalah penghitungan Divisi yang dipilih harus diulang untuk setiap fungsi IF. Dalam daftar yang lebih panjang (mis. e. ratusan ribu baris), hal ini dapat berdampak negatif terhadap waktu pemrosesan. Dengan melakukan COUNTIF sebagai perhitungan "pembantu" yang terpisah, itu hanya boleh dilakukan satu kali

Metode #2 – TEXTJOIN

Menggunakan fungsi TEXTJOIN (tersedia di Excel 2016 dari Office 365) kami akan tampil

Fungsi TEXTJOIN memiliki parameter berikut

• Pembatas – karakter yang memisahkan nilai yang dikembalikan
  Kami akan membuat daftar yang dibatasi koma, jadi kami akan memasukkan ","
• Abaikan_kosong – Ini menentukan apakah akan menyertakan sel kosong dalam daftar hasil
  Kami tidak yakin dengan kebutuhan kami, jadi kami akan menggunakan FALSE dan mengubahnya nanti jika diperlukan
• Teks1 – Ini adalah rentang sel yang berisi data sumber
  Kami akan memilih A5. A14

Mulailah dengan memilih sel G4 dan mengganti Divisi "Game" dengan "Utility"

Pilih sel H5 dan masukkan rumus berikut

=INDEX($B$5:$B$14

Ini memberikan hasil berikut

=INDEX($B$5:$B$14

Kami tidak menginginkan daftar lengkap Aplikasi, kami hanya menginginkan Aplikasi yang terkait dengan Divisi yang dipilih (sel G4). Untuk memfilter daftar ke Aplikasi yang terkait saja, kami akan melakukan pengujian logis, menggunakan IF, untuk membandingkan setiap Divisi yang terkait dengan Aplikasi dengan Divisi yang dipilih. Perbarui rumus sebagai berikut. Ingatlah untuk menyelesaikan rumus dengan menekan CTRL-Shift-Enter

=INDEX($B$5:$B$14,3)

Ini memberikan hasil berikut

=INDEX($B$5:$B$14,3)

Jika kita menyorot fungsi IF dalam rumus dan menekan F9, kita akan melihat respons berikut

.

Untuk menghapus notasi sel kosong, perbarui rumus dengan mengubah parameter Ignore_empty dari FALSE menjadi TRUE. Ingatlah untuk menyelesaikan rumus dengan menekan CTRL-Shift-Enter

Bagaimana Anda menggunakan pencocokan INDEX dengan banyak hasil?

Bagaimana cara mengembalikan beberapa nilai berdasarkan beberapa kriteria di Excel?

Bagaimana Anda menggunakan pencocokan INDEX dengan banyak kriteria di Excel?

Bisakah Anda melakukan pencocokan INDEX dengan 2 kriteria?