Excel Solver: Birden Çok Değişkenle Karmaşık Kararları Optimize Edin

Varsayımsal analiz (what-if) farklı senaryoları test ederek sonuçlarınızı nasıl etkilediklerini görmenizi sağlar. Maliyetler veya kapasiteler gibi değişkenlerle güvenli bir ortamda deney yapmanıza ve en etkili çözümü bulmanıza imkân tanır. Bu yaklaşım, kaynak yöneten, program planlayan veya stratejik iş kararları alan herkes için kritiktir.

Elle deneme-yanılma saatler sürer ve çoğu zaman en iyi cevabı kaçırır. Solver ise saniyeler içinde matematiksel olarak en iyiyi garanti eder. Bu, kaynak tahmini yapmakla en iyi kararı verdiğinizi bilmek arasındaki farktır.

Bu eğitim, bu tür optimizasyon problemleri için geliştirilmiş gelişmiş özellik olan Excel Solver’ı adım adım anlatır. Gerçek dünyadan örneklerle elektronik tablonuzu nasıl kuracağınızı, analizi nasıl çalıştıracağınızı ve bulguları nasıl yorumlayacağınızı göstereceğim.

Excel Solver’ı Anlamak: Temel Kavramlar

Size Solver’ı nasıl kullanacağınızı göstermeden önce, her optimizasyon probleminin üç yapı taşını açıklayayım. Bunlar birer moda sözcük değil; Solver’ın çalışmasını sağlayan unsurlar.

Amaç hücresi, karar değişkenleri ve kısıtlar

 Amaç hücresini hedefiniz olarak düşünün. Bu, toplam kâr veya toplam maliyet gibi, maksimize etmek ya da minimize etmek istediğiniz formüldür. Bu bir sayı değil mutlaka bir formül olmalı; çünkü Solver, ayarlamalar yaptığınızda bunun nasıl değiştiğini görmelidir.

Karar değişkenleri neyi bulmaya çalıştığınızdır. Kaç sandalye üretmeliyiz? Her pazarlama kanalına ne kadar harcamalıyız? Ben bunlara her zaman sıfırdan başlarım; Solver çalıştıktan sonra nelerin değiştiğini görmek kolay olur.

Kısıtlar sınırlarınız ve kurallarınızdır. Bütçenizden fazlasını harcayamazsınız. Negatif miktarlar üretemezsiniz. Bazı projeler asgari yatırım gerektirir. Bu kısıtlar, matematik probleminizi gerçek iş kısıtlarını yansıtan gerçekçi bir yapıya dönüştürür.

İşleyiş şöyle: Solver, kısıtların tamamı sağlanırken amacınızı optimize etmek için karar değişkenlerinizi ayarlar. Yöneylem araştırması yaptıysanız tanıdık gelecektir. Yapmadıysanız, garantili akıllı deneme-yanılma gibi düşünebilirsiniz.

Goal Seek ile karşılaştırma

Bu sık sorulur. Goal Seek daha basittir. Belirli bir hedefe ulaşmak için tek bir hücreyi değiştirir. İstediğiniz değeri biliyorsanız ve sizi oraya götürecek girdiyi bulmanız gerekiyorsa onu kullanın.

Solver farklıdır. Birden çok karar ve kısıt olduğunda en iyi cevabı bulur. ISolver ihtiyaç duyduğunuzda, birden fazla değişkeni gözeterek bir şeyi maksimize ya da minimize etmek için kullanılır.

Benim genel kuralım: Tam olarak hangi sayıyı istediğinizi söyleyebiliyorsanız Goal Seek’i kullanın. “Bunu yapmanın en iyi yolu nedir?” diye soruyorsanız Solver’ı kullanın.

Solver Eklentisini Yükleme ve Etkinleştirme

Solver Excel ile birlikte gelir, ancak önce etkinleştirmeniz gerekir. Endişelenmeyin, bir dakikadan kısa sürer.

Windows ve Mac için kurulum adımları

Windows için:

1. Dosya > Seçenekler > Eklentiler
2. Yönet açılır menüsünden Excel Eklentileri’ni seçin, Git’e tıklayın
3.  Solver Add-in’i işaretleyin, Tamam’a tıklayın
4.  Veri sekmesine gidin ve Çözümleme grubunda Solver’ı bulun

Mac için:

1. Araçlar > Excel Eklentileri
2.  Solver Add-in’i işaretleyin, Tamam’a tıklayın

