01 dari 03
Cari Data ke Kiri
Tinjauan Formula Excel Excel Kiri
Fungsi VLOOKUP Excel digunakan untuk mencari dan mengembalikan maklumat dari jadual data berdasarkan nilai pencarian yang anda pilih.
Biasanya, VLOOKUP memerlukan nilai carian untuk berada di lajur kiri jadual data, dan fungsi mengembalikan medan data lain yang berada di baris yang sama di sebelah kanan nilai ini.
Dengan menggabungkan VLOOKUP dengan fungsi CHOOSE ; Walau bagaimanapun, formula carian kiri boleh dibuat yang akan:
- membenarkan nilai carian untuk dipilih dari mana-mana lajur dalam jadual data
- kembali maklumat yang terletak di mana-mana ruangan di sebelah kiri nilai carian
Contoh: Menggunakan VLOOKUP dan PILIHAN Fungsi dalam Formula Lookup Kiri
Langkah-langkah yang terperinci di bawah membuat formula carian kiri dilihat dalam imej di atas.
Formula itu
= VLOOKUP ($ D $ 2, PILIH ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)menjadikannya mungkin untuk mencari bahagian yang dibekalkan oleh syarikat-syarikat yang berlainan yang disenaraikan dalam lajur 3 jadual data.
Tugas fungsi PILIH dalam formula adalah untuk menipu VLOOKUP untuk mempercayai bahawa lajur 3 adalah benar-benar lajur 1. Akibatnya, nama Syarikat boleh digunakan sebagai nilai pencarian untuk mencari nama bahagian yang dibekalkan oleh setiap syarikat.
Langkah Tutorial - Memasukkan Data Tutorial
- Masukkan tajuk berikut ke dalam sel yang ditunjukkan: D1 - Pembekal E1 - Bahagian
- Masukkan jadual data yang dilihat pada imej di atas ke dalam sel D4 hingga F9
- Baris 2 dan 3 dibiarkan kosong untuk memenuhi kriteria carian dan rumus pencarian kiri yang dibuat semasa tutorial ini
Memulakan Formula Lookup Kiri - Membuka Peti Dialog VLOOKUP
Walaupun mungkin hanya menaip formula di atas langsung ke dalam sel F1 dalam lembaran kerja, ramai orang mengalami kesulitan dengan sintaks formula.
Satu alternatif, dalam kes ini, adalah menggunakan kotak dialog VLOOKUP. Hampir semua fungsi Excel mempunyai kotak dialog yang membolehkan anda memasukkan setiap hujah fungsi pada baris berasingan.
Langkah Tutorial
- Klik pada sel E2 lembaran kerja - lokasi di mana hasil carian formula kiri akan dipaparkan
- Klik pada tab Rumus pada reben
- Klik pada pilihan Lookup & Rujukan dalam reben untuk membuka senarai drop-down fungsi
- Klik pada VLOOKUP dalam senarai untuk memaparkan kotak dialog fungsi
02 dari 03
Memasuki Argumen ke dalam Peti Dialog VLOOKUP - Klik untuk Lihat Imej Besar
Argumen VLOOKUP
Hujah fungsi adalah nilai yang digunakan oleh fungsi untuk mengira hasilnya.
Dalam kotak dialog fungsi, nama setiap hujah terletak pada baris berasingan diikuti dengan medan untuk memasukkan nilai.
Masukkan nilai berikut untuk setiap hujah VLOOKUP pada baris dialog kotak yang betul seperti yang ditunjukkan dalam imej di atas.
Nilai Lookup
Nilai carian ialah medan maklumat yang digunakan untuk mencari pelbagai jadual. VLOOKUP mengembalikan bidang data lain dari baris yang sama dengan nilai carian.
Contoh ini menggunakan rujukan sel untuk lokasi di mana nama syarikat akan dimasukkan ke dalam lembaran kerja. Kelebihan ini adalah menjadikannya mudah untuk menukar nama syarikat tanpa menyunting formula.
Langkah Tutorial
- Klik pada baris lookup_value dalam kotak dialog
- Klik pada sel D2 untuk menambah rujukan sel ini ke baris lookup_value
- Tekan kekunci F4 pada papan kekunci untuk membuat rujukan sel mutlak - $ D $ 2
Nota: Rujukan sel mutlak digunakan untuk nilai carian dan hujah array meja untuk mengelakkan ralat jika formula carian disalin ke sel lain dalam lembaran kerja.
Arahan Jadual: Memasuki PILIHAN Fungsi
Hujah tatasusunan meja ialah blok data bersebelahan yang mana maklumat khusus diambil.
Biasanya, VLOOKUP hanya kelihatan ke kanan hujah nilai pencarian untuk mencari data dalam tatasusunan jadual. Untuk mendapatkannya untuk melihat ke kiri, VLOOKUP mesti ditipu dengan menyusun semula lajur dalam pelbagai jadual menggunakan fungsi PILIH.
Dalam formula ini, fungsi CHOOSE menyelesaikan dua tugas:
- ia mewujudkan array meja yang hanya dua lajur lebar - lajur D dan F
- ia mengubah hak kepada urutan kiri lajur dalam array meja supaya lajur F datang pertama dan lajur D adalah kedua
Butiran tentang bagaimana fungsi PILIH menyelesaikan tugas-tugas ini boleh didapati di halaman 3 tutorial .
Langkah Tutorial
Nota: Apabila memasukkan fungsi secara manual, setiap hujah fungsi mesti dipisahkan oleh koma "," .
- Dalam kotak dialog fungsi VLOOKUP, klik pada baris Table_array
- Masukkan fungsi CHOOSE berikut
- PILIH ({1,2}, $ F: $ F, $ D: $ D)
Nombor Indeks Lajur
Biasanya, nombor indeks lajur menunjukkan lajur dari jadual jadual mengandungi data yang anda ikuti. Dalam formula ini; Walau bagaimanapun, ia merujuk kepada urutan lajur yang ditetapkan oleh fungsi PILIH.
Fungsi CHOOSE mewujudkan satu array jadual iaitu dua lajur lebar dengan lajur F pertama diikuti oleh lajur D. Oleh kerana maklumat dicari - nama bahagian - berada di lajur D, nilai hujah indeks lajur mesti ditetapkan ke 2.
Langkah Tutorial
- Klik pada baris Col_index_num dalam kotak dialog
- Taipkan 2 dalam baris ini
Senarai Julat
Argumen Range_lookup VLOOKUP adalah nilai logik (TRUE atau FALSE sahaja) yang menunjukkan sama ada anda mahu VLOOKUP untuk mencari persamaan yang tepat atau anggaran untuk nilai carian.
- Jika TRUE atau jika hujah ini diabaikan, VLOOKUP mengembalikan sama ada padanan tepat kepada Lookup_value, atau, jika padanan tepat tidak dijumpai, VLOOKUP mengembalikan nilai seterusnya seterusnya. Untuk formula untuk melakukan ini, data dalam lajur pertama Table_array mesti disusun dalam urutan menaik .
- Jika FALSE, VLOOKUP hanya akan menggunakan padanan tepat pada Lookup_value. Jika terdapat dua atau lebih nilai dalam lajur pertama Table_array yang sepadan dengan nilai carian, nilai pertama yang dijumpai digunakan. Jika padanan tepat tidak dijumpai, ralat # N / A dikembalikan.
Dalam tutorial ini, kerana kami mencari nama bahagian tertentu, Range_lookup akan ditetapkan kepada Palsu supaya hanya padanan tepat dikembalikan oleh formula.
Langkah Tutorial
- Klik pada baris Range_lookup dalam kotak dialog
- Ketik kata Palsu dalam baris ini untuk menunjukkan bahawa kami mahu VLOOKUP mengembalikan padanan tepat untuk data yang kami cari
- Klik OK untuk melengkapkan formula carian kiri dan kotak dialog yang rapat
- Oleh kerana kami belum memasukkan nama syarikat ke dalam sel D2, ralat # N / A sepatutnya terdapat dalam sel E2
03 dari 03
Menguji Formula Lookup Kiri
Mengembalikan Data dengan Formula Lookup Kiri
Untuk mencari syarikat yang membekalkan bahagian mana, taipkan nama syarikat ke dalam sel D2 dan tekan kekunci ENTER pada papan kekunci.
Nama bahagian akan dipaparkan dalam sel E2.
Langkah Tutorial
- Klik pada sel D2 dalam lembaran kerja anda
- Ketik Alat Plus ke sel D2 dan tekan tombol ENTER pada keyboard
- Alat teks - bahagian yang dibekalkan oleh syarikat Gadgets Plus - harus dipaparkan dalam sel E2
- Uji formula pencarian lagi dengan menaip nama syarikat lain ke dalam sel D2 dan nama bahagian yang sepadan akan muncul di sel E2
Mesej Gangguan VLOOKUP
Sekiranya mesej ralat seperti # N / A muncul di sel E2, mula-mula semak kesilapan ejaan dalam sel D2.
Jika ejaan tidak menjadi masalah, senarai mesej ralat VLOOKUP ini dapat membantu anda menentukan di mana masalahnya terletak.
Memecah kerja PILIHAN Fungsi
Seperti yang disebutkan, dalam formula ini, fungsi CHOOSE mempunyai dua pekerjaan:
- ia mewujudkan array meja yang hanya dua lajur lebar - lajur D dan F
- ia mengubah hak kepada urutan kiri lajur dalam array meja supaya lajur F datang pertama dan lajur D adalah kedua
Membuat Array Jadual Dua Lajur
Sintaks untuk fungsi PILIH adalah:
= PILIHAN (Index_number, Value1, Value2, ... Value254)
Fungsi CHOOSE biasanya mengembalikan satu nilai dari senarai nilai (Value1 hingga Value254) berdasarkan nombor indeks yang dimasukkan.
Jika nombor indeks adalah 1, fungsi mengembalikan nilai1 dari senarai; jika nombor indeks adalah 2, fungsi itu mengembalikan Value2 dari senarai dan sebagainya.
Dengan memasukkan nombor indeks berganda; Walau bagaimanapun, fungsi ini akan memulangkan pelbagai nilai dalam sebarang pesanan yang dikehendaki. Mendapatkan PILIH untuk mengembalikan beberapa nilai dilakukan dengan membuat array .
Memasuki array diselesaikan dengan mengelilingi nombor yang dimasukkan dengan pendakap atau kurungan keriting. Dua nombor dimasukkan untuk nombor indeks: {1,2} .
Perlu diingatkan bahawa CHOOSE tidak terhad kepada membuat dua jadual lajur. Dengan memasukkan nombor tambahan dalam array - seperti {1,2,3} - dan julat tambahan dalam hujah nilai, tiga jadual lajur boleh diwujudkan.
Lajur tambahan akan membolehkan anda untuk mengembalikan maklumat yang berbeza dengan formula carian kiri hanya dengan menukar hujah indeks kolum VLOOKUP kepada bilangan lajur yang mengandungi maklumat yang dikehendaki.
Mengubah Perintah Lajur dengan PILIHAN Fungsi
Dalam fungsi PILIH yang digunakan dalam formula ini: PILIH ({1,2}, $ F: $ F, $ D: $ D) , julat bagi ruangan F disenaraikan sebelum lajur D.
Oleh kerana fungsi CHOOSE menetapkan tatas jadual VLOOKUP - sumber data untuk fungsi itu - menukar urutan lajur dalam fungsi CHOOSE akan diluluskan bersama ke VLOOKUP.
Sekarang, setakat VLOOKUP, tatasusunan meja hanya dua tiang lebar dengan lajur F di sebelah kiri dan lajur D di sebelah kanan. Oleh kerana ruangan F mengandungi nama syarikat yang ingin kami cari, dan sejak ruangan D mengandungi nama-nama bahagian, VLOOKUP akan dapat melakukan tugas lookup normalnya dalam mencari data yang terletak di sebelah kiri nilai pencarian.
Akibatnya, VLOOKUP dapat menggunakan nama syarikat untuk mencari bahagian yang mereka sediakan.