Menghitung hasil majemuk dengan tabel data

Tabel data adalah rentang sel di mana Anda bisa mengubah nilai dalam beberapa sel dan menemukan jawaban yang berbeda untuk masalah. Contoh yang baik dari tabel data menggunakan fungsi PMT dengan jumlah pinjaman dan suku bunga yang berbeda untuk menghitung jumlah yang terjangkau pada pinjaman hipotek rumah. Bereksperimen dengan nilai berbeda untuk mengamati variasi terkait dalam hasil adalah tugas umum dalam analisis data.

Di Microsoft Excel, tabel data merupakan bagian dari rangkaian perintah yang dikenal sebagai alat analisis bagaimana-jika. Saat Anda menyusun dan menganalisis tabel data, Anda sedang melakukan analisis bagaimana-jika.

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

Catatan: Anda dapat melakukan penghitungan yang lebih cepat dengan tabel data dan Visual Basic for Applications (VBA). Untuk informasi selengkapnya, lihat tabel data What-if Excel: perhitungan lebih cepat dengan VBA.

Tipe analisis What-if    

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

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

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

Pelajari selengkapnya dalam artikel, pengenalan analisis bagaimana-jika.

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

Tabel data satu-variabel    

Gunakan tabel data satu-variabel jika Anda ingin melihat perbedaan nilai satu variabel dalam satu atau beberapa rumus akan mengubah hasil rumus tersebut. Misalnya, Anda bisa menggunakan tabel data satu-variabel untuk melihat bagaimana perbedaan suku bunga mempengaruhi pembayaran hipotek bulanan dengan menggunakan fungsi PMT. Anda memasukkan nilai variabel dalam satu kolom atau baris, dan hasilnya ditampilkan di kolom atau baris yang berdekatan.

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

Tabel data dengan satu variabel

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 bisa menggunakan tabel data dua-variabel untuk melihat bagaimana kombinasi berbeda dari suku bunga dan ketentuan pinjaman akan mempengaruhi pembayaran hipotek bulanan.

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

Tabel data dengan dua variabel
 

Perhitungan tabel data    

Setiap kali lembar kerja dihitung ulang, semua tabel data juga akan dihitung ulang — bahkan jika tidak ada perubahan pada data. Untuk mempercepat perhitungan 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 perhitungan dalam lembar kerja yang berisi tabel data.

Tabel data satu variabel berisi nilai inputnya baik dalam satu kolom (berorientasi kolom) maupun di seluruh baris (berorientasi baris). Setiap rumus dalam tabel data satu-variabel harus merujuk ke satu sel inputsaja.

Ikuti langkah-langkah berikut:

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

  2. Lakukan salah satu hal berikut ini:

    • Jika tabel data berorientasi pada kolom (nilai variabel Anda berada dalam kolom), ketikkan rumus di sel satu baris di atas dan satu sel di sebelah kanan kolom nilai. Tabel data satu variabel ini berorientasi pada kolom, dan rumus terkandung dalam sel D2.

      Tabel data dengan satu variabel

      Jika Anda ingin memeriksa efek dari berbagai nilai pada rumus lainnya, masukkan rumus tambahan dalam sel di sebelah kanan rumus pertama.

    • Jika tabel data berorientasi pada baris (nilai variabel Anda berada dalam baris), ketikkan rumus di kolom sel satu di sebelah kiri nilai pertama dan satu sel di bawah baris nilai.

      Jika Anda ingin memeriksa efek dari berbagai nilai pada rumus lainnya, 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 ini:

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

    • Jika tabel data berorientasi pada baris, masukkan referensi sel untuk sel input di bidang sel input baris .

      Catatan: Setelah Anda 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 berikut:

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

    • Jika tabel data berorientasi pada baris, masukkan rumus baru dalam sel kosong di bawah rumus yang 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 pada kolom, masukkan referensi sel untuk sel input dalam kotak sel input kolom .

    • Jika tabel data berorientasi pada 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 berikut:

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

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

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

    Dalam kasus ini, ketikkan suku bunga berbeda dalam sel C3, C4, dan C5.

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

    Ketikkan persyaratan pinjaman (dalam bulan) dalam sel D2 dan E2.

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

    Dalam kasus ini, pilih rentang C2: E5.

  5. Pada tab data , di grup alat data atau grup perkiraan (dalam Excel 2016), klik tabel analisis bagaimana-jika >data (dalam grup alat data atau grup perkiraan Excel 2016). 

  6. Di bidang sel input baris , masukkan referensi ke sel input untuk nilai input dalam baris.
    Ketikkan sel B4 dalam kotak sel input baris .

  7. Di bidang sel input kolom , masukkan referensi ke sel input untuk nilai input dalam kolom.
    Ketikkan B3 dalam kotak sel input kolom .

  8. Klik OK.