Excel Online kullanıyorsanız kötü bir haberim var. Solver’ı desteklemiyor. Masaüstü sürümüne (2016 veya sonrası) ihtiyacınız olacak.

Yapılandırma

Etkinleştirdikten sonra Solver’a Veri sekmesi > Solver düğmesi üzerinden erişirsiniz. Varsayılan ayarlar çoğu problem için gayet uygundur; bu kısmı fazla düşünmeyin.

Yine de sorun yaşarsanız iki ayar önemlidir. Hassasiyet, Solver’ın kısıtları ne kadar sıkı sağladığını kontrol eder. Kuruşların önemli olduğu finansal modellerde bunu sıkılaştırırım. Yakınsama, Solver’a çözümü iyileştirmeyi ne zaman bırakacağını söyler. Solver çok erken, tam optimal olmayan bir cevapla durursa bunu düşürürüm.

İkisini de Solver Parametreleri iletişim kutusu > Seçenekler düğmesinde bulabilirsiniz; ama açıkçası nadiren dokunurum.

Probleminizi Kurma: Excel’de Model Formülasyonu

Düzenli bir elektronik tablo, Solver yapılandırmasını kolaylaştırır.

Bir örnek üzerinden ilerleyelim: sandalye ve masa üreten bir mobilya imalatçısı. Sandalyeler 60 $ kâr getirir (4 işçilik saati, 2 birim odun gerekir). Masalar 75 $ kâr getirir (6 işçilik saati, 2 birim odun gerekir). Elimizde 240 işçilik saati ve 100 birim odun var. Her birinden kaç üretmeliyiz?

Problem tanımı ve elektronik tablo kurulumu

Her Solver modelini aynı şekilde düzenlerim. Her zaman dört bölüm:

1. Girdi verileri (ürün bilgisi, kaynak limitleri)
2. Karar değişkenleri (üretilecek miktarlar)
3. Amaç fonksiyonu (toplam kâr)
4. Kısıt hesaplamaları (kullanılan kaynaklar vs. mevcutlar)

Tüm dört bölümüyle eksiksiz Solver modeli. Görsel: Yazar.

Bu yerleşim her şeyi şeffaf kılar. Modelinize bakan herkes, neyi optimize ettiğinizi ve hangi kısıtlarla çalıştığınızı anında görebilir.

Şimdi, önemli hücreler için her zaman adlandırılmış aralıklar oluştururum. =B8*B3+B9*B4 yazmak yerine =SUMPRODUCT(QuantityToProduce,ProfitPerUnit) yazabilirim. Altı ay sonra hücre başvurularının ne anlama geldiğini unuttuğunuzda çok daha anlaşılır olur.

Adlandırılmış aralıklar formülleri okunur kılar. Görsel: Yazar.

Oluşturmak için: hücreleri seçin, Ad Kutusu’na tıklayın, bir ad yazın, Enter’a basın. İki saniye sürer, sonrasında baş ağrısından kurtarır.

Karar değişkenleri ve amaç fonksiyonu

Karar değişkenlerine sıfırdan başlarım. Bu, karşılaştırmaları kolaylaştırır. Bazıları tahmini değerlerle başlamayı sever; sorun değil ama sıfır benim için işe yarar.

Amaç fonksiyonu mutlaka bir formül olmalı. Mobilya örneğimiz için şunu kullanıyorum:

=SUMPRODUCT(B8:B9,B3:B4)Altyapı:

Bu, (Sandalye × 60 $) + (Masa × 75 $) hesaplamasını yapar.

Basit çarpma ve toplama. Çoğu iş problemi için gereken budur.

Kısıtlar

Kısıtlar, gerçek iş limitlerini matematiğe çevirdiğiniz yerdir. Mobilya üreticimizin iki kaynak kısıtı var:

• İşçilik: =SUMPRODUCT(B8:B9,C3:C4) <= 240

• Odun: =SUMPRODUCT(B8:B9,D3:D4) <= 100

Ve acı tecrübeyle öğrendiğim bir şey: her zaman negatif olmama kısıtlarını ekleyin (B8:B9 >= 0). Aksi halde Solver negatif sandalye üretmeyi önerebilir ki gerçek dünyada bunun karşılığı yok.

Ayrıca bir durum sütunu ekler, şu tür =IF() formülleri kullanırım  =IF(B16<=C16,"OK","AŞILDI"). Solver’ı çalıştırmadan önce anında görsel geri bildirim verir. Yeşil ışıklar devam, kırmızı ise kurulumda bir sorun var demektir.

