Saat Anda memiliki rumus kompleks yang bergantung pada beberapa variabel dan ingin memahami bagaimana mengubah input tersebut berdampak pada hasil secara efisien, tabel data analisis Bagaimana-Jika di Excel adalah alat yang ampuh. Ini memungkinkan Anda melihat semua kemungkinan hasil dengan cepat. Berikut panduan langkah demi langkah tentang cara membuat tabel data di Excel. Selain itu, kita akan membahas beberapa poin penting untuk menggunakan tabel data secara efektif dan mendemonstrasikan operasi lain seperti menghapus, mengedit, dan menghitung ulang tabel data.

Apa itu tabel data di Excel?

Buat tabel data satu variabel

• Tabel data berorientasi kolom
• Tabel data berorientasi baris
• Beberapa rumus untuk tabel data satu variabel

Buat tabel data dua variabel

Poin-poin penting menggunakan tabel data

Operasi lain untuk menggunakan tabel data

• Hapus tabel data
• Edit hasil tabel data
• Hitung ulang tabel data secara manual

Apa itu tabel data di Excel?

Di Excel, tabel data adalah salah satu alat Analisis Bagaimana-Jika yang memungkinkan Anda bereksperimen dengan nilai masukan berbeda untuk rumus dan mengamati perubahan pada keluaran rumus. Alat ini sangat berguna untuk mengeksplorasi berbagai skenario dan melakukan analisis sensitivitas, terutama ketika suatu rumus bergantung pada banyak variabel.

Ada dua jenis tabel data di Excel:

Tabel Data Satu Variabel: Hal ini memungkinkan Anda menganalisis pengaruh nilai yang berbeda dari satu variabel terhadap rumus. Anda dapat menyiapkan tabel data satu variabel dalam format berorientasi baris atau kolom, bergantung pada apakah Anda ingin memvariasikan input di seluruh baris atau di bawah kolom.

Tabel Data Dua Variabel: Jenis ini memungkinkan Anda melihat dampak perubahan dua variabel berbeda pada hasil rumus. Dalam tabel data dua variabel, Anda memvariasikan nilai di sepanjang baris dan kolom.

Buat tabel data satu variabel

Membuat tabel data satu variabel di Excel adalah keterampilan berharga untuk menganalisis bagaimana perubahan dalam satu masukan dapat memengaruhi berbagai hasil. Bagian ini akan memandu Anda melalui proses pembuatan tabel data satu variabel berorientasi kolom, berorientasi baris, dan beberapa rumus.

Luar biasa! Menggunakan Tab Efisien di Excel Seperti Chrome, Edge, Firefox dan Safari!
Hemat 50% waktu Anda, dan kurangi ribuan klik mouse untuk Anda setiap hari!

---

Tabel data berorientasi kolom

Tabel data berorientasi kolom berguna saat Anda ingin menguji bagaimana nilai-nilai yang berbeda dari satu variabel memengaruhi output rumus, dengan nilai-nilai variabel dicantumkan di bawah kolom. Mari kita perhatikan contoh keuangan sederhana:

Misalkan Anda sedang mempertimbangkan pinjaman sebesar $50,000, yang Anda rencanakan untuk dibayar kembali dalam jangka waktu 3 tahun (setara dengan 36 bulan). Untuk mengevaluasi pembayaran bulanan apa yang terjangkau berdasarkan gaji Anda, Anda tertarik untuk mengeksplorasi bagaimana berbagai suku bunga akan memengaruhi jumlah yang harus Anda bayarkan setiap bulan.

Langkah 1: Siapkan rumus dasar

Untuk menghitung pembayarannya, disini saya akan menetapkan bunganya sebesar 5%. Di sel B4, masukkan rumus PMT, yang menghitung pembayaran bulanan berdasarkan tingkat bunga, jumlah periode, dan jumlah pinjaman. Lihat tangkapan layar:

= -PMT($B$1/12, $B$2*12, $B$3)

Langkah 2: Cantumkan suku bunga dalam kolom

