Excel Lookup Formula dengan Kriteria Pelbagai

Dengan menggunakan formula tatasusunan dalam Excel, kita boleh membuat formula pencarian yang menggunakan pelbagai kriteria untuk mencari maklumat dalam pangkalan data atau jadual data.

Formula array melibatkan sarang fungsi MATCH di dalam fungsi INDEX .

Tutorial ini termasuk contoh langkah demi langkah untuk membuat formula carian yang menggunakan beberapa kriteria untuk mencari pembekal Widget titanium dalam pangkalan data sampel.

Mengikuti langkah-langkah dalam topik tutorial di bawah ini berjalan anda melalui membuat dan menggunakan formula yang dilihat pada imej di atas.

01 dari 09

Memasuki Data Tutorial

Langkah pertama dalam tutorial adalah memasukkan data ke dalam lembaran kerja Excel.

Untuk mengikuti langkah-langkah dalam tutorial masukkan data yang ditunjukkan dalam imej di atas ke dalam sel berikut.

• Masukkan julat atas data ke dalam sel D1 hingga F2
• Masukkan julat kedua ke dalam sel D5 hingga F11

Baris 3 dan 4 dibiarkan kosong untuk menampung formula array yang dibuat semasa tutorial ini.

Tutorial tidak termasuk pemformatan yang dilihat pada imej, tetapi ini tidak akan mempengaruhi bagaimana formula carian berfungsi.

Maklumat mengenai pilihan pemformatan yang serupa dengan yang dilihat di atas boleh didapati dalam Tutorial Pemformatan Dasar Excel ini.

02 dari 09

Memulakan Fungsi INDEX

Fungsi INDEX adalah salah satu daripada beberapa dalam Excel yang mempunyai pelbagai bentuk. Fungsi ini mempunyai Borang Array dan Borang Rujukan .

Borang Array mengembalikan data sebenar dari pangkalan data atau jadual data, manakala Borang Rujukan memberikan rujukan sel atau lokasi data dalam jadual.

Dalam tutorial ini kami akan menggunakan Borang Array kerana kami ingin mengetahui nama pembekal untuk widget titanium dan bukannya rujukan sel kepada pembekal ini dalam pangkalan data kami.

Setiap bentuk mempunyai senarai hujah yang berbeza yang mesti dipilih sebelum memulakan fungsi.

Langkah Tutorial

1. Klik pada sel F3 untuk menjadikannya sel aktif . Di sinilah kita akan memasuki fungsi bersarang.
2. Klik pada tab Rumus pada menu reben .
3. Pilih Lookup dan Rujukan daripada reben untuk membuka senarai drop-down fungsi.
4. Klik pada INDEX dalam senarai untuk memaparkan kotak dialog Pilih Argumen .
5. Pilih array, row_num, col_num pilihan dalam kotak dialog.
6. Klik OK untuk membuka kotak dialog fungsi INDEX.

03 dari 09

Memasuki Argumen Aras Fungsi INDEX

Hujah pertama yang diperlukan adalah hujah Array. Argumen ini menentukan julat sel untuk dicari data yang dikehendaki.

Untuk tutorial ini hujah ini akan menjadi pangkalan data sampel kami.

Langkah Tutorial

1. Dalam kotak dialog fungsi INDEX, klik pada baris Array .
2. Sorot sel-sel D6 ke F11 dalam lembaran kerja untuk memasuki julat ke dalam kotak dialog.

04 dari 09

Memulakan Fungsi MATCH Nested

Apabila bersarang satu fungsi di dalam yang lain, tidak mungkin untuk membuka kotak dialog fungsi kedua atau bersarang untuk memasukkan argumen yang diperlukan.

Fungsi bersarang mesti ditaip sebagai salah satu argumen fungsi pertama.

Dalam tutorial ini, fungsi MATCH bersarang dan hujahnya akan dimasukkan ke dalam baris kedua kotak dialog fungsi INDEX - baris Row_num .

Adalah penting untuk diperhatikan bahawa, apabila memasuki fungsi secara manual, hujah fungsi dipisahkan dari satu sama lain dengan koma "," .

Memasuki Argumen Lookup Lookup MATCH itu

Langkah pertama dalam memasuki fungsi MATCH bersarang ialah memasukkan argumen Lookup_value .

