01 03
Temukan Data ke Kiri
Tinjauan Rumus Tampilan Kiri Excel
Fungsi VLOOKUP Excel digunakan untuk mencari dan mengembalikan informasi dari tabel data berdasarkan nilai pencarian yang Anda pilih.
Biasanya, VLOOKUP membutuhkan nilai pencarian berada di kolom paling kiri dari tabel data, dan fungsi mengembalikan bidang data lain yang terletak di baris yang sama di sebelah kanan nilai ini.
Dengan menggabungkan VLOOKUP dengan fungsi CHOOSE ; namun, formula pencarian kiri dapat dibuat yang akan:
- memungkinkan nilai pencarian untuk dipilih dari kolom mana saja dalam tabel data
- Informasi pengembalian berada di kolom mana pun di sebelah kiri nilai pencarian
Contoh: Menggunakan Fungsi VLOOKUP dan PILIHAN dalam Formula Pencarian Kiri
Langkah-langkah yang dijelaskan di bawah ini membuat rumus pencarian kiri terlihat pada gambar di atas.
Rumusnya
= VLOOKUP ($ D $ 2, MEMILIH ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)memungkinkan untuk menemukan bagian yang disediakan oleh perusahaan yang berbeda yang tercantum dalam kolom 3 dari tabel data.
Tugas fungsi MEMILIH dalam rumus adalah untuk mengelabui VLOOKUP agar percaya bahwa kolom 3 sebenarnya kolom 1. Akibatnya, nama Perusahaan dapat digunakan sebagai nilai pencarian untuk menemukan nama bagian yang disediakan oleh masing-masing perusahaan.
Langkah Tutorial - Memasukkan Data Tutorial
- Masukkan judul berikut ke dalam sel yang ditunjukkan: D1 - Pemasok E1 - Bagian
- Masukkan tabel data yang terlihat pada gambar di atas ke dalam sel D4 ke F9
- Baris 2 dan 3 dibiarkan kosong untuk mengakomodasi kriteria pencarian dan formula pencarian kiri yang dibuat selama tutorial ini
Memulai Formula Pencarian Kiri - Membuka Kotak Dialog VLOOKUP
Meskipun dimungkinkan untuk mengetikkan rumus di atas langsung ke sel F1 di lembar kerja, banyak orang mengalami kesulitan dengan sintaks rumus.
Alternatif, dalam hal ini, adalah menggunakan kotak dialog VLOOKUP. Hampir semua fungsi Excel memiliki kotak dialog yang memungkinkan Anda untuk memasukkan masing-masing argumen fungsi pada baris terpisah.
Langkah-langkah Tutorial
- Klik pada sel E2 dari lembar kerja - lokasi di mana hasil dari rumus pencarian kiri akan ditampilkan
- Klik pada tab Rumus pada pita
- Klik opsi Pencarian & Referensi di pita untuk membuka daftar tarik-turun fungsi
- Klik VLOOKUP dalam daftar untuk memunculkan kotak dialog fungsi
02 03
Memasukkan Argumen ke dalam Kotak Dialog VLOOKUP - Klik untuk Melihat Gambar Lebih Besar
Argumen VLOOKUP
Argumen fungsi adalah nilai yang digunakan oleh fungsi untuk menghitung hasil.
Dalam kotak dialog fungsi, nama setiap argumen terletak pada baris terpisah yang diikuti oleh bidang untuk memasukkan nilai.
Masukkan nilai berikut untuk masing-masing argumen VLOOKUP pada baris yang benar dari kotak dialog seperti yang ditunjukkan pada gambar di atas.
Nilai Pencarian
Nilai pencarian adalah bidang informasi yang digunakan untuk mencari susunan tabel. VLOOKUP mengembalikan bidang lain data dari baris yang sama dengan nilai pencarian.
Contoh ini menggunakan referensi sel ke lokasi tempat nama perusahaan akan dimasukkan ke dalam lembar kerja. Keuntungan dari ini adalah memudahkan untuk mengubah nama perusahaan tanpa mengedit formula.
Langkah-langkah Tutorial
- Klik pada baris lookup_value di kotak dialog
- Klik pada sel D2 untuk menambahkan referensi sel ini ke garis lookup_value
- Tekan tombol F4 pada keyboard untuk membuat referensi sel mutlak - $ D $ 2
Catatan: Referensi sel absolut digunakan untuk nilai pencarian dan argumen tabel array untuk mencegah kesalahan jika rumus pencarian disalin ke sel lain di lembar kerja.
Tabel Array: Memasuki Fungsi PILIHAN
Argumen table array adalah blok data yang bersebelahan dari mana informasi tertentu diambil.
Biasanya, VLOOKUP hanya melihat ke kanan dari argumen nilai pencarian untuk menemukan data dalam larik tabel. Agar terlihat kiri, VLOOKUP harus diakali dengan menata ulang kolom dalam larik tabel menggunakan fungsi PILIHAN.
Dalam rumus ini, fungsi PILIHAN menyelesaikan dua tugas:
- itu menciptakan array tabel yang hanya dua kolom lebar - kolom D dan F
- itu mengubah urutan kanan ke kiri dari kolom dalam array tabel sehingga kolom F datang pertama dan kolom D adalah detik
Rincian tentang bagaimana fungsi CHOOSE menyelesaikan tugas-tugas ini dapat ditemukan di halaman 3 dari tutorial .
Langkah-langkah Tutorial
Catatan: Saat memasukkan fungsi secara manual, masing-masing argumen fungsi harus dipisahkan dengan koma "," .
- Dalam kotak dialog fungsi VLOOKUP, klik pada baris Table_array
- Masukkan fungsi PILIHAN berikut
- PILIH ({1,2}, $ F: $ F, $ D: $ D)
Nomor Indeks Kolom
Biasanya, nomor indeks kolom menunjukkan kolom mana dari larik tabel berisi data yang Anda cari. Dalam formula ini; Namun, ini mengacu pada urutan kolom yang ditetapkan oleh fungsi MEMILIH.
Fungsi PILIHAN membuat larik tabel yang memiliki lebar dua kolom dengan kolom F pertama diikuti oleh kolom D. Karena informasi yang dicari - nama bagian - berada di kolom D, nilai argumen indeks kolom harus disetel ke 2.
Langkah-langkah Tutorial
- Klik pada baris Col_index_num di kotak dialog
- Ketik a 2 di baris ini
The Range Lookup
Argumen Range_lookup VLOOKUP adalah nilai logis (hanya TRUE atau FALSE) yang menunjukkan apakah Anda ingin VLOOKUP untuk menemukan pencocokan tepat atau perkiraan untuk nilai pencarian.
- Jika TRUE atau jika argumen ini dihilangkan, VLOOKUP mengembalikan kecocokan tepat ke Lookup_value, atau, jika pencocokan tepat tidak ditemukan, VLOOKUP mengembalikan nilai terbesar berikutnya. Untuk rumus untuk melakukan ini, data di kolom pertama Table_array harus disortir dalam urutan menaik .
- Jika FALSE, VLOOKUP hanya akan menggunakan pencocokan tepat ke Lookup_value. Jika ada dua atau lebih nilai di kolom pertama Table_array yang cocok dengan nilai pencarian, nilai pertama yang ditemukan digunakan. Jika pencocokan tepat tidak ditemukan, kesalahan # N / A dikembalikan.
Dalam tutorial ini, karena kami mencari nama bagian tertentu, Range_lookup akan disetel ke False sehingga hanya pencocokan tepat yang dikembalikan oleh rumus.
Langkah-langkah Tutorial
- Klik pada baris Range_lookup di kotak dialog
- Ketik kata Salah dalam baris ini untuk menunjukkan bahwa kami ingin VLOOKUP untuk mengembalikan kecocokan tepat untuk data yang kami cari
- Klik OK untuk menyelesaikan rumus pencarian kiri dan tutup kotak dialog
- Karena kita belum memasukkan nama perusahaan ke dalam sel D2, kesalahan # N / A harus ada di sel E2
03 03
Menguji Formula Left Lookup
Mengembalikan Data dengan Formula Pencarian Kiri
Untuk menemukan perusahaan mana yang memasok bagian-bagian mana, ketik nama perusahaan ke sel D2 dan tekan tombol ENTER pada keyboard.
Nama bagian akan ditampilkan di sel E2.
Langkah-langkah Tutorial
- Klik pada sel D2 di lembar kerja Anda
- Ketik Gadget Plus ke dalam sel D2 dan tekan tombol ENTER pada keyboard
- Gadget teks - bagian yang disediakan oleh perusahaan Gadgets Plus - harus ditampilkan di sel E2
- Uji rumus pencarian lebih lanjut dengan mengetikkan nama perusahaan lain ke dalam sel D2 dan nama bagian yang sesuai akan muncul di sel E2
Pesan Kesalahan VLOOKUP
Jika pesan kesalahan seperti #N / A muncul di sel E2, periksa dulu kesalahan ejaan di sel D2.
Jika ejaan tidak menjadi masalah, daftar pesan kesalahan VLOOKUP ini dapat membantu Anda menentukan di mana letak masalahnya.
Mendobrak Pekerjaan Fungsi PILIHAN
Seperti disebutkan, dalam rumus ini, fungsi PILIHAN memiliki dua pekerjaan:
- itu menciptakan array tabel yang hanya dua kolom lebar - kolom D dan F
- itu mengubah urutan kanan ke kiri dari kolom dalam array tabel sehingga kolom F datang pertama dan kolom D adalah detik
Membuat Array Tabel Dua Kolom
Sintaks untuk fungsi PILIHAN adalah:
= PILIH (Indeks_Jumlah, Nilai1, Nilai2, ... Nilai254)
Fungsi CHOOSE biasanya mengembalikan satu nilai dari daftar nilai (Value1 ke Value254) berdasarkan nomor indeks yang dimasukkan.
Jika nomor indeks adalah 1, fungsi mengembalikan nilai1 dari daftar; jika nomor indeks adalah 2, fungsi mengembalikan Value2 dari daftar dan seterusnya.
Dengan memasukkan beberapa nomor indeks; namun, fungsi ini akan mengembalikan beberapa nilai dalam urutan apa pun yang diinginkan. MEMILIH untuk mengembalikan beberapa nilai dilakukan dengan membuat larik .
Memasukkan array dilakukan dengan mengelilingi angka yang dimasukkan dengan kurung kurawal kurawal. Dua angka dimasukkan untuk nomor indeks: {1,2} .
Perlu dicatat bahwa MEMILIH tidak terbatas untuk membuat tabel dua kolom. Dengan memasukkan nomor tambahan dalam array - seperti {1,2,3} - dan rentang tambahan dalam argumen nilai, tabel tiga kolom dapat dibuat.
Kolom tambahan akan memungkinkan Anda untuk mengembalikan informasi yang berbeda dengan rumus pencarian kiri hanya dengan mengubah argumen nomor indeks kolom VLOOKUP ke nomor kolom yang berisi informasi yang diinginkan.
Mengubah Urutan Kolom dengan Fungsi PILIH
Dalam fungsi CHOOSE yang digunakan dalam rumus ini: MEMILIH ({1,2}, $ F: $ F, $ D: $ D) , rentang untuk kolom F tercantum sebelum kolom D.
Karena fungsi CHOOSE menetapkan array tabel VLOOKUP - sumber data untuk fungsi itu - mengubah urutan kolom dalam fungsi CHOOSE akan diteruskan ke VLOOKUP.
Sekarang, sejauh menyangkut VLOOKUP, array tabel hanya dua kolom lebar dengan kolom F di sebelah kiri dan kolom D di sebelah kanan. Karena kolom F berisi nama perusahaan yang ingin kita cari, dan karena kolom D berisi nama bagian, VLOOKUP akan dapat melakukan tugas pencarian normal dalam mencari data yang terletak di sebelah kiri nilai pencarian.
Akibatnya, VLOOKUP dapat menggunakan nama perusahaan untuk menemukan bagian yang mereka berikan.