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 pelbagai kriteria untuk mencari pembekalWidget titanium dalam pangkalan data sampel. Ikuti bersama-sama untuk mengetahui betapa mudahnya untuk membuat formula carian tersuai anda sendiri.
Sediakan Buku Kerja Excel Anda dengan Data
Untuk mengikuti langkah-langkah dalam tutorial ini, anda perlu bermula dengan memasukkan data sampel ke dalam sel berikut, seperti yang ditunjukkan dalam imej di atas. Baris 3 dan 4 dibiarkan kosong untuk menampung formula array yang dibuat semasa tutorial ini.
- Masukkan julat data teratas ke dalam sel D1 kepada F2
- Masukkan julat kedua ke dalam sel D5 kepada F11
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 08Buat Fungsi INDEX dalam Excel
The INDEX fungsi adalah salah satu daripada beberapa dalam Excel yang mempunyai pelbagai bentuk. Fungsi ini mempunyai Borang Array dan a 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. Ikuti langkah-langkah ini untuk mencipta Fungsi INDEX anda:
- Klik pada sel F3 untuk menjadikannya sel aktif - ini adalah di mana kita akan memasuki fungsi bersarang.
- Klik pada Formula tab menu reben.
- Pilih Lookup dan Rujukan dari bar pita untuk membuka fungsi drop down.
- Klik pada INDEX dalam senarai untuk memaparkan Pilih Argumen kotak dialog.
- Pilih array, row_num, col_num pilihan dalam kotak dialog.
- Klik okey untuk membuka kotak dialog fungsi INDEX; ini akan membuka Formula Builder dalam Excel.
- Di dalam Pembina Formula, Klik pada Array talian.
- Serlahkan sel-sel D6 kepada F11, dengan mengklik dan menyeret, dalam lembaran kerja untuk memasuki julat ke pembina.
Dalam versi lama Excel Pembina Formula akan digantikan dengan Argumen Fungsi tingkap. Gunakan langkah yang sama seperti yang dinyatakan dalam tutorial ini ke dalam tetingkap.
03 dari 08Mulakan Fungsi MATCH Nested
Apabila bersarang satu fungsi di dalam yang lain, tidak mungkin untuk membuka pembina formula fungsi kedua atau bersarang atau masukkan argumen yang diperlukan. Fungsi bersarang mesti ditaip sebagai salah satu yang disusun dalam fungsi pertama.
Apabila memasukkan fungsi secara manual, hujah fungsi dipisahkan dari satu sama lain dengan koma.
Langkah pertama dalam memasukkan fungsi MATCH bersarang ialah memasuki Lookup_value hujah. 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; tbeliau dilakukan dengan menggabungkan atau menyertai dua atau lebih rujukan sel bersama menggunakan simbol ampersand - &.
- Di dalam Pembina Formula, Klik pada Row_num talian.
- Taipkan nama fungsi MATCH diikuti oleh pendakap pusingan terbuka.
- Klik pada sel D3 untuk memasukkan rujukan sel ke dalam kotak dialog.
- Taipkan ampersand selepas rujukan sel D3 untuk menambah rujukan sel kedua.
- Klik pada sel E3 untuk memasukkan rujukan sel kedua ini.
- Taipkan koma selepas rujukan sel E3 untuk melengkapkan kemasukan fungsi MATCH Lookup_value hujah.
Dalam langkah terakhir tutorial, kami Lookup_values akan dimasukkan ke dalam sel D3 dan E3 lembaran kerja.
04 dari 08Lengkapkan Fungsi MATCH Nested
Langkah ini meliputi menambah Lookup_array hujah untuk bersarang MATCH fungsi. 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 dinyatakan, oleh itu untuk memasukkan beberapa array kita sekali lagi menggunakan ampersand untuk menggabungkan array bersama-sama.
Langkah-langkah ini akan dimasukkan selepas koma yang dimasukkan dalam langkah sebelumnya pada Row_num talian di INDEX fungsi.
- Klik pada Row_num baris selepas koma untuk meletakkan titik kemasukan pada akhir kemasukan semasa.
- Serlahkan sel-sel D6 kepada D11 dalam lembaran kerja untuk memasuki julat - ini adalah array pertama yang berfungsi untuk mencari.
- Taipkan ampersand selepas rujukan sel D6: D11 kerana kita mahukan fungsi untuk mencari dua baris.
- Serlahkan sel-sel E6 kepada E11 dalam lembaran kerja untuk memasuki julat - ini adalah array kedua yang berfungsi untuk mencari.
- Taipkan koma selepas rujukan sel E3 untuk melengkapkan kemasukan MATCH fungsi Lookup_array hujah.
- Tinggalkan INDEX kotak dialog fungsi terbuka untuk langkah seterusnya dalam tutorial.
Tambah Argumen Jenis MATCH
Argumen ketiga dan terakhir fungsi MATCH ialah Match_type hujah; ia memberitahu Excel bagaimana untuk memadankannya Lookup_value dengan nilai dalam Lookup_array - pilihan yang tersedia ialah 1, 0, atau -1.
Hujah ini adalah pilihan. Jika ia dihilangkan fungsi menggunakan nilai lalai 1.
- Sekiranya 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.
- Sekiranya Match_type = 0: MATCH menemui nilai pertama yang betul-betul sama dengan Lookup_value. Data Lookup_array boleh disusun dalam sebarang pesanan.
- Sekiranya 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-langkah ini akan dimasukkan selepas koma yang dimasukkan dalam langkah sebelumnya pada Row_num talian di INDEX fungsi.
- Berikutan koma pada Row_num baris, taipkan sifar - 0 - kerana kami mahukan fungsi bersarang untuk mengembalikan padanan tepat kepada syarat yang kami masukkan dalam sel D3 dan E3.
- Taip kurungan bulat penutup - ) - untuk melengkapkan MATCH fungsi.
- Tinggalkan Pembina Formula buka untuk langkah seterusnya dalam tutorial.
Menamatkan Fungsi INDEX
Sekarang bahawa MATCH fungsi dilakukan, kami akan berpindah ke baris ketiga Formula Builder dan masukkan hujah terakhir untuk INDEX fungsi. Hujah ketiga dan terakhir ini ialah Column_num hujah yang memberitahu Excel nombor lajur dalam julat D6 kepada F11; ini adalah di mana ia akan mencari maklumat yang kami ingin dikembalikan oleh fungsi itu. Dalam kes ini, pembekal untuk widget titanium.
- Klik pada Column_num talian.
- Masukkan nombor tiga - 3 - pada baris ini kerana kami mencari data dalam lajur ketiga julat D6 kepada F11.
- Sekali lagi, biarkan Pembina Formula buka untuk langkah seterusnya dalam tutorial.
Mewujudkan Formula Array
Sebelum menutup Pembina Formula, kita perlu mengubah fungsi bersarang kita menjadi formula tatasusunan; ini adalah apa yang membolehkan fungsi kami mencari pelbagai istilah dalam jadual data. Dalam tutorial ini, kami mencari untuk memadankan dua syarat: Widget dari lajur 1 dan Titanium dari ruangan 2.
Mewujudkan formula tatasusunan dalam Excel dilakukan dengan menekan CTRL, SHIFT, dan ENTER kekunci pada papan kekunci pada masa yang sama. Apabila ditekan, fungsi tersebut akan dikelilingi oleh pendakap kerinting, yang menunjukkan bahawa fungsi kini menjadi array.
- Menutup Pembina Formula dengan mengklik Selesai butang.
- Selanjutnya, pilih sel F4 dan tekan Masukkan kunci untuk melihat formula.
- Untuk menukar Formula ke array, serentak tekan CTRL + SHIFT + ENTER pada papan kekunci anda.
- Sekiranya dilakukan dengan betul a # N / A ralat akan muncul dalam sel F3 - sel di mana kita memasuki fungsi.
- The # N / A ralat muncul di dalam sel F3 kerana sel-sel D3 dan E3 kosong. D3 dan E3 adalah sel-sel di mana kita memberitahu fungsi untuk mencari Lookup_values. Apabila data ditambahkan ke kedua-dua sel tersebut, ralat akan digantikan dengan maklumat dari pangkalan data.
Menambah Kriteria Carian
Akhirnya, kami akan menambah istilah carian pada lembaran kerja kami. Seperti yang dinyatakan dalam langkah terdahulu, kami ingin memadankan terma tersebut Widget dari lajur 1 dan Titanium dari lajur 2. Jika, dan hanya jika, formula kami mendapati perlawanan untuk kedua-dua istilah dalam lajur yang sesuai dalam pangkalan data, ia akan mengembalikan nilai dari lajur ketiga.
- Klik pada sel D3.
- Taipkan Widget dan tekan Masukkan kekunci pada papan kekunci.
- Klik pada sel E3.
- Taipkan Titanium dan tekan Masukkan kekunci pada papan kekunci.
- Nama pembekal Widget Inc. sepatutnya muncul dalam sel F3 - lokasi fungsi kerana ia adalah satu-satunya pembekal yang disenaraikan yang menjual Widget Titanium.
- Apabila anda mengklik pada sel F3, fungsi lengkap muncul di bar formula di atas lembaran kerja.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
Dalam contoh kami, hanya ada satu pembekal untuk widget titanium. Sekiranya terdapat lebih daripada satu pembekal, pembekal yang disenaraikan dahulu dalam pangkalan data dikembalikan oleh fungsi tersebut.
