Pencarian Sasaran di Excel: Kupas Tuntas


Fitur Pencarian Sasaran di Excel adalah alat analisis what if.  Dengan asumsi bahwa Anda mengetahui hasil tunggal yang ingin Anda capai, Goal Seek memungkinkan Anda mencapai tujuan tersebut dengan bekerja mundur untuk menyesuaikan secara matematis satu variabel tertentu dalam persamaan.

Goal Seek sering digunakan dalam model keuangan tetapi juga umum digunakan dalam penjualan, pemilihan, dan jenis prakiraan lainnya.

Perintah Goal Seek di Excel terletak di tab Data, di dalam grup perintah Forecast, di bawah menu dropdown What-If Analysis.

Pencarian Sasaran Excel


Contoh 1

Untuk mengilustrasikan cara kerjanya dalam contoh sederhana, bayangkan sel B1 (di bawah) menampilkan pendapatan bulanan Anda, dan sel B3 hingga B11 menunjukkan pengeluaran Anda. Tabungan Anda (B14) ditampilkan sebagai selisih antara keduanya dan ditampilkan sebagai persentase dari pendapatan Anda di sel B15.

Jika Anda ingin tabungan Anda menjadi 10% dari pendapatan Anda, dan dengan asumsi bahwa Anda tidak dapat mengurangi pengeluaran Anda, Anda menyadari bahwa Anda harus meningkatkan pendapatan Anda. Tapi sampai berapa?

Dengan menggunakan perintah Goal Seek, kita dapat menunjukkan hasil yang diinginkan, dan Excel akan menentukan penyesuaian yang perlu kita buat untuk satu variabel. Kami akan menggunakan Goal Seek untuk mendapatkan nilai pendapatan yang dibutuhkan.

Pencarian Sasaran ExcelSetelah memilih perintah Goal Seek, jendela pop-up akan muncul. Di ‘Setel sel’, masukkan referensi sel, yang akan berisi hasil yang Anda inginkan (dalam kasus kami, B15). Di bidang ‘Untuk nilai’, masukkan hasil yang diinginkan (10%).

Bidang ketiga (Dengan mengubah sel) mengacu pada sel yang Anda ingin Excel untuk menyesuaikan (B1) untuk mencapai hasil yang diinginkan.

Pencarian Sasaran ExcelSetelah memilih OK, Excel akan bekerja mundur untuk menghitung pendapatan yang diperlukan untuk memungkinkan penghematan 10%. Goal Seek mencoba menghasilkan solusi yang sama persis dengan nilai target.

Di akhir proses, jendela pop-up Goal Seek berubah menjadi jendela Goal Seek Status, yang menyatakan apakah solusi ditemukan atau tidak, dan pratinjau hasilnya ditampilkan di lembar kerja. Untuk menerima angka ini, klik OK di jendela Status Pencarian Sasaran, dan nilai di lembar kerja akan disesuaikan.

Baca Juga:  Cara Menghitung Rata-Rata di Excel

Pencarian Sasaran ExcelPoin yang perlu diperhatikan:

    • Fitur Goal Seek hanya memungkinkan penyesuaian satu variabel pada satu waktu.
    • Sel yang dipilih dalam bidang “Setel sel” harus berupa sel yang berisi rumus.
    • Sel yang dipilih di bidang “Dengan mengubah sel” harus berupa sel yang berisi konstanta.
    • Setelah “OK” dipilih dari jendela Status Pencarian Sasaran, nilai pada lembar kerja disesuaikan dan hanya dapat diambil dengan memilih perintah ‘Batalkan’ (pintasan Windows – pintasan CtrlZ / Mac – CmdZ).

Contoh 2

Dalam pengaturan bisnis, Goal Seek juga dapat digunakan untuk menentukan persyaratan impas ketika semua parameter lainnya diketahui.

