Cara Membuat Formula Carian Excel Dengan Pelbagai Kriteria

2024 Pengarang: Abigail Brown | [email protected]. Diubah suai terakhir: 2023-12-17 06:56

Apa yang Perlu Diketahui

• Mula-mula, buat fungsi INDEX, kemudian mulakan fungsi MATCH bersarang dengan memasukkan argumen Lookup_value.
• Seterusnya, tambahkan argumen Lookup_array diikuti dengan argumen Match_type, kemudian tentukan julat lajur.
• Kemudian, tukar fungsi bersarang menjadi formula tatasusunan dengan menekan Ctrl+ Shift+ Enter. Akhir sekali, tambahkan istilah carian pada lembaran kerja.

Artikel ini menerangkan cara membuat formula carian yang menggunakan berbilang kriteria dalam Excel untuk mencari maklumat dalam pangkalan data atau jadual data dengan menggunakan formula tatasusunan. Formula tatasusunan melibatkan sarang fungsi MATCH di dalam fungsi INDEX. Maklumat meliputi Excel untuk Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 dan Excel untuk Mac.

Ikuti Bersama Tutorial

Untuk mengikuti langkah dalam tutorial ini, masukkan data sampel ke dalam sel berikut, seperti yang ditunjukkan dalam imej di bawah. Baris 3 dan 4 dibiarkan kosong untuk menampung formula tatasusunan yang dibuat semasa tutorial ini. (Perhatikan bahawa tutorial ini tidak termasuk pemformatan yang dilihat dalam imej.)

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

Buat Fungsi INDEX dalam Excel

Fungsi INDEX ialah salah satu daripada beberapa fungsi dalam Excel yang mempunyai berbilang bentuk. Fungsi ini mempunyai Borang Tatasusunan dan Borang Rujukan. Borang Tatasusunan mengembalikan data daripada pangkalan data atau jadual data. Borang Rujukan memberikan rujukan sel atau lokasi data dalam jadual.

Dalam tutorial ini, Borang Tatasusunan digunakan untuk mencari nama pembekal bagi widget titanium, dan bukannya rujukan sel kepada pembekal ini dalam pangkalan data.

Ikuti langkah ini untuk mencipta fungsi INDEX:

1. Pilih sel F3 untuk menjadikannya sel aktif. Sel ini ialah tempat fungsi bersarang akan dimasukkan.

2. Pergi ke Formula.

   

3. Pilih Cari & Rujukan untuk membuka senarai juntai bawah fungsi.
4. Pilih INDEX untuk membuka kotak dialog Pilih Argumen.
5. Pilih array, row_num, column_num.
6. Pilih OK untuk membuka kotak dialog Function Arguments. Dalam Excel untuk Mac, Formula Builder dibuka.
7. Letakkan kursor dalam kotak teks Array.

8. Serlahkan sel D6 hingga F11 dalam lembaran kerja untuk memasukkan julat ke dalam kotak dialog.

   Biarkan kotak dialog Fungsi Argumen terbuka. Formula belum selesai. Anda akan melengkapkan formula dalam arahan di bawah.

   

Mulakan Fungsi PERLAWANAN Bersarang

Apabila menyarangkan satu fungsi di dalam yang lain, ia tidak boleh membuka pembina formula fungsi kedua, atau bersarang, untuk memasukkan argumen yang diperlukan. Fungsi bersarang mesti dimasukkan sebagai salah satu argumen fungsi pertama.

Apabila memasukkan fungsi secara manual, argumen fungsi dipisahkan antara satu sama lain dengan koma.

Langkah pertama untuk memasukkan fungsi MATCH bersarang ialah memasukkan argumen Lookup_value. Lookup_value ialah rujukan lokasi atau sel untuk istilah carian untuk dipadankan dalam pangkalan data.

Nilai_carian hanya menerima satu kriteria atau istilah carian. Untuk mencari berbilang kriteria, lanjutkan Lookup_value dengan menggabungkan, atau menggabungkan, dua atau lebih rujukan sel menggunakan simbol ampersand (&).

1. Dalam kotak dialog Function Arguments, letakkan kursor dalam kotak teks Row_num.

