Dengan menggabungkan ExcelVLOOKUP berfungsi denganKOLOM berfungsi kita dapat membuat rumus pencarian yang memungkinkan Anda untuk mengembalikan beberapa nilai dari satu baris database atau tabel data.
01 dari 10Return Multiple Values dengan Excel VLOOKUP
Mengikuti langkah-langkah yang tercantum di bawah ini membuat rumus pencarian yang terlihat pada gambar di atas yang akan mengembalikan beberapa nilai dari satu rekaman data.
Rumus pencarian mensyaratkan bahwa KOLOM fungsi bersarang di dalam VLOOKUP. Bersarang fungsi melibatkan memasukkan fungsi kedua sebagai salah satu argumen untuk fungsi pertama.
Dalam tutorial ini, KOLOM fungsi akan dimasukkan sebagai indeks kolom jumlah argumen untuk VLOOKUP. Langkah terakhir dalam tutorial melibatkan menyalin rumus pencarian ke kolom tambahan untuk mengambil nilai tambahan untuk bagian yang dipilih.
Masukkan Data Tutorial
Langkah pertama dalam tutorial adalah memasukkan data ke dalam lembar kerja Excel. Untuk mengikuti langkah-langkah dalam tutorial, masukkan data yang ditunjukkan pada gambar di atas ke dalam sel berikut:
- Masukkan kisaran data teratas ke dalam sel D1 untuk G1.
- Masukkan rentang kedua ke dalam sel D4 untuk G10.
Kriteria pencarian dan formula pencarian yang dibuat selama tutorial ini akan dimasukkan baris 2 dari lembar kerja.
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.
03 dari 10Membuat Rentang Bernama untuk Tabel Data
Rentang bernama adalah cara mudah untuk merujuk ke berbagai data dalam rumus. Daripada mengetikkan referensi sel untuk data, Anda cukup mengetikkan nama rentang.
Keuntungan kedua untuk menggunakan rentang bernama adalah bahwa referensi sel untuk rentang ini tidak pernah berubah bahkan ketika formula disalin ke sel lain di lembar kerja. Karena itu, nama rentang merupakan alternatif untuk menggunakan referensi sel absolut untuk mencegah kesalahan saat menyalin rumus.
Nama rentang tidak termasuk judul atau nama kolom untuk data (baris 4) tetapi hanya data itu sendiri.
- Menyoroti sel D5 untuk G10 di lembar kerja untuk memilihnya.
- Klik pada Kotak Nama terletak di atas kolom A.
- Mengetik Meja ke dalam Kotak Nama.
- tekan Memasukkan kunci pada keyboard.
- Sel D5 untuk G10 sekarang memiliki nama rentang Meja. Kami akan menggunakan nama untuk VLOOKUP tabel array argumen nanti di tutorial.
Membuka Kotak Dialog VLOOKUP
Meskipun dimungkinkan untuk hanya mengetik rumus pencarian kami langsung ke sel di lembar kerja, banyak orang merasa sulit untuk menjaga sintaks lurus - terutama untuk rumus kompleks seperti yang kami gunakan dalam tutorial ini.
Alternatif, dalam hal ini, adalah menggunakan VLOOKUP kotak dialog. Hampir semua fungsi Excel memiliki kotak dialog yang memungkinkan Anda untuk memasukkan masing-masing argumen fungsi pada baris terpisah.
Langkah-langkah Tutorial
- Klik sel E2 dari lembar kerja - lokasi di mana hasil dari rumus pencarian dua dimensi akan ditampilkan.
- Klik pada Tab Rumus dari pita.
- Klik pada Lookup & Referensi opsi dalam pita untuk membuka fungsi drop-down.
- Klik VLOOKUP dalam daftar untuk membuka kotak dialog fungsi.
Memasukkan Argumen Nilai Pencarian
Biasanya, itu menengadah nilai cocok dengan bidang data di kolom pertama dari tabel data. Dalam contoh kami, menengadah nilai mengacu pada nama bagian perangkat keras yang ingin kita cari informasinya. Jenis data yang diizinkan untuk menengadah nilai adalah data teks, nilai logika, angka, dan referensi sel.
Referensi Sel Absolut
Biasanya, ketika formula disalin di Excel, referensi sel berubah untuk mencerminkan lokasi baru mereka. Jika ini terjadi, D2, referensi sel untuk menengadah nilai, akan berubah saat rumus disalin membuat kesalahan di sel F2 dan G2.
Referensi sel absolut tidak berubah ketika formula disalin.
Untuk mencegah kesalahan, kami akan mengubah referensi sel D2 menjadi referensi sel absolut. Referensi sel absolut dibuat dengan menekan F4 kunci pada keyboard. Melakukan hal itu menambahkan tanda dolar di sekitar referensi sel seperti $ D $ 2.
- Klik pada nilai lookup baris di kotak dialog.
- Klik sel D2 untuk menambahkan referensi sel ini ke nilai lookup line - ini adalah sel di mana kita akan mengetikkan nama bagian tentang mana kita mencari informasi
- Tanpa memindahkan titik penyisipan, tekan F4 kunci pada keyboard untuk mengkonversi D2 ke referensi sel absolut $ D $ 2.
- Tinggalkan VLOOKUP kotak dialog fungsi terbuka untuk langkah berikutnya dalam tutorial.
Memasukkan Argumen Larik Tabel
Array tabel adalah tabel data yang dicari pencarian rumus untuk menemukan informasi yang kita inginkan.Array tabel harus berisi setidaknya dua kolom data.
Kolom pertama berisi argumen nilai pencarian (langkah sebelumnya dalam tutorial), sedangkan kolom kedua akan dicari oleh rumus pencarian untuk menemukan informasi yang kami tentukan.
Argumen larik tabel harus dimasukkan sebagai rentang yang berisi referensi sel untuk tabel data atau sebagai nama rentang.
- Klik pada table_array baris di kotak dialog.
- Mengetik Meja untuk memasukkan nama rentang untuk argumen ini.
- Tinggalkan VLOOKUP kotak dialog fungsi terbuka untuk langkah berikutnya dalam tutorial.
Bersarang Fungsi KOLOM
Biasanya VLOOKUP hanya mengembalikan data dari satu kolom tabel data dan kolom ini diatur oleh nomor indeks kolom argumen.
Dalam contoh ini, kami memiliki tiga kolom yang ingin kami kembalikan datanya sehingga kami perlu cara mudah mengubah nomor indeks kolom tanpa mengedit formula pencarian kami - ini adalah tempat KOLOM fungsi datang.
Dengan memasukkannya sebagai nomor indeks kolom argumen, itu akan berubah ketika formula pencarian disalin dari sel D2 untuk sel E2 dan F2 nanti di tutorial.
Fungsi Bersarang
Itu KOLOM berfungsi, oleh karena itu, bertindak sebagai VLOOKUP's nomor indeks kolom argumen; ini dilakukan dengan bersarang KOLOM fungsi dalam VLOOKUP dalam Col_index_num garis kotak dialog.
Memasukkan Fungsi KOLOM secara Manual
Ketika fungsi bertumpuk, Excel tidak mengizinkan kita untuk membuka kotak dialog fungsi kedua untuk memasukkan argumennya. Itu KOLOM fungsi, oleh karena itu, harus dimasukkan secara manual di Col_index_num baris. Itu KOLOM fungsi hanya memiliki satu argumen - Referensi argumen yang merupakan referensi sel.
Memilih Argumen Referensi Fungsi KOLOM
Itu KOLOM tugas fungsi adalah mengembalikan jumlah kolom yang diberikan sebagai Referensi argumen. Dengan kata lain, ia mengubah huruf kolom menjadi angka dengan kolom A menjadi kolom pertama, kolom B yang kedua dan seterusnya.
Karena bidang data pertama, yang kita inginkan adalah harga barang, yang ada di kolom dua tabel data. Kita dapat memilih referensi sel untuk setiap sel di kolom B sebagai Referensi Argumen untuk mendapatkan nomornya 2 Untuk Col_index_num argumen.
- Dalam VLOOKUP kotak dialog fungsi, klik pada Col_index_num baris.
- Ketik nama fungsi kolom diikuti oleh buka kurung bulat.
- Klik sel B1 di lembar kerja untuk memasukkan referensi sel itu sebagai Referensi argumen.
- Ketik a menutup kurung bulat untuk menyelesaikan KOLOM fungsi
- Tinggalkan VLOOKUP kotak dialog fungsi terbuka untuk langkah berikutnya dalam tutorial.
Memasukkan Argumen Pencarian Rentang VLOOKUP
VLOOKUP's Range_lookup argumen adalah nilai logis (BENAR atau SALAH) yang menunjukkan apakah Anda mau VLOOKUP untuk menemukan pencocokan tepat atau perkiraan untuk Nilai lookup.
- Jika BENAR atau jika argumen ini dihilangkan: VLOOKUP menghasilkan pencocokan tepat untuk Nilai lookupAtau, jika kecocokan yang tepat tidak ditemukan, VLOOKUP mengembalikan nilai terbesar berikutnya. Untuk rumus untuk melakukan ini, data di kolom pertama Table_array harus diurutkan dalam urutan menaik.
- Jika SALAH: VLOOKUP hanya akan menggunakan pencocokan tepat untuk Nilai lookup. Jika ada dua atau lebih nilai di kolom pertama Table_array yang cocok dengan nilai pencarian, nilai pertama yang ditemukan digunakan. Jika tidak ditemukan yang sama persis, a # N / A kesalahan dikembalikan.
Dalam tutorial ini, karena kami mencari informasi spesifik tentang item perangkat keras tertentu, kami akan mengaturnya Range_lookup sama dengan Salah.
- Klik pada Range_lookup baris di kotak dialog.
- Ketik kata Salah di baris ini untuk menunjukkan yang kita inginkan VLOOKUP untuk mengembalikan kecocokan tepat untuk data yang kami cari.
- Klik baik untuk melengkapi formula pencarian dan menutup kotak dialog.
- Karena kami belum memasukkan kriteria pencarian ke dalam sel D2 sebuah Kesalahan # N / A akan hadir di sel E2.
- Kesalahan ini akan diperbaiki ketika kami akan menambahkan kriteria pencarian di langkah terakhir dari tutorial.
Menyalin Rumus Pencarian
Rumus pencarian dimaksudkan untuk mengambil data dari beberapa kolom dari tabel data pada satu waktu. Untuk melakukan ini, rumus pencarian harus berada di semua bidang dari mana kita menginginkan informasi.
Dalam tutorial ini kami ingin mengambil data dari kolom 2, 3, dan 4 dari tabel data - yaitu harga, nomor bagian, dan nama pemasok ketika kita memasukkan nama sebagian sebagai Nilai lookup.
Karena data diletakkan dalam pola biasa di lembar kerja, kita dapat menyalin rumus pencarian di sel E2 untuk sel F2 dan G2.
Saat formula disalin, Excel akan memperbarui referensi sel relatif di KOLOM fungsi (sel B1) untuk mencerminkan lokasi baru formula.
Selain itu, Excel tidak mengubah referensi sel absolut $ D $ 2 dan rentang bernama Meja sebagai formula yang disalin.
Ada lebih dari satu cara untuk menyalin data di Excel, tetapi mungkin cara termudah adalah dengan menggunakan Isi Menangani.
- Klik sel E2, di mana rumus pencarian berada, untuk menjadikannya sel aktif.
- Tempatkan penunjuk mouse di atas kotak hitam di sudut kanan bawah - penunjuk akan berubah menjadi tanda tambah untuk menunjukkan pegangan isian.
- Klik tombol kiri mouse dan seret gagang isian menyeberang ke sel G2.
- Melepaskan tombol mouse dan sel F3 harus mengandung rumus pencarian dua dimensi.
- Jika dilakukan dengan benar, sel F2 dan G2 seharusnya sekarang juga mengandung # N / A kesalahan yang hadir di sel E2.
Memasukkan Kriteria Pencarian
Setelah formula pencarian telah disalin ke sel yang diperlukan, ia dapat digunakan untuk mengambil informasi dari tabel data. Untuk melakukannya, ketik nama barang Anda ingin mengambil ke dalam Nilai lookup sel (D2) dan tekan Memasukkan kunci pada keyboard.
Setelah selesai, setiap sel yang berisi rumus pencarian harus berisi bagian data yang berbeda tentang item perangkat keras yang Anda cari.
- Klik sel D2 di lembar kerja.
- Mengetik Widget ke sel D2 dan tekan tombol Memasukkan kunci pada keyboard.
- Informasi berikut ini harus ditampilkan sel E2 untuk G2.
- E2: $ 14,76 - harga sebuah widget
- F2: PN-98769 - nomor bagian untuk widget
- G2: Widgets Inc. - nama pemasok untuk widget
- Uji coba VLOOKUP formula array lebih lanjut dengan mengetikkan nama bagian lain ke dalam sel D2 dan mengamati hasilnya sel E2 untuk G2
