Fungsi Goal Seek di Excel: panduan lengkap dan contoh

La Fungsi Goal Seek di Excel Ini adalah salah satu alat yang luput dari perhatian hingga Anda menemukannya, dan Anda tak bisa membayangkan bagaimana Anda hidup tanpanya. Alat ini memungkinkan Anda memberi tahu Excel, "Inilah yang ingin saya capai," dan program itu sendiri akan menghitung nilai yang seharusnya dimiliki sel tertentu agar perhitungannya berhasil.

Daripada mencoba angka-angka dengan panik sampai Anda menemukan angka yang cocok, Anda dapat menggunakan Temukan Tujuan agar Excel dapat melakukan pekerjaan beratnyaSangat berguna dalam keuangan (pinjaman, investasi, anggaran pribadi), dalam masalah akademis (nilai, rata-rata, beasiswa) dan dalam perhitungan apa pun di mana Anda telah menetapkan rumus dan kehilangan sesuatu yang tidak diketahui.

Apa sebenarnya Goal Seek di Excel?

Buscar Objetivo Ini adalah bagian dari alat Analisis Bagaimana jika… di Excel. Alih-alih bertanya pada diri sendiri, "Jika saya mengubah data ini, apa yang terjadi pada hasilnya?", Anda membalikkan pendekatannya: Anda memberi tahu Excel hasil apa yang ingin Anda dapatkan dan memintanya untuk menghitung nilai yang seharusnya diambil oleh salah satu sel yang terlibat dalam rumus.

Dengan kata lain, alat ini bekerja “Kebalikan” dari apa yang biasanya Anda lakukanAnda sudah tahu nilai akhir yang dibutuhkan, dan Anda membiarkan Excel menemukan nilai input yang membuat rumus Anda menghasilkan hasil tersebut. Secara matematis, ini seperti menyelesaikan persamaan dengan satu variabel yang tidak diketahui tanpa harus mengisolasinya secara manual.

Fungsi ini tersedia di Excel dan juga di office suite lainnya seperti Dokumen ONLYOFFICEyang telah dimasukkan ke dalam spreadsheet mereka untuk menawarkan jenis analisis skenario yang sama. Dalam semua kasus, filosofinya sama: Tetapkan tujuan, pilih variabel, dan biarkan program berjalan berulang. sampai nilai yang cocok ditemukan.

Beberapa situasi umum di mana penggunaan Goal Seek sangat berguna adalah perhitungan amortisasi pinjaman, perencanaan tabungan pensiun, desain anggaran pribadi atau perusahaan dan analisis penjualan yang diperlukan untuk mencapai laba tertentu atau titik impas.

Konsep kunci sebelum menggunakan Goal Seek

Sebelum Anda mulai menggunakan alat ini, penting untuk memahami tiga ide berikut: sel target, nilai yang diinginkan, dan sel yang berubahJika Anda mengidentifikasi unsur-unsur ini dengan benar, sisanya mudah saja.

La sel sasaran Ini adalah sel tempat Anda menyimpan hasil rumus yang ingin Anda ambil nilai tertentu. Misalnya, bisa berupa rata-rata nilai akhir Anda, cicilan pinjaman bulanan, atau total keuntungan suatu produk. Penting: sel ini harus berisi rumus, bukan angka yang ditulis tangan.

El nilai yang diinginkan Ini adalah angka yang ingin dicapai sel target. Angka ini bisa berupa rata-rata minimum untuk mempertahankan beasiswa, pembayaran bulanan maksimum yang diizinkan anggaran Anda, atau keuntungan yang telah Anda tetapkan sebagai target bisnis Anda. Anda memutuskan nilai ini berdasarkan tujuan Anda..

La sel yang berubah Ini adalah data yang tidak diketahui dalam soal, data yang ingin Anda hitung di Excel. Data ini harus berupa sel yang merupakan bagian dari rumus di sel target. Ini bisa berupa saldo terutang, jumlah pinjaman, jangka waktu pelunasan, harga jual, jumlah unit terjual, dll.