Di kolom, cantumkan suku bunga berbeda yang ingin Anda uji. Misalnya, cantumkan nilai dari 4% hingga 11% dengan kelipatan 1% dalam satu kolom, dan sisakan setidaknya satu kolom kosong di sebelah kanan untuk hasilnya. Lihat tangkapan layar:

Langkah 3: Buat tabel data

1. Di sel E2, masukkan rumus ini: = B4.
   
   Note: B4 adalah sel tempat rumus utama Anda berada, ini adalah rumus yang hasilnya ingin Anda lihat berubah saat Anda memvariasikan tingkat bunga.
   
2. Pilih rentang yang menyertakan rumus Anda, daftar suku bunga, dan sel yang berdekatan untuk hasilnya (misalnya, pilih D2 hingga E10). Lihat tangkapan layar:
   
3. Buka pita, klik pada Data Tab, lalu klik Analisis Bagaimana-Jika > Data Tabel, lihat tangkapan layar:
   
4. Dalam majalah Data Tabel kotak dialog, klik di Kolom Masukan sel kotak (karena kami menggunakan kolom untuk nilai input), dan pilih sel variabel yang direferensikan dalam rumus Anda. Dalam contoh ini, kita memilih B1 yang berisi tingkat bunga. Terakhir, klik OK tombol, lihat tangkapan layar:
   
5. Excel akan secara otomatis mengisi sel kosong yang berdekatan dengan setiap nilai variabel Anda (suku bunga berbeda) dengan hasil yang sesuai. Lihat tangkapan layar:
   
6. Terapkan format angka yang diinginkan pada hasil (Mata Uang) sesuai kebutuhan Anda. Sekarang, tabel data berorientasi kolom berhasil dibuat, dan Anda dapat dengan cepat membaca hasilnya untuk mengevaluasi jumlah pembayaran bulanan mana yang terjangkau bagi Anda ketika tingkat suku bunga berubah. Lihat tangkapan layar:
   

Tabel data berorientasi baris

Membuat tabel data berorientasi baris di Excel melibatkan pengaturan data Anda sehingga nilai variabel dicantumkan di seluruh baris, bukan di kolom. Dengan mengambil contoh di atas sebagai referensi, mari kita lanjutkan dengan langkah-langkah menyelesaikan pembuatan tabel data berorientasi baris di Excel.

Langkah 1: Cantumkan suku bunga secara berurutan

Tempatkan nilai variabel (suku bunga) dalam satu baris, pastikan setidaknya ada satu kolom kosong di sebelah kiri untuk rumus dan satu baris kosong di bawah untuk hasilnya, lihat tangkapan layar:

Langkah 2: Buat tabel data

1. Di sel A9, masukkan rumus ini: = B4.
   
2. Pilih rentang yang menyertakan rumus Anda, daftar suku bunga, dan sel yang berdekatan untuk hasilnya (misalnya, pilih A8 hingga I9). Lalu klik Data > Analisis Bagaimana-Jika > Data Tabel.
3. Dalam majalah Data Tabel kotak dialog, klik di Sel masukan baris kotak (karena kami menggunakan baris untuk nilai input), dan pilih sel variabel yang direferensikan dalam rumus Anda. Dalam contoh ini, kita memilih B1 yang berisi tingkat bunga. Terakhir, klik OK tombol, lihat tangkapan layar:
   
4. Terapkan format angka yang diinginkan pada hasil (Mata Uang) sesuai kebutuhan Anda. Sekarang, tabel data berorientasi baris telah dibuat, lihat tangkapan layar:
   

Beberapa rumus untuk tabel data satu variabel

Membuat tabel data satu variabel dengan beberapa rumus di Excel memungkinkan Anda melihat bagaimana perubahan satu masukan memengaruhi beberapa rumus berbeda sekaligus. Pada contoh di atas, bagaimana jika Anda ingin melihat perubahan suku bunga baik cicilan maupun total bunga? Berikut cara mengaturnya:

Langkah 1: Tambahkan rumus baru untuk menghitung total bunga

Di sel B5, masukkan rumus berikut untuk menghitung total bunga:

=B4*B2*12-B3

Langkah 2: Susun data sumber tabel data

Di kolom, cantumkan suku bunga berbeda yang ingin Anda uji, dan sisakan setidaknya dua kolom kosong di sebelah kanan untuk hasilnya. Lihat tangkapan layar:

Langkah 3: Buat tabel data:

1. Di sel E2, masukkan rumus ini: = B4 untuk membuat referensi perhitungan pelunasan pada data asli.
2. Di sel F2, masukkan rumus ini: = B5 untuk membuat referensi ke minat total pada data asli.
   
3. Pilih rentang yang menyertakan rumus Anda, daftar suku bunga, dan sel yang berdekatan untuk hasilnya (misalnya, pilih D2 hingga F10). Lalu klik Data > Analisis Bagaimana-Jika > Data Tabel.
4. Dalam majalah Data Tabel kotak dialog, klik di Sel masukan kolom kotak (karena kami menggunakan kolom untuk nilai input), dan pilih sel variabel yang direferensikan dalam rumus Anda. Dalam contoh ini, kita memilih B1 yang berisi tingkat bunga. Terakhir, klik OK tombol, lihat tangkapan layar:
   
5. Terapkan format angka yang diinginkan pada hasil (Mata Uang) sesuai kebutuhan Anda. Dan Anda dapat melihat hasil setiap rumus berdasarkan nilai variabel yang bervariasi.
   

Buat tabel data dua variabel

Tabel data dua variabel di Excel menampilkan dampak kombinasi berbeda dari dua kumpulan nilai variabel pada hasil rumus, menunjukkan bagaimana perubahan pada dua masukan rumus secara bersamaan memengaruhi hasilnya.

Di sini, saya telah menggambar sketsa sederhana untuk membantu Anda lebih memahami tampilan dan struktur tabel data dua variabel.

Berdasarkan contoh membuat tabel data satu variabel, sekarang mari kita pelajari cara membuat tabel data dua variabel di Excel.

Dalam kumpulan data di bawah ini, kami memiliki tingkat bunga, jangka waktu pinjaman dan jumlah pinjaman, dan telah menghitung pembayaran bulanan dengan menggunakan rumus ini: =-PMT($B$1/12, $B$2*12, $B$3) demikian juga. Di sini, kami akan fokus pada dua variabel utama dari data kami: suku bunga dan jumlah pinjaman, untuk mengamati bagaimana perubahan kedua faktor ini secara bersamaan mempengaruhi jumlah pembayaran kembali.

Langkah 1: Siapkan dua variabel yang berubah

1. Di kolom, cantumkan suku bunga berbeda yang ingin Anda uji. lihat tangkapan layar:
   
2. Secara berturut-turut, masukkan nilai jumlah pinjaman yang berbeda tepat di atas nilai kolom (mulai dari satu sel di sebelah kanan sel rumus), lihat tangkapan layar:
   

Langkah 2: Buat tabel data

1. Tempatkan rumus Anda di perpotongan baris dan kolom tempat Anda mencantumkan nilai variabel. Dalam hal ini, saya akan memasukkan rumus ini: = B4 ke dalam sel E2. Lihat tangkapan layar:
   
2. Pilih rentang yang mencakup jumlah pinjaman Anda, suku bunga, sel rumus, dan sel tempat hasilnya akan ditampilkan.
   
3. Lalu klik Data > Analisis Bagaimana-Jika > Data Tabel. Di kotak dialog Tabel Data:
   • Dalam majalah Sel masukan baris kotak, pilih referensi sel ke sel input untuk nilai variabel di baris (dalam contoh ini, B3 berisi jumlah pinjaman).
   • Dalam majalah Kolom masukan sell kotak, pilih referensi sel ke sel input untuk nilai variabel di kolom (B1 berisi suku bunga).
   • Dan kemudian, klik OK Tombol.
     
