Cukup asring, kudu ngitung hasil akhir kanggo macem-macem kombinasi data input. Mangkono, pangguna bakal bisa ngira-ngira kabeh opsi sing bisa kanggo tumindak, pilih wong sing menehi hasil interaksi nggoleki, lan, pungkasanipun, milih pilihan paling optimal. Ing Excel, ana alat khusus kanggo tugas iki - "Tabel Data" ("Lookup table"). Ayo ngerteni carane nggunakake kanggo nindakake skenario ing ndhuwur.
Deleng uga: Pilihan parameter ing Excel
Nggunakake tabel data
Piranti "Tabel Data" iki dirancang kanggo ngetung asil karo beda variasi siji utawa loro variabel sing ditetepake. Sawise pitungan, kabeh opsi bisa ditampilake ing wangun tabel, sing diarani matrik analisis faktor. "Tabel Data" nuduhake klompok alat Analisis apa-apasing diselehake ing pita ing tab "Data" ing blok "Nggarap data". Sadurunge Excel 2007, alat iki ngagem jeneng. "Lookup table"sing luwih akurat dituduhake saka jeneng saiki.
Tabel panelusur bisa digunakake ing pirang-pirang kasus. Contone, pilihan khas yaiku yen sampeyan kudu ngetung jumlah pembayaran silihan saben wulan kanthi beda variasi periode kredit lan jumlah kredit, utawa periode kredit lan tarif kapentingan. Alat iki uga bisa digunakake nalika nganalisis model proyek investasi.
Nanging sampeyan uga kudu ngerti yen nggunakake gedhe alat iki bisa nyebabake sistem rem, amarga data terus dikalalake. Mulane, dianjurake supaya ora nggunakake alat iki ing tataran tabular cilik kanggo ngrampungake masalah sing padha, ananging kanggo nerapake rumus rumus nggunakake marker isi.
Aplikasi sing diyakini "Data Tabel" mung ing jangkoan tabular gedhe, nalika nyalin rumus bisa njupuk wektu akeh, lan sajrone prosedur kasebut, kasalahan kasalahan tambah. Nanging sanajan ing kasus iki, dianjurake kanggo mateni recalculation otomatis rumus ing sawetara tabel lookup, supaya ora diakibatake ing sistem.
Perbedaan utama antarane macem-macem nggunakake tabel data yaiku jumlah variabel sing ana ing pitungan: siji utawa loro variabel.
Metode 1: gunakake alat kanthi variabel siji
Sanalika ayo padha nimbang opsi nalika tabel data digunakake karo siji nilai variabel. Njupuk conto sing paling khas saka kredit.
Dadi, saiki kita ana kahanan kredit ing ngisor iki:
- Periode utang - 3 taun (36 sasi);
- Jumlah utang - 900.000 rubel;
- Tingkat bunga - 12,5% saben taun.
Pembayaran digawe ing pungkasan periode pambayaran (sasi) kanthi nggunakake rencana anuitas, yaiku ing saham sing padha. Ing wektu sing padha, ing awal kabeh periode etangan, pembayaran kapentingan nggawe bagean penting saka pembayaran, nanging minangka badan nyusut, pembayaran kapentingan mudhun, lan jumlah repayment awak dhewe mundhak. Pembayaran total, sing kasebut ing ndhuwur, tetep ora diganti.
Sampeyan kudu ngetung apa jumlah pembayaran saben wulan, sing kalebu mbayar pajeg awak lan pembayaran bunga. Iki, Excel duwe operator PMT.
PMT Iku kalebu klompok fungsi finansial lan tugasé kanggo ngetung pambayaran silihan bulanan jinis annuity adhedhasar jumlah awak utang, istilah etangan lan tingkat bunga. Sintaks kanggo fungsi iki kaya mangkene.
= PMT (tingkat; nper; ps; bs; ketik)
"Bet" - Alesan nemtokake tingkat kapentingan pembayaran kredit. Indikator disetel kanggo periode kasebut. Periode mbayari kita sasi siji. Mulane, tingkat tahunan 12,5% kudu diremehake menyang nomer sasi ing taun, yaiku, 12.
"Kper" - Pitakonan sing nemtokake jumlah periode kanggo kabeh periode etangan. Contone, periode iki sasi siji, lan wektu silihan 3 taun utawa 36 sasi. Mangkono, jumlah periode bakal awal 36.
"PS" - pitakonan sing nemtokake nilai saiki saka silihan, yaiku, ukuran awak pinjaman nalika ditokake. Ing kasus kita, angka iki 900.000 rubel.
"BS" - pitakonan sing nuduhaké ukuran awak silihan ing wektu pembayaran penuh. Alami, indikator iki bakal padha karo nol. Argumentasi iki opsional. Yen sampeyan mlayu, dianggep padha karo nomer "0".
"Ketik" - uga pilihan opsional. Dheweke ngandhani babagan nalika pembayaran bakal digawe: ing wiwitan periode (parameter - "1") utawa ing pungkasan periode (parameter - "0"). Nalika kita elinga, bayaran kita digawe ing pungkasan sasi tanggalan, yaiku, nilai argumen iki bakal padha "0". Nanging, yen indikator iki ora diwatesi, lan kanthi standar, yen ora dianggo, angka kasebut bakal dianggep "0", banjur ing conto kasebut ora bisa digunakake ing kabeh.
- Dadi, kita terus menyang pitungan. Pilih sel ing sheet endi angka sing dihasilake bakal ditampilake. Kita klik tombol "Pasang fungsi".
- Wiwit Wisaya Fungsi. Nggawe transisi menyang kategori "Financial", milih saka dhaftar jeneng "PLT" lan klik tombol "OK".
- Sawisé iki, ana aktivasi saka jendhela argumen fungsi ing ndhuwur.
Sijine kursor ing lapangan "Bet"banjur klik ing sel ing sheet kanthi nilai saka tingkat kapentingan taunan. Minangka sampeyan bisa ndeleng, koordinat sing ditampilake langsung ing lapangan. Nanging, nalika kita elinga, kita butuh tingkat saben wulan, lan mulane kita dibagi asil 12 (/12).
Ing lapangan "Kper" kanthi cara sing padha, kita nglebokake koordinat sel-sel istilah kredit. Ing kasus iki, ora ana sing kudu dipérang.
Ing lapangan "Ps" sampeyan kudu nemtokake koordinat sel sing ngemot nilai awak kredit. We do it. Kita uga sijine tandha ing ngarep koordinat sing dituduhake. "-". Titik yaiku fungsi kasebut PMT kanthi standar, menehi asil pungkasan karo tandha negatif, kanthi nimbang babagan pembayaran silihan saben wulan mundhut. Nanging kanggo kajelasan, kita butuh tabel data dadi positif. Mulane, kita sijine tandha "minus" sadurunge salah sawijining argumen fungsi. Minangka dikenal, penggandaan "minus" ing "minus" pungkasanipun menehi plus.
Ing lapangan "Bs" lan "Ketik" Kita ora nglebokake data ing kabeh. Kita klik tombol "OK".
- Sakwise kuwi, operator ngitung lan nampilake ing sel sing wis ditetepake hasil pembayaran saben wulan - 30108,26 rubles. Nanging masalah iku sing nyilih bisa mbayar paling gedhe 29.000 rubel saben sasi, yaiku, kudu nggoleki kahanan sing menehi bank kanthi tingkat kapentingan sing luwih murah, utawa nyuda badan pinjaman, utawa ngluwihi istilah pinjaman. Ngetung macem-macem opsi kanggo tumindak bakal mbantu kita tabel lookup.
- Kanggo miwiti, gunakake tabel lookup karo siji variabel. Ayo kita nliti babagan nilai saben wulan pembayaran saben wulan sing beda-beda kanthi variasi ing tingkat taunan, mulai saka 9,5% taunan lan pungkasan 12,5% pa karo langkah 0,5%. Kabeh kondisi liyane sing ora diowahi. Tarik sawetara tabel, jeneng kolom sing cocog karo variasi saka tingkat kapentingan. Kanthi baris iki "Pembayaran saben wulan" ninggalake kaya. Sèl sing sepisanan kudu ngemot formula sing diitung sadurungé. Kanggo informasi luwih lengkap, sampeyan bisa nambah garis "Total jumlah silihan" lan "Total Kapentingan". Kolom ing ngendi pitungan kasebut rampung tanpa header.
- Sabanjure, kita ngetung jumlah total silihan ing kahanan saiki. Kanggo nindakake iki, pilih sel pisanan saka baris kasebut. "Total jumlah silihan" lan ngembangake isi sèl "Pembayaran saben wulan" lan "Pinjaman istilah". Sawise klik iki ing Ketik.
- Kanggo ngetung jumlah total kapentingan miturut kahanan saiki, kanthi cara sing padha, kita nyuda nilai saka awak utang saka jumlah total silihan. Kanggo nampilake asil ing layar klik tombol kasebut. Ketik. Mangkono, kita njaluk jumlah sing kita overpay nalika bali silihan.
- Saiki wektu kanggo aplikasi alat. "Tabel Data". Pilih kabeh tabel, kajaba kanggo jeneng saben baris. Sawise iku pindhah menyang tab "Data". Klik tombol ing pita Analisis apa-apasing diselehake ing grup alat "Nggarap data" (ing Excel 2016, klompok alat) "Prakiraan"). Banjur menu cilik mbukak. Ing kono kita milih posisi "Data Tabel ...".
- Jendhela cilik mbukak, sing kasebut "Tabel Data". Minangka sampeyan bisa ndeleng, duwe rong lapangan. Awit kita bisa nyinaoni salah sawijining variabel, kita kudu mung siji. Amarga owah-owahan variabel kita dumadi ing kolom, kita bakal nggunakake lapangan "Ganti nilai miturut kolom ing". Kita nyelehake kursor ing kono, banjur klik ing sèl ing set data awal, sing ngemot nilai saiki persen. Sawise koordinat sèl ditampilake ing lapangan, klik tombol "OK".
- Piranti iki ngitung lan ngisi kabeh jangkauan tabel kanthi nilai sing cocog karo opsi tingkat kapentingan sing beda. Yen sampeyan nglebokake kursor ing sembarang elemen saka tabel iki, sampeyan bisa ndeleng yen garis rumus ora nuduhake formula pitungan pembayaran biasa, nanging formula khusus saka array non-pecah. Mangkono, ora bisa maneh ngganti angka-angka ing sel individu. Mbusak asil pitungan mung bisa digabungake, lan ora kasebut.
Kajaba iku, bisa dicathet yen angka pembayaran saben wulan ing 12,5% saben taun, ditampa kanthi nggunakake tabel lookup, cocog karo nilai ing tingkat kapentingan sing padha ditampa kanthi migunakake fungsi PMT. Iki maneh mbuktikake bener saka pitungan.
Sawise nganalisa rangking tabel iki, kudu dikatutake, sing kita tingali, mung kanthi tingkat 9,5% saben taun, tingkat pembayaran saben wulan sing ditrima (kurang saka 29.000 rubel) ditampa.
Lesson: Pitungan pembayaran annuity ing Excel
Metode 2: nggunakake alat kanthi rong variabel
Mesthi, angel banget, yen kabeh nyata, kanggo nemokake bank-bank sing ngetokake silihan ing 9,5% saben taun. Mulane, ayo deleng opsi apa sing ana kanggo nandur modal ing tingkat pembayaran saben wulan sing bisa ditampa kanggo macem-macem kombinasi variabel liyane: ukuran awak silihan lan periode pinjaman. Ing wektu sing padha, tingkat kapentingan tetep ora berubah (12,5%). Alat kasebut bakal mbantu kita tugas iki. "Tabel Data" nggunakake rong variabel.
- Gambar tataran tabel anyar. Saiki istilah kredit bakal dituduhake ing jeneng asli (saka 2 nganti 6 taun ing sasi ing taun siji-siji), lan ing larik - ukuran awak silihan (saka 850000 nganti 950000 rubles ing tambahan 10000 rubel). Ing kasus iki, penting banget yen sèl ing formula rumus kasebut dumunung (ing kasus kita PMT), dumunung ing tapel wates jeneng baris lan kolom. Tanpa kondisi iki, alat kasebut ora bisa digunakake nalika nggunakake rong variabel.
- Banjur pilih kabeh rentang tabel asil, kalebu jeneng kolom, larik lan sèl kanthi formula PMT. Pindhah menyang tab "Data". Kaya ing wektu sadurunge, klik tombol. Analisis apa-apaing kelompok alat "Nggarap data". Ing dhaptar sing mbukak, pilih item kasebut "Data Tabel ...".
- Jendhela alat diwiwiti. "Tabel Data". Ing kasus iki, kita perlu loro lapangan. Ing lapangan "Ganti nilai miturut kolom ing" kita nemtokake koordinat sel sing ngemot istilah silihan ing data utamane. Ing lapangan "Diganti angka kanthi larik ing" nemtokake alamat sèl saka paramèter pisanan sing ngemot nilai awak saka silihan Sawise kabeh data wis ditemokake. Kita klik tombol "OK".
- Program iki nggawe pitungan lan ngisi rentang tabel karo data. Ing persimpangan saka kolom lan kolom, saiki bisa mirsani carane persis pembayaran saben wulan, karo jumlah sing cocog karo kapentingan taunan lan periode krediting kasebut.
- Minangka sampeyan bisa ndeleng, cukup akeh nilai. Kanggo ngatasi masalah liyane bisa uga ana liyane. Mulane, supaya asil output luwih visual lan langsung nemtokake nilai kasebut ora gawe marem kondisi sing diwenehake, sampeyan bisa nggunakake alat visualisasi. Ing kasus kita bakal format format sing kondisional. Pilih kabeh angka saka sawetara tabel, ora kalebu baris lan kolom kolom.
- Pindhah menyang tab "Ngarep" lan klik ing lambang "Conditional Formatting". Dumunung ing kothak piranti. "Gaya" ing tape. Ing menu sing mbukak, pilih item kasebut "Aturan sel kanggo sel". Ing dhaptar tambahan klik ing posisi "Kurang ...".
- Ngisor iki, jendhela setelan format sing kondisional mbukak. Ing sisih kiwa kita nemtokake nilai kasebut, kurang saka sel sing bakal dipilih. Minangka kita elinga, kita kepenak karo kondisi ing kang pembayaran saben wulan ing silihan bakal kurang 29000 rubles. Ketik angka iki. Ing kolom tengen, sampeyan bisa milih warna pilihan, sanajan sampeyan bisa ninggalake kanthi standar. Sawise kabeh setelan sing dibutuhake wis dicithak, klik tombol. "OK".
- Sawise kabeh, kabeh sel sing angka sing cocog karo kondisi ndhuwur bakal disorot warna.
Sawise nganalisa dhaptar meja, sampeyan bisa nggolek sawetara kesimpulan. Minangka sampeyan bisa ndeleng, kanthi periode silihan saiki (36 sasi), supaya sampeyan bisa ngetrapake jumlah bayaran saben wulan, ing ndhuwur, sampeyan kudu njupuk silihan sing ora ngluwihi 8.600.000.00 rubel, yaiku, 40.000 kurang saka rencana awal.
Yen kita isih duwe utang ing jumlah 900.000 rubel, banjur istilah silihan kudu 4 taun (48 sasi). Mung ing kasus iki, jumlah pembayaran saben wulan ora ngluwihi wates sing wis ana ing 29.000 rubel.
Mangkono, njupuk kauntungan saka urutane tabel iki lan nganalisa pro lan kontra saka saben opsi, peminjam bisa nggawe keputusan tartamtu babagan syarat kredit, milih pilihan sing paling cocog karo kabutuhan.
Mesthine, tabel lookup bisa digunakake ora mung kanggo ngetung opsi kredit, nanging uga kanggo ngatasi akeh masalah liyane.
Pelajaran: Format Formulir ing Excel
Umumé, kudu dicathet menawa tabel panelusur minangka alat sing praktis banget lan prasaja kanggo nemtokake asil saka macem-macem kombinasi saka variabel. Kanthi nggunakake formasi bersyarat bebarengan karo iku, sampeyan bisa nggambarake informasi sing ditampa.
