Membuat Data Table dengan Satu atau Dua Variabel dengan Excel 2010 By Aurino Djamaris -March 13, 2012
Daftar Isi (Contents) Data Tables in Excel Satu Variable .............................................................................................2 Data Tables in Excel Dua Variable ..............................................................................................8 Bibliography ............................................................................................................................. 10
Daftar Gambar (Figures) Gambar 1 Tabel Pembayaran Hutang ..........................................................................................2 Gambar 2 Memasukkan Rumus PMT .........................................................................................3 Gambar 3. Memilih lokasi Data Tabel Excel ...............................................................................4 Gambar 4. Toolbar What-if Data Tabel .......................................................................................4 Gambar 5. Data Table Dialog Box ..............................................................................................4 Gambar 6. Memasukkan Input Cell ke Kotak Dialog ...................................................................5 Gambar 7. Hasil Data Table dengan Rumus PMT .......................................................................5 Gambar 8. Data Tabel dengan Input Mendatar ............................................................................6 Gambar 9. Range Terpilih untuk Data Tabel Mendatar ...............................................................6 Gambar 10. What-if - Data Table Toolbar ...................................................................................7 Gambar 11. Hasil Data Tabel PMT Layout Mendatar ..................................................................8 Gambar 12 Entri awal Data Table dengan 2 Variabel ..................................................................9 Gambar 13 Proyeksi Penjualan 2012 setelah Menggunakan What-If-Analysis Data Table ......... 10
Data Tables in Excel Satu Variable 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.xlsx (Djamaris, 2012) 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, (lihat: Gambar 1 Tabel Pembayaran Hutang)
Gambar 1 Tabel Pembayaran Hutang
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.
Gambar 2 Memasukkan Rumus PMT
Kita meletakkan fungsi PMT (lihat:Gambar 2 Memasukkan Rumus 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 Gambar 3. Memilih lokasi Data Tabel Excel.
Gambar 3. Memilih lokasi Data Tabel Excel
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"
Gambar 4. Toolbar What-if Data Tabel
Laku Klik Data Table… sehingga muncul kotak dialog
Gambar 5. Data Table Dialog Box
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:
Gambar 6. Memasukkan Input Cell ke Kotak Dialog
Klik OK maka excel akan mengisi sel-sel yang berada di bawah D3, yaitu D4, D5, D6 menjadi sebagai berikut:
Gambar 7. Hasil Data Table dengan Rumus PMT
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:
Gambar 8. Data Tabel dengan Input Mendatar
Maka pilihan kita adalah sel B4 s/d E5:
Gambar 9. Range Terpilih untuk Data Tabel Mendatar
Kemudian kita lakukan langkah sebagai berikut:
Dari menu Excel, Klik Data Cari Data Tools group Klik panah kecil sebelah kanan "What if Analysis"
Gambar 10. What-if - Data Table Toolbar
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:
Gambar 11. Hasil Data Tabel PMT Layout Mendatar
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.
Data Tables in Excel Dua Variable Untuk membuat data table dengan dua variabel yang dapat dipergunakan dalam analisis what-if dalam Excel 2010, maka Anda dapat memasukkan dua range nilai input untuk rumus (formula): rumus nilai untuk sel Row Input dalam dialog box Data Table sepanjang baris pertama tabel dan range nilai untuk sel Column Input Cell dalam dialog box ke bawah pada kolom pertama tabel tersebut. Kemudian masukan formula (atau dengan mengcopy) ke dalam sel yang terletak pada perpotongan antara baris dan kolom nilai input. Berikut adalah contoh dan cara untuk membuat data table dengan dua variabel untuk membantu Anda memahami fasilitas excel ini. Gambar 1 di bawah ini menggambarkan Proyeksi Penjualan yang menggunakan dua variabel yang dipergunakan dalam menghitung proyeksi penjualan tahun 2012. Tingkat pertumbuhan (%) terhadap penjualan tahun lalu (di sel B3) dan pengeluaran (di sel B4). Dan rumusnya pertumbuhan di sel B5 adalah =B2+(B2*B3)-(B2*B4)
Gambar 12 Entri awal Data Table dengan 2 Variabel
Perhitungan Proyeksi penjualan dan pengeluaran dengan persentase pertumbuhan yang mungkin terjadi untuk dimasukkan ke dalam data table dengan dua variabel. Pada Kolom dimasukkan data pertumbuhan mulai dari 1% s/d 5.5% dalam kolom B dalam range B8:B17, dan pada baris dimasukan persentase pengeluaran dalam range C7:F7. Berikut langkah untuk membuat data table seperti Gambar 1. 1. Copy rumus yang dimasukkan ke sel B5 ke sel B7 dengan mengetikkan = (tanda sama dengan) dan klik sel B5. Karena Anda hendak membuat data table dengan dua variabel maka rumus harus dicopykan ke perpotongan antara baris dan kolom nilai input dalam hal ini adalah sel B7. 2. Selanjutnya pilih range B7:H17. Rangen data table termasuk rumus yang telah dicopy sebelumnya. 3. Pilih What-If Analysis→Data Table dalam kelompok Data Tools pada tab Data. Excel akan membuka dialog box Data Table dan menunggu Anda untuk memasukkan alamat sel ke Row Input Cell.
4. Klik sel B4 untuk memasukkan alamat sel $B$4, ke text box Row Input Cell. 5. Klik text box dari Column Input Cell dan klik sell B3 untuk memasukkan alamat , $B$3. 6. Kalau sudah diisi kedua textbox tersebut, maka klik OK. Excel akan mengisi sel kosong dari data table dengan rumus TABLE dengan menggunakan B4 sebagai Row Input Cell dan B3 sebagai Column Input Cell.
Gambar 13 Proyeksi Penjualan 2012 setelah Menggunakan What-If-Analysis Data Table
Proyeksi penjualan Sales sesuai dengan persentase pertumbuhan penjualan dan pengeluaran dibuat dalan sel C8:H17. Rumur array {=TABLE(B4,B3)} yang dibuat oleh Excel adalah untuk data table dengan dua variabel dengan menyatakan Row Input Cell (B4) dan Column Input Cell (B3). Karena satu rumus ini dipakai untuk range C8:H17, maka Anda tidak dapat merubah salah satu sel dari range ini.
Bibliography Djamaris, A. (2012, 3 7). Data Tables dalam Excel. Retrieved 3 12, 2012, from Applied Business Computation: http://abc.aurino.com/?p=415
Microsoft Corporation. (n.d.). Calculate multiple results by using a data table. Retrieved 3 13, 2012, from Office: http://office.microsoft.com/en-001/excel-help/calculate-multipleresults-by-using-a-data-table-HP010072656.aspx