Itu penting sel target bergantung, secara langsung atau tidak langsung, pada sel yang berubahJika tidak ada hubungan di antara keduanya dalam rumus, Goal Seek tidak akan dapat menemukan hasil apa pun. Pada dasarnya, alat ini menggantikan metode "coba-coba" klasik dengan proses iterasi otomatis.

Cara Kerja Goal Seek Langkah demi Langkah: Contoh Pinjaman

Contoh klasik untuk memahami Goal Seek adalah pinjaman dengan pembayaran berkalaBayangkan Anda tahu berapa banyak uang yang ingin Anda pinjam, berapa bulan Anda ingin membayarnya kembali, dan berapa pembayaran maksimum yang Anda mampu, tetapi Anda tidak tahu berapa tingkat bunga yang Anda perlukan agar angka-angka tersebut sesuai.

Jenis perhitungan ini menggunakan fungsi PEMBAYARAN (PMT) Fungsi Excel mengembalikan jumlah yang harus dibayarkan di setiap periode. Kuncinya adalah Anda sudah mengetahui pembayaran bulanannya, jadi Anda akan menggunakan Goal Seek agar Excel menyesuaikan suku bunga hingga mencapai jumlah tersebut.

1. Siapkan lembar kerja pinjaman

Pertama, itu adalah ide yang bagus mengatur data dalam tabel kecil Untuk menjaga semuanya tetap teratur, Anda dapat membuat sesuatu seperti ini di lembar baru:

• Dalam A1: “Jumlah pinjaman”. Di B1: modal yang ingin Anda minta, misalnya 100000.
• Dalam A2: “Jangka waktu dalam bulan”. Dalam B2: jumlah bulan, misalnya 180 (yang akan menjadi 15 tahun).
• Dalam A3: “Suku bunga”. Di B3Untuk saat ini, biarkan kosong, karena itu akan menjadi hal yang tidak diketahui.
• Dalam A4: “Pembayaran”. Di B4:di sini akan masuk rumus PEMBAYARAN.

Sekarang tulis rumus di sel B4. =PMT(B3/12;B2;B1)Anda membagi B3 dengan 12 karena suku bunga biasanya dinyatakan dalam satuan tahunan, dan Anda menghitung pembayaran bulanan. Karena B3 kosong, Excel akan mengasumsikan bunga 0% dan memberikan pembayaran bulanan hanya berdasarkan jangka waktu dan jumlah.

Dalam contoh khusus ini, Anda mungkin melihat bahwa biaya yang dihitung adalah sekitar 555,56Namun, nilai tersebut belum relevan bagi Anda. Yang penting adalah Anda sudah memilikinya. struktur rumus siap untuk menggunakan Temukan Target.

2. Jalankan Goal Seek untuk menemukan tingkat bunga

Setelah rumusnya siap, Anda sekarang dapat membuka alat analisis. Buka tab datos dan dalam kelompok perintah Untuk alat peramalan atau data, pilih Analisis hipotesis > Pencarian tujuanKotak dialog dengan tiga bidang akan terbuka.

Di dalam bingkai “Atur sel” Anda menentukan sel yang berisi rumus yang hasilnya ingin Anda perbaiki, dalam hal ini B4. Sel tersebut adalah sel target Anda, tempat perhitungan pembayaran bulanan menggunakan fungsi PMT berada.

Di lapangan “Keberanian untuk” Tulis jumlah yang sebenarnya ingin Anda bayarkan. Jika Anda ingin membayar 900 per bulan, tuliskan di sini. -900Tanda negatif penting karena, di Excel, pembayaran yang keluar dari kantong Anda dianggap jumlah negatif.

Di dalam bingkai “Mengubah sel” Masukkan referensi sel yang harus disesuaikan Excel untuk mencapai pembayaran tersebut. Dalam contoh ini, referensinya adalah B3, yang merupakan nilai suku bunga. Ketika Anda mengklik OK, Goal Seek akan mulai melakukan iterasi, mengubah suku bunga hingga menemukan nilai yang membuat pembayaran bulanan tetap pada -900.

Setelah selesai, alat akan menunjukkan kepada Anda jendela status dengan hasilAnda akan melihat suku bunga yang dihitung di B3 dan pembayaran baru di B4. Excel biasanya menemukan solusi yang cukup akurat jika rumusnya benar dan soalnya masuk akal secara numerik.

