Dengan menggunakan rumus array di Excel kita dapat membuat rumus pencarian yang menggunakan beberapa kriteria untuk menemukan informasi dalam database atau tabel data. Rumus larik melibatkan penumpukan fungsi MATCH di dalam fungsi INDEX.
Tutorial ini mencakup contoh langkah demi langkah pembuatan rumus pencarian yang menggunakan beberapa kriteria untuk menemukan pemasokWidget titanium dalam database sampel. Ikuti terus untuk mempelajari betapa mudahnya membuat rumus pencarian khusus Anda sendiri.
Siapkan Buku Kerja Excel Anda dengan Data
Untuk mengikuti langkah-langkah dalam tutorial ini, Anda harus mulai dengan memasukkan data sampel ke sel-sel berikut, seperti yang ditunjukkan pada gambar di atas. Baris 3 dan 4 dibiarkan kosong untuk mengakomodasi rumus larik yang dibuat selama tutorial ini.
- Masukkan rentang data teratas ke dalam sel D1 untuk F2
- Masukkan rentang kedua ke dalam sel D5 untuk F11
Tutorial tidak menyertakan pemformatan yang terlihat pada gambar, tetapi ini tidak akan memengaruhi cara kerja rumus pencarian. Informasi tentang opsi pemformatan yang serupa dengan yang terlihat di atas tersedia dalam Tutorial Memformat Excel Dasar ini.
02 08Buat Fungsi INDEX di Excel
Itu INDEKS fungsi adalah salah satu dari sedikit di Excel yang memiliki beberapa bentuk. Fungsi ini memiliki Formulir Array dan a Formulir Referensi. Formulir Array mengembalikan data aktual dari database atau tabel data, sementara Formulir Referensi memberi Anda referensi sel atau lokasi data dalam tabel.
Dalam tutorial ini, kita akan menggunakan Formulir Array karena kita ingin tahu nama pemasok untuk widget titanium daripada referensi sel ke pemasok ini di database kami. Ikuti langkah-langkah ini untuk membuat Fungsi INDEX Anda:
- Klik pada sel F3 untuk menjadikannya sel aktif - disinilah kita akan memasuki fungsi bertingkat.
- Klik pada Rumus tab dari menu pita.
- Memilih Lookup dan Referensi dari bilah pita untuk membuka fungsi tarik turun.
- Klik INDEKS dalam daftar untuk memunculkan Pilih Argumen kotak dialog.
- Memilih larik, row_num, col_num opsi di kotak dialog.
- Klik baik untuk membuka kotak dialog fungsi INDEX; ini akan membuka Formula Builder di Excel.
- Dalam Pembuat Rumus, klik pada Array baris.
- Sel sorotan D6 untuk F11, dengan mengklik dan menarik, di lembar kerja untuk memasukkan rentang ke dalam pembuat.
Dalam versi Excel yang lebih lama Pembuat Rumus akan digantikan oleh Argumen Fungsi jendela. Terapkan langkah yang sama seperti yang disebutkan dalam tutorial ini ke jendela.
03 dari 08Mulai Fungsi MATCH Bersarang
Ketika bersarang satu fungsi di dalam yang lain tidak mungkin untuk membuka pembangun rumus fungsi kedua atau bertingkat o masukkan argumen yang diperlukan. Fungsi bertingkat harus diketik sebagai salah satu yang diurutkan dalam fungsi pertama.
Saat memasukkan fungsi secara manual, argumen fungsi dipisahkan satu sama lain oleh koma.
Langkah pertama dalam memasuki fungsi MATCH bersarang adalah memasukkan Nilai lookup argumen. Lookup_value adalah lokasi atau referensi sel untuk istilah pencarian yang ingin kami tandingkan dalam basis data.
Biasanya, Lookup_value hanya menerima satu kriteria atau istilah pencarian. Untuk mencari beberapa kriteria, kita harus memperluas Lookup_value; tini dilakukan dengan menggabungkan atau menggabungkan dua atau lebih referensi sel bersama-sama menggunakan simbol ampersand - &.
- Dalam Pembuat Rumus, klik pada Row_num baris.
- Ketik nama fungsi PERTANDINGAN diikuti oleh braket bulat terbuka.
- Klik pada sel D3 untuk memasukkan referensi sel ke dalam kotak dialog.
- Ketik ampersand setelah referensi sel D3 untuk menambahkan referensi sel kedua.
- Klik pada sel E3 untuk memasukkan referensi sel kedua ini.
- Ketik koma setelah referensi sel E3 untuk menyelesaikan entri fungsi MATCH Nilai lookup argumen.
Pada langkah terakhir dari tutorial, kami Lookup_values akan dimasukkan ke dalam sel D3 dan E3 dari lembar kerja.
04 dari 08Lengkapi Fungsi MATCH Bersarang
Langkah ini mencakup penambahan Lookup_array argumen untuk bersarang PERTANDINGAN fungsi. Lookup_array adalah rentang sel yang fungsi MATCH akan mencari untuk menemukan argumen Lookup_value yang ditambahkan pada langkah sebelumnya dari tutorial.
Karena kami telah mengidentifikasi dua bidang pencarian dalam argumen Lookup_array, kami harus melakukan hal yang sama untuk Lookup_array. Fungsi MATCH hanya mencari satu larik untuk setiap istilah yang ditentukan, sehingga untuk memasukkan beberapa larik, kami kembali menggunakan ampersand untuk menyatukan array bersama.
Langkah-langkah ini harus dimasukkan setelah koma dimasukkan pada langkah sebelumnya pada Row_num baris dalam INDEKS fungsi.
- Klik pada Row_num baris setelah koma untuk menempatkan titik penyisipan pada akhir entri saat ini.
- Sel sorotan D6 untuk D11 di lembar kerja untuk memasukkan rentang - ini adalah larik pertama yang berfungsi untuk mencari.
- Ketik ampersand setelah referensi sel D6: D11 karena kami ingin fungsi untuk mencari dua larik.
- Sel sorotan E6 untuk E11 di lembar kerja untuk memasukkan rentang - ini adalah larik kedua yang berfungsi untuk mencari.
- Ketik koma setelah referensi sel E3 untuk menyelesaikan entri PERTANDINGAN fungsi ini Lookup_array argumen.
- Tinggalkan INDEKS kotak dialog fungsi terbuka untuk langkah berikutnya dalam tutorial.
Tambahkan Argumen MATCH Type
Argumen ketiga dan terakhir dari fungsi MATCH adalah Match_type argumen; itu memberitahu Excel cara mencocokkan Nilai lookup dengan nilai di Lookup_array - pilihan yang tersedia adalah 1, 0, atau -1.
Argumen ini opsional. Jika dihilangkan, fungsi menggunakan nilai default 1.
- Jika itu Match_type = 1 atau dihilangkan: MATCH menemukan nilai terbesar yang kurang dari atau sama dengan Lookup_value. Data Lookup_array harus diurutkan dalam urutan menaik.
- Jika itu Match_type = 0: MATCH menemukan nilai pertama yang persis sama dengan Lookup_value. Data Lookup_array dapat disortir dalam urutan apa pun.
- Jika itu Match_type = -1: MATCH menemukan nilai terkecil yang lebih besar atau sama dengan Lookup_value. Data Lookup_array harus disortir dalam urutan menurun.
Langkah-langkah ini harus dimasukkan setelah koma dimasukkan pada langkah sebelumnya pada Row_num baris dalam INDEKS fungsi.
- Mengikuti koma pada Row_num garis, ketik nol - 0 - karena kami ingin fungsi yang disarangkan untuk mengembalikan pencocokan tepat ke istilah yang kami masukkan dalam sel D3 dan E3.
- Ketikkan kurung tutup putaran - ) - untuk menyelesaikan PERTANDINGAN fungsi.
- Tinggalkan Pembuat Rumus terbuka untuk langkah selanjutnya dalam tutorial.
Menyelesaikan Fungsi INDEX
Sekarang itu PERTANDINGAN fungsi selesai kita akan pindah ke baris ketiga dari Builder Rumus dan masukkan argumen terakhir untuk INDEKS fungsi. Argumen ketiga dan terakhir ini adalah Column_num argumen yang memberi tahu Excel nomor kolom dalam rentang D6 untuk F11; disinilah ia akan menemukan informasi yang kita inginkan untuk dikembalikan oleh fungsi. Dalam hal ini, pemasok untuk widget titanium.
- Klik pada Column_num baris.
- Masukkan nomor tiga - 3 - pada baris ini karena kami mencari data di kolom ketiga rentang D6 untuk F11.
- Sekali lagi, tinggalkan Pembuat Rumus terbuka untuk langkah selanjutnya dalam tutorial.
Membuat Rumus Array
Sebelum menutup Pembuat Rumus, kita perlu mengubah fungsi nested kita menjadi rumus array; inilah yang memungkinkan fungsi kami untuk mencari beberapa istilah dalam tabel data. Dalam tutorial ini, kami ingin mencocokkan dua istilah: Widget dari kolom 1 dan Titanium dari kolom 2.
Membuat rumus array di Excel dilakukan dengan menekan tombol CTRL, BERGESER, dan MEMASUKKAN kunci pada keyboard pada saat bersamaan. Setelah ditekan, fungsi akan dikelilingi oleh kurung kurawal, yang menunjukkan bahwa fungsi tersebut sekarang menjadi array.
- Tutup Pembuat Rumus dengan mengklik Selesai tombol.
- Selanjutnya, pilih sel F4 dan tekan tombol Memasukkan kunci untuk melihat rumus.
- Untuk mengkonversi Formula ke array, tekan secara bersamaan CTRL + SHIFT + ENTER di keyboard Anda.
- Jika dilakukan dengan benar, a # N / A kesalahan akan muncul di sel F3 - sel tempat kita memasuki fungsi.
- Itu # N / A kesalahan muncul di sel F3 karena sel D3 dan E3 kosong. D3 dan E3 adalah sel tempat kami memberi tahu fungsi untuk menemukan Lookup_values. Setelah data ditambahkan ke dua sel ini, kesalahan akan digantikan oleh informasi dari database.
Menambahkan Kriteria Pencarian
Akhirnya, kami akan menambahkan istilah pencarian ke lembar kerja kami. Seperti yang disebutkan pada langkah sebelumnya, kami ingin mencocokkan ketentuan Widget dari kolom 1 dan Titanium dari kolom 2. Jika, dan hanya jika, rumus kami menemukan kecocokan untuk kedua istilah dalam kolom yang sesuai dalam basis data, akan mengembalikan nilai dari kolom ketiga.
- Klik pada sel D3.
- Mengetik Widget dan tekan tombol Memasukkan kunci pada keyboard.
- Klik pada sel E3.
- Mengetik Titanium dan tekan tombol Memasukkan kunci pada keyboard.
- Nama pemasok Widgets Inc. akan muncul di sel F3 - lokasi fungsi karena merupakan satu-satunya pemasok yang terdaftar yang menjual Titanium Widgets.
- Ketika Anda mengklik pada sel F3, fungsi lengkap muncul di bilah rumus di atas lembar kerja.
{= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
Dalam contoh kami, hanya ada satu pemasok untuk widget titanium. Jika ada lebih dari satu pemasok, pemasok yang tercantum pertama dalam database dikembalikan oleh fungsi.
