Sorunun formülasyonu
Çalıştığınız şirketin, malların Moskova'ya dağılmış beş mağazanıza gittiği üç deposu olduğunu varsayalım.
Her mağaza, bildiğimiz belirli miktarda mal satabilir. Depoların her birinin sınırlı bir kapasitesi vardır. Görev, toplam nakliye maliyetlerini en aza indirmek için malları hangi depodan hangi mağazalara teslim edeceğini rasyonel olarak seçmektir.
Optimizasyona başlamadan önce, durumu açıklayan matematiksel modelimiz olan bir Excel sayfasında basit bir tablo derlemek gerekecektir:
Anlaşılan budur ki:
- Açık sarı tablo (C4:G6), her depodan her mağazaya bir öğenin nakliye maliyetini açıklar.
- Mor hücreler (C15:G14), her mağazanın satması için gereken mal miktarını tanımlar.
- Kırmızı hücreler (J10:J13) her bir deponun kapasitesini, yani deponun tutabileceği maksimum mal miktarını gösterir.
- Sarı (C13:G13) ve mavi (H10:H13) hücreler, sırasıyla yeşil hücreler için satır ve sütun toplamlarıdır.
- Toplam nakliye maliyeti (J18), ürün sayısının ve bunlara karşılık gelen nakliye maliyetlerinin toplamı olarak hesaplanır - hesaplama için fonksiyon burada kullanılır SUMPRODUCT (TOPLAMÇARPIM).
Böylece görevimiz yeşil hücrelerin optimal değerlerinin seçimine indirgenmiştir. Ve böylece hattın toplam miktarı (mavi hücreler) deponun kapasitesini (kırmızı hücreler) aşmaz ve aynı zamanda her mağaza satması gereken mal miktarını alır (mağazadaki her mağaza için miktar). sarı hücreler gereksinimlere mümkün olduğunca yakın olmalıdır - mor hücreler).
Çözüm
Matematikte, kaynakların optimal dağılımını seçmeyle ilgili bu tür problemler uzun süredir formüle edilmiş ve tanımlanmıştır. Ve tabii ki, onları çözmenin yolları uzun süredir (ki bu çok uzun) açık olmayan numaralandırmayla değil, çok az sayıda yinelemeyle geliştirilmiştir. Excel, bir eklenti kullanarak kullanıcıya bu tür işlevsellik sağlar. Arama Çözümleri (Çözücü) sekmeden Veri (Tarih):
sekmesinde ise Veri Excel'inizde böyle bir komut yok – sorun değil – bu, eklentinin henüz bağlanmamış olduğu anlamına gelir. Etkinleştirmek için açın filetoArdından seçmek parametreler - Eklentiler - Hakkımızda (Seçenekler — Eklentiler — Git). Açılan pencerede ihtiyacımız olan satırın yanındaki kutucuğu işaretleyin. Arama Çözümleri (Çözücü).
Eklentiyi çalıştıralım:
Bu pencerede, aşağıdaki parametreleri ayarlamanız gerekir:
- Hedef işlevi optimize edin (t'yi ayarlapara hücre) – burada optimizasyonumuzun nihai ana hedefini, yani toplam nakliye maliyetini içeren pembe kutuyu (J18) belirtmek gerekir. Hedef hücre minimize edilebilir (bizim durumumuzda olduğu gibi gider ise), maksimize edilebilir (örneğin kar ise) veya belirli bir değere getirmeye çalışabilir (örneğin, tahsis edilen bütçeye tam olarak sığdırılabilir).
- Değişken Hücreleri Değiştirme (By değiştirme hücreler) – burada, sonucu elde etmek istediğimiz değerleri değiştirerek yeşil hücreleri (C10: G12) belirtiyoruz – minimum teslimat maliyeti.
- Kısıtlamalarla tutarlı (Konu için the kısıtlamalar) – optimize ederken dikkate alınması gereken kısıtlamaların bir listesi. Listeye kısıtlamalar eklemek için düğmesine tıklayın. Ekle (Ekle) ve beliren pencerede koşulu girin. Bizim durumumuzda, bu talep kısıtlaması olacaktır:
ve maksimum depo hacmi sınırı:
Fiziksel faktörlerle ilişkili bariz sınırlamalara (depoların kapasitesi ve ulaşım araçları, bütçe ve zaman kısıtlamaları vb.) ek olarak, bazen “Excel'e özel” kısıtlamalar eklemek gerekir. Örneğin, Excel, malları mağazalardan depoya geri taşımayı teklif ederek teslimat maliyetini "optimize etmeniz" için kolayca ayarlayabilir - maliyetler negatif olur, yani biz kar ederiz! 🙂
Bunun olmasını önlemek için, onay kutusunu etkin bırakmak en iyisidir. Sınırsız Değişkenleri Negatif Olmayan Yapın hatta bazen bu tür anları kısıtlamalar listesine açıkça kaydedin.
Gerekli tüm parametreleri ayarladıktan sonra pencere şöyle görünmelidir:
Bir çözme yöntemi seçin açılır listesinde ayrıca üç seçenek arasından seçim yapmak için uygun matematiksel yöntemi seçmeniz gerekir:
- Simpleks yöntemi lineer problemleri, yani çıktının lineer olarak girdiye bağlı olduğu problemleri çözmek için basit ve hızlı bir yöntemdir.
- Genel Düşürülmüş Gradyan Yöntemi (OGG) – girdi ve çıktı verileri arasında doğrusal olmayan karmaşık bağımlılıkların olduğu doğrusal olmayan problemler için (örneğin, satışların reklam maliyetlerine bağımlılığı).
- Çözüm için evrimsel arayış – biyolojik evrim ilkelerine dayanan nispeten yeni bir optimizasyon yöntemi (merhaba Darwin). Bu yöntem, ilk ikisinden çok daha uzun süre çalışır, ancak hemen hemen her sorunu çözebilir (doğrusal olmayan, ayrık).
Görevimiz açıkça doğrusaldır: 1 parça teslim edildi - 40 ruble harcandı, 2 parça teslim edildi - 80 ruble harcandı. vb., bu nedenle simpleks yöntemi en iyi seçimdir.
Artık hesaplama için veriler girildiğine göre, düğmesine basın. Bir çözüm bul (Çözmek)optimizasyonu başlatmak için Çok sayıda değişen hücre ve kısıtlama içeren ciddi durumlarda, bir çözüm bulmak uzun zaman alabilir (özellikle evrimsel yöntemle), ancak Excel için görevimiz bir sorun olmayacak - birkaç dakika içinde aşağıdaki sonuçları alacağız :
Depolarımızın kapasitesini aşmadan ve her mağaza için gerekli sayıda mal için tüm talepleri karşılarken, tedarik hacimlerinin mağazalar arasında ne kadar ilginç bir şekilde dağıldığına dikkat edin.
Bulunan çözüm bize uygunsa, kaydedebilir veya orijinal değerlere geri dönüp diğer parametrelerle tekrar deneyebiliriz. Ayrıca seçilen parametre kombinasyonunu şu şekilde kaydedebilirsiniz: senaryo. Kullanıcının isteği üzerine Excel üç tür oluşturabilir Raporlar ayrı sayfalarda çözülen problem hakkında: sonuçlar hakkında bir rapor, çözümün matematiksel kararlılığı hakkında bir rapor ve çözümün sınırları (kısıtlamaları) hakkında bir rapor, ancak çoğu durumda bunlar sadece uzmanların ilgisini çekiyor .
Bununla birlikte, Excel'in uygun bir çözüm bulamadığı durumlar da vardır. Örneğimizde depoların toplam kapasitesinden daha fazla miktarda mağazaların ihtiyaçlarını belirtirsek, böyle bir durumu simüle etmek mümkündür. Ardından, bir optimizasyon gerçekleştirirken Excel, çözüme mümkün olduğunca yaklaşmaya çalışacak ve ardından çözümün bulunamadığına dair bir mesaj görüntüleyecektir. Bununla birlikte, bu durumda bile birçok faydalı bilgiye sahibiz – özellikle iş süreçlerimizin “zayıf halkalarını” görebilir ve iyileştirme alanlarını anlayabiliriz.
Ele alınan örnek elbette nispeten basittir, ancak çok daha karmaşık sorunları çözmek için kolayca ölçeklenir. Örneğin:
- Finansal kaynakların dağılımının optimizasyonu iş planındaki veya projenin bütçesindeki harcama kalemine göre. Bu durumda kısıtlamalar, finansman miktarı ve projenin zamanlaması olacaktır ve optimizasyonun amacı, karı maksimize etmek ve proje maliyetlerini minimize etmektir.
- Çalışan planlama optimizasyonu işletmenin ücret fonunu en aza indirmek için. Bu durumda kısıtlamalar, her çalışanın istihdam programına ve personel tablosunun gereksinimlerine göre istekleri olacaktır.
- Yatırım yatırımlarının optimizasyonu - yine, karı maksimize etmek veya (daha önemliyse) riskleri en aza indirmek için fonları birkaç banka, menkul kıymet veya teşebbüs hissesi arasında doğru bir şekilde dağıtma ihtiyacı.
Her durumda, eklenti Arama Çözümleri (çözücü) çok güçlü ve güzel bir Excel aracıdır ve modern iş hayatında karşılaşmanız gereken birçok zor durumda yardımcı olabileceğinden dikkatinizi hak eder.