Untuk melengkapi semuanya, Anda bisa untuk format sel B3 sebagai persentase Dari tab Beranda, pilih format persentase dan sesuaikan jumlah angka desimal menggunakan ikon tambah atau kurangi angka desimal. Ini akan menampilkan suku bunga dalam format yang lebih mudah dibaca.

Contoh penggunaan lainnya: catatan, investasi, dan kredit

Kekuatan Goal Seek tidak terbatas pada pinjaman. Ia dapat diterapkan pada berbagai situasi sehari-hari di mana Anda memiliki tujuan numerik dan Anda kehilangan variabel yang memenuhi tujuan tersebut. Kita akan melihat beberapa kasus praktis yang sangat umum.

1. Mengetahui nilai yang Anda butuhkan pada ujian terakhir

Salah satu contoh yang paling intuitif adalah kualifikasiBayangkan Anda sudah memasukkan beberapa nilai dan hanya tinggal satu ujian lagi. Anda tahu nilai rata-rata minimum yang dibutuhkan untuk lulus mata kuliah atau mempertahankan beasiswa, tetapi Anda tidak yakin nilai apa yang dibutuhkan untuk ujian terakhir itu.

Misalkan Anda membutuhkan rata-rata 70 untuk bisa mengikuti ujian akhir atau melanjutkan beasiswa. Anda sudah mengikuti 3 ujian, masih ada satu ujian lagi, dan Anda ingin Excel memberi tahu Anda Nilai berapa yang perlu Anda dapatkan pada ujian keempat itu? sehingga rata-rata keseluruhannya setidaknya 70.

Pertama, masukkan nilai yang diketahui di kolom, misalnya, B2:B4. Kosongkan sel B5 karena akan menjadi nilai ujian yang tertunda, dan di sel lain, misalnya B7, tulis rumus rata-rata: =RATA-RATA(B2:B5)Itu akan menjadi sel target Anda.

Buka Pencarian Sasaran dari Data > Analisis What-if > Pencarian Sasaran dan Anda mengonfigurasinya seperti ini:

• Atur sel: B7 (sel dengan rumus rata-rata).
• Nilai untuk: 70 (rata-rata yang ingin Anda capai).
• Mengubah sel: B5 (nilai untuk ujian yang tertunda).

Setelah konfirmasi, Excel akan menghitung nilai yang seharusnya dimiliki B5 sehingga rata-rata di B7 adalah 70. Misalnya, mungkin memberi tahu Anda bahwa Anda memerlukan 85 pada ujian terakhir untuk mencapai rata-rata yang dibutuhkan. Ide yang sama dapat digunakan dengan rata-rata tertimbang menggunakan fungsi seperti SUMPRODUCT y SUMA, jika setiap pengujian memiliki bobot yang berbeda.

2. Rata-rata tertimbang dan nilai dengan bobot yang berbeda

Dalam banyak mata kuliah, tidak semua penilaian diberi bobot yang sama. Tugas mungkin diberi nilai 30%, kuis 20%, dan ujian akhir 50%, misalnya. Dalam kasus ini, alih-alih menggunakan rata-rata sederhana, Anda biasanya menggunakan rumus rata-rata tertimbang.

Cara umum untuk mengaturnya adalah dengan menempatkan peringkat di satu kolom dan bobot (dalam bentuk desimal atau persentase) di kolom lain. Anda dapat menggunakan kombinasi SUMPRODUCT(nilai;bobot)/SUM(bobot) untuk mendapatkan nilai akhir.

Sel yang berisi rumus rata-rata tertimbang akan menjadi sel target Anda. Kemudian, seperti sebelumnya, Anda menggunakan Goal Seek agar Excel menghitung rata-ratanya. menyesuaikan nilai ujian tertentu sampai nilai keseluruhan mencapai, katakanlah, 50% atau nilai yang Anda perlukan untuk lulus.

Dengan menggunakan pendekatan ini, Anda mungkin menemukan bahwa, misalnya, Anda membutuhkan 57,5% pada ujian akhir sehingga dengan bobot masing-masing komponen, nilai mata kuliah mencapai 50%.

3. Perencanaan investasi dan tabungan

