Menghitung hasil majemuk dengan tabel data

Di Microsoft Excel, tabel data adalah bagian dari rangkaian perintah yang dikenal sebagai alat analisis What-If. Saat Anda membuat dan menganalisis tabel data, Anda melakukan analisis bagaimana-jika.

Analisis bagaimana-jika adalah proses mengubah nilai dalam sel untuk melihat bagaimana perubahan tersebut akan mempengaruhi hasil rumus pada lembar kerja. Misalnya, Anda bisa menggunakan tabel data untuk bervariasi suku bunga dan jangka waktu untuk pinjaman—untuk mengevaluasi potensi jumlah pembayaran bulanan.

Tipe analisis bagaimana-jika    

Ada tiga tipe alat analisis bagaimana-jika di Excel: skenario, tabel data, dan pencarian tujuan. Skenario dan tabel data menggunakan sekumpulan nilai input untuk menghitung kemungkinan hasil. Pencarian tujuan berbeda, menggunakan satu hasil dan menghitung kemungkinan nilai input yang akan menghasilkan hasil tersebut.

Seperti skenario, tabel data membantu Anda menjelajahi serangkaian kemungkinan hasil. Tidak seperti skenario, tabel data memperlihatkan semua hasil dalam satu tabel dalam satu lembar kerja. Menggunakan tabel data memudahkan untuk memeriksa berbagai kemungkinan secara sekilas. Karena Anda fokus pada satu atau dua variabel saja, hasilnya akan mudah dibaca dan dibagikan dalam bentuk tabel.

Tabel data tidak dapat mengakomodasi lebih dari dua variabel. Jika Anda ingin menganalisis lebih dari dua variabel, Sebagai gantinya Anda harus menggunakan skenario. Meskipun terbatas hanya satu atau dua variabel (satu untuk sel input baris dan satu untuk sel input kolom), tabel data bisa menyertakan nilai variabel yang berbeda sebanyak yang Anda inginkan. Skenario bisa memiliki maksimal 32 nilai berbeda, tapi Anda bisa membuat skenario sebanyak yang Anda inginkan.

Pelajari selengkapnya dalam artikel, Pengenalan analisis What-If.

Buat tabel data satu variabel atau dua variabel, bergantung pada jumlah variabel dan rumus yang perlu Anda uji.

Tabel data satu variabel    

Gunakan tabel data satu variabel jika Anda ingin melihat bagaimana nilai yang berbeda dari satu variabel dalam satu atau beberapa rumus akan mengubah hasil rumus tersebut. Misalnya, Anda dapat menggunakan tabel data satu variabel untuk melihat bagaimana perbedaan suku bunga memengaruhi pembayaran hipotret bulanan menggunakan fungsi PMT. Anda memasukkan nilai variabel dalam satu kolom atau baris, dan hasil ditampilkan dalam kolom atau baris yang berdekatan.

Dalam ilustrasi berikut ini, sel D2 berisi rumus pembayaran, =PMT(B3/12,B4,-B5), yang merujuk ke sel input B3.

Tabel data dua variabel    

Gunakan tabel data dua variabel untuk melihat bagaimana nilai yang berbeda dari dua variabel dalam satu rumus akan mengubah hasil rumus tersebut. Misalnya, Anda dapat menggunakan tabel data dua variabel untuk melihat bagaimana kombinasi suku bunga dan ketentuan pinjaman yang berbeda akan memengaruhi pembayaran hipotpi bulanan.

Dalam ilustrasi berikut, sel C2 berisi rumus pembayaran, =PMT(B3/12,B4,-B5), yang menggunakan dua sel input, B3 dan B4.

 

Penghitungan tabel data    

Setiap kali lembar kerja menghitung ulang, tabel data apa pun juga akan dihitung ulang—bahkan jika belum ada perubahan pada data. Untuk mempercepat penghitungan lembar kerja yang berisi tabel data, Anda bisa mengubah opsi Penghitungan untuk menghitung ulang lembar kerja secara otomatis tapi bukan tabel data. Untuk mempelajari selengkapnya, lihat bagian Mempercepat penghitungan dalam lembar kerja yang berisi tabel data.

Tabel data satu variabel berisi nilai inputnya baik dalam kolom tunggal (berorientasi kolom), atau di seluruh baris (berorientasi baris). Rumus apa pun dalam tabel data satu variabel harus merujuk hanya ke satu sel input.

Ikuti langkah-langkah ini:

1. Ketikkan daftar nilai yang ingin Anda ganti dalam sel input—baik ke bawah satu kolom atau di sepanjang satu baris. Biarkan beberapa baris dan kolom kosong di salah satu sisi nilai.

2. Lakukan salah satu hal berikut:

   • Jika tabel data berorientasi kolom (nilai variabel Anda berada dalam kolom), ketikkan rumus dalam sel satu baris di atas dan satu sel di sebelah kanan kolom nilai. Tabel data satu variabel ini berorientasi kolom, dan rumus dimuat dalam sel D2.
     
     
     
     Jika Anda ingin memeriksa efek berbagai nilai pada rumus lain, masukkan rumus tambahan dalam sel di sebelah kanan rumus pertama.

   • Jika tabel data berorientasi baris (nilai variabel Anda berada dalam baris), ketikkan rumus di sel satu kolom di sebelah kiri nilai pertama dan satu sel di bawah baris nilai.
     
     Jika Anda ingin memeriksa efek berbagai nilai pada rumus lain, masukkan rumus tambahan dalam sel di bawah rumus pertama.

3. Pilih rentang sel yang berisi rumus dan nilai yang ingin Anda ganti. Dalam gambar di atas, rentang ini adalah C2:D5.