Adım Adım: Excel’de Solver Nasıl Kullanılır

Tamam, modeli kurduk. Şimdi gerçekten Solver’ı nasıl çalıştıracağınızı göstereyim. Ne yaptığınızı bildiğinizde bu kısım 30 saniye bile sürmez.

Solver Parametreleri iletişim kutusunu kullanma

Solver’ı Veri sekmesi > Solver düğmesine tıklayarak açın.

Yapılandırmadan önce Solver iletişim kutusu. Görsel: Yazar.

Doldurmanız gerekenler:

Amaç Ayarla: Bu alana tıklayın, sonra B12 hücresine tıklayın (Toplam Kâr hücremiz).

Şuna: Maks’ı seçin; çünkü kârı maksimize etmek istiyoruz.

Değiştirilecek Değişken Hücreleri: B8:B9’u seçin (karar vereceğimiz miktarlar).

Kısıtlara Tabi: Her kısıt için Ekle’ye tıklayın:

• İşçilik: $B$16 <= $C$16

• Odun: $B$17 <= $C$17

• Negatif olmama: $B$8:$B$9 >= 0

Bir Çözüm Yöntemi Seçin: Simplex LP’yi seçin. Diğer yöntemleri sonra açıklayacağım, ancak bunun gibi doğrusal problemler için Simplex LP istediğiniz yöntemdir.

Solver yapılandırıldı ve çözmeye hazır. Görsel: Yazar.

Modeli çözme

Çöz’e tıklamadan önce mevcut değerlere hızlıca bakın. Her şeyin sıfır olması gerekir.

Her yerde başlangıçta sıfırlar. Görsel: Yazar.

Şimdi Çöz’e tıklayın. Bizimki gibi basit problemler için bu birkaç saniye sürer.

Başarılı! Solver optimal çözümü buldu. Görsel: Yazar.

Solver Sonuçları iletişim kutusu açıldığında “Solver bir çözüm buldu.” yazmalıdır. Raporlar altında Yanıt ve Duyarlılık kutularını işaretleyin. Bunlar, birazdan inceleyeceğimiz ayrıntılı analizleri verir. Ardından Tamam’a tıklayın.

Optimal çözüm: 30 sandalye ve 20 masa. Görsel: Yazar.

İşte bu kadar. Çözüm, 3.300 $ kâr için 30 sandalye ve 20 masa üretmemiz gerektiğini gösteriyor. Her iki kısıtta da “OK” göründüğüne dikkat edin; bu da kaynaklarımızı sınırları aşmadan verimli kullandığımız anlamına gelir.

İş akışı özeti

Her seferinde izlediğim süreç:

1. Modeli kurun (girdiler, değişkenler, amaç, kısıtlar)
2. Solver’ı yapılandırın (neyi optimize edeceğinizi ve neyi ayarlayacağınızı belirleyin)
3. Çöz’e tıklayın ve bekleyin
4. Çözümün iş açısından mantıklı olup olmadığını kontrol edin
5. Daha derin içgörüler için raporları inceleyin

Son adım önemlidir. Solver bir cevap buldu diye, bunun işletmeniz için doğru cevap olduğu anlamına gelmez. Sonuçları mutlaka sağduyuyla kontrol edin.

Solver Yöntemleri, Parametreleri ve Gelişmiş Seçenekler

Solver size üç farklı algoritma sunar. Doğru olanı seçmek hız ve doğrulukta büyük fark yaratır.

Çözüm yöntemlerine genel bakış

Farklı problem türleri için üç çözüm yöntemi. Görsel: Yazar.

Simplex LP

Zamanın %80’inde Simplex LP kullanırım. Her şeyin sadece toplama ve çarpmadan oluştuğu doğrusal problemler içindir. Ürün karması? Bütçe tahsisi? Kaynak planlama? Simplex LP. Hızlıdır ve size en iyi cevabı garanti eder.

GRG Nonlinear

GRG Nonlinear, formüllerinizde eğriler, kareler veya üstel ifadeler olduğunda kullanılır. Düşünün ki fiyat arttıkça talebin azaldığı ama düz bir çizgi olmayan fiyatlandırma modelleri veya varyans hesaplamalı portföy optimizasyonu. Yerel en iyileri bulur; bu her zaman mutlak en iyi olmayabilir ama genelde yeterince iyidir.

Evolutionary