Pencarian Target juga merupakan sekutu saat Anda mempersiapkan rencana investasi atau tabunganJika Anda sudah mengetahui perkiraan imbal hasil investasi dan jangka waktunya, Anda dapat mengajukan pertanyaan seperti: "Berapa banyak yang harus saya investasikan setiap bulan untuk mencapai jumlah akumulasi X?" atau "Berapa jumlah awal yang perlu saya investasikan jika saya ingin mencapai imbal hasil tertentu?"

Fungsi keuangan seperti FV (nilai masa depan), PV (nilai sekarang), RATE atau PEMBAYARAN itu sendiri. Anda membuat rumus yang menggambarkan situasi Anda (misalnya, nilai masa depan dari serangkaian iuran berkala) dan membiarkan iuran bulanan atau modal awal sebagai variabel.

Setelah model dirakit, Anda menggunakan Target Find untuk mengunci posisi Anda. tujuan penghematan atau profitabilitas Di sel hasil, Excel kemudian menyesuaikan kontribusi berkala atau jumlah investasi awal. Logikanya sama dengan pinjaman, tetapi diterapkan pada sisi tabungan atau investasi.

4. Kredit dan keterbatasan anggaran

Kembali ke pinjaman, Anda bisa melangkah lebih jauh. Bayangkan Anda telah menghitung dengan PEMBAYARAN berapa jumlah yang harus Anda bayar setiap tahun untuk pinjaman sebesar €12.000 selama 20 tahun dengan suku bunga 5%. Hasilnya adalah pembayaran sekitar €962,91 per tahun, tetapi Anggaran maksimum Anda adalah 900.

Dengan Goal Seek Anda dapat membingkai masalah secara terbalik: Anda ingin pembayaran tahunan menjadi -900 Dan Anda ingin jumlah pinjaman disesuaikan. Anda mengonfigurasi alat tersebut sehingga sel target adalah sel yang berisi hasil PEMBAYARAN, nilai yang diinginkan adalah -900, dan sel yang berubah adalah pokok pinjaman.

Hasilnya bisa berupa, misalnya, jumlah pinjaman sekitar 11.216Dengan cara ini, Anda tahu persis berapa banyak yang bisa Anda pinjam tanpa melebihi batas tahunan €900. Jika, alih-alih menetapkan pokok pinjaman, Anda ingin mempertahankan jumlah pinjaman tetap sama dan mengubah el tiempo Untuk pengembalian, Anda hanya perlu menentukan sel yang berisi jumlah tahun atau bulan sebagai sel yang berubah.

5. Analisis manfaat dan penjualan yang diperlukan

Dalam dunia bisnis, Goal Seek berguna untuk pertanyaan-pertanyaan seperti:Berapa banyak unit yang harus saya jual untuk mendapatkan 50.000?"atau pada harga berapa saya harus menjual untuk mencapai margin tertentu, mengingat biaya dan kapasitas produksi?"

Anda dapat menyusun rumus keuntungan seperti ini: (Harga – Biaya) × Unit terjual dan menggunakannya sebagai sel target. Tergantung kebutuhan Anda, Anda dapat menetapkan keuntungan yang diinginkan dan membiarkan sel yang berubah menjadi jumlah unit yang akan dijual atau harga satuan.

Misalnya, jika Anda menetapkan target keuntungan sebesar 50.000, Anda dapat meminta Goal Seek menghitung berapa banyak unit yang perlu dijual, menjaga harga dan biaya tetapAtau sebaliknya: tetapkan volume penjualan yang dibatasi oleh kapasitas produksi dan biarkan harga disesuaikan untuk mencapai laba yang diinginkan.

Perbedaan antara Goal Seek dan Solver

Hal yang penting adalah memahami di mana kegunaannya berakhir. Buscar Objetivo dan dimana yang satu dimulai? SolverAdd-in Excel lain yang sangat canggih. Keduanya digunakan untuk analisis skenario, tetapi tidak menyelesaikan jenis masalah yang sama.

Tujuan Pencarian dirancang untuk masalah dengan satu variabelDengan kata lain, Anda hanya dapat mengubah satu sel untuk mencoba mencapai nilai target. Jika Anda memiliki lebih dari satu sel yang tidak diketahui, hal ini tidak akan berhasil, dan di sinilah Solver berperan.