4. Pada tab Data , klik Analisis Bagaimana-Jika > Tabel Data (dalam grup Alat Data atau grup Perkiraan Excel 2016).

5. Lakukan salah satu hal berikut:

   • Jika tabel data berorientasi kolom, masukkan referensi sel untuk sel input dalam bidang Sel input kolom . Dalam gambar di atas, sel input adalah B3.

   • Jika tabel data berorientasi baris, masukkan referensi sel untuk sel input dalam bidang Sel input baris .

     Catatan: Setelah membuat tabel data, Anda mungkin ingin mengubah format sel hasil. Dalam gambar, sel hasil diformat sebagai mata uang.

Rumus yang digunakan dalam tabel data satu variabel harus merujuk ke sel input yang sama.

Ikuti langkah-langkah berikut

1. Lakukan salah satu hal ini:

   • Jika tabel data berorientasi kolom, masukkan rumus baru dalam sel kosong di sebelah kanan rumus yang sudah ada di baris atas tabel data.

   • Jika tabel data berorientasi baris, masukkan rumus baru dalam sel kosong di bawah rumus yang sudah ada di kolom pertama tabel data.

2. Pilih rentang sel yang berisi tabel data dan rumus baru.

3. Pada tab Data , klik Analisis Bagaimana-Jika > Tabel Data (dalam grup Alat Data atau grup PerkiraanExcel 2016).

4. Lakukan salah satu hal berikut ini:

   • Jika tabel data berorientasi kolom, masukkan referensi sel untuk sel input dalam kotak Sel input kolom .

   • Jika tabel data berorientasi baris, masukkan referensi sel untuk sel input dalam kotak Sel input baris .

Tabel data dua variabel menggunakan rumus yang berisi dua daftar nilai input. Rumus harus merujuk ke dua sel input yang berbeda.

Ikuti langkah-langkah ini:

1. Dalam sel di lembar kerja, masukkan rumus yang merujuk ke dua sel input.

   Dalam contoh berikut—di mana nilai mulai rumus dimasukkan dalam sel B3, B4, dan B5, Ketikkan rumus =PMT(B3/12,B4,-B5) di sel C2.

2. Ketikkan satu daftar nilai input dalam kolom yang sama, di bawah rumus.

   Dalam hal ini, ketikkan suku bunga yang berbeda di sel C3, C4, dan C5.

3. Masukkan daftar kedua dalam baris yang sama dengan rumus—di sebelah kanannya.

   Ketikkan ketentuan pinjaman (dalam bulan) di sel D2 dan E2.

4. Pilih rentang sel yang berisi rumus (C2), baris dan kolom nilai (C3:C5 dan D2:E2), dan sel di mana Anda menginginkan nilai terhitung (D3:E5).

   Dalam hal ini, pilih rentang C2:E5.

5. Pada tab Data , dalam grup Alat Data atau grup Perkiraan (di Excel 2016), klik Analisis Bagaimana-Jika > Tabel Data (dalam grup Alat Data atau grup Perkiraan Excel 2016).

6. Dalam bidang Sel input baris , masukkan referensi ke sel input untuk nilai input dalam baris.
   Ketik sel B4 dalam kotak Sel input baris .

7. Dalam bidang Sel input kolom , masukkan referensi ke sel input untuk nilai input dalam kolom.
   Ketik B3 dalam kotak Sel input kolom .

8. Klik OK.

Contoh tabel data dua variabel

Tabel data dua variabel dapat memperlihatkan bagaimana kombinasi suku bunga dan ketentuan pinjaman yang berbeda akan memengaruhi pembayaran hipotpi bulanan. Dalam gambar di sini, sel C2 berisi rumus pembayaran, =PMT(B3/12,B4,-B5), yang menggunakan dua sel input, B3 dan B4.

Saat Anda mengatur opsi penghitungan ini, tidak ada penghitungan tabel data yang terjadi ketika penghitungan ulang dilakukan di seluruh buku kerja. Untuk menghitung ulang tabel data Anda secara manual, pilih rumusnya lalu tekan F9.

Ikuti langkah-langkah ini untuk meningkatkan kinerja penghitungan:

1. Klik Opsi > File > Rumus.

2. Di bagian Opsi penghitungan , di bawah Hitung, klik Otomatis kecuali untuk tabel data.

   Tips: Secara opsional, pada tab Rumus , klik panah pada Opsi Penghitungan, lalu klik Otomatis Kecuali Tabel Data (dalam grup Penghitungan ).

Anda bisa menggunakan beberapa alat Excel lainnya untuk melakukan analisis bagaimana-jika jika Anda memiliki tujuan tertentu atau kumpulan data variabel yang lebih besar.

Pencarian Tujuan

Jika Anda mengetahui hasil yang diharapkan dari rumus, tetapi tidak mengetahui nilai input apa yang diperlukan rumus untuk mendapatkan hasil tersebut, gunakan fitur Goal-Seek. Lihat artikel Menggunakan Pencarian Tujuan untuk menemukan hasil yang Anda inginkan dengan menyesuaikan nilai input.

Excel Solver

Anda bisa menggunakan add-in Solver Excel untuk menemukan nilai optimal untuk sekumpulan variabel input. Solver bekerja dengan sekelompok sel (disebut variabel keputusan, atau sel variabel saja) yang digunakan dalam menghitung rumus dalam sel tujuan dan batasan. Solver menyesuaikan nilai di dalam sel variabel keputusan untuk memenuhi batas pada sel batasan dan memberikan hasil yang Anda inginkan untuk sel tujuan. Pelajari selengkapnya di artikel ini: Tentukan dan selesaikan masalah dengan menggunakan Solver.