Evolutionary kaba kuvvet seçeneğidir. Tamsayı kısıtlarınız (sadece tam birimler üretebilirsiniz), ikili kararlarınız (evet ya da hayır) veya formüllerinizde =IF() ifadeleri olduğunda kullanın. Daha yavaştır çünkü temelde bir sürü rastgele çözüm dener ve iyi olanları elde tutar. Ama diğer yöntemler başarısız olduğunda işe yarar.

Benim karar kuralım basit: Simplex LP ile başlayın. Formülleriniz üsler ya da değişkenlerin çarpımlarını içeriyorsa GRG Nonlinear’a geçin. Tamsayı veya ikili değişkenlere ihtiyacınız varsa Evolutionary’yi kullanın.

Solver seçenekleri ve ayarları

Gelişmiş ayarlar için Solver iletişim kutusunda Seçenekler’e tıklayın. Genelde bunlarla oynamam ama önemli olanlar şunlar:

Solver’ın ne kadar süre çalışacağını Maks Süre ile sınırlayın. Varsayılan 100 saniyedir; çoğu problem için fazlasıyla yeterlidir. Daha fazla düşünme süresi gerektiren çok büyük modellerde artırırım.

Solver’ın kaç deneme yapacağını Yineleme ile sınırlayın. Çözemeden bu sınıra ulaşırsa artırın.

Solver’ın kısıtlar konusunda ne kadar katı olacağını Hassasiyet ile kontrol edin. Her kuruşun önemli olduğu finansal modellerde bunu sıkılaştırırım, aksi halde dokunmam.

Rapor Oluşturma ve Duyarlılık Analizi

Rapor kutularını işaretlemenizi söylediğimi hatırlıyor musunuz? İşte elde edecekleriniz.

Yanıt Raporu çözümü belgelendirir. Görsel: Yazar.

Yanıt Raporu iz sürme belgenizdir. Nelerin değiştiğini gösterir: karar değişkenleri 0’dan 30 sandalye ve 20 masaya çıktı, kâr 0 $’dan 3.300 $’a yükseldi. Ayrıca hangi kısıtların bağlayıcı (tam kullanılmış) hangilerinin bağlayıcı olmadığı (boşluk bırakan) bilgisini verir. Bizim durumda hem işçilik hem odun bağlayıcı; yani mevcut kapasitenin tamamını kullanıyoruz.

Çözüm değişmeden girdiler ne kadar değişebilir. Görsel: Yazar.

Duyarlılık Raporu işin ilginç kısmıdır. İzin Verilen Artış/Azalış sütunları, farklı bir çözüme ihtiyaç duymadan önce girdilerinizin ne kadar değişebileceğini gösterir. Geniş aralıklar cevabınızın sağlam olduğu anlamına gelir. Dar aralıklar küçük değişimlerin her şeyi tersine çevirebileceğini gösterir.

Gölge fiyatlar kaynak değerini gösterir. Görsel: Yazar.

Asıl altın değerinde olan ise gölge fiyattır. Bu, bir kaynak biriminin daha ne kadar değer kattığını söyler. İşçiliğin gölge fiyatı 7,5 $ ise, bir işçilik saati daha kâra 7,5 $ ekler. Dolayısıyla saatlik 5 $’a geçici işçi bulabiliyorsanız yapın. 10 $’da ise vazgeçin.

Duyarlılık analizinin önemi

Bu raporları üç soruyu yanıtlamak için kullanırım: Hangi kaynaklara yatırım yapmalıyım? Çözümüm kırılgan mı? Tahminlerim yanlış çıkarsa ne olur? Raporlar üçünü de net yanıtlar.

Önce, hangi kaynaklara yatırım yapmam gerektiğine karar vermek için gölge fiyatlara bakarım. Gölge fiyatı yüksek olanın değeri yüksektir.

Sonra, çözümümün kırılgan olup olmadığını görmek için izin verilen aralıklara bakarım. Çözüm değişmeden kâr sadece 1 $ değişebiliyorsa ince buz üstündesiniz. 50 $ değişebiliyorsa durum iyi.

Son olarak, tahminlerim yanlış çıkarsa ne olur diye sorarım. İzin verilen aralıklar, tekrar hesaplama gerektirmeden önce ne kadar yanılabileceğinizi söyler.

Tavsiyem: bir bütçeyi onaylamadan önce gölge fiyatları mutlaka kontrol edin. Bir kaynağın gölge fiyatı sıfır ise, fazla kapasiteniz var demektir. Oraya daha fazla yatırım yapmak israftır. Parayı gölge fiyatların en yüksek olduğu yere koyun.