2. Masukkan PERLAWANAN(.
3. Pilih sel D3 untuk memasukkan rujukan sel tersebut ke dalam kotak dialog.
4. Masukkan & (ampersand) selepas rujukan sel D3 untuk menambah rujukan sel kedua.
5. Pilih sel E3 untuk memasukkan rujukan sel kedua.

6. Enter , (koma) selepas rujukan sel E3 untuk melengkapkan kemasukan argumen Lookup_value fungsi MATCH.

   

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

Lengkapkan Fungsi PERLAWANAN Bersarang

Langkah ini meliputi penambahan argumen Lookup_array untuk fungsi MATCH bersarang. Lookup_array ialah julat sel yang dicari oleh fungsi MATCH untuk mencari argumen Lookup_value yang ditambahkan dalam langkah tutorial sebelumnya.

Oleh kerana dua medan carian telah dikenal pasti dalam argumen Lookup_array, perkara yang sama mesti dilakukan untuk Lookup_array. Fungsi MATCH hanya mencari satu tatasusunan untuk setiap istilah yang ditentukan. Untuk memasukkan berbilang tatasusunan, gunakan ampersand untuk menggabungkan tatasusunan bersama-sama.

1. Letakkan kursor pada penghujung data dalam kotak teks Row_num. Kursor muncul selepas koma pada penghujung entri semasa.
2. Serlahkan sel D6 hingga D11 dalam lembaran kerja untuk memasuki julat. Julat ini ialah tatasusunan pertama yang dicari oleh fungsi.
3. Masukkan & (an ampersand) selepas rujukan sel D6:D11. Simbol ini menyebabkan fungsi mencari dua tatasusunan.
4. Serlahkan sel E6 hingga E11 dalam lembaran kerja untuk memasuki julat. Julat ini ialah tatasusunan kedua yang dicari oleh fungsi.

5. Masukkan , (koma) selepas rujukan sel E3 untuk melengkapkan kemasukan argumen Lookup_array fungsi MATCH.

   

6. Biarkan kotak dialog terbuka untuk langkah seterusnya dalam tutorial.

Tambahkan Argumen Jenis MATCH

Argumen ketiga dan terakhir bagi fungsi MATCH ialah argumen Match_type. Argumen ini memberitahu Excel cara memadankan Lookup_value dengan nilai dalam Lookup_array. Pilihan yang tersedia ialah 1, 0 atau -1.

Argumen ini adalah pilihan. Jika ia ditinggalkan, fungsi menggunakan nilai lalai 1.

• Jika Match_type=1 atau ditinggalkan, MATCH mencari nilai terbesar yang kurang daripada atau sama dengan Lookup_value. Data Lookup_array mesti diisih mengikut tertib menaik.
• Jika Match_type=0, MATCH mencari nilai pertama yang sama dengan Lookup_value. Data Lookup_array boleh diisih dalam sebarang susunan.
• Jika Match_type=-1, MATCH mencari nilai terkecil yang lebih besar daripada atau sama dengan Lookup_value. Data Lookup_array mesti diisih mengikut tertib menurun.

Masukkan langkah ini selepas koma dimasukkan dalam langkah sebelumnya pada baris Row_num dalam fungsi INDEX:

1. Masukkan 0 (sifar) selepas koma dalam kotak teks Row_num. Nombor ini menyebabkan fungsi bersarang mengembalikan padanan tepat kepada istilah yang dimasukkan dalam sel D3 dan E3.

2. Masukkan ) (kurungan bulat penutup) untuk melengkapkan fungsi MATCH.

   

3. Biarkan kotak dialog terbuka untuk langkah seterusnya dalam tutorial.

Selesaikan Fungsi INDEX

Fungsi MATCH selesai. Tiba masanya untuk beralih ke kotak teks Column_num kotak dialog dan masukkan hujah terakhir untuk fungsi INDEX. Argumen ini memberitahu Excel nombor lajur berada dalam julat D6 hingga F11. Julat ini adalah tempat ia mencari maklumat yang dikembalikan oleh fungsi. Dalam kes ini, pembekal untuk widget titanium.

1. Letakkan kursor dalam kotak teks Column_num.

2. Masukkan 3 (nombor tiga). Nombor ini memberitahu formula untuk mencari data dalam lajur ketiga julat D6 hingga F11.

   

3. Biarkan kotak dialog terbuka untuk langkah seterusnya dalam tutorial.

Buat Formula Tatasusunan

Sebelum menutup kotak dialog, tukar fungsi bersarang menjadi formula tatasusunan. Tatasusunan ini membolehkan fungsi mencari berbilang istilah dalam jadual data. Dalam tutorial ini, dua istilah dipadankan: Widget dari lajur 1 dan Titanium dari lajur 2.

Untuk mencipta formula tatasusunan dalam Excel, tekan CTRL, SHIFT dan ENTERkekunci serentak. Setelah ditekan, fungsi itu dikelilingi oleh pendakap kerinting, menunjukkan bahawa fungsi itu kini merupakan tatasusunan.

1. Pilih OK untuk menutup kotak dialog. Dalam Excel untuk Mac, pilih Selesai.
2. Pilih sel F3 untuk melihat formula, kemudian letakkan kursor pada penghujung formula dalam Formula Bar.
3. Untuk menukar formula kepada tatasusunan, tekan CTRL+ SHIFT+ ENTER.
4. A N/A ralat muncul dalam sel F3. Ini ialah sel tempat fungsi itu dimasukkan.

5. Ralat N/A muncul dalam sel F3 kerana sel D3 dan E3 kosong. D3 dan E3 ialah sel di mana fungsi mencari untuk mencari nilai_carian. Selepas data ditambahkan pada kedua-dua sel ini, ralat digantikan dengan maklumat daripada pangkalan data.

   

Tambahkan Kriteria Carian

Langkah terakhir ialah menambah istilah carian pada lembaran kerja. Langkah ini sepadan dengan istilah Widget dari lajur 1 dan Titanium dari lajur 2.

Jika formula menemui padanan untuk kedua-dua istilah dalam lajur yang sesuai dalam pangkalan data, ia mengembalikan nilai daripada lajur ketiga.

1. Pilih sel D3.
2. Masukkan Widget.
3. Pilih sel E3.
4. Taip Titanium, dan tekan Enter.
5. Nama pembekal, Widgets Inc., muncul dalam sel F3. Ini adalah satu-satunya pembekal tersenarai yang menjual Widget Titanium.

6. Pilih sel F3. Fungsi ini muncul dalam bar formula di atas lembaran kerja.

   {=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
   Dalam contoh ini, hanya terdapat satu pembekal untuk widget titanium. Jika terdapat lebih daripada satu pembekal, pembekal yang disenaraikan dahulu dalam pangkalan data dikembalikan oleh fungsi tersebut.