Contoh tabel data dua-variabel

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

Tabel data dengan dua variabel

Saat Anda mengatur opsi perhitungan ini, tidak ada perhitungan data-tabel 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 perhitungan:

  1. Lakukan salah satu hal berikut ini:

    • Di Excel 2007, klik tombol Microsoft Office Gambar tombol Office , klik opsi Excel, lalu klik kategori rumus .

    • Di semua versi lainnya, 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 perhitungan, lalu klik otomatis kecuali tabel data (dalam grup perhitungan ).

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

Pencarian Tujuan

Jika Anda tahu hasil yang diharapkan dari rumus, tapi tidak tahu persis apa nilai input yang diperlukan rumus untuk mendapatkan hasil tersebut, gunakan fitur pencarian tujuan. Lihat artikel menggunakan pencarian tujuan untuk menemukan hasil yang Anda inginkan dengan menyesuaikan nilai input.

Solver Excel

Anda bisa menggunakan Add-in Solver Excel untuk menemukan nilai optimal untuk sekumpulan variabel input. Solver bekerja dengan sekelompok sel (disebut variabel keputusan, atau hanya sel variabel) yang digunakan dalam komputasi 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 dalam artikel ini: menentukan dan menyelesaikan masalah dengan menggunakan Solver.

Dengan menyambungkan angka yang berbeda ke dalam sel, Anda bisa dengan cepat menemukan jawaban yang berbeda untuk masalah. Contoh hebat adalah menggunakan fungsi PMT dengan tingkat suku bunga dan periode pinjaman yang berbeda (dalam bulan) untuk mencari tahu berapa banyak pinjaman yang dapat Anda beli untuk rumah atau mobil. Masukkan angka Anda ke dalam rentang sel yang disebut tabel data.

Di sini, tabel data adalah rentang sel B2: D8. Anda dapat mengubah nilai dalam B4, jumlah pinjaman, dan pembayaran bulanan di kolom D diperbarui secara otomatis. Menggunakan suku bunga 3,75%, D2 mengembalikan pembayaran bulanan $1.042,01 menggunakan rumus ini: = PMT (C2/12, $B $3, $B $4).

Rentang sel ini, B2:D8, adalah sebuah tabel data

Anda bisa menggunakan satu atau dua variabel, tergantung pada jumlah variabel dan rumus yang ingin Anda uji.

Gunakan uji satu-variabel untuk melihat bagaimana perbedaan nilai satu variabel dalam rumus akan mengubah hasilnya. Misalnya, Anda dapat mengubah suku bunga untuk pembayaran hipotek bulanan dengan menggunakan fungsi PMT. Anda memasukkan nilai variabel (suku bunga) dalam satu kolom atau baris, dan hasilnya ditampilkan di kolom atau baris di dekatnya.

Dalam buku kerja Live ini, sel D2 berisi rumus pembayaran =PMT (C2/12, $B $3, $B $4). Sel B3 adalah sel variabel , di mana Anda bisa menyambungkan panjang istilah yang berbeda (jumlah periode pembayaran bulanan). Di sel D2, fungsi PMT plugs di suku bunga 3.75%/12, 360 bulan, dan $225.000 pinjaman, dan menghitung pembayaran bulanan $1.042,01.

Gunakan uji dua-variabel untuk melihat bagaimana nilai yang berbeda dari dua variabel dalam rumus akan mengubah hasilnya. Misalnya, Anda bisa menguji kombinasi berbeda dari suku bunga dan jumlah periode pembayaran bulanan untuk menghitung pembayaran hipotek.

Dalam buku kerja Live ini, sel C3 berisi rumus pembayaran, =PMT ($B $3/12, $B $2, B4), yang menggunakan dua sel variabel, B2 dan B3. Di sel C2, fungsi PMT plugs di suku bunga 3.875%/12, 360 bulan, dan $225.000 pinjaman, dan menghitung pembayaran bulanan $1.058,03.

Perlu bantuan lainnya?

Anda dapat bertanya kapan saja kepada para ahli di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Answers, atau menyarankan peningkatan maupun fitur baru di User Voice Excel.

Catatan:  Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda? Berikut adalah artikel dalam bahasa Inggris untuk referensi.

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×