Solver memungkinkan Anda bekerja dengan beberapa sel variabel sekaligusAnda dapat menetapkan batasan (misalnya, sel tertentu tidak boleh negatif, batas maksimum tertentu tidak boleh dilampaui, dll.) dan memutuskan apakah tujuan Anda adalah memaksimalkan, meminimalkan, atau menyamakan nilai tertentu. Ini adalah alat optimasi yang jauh lebih komprehensif.

Misalnya, jika Anda ingin mengetahui nilai yang perlu Anda peroleh untuk mata pelajaran Bahasa Inggris dan Kimia agar rata-rata kedua mata pelajaran tersebut ditambah mata pelajaran lainnya adalah 84, maka Anda menghadapi masalah. masalah dengan dua variabelGoal Seek tidak akan berfungsi, tetapi Solver akan berfungsi. Dalam hal ini, Anda akan menetapkan sel target ke nilai rata-rata, menetapkan nilainya ke 84, dan menentukan bahwa sel yang dapat diubah adalah nilai Bahasa Inggris dan Kimia.

Contoh khas lainnya dengan Solver adalah memaksimalkan keuntungan di bawah kendala, seperti ketika sebuah perusahaan menjual sejumlah layanan, masing-masing dengan laba sendiri, dan ingin menemukan kombinasi unit untuk dijual yang memaksimalkan laba total dengan tetap memperhatikan kondisi tertentu (penjualan minimum untuk satu layanan, penjualan maksimum untuk layanan lain, batas keseluruhan unit, dsb.).

Masalah umum saat menggunakan Goal Seek dan cara menghindarinya

Meskipun Goal Seek sangat mudah digunakan, terkadang aplikasi ini mungkin tidak memberikan solusi atau malah memberikan hasil yang aneh. Dalam kebanyakan kasus, Kesalahannya terletak pada konfigurasi atau sifat numerik dari masalah tersebutbukan pada alat itu sendiri.

Penyebab umum adalah bahwa sel target tidak bergantung pada perubahan selJika sel yang Anda tunjukkan sebagai variabel tidak terlibat dalam rumus sel target (secara langsung atau tidak langsung), Excel tidak perlu menyesuaikan apa pun, dan pencarian tujuan akan tetap gagal.

Mereka juga bisa menyebabkan masalah bagi Anda referensi melingkarArtinya, rumus di mana sel bergantung pada dirinya sendiri. Hal ini dapat menyebabkan perhitungan tidak konvergen atau Excel menonaktifkan opsi tertentu. Sebaiknya periksa apakah rumus Anda konsisten dan tidak saling mendukung secara rekursif tanpa kontrol.

Hal lain yang perlu ditinjau adalah opsi iterasi Di Excel, jika jumlah maksimum iterasi atau perubahan maksimum yang diizinkan antar iterasi terlalu rendah, Goal Seek mungkin berhenti sebelum waktunya atau menghasilkan perkiraan yang tidak akurat. Parameter ini dapat disesuaikan di File > Opsi > Rumus, dengan menambah jumlah maksimum iterasi atau menyesuaikan perubahan maksimum.

Terakhir, ada baiknya untuk memeriksa apakah Sasaran yang Anda usulkan harus masuk akal secara numerik.Jika Anda meminta sesuatu yang mustahil mengingat batasan model Anda (misalnya, pembayaran bulanan yang sangat rendah untuk pinjaman besar dengan suku bunga yang sangat tinggi), wajar jika Excel tidak akan dapat menemukan nilai yang sesuai.

Alat Goal Seek di Excel, bila digunakan dengan bijak, menjadi jalan pintas yang ampuh untuk menjawab semua jenis pertanyaan numerik: alat ini memungkinkan Anda untuk berpindah dari "apa yang terjadi jika saya mengubah ini?" menjadi "inilah yang ingin saya capai, beri tahu saya apa yang perlu saya ubah," dan dengan itu Ini menghemat banyak waktu percobaan dan kesalahan. baik dalam mengelola keuangan pribadi maupun dalam menganalisis bisnis atau studi Anda.