Menggunakan fungsi VLOOKUP untuk mengganti nilai

Siapa yang terlalu malas atau tidak punya waktu untuk membaca – tonton videonya. Detail dan nuansa ada di teks di bawah ini.

Rumusan masalah

Jadi, kami memiliki dua tabel – meja pesanan и Daftar Harga:

Tugasnya adalah mensubtitusi harga dari price list ke tabel order secara otomatis, fokus pada nama produk agar nantinya bisa dihitung biayanya.

Solusi

Di set fungsi Excel, di bawah kategori Referensi dan array (Pencarian dan referensi) ada fungsi VPR (VLOOKUP).Fungsi ini mencari nilai yang diberikan (dalam contoh kami, ini adalah kata "Apel") di kolom paling kiri dari tabel yang ditentukan (daftar harga) bergerak dari atas ke bawah dan, setelah menemukannya, menampilkan konten sel yang berdekatan (23 rubel) . Secara skema, operasi fungsi ini dapat direpresentasikan Jadi:

Untuk kemudahan penggunaan fungsi lebih lanjut, lakukan satu hal sekaligus – berikan rentang sel dalam daftar harga nama Anda sendiri. Untuk melakukan ini, pilih semua sel dari daftar harga kecuali untuk "tajuk" (G3: H19), pilih dari menu Sisipkan – Nama – Tetapkan (Sisipkan — Nama — Tentukan) atau tekan CTRL + F3 dan masukkan nama apa saja (tanpa spasi) seperti Harga… Sekarang, di masa depan, Anda dapat menggunakan nama ini untuk menautkan ke daftar harga.

Sekarang kita menggunakan fungsi VPR… Pilih sel yang akan dimasukkan (D3) dan buka tab Rumus – Penyisipan Fungsi (Rumus — Sisipkan Fungsi)… Dalam kategori Referensi dan array (Pencarian dan Referensi) temukan fungsinya VPR (VLOOKUP) dan tekan OK… Jendela untuk memasukkan argumen untuk fungsi akan muncul:

Menggunakan fungsi VLOOKUP untuk mengganti nilai

Kami mengisinya secara bergantian:

  • Nilai yang diinginkan (Nilai lookup) – nama produk yang fungsinya harus ditemukan di kolom paling kiri dari daftar harga. Dalam kasus kami, kata "Apel" dari sel B3.
  • tabel (Rangkaian Tabel) – tabel dari mana nilai yang diinginkan diambil, yaitu, daftar harga kami. Untuk referensi, kami menggunakan nama kami sendiri "Harga" yang diberikan sebelumnya. Jika Anda tidak memberi nama, Anda bisa memilih tabelnya, tapi jangan lupa tekan tombol F4untuk menyematkan tautan dengan tanda dolar, karena jika tidak, itu akan meluncur ke bawah saat menyalin rumus kita ke sel lainnya di kolom D3:D30.
  • Nomor_kolom (Nomor indeks kolom) – nomor seri (bukan huruf!) Dari kolom dalam daftar harga dari mana kita akan mengambil nilai harga. Kolom pertama daftar harga dengan nama diberi nomor 1, oleh karena itu kita membutuhkan harga dari kolom nomor 2.
  • interval_pencarian (Pencarian Rentang) – hanya dua nilai yang dapat dimasukkan di bidang ini: FALSE atau TRUE:
      • Jika nilai dimasukkan 0 or BOHONG (SALAH), maka sebenarnya ini berarti hanya pencarian yang diperbolehkan pertandingan yang tepat, yaitu jika fungsi tidak menemukan item non-standar yang ditentukan dalam tabel pesanan dalam daftar harga (jika "Kelapa" dimasukkan, misalnya), itu akan menghasilkan kesalahan #N/A (tidak ada data).
      • Jika nilai dimasukkan 1 or BENAR (BENAR), maka ini berarti Anda mengizinkan pencarian bukan untuk yang tepat, tetapi perkiraan pertandingan, yaitu dalam kasus “kelapa”, fungsi akan mencoba mencari produk dengan nama yang sedekat mungkin dengan “kelapa” dan mengembalikan harga untuk nama ini. Dalam kebanyakan kasus, perkiraan substitusi seperti itu dapat menipu pengguna dengan mengganti nilai produk yang salah yang sebenarnya ada! Jadi untuk sebagian besar masalah bisnis nyata, pencarian perkiraan sebaiknya tidak diizinkan. Pengecualian adalah ketika kita mencari angka dan bukan teks – misalnya, saat menghitung Diskon Langkah.

Semuanya! Tinggal tekan OK dan salin fungsi yang dimasukkan ke seluruh kolom.

# N / A kesalahan dan penindasannya

fungsi VPR (VLOOKUP) mengembalikan kesalahan #N/A (#T/A) jika sebuah:

  • Pencarian tepat diaktifkan (argumen Tampilan interval = 0) dan nama yang diinginkan tidak ada di tabel.
  • Pencarian kasar termasuk (Tampilan interval = 1), tapi tabel, di mana pencarian berlangsung tidak diurutkan dalam urutan nama.
  • Format sel tempat nilai nama yang diperlukan berasal (misalnya, B3 dalam kasus kami) dan format sel kolom pertama (F3: F19) tabel berbeda (misalnya, numerik dan teks ). Kasus ini sangat umum ketika menggunakan kode numerik (nomor rekening, pengenal, tanggal, dll.) alih-alih nama teks. Dalam hal ini, Anda dapat menggunakan fungsi Ч и TEKS untuk mengkonversi format data. Ini akan terlihat seperti ini:

    =VLOOKUP(TEXT(B3),harga,0)

    Anda dapat membaca lebih lanjut tentang ini di sini.

  • Fungsi tidak dapat menemukan nilai yang diperlukan karena kode berisi spasi atau karakter tidak terlihat yang tidak dapat dicetak (jeda baris, dll.). Dalam hal ini, Anda dapat menggunakan fungsi teks TRIM (MEMANGKAS) и PRINT(MEMBERSIHKAN) untuk menghapusnya:

    =VLOOKUP(TRIMSPACES(BERSIH(B3)),harga,0)

    =VLOOKUP(TRIM(BERSIH(B3));harga;0)

Untuk menekan pesan kesalahan # N / A (#T/A) dalam kasus di mana fungsi tidak dapat menemukan kecocokan yang tepat, Anda dapat menggunakan fungsi KESALAHAN (KESALAHAN)… Jadi, misalnya, konstruksi ini memotong kesalahan apa pun yang dihasilkan oleh VLOOKUP dan menggantinya dengan nol:

= IFERROR (VLOOKUP (B3, harga, 2, 0), 0)

= IFERROR (VLOOKUP (B3; harga; 2; 0); 0)

PS

Jika Anda perlu mengekstrak bukan satu nilai, tetapi seluruh set sekaligus (jika ada beberapa nilai yang berbeda), maka Anda harus memperdukunan dengan rumus array. atau gunakan fitur XLOOKUP baru dari Office 365.

 

  • Versi yang disempurnakan dari fungsi VLOOKUP (VLOOKUP 2).
  • Perhitungan cepat diskon langkah (rentang) menggunakan fungsi VLOOKUP.
  • Cara membuat "VLOOKUP kiri" menggunakan fungsi INDEX dan MATCH
  • Cara menggunakan fungsi VLOOKUP untuk mengisi formulir dengan data dari daftar
  • Cara mengeluarkan bukan yang pertama, tetapi semua nilai dari tabel sekaligus
  • Fungsi VLOOKUP2 dan VLOOKUP3 dari add-on PLEX

 

Tinggalkan Balasan