Daptar eusi
Simkuring nembe dedicated artikel ka salah sahiji fungsi Excel pang gunana disebut VPR sareng nunjukkeun kumaha éta tiasa dianggo pikeun nimba inpormasi anu diperyogikeun tina pangkalan data kana sél lembar kerja. Urang ogé disebutkeun yen aya dua kasus pamakéan pikeun fungsi VPR sarta ngan salah sahijina ngurus queries database. Dina artikel ieu, anjeun bakal diajar cara sejen kirang dipikawanoh ngagunakeun fungsi VPR dina Excel.
Upami anjeun henteu acan ngalakukeun ieu, teras pastikeun maca tulisan terakhir ngeunaan fungsi éta VPR, sabab sagala informasi di handap nganggap yén anjeun geus akrab jeung prinsip dijelaskeun dina artikel munggaran.
Nalika damel sareng pangkalan data, fungsina VPR a identifier unik diliwatan, nu dipaké pikeun ngaidentipikasi informasi urang hayang manggihan (Contona, kode produk atawa nomer idéntifikasi customer). Kodeu unik ieu kedah aya dina pangkalan data, upami henteu VPR bakal ngalaporkeun kasalahan. Dina tulisan ieu, urang bakal ningali cara ngagunakeun fungsi ieu VPRlamun id teu aya dina database pisan. Saolah-olah fungsi VPR switched kana mode perkiraan, sarta milih data naon nyadiakeun kami lamun urang hayang manggihan hiji hal. Dina kaayaan nu tangtu, ieu téh kahayang diperlukeun.
Hiji conto tina kahirupan. Urang nyetél tugas
Hayu urang ngagambarkeun tulisan ieu sareng conto kahirupan nyata - ngitung komisi dumasar kana rupa-rupa métrik penjualan. Urang mimitian ku pilihan anu saderhana pisan, teras urang laun-laun bakal ngahesekeunana dugi ka hiji-hijina solusi rasional pikeun masalahna nyaéta ngagunakeun fungsina. VPR. Skenario awal pikeun tugas fiktif urang nyaéta kieu: lamun salesperson a nyieun leuwih ti $30000 di jualan dina sataun, teras komisi na 30%. Upami teu kitu, komisi ngan 20%. Hayu urang nempatkeun dina bentuk tabel:
Seller ngalebetkeun data penjualanna dina sél B1, sareng rumus dina sél B2 nangtukeun tingkat komisi anu leres anu tiasa diarepkeun ku seller. Sabalikna, laju anu hasilna dianggo dina sél B3 pikeun ngitung total komisi anu kedah ditampi ku penjual (saukur ngalikeun sél B1 sareng B2).
Bagian anu paling pikaresepeun tina méja dikandung dina sél B2 - ieu mangrupikeun rumus pikeun nangtukeun tingkat komisi. Rumus ieu ngandung fungsi Excel anu disebut IF (lamun). Pikeun pamiarsa anu henteu wawuh sareng fungsi ieu, kuring bakal ngajelaskeun kumaha éta jalanna:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Kaayaan mangrupa argumen fungsi nu nyokot nilai boh KODE BENER (BENER), atawa PALSU (PALSU). Dina conto di luhur, éksprési B1
Naha leres B1 kirang ti B5?
Atawa anjeun bisa nyebutkeun béda:
Naha leres yén jumlah total penjualan taun éta kirang tina nilai ambang?
Lamun urang ngajawab patarosan ieu nuhun (BENER), lajeng fungsi mulih nilai lamun leres (nilai lamun BENER). Dina hal urang, ieu bakal nilai sél B6, ie laju komisi lamun total jualan handap bangbarung. Lamun urang ngajawab patarosan NO (PALSU) lajeng mulang nilai lamun palsu (nilai lamun SALAH). Dina kasus urang, ieu mangrupikeun nilai sél B7, nyaéta tingkat komisi nalika total penjualan langkung luhur ambang.
Sakumaha anjeun tiasa tingali, upami urang nyandak total penjualan $ 20000, urang nampi tingkat komisi 2% dina sél B20. Upami urang ngalebetkeun nilai $40000, maka tingkat komisi bakal robih ku 30%:
Ieu kumaha méja kami jalan.
Urang ngahesekeun tugas
Hayu urang nyieun hal saeutik leuwih hese. Hayu urang nyetél bangbarung sejen: lamun seller earns leuwih ti $40000, laju komisi naek ka 40%:
Sagalana sigana basajan tur jelas, tapi rumus urang dina sél B2 janten noticeably leuwih pajeulit. Lamun kasampak raket dina rumus, anjeun bakal nempo yén argumen katilu fungsi IF (IF) robah jadi fungsi full-fledged sejen IF (lamun). Konstruksi ieu disebut nyarang fungsi kana unggal lianna. Excel happily ngamungkinkeun constructs ieu, sarta aranjeunna malah jalan, tapi maranéhna geus loba harder maca jeung ngarti.
Urang moal delve kana detil teknis - naha jeung kumaha gawéna, sarta kami moal balik kana nuances nulis fungsi nested. Barina ogé, ieu artikel dedicated ka fungsi VPR, lain pituduh lengkep pikeun Excel.
Naon waé masalahna, rumusna janten langkung rumit! Kumaha upami urang ngenalkeun pilihan sanés pikeun tingkat komisi 50% pikeun anu ngajual anu ngahasilkeun penjualan langkung ti $ 50000. Sareng upami aya anu ngajual langkung ti $ 60000, aranjeunna bakal mayar komisi 60%?
Ayeuna rumus dina sél B2, sanajan ieu ditulis tanpa kasalahan, geus jadi lengkep unreadable. Kuring nyangka yén aya sababaraha anu hoyong nganggo rumus sareng 4 tingkat nyarang dina proyék-proyékna. Pasti aya cara anu langkung gampang?!
Sareng aya cara sapertos kitu! Fungsina bakal ngabantosan urang VPR.
Urang nerapkeun fungsi VLOOKUP pikeun ngajawab masalah
Hayu urang ngarobah desain tabel urang saeutik. Urang bakal ngajaga sadaya widang sareng data anu sami, tapi susun dina cara anu énggal sareng langkung kompak:
Candak momen sarta pastikeun tabel anyar Méja ongkos ngawengku data anu sarua sakumaha tabel bangbarung saméméhna.
Gagasan utama nyaéta ngagunakeun fungsi VPR pikeun nangtukeun laju tarif nu dipikahoyong nurutkeun tabel Méja ongkos gumantung kana volume jualan. Perhatikeun yén seller bisa ngajual barang pikeun jumlah nu teu sarua jeung salah sahiji lima thresholds dina tabél. Salaku conto, anjeunna tiasa ngajual $ 34988, tapi henteu aya jumlah sapertos kitu. Hayu urang tingali kumaha fungsina VPR bisa nungkulan kaayaan kitu.
Nyelapkeun fungsi VLOOKUP
Pilih sél B2 (dimana urang hoyong nyelapkeun rumus kami) sareng milarian VLOOKUP (VLOOKUP) dina Perpustakaan Fungsi Excel: Rumusna (rumus) > fungsi Perpustakaan (Fungsi Perpustakaan) > Milarian & Rujukan (Rujukan jeung arrays).
Hiji kotak dialog nembongan Fungsi Arguméntasi (Argumen fungsi). Urang eusian nilai argumen hiji-hiji, dimimitian ku Pilarian_nilai (Lookup_value). Dina conto ieu, ieu mangrupikeun jumlah penjualan tina sél B1. Nempatkeun kursor dina widang Pilarian_nilai (Lookup_value) tur pilih sél B1.
Salajengna, Anjeun kudu nangtukeun fungsi VPRdimana néangan data. Dina conto urang, ieu tabel Méja ongkos. Nempatkeun kursor dina widang Table_array (méja) tur pilih sakabéh méja Méja ongkosiwal headers.
Salajengna, urang kedah nangtukeun kolom mana pikeun nimba data tina ngagunakeun rumus urang. Kami museurkeun laju komisi, anu aya dina kolom kadua tabel. Ku alatan éta, pikeun argumen Col_index_num (Column_number) asupkeun nilai 2.
Sarta pamustunganana, urang ngenalkeun argumen panungtungan - Range_lookup (Interval_lookup).
penting: éta pamakéan argumen ieu ngajadikeun bédana antara dua cara nerapkeun fungsi VPR. Nalika damel sareng database, argumen Range_lookup (range_lookup) kudu salawasna mibanda nilai a PALSU (PALSU) pikeun milarian patandingan anu pas. Dina pamakéan kami fungsi VPR, urang kedah ngantepkeun kolom ieu kosong, atanapi lebetkeun nilai KODE BENER (BENER). Penting pisan pikeun milih pilihan ieu kalayan leres.
Sangkan leuwih jelas, urang bakal ngawanohkeun KODE BENER (BENER) di lapangan Range_lookup (Interval_lookup). Sanajan, lamun ninggalkeun widang kosong, ieu moal kasalahan, saprak KODE BENER nyaeta nilai standar na:
Kami parantos ngeusian sadaya parameter. Ayeuna urang pencét OK, sarta Excel nyieun rumus pikeun urang kalawan fungsi VPR.
Upami urang ékspérimén sareng sababaraha nilai anu béda pikeun jumlah penjualan, maka urang bakal mastikeun yén rumusna leres.
kacindekan
Nalika fungsi VPR jalan kalawan database, argumen Range_lookup (range_lookup) kedah nampi PALSU (PALSU). Jeung nilai diasupkeun salaku Pilarian_nilai (Lookup_value) kudu aya dina database. Dina basa sejen, éta néangan hiji patandingan pasti.
Dina conto anu urang tingali dina tulisan ieu, henteu kedah kéngingkeun patandingan anu pasti. Ieu kasus nalika fungsi VPR kudu pindah ka mode perkiraan pikeun mulangkeun hasil nu dipikahoyong.
Salaku conto: Kami hoyong nangtukeun tingkat naon anu dianggo dina itungan komisi pikeun salesperson kalayan volume penjualan $34988. Fungsi VPR mulih kami nilai 30%, nu leres pisan. Tapi naha rumus milih baris ngandung persis 30% teu 20% atawa 40%? Naon anu dimaksud ku pilarian perkiraan? Hayu urang jelas.
Nalika argumen Range_lookup (interval_lookup) boga nilai KODE BENER (BENER) atawa disingkahkeun, fungsi VPR iterates ngaliwatan kolom kahiji tur pilih nilai pangbadagna nu teu ngaleuwihan nilai lookup.
titik penting: Pikeun skéma ieu jalan, kolom kahiji tabel kudu diurutkeun dina urutan naek.