Cara Mengotomatiskan Pemecah Excel

Dalam Cara Mendapatkan Lebih Banyak dari Excel Solver, saya membahas teknik untuk menyempurnakan model Anda dan memberdayakan analisis Anda. Salah satu topik yang paling banyak saya terima pertanyaannya adalah mengotomatiskan pemecah untuk memecahkan beberapa nilai.

Ada penjelasan singkat di artikel sebelumnya, tetapi tidak ada yang didedikasikan untuk bagaimana menerapkannya dari awal hingga akhir. Pada artikel ini kita akan membahas secara mendalam dari awal sampai akhir yang mencakup konsep dan metode langkah demi langkah. Pada akhirnya, Anda akan dapat mengotomatiskan salah satu model pribadi Anda dan menskalakan analisis Anda.

Bagaimana kita akan melakukannya?

Kami akan memanfaatkan perekam makro di Excel untuk menangani sebagian besar kode yang sebenarnya. Alat rekam makro akan memungkinkan Excel untuk mengubah langkah manual kita menjadi kode VBA.

Kami kemudian akan mengidentifikasi bagian-bagian kunci dari kode untuk diubah. Kemudian kita akan membuat loop untuk secara dinamis memilih dan mengganti nilai-nilai ini dan menjalankan solver lagi.

Ini akan menjadi semi-teknis tetapi melalui gaya pengembangan ini kita dapat fokus pada proses dan konsep daripada berfokus pada cara menulis kode.

Menyiapkan perekam makro

Fungsionalitas rekaman makro ditemukan di tab pengembang di pita. Ini tidak tersedia secara default tetapi mudah diterapkan. Jika Anda menavigasi ke opsi pita Anda, Anda akan melihat kotak centang untuk tab pengembang.

Tangkapan layar di bawah ini untuk Mac tetapi Excel berbasis PC serupa.

Jika Anda belum menginstal add-on solver, Anda dapat menginstalnya melalui menu Add-in. Situs dukungan Microsoft memiliki instruksi untuk semua platform, Muat Add-in Solver di Excel.

Merekam makro

Sekarang setelah pengaturan selesai, kami siap merekam! Siapkan ruang kerja Anda dengan tepat dengan semua rumus dan referensi Anda. Anda akan ingin mengatur semuanya ke titik sebelum Anda membangun model pemecah.

Kembali ke tab pengembang di pita. Anda akan melihat selembar kertas dengan titik merah. Klik tombol itu untuk membuka perekam.

Beri nama makro Anda dan isi deskripsi jika Anda mau.

Setelah Anda menekan Ok, perekam akan dimulai.

Ikuti langkah-langkah menyiapkan pemecah Anda dan selesaikan. Setelah ini selesai kembali ke tab pengembang dan berhenti merekam. Itu akan menjadi lokasi yang sama dengan tombol yang Anda gunakan untuk memulai perekaman.

Sekarang setelah kita memiliki kode dasar, kita dapat membersihkannya dan mempersiapkannya untuk langkah selanjutnya.

Bersihkan kode

Sekarang kita dapat mengedit kode, menghapus bagian yang tidak perlu, dan memastikan kita hanya menggunakan bagian penting dari rekaman makro.

Anda dapat mengedit kode melalui editor VBA Excel. Untuk mengakses kode rekaman Anda, klik tombol Macro.

Arahkan ke makro Anda di menu baru dan klik edit.

Sekarang setelah editor kode kami terbuka, kami dapat mulai membuat tweak.

Menghapus Kode yang Tidak Perlu

Jika Anda kebetulan mengklik banyak hal lain saat merekam, Anda dapat menghapusnya sekarang dan dibiarkan dengan sesuatu yang mirip dengan di bawah ini. Jika ada banyak kode tambahan dan Anda khawatir akan merusaknya, Anda selalu dapat merekam ulang.

Menyiapkan referensi pemecah

Satu langkah terakhir sebelum kita melanjutkan, pergi ke Tools > References dan pilih Solver. Jika Anda tidak melakukan ini, paket tidak akan dimuat di makro dan Anda akan mendapatkan kesalahan.

Anda sekarang dapat mengubah parameter Anda, buka menu makro, pilih makro Anda, tekan run dan itu akan memperbarui hasilnya.

Mari kita sejajarkan kode dengan model solver kita. Anda akan melihat bagaimana garisnya. Bahasanya berbeda tetapi Anda dapat menyatukan apa artinya semua itu.

Selanjutnya kita akan mengisolasi bagian-bagian yang perlu kita ubah dan menyelesaikan otomatisasi kita.

