Data Tables dalam Excel

By Aurino Djamaris

Dalam Excel, Data Table adalah cara untuk melihat hasil berbeda-beda dengan mengubah sebuah sel masukan dalam rumus Anda. Sebagai contoh, jika kita akan mengubah tingkat bunga, dan melihat berapa bunga yang akan dikenakan dari pinjaman sebesar 100.000.000 setiap bulannya. Tingkat bunga merupakan sel input. Dengan memerintahkan kepada Excel untuk mengubah input ini, maka kita akan dapat dengan cepat melihat pembayaran bulanan yang berbeda-beda. Sebagai contoh kita ingin tahu berapa banyak yang harus kita bayar kembali setiap bulannya jika tingkat bunga adalah 24 persen per tahun. Akan tetapi bank lain menawarkan tingkat bunga lain misalnya 22 persen per tahun, 20 persen per tahun, dan 18 persen per tahun.

Download file ContohDataTable

Untuk membuat perhitungan tersebut kita memakai formula PMT dengan syntax:

PMT(rate, nper, pv, fv, type)

Namun dalam perhitungan ini kita hanya perlu tiga parameter jadi:

PMT(rate, nper, pv)

Dimana:

Rate berarti tingkat bunga. Parameter atau argumen kedua, nper, adalah periode pembayaran atau berapa bulan Anda harus membayar kembali pinjaman. Argumen ketiga, pv, adalah nilai pinjaman saat ini atau seberapa banyak Anda ingin meminjam.

Baiklah kita membuat spreadsheet baru dengan tingkat bunga 24%, Periode 5 tahun atau 60 bulan dan jumlah pinjaman Rp. 100.000.000, sebagai berikut:


Kemudian di D2 kita masukan “Pembayaran Cicilan/bulan”, dan di D3 kita masukan formula PMT() untuk menghitung besarnya pembayaran cicilan per bulan yaitu:

=PMT(B4/12,B5,-B6)

Nilai Rate dimasukan ke dalam rumus harus dibagi 12 terlebih dahulu karena pembayaran bulanan sedangkan tingkat bunga adalah tahunan. Sedangkan periode (nper) langsung dimasukan. Untuk nilai pinjaman (pv) kita masukan nilai minus (-), karena kita anggap sebagai pinjaman, sehingga nilai pembayaran per bulan (PMT)nantinya akan mendapatkan nilai positif.


Kita meletakkan fungsi PMT di sel D3 karena sel D3 berada satu baris di atas dan satu kolom di samping kanan angka tingkat bunga 22%. Nantinya pembayaran bulanan dengan bunga masing-masing berada di sel D4 sampai dengan D6. Excel memang menetapkan Data Table layout sedemikian rupa.

Agar Excel dapat menghitung total pembayaran bulanan baru berdasarkan bungan yang kita masukan ke dalam sel C4 s/d C6 sesuai dengan fungsi yang telah kita tuliskan pada sel D4.

Jadi sorotlah sel C3 sampai dengan D6 seperti gambar di bawah ini:


Setelah sel C3 s/d D6 dipilih, maka kita dapat membuat Data Table Excel, sehingga hasilnya adalah pembayaran bulanan baru. Caranya:

  • Dari menu Excel, Klik Data
  • Cari Data Tools group
  • Klik panah kecil sebelah kanan “What if Analysis”

     


  • Laku Klik Data Table… sehingga muncul kotak dialog


Pada kotak dialog, yang perlu diisi adalah Row input cell atau Column input cell. Kita menginginkan Excel untuk mengisi ke bawah, arah kolom. Jadi kita perlu kotak teks kedua pada dialog box ” Column input cell “. Jika kita ingin mengisi baris, kita akan menggunakan ” Row input cell “.

Yang kita anggap sebagai Sel masukan sel yang berisi tingkat bunga. Sel ini adalah sel yang harus diubah oleh Excel.

Jadi klik di dalam Column input cell dan masukkan B4:


Klik OK maka excel akan mengisi sel-sel yang berada di bawah D3, yaitu D4, D5, D6 menjadi sebagai berikut:


Jadi kalau kita mendapatkan tingkat bungan 18% maka pembayaran bulanan adalah Rp2.639.442,-

Jika Anda pilih sel D4 s/d D6 maka dalam kotak Formula akan tampil

{=TABLE(,B4)}

Yang berarti bahwa sel tersebut diperoleh dari input sel B4.

 

Bagaimana kalau kita ingin bentuk hasil mendatar atau berada dalam satu baris

Misalnya dengan bentuk sebagai berikut:


Maka pilihan kita adalah sel B4 s/d E5:


Kemudian kita lakukan langkah sebagai berikut:

  • Dari menu Excel, Klik Data
  • Cari Data Tools group
  • Klik panah kecil sebelah kanan “What if Analysis”

     


  • Laku Klik Data Table… sehingga muncul kotak dialog


Yang kita anggap sebagai Sel masukan sel yang berisi tingkat bunga. Sel ini adalah sel yang harus diubah oleh Excel.

Jadi klik di dalam Row input cell dan masukkan B4:


Klik OK maka excel akan mengisi sel-sel yang berada di kanan B5, yaitu C5, D5, E5 menjadi sebagai berikut:


Sama halnya dengan perhitungan melalui kolom, kalau kita mendapatkan tingkat bunga 18% maka pembayaran bulanan adalah Rp2.639.442,74

Jika Anda pilih sel C5 s/d E5 maka dalam kotak Formula akan tampil

{=TABLE(B4,)}

Yang berarti bahwa sel tersebut diperoleh dari input sel B4.

 


Leave a Reply

Your email address will not be published. Required fields are marked *