Excel Çözücü Nasıl Otomatikleştirilir

Excel Çözücüden Nasıl Daha Fazla Yararlanılır bölümünde, modelinizi geliştirmek ve analizinizi güçlendirmek için teknikleri ele aldım. En çok soru aldığım konulardan biri, çözücünün birden çok değeri çözecek şekilde otomatikleştirilmesidir.

Önceki makalede kısa bir açıklama vardı, ancak baştan sona nasıl uygulanacağına dair hiçbir şey yoktu. Bu makalede, kavramları ve yöntemleri adım adım ele alarak baştan sona derinlemesine gideceğiz. Sonunda, kendi kişisel modellerinizden herhangi birini otomatikleştirebilecek ve analizinizi ölçeklendirebileceksiniz.

nasıl yapacağız

Gerçek kodun çoğunu işlemek için Excel'deki makro kaydediciden yararlanacağız. Makro kaydetme aracı, Excel'in manuel adımlarımızı VBA koduna dönüştürmesine olanak tanır.

Daha sonra değiştirilecek anahtar kod parçalarını belirleyeceğiz. Daha sonra bu değerleri dinamik olarak seçip değiştirmek için bir döngü oluşturacağız ve çözücüyü tekrar çalıştıracağız.

Bu yarı teknik olacak, ancak bu geliştirme tarzıyla kod yazma yerine sürece ve kavramlara odaklanabiliriz.

Makro kaydediciyi ayarlama

Makro kaydı işlevi, şeritteki geliştirici sekmesinde bulunur. Bu, varsayılan olarak mevcut değildir, ancak uygulanması kolaydır. Şerit seçeneklerinize giderseniz, geliştirici sekmesi için bir onay kutusu görürsünüz.

Aşağıdaki ekran görüntüsü Mac içindir ancak PC tabanlı Excel benzerdir.

Çözücü eklentisini yüklemediyseniz Eklentiler menüsünden yükleyebilirsiniz. Microsoft destek sitesinde tüm platformlar için yönergeler vardır, Çözücü Eklentisini Excel'e Yükleyin.

Makroyu kaydetme

Kurulum tamamlandığına göre artık kayıt yapmaya hazırız! Tüm formülleriniz ve referanslarınız ile çalışma alanınızı uygun şekilde kurun. Çözücü modelini oluşturmadan önce her şeyi noktaya kadar ayarlamak isteyeceksiniz.

Şeritteki geliştirici sekmesine geri dönün. Kırmızı noktalı bir kağıt parçası göreceksiniz. Kaydediciyi açmak için bu düğmeye tıklayın.

Makronuza bir ad verin ve isterseniz açıklamayı doldurun.

Tamam'a bastığınızda kayıt cihazı başlayacaktır.

Çözücünüzü kurma adımlarını izleyin ve çözün. Bu tamamlandıktan sonra geliştirici sekmesine geri dönün ve kaydı durdurun. Kaydı başlatmak için kullandığınız düğmeyle aynı konum olacaktır.

Artık temel koda sahip olduğumuza göre onu temizleyebilir ve sonraki adımlar için hazırlayabiliriz.

Kodu temizle

Artık kodu düzenleyebilir, gereksiz parçaları kaldırabilir ve yalnızca makro kaydının temel parçalarını kullandığımızdan emin olabiliriz.

Kodu Excel'in VBA düzenleyicisi aracılığıyla düzenleyebilirsiniz. Kayıtlı kodunuza erişmek için Makrolar düğmesini tıklayın.

Yeni menüde makronuza gidin ve düzenle'yi tıklayın.

Artık kod düzenleyicimizi açtığımıza göre ince ayarlar yapmaya başlayabiliriz.

Gereksiz Kodu Kaldırma

Kayıt sırasında bir sürü başka şeye tıkladıysanız, bunları şimdi temizleyebilir ve aşağıdakine benzer bir şeyle kalabilirsiniz. Çok fazla ekstra kod varsa ve onu kırma konusunda endişeleniyorsanız, her zaman yeniden kaydedebilirsiniz.

Çözücü referanslarını ayarlama

Devam etmeden önce son bir adım, Araçlar > Referanslar'a gidin ve Çözücü'yü seçin. Bunu yapmazsanız paket makroya yüklenmez ve bir hata alırsınız.

Artık makrolar menüsünü açarak parametrelerinizi değiştirebilir, makronuzu seçebilir, çalıştır'a bastığınızda sonuçları güncelleyebilirsiniz.

Kodu çözücü modelimize göre sıralayalım. Nasıl sıralandığını göreceksin. Dil farklı ama ne anlama geldiğini bir araya getirebilirsiniz.

Ardından, değiştirmemiz gereken parçaları ayıracağız ve otomasyonumuzu tamamlayacağız.

Durulayın ve tekrarlayın – döngüyü oluşturun