Bilas dan ulangi – buat lingkaran

Sekarang kita memiliki pemecah yang berfungsi. Kita perlu mengulanginya berkali-kali. Mari kita beri contoh dan perkuat rencananya.

Kami memulai dengan anggaran $1.000 pada model pertama, tetapi kami ingin melakukan hal yang sama untuk kenaikan $100 hingga $2.500.

Mari selami! Kami memiliki daftar nilai di kolom B dan output kami di kolom C. Jika kami menuliskan prosesnya akan terlihat seperti,

1. Tetapkan total pembelanjaan ke nilai di B10.
2. Memecahkan model.
3. Tempatkan output di C10.
4. Pilih nilai B berikutnya.
5. Atur output di baris C.
6. Ulangi langkah 2-5 untuk semua nilai di B.

Memulai putaran

Loop adalah konstruksi pengkodean yang mengulangi suatu tindakan hingga mencapai titik akhir yang logis. Sebuah loop akan berguna di sini untuk mengulangi proses yang sama, menjalankan solver, pada setiap nilai hingga kita mencapai akhir nilai.

Untuk contoh ini kita akan mendefinisikan titik akhir dengan jumlah baris. B10:B25 memiliki 16 nilai.

Kami akan memulai loop kami dan membungkusnya di sekitar kode solver kami. Kami akan menyiapkan variabel untuk menahan nomor loop (i) dan menelusuri setiap nilai, menyelesaikan loop setiap kali.

Menyetel ulang pemecah

Kami akan menambahkan kondisi lain di awal kode kami. Karena kami ingin menjalankan kembali solver setiap iterasi, kami akan meresetnya setiap loop. Ini akan menghapus pengaturan dan memulai model baru.

Kita dapat melakukan ini melalui SolverReset .

Memperbarui referensi sel

Kami sekarang memecahkan enam belas kali tetapi perlu memperbarui referensi sel kami. Sementara kami awalnya mereferensikan B10, kami perlu menurunkan satu sel setiap menjalankan untuk memperbarui batas. Kita dapat melakukan ini melalui fungsi offset. Kita dapat memperbarui “$B$10” untuk mereferensikan sel kemudian diimbangi dengan i baris (0 pada putaran pertama, 1 baris pada putaran kedua, dan seterusnya). Setiap kali loop berjalan, i bertambah satu.

Mengumpulkan keluaran

Output akan berubah setiap kali jadi kami ingin menyimpannya. Kita dapat mengikuti proses yang sama di atas tetapi diimbangi dari C10 dan mengaturnya sama dengan nilai model yang diselesaikan. Kemudian loop dimulai lagi.

Kami akan memanggil C10 mengimbanginya dengan jumlah baris yang sesuai lalu menempelkan nilai output kami.

Menghindari pemberitahuan pemecah

Mari kita selamatkan diri kita dari sakit kepala dan tidak belajar dengan contoh sejenak. Ingat ketika Anda menjalankan pemecah dan menu itu muncul menanyakan apakah Anda ingin menerima solusinya? Itu akan muncul setiap saat jika Anda tidak menonaktifkannya. Bayangkan jika Anda membuat loop dengan nilai 100-an, Anda harus mengklik terima setiap saat!

Silakan tambahkan baris berikut setelah kode solver Anda dan tambahkan (TRUE) ke SolverSolve. Ini akan meniru Anda menerima perubahan.

Sekarang kita memiliki kode yang sudah jadi!

Aktif dan berjalan

Kita sekarang dapat menjalankan kode dengan memilihnya dari daftar makro dan menjalankannya. Jika Anda ingin menggunakan buku kerja sebagai templat, Anda dapat menetapkan makro ke tombol untuk kemudahan penggunaan. Anda dapat menemukannya di tab pengembang.

Anda juga dapat menambahkan grafik perbandingan apa pun. Dalam hal ini kami ingin membandingkan tingkat pertumbuhan pembelanjaan versus pertumbuhan volume konversi. Contoh ini tidak menarik tetapi data nyata dapat membantu mengungkapkan titik henti efisiensi

Kesimpulan

Kami membahas cukup banyak! Selamat telah mengaktifkan dan menjalankan pemecah otomatis Anda. Contoh yang kami bahas terlalu sederhana, tetapi Anda dapat mengambil konsep yang sama dan menerapkannya ke model pemecah apa pun.

Ini dapat menghemat banyak waktu untuk model yang perlu Anda selesaikan beberapa kali dan mendorong pengguna untuk bereksperimen dengan skenario yang berbeda.

Tanggal publikasi asli: 30/07/2019