Paragraf
We bubar darmabakti artikel kanggo salah siji saka fungsi Excel paling migunani disebut VPR lan nuduhake carane bisa digunakake kanggo extract informasi sing dibutuhake saka database menyang sel lembar kerja. Kita uga nyatakake yen ana rong kasus panggunaan kanggo fungsi kasebut VPR lan mung siji sing ngurusi pitakon database. Ing artikel iki, sampeyan bakal sinau cara liyane sing kurang dikenal kanggo nggunakake fungsi kasebut VPR ing Excel.
Yen sampeyan durung nindakake iki, mula maca artikel pungkasan babagan fungsi kasebut VPR, amarga kabeh informasi ing ngisor iki nganggep yen sampeyan wis ngerti prinsip sing diterangake ing artikel pisanan.
Nalika nggarap database, fungsi VPR pengenal unik liwati, kang digunakake kanggo ngenali informasi kita arep kanggo golek (Contone, kode produk utawa nomer identifikasi customer). Kode unik iki kudu ana ing basis data, yen ora VPR bakal nglaporake kesalahan. Ing artikel iki, kita bakal ndeleng cara nggunakake fungsi kasebut VPRnalika id ora ana ing database. Minangka fungsi VPR diuripake kanggo mode kira-kira, lan milih apa data kanggo nyedhiyani kita nalika kita arep kanggo golek soko. Ing kahanan tartamtu, iki persis apa sing dibutuhake.
Tuladha saka urip. We nyetel tugas
Ayo digambarake artikel iki kanthi conto nyata - ngitung komisi adhedhasar macem-macem metrik dodolan. Kita bakal miwiti kanthi pilihan sing gampang banget, banjur mboko sithik bakal rumit nganti siji-sijine solusi rasional kanggo masalah kasebut yaiku nggunakake fungsi kasebut. VPR. Skenario awal kanggo tugas fiktif kita kaya ing ngisor iki: yen salesman nggawe luwih saka $30000 ing dodolan ing setahun, banjur komisi iku 30%. Yen ora, komisi mung 20%. Ayo dilebokake ing wangun tabel:
Sing bakul ngetik data dodolan ing sel B1, lan rumus ing sel B2 nemtokake tingkat komisi sing bener sing bisa dikarepake penjual. Sabanjure, tingkat asil digunakake ing sel B3 kanggo ngetung total komisi sing kudu ditampa bakul (mung multiplying sel B1 lan B2).
Sisih paling menarik saka tabel ana ing sel B2 - iki minangka rumus kanggo nemtokake tingkat komisi. Rumus iki ngemot fungsi Excel sing diarani IF (YEN). Kanggo sing maca sing ora kenal karo fungsi iki, aku bakal nerangake cara kerjane:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
kawontenan minangka argumen fungsi sing njupuk nilai salah siji KODE BENER (TRUE), utawa lupute (PALSU). Ing conto ing ndhuwur, ekspresi B1
Apa bener B1 kurang saka B5?
Utawa sampeyan bisa ngomong kanthi beda:
Apa bener yen jumlah total dodolan kanggo taun kurang saka nilai ambang?
Yen kita mangsuli pitakonan iki YES (TRUE), banjur fungsi bali nilai yen bener (nilai yen TRUE). Ing kasus kita, iki bakal dadi nilai sel B6, yaiku tingkat komisi nalika total dodolan ana ing sangisore ambang. Yen kita mangsuli pitakonan NO (PALSU) banjur bali nilai yen palsu (nilai yen FALSE). Ing kasus kita, iki minangka nilai sel B7, yaiku tingkat komisi nalika total dodolan ngluwihi ambang.
Kaya sing sampeyan ngerteni, yen kita njupuk total dodolan $ 20000, kita entuk tingkat komisi 2% ing sel B20. Yen kita ngetik nilai $40000, tingkat komisi bakal ganti 30%:
Iki cara kerja meja kita.
We complicate tugas
Ayo dadi luwih angel. Ayo nyetel batesan liyane: yen bakul entuk luwih saka $40000, tingkat komisi mundhak dadi 40%:
Kabeh katon prasaja lan jelas, nanging rumus ing sel B2 dadi luwih rumit. Yen katon rapet ing rumus, sampeyan bakal weruh sing argumen katelu saka fungsi IF (IF) diuripake menyang fungsi lengkap liyane IF (YEN). Konstruksi iki diarani nesting of functions into each other. Excel kanthi seneng ngidini konstruksi kasebut, lan malah bisa digunakake, nanging luwih angel diwaca lan dingerteni.
Kita ora bakal nyelidiki rincian teknis - kenapa lan cara kerjane, lan kita ora bakal pindhah menyang nuansa nulis fungsi nested. Sawise kabeh, iki minangka artikel khusus kanggo fungsi kasebut VPR, dudu pandhuan lengkap kanggo Excel.
Apa wae, rumus kasebut dadi luwih rumit! Apa yen kita ngenalake opsi liyane kanggo tingkat komisi 50% kanggo para penjual sing nggawe dodolan luwih saka $ 50000. Lan yen ana wong sing adol luwih saka $ 60000, bakal mbayar komisi 60%?
Saiki rumus ing sel B2, sanajan ditulis tanpa kesalahan, wis dadi ora bisa diwaca. Aku sing ana sawetara sing pengin nggunakake rumus karo 4 tingkat nesting ing proyèk. Mesthi ana cara sing luwih gampang?!
Lan ana cara kuwi! Fungsi kasebut bakal mbantu kita VPR.
Kita nggunakake fungsi VLOOKUP kanggo ngatasi masalah kasebut
Ayo ngganti desain meja kita. Kita bakal nyimpen kabeh kolom lan data sing padha, nanging ngatur kanthi cara sing anyar lan luwih kompak:
Njupuk wayahe lan priksa manawa meja anyar Tabel Rate kalebu data sing padha karo tabel ambang sadurunge.
Ide utama yaiku nggunakake fungsi kasebut VPR kanggo nemtokake tarif tarif sing dikarepake miturut tabel Tabel Rate gumantung saka volume dodolan. Wigati dimangerteni menawa bakul bisa ngedol barang kanggo jumlah sing ora padha karo siji saka limang batesan ing meja. Contone, dheweke bisa ngedol $ 34988, nanging ora ana jumlah kasebut. Ayo ndeleng carane fungsi VPR bisa ngadhepi kahanan kaya mengkono.
Nglebokake fungsi VLOOKUP
Pilih sel B2 (ing ngendi kita pengin nglebokake rumus) lan goleki VLOOKUP (VLOOKUP) ing Pustaka Fungsi Excel: Rumus (rumus) > Fungsi Perpustakaan (Fungsi Pustaka) > Goleki & Referensi (Referensi lan susunan).
Kothak dialog katon Fungsi Argumen (Argumen fungsi). Kita ngisi nilai-nilai argumen siji-siji, diwiwiti kanthi Lookup_value (Nilai_goleki). Ing conto iki, iki minangka jumlah total dodolan saka sel B1. Sijine kursor ing lapangan Lookup_value (Lookup_value) banjur pilih sel B1.
Sabanjure, sampeyan kudu nemtokake fungsi VPRngendi kanggo nggoleki data. Ing conto kita, iki minangka tabel Tabel Rate. Sijine kursor ing lapangan Tabel_array (Tabel) lan pilih kabeh tabel Tabel Ratekajaba kanggo header.
Sabanjure, kita kudu nemtokake kolom kanggo ngekstrak data nggunakake rumus kita. Kita kasengsem ing tingkat komisi, sing ana ing kolom kapindho tabel. Mulane, kanggo argumentasi Col_index_num (Column_number) ketik nilai 2.
Lan pungkasane, kita ngenalake argumen pungkasan - Range_lookup (Interval_lookup).
penting: iku nggunakake argumen iki sing ndadekake prabédan antarane rong cara aplikasi fungsi VPR. Nalika nggarap database, argumen Range_lookup (range_lookup) kudu tansah duwe nilai lupute (PALSU) kanggo nggoleki sing cocog. Ing kita nggunakake fungsi VPR, kita kudu ninggalake kolom iki kosong, utawa ketik nilai KODE BENER (BENER). Penting banget kanggo milih pilihan iki kanthi bener.
Kanggo nggawe luwih cetha, kita bakal ngenalake KODE BENER (BENER) ing lapangan Range_lookup (Interval_lookup). Senajan, yen sampeyan ninggalake kolom kosong, iki ora bakal dadi kesalahan, wiwit KODE BENER iku nilai standar:
Kita wis ngisi kabeh paramèter. Saiki kita pencet OK, lan Excel nggawe rumus kanggo kita kanthi fungsi VPR.
Yen kita eksprimen karo sawetara nilai beda kanggo jumlah dodolan, kita bakal mesthekake yen rumus bisa digunakake kanthi bener.
kesimpulan
Nalika fungsi VPR dianggo karo database, argumen Range_lookup (range_lookup) kudu nampa lupute (PALSU). Lan nilai ngetik minangka Lookup_value (Lookup_value) kudu ana ing database. Ing tembung liyane, iku looking for match pas.
Ing conto sing wis dideleng ing artikel iki, ora perlu golek sing cocog. Iki kasus nalika fungsi VPR kudu ngalih menyang mode kira-kira kanggo ngasilake asil sing dikarepake.
Tuladhane: Kita pengin nemtokake tarif apa sing digunakake ing pitungan komisi kanggo salesperson kanthi volume penjualan $34988. Fungsi VPR ngasilake kita nilai 30%, kang pancen bener. Nanging kok rumus milih baris ngemot persis 30% lan ora 20% utawa 40%? Apa tegese telusuran kira-kira? Ayo dadi cetha.
Nalika argumentasi Range_lookup (interval_lookup) nduweni nilai KODE BENER (TRUE) utawa tilar, fungsi VPR iterates liwat kolom pisanan lan milih nilai paling gedhe sing ora ngluwihi nilai goleki.
Titik penting: Supaya skema iki bisa digunakake, kolom pisanan tabel kudu diurutake kanthi urutan munggah.