Lookup Lookup akan menjadi lokasi atau rujukan sel untuk istilah carian yang kita mahu padankan dalam pangkalan data.

Biasanya Lookup_value hanya menerima satu kriteria carian atau istilah. Untuk mencari pelbagai kriteria, kita perlu melanjutkan Lookup_value .

Ini dilakukan dengan menggabungkan atau menyertai dua atau lebih rujukan sel bersama menggunakan simbol ampersand " & ".

Langkah Tutorial

1. Dalam kotak dialog fungsi INDEX, klik pada baris Row_num .
2. Taipkan matlamat nama fungsi diikuti dengan kurungan bulat terbuka " ( "
3. Klik pada sel D3 untuk memasukkan rujukan sel ke dalam kotak dialog.
4. Taip ampersand " & " selepas rujukan sel D3 untuk menambah rujukan sel kedua.
5. Klik pada sel E3 untuk memasukkan rujukan sel kedua ke dalam kotak dialog.
6. Taipkan koma "," selepas rujukan sel E3 untuk menyelesaikan kemasukan hujah Lookup_value MATCH itu.
7. Biarkan kotak dialog fungsi INDEX terbuka untuk langkah seterusnya dalam tutorial.

Dalam langkah terakhir tutorial, Lookup_values ​​akan dimasukkan ke dalam sel D3 dan E3 lembaran kerja.

05 dari 09

Menambah Lookup_array untuk Fungsi MATCH

Langkah ini meliputi menambah argumen Lookup_array untuk fungsi MATCH bersarang.

Lookup_array adalah rangkaian sel yang fungsi MATCH akan mencari untuk mencari hujah Lookup_value yang ditambahkan pada langkah sebelumnya tutorial.

Oleh kerana kita telah mengenal pasti dua medan carian dalam argumen Lookup_array kita harus melakukan perkara yang sama untuk Lookup_array . Fungsi MATCH hanya mencari satu array untuk setiap istilah yang ditentukan.

Untuk memasukkan pelbagai array kami sekali lagi menggunakan ampersand " & " untuk menggabungkan array bersama-sama.

Langkah Tutorial

Langkah-langkah ini akan dimasukkan selepas koma yang dimasukkan ke dalam langkah sebelumnya pada baris Row_num dalam kotak dialog fungsi INDEX.

1. Klik pada baris Row_num selepas koma untuk meletakkan titik sisipan pada akhir entri semasa.
2. Sorot sel-sel D6 hingga D11 dalam lembaran kerja untuk memasuki julat. Ini adalah array pertama yang berfungsi untuk mencari.
3. Taipkan ampersand " & " selepas rujukan sel D6: D11 kerana kami ingin fungsi mencari dua baris.
4. Sorot sel E6 ke E11 dalam lembaran kerja untuk memasuki julat. Ini adalah array kedua yang berfungsi untuk mencari.
5. Taipkan koma "," selepas rujukan sel E3 untuk menyelesaikan kemasukan argumen Lookup MATCH itu MATCH.
6. Biarkan kotak dialog fungsi INDEX terbuka untuk langkah seterusnya dalam tutorial.

06 dari 09

Menambah Jenis Padanan dan Menyelesaikan Fungsi MATCH

Argumen ketiga dan terakhir fungsi MATCH ialah argumen Match_type.

Hujah ini memberitahu Excel bagaimana untuk memadankan Lookup_value dengan nilai dalam Lookup_array. Pilihannya ialah: 1, 0, atau -1.

Hujah ini adalah pilihan. Jika ia dihilangkan fungsi menggunakan nilai lalai 1.

• jika Match_type = 1 atau diabaikan: MATCH menemui nilai terbesar yang kurang daripada atau sama dengan Lookup_value. Data Lookup_array mesti disusun dalam urutan menaik.
• jika match_type = 0: MATCH menemui nilai pertama yang betul-betul sama dengan Lookup_value. Data Lookup_array boleh disusun dalam sebarang pesanan.
• jika Match_type = -1: MATCH menemui nilai terkecil yang lebih besar daripada atau sama dengan Lookup_value. Data Lookup_array mesti disusun mengikut urutan menurun.

Langkah Tutorial

Langkah-langkah ini akan dimasukkan selepas koma yang dimasukkan ke dalam langkah sebelumnya pada baris Row_num dalam kotak dialog fungsi INDEX.