4. Sekarang Excel akan mengisi tabel data dengan hasil setiap kombinasi jumlah pinjaman dan tingkat suku bunga. Ini memberikan pandangan langsung tentang bagaimana kombinasi jumlah pinjaman dan suku bunga yang berbeda mempengaruhi pembayaran bulanan, menjadikannya alat yang berharga untuk perencanaan dan analisis keuangan.
   
5. Terakhir, Anda harus menerapkan format angka yang diinginkan pada hasil (Mata Uang) sesuai kebutuhan Anda.
   

Poin-poin penting menggunakan tabel data

• Tabel data yang baru dibuat harus berada pada lembar kerja yang sama dengan data aslinya.
• Keluaran tabel data bergantung pada sel rumus di kumpulan data sumber, dan perubahan apa pun pada sel rumus ini akan memperbarui keluaran secara otomatis.
• Setelah nilai dihitung menggunakan tabel data, nilai tersebut tidak dapat dibatalkan Ctrl + Z. Namun, Anda dapat memilih semua nilai secara manual dan menghapusnya.
• Tabel data menghasilkan rumus array, sehingga setiap sel dalam tabel tidak dapat diubah atau dihapus.

Operasi lain untuk menggunakan tabel data

Setelah Anda membuat tabel data, Anda mungkin memerlukan operasi tambahan untuk mengelolanya secara efektif, termasuk menghapus tabel data, mengubah hasilnya, dan melakukan penghitungan ulang manual.

Hapus tabel data

Karena hasil tabel data dihitung dengan rumus array, Anda tidak dapat menghapus nilai individual dari tabel data. Namun, Anda hanya dapat menghapus seluruh tabel data.

Pilih semua sel tabel data atau hanya sel dengan hasilnya, lalu tekan Delete tombol pada papan ketik.

Edit hasil tabel data

Memang benar, tidak mungkin mengedit sel individual dalam tabel data secara langsung, karena sel tersebut berisi rumus array yang dihasilkan Excel secara otomatis.

Untuk melakukan perubahan, Anda biasanya perlu menghapus tabel data yang ada dan kemudian membuat tabel baru dengan modifikasi yang diinginkan. Ini melibatkan penyesuaian rumus dasar atau nilai masukan, lalu menyiapkan tabel data lagi untuk mencerminkan perubahan ini.

Hitung ulang tabel data secara manual

Biasanya, Excel menghitung ulang semua rumus di semua buku kerja yang terbuka setiap kali ada perubahan. Jika tabel data besar yang berisi banyak nilai variabel dan rumus kompleks menyebabkan buku kerja Excel Anda melambat.

Untuk menghindari Excel secara otomatis melakukan penghitungan untuk semua tabel data setiap kali ada perubahan yang dilakukan di buku kerja, Anda bisa mengalihkan mode penghitungan dari Otomatis ke Manual. Silakan lakukan hal berikut:

Pergi ke Rumus tab, dan kemudian klik Opsi Perhitungan > Otomatis Kecuali Tabel Data, lihat tangkapan layar:

Setelah Anda mengubah pengaturan ini, saat Anda menghitung ulang seluruh buku kerja, Excel tidak lagi secara otomatis memperbarui penghitungan di tabel data Anda.

Jika Anda perlu memperbarui tabel data secara manual, cukup pilih sel tempat hasilnya (sel yang berisi rumus TABLE()) ditampilkan, lalu tekan F9 kunci.

Dengan mengikuti langkah-langkah yang diuraikan dalam panduan ini dan mengingat poin-poin penting, Anda dapat menggunakan tabel data secara efisien untuk kebutuhan analisis data Anda. Jika Anda tertarik untuk menjelajahi lebih banyak tip dan trik Excel, situs web kami menawarkan ribuan tutorial, silakan klik di sini untuk mengaksesnya. Terima kasih telah membaca, dan kami berharap dapat memberi Anda informasi bermanfaat lainnya di masa mendatang!