Contents [show]
Siapa yang terlalu malas atau tidak mempunyai masa untuk membaca - tonton video. Butiran dan nuansa ada dalam teks di bawah.
Perumusan masalah
Jadi, kami mempunyai dua meja - meja pesanan и senarai harga:
Tugasnya adalah untuk menggantikan harga dari senarai harga ke dalam jadual pesanan secara automatik, memfokuskan pada nama produk supaya kemudian anda boleh mengira kos.
Penyelesaian
Dalam set fungsi Excel, di bawah kategori Rujukan dan tatasusunan (Cari dan rujukan) ada fungsi VPR (TAMPILAN).Fungsi ini mencari nilai yang diberikan (dalam contoh kami, ini ialah perkataan "Epal") dalam lajur paling kiri jadual yang ditentukan (senarai harga) bergerak dari atas ke bawah dan, setelah menemuinya, memaparkan kandungan sel bersebelahan (23 rubel) .Secara skematik, operasi fungsi ini boleh diwakili Jadi:
Untuk memudahkan penggunaan fungsi selanjutnya, lakukan satu perkara sekaligus – berikan julat sel dalam senarai harga nama anda sendiri. Untuk melakukan ini, pilih semua sel senarai harga kecuali untuk "pengepala" (G3: H19), pilih daripada menu Sisipkan – Nama – Tugaskan (Sisipkan — Nama — Tentukan) atau tekan CTRL + F3 dan masukkan sebarang nama (tiada ruang) seperti Harga… Sekarang, pada masa hadapan, anda boleh menggunakan nama ini untuk memaut ke senarai harga.
Sekarang kita menggunakan fungsi tersebut VPR… Pilih sel di mana ia akan dimasukkan (D3) dan buka tab Formula – Sisipan Fungsi (Formula — Fungsi Sisipkan)… Dalam kategori Rujukan dan tatasusunan (Cari dan Rujukan) cari fungsinya VPR (TAMPILAN) dan tekan OK… Tetingkap untuk memasukkan argumen untuk fungsi akan muncul:
Kami mengisinya secara bergilir:
- Nilai yang dikehendaki (Nilai Carian) – nama produk yang perlu dicari oleh fungsi dalam lajur paling kiri senarai harga. Dalam kes kami, perkataan "Epal" daripada sel B3.
- Jadual (Susun Meja) – jadual dari mana nilai yang dikehendaki uXNUMXbuXNUMXbare diambil, iaitu senarai harga kami. Sebagai rujukan, kami menggunakan nama kami sendiri "Harga" yang diberikan sebelum ini. Jika anda tidak memberikan nama, anda boleh pilih jadual, tetapi jangan lupa tekan butang F4untuk menyematkan pautan dengan tanda dolar, kerana jika tidak, ia akan meluncur ke bawah apabila menyalin formula kami ke seluruh sel dalam lajur D3:D30.
- Nombor_lajur (Nombor indeks lajur) – nombor siri (bukan huruf!) Daripada lajur dalam senarai harga yang akan kami ambil nilai harga. Lajur pertama senarai harga dengan nama bernombor 1, oleh itu kami memerlukan harga dari lajur bernombor 2.
- interval_lookup (Pencarian Julat) – hanya dua nilai boleh dimasukkan dalam medan ini: FALSE atau TRUE:
- Jika nilai dimasukkan 0 or BERBOHONG (SALAH), maka sebenarnya ini bermakna hanya carian dibenarkan perlawanan tepat, iaitu jika fungsi tidak menemui item bukan standard yang dinyatakan dalam jadual pesanan dalam senarai harga (jika "Kelapa" dimasukkan, sebagai contoh), ia akan menjana ralat #N/A (tiada data).
- Jika nilai dimasukkan 1 or TRUE (BENAR), maka ini bermakna anda membenarkan carian bukan untuk yang tepat, tetapi padanan anggaran, iaitu dalam kes "kelapa", fungsi akan cuba mencari produk dengan nama yang sedekat mungkin dengan "kelapa" dan mengembalikan harga untuk nama ini. Dalam kebanyakan kes, penggantian anggaran sedemikian boleh memperdaya pengguna dengan menggantikan nilai produk yang salah yang sebenarnya ada! Jadi untuk kebanyakan masalah perniagaan sebenar, carian anggaran adalah yang terbaik untuk tidak dibenarkan. Pengecualian ialah apabila kita mencari nombor dan bukan teks – contohnya, apabila mengira Diskaun Langkah.
Semuanya! Ia kekal untuk menekan OK dan salin fungsi yang dimasukkan ke seluruh lajur.
# N / A ralat dan penindasannya
fungsi VPR (TAMPILAN) mengembalikan ralat #N/A (#N/A) sekiranya:
- Carian tepat didayakan (argumen Paparan selang = 0) dan nama yang dikehendaki tiada dalam Jadual.
- Carian kasar disertakan (Paparan selang = 1), tetapi Jadual, di mana carian sedang berlaku tidak diisih dalam susunan menaik nama.
- Format sel di mana nilai nama yang diperlukan berasal (contohnya, B3 dalam kes kami) dan format sel lajur pertama (F3: F19) jadual adalah berbeza (contohnya, angka dan teks ). Kes ini adalah tipikal apabila menggunakan kod angka (nombor akaun, pengecam, tarikh, dll.) dan bukannya nama teks. Dalam kes ini, anda boleh menggunakan fungsi Ч и TEXT untuk menukar format data. Ia akan kelihatan seperti ini:
=VLOOKUP(TEKS(B3),harga,0)
Anda boleh membaca lebih lanjut mengenai ini di sini.
- Fungsi tidak dapat mencari nilai yang diperlukan kerana kod tersebut mengandungi ruang atau aksara tidak boleh dicetak yang tidak kelihatan (pemutus baris, dsb.). Dalam kes ini, anda boleh menggunakan fungsi teks TRIM (TRIM) и PRINT(BERSIH) untuk mengeluarkannya:
=VLOOKUP(TRIMSPACES(BERSIH(B3)),harga,0)
=VLOOKUP(TRIM(BERSIH(B3));harga;0)
Untuk menyekat mesej ralat # N / A (#N/A) dalam kes di mana fungsi tidak dapat mencari padanan tepat, anda boleh menggunakan fungsi tersebut IFERROR (IFERROR)… Jadi, sebagai contoh, pembinaan ini memintas sebarang ralat yang dihasilkan oleh VLOOKUP dan menggantikannya dengan sifar:
= IFERROR (VLOOKUP (B3, harga, 2, 0), 0)
= IFERROR (VLOOKUP (B3; harga; 2; 0); 0)
PS
Jika anda perlu mengekstrak bukan satu nilai, tetapi keseluruhan set sekali gus (jika terdapat beberapa nilai yang berbeza), maka anda perlu menggunakan formula tatasusunan. atau gunakan ciri XLOOKUP baharu daripada Office 365.
- Versi fungsi VLOOKUP yang dipertingkatkan (VLOOKUP 2).
- Pengiraan pantas diskaun langkah (julat) menggunakan fungsi VLOOKUP.
- Cara membuat "VLOOKUP kiri" menggunakan fungsi INDEX dan MATCH
- Cara menggunakan fungsi VLOOKUP untuk mengisi borang dengan data daripada senarai
- Bagaimana untuk menarik keluar bukan yang pertama, tetapi semua nilai dari jadual sekaligus
- VLOOKUP2 dan VLOOKUP3 berfungsi daripada alat tambah PLEX