1. Berikutan koma di baris Row_num , taipkan sifar " 0 " kerana kami mahu fungsi bersarang untuk mengembalikan padanan tepat kepada syarat yang kami masukkan dalam sel D3 dan E3.
2. Taipkan pendakap pusingan penutup " ) " untuk melengkapkan fungsi MATCH.
3. Biarkan kotak dialog fungsi INDEX terbuka untuk langkah seterusnya dalam tutorial.

07 dari 09

Kembali ke Fungsi INDEX

Sekarang bahawa fungsi MATCH selesai, kita akan pindah ke baris ketiga kotak dialog terbuka dan masukkan hujah terakhir untuk fungsi INDEX.

Hujah ketiga dan terakhir ini adalah argumen Column_num yang memberitahu Excel nombor lajur dalam julat D6 hingga F11 di mana ia akan mencari maklumat yang kami mahu dikembalikan oleh fungsi. Dalam kes ini, pembekal untuk widget titanium .

Langkah Tutorial

1. Klik pada baris Column_num dalam kotak dialog.
2. Masukkan nombor tiga " 3 " (tiada petikan) di baris ini kerana kami sedang mencari data dalam lajur ketiga julat D6 hingga F11.
3. Jangan Klik OK atau tutup kotak dialog fungsi INDEX. Ia mesti kekal terbuka untuk langkah seterusnya dalam tutorial - mencipta formula tatasusunan .

08 dari 09

Mewujudkan Formula Array

Sebelum menutup kotak dialog, kita perlu menukar fungsi bersarang kita ke dalam formula tatasusunan .

Formula array adalah apa yang membolehkannya mencari pelbagai istilah dalam jadual data. Dalam tutorial ini, kami ingin memadankan dua syarat: Widget dari lajur 1 dan titanium dari lajur 2.

Mewujudkan formula tatasusunan dalam Excel dilakukan dengan menekan kekunci CTRL , SHIFT , dan ENTER pada papan kekunci pada masa yang sama.

Kesan menekan kekunci ini bersama-sama adalah mengelilingi fungsi dengan pendakap kerinting: {} menunjukkan bahawa ia kini merupakan formula tatasusunan.

Langkah Tutorial

1. Dengan kotak dialog selesai masih dibuka dari langkah sebelumnya tutorial ini, tekan dan tahan kekunci CTRL dan SHIFT pada papan kekunci kemudian tekan dan lepaskan kekunci ENTER .
2. Jika dilakukan dengan betul, kotak dialog akan ditutup dan ralat # N / A akan muncul dalam sel F3 - sel di mana kami memasuki fungsi.
3. Kesalahan # N / A muncul di sel F3 kerana sel-sel D3 dan E3 kosong. D3 dan E3 adalah sel-sel di mana kita memberitahu fungsi untuk mencari Lookup_values ​​dalam langkah 5 tutorial. Apabila data ditambahkan ke kedua-dua sel tersebut, ralat akan digantikan dengan maklumat dari pangkalan data .

09 dari 09

Menambah Kriteria Carian

Langkah terakhir dalam tutorial ini adalah untuk menambah istilah carian pada lembaran kerja kami.

Seperti yang dinyatakan dalam langkah sebelumnya, kami sedang mencari untuk memadankan terma Widget dari lajur 1 dan Titanium dari lajur 2.

Jika, dan hanya jika, formula kami mendapati padanan untuk kedua-dua istilah dalam lajur yang sesuai dalam pangkalan data, ia akan mengembalikan nilai dari lajur ketiga.

Langkah Tutorial

1. Klik pada sel D3.
2. Taip Widgets dan tekan kekunci Enter pada papan kekunci.
3. Klik pada sel E3.
4. Taip Titanium dan tekan kekunci Enter pada papan kekunci.
5. Nama pembekal Widgets Inc. sepatutnya muncul di sel F3 - lokasi fungsi itu kerana ia adalah satu-satunya pembekal tersenarai yang menjual Widget Titanium.
6. Apabila anda mengklik pada fungsi F3 sel yang lengkap
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   muncul di bar formula di atas lembaran kerja .

Nota: Dalam contoh kami terdapat hanya satu pembekal untuk widget titanium. Sekiranya terdapat lebih daripada satu pembekal, pembekal yang disenaraikan dahulu dalam pangkalan data dikembalikan oleh fungsi tersebut.