Analisis Bagaimana-Jika Excel menawarkan Tabel Data. Ini nama yang buruk. Ini harus disebut Analisis Sensitivitas. Ini keren. Bacalah di sini.
Pencarian Tujuan memungkinkan Anda menemukan sekumpulan masukan yang mengarah ke hasil tertentu. Terkadang, Anda ingin melihat banyak hasil berbeda dari berbagai kombinasi input. Asalkan Anda hanya memiliki dua sel input untuk diubah, tabel data menyediakan cara cepat untuk membandingkan alternatif.
Menggunakan contoh pembayaran pinjaman, katakanlah Anda ingin menghitung harga untuk berbagai saldo pokok dan untuk berbagai persyaratan.
Pastikan rumus yang ingin Anda modelkan berada di pojok kiri atas rentang. Letakkan berbagai nilai untuk satu variabel di bawah kolom kiri dan berbagai nilai untuk variabel lain di bagian atas.
Dari tab Data, pilih Analisis Bagaimana-Jika, tabel data.
Anda memiliki nilai di sepanjang baris atas tabel input. Anda ingin Excel memasukkan nilai-nilai tersebut ke dalam sel input tertentu. Tentukan sel input itu sebagai Row Input Cell.
Anda memiliki nilai di sepanjang kolom kiri. Anda ingin itu dicolokkan ke sel input lain. Tentukan sel itu sebagai Sel Input Kolom.
Saat Anda mengklik OK, Excel akan mengulangi rumus di kolom kiri atas untuk semua kombinasi baris atas dan kolom kiri. Pada gambar di bawah, Anda melihat 60 pembayaran pinjaman yang berbeda berdasarkan berbagai hasil.
Perhatikan bahwa saya memformat hasil tabel agar tidak memiliki desimal dan menggunakan Home, Conditional Formatting, Color Scale untuk menambahkan bayangan merah / kuning / hijau.
Inilah bagian terbaiknya: Tabel ini "hidup". Jika Anda mengubah sel input di sepanjang kolom kiri atau baris atas, nilai dalam tabel akan dihitung ulang. Di bawah, nilai di sepanjang sisi kiri difokuskan pada kisaran $ 23K hingga $ 24K.
Terima kasih kepada Owen W. Green karena telah menyarankan tabel.
Menonton video
- Tiga alat bagaimana-jika di Excel
- Kemarin - Pencarian Tujuan
- Hari ini - Tabel Data
- Cocok untuk masalah dua variabel
- Trivia: fungsi array TABLE tidak dapat dimasukkan secara manual - ini tidak akan berfungsi
- Gunakan Skala Warna untuk mewarnai jawaban
- Bagaimana jika Anda memiliki 3 variabel untuk diubah? Skenario? Tidak! Salin lembar kerja
- Tabel lambat untuk dihitung: mode kalkulasi untuk Semua Kecuali Tabel
- Terima kasih kepada Owen W. Green karena telah menyarankan tip ini
Transkrip Video
Pelajari Excel dari podcast, episode 2034 - Bagaimana-Jika dengan Tabel Data!
Saya membuat podcasting seluruh buku ini, klik "i" di pojok kanan atas untuk masuk ke playlist!
Hari ini kita akan berbicara tentang alat kedua di bawah Analisis Bagaimana-Jika, kemarin kita berbicara tentang Pencarian Tujuan, hari ini kita akan membahas Tabel Data. Jadi kami memiliki model kecil yang bagus di sini, ini adalah model kecil, 3 sel input, satu rumus. Tetapi model ini dapat berupa ratusan sel input, ribuan baris, selama itu sampai pada satu jawaban akhir, dan kami ingin memodelkan jawaban ini untuk beberapa nilai yang berbeda dari 2-3 (?) Sel input. Misalnya, mungkin kami tertarik untuk melihat mobil yang berbeda, jadi di mana saja mulai dari 20000 ke atas, jadi saya akan memasukkan 20 dan 21000 di sana, ambil pegangan isian dan tarik, turunkan menjadi 28000. Di bagian atas kami ' sedang melihat persyaratan yang berbeda, jadi pinjaman 36 bulan, pinjaman 42 bulan, pinjaman 48 bulan, 54, 60, 66, dan bahkan 72.
Baiklah sekarang, langkah selanjutnya ini sepenuhnya opsional, tetapi sangat membantu saya untuk memikirkan hal ini, saya selalu mengubah warna nilai di bagian atas dan nilai di sepanjang kiri. Dan yang paling penting di sini adalah bahwa sel sudut itu, sel sudut yang paling penting itu, harus menjadi jawaban yang kita coba modelkan, oke. Jadi, Anda harus mulai memilih dari sel sudut itu dengan jawabannya, lalu memilih semua baris dan semua kolom. Jadi kita masuk ke Data, Analisis Bagaimana-Jika, dan Tabel Data, dan menanyakan dua hal di sini, dan inilah cara Anda memikirkannya. Dikatakan ada banyak item berbeda di sepanjang baris atas dalam tabel, saya ingin mengambil item itu, satu per satu, dan menyambungkannya ke model, di mana kita harus memasukkan? Jadi item ini, ini adalah istilah, mereka harus masuk ke sel B2. Lalu,ada banyak item di sepanjang kolom kiri, kami ingin mengambilnya, satu per satu, dan menyambungkannya ke B1, seperti itu, baiklah dan kami klik OK, BAM, model ini menjalankan model ini berulang kali .
Sekarang hanya sedikit pembersihan di sini, saya selalu masuk dan melakukan Rumah, dan mungkin 0 tempat desimal, seperti itu. Dan mungkin sedikit Pemformatan Bersyarat, Skala Warna, dan mari kita gunakan angka merah untuk angka besar dan hijau untuk angka kecil, hanya untuk memberi saya cara untuk melacak ini secara visual. Sekarang sepertinya jika kita menembak untuk $ 425, kita seperti, Anda tahu, di tempat ini atau tempat ini, atau Anda tahu, mungkin di sini, kita semua akan mendapatkan kita mendekati $ 425. Jadi saya dapat melihat berbagai peluang, berbagai kombinasi kita, untuk membawa kita ke nilai-nilai itu.
Sekarang beberapa hal, bagian di dalam sini, sebenarnya adalah rumus array besar, jadi = TABLE (B2, B1), input baris dan kolom. Ini aneh, Anda tidak diizinkan untuk mengetik ini, Anda hanya dapat membuat ini menggunakan Data, Analisis Bagaimana-Jika, Anda harus menggunakan kotak dialog itu. Jika Anda mencoba dan mengetik rumus itu, tekan Ctrl + Shift + Enter, itu tidak akan berfungsi, bukan? Jadi, ini adalah fungsi di Excel, tetapi jika Anda cukup pintar untuk mengetiknya, sayang sekali, itu tidak akan berfungsi, tetapi terus menghitung ulang. Jadi jika kita menentukan bahwa kita hanya melihat suku-suku dari 48, dan kita ingin melihat dalam kelompok 3 atau sesuatu seperti itu, jadi saat saya mengubah angka-angka ini, semua itu dihitung. Dalam kasus ini, ini hanya melakukan satu rumus untuk masing-masing, tetapi bayangkan jika kita melakukan 100 rumus, ini akan melambat secara dramatis. Jadi di sini, di bawah Rumus, di sana 'sebenarnya adalah opsi Opsi Penghitungan, Otomatis atau Manual, ada opsi ketiga yang mengatakan "Ya, hitung ulang semuanya kecuali untuk Tabel Data, jangan terus menghitung ulang tabel data". Karena ini bisa menjadi hambatan besar pada waktu kalkulasi.
Baiklah sekarang, tabel data sangat bagus ketika Anda memiliki dua variabel untuk diubah, tetapi kami memiliki tiga variabel untuk diubah. Bagaimana jika ada suku bunga yang berbeda, apakah saya merekomendasikan untuk pergi ke Scenario Manager? TIDAK, saya TIDAK PERNAH merekomendasikan untuk pergi ke Scenario Manager! Dalam hal ini kami memiliki 9x7, yaitu 63 skenario berbeda yang kami hitung di sini, untuk membuat 63 skenario Manajer Skenario berbeda akan membutuhkan waktu 2 jam, itu mengerikan. Saya tidak membahas ini di buku "MrExcel XL", karena ini adalah 40 tip terbaik. Ini mungkin ada di buku "Power Excel" saya dengan 567 misteri Excel terpecahkan, tapi saya yakin bahwa saya mengeluh tentang betapa menyedihkan penggunaannya, Anda tidak akan melihat saya melakukan Manajer Skenario di sini. Jika kami benar-benar harus melakukan ini untuk beberapa tingkat yang berbeda, hal terbaik yang harus dilakukan hanyalah Ctrl-drag, ambil sheet ini, Ctrl-drag, Ctrl-drag,Ctrl-seret, lalu ubah tarif di setiap lembar. Jadi jika kita bisa mendapatkan 5% atau 4,75% atau sesuatu seperti itu dan seterusnya, tidak ada cara mudah untuk mengaturnya untuk 3 variabel di Scenario Manager. Baiklah, "40 Tips Excel Terbaik Sepanjang Masa", semua di buku ini, Anda bisa membeli buku, klik "i" di pojok kanan atas.
Ringkasan episode hari ini: Ada tiga alat Bagaimana-Jika di Excel, kemarin kita berbicara tentang Pencarian Tujuan, hari ini Tabel Data. Ini mengagumkan untuk masalah 2-variabel, besok Anda akan melihat satu masalah dengan masalah 1-variabel. Fungsi larik tabel tidak dapat dimasukkan secara manual, tidak akan berfungsi, Anda harus menggunakan Data, Analisis Bagaimana-Jika, Tabel Data. Saya menggunakan skala warna, Beranda, Pemformatan Bersyarat, Skala Warna, untuk mewarnai jawaban. Jika Anda memiliki 3 variabel untuk diubah, Anda melakukan skenario? Tidak, cukup buat salinan lembar kerja atau salinan tabel, penghitungannya lambat, terutama dengan model yang rumit. Ada mode kalkulasi untuk Otomatis untuk semua kecuali tabel, dan Owen W. Green menyarankan untuk memasukkan fitur ini ke dalam buku.
Jadi terima kasih padanya, dan terima kasih sudah mampir, sampai jumpa lagi di netcast lain!
Unduh berkas
Unduh file contoh di sini: Podcast2034.xlsx