Pencarian Sasaran ExcelNilai dalam sel C1, C2, dan C4 adalah konstanta yang digunakan sebagai rumus untuk sampai pada nilai di sel yang tersisa. Perusahaan membukukan kerugian seperti yang ditunjukkan pada sel C7.

Kita dapat menggunakan Goal Seek untuk mengubah Harga Jual, Jumlah Penjualan, atau Biaya Tetap. Karakteristik utama dari biaya tetap, tentu saja, sifatnya yang tidak dapat diubah, sehingga akan tetap pada $75.000. Berdasarkan faktor pasar, $600 ditentukan sebagai harga jual terbaik, jadi satu-satunya nilai yang kami kendalikan adalah kuantitas yang terjual (C2).

Di jendela Goal Seek, kami menyetel sel C7 ke nol (break even), dengan mengubah C2 (kuantitas terjual).

Pencarian Sasaran ExcelGoal Seek menemukan solusi dalam parameter yang diberikan.

Pencarian Sasaran ExcelKami mengklik OK untuk menerima, dan lembar kerja menunjukkan bahwa kami perlu menjual 139 unit untuk mencapai titik impas.

Pemecahan Masalah Pencarian Sasaran

Ada kalanya sepertinya Goal Seek tidak berfungsi karena tidak dapat menemukan solusi berdasarkan parameter yang dikirimkan.

Pada contoh di bawah ini, kita ingin mengetahui berapa banyak penjualan yang perlu kita lakukan untuk memperoleh pendapatan $3.600, dengan menggunakan parameter yang dinyatakan dalam A1 hingga C2. Kami mencoba menggunakan Goal Seek tetapi menerima pesan berikut.

Pencarian Sasaran Excel - pemecahan masalahDalam kasus di atas, meskipun Goal Seek menemukan bahwa nilai 3.475 penjualan mendekati, itu tidak dianggap sebagai solusi karena nilai target $3.600 (dibulatkan ke seperseribu terdekat) tidak terpenuhi.

Hal ini biasanya disebabkan oleh dua faktor:

    1. Iterasi maksimum (jumlah upaya yang dapat dilakukan Excel untuk sampai pada solusi).
    1. Perubahan maksimum (tingkat akurasi yang dibutuhkan dari solusi Excel).
Baca Juga:  Meringkas Data Teks Dengan Tabel Pivot

Untuk situasi di mana solusi tepat diinginkan, pengaturan di atas dapat disesuaikan dengan membuka File > Opsi > Rumus dan mengubah nilai di setiap bidang sesuai keinginan.

Pencarian Sasaran Excel - pemecahan masalah

    • Jumlah iterasi default adalah 100. Untuk mengizinkan Excel mencoba solusi yang lebih mungkin, tingkatkan jumlah maksimum iterasi.
    • Perubahan maksimum default adalah 0,001. Untuk memaksa Excel menemukan solusi yang lebih tepat, sesuaikan Perubahan Maksimum ke angka yang sangat kecil, seperti 0,000001. Untuk memungkinkan hasil yang lebih fleksibel, buat Perubahan Maksimum menjadi angka yang lebih besar, mungkin lebih dekat ke angka 1.

Melakukan hal di atas akan menyesuaikan pengaturan Goal Seek untuk buku kerja saat ini.

Dalam kasus contoh ini, kami fleksibel dan akan menerima nilai yang mendekati target awal kami. Kami dapat:

    1. Terima solusi yang disajikan,
    1. Klik OK dan sesuaikan nilai pada lembar kerja kami secara manual ke nilai yang mendekati solusi yang disajikan, atau
    1. Sesuaikan perubahan maksimum dan/atau iterasi.

Untuk pergi dengan opsi pertama, cukup klik OK.

Untuk menggunakan opsi kedua, kita klik OK dan secara manual mengubah nilai di sel B6 sampai kita mencapai hasil yang memuaskan di sel keluaran kita.

Jika kami memilih opsi ketiga, kami dapat menyesuaikan perubahan maksimum menjadi 0,1 (artinya kami akan menerima jawaban dalam 10 sen dari tujuan kami). Ketika kami menjalankan kembali Goal Seek, kami melihat bahwa solusi yang berada dalam jarak 10 sen dari nilai target kami telah ditemukan.

Pencarian Sasaran Excel - pemecahan masalahKami mengklik OK untuk menerima, dan lembar kerja diperbarui. Angka yang ditampilkan terlihat sama, tetapi peningkatan jumlah tempat desimal menunjukkan angka 3474,9 penjualan untuk mendapatkan 10 sen dari nilai target. Jika kami menjual 3.474,9 unit, pendapatan kami akan menjadi $3.599,91.

Pencarian Sasaran di Excel

Otomatiskan Pencarian Sasaran

Salah satu alasan mengapa banyak orang mungkin tidak menyadari Pencarian Sasaran Excel adalah lokasinya di dalam pita Excel, dan jumlah langkah yang diperlukan untuk melihat hasil dari perubahan yang diusulkan. Untuk menghindari ini, kami dapat merekam makro sehingga pencarian tujuan dapat dicapai dengan satu klik.

Baca Juga:  Mengintegrasikan Power BI Dan PowerApps

Menyiapkan makro akan membutuhkan beberapa langkah, tetapi setelah selesai, itu membuat pekerjaan kita lebih mudah saat berikutnya kita harus bereksperimen dengan angka-angka ini.

Mari kita rekam langkah Goal Seek sebagai makro baru.

    1. Pertama, aktifkan tab Pengembang pada pita Excel (jika tidak ditampilkan) dengan mengklik kanan pada pita lalu mengklik perintah “Sesuaikan Pita”.
    1. Di sisi kanan jendela pop-up, di bawah Sesuaikan Pitacari Pengembang kotak centang dan klik OK.

Pencarian Sasaran Excel - makroTab Pengembang sekarang akan muncul di pita Anda.

    1. Buka tab Pengembang dan pilih Rekam Makro.
    1. Dalam Rekam Makro jendela, masukkan nama makro. Kami telah menamai milik kami CalcQuantity.
    1. Tetapkan tombol pintas. Dalam contoh kami, kami menggunakan Ctrl+Shift+Qdan makro akan disimpan dalam buku kerja ini saja.

Pencarian Sasaran Excel - makro

    1. Kami akan melakukan Goal Seek seperti biasa. Semua langkah dari sini dan seterusnya akan direkam sampai kami berhenti merekam. Kami akan mengatur sel C7 ke nol dengan mengubah C2, dan klik OK.
    1. Kembali ke tab Pengembang dan Berhenti Merekam.
    1. Sekarang mari kita siapkan sel independen yang akan digunakan sebagai nilai input untuk sel C7 setiap kali kita menjalankan Goal Seek versi sekali klik di masa mendatang. Dalam contoh kita, kita akan menggunakan sel F1.

Pencarian Sasaran Excel - makro

    1. Sekarang kita akan menyesuaikan makro yang baru saja kita buat dengan mengubah nilai nol menjadi apa pun yang dimasukkan di sel F1. Klik Dasar visual perintah pada Pengembang tab.

Pencarian Sasaran Excel - makro

    1. Di dalam kode Modul, ubah SasaranPencarian Sasaran:=0 ke Sasaran GoalSeek:=Rentang(“F1”).
    1. Simpan dan keluar dari Visual Basic.

Pencarian Sasaran Excel - makroPerhatikan bahwa file Excel Anda perlu disimpan sebagai tipe buku kerja berkemampuan makro agar makro Anda berfungsi.

Sekarang kita dapat memasukkan nilai apa pun di F1 sebagai jumlah target laba kita, lalu gunakan tombol pintasan makro untuk menjalankan makro dalam satu klik. Bukankah itu keren?

Tinggalkan komentar