Artık çalışan bir çözücümüz var. Bunu defalarca tekrarlamamız gerekiyor. Birlikte bir örnek verelim ve planı sağlamlaştıralım.

İlk modelde 1.000$'lık bir bütçeyle başladık ama aynı şeyi 2.500$'a kadar 100$'lık artışlarla yapmak istiyoruz.

Hadi dalalım! B sütununda bir değerler listemiz ve C sütununda çıktımız var. İşlemi yazarsak şöyle görünürdü:

1. Toplam harcamayı B10'daki değere ayarlayın.
2. Modeli çözün.
3. Çıktıyı C10'a yerleştirin.
4. Sonraki B değerini seçin.
5. Çıktıyı C satırında ayarlayın.
6. B'deki tüm değerler için 2-5 arasındaki adımları tekrarlayın.

Döngüyü başlatmak

Döngüler, mantıksal bir bitiş noktasına ulaşana kadar bir eylemi tekrarlayan kodlama yapılarıdır. Değerin sonuna ulaşana kadar her değerde çözücüyü çalıştırarak aynı işlemi tekrarlamak için bir döngü burada kullanışlı olacaktır.

Bu örnek için bitiş noktasını satır sayısına göre tanımlayacağız. B10:B25'in 16 değeri vardır.

Döngümüzü başlatacağız ve onu çözücü kodumuza saracağız. Döngü numarasını (i) tutmak için bir değişken ayarlayacağız ve her bir değerden geçerek döngüyü her seferinde çözeceğiz.

Çözücüyü sıfırlama

Kodumuzun başına başka bir koşul ekleyeceğiz. Çözücüyü her yinelemede yeniden çalıştırmak istediğimizden, her döngüde onu sıfırlayacağız. Bu, ayarları temizleyecek ve yeni bir model başlatacaktır.

Bunu SolverReset ile yapabiliriz .

Hücre referanslarını güncelleme

Şimdi on altı kez çözüyoruz ancak hücre referanslarımızı güncellememiz gerekiyor. Başlangıçta B10'a başvururken, limitleri güncellemek için her çalıştırmada bir hücre aşağı gitmemiz gerekiyor. Bunu ofset fonksiyonu ile yapabiliriz. Hücreye referans vermek için “$B$10”u güncelleyebiliriz ve ardından i satırları (ilk çalıştırmada 0, ikinci satırda 1 satır vb.) ile dengeleyebiliriz. Döngü her çalıştığında, i bir artar.

çıktının toplanması

Çıktı her seferinde değişecek, bu yüzden onu kaydetmek isteyeceğiz. Yukarıdaki aynı işlemi takip edebiliriz ancak C10'dan ofset ve çözülen modelin değerine eşit olarak ayarlayabiliriz. Sonra döngü baştan başlar.

C10'u uygun sayıda satırla ofset olarak arayacağız ve ardından çıktımızın değerini yapıştıracağız.

Çözücü bildirimlerinden kaçınma

Kendimizi bir baş ağrısından kurtaralım ve bir an için örnek olarak öğrenmeyelim. Çözücüyü çalıştırdığınız zamanı ve çözümü kabul etmek isteyip istemediğinizi soran o menünün açıldığını hatırlıyor musunuz? Devre dışı bırakmazsanız, bu her seferinde açılır. 100'lerce değer içeren bir döngü oluşturduğunuzu düşünün, her seferinde kabul et'i tıklamanız gerekir!

Devam edin ve çözücü kodunuzdan sonra aşağıdaki satırları ekleyin ve SolverSolve'a (TRUE) ekleyin. Bu, değişiklikleri kabul ettiğinizi taklit edecektir.

Şimdi bitmiş kodumuz var!

Faal ve çalışır durumda

Artık kodu makro listesinden seçip çalıştırarak çalıştırabiliriz. Çalışma kitabınızı şablon olarak kullanmak isterseniz, kullanım kolaylığı için makroyu bir düğmeye atayabilirsiniz. Bunları geliştirici sekmesinde bulabilirsiniz.

Ayrıca herhangi bir karşılaştırma grafiği de ekleyebilirsiniz. Bu durumda, dönüşüm hacmi büyümesiyle harcama artışı oranını karşılaştırmak istiyoruz. Bu örnek ilginç değil, ancak gerçek veriler verimlilik sınır değerlerini ortaya çıkarmaya yardımcı olabilir

Çözüm

Bir hayli kapladık! Otomatik çözücünüzü hazır ve çalışır hale getirdiğiniz için tebrikler. Ele aldığımız örnek fazlasıyla basitti, ancak aynı kavramları alıp herhangi bir çözücü modeline uygulayabilirsiniz.

Bu, birden çok kez çözmeniz gereken modeller için çok fazla zaman kazandırabilir ve kullanıcıları farklı senaryoları denemeye teşvik edebilir.

Orijinal yayın tarihi: 07/30/2019