Utama perisian Cara Mencipta Formula Excel Carian Kiri Menggunakan VLOOKUP
perisian

Cara Mencipta Formula Excel Carian Kiri Menggunakan VLOOKUP

Cara Mencipta Formula Excel Carian Kiri Menggunakan VLOOKUP
Anonim

Menggabungkan VLOOKUP Excel dan PILIH untuk membuat rumus pencarian kiri

  • Dokumen
  • Persembahan
  • Penerbitan atas meja
  • Reka bentuk grafik
  • Pangkalan data
  • Animasi & Video
  • olehTed French

    Seorang pakar spreadsheet yang merupakan tuan di Excel dan Google Sheets.

    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 paling kiri jadual data, dan fungsi mengembalikan bidang data lain yang terletak di baris yang sama di sebelah kanan nilai ini.

    Walau bagaimanapun, dengan menggabungkan VLOOKUP dengan fungsi CHOOSE, rumus pencarian kiri boleh dibuat bahawa:

    • Mematuhi nilai carian dari mana-mana lajur dalam jadual data
    • Mengembalikan maklumat yang terletak di mana-mana lajur di sebelah kiri nilai carian

    Arahan ini digunakan untuk versi Excel 2019, 2016, 2013, 2010, dan Excel untuk Office 365.

    01

    daripada 05

    Tutorial: Menggunakan VLOOKUP dan PILIHAN Fungsi dalam Formula Lookup Kiri

    Untuk membuat formula carian kiri dilihat dalam imej contoh, gunakan formula:

    = VLOOKUP ($ D $ 2, PILIH ({1, 2}, $ F: $ F, $ D: $ D), 2, FALSE)

    Dalam contoh ini, formula menjadikannya mudah 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 lajur 1. Sebagai hasilnya, nama syarikat boleh digunakan sebagai nilai pencarian untuk mencari nama bahagian yang dibekalkan oleh setiap syarikat.

    Masukkan Data Tutorial

    1. Masukkan Pembekal tajuk ke dalam sel D1 .
    2. Masukkan bahagian Bahagian dalam sel E1 .
    3. Masukkan jadual data yang dilihat pada imej di atas ke dalam sel D4 hingga F9
    4. Baris 2 dan 3 dibiarkan kosong untuk memenuhi kriteria carian dan rumus pencarian kiri yang dibuat semasa tutorial ini

    Buka kotak dialog VLOOKUP

    Walaupun adalah mungkin untuk menaip formula terus ke dalam sel F1 dalam lembaran kerja, ramai orang mengalami kesulitan dengan sintaks formula.

    Dalam kes ini, lebih selamat menggunakan kotak dialog VLOOKUP. Hampir semua fungsi Excel mempunyai kotak dialog yang membolehkan anda memasukkan setiap hujah fungsi pada baris berasingan.

    1. Klik pada sel E2 lembaran kerja. E2 ialah lokasi di mana hasil carian formula kiri akan dipaparkan.
    2. Klik pada tab Rumus pada reben.
    3. Klik pada pilihan Lookup & Rujukan dalam reben untuk membuka senarai drop-down fungsi.
    4. Klik pada VLOOKUP dalam senarai untuk memaparkan kotak dialog fungsi.

    02

    daripada 05

    Memasuki Argumen Ke dalam Peti Dialog VLOOKUP

    Hujah fungsi ialah nilai yang digunakan oleh fungsi untuk mengira hasilnya.

    Dalam kotak dialog fungsi, nama setiap hujah adalah 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 yang disertakan.

    Nilai Lookup

    Nilai pencarian adalah 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.

    1. Klik pada baris lookup_value dalam kotak dialog.
    2. Klik pada sel D2 untuk menambah rujukan sel ini ke baris lookup_value .
    3. Tekan kekunci F4 pada papan kekunci untuk membuat rujukan sel mutlak - $ D $ 2.

    Rujukan sel mutlak digunakan untuk nilai carian dan hujah array meja untuk mengelakkan kesalahan jika formula carian disalin ke sel lain dalam lembaran kerja.

    Memasuki PILIHAN Fungsi

    Hujah array meja adalah blok data bersebelahan yang mana maklumat khusus diambil.

    Biasanya, VLOOKUP hanya kelihatan di sebelah kanan hujah nilai pencarian untuk mencari data dalam pelbagai jadual. Untuk mendapatkannya untuk melihat ke kiri, VLOOKUP mesti ditipu dengan menyusun semula lajur dalam pelbagai jadual menggunakan fungsi PILIH.

    Dalam formula ini, fungsi PILIH menyelesaikan dua tugas:

    1. Ia mewujudkan satu array jadual yang hanya dua lajur lebar (lajur D dan F).
    2. Ia mengubah susunan kanan ke kiri dari lajur dalam array jadual supaya lajur F menjadi lebih awal dan lajur D adalah kedua.

    Memasuki Fungsi

    Apabila memasuki fungsi secara manual, setiap hujah fungsi mesti dipisahkan oleh koma.

    1. Dalam kotak dialog fungsi VLOOKUP, klik pada baris Table_array .
    2. Masukkan fungsi CHOOSE berikut: PILIH ({1, 2}, $ F: $ F, $ D: $ D)

    Nombor Indeks Lajur

    Biasanya, nombor indeks lajur menunjukkan lajur dari array jadual mengandungi data yang anda ikuti. Walau bagaimanapun, dalam formula ini, ia merujuk kepada urutan lajur yang ditetapkan oleh fungsi PILIH.

    Fungsi CHOOSE mewujudkan satu array jadual iaitu dua lajur lebar dengan lajur F yang pertama diikuti oleh lajur D. Oleh kerana maklumat yang dicari-nama bahagian - berada dalam lajur D, nilai hujah indeks lajur mesti ditetapkan ke 2.

    1. Klik pada baris Col_index_num dalam kotak dialog.
    2. Taipkan 2 dalam baris ini.

    Cari Pelbagai

    Hujah 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 pada 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 menggunakan padanan tepat pada Lookup_value. Sekiranya 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 ditetapkan kepada Palsu supaya hanya padanan tepat dikembalikan oleh formula.

    1. Klik pada baris Range_lookup dalam kotak dialog.
    2. Ketik kata Palsu dalam baris ini untuk menunjukkan bahawa kami mahu VLOOKUP mengembalikan padanan tepat untuk data yang kami cari.
    3. Klik OK untuk melengkapkan formula carian kiri dan kotak dialog yang rapat.
    4. Kerana kita belum memasukkan nama syarikat ke dalam sel D2, ralat # N / A muncul di sel E2.

    03

    daripada 05

    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 dipaparkan dalam sel E2.

    1. Klik pada sel D2 dalam lembaran kerja anda.
    2. Ketik Alat Plus ke sel D2 dan tekan tombol ENTER pada keyboard.
    3. Teks "Alat" - bahagian yang dibekalkan oleh syarikat Gadget Plus - sepatutnya 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.

    Sekiranya mesej ralat seperti # N / A muncul di sel E2, periksa kesilapan ejaan dalam sel D2.

    04

    daripada 05

    Mewujudkan Arahan Jadual Dua Lajur

    Sintaks untuk fungsi PILIH adalah:

    = PILIH (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.

    Apabila beberapa nombor indeks dimasukkan, fungsi mengembalikan beberapa nilai dalam sebarang urutan yang dikehendaki. Mendapatkan PILIH untuk mengembalikan beberapa nilai dilakukan dengan membuat array.

    Untuk memasukkan array, surround nombor yang dimasukkan dengan pendakap atau kurungan kerinting. Dua nombor dimasukkan untuk nombor indeks: {1, 2} .

    Perlu diingat bahawa CHOOSE tidak terhad untuk membuat jadual dua lajur. Dengan menyertakan nombor tambahan dalam array - seperti {1, 2, 3} - dan julat tambahan dalam hujah nilai, CHOOSE mencipta jadual tiga lajur.

    Lajur tambahan membolehkan anda mengembalikan maklumat yang berbeza dengan formula carian kiri dengan menukar hujah indeks lajur VLOOKUP kepada bilangan lajur yang mengandungi maklumat yang dikehendaki.

    05

    daripada 05

    Mengubah Perintah Lajur Dengan PILIHAN Fungsi

    Dalam fungsi CHOOSE yang digunakan dalam formula ini:

    PILIH ({1, 2}, $ F: $ F, $ D: $ D)

    julat bagi lajur F disenaraikan sebelum lajur D.

    Oleh kerana fungsi CHOOSE menetapkan pelbagai jadual VLOOKUP (sumber data untuk fungsi itu) menukar urutan lajur dalam fungsi CHOOSE diluluskan bersama untuk VLOOKUP.

    Setakat VLOOKUP, tatasusunan meja hanya dua lajur lebar dengan lajur F di sebelah kiri dan lajur D di sebelah kanan. Oleh sebab lajur F mengandungi nama syarikat yang kita mahu cari, dan sejak lajur D mengandungi nama-nama bahagian, VLOOKUP dapat melakukan tugas pencarian biasa dalam mencari data yang terletak di sebelah kiri nilai pencarian.

    Akibatnya, VLOOKUP boleh menggunakan nama syarikat untuk mencari bahagian yang mereka sediakan.