Sorun Giderme ve Hata Çözümü

Solver’ın hata mesajları korkutucu gelebilir ama genelde çözmesi kolaydır. En sık gördüklerim şunlar.

Yaygın hatalar ve mesajlar

Solver uygun (feasible) bir çözüm bulamadı

Bu, kısıtlarınızın birbiriyle çeliştiği anlamına gelir.

 Hiçbir çözüm tüm kısıtları sağlamıyor. Görsel: Yazar.

Genellikle kaynakların destekleyemeyeceği bir kâr hedefi gibi imkânsız bir şey istediğinizde olur. Çözüm, Solver çalışana kadar kısıtları tek tek kaldırmaktır; bu yöntem tam olarak hangi kısıtın çatışmaya neden olduğunu belirler.

Ayarlanan Hücre değerleri yakınsamıyor

Bu, amacınızın sonsuza dek büyüyebileceği anlamına gelir. Bir yerde üst sınır eksik. Gerçek işletmelerin her zaman limitleri vardır, bunları ekleyin.

Solver mevcut çözümü iyileştiremiyor

Bu genelde doğrusal olmayan problemlerle olur. Farklı başlangıç değerleri deneyin veya Evolutionary yöntemine geçin. Bazen yerel optimum yeterince iyidir.

Model formülasyonu hataları

Döngüsel başvurular

A hücresi B’ye, B hücresi A’ya bağlı olduğunda ortaya çıkar. Excel sizi uyarır. Döngüyü bulup kırmak için Formüller sekmesi > Bağımlılıkları İzle’yi kullanın.

Tamsayı kısıtları

Tam sayılar için tamsayı kısıtları ekleme. Görsel: Yazar.

Geniş Ölçekli Problemler için Solver Sınırlamaları ve Alternatifler

Excel Solver harikadır, ancak bilmeniz gereken sınırları vardır.

Solver’ın sınırlamalarını anlamak

Standart Solver, 200 karar değişkeni ve 100 kısıtta tıkanır. Çok gibi gelebilir ama 200 çalışanı 50 zaman dilimine göre planlamaya çalıştığınızda durum değişir. Bu 10.000 değişkendir ve Solver’ın çok ötesindedir.

Solver’ı aylık üretim planları, üç aylık bütçeler ve proje seçimi için kullanırım. Taktiksel kararlar için idealdir. Ancak binlerce lokasyon içeren tedarik zinciri ağları gibi kurumsal ölçekteki problemler için tasarlanmamıştır.

Büyük problemler için alternatifler

OpenSolver

OpenSolver ücretsizdir ve bu sınırları kaldırır. Bilgisayarınızın belleğinin izin verdiği kadar çok değişkeni işler, Excel Solver ile aynı arayüzü kullanır ve OpenSolver.org’dan indirilebilir. Standart Solver’ı aştığınızda öneririm.

Frontline Solver Premium

Frontline Solver ücretlidir (yıllık 300–1.500 $) ama binlerce değişkeni daha iyi algoritmalarla destekler. Bunu tam zamanlı profesyonelce yapsaydım kullanacağım araç bu olurdu.

Bağımsız yazılımlar

 CPLEX veya Gurobi gibi araçlar milyonlarca değişkeni işler ancak programlama bilgisi ve ciddi yatırım gerektirir. Bu, kurumsal seviye bir konudur.

Bana göre, çoğu iş analisti için Excel Solver ihtiyacın %95’ini karşılar. Sınırlarına dayanırsanız bu aslında iyi bir işaret; daha iyi araçları haklı çıkaracak kadar sofistike işler yapıyorsunuz demektir.

Sonuç

Temel kurulumdan gölge fiyatların yorumlanmasına kadar çok şey ele aldık. 

Artık bir toplantı odasında fikir tartışmak yerine sayıları ekrana koyabilirsiniz. “İşte matematiksel olarak en iyi plan.” Bu, konuşmanın seyrini değiştirir. Tahmin etmeyi bırakır, kanıtlamaya başlarsınız.

Bir sonraki karmaşık probleminizde deneyin; ertelediğiniz o planlama çakışması veya bütçe sıkışıklığı. Mükemmel cevabın aslında hep elektronik tablonuzda saklı olduğunu görebilirsiniz.

Patronunuzu etkileyecek daha fazla teknik öğrenmek isterseniz, Excel’de Veri Analizi kursumuzu alarak kendinizi geliştirmeye devam edin.