Saha anu puguh teuing atanapi henteu gaduh waktos maca - tingali pidéo. Rincian sareng nuansa aya dina téks di handap ieu.
Rumusan masalah
Janten, urang gaduh dua tabel - méja pesenan и daptar harga:
Tugasna nyaéta ngagentos harga tina daptar harga kana tabel pesenan sacara otomatis, fokus kana nami produk supados engké anjeun tiasa ngitung biaya.
leyuran
Dina set fungsi Excel, dina kategori Rujukan jeung arrays (Milarian sareng rujukan) aya fungsi VPR (VLOOKUP).Fungsi ieu milarian nilai anu dipasihkeun (dina conto urang, ieu mangrupikeun kecap "Apel") dina kolom paling kénca dina tabel anu ditangtukeun (daptar harga) pindah ti luhur ka handap sareng, saatos mendakanana, ningalikeun eusi sél anu padeukeut. (23 rubles) .Schematically, operasi fungsi ieu bisa digambarkeun jadi:
Pikeun betah pamakéan salajengna tina fungsi, ngalakukeun hiji hal sakaligus - masihan rentang sél dina daptar harga ngaran anjeun sorangan. Jang ngalampahkeun ieu, pilih sadaya sél tina daptar harga iwal ti "header" (G3: H19), pilih tina ménu. Lebetkeun - Ngaran - Assign (Selapkeun - Ngaran - Tangtukeun) atawa pencet CTRL + F3 sareng lebetkeun nami naon waé (henteu aya spasi) sapertos harga… Ayeuna, dina mangsa nu bakal datang, Anjeun bisa make ngaran ieu numbu ka daptar harga.
Ayeuna kami nganggo fungsi VPR… Pilih sél dimana eta bakal diasupkeun (D3) jeung muka tab nu Rumus - Selapkeun Fungsi (Rumus - Fungsi Selapkeun)… Dina kategori Rujukan jeung arrays (Paluruh sareng Rujukan) manggihan fungsi VPR (VLOOKUP) terus pencét OK... Jandéla pikeun ngasupkeun argumen pikeun fungsi bakal muncul:
Urang ngeusian aranjeunna dina gilirannana:
- nilai nu dipikahoyong (Nilai Pilarian) – ngaran produk nu fungsi kudu manggihan dina kolom leftmost tina daptar harga. Dina hal urang, kecap "Apel" ti sél B3.
- meja (Table Array) – tabel ti mana nilai nu dipikahoyong uXNUMXbuXNUMXbare dicokot, nyaeta, daptar harga urang. Pikeun rujukan, kami nganggo nami urang sorangan "Harga" anu dipasihkeun sateuacana. Lamun teu masihan ngaran, Anjeun ngan bisa milih tabél, tapi tong hilap pencét tombolna F4pikeun pin link kalayan tanda dollar, sabab lamun henteu, éta bakal ngageser ka handap nalika nyalin rumus urang ka handap ka sesa sél dina kolom D3:D30.
- Jumlah_kolom (Nomer indéks kolom) - angka serial (sanes hurup!) Tina kolom dina daptar harga ti mana urang bakal nyandak nilai harga. Kolom kahiji tina daptar harga sareng nami nomer 1, janten urang peryogi harga tina kolom nomer 2.
- interval_lookup (Paluruhan Rentang) - ngan ukur dua nilai anu tiasa diasupkeun dina widang ieu: PALSU atanapi BENER:
- Lamun nilai diasupkeun 0 or bohong (PALSU), lajeng dina kanyataanana ieu ngandung harti yén ngan pilarian diwenangkeun pertandingan pasti, ie lamun fungsi nu teu manggihan item non-standar dieusian dina tabel urutan dina daptar harga (lamun "Kalapa" diasupkeun, contona), bakal ngahasilkeun kasalahan #N / A (euweuh data).
- Lamun nilai diasupkeun 1 or BENER (BENER), lajeng ieu ngandung harti yén anjeun ngidinan pilarian teu keur pasti, tapi cocog perkiraan, ie dina kasus "kalapa", fungsi bakal coba pikeun manggihan produk kalawan ngaran anu sacaket mungkin mun "kalapa" jeung balik harga pikeun ngaran ieu. Dina kalolobaan kasus, substitusi perkiraan sapertos tiasa maénkeun trik dina pangguna ku ngagentos nilai produk anu salah anu leres-leres aya! Ku kituna pikeun sabagéan ageung masalah bisnis nyata, pilarian perkiraan pangalusna teu ngidinan. Pangecualian nyaéta nalika urang milarian nomer sareng sanés téks - contona, nalika ngitung diskon Lengkah.
Sagalana! Tetep pencét OK sarta nyalin fungsi diasupkeun kana sakabéh kolom.
# N / A kasalahan jeung suprési maranéhna
fungsi VPR (VLOOKUP) mulih #N / A kasalahan (#N/A) lamun a:
- Pilarian pasti diaktipkeun (argumen Tempo interval = 0) sareng nami anu dipikahoyong henteu aya dina meja.
- Paluruh kasar kaasup (Tempo interval = 1), Tapi meja, nu pilarian lumangsung teu diurutkeun dina urutan naek tina ngaran.
- Format sél dimana nilai anu dibutuhkeun tina nami asalna (contona, B3 dina kasus urang) sareng format sél dina kolom kahiji (F3: F19) tabel béda (contona, angka sareng téks. ). Kasus ieu utamana has lamun ngagunakeun kode numerik (nomer akun, identifiers, kaping, jsb) tinimbang ngaran téks. Dina hal ieu, anjeun tiasa nganggo fungsi Ч и naskah pikeun ngarobah format data. Éta bakal katingali sapertos kieu:
=VLOOKUP(TEXT(B3),harga,0)
Anjeun tiasa maca langkung seueur ngeunaan ieu di dieu.
- fungsi nu teu bisa manggihan nilai diperlukeun sabab kode ngandung spasi atawa karakter non-print halimunan (garis putus, jsb). Dina hal ieu, anjeun tiasa nganggo fungsi téks motong (TRIM) и nyitak(BERSIH) pikeun ngaleungitkeun aranjeunna:
=VLOOKUP(TRIMSPACES(BERSIH(B3)),harga,0)
=VLOOKUP(TRIM(BERSIH(B3));harga;0)
Pikeun ngeureunkeun pesen kasalahan # N / A (#N/A) dina kasus dimana fungsi nu teu bisa manggihan hiji patandingan pasti, anjeun tiasa nganggo fungsi IFERROR (IFERROR)... Jadi, contona, konstruksi ieu intercepts sagala kasalahan dihasilkeun ku VLOOKUP tur ngaganti aranjeunna kalayan nol:
= IFERROR (VLOOKUP (B3, harga, 2, 0), 0)
= IFERROR (VLOOKUP (B3; harga; 2; 0); 0)
PS
Upami anjeun kedah nimba henteu hiji nilai, tapi sadayana set sakaligus (upami aya sababaraha anu béda), maka anjeun kedah shamanize kalayan rumus Asép Sunandar Sunarya. atanapi nganggo fitur XLOOKUP énggal ti Office 365.
- Versi ningkat tina fungsi VLOOKUP (VLOOKUP 2).
- Itungan gancang diskon step (rentang) ngagunakeun fungsi VLOOKUP.
- Kumaha carana ngadamel "VLOOKUP kénca" nganggo fungsi INDEX sareng MATCH
- Kumaha ngagunakeun fungsi VLOOKUP pikeun ngeusian formulir sareng data tina daptar
- Kumaha carana narik kaluar teu kahiji, tapi sakabeh nilai tina tabél sakaligus
- VLOOKUP2 sareng VLOOKUP3 fungsina tina tambihan PLEX