EXCEL VBA'DA GOAL SEEK [HEDEF ARAMA]

 

VBA Hedef Arama işlevi, istenen sonucu üretmek için bir hücrede gereken girdi değerini belirlemenizi sağlayan güçlü bir araçtır. Özellikle aklınızdaki hedef değere ulaşmanızı sağlayacak eşleşen girdi değerini bulmak istediğinizde faydalıdır.

Bir örneğe bakalım. Basit bir kredi hesaplamanız olduğunu ve beş yıl boyunca aylık 200$ ödemelerle 10.000$'lık bir krediyi geri ödemek için gereken faiz oranını belirlemek istediğinizi varsayalım. İlk olarak, beş yıl boyunca %10 yıllık faiz oranı ve aylık ödemelerle 10.000$'lık bir kredi için aylık ödemeyi bulmak için aşağıdaki formülü kullanabilirsiniz:

= PMT (A1, 5 * 12, -10000)

Değerleri girdiğinizde:

• A1'de yıllık %10 faiz oranı bulunmaktadır.
• 5*12 toplam ödeme periyot sayısını (5 yıl için 60 ay) göstermektedir.
• -10000 kredi tutarıdır.

Aylık ödeme Excel üzerinden hesaplanacak ve sonuç -$212.47 olacaktır.

Şimdi VBA Hedef Arama'yı kullanarak her ay 10.000 doları ödemek için ödeyebileceğimiz 200 doların yüzdesini hesaplayacağız.

Faiz oranına göre aylık ödemeyi belirleme formülü bu örnekte B1'de gösterilmiştir. Bu kodu çalıştırarak, A1 hücresinin faiz oranı 200$ aylık ödeme üretecek şekilde ayarlanacaktır.

VBA kodunu çalıştırdığınızda , A1 Hücresini %7'ye değiştirerek koşulumuzun sağlandığını göreceksiniz.

ÖNEMLİ NOKTALAR

• Hedef hücrelerde istenilen çıktılara ulaşılabilmesi için VBA Goal Seek girdi değerlerinin belirlenmesine yardımcı olur.
• VBA Goal Seek with Constraints, Goal Seek işlemine koşullar veya kısıtlamalar uygulamak için VBA'yı kullanmayla ilgilenir. Sınırlamalar, uygulanabilir çözümlerin kapsamını daraltabilir ve Goal Seek'i belirli gereksinimleri karşılayan veya belirli kısıtlamalara uyan çözümleri belirlemeye zorlayabilir.
• Goal Seek'te VBA kodunuzu Değişken Aralığı ile yapılandırarak tek bir değişkeni ayarlamanın yanı sıra bir hücre aralığını dinamik olarak değişen değişkenler olarak tanımlayabilirsiniz.
• Birden fazla değişen hücre için döngüler kullanmayı göz önünde bulundurun ve yineleme sınırları ve yakınsama sorunlarına dikkat edin.

VBA Hedef Arama Sözdizimi

VBA'da Hedef Arama'nın sözdizimi aşağıdaki gibidir:

Range(“TargetCell”).GoalSeek Goal:=TargetValue, ChangingCell:=ChangingCellRange

Sözdiziminin dökümü şu şekildedir:

• Range(“TargetCell”): Bunu , hedef belirlemek istediğiniz formülü veya değeri içeren hücrenin başvurusuyla değiştirin .
• .GoalSeek: Belirtilen aralıkta Hedef Arama işlemini çağırmak için kullanılan yöntemdir.
• Target:=TargetValue: Hedef hücrede elde etmek istediğiniz istenen değeri “HedefDeğer” ile değiştirin.
• ChangingCell:=ChangingCellRange: “ChangingCellRange” ifadesini hedefe ulaşmak için ayarlamak istediğiniz hücre veya hücre aralığının referansıyla değiştirin.

Excel VBA'da Hedef Arama Nasıl Kullanılır? (Adımlarla)

