Formula Excel: Ekstrak semua padanan dengan lajur penolong -

Isi kandungan

Formula generik

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

Ringkasan

Salah satu cara untuk mengekstrak pelbagai padanan di Excel adalah dengan menggunakan INDEX dan MATCH dengan lajur pembantu yang menandakan data yang sepadan. Ini mengelakkan kerumitan formula susunan yang lebih maju. Dalam contoh yang ditunjukkan, formula dalam H6 adalah:

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

di mana ct (G3), data (B3: E52), dan helper (E3: E52) dinamakan julat.

Penjelasan

Cabaran dengan formula pencarian yang mengambil lebih daripada satu perlawanan adalah menguruskan pendua (iaitu beberapa perlawanan). Rumus carian seperti VLOOKUP dan INDEX + MATCH dapat dengan mudah mencari perlawanan pertama, tetapi lebih sukar untuk mencari "semua perlawanan" apabila kriteria mencari lebih dari satu perlawanan.

Rumus ini menangani cabaran ini dengan menggunakan kolom penolong yang mengembalikan nilai numerik yang dapat digunakan untuk mengekstrak pelbagai padanan dengan mudah. Rumus di ruang penolong kelihatan seperti ini:

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

Lajur penolong menguji setiap baris dalam data untuk melihat apakah Jabatan di lajur C sepadan dengan nilai di I3 dan Bangunan di lajur D sepadan dengan nilai di J3. Kedua-dua ujian logik mesti mengembalikan BENAR agar DAN mengembalikan BENAR.

Untuk setiap baris, hasil dari fungsi AND ditambahkan ke "nilai di atas" di lajur penolong untuk menghasilkan kiraan. Kesan praktikal formula ini adalah pembilang kenaikan yang hanya berubah apabila pertandingan (baru) dijumpai. Kemudian nilainya tetap sama sehingga perlawanan seterusnya dijumpai. Ini berfungsi kerana hasil BENAR / PALSU dikembalikan oleh AND dipaksakan kepada nilai 1/0 sebagai sebahagian daripada operasi penjumlahan. Hasil PALSU tidak menambah apa-apa, dan keputusan BENAR menambah 1.

Kembali ke kawasan pengekstrakan, formula pencarian Nama di lajur H kelihatan seperti ini:

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

Bekerja dari dalam ke luar, bahagian INDEX + MATCH formula mencari nama untuk perlawanan pertama yang dijumpai, menggunakan nombor baris di lajur G sebagai nilai padanan:

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

INDEX menerima semua 3 lajur data sebagai array (dinamakan julat "data"), dan MATCH dikonfigurasi untuk mencocokkan nombor baris di dalam kolom penolong (julat bernama "helper") dalam mod pencocokan tepat (argumen ke-3 ditetapkan ke nol) .

Di sinilah kepandaian formula menjadi jelas. Lajur pembantu jelas mengandungi pendua, tetapi tidak menjadi masalah, kerana PERTANDINGAN hanya akan sesuai dengan nilai pertama. Berdasarkan reka bentuk, setiap "nilai pertama" sesuai dengan baris yang betul dalam jadual data.

Rumus dalam lajur I dan J sama dengan H, kecuali untuk bilangan lajur, yang dinaikkan dalam setiap kes dengan satu.

Pernyataan IF yang membungkus formula INDEX / MATCH melakukan fungsi sederhana - ia memeriksa setiap nombor baris di kawasan pengekstrakan untuk melihat apakah nombor baris kurang dari atau sama dengan nilai dalam G3 (dinamakan julat "ct"), yang jumlah keseluruhan semua rekod yang sepadan. Sekiranya demikian, logik INDEX / MATCH dijalankan. Sekiranya tidak, JIKA mengeluarkan rentetan kosong ("").

Rumus dalam G3 (dinamakan julat "ct") mudah:

=MAX(helper)

Oleh kerana nilai maksimum di lajur penolong adalah sama dengan jumlah kiraan padanan, fungsi MAX adalah semua yang kita perlukan.

Catatan: kawasan pengekstrakan perlu dikonfigurasi secara manual untuk mengendalikan seberapa banyak data yang diperlukan (iaitu 5 baris, 10 baris, 20 baris, dll.). Dalam contoh ini, terhad kepada 5 baris sahaja untuk memastikan lembaran kerja tetap padat.

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