Rumus Excel: Ekstrak semua kecocokan dengan kolom pembantu -

Daftar Isi

Formula umum

=IF(rowcheck,INDEX(data,MATCH(rownum,helper,0),column),"")

Ringkasan

Salah satu cara untuk mengekstrak beberapa kecocokan di Excel adalah dengan menggunakan INDEX dan MATCH dengan kolom pembantu yang menandai data yang cocok. Ini menghindari kompleksitas rumus array yang lebih canggih. Pada contoh yang ditampilkan, rumus di H6 adalah:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

di mana ct (G3), data (B3: E52), dan helper (E3: E52) diberi nama rentang.

Penjelasan

Tantangan dengan rumus pencarian yang mengambil lebih dari satu kecocokan adalah mengelola duplikat (yaitu beberapa kecocokan). Rumus pencarian seperti VLOOKUP dan INDEX + MATCH dapat dengan mudah menemukan kecocokan pertama, tetapi akan jauh lebih sulit untuk mencari "semua kecocokan" saat kriteria menemukan lebih dari satu kecocokan.

Rumus ini menangani tantangan ini dengan menggunakan kolom pembantu yang mengembalikan nilai numerik yang dapat digunakan untuk mengekstrak banyak kecocokan dengan mudah. Rumus di kolom helper terlihat seperti ini:

=SUM(E2,AND(C3=$I$3,D3=$J$3))

Kolom pembantu menguji setiap baris dalam data untuk melihat apakah Departemen di kolom C cocok dengan nilai di I3 dan Bangunan di kolom D cocok dengan nilai di J3. Kedua tes logika harus mengembalikan TRUE agar AND menampilkan TRUE.

Untuk setiap baris, hasil dari fungsi DAN ditambahkan ke "nilai di atas" di kolom pembantu untuk menghasilkan hitungan. Efek praktis dari rumus ini adalah penghitung inkrementasi yang hanya berubah ketika kecocokan (baru) ditemukan. Kemudian nilainya tetap sama sampai kecocokan berikutnya ditemukan. Ini berfungsi karena hasil TRUE / FALSE yang dikembalikan oleh AND dipaksa ke nilai 1/0 sebagai bagian dari operasi penjumlahan. Hasil yang SALAH tidak menambahkan apa-apa, dan hasil yang BENAR menambahkan 1.

Kembali ke area ekstraksi, rumus pencarian untuk Nama di kolom H terlihat seperti ini:

=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")

Bekerja dari dalam ke luar, bagian INDEX + MATCH dari rumus mencari nama untuk kecocokan pertama yang ditemukan, menggunakan nomor baris di kolom G sebagai nilai kecocokan:

INDEX(data,MATCH($G6,helper,0),1)

INDEX menerima semua 3 kolom data sebagai larik (bernama rentang "data"), dan MATCH dikonfigurasi agar sesuai dengan nomor baris di dalam kolom pembantu (rentang bernama "pembantu") dalam mode pencocokan tepat (argumen ke-3 disetel ke nol) .

Di sinilah kepintaran rumus menjadi nyata. Kolom pembantu jelas berisi duplikat, tetapi itu tidak masalah, karena MATCH hanya akan cocok dengan nilai pertama. Secara desain, setiap "nilai pertama" sesuai dengan baris yang benar dalam tabel data.

Rumus dalam kolom I dan J sama dengan H, kecuali untuk nomor kolom, yang dalam setiap kasus bertambah satu.

Pernyataan IF yang membungkus rumus INDEX / MATCH menjalankan fungsi sederhana - ia memeriksa setiap nomor baris di area ekstraksi untuk melihat apakah nomor baris kurang dari atau sama dengan nilai di G3 (bernama rentang "ct"), yaitu jumlah total dari semua catatan yang cocok. Jika demikian, logika INDEX / MATCH dijalankan. Jika tidak, IF mengeluarkan string kosong ("").

Rumus di G3 (bernama range "ct") sederhana:

=MAX(helper)

Karena nilai maksimum di kolom pembantu sama dengan jumlah pertandingan total, fungsi MAX adalah yang kita butuhkan.

Catatan: area ekstraksi perlu dikonfigurasi secara manual untuk menangani data sebanyak yang diperlukan (mis. 5 baris, 10 baris, 20 baris, dll.). Dalam contoh ini, dibatasi hingga 5 baris hanya untuk membuat lembar kerja tetap padat.

Saya mempelajari teknik ini dalam buku Mike Girvin, Control + Shift + Enter.