Excel'de VBA Hedef Arama'yı kullanmak için şu adımları izleyin:

1. Excel'i açın ve VBA düzenleyicisini açmak için ALT + F11 tuşlarına basın.

   
   

2. Menüde “Ekle”ye tıklayıp “Modül”ü seçerek yeni bir modül ekleyin.

   
   

3. Modülün içine kodunuzu yazın. VBA'da Goal Seek için temel sözdizimi şu şekildedir:

   
   Range("TargetCell").GoalSeek Goal:=TargetValue, ChangingCell:=ChangingCell

4. Kodunuzu çalıştırmak için VBA editörünün Çalıştır → Alt/KullanıcıFormunu Çalıştır seçeneklerini veya F5 tuşunu kullanabilirsiniz.

5. VBA kodu çalışmayı tamamladıktan sonra sonuçları çalışma sayfanızda görüntüleyin.

   
   Artık hedefinizi belirlediğinize göre, hedef hücredeki değer onunla eşleşmeli ve değişen hücre veya hücreler buna uygun şekilde ayarlanmış olmalıdır.

Örnekler

Örnek 1

Diyelim ki bir maliyet modeliniz var ve kârınızı maksimize etmek için birim başına en iyi fiyatı belirlemek istiyorsunuz. VBA hedef arama işlevi, kârı maksimize etmek için otomatik olarak birim başına en iyi fiyatı belirleyebildiği için bunu başarmak için kullanılabilir.

• Adım 1: İlk olarak, yeni modülde MaximizeProfit adlı özel bir VBA fonksiyonu oluşturuyoruz . Satılan birim miktarına göre, bu fonksiyon kârı belirleyecek ve sonuç olarak double veri tipi döndürecek.

• Adım 2: Burada kârı hesaplıyoruz. Örneğin, kârın birim miktarının on katı olduğunu varsayarak, birim sayısını temsil eden B2 hücresindeki değeri 10 ile çarpar. Sonuç MaximizeProfit değişkenine atanır.

• Adım 3: Şimdi, “GoalSeek1” adında bir VBA alt rutini tanımlıyoruz. Bu alt rutin, Goal Seek işlemini gerçekleştirecektir.

• Adım 4: Bu bölümde Hedef Arama operasyonunu belirtiyoruz:
  • Range(“C2”) karı maksimize etmek istediğimiz hedef hücredir.
  • Target:=MaximizeProfit, maksimize etmek için hedef olarak MaximizeProfit özel fonksiyonunu kullanmak istediğimizi belirtir.
  • ChangingCell:=Range(“B2”), hedefe ulaşmak için B2 hücresindeki değeri (birim sayısını) değiştirmek istediğimizi belirtir.

• Adım 5: Şimdi modülü kaydedip VBE'den çıkalım.

Bu kodu çalıştırmak istediğiniz Excel çalışma sayfasında, Makro penceresini açmak için alt+F8 tuşlarına basın.

Makro penceresinde GoalSeek1’i seçin ve çalıştır’a tıklayın.

• Adım 6: Bu kod yürütüldüğünde, VBA Hedefi'ni kullanarak kârı (C2 hücresinde) çalışma sayfanızda verilen birim miktarının (A2 hücresinde) 25 katı olarak belirler. Kârı maksimize etmek için kaç birim satılması gerektiğini belirleyin.

Bu durumda, maksimum %625 kar elde etmek için birim fiyatı %25'e çıkarmamız gerekecektir.

İşte tam kod:

Function MaximizeProfit() As Double
MaximizeProfit = Range(“B2”).Value * 25 ‘ Example: Profit is 25 times the number of units
End Function

Sub GoalSeek1()
Range(“C2”).GoalSeek Goal:=MaximizeProfit, ChangingCell:=Range(“A2”)
End Sub

Örnek 2

Bu örnekte, VBA Goal Seek işlevini kullanarak denge noktasının nasıl bulunacağını anlayacağız. Bunu yapmak için, toplam gelir ve toplam maliyetin eşit olduğu bir durumu temsil eden kâr/zarar (B3 hücresinde) sıfıra eşitlenene kadar satılan birim sayısını (B1 hücresinde) otomatik olarak ayarlayacağız.

• Adım 1: Yeni modülde ilk olarak “FindBreakEven” adlı bir VBA alt rutini tanımlıyoruz. Bu alt rutin, eşit noktayı belirlemek için Hedef Arama fonksiyonunu kullanacaktır.

• Adım 2 : Burada, çalışma sayfamızdaki hücreleri belirtmek için kullanılacak TargetCell ve ChangingCell adlı iki Aralık değişkeni tanımlıyoruz.

• Adım 3: Bu satırda, “TargetCell”i B3 hücresi olarak ayarlıyoruz. B3 hücresindeki karı veya zararı hesaplıyoruz ve hedefimiz bu değerin sıfıra eşit olduğu noktayı veya denge noktasını belirlemek.

• Adım 4: Burada, B1 hücresini “ChangingCell” olarak seçiyoruz. Satılan birim sayısı için girdi B1 hücresi tarafından temsil edilir ve Hedef Arama işlemi sıfır kar veya zarar elde etmek için bu değeri değiştirir.

• Adım 5: VBA Hedef Arama işlemi burada gerçekleştirilir. Denge noktasını belirlemek için, B1 hücresinde satılan birim sayısını (ChangingCell tarafından belirtildiği gibi) B3 hücresindeki kar veya zarar (TargetCell tarafından belirtildiği gibi) sıfıra eşit olana kadar değiştirir.

• Adım 6: Şimdi makroyu kaydedip VBE'den çıkalım.

Bu kodu çalıştırmak istediğiniz Excel çalışma sayfasında, Makro penceresini açmak için alt+F8 tuşlarına basın.

VBA makro penceresinden “FindBreakEven” seçeneğini seçin ve çalıştır’a tıklayın.

• Adım 7: VBA Hedef Arama işlevini kullanarak denge noktasını belirler. Bu, satılan birim miktarını (B1 hücresinde) kar veya zarar (B3 hücresinde) sıfır olana kadar değiştirmeyi içerir. Başka bir deyişle, toplam gelir ve toplam maliyetin birbirine eşit olduğu ve kar veya zararın olmadığı noktayı bulur.

Yani bizim durumumuzda B1 hücresi 1.200 dolara değişiyor, bu da bu fiyatta zarar etmeme pozisyonunda olacağımızı gösteriyor.

İşte tam kod:

Sub FindBreakEven()
Dim TargetCell As Range
Dim ChangingCell As Range
Set TargetCell = Range(“B3”)
Set ChangingCell = Range(“B1”)
TargetCell.GoalSeek Goal:=0, ChangingCell:=ChangingCell
End Sub

Dikkat Edilmesi Gereken Önemli Noktalar

• VBA Hedef Arama'yı kullanırken, optimizasyon sürecini kontrol etmek için yineleme sayısına sınırlar koyabilirsiniz. Sonsuz döngüyü önlemek ve Hedef Arama işleminin belirtilen sınıra ulaştıktan sonra sona ermesini garantilemek için, VBA Hedef Arama Sınırı Yinelemeleri maksimum yineleme sayısı ayarlamayı gerektirir.
• VBA Goal Seek her zaman bir sorunu çözemeyebilir, özellikle karmaşık modellerde. Yinelemeli hesaplamalar kullandığı için her zaman bir sonuca varamayabilir.
• VBA Hedef Arama Döngüsü, Hedef Arama prosedürünün bir döngü yapısı kullanılarak tekrar tekrar otomatikleştirildiği ve tekrarlandığı bir durumu açıklar.
• VBA Goal Seek genellikle tek bir değişen hücreyi yönetir, ancak birden fazla değişen hücreyi yönetmek için döngüler ve ek VBA mantığı kullanabilirsiniz.