Goal Seek ialah salah satu alat Analisis What-if Excel yang membantu anda mencari nilai input yang betul bagi formula untuk mendapatkan output yang diingini. Ia menunjukkan bagaimana satu nilai dalam formula mempengaruhi yang lain. Dalam erti kata lain, jika anda mempunyai nilai sasaran yang ingin anda capai, anda boleh menggunakan pencarian matlamat untuk mencari nilai input yang sesuai untuk mendapatkannya.
Sebagai contoh, katakan anda mendapat sejumlah 75 markah dalam subjek dan anda memerlukan sekurang-kurangnya 90 untuk mendapatkan gred S dalam subjek itu. Syukurlah, anda mempunyai satu ujian terakhir yang mungkin membantu anda meningkatkan skor purata anda. Anda boleh menggunakan Goal Seek untuk mengetahui jumlah markah yang anda perlukan pada ujian akhir itu untuk mendapatkan gred S.
Pencarian Matlamat menggunakan kaedah percubaan dan kesilapan untuk menjejak kembali masalah dengan memasukkan dalam tekaan sehingga ia mencapai keputusan yang betul. Pencarian Matlamat boleh mencari nilai input terbaik untuk formula dalam masa beberapa saat yang akan mengambil masa yang lama untuk memikirkan secara manual. Dalam artikel ini, kami akan menunjukkan kepada anda cara menggunakan alat Pencarian Matlamat dalam Excel dengan beberapa contoh.
Komponen Fungsi Pencarian Matlamat
Fungsi Pencarian Matlamat terdiri daripada tiga parameter, iaitu:
- Tetapkan sel – Ini ialah sel di mana formula dimasukkan. Ia menentukan sel di mana anda mahu output yang diingini.
- Untuk nilai – Ini ialah Nilai Sasaran / Dikehendaki yang anda inginkan hasil daripada operasi mencari matlamat.
- Dengan menukar sel – Ini menentukan sel yang nilainya perlu dilaraskan untuk mendapatkan output yang diingini.
Cara Menggunakan Goal Seek dalam Excel: Contoh 1
Untuk menunjukkan cara ia berfungsi dalam contoh mudah, bayangkan anda menjalankan gerai buah-buahan. Dalam tangkapan skrin di bawah, sel B11 (di bawah) memaparkan kos anda untuk membeli buah-buahan untuk gerai anda dan sel B12 menunjukkan jumlah hasil anda untuk menjual buah-buahan tersebut. Dan sel B13 menunjukkan peratusan keuntungan anda (20%).
Jika anda ingin meningkatkan keuntungan anda kepada '30%', tetapi anda tidak dapat meningkatkan pelaburan anda, jadi anda mesti meningkatkan pendapatan anda untuk mendapatkan keuntungan. Tetapi berapa banyak? Pencarian matlamat akan membantu anda menemuinya.
Anda menggunakan formula berikut dalam sel B13 untuk mengira peratusan keuntungan:
=(B12-B11)/B12Sekarang, anda ingin mengira margin keuntungan supaya peratusan keuntungan anda ialah 30%. Anda boleh melakukan ini dengan Goal Seek dalam Excel.
Perkara pertama yang perlu dilakukan ialah memilih sel yang nilainya ingin anda laraskan. Setiap kali anda menggunakan Goal Seek, anda mesti memilih sel yang mengandungi formula atau fungsi. Dalam kes kami, kami akan memilih sel B13 kerana ia mengandungi formula untuk mengira peratusan.
Kemudian pergi ke tab 'Data', klik butang 'Bagaimana jika Analisis' dalam kumpulan Ramalan, dan pilih 'Cari Matlamat'.
Kotak dialog Pencarian Matlamat akan muncul dengan 3 medan:
- Tetapkan sel – Masukkan rujukan sel yang mengandungi formula (B13). Ini adalah sel yang akan mempunyai output yang anda inginkan.
- Untuk nilai – Masukkan hasil yang diingini yang anda cuba capai (30%).
- Dengan menukar sel – Masukkan rujukan sel untuk nilai input yang anda ingin ubah (B12) untuk mencapai hasil yang diingini. Hanya klik pada sel atau masukkan rujukan sel secara manual. Apabila anda memilih sel, Excel akan menambah tanda '$' sebelum huruf lajur dan nombor baris untuk menjadikannya sel mutlak.
Apabila anda selesai, klik 'OK' untuk mengujinya.
Kemudian kotak dialog 'Status Carian Matlamat' akan muncul dan memberitahu anda jika ia menemui sebarang penyelesaian seperti yang ditunjukkan di bawah. Jika penyelesaian telah ditemui, nilai input dalam 'sel berubah (B12)' akan dilaraskan kepada nilai baharu. Inilah yang kita mahu. Jadi dalam contoh ini, analisis menentukan bahawa, untuk anda mencapai matlamat keuntungan 30% anda, anda perlu mencapai hasil sebanyak $1635.5 (B12).
Klik 'OK' dan Excel akan menukar nilai sel atau klik 'Batal' untuk membuang penyelesaian dan memulihkan nilai asal.
Nilai input (1635.5) dalam sel B12 ialah apa yang kami dapati menggunakan Goal Seek untuk mencapai matlamat kami (30%).
Perkara yang Perlu Diingati apabila menggunakan Goal Seek
- Sel Set mesti sentiasa mengandungi formula yang bergantung pada sel 'Dengan Menukar Sel'.
- Anda hanya boleh menggunakan Goal Seek pada nilai input sel tunggal pada satu masa
- 'Dengan menukar sel' mesti mengandungi nilai dan bukan formula.
- Jika Pencarian Matlamat tidak dapat mencari penyelesaian yang betul, ia menunjukkan nilai paling hampir yang boleh dihasilkan dan memberitahu anda bahawa mesej 'Pencarian Matlamat mungkin tidak menemui penyelesaian'.
Perkara yang Perlu Dilakukan Apabila Pencarian Matlamat Excel Tidak Berfungsi
Walau bagaimanapun, jika anda pasti bahawa terdapat penyelesaian, terdapat beberapa perkara yang anda boleh cuba untuk menyelesaikan isu ini.
Semak parameter dan nilai Goal Seek
Terdapat dua perkara yang perlu anda semak: pertama, semak sama ada parameter 'Set sel' merujuk kepada sel formula. Kedua, pastikan sel formula (Set sel) bergantung, secara langsung atau tidak langsung, pada sel yang berubah.
Melaraskan Tetapan Lelaran
Dalam tetapan Excel, anda boleh menukar bilangan percubaan yang mungkin dilakukan Excel untuk mencari penyelesaian yang betul serta ketepatannya.
Untuk menukar tetapan pengiraan lelaran, klik 'Fail' daripada senarai tab. Kemudian, klik 'Pilihan' di bahagian bawah.
Pada tetingkap Excel Options, klik 'Formula' di anak tetingkap sebelah kiri.
Di bawah bahagian 'Pilihan pengiraan', tukar tetapan ini:
- Lelaran Maksimum – Ia menunjukkan bilangan penyelesaian yang mungkin dikira oleh excel; semakin tinggi nombor semakin banyak lelaran yang boleh dilakukan. Contohnya, jika anda menetapkan 'Lelaran Maksimum' kepada 150, Excel menguji 150 penyelesaian yang mungkin.
- Perubahan Maksimum - Ia menunjukkan ketepatan keputusan; bilangan yang lebih kecil memberikan ketepatan yang lebih tinggi. Sebagai contoh, jika nilai sel input anda sama dengan '0' tetapi Pencarian Matlamat berhenti mengira pada '0.001', menukar ini kepada '0.0001' sepatutnya menyelesaikan masalah.
Tingkatkan nilai 'Lelaran Maksimum' jika anda mahu Excel menguji lebih banyak penyelesaian yang mungkin dan kurangkan nilai 'Perubahan Maksimum' jika anda mahukan hasil yang lebih tepat.
Tangkapan skrin di bawah menunjukkan nilai lalai:
Tiada Rujukan Pekeliling
Apabila formula merujuk semula kepada selnya sendiri, ia dipanggil rujukan bulat. Jika anda mahu Excel Goal Seek berfungsi dengan betul, formula tidak boleh menggunakan rujukan bulat.
Contoh Pencarian Matlamat Excel 2
Katakan anda meminjam wang sebanyak '$25000' daripada seseorang. Mereka meminjamkan wang kepada anda pada kadar faedah 7% sebulan untuk tempoh 20 bulan, yang menjadikan pembayaran balik '$1327' sebulan. Tetapi anda hanya mampu membayar '$1,000' sebulan selama 20 bulan dengan faedah 7%. Goal Seek boleh membantu anda mencari jumlah pinjaman yang menghasilkan bayaran bulanan (EMI) sebanyak '$1000'.
Masukkan tiga pembolehubah input yang anda perlukan untuk mengira pengiraan PMT anda iaitu kadar faedah 7%, tempoh 20 bulan dan jumlah prinsipal $25,000.
Masukkan formula PMT berikut dalam sel B5 yang akan memberi anda jumlah EMI sebanyak $1,327.97.
Sintaks fungsi PMT:
=PMT(Kadar Faedah/12, Tempoh, Prinsipal)Formulanya:
=PMT(B3/12,B4,-B2)
Pilih sel B5 (sel formula) dan pergi ke Data -> Bagaimana Jika Analisis -> Pencarian Matlamat.
Gunakan parameter ini pada tetingkap 'Cari Matlamat':
- Tetapkan sel – B5 (sel yang mengandungi formula yang mengira EMI)
- Untuk nilai – 1000 (hasil formula/matlamat yang anda cari)
- Dengan menukar sel – B2 (ini ialah jumlah pinjaman yang anda ingin ubah untuk mencapai nilai matlamat)
Kemudian, tekan 'OK' untuk menyimpan penyelesaian yang ditemui atau 'Batal' untuk membuangnya.
Analisis memberitahu anda bahawa jumlah maksimum wang yang anda boleh pinjam ialah $18825 jika anda ingin melebihi belanjawan anda.
Begitulah cara anda menggunakan Goal Seek dalam Excel.