MS Excel'deki optimizasyon sorunlarını çözme aracı, eklenti Bir çözüm bulmak. Çözüm bulma prosedürü, hedef adı verilen bir hücrede bulunan formülün optimum değerini bulmanızı sağlar. Bu prosedür, hedef hücredeki bir formülle doğrudan veya dolaylı olarak ilişkili bir grup hücreyle çalışır. Prosedür, hedef hücrede bulunan formülden istenen sonucu elde etmek için etkilenen hücrelerdeki değerleri değiştirir.

Bu eklenti yüklüyse, o zaman Bir çözüm bulmak menüden başlar Hizmet... Böyle bir öğe yoksa, komutu çalıştırmalısınız. Hizmet → Eklentiler ... ve eklentiye karşı kutuyu işaretleyin
Bir çözüm bulmak(Şekil 2.1).

Takım Hizmet → Bir çözüm bulmak bir iletişim kutusu açar "Bir çözüm arayın".

Pencerede Bir çözüm bulmakaşağıdaki alanlar mevcuttur:

Hedef hücreyi ayarlayın- değeri maksimize edilmesi, küçültülmesi veya belirli bir sayıya eşit olarak ayarlanması gereken hedef hücreyi göstermeye yarar. Bu hücre, formülü içermelidir.

Eşit- hedef hücre değeri için optimizasyon seçeneğini seçmeye yarar (maksimizasyon, minimizasyon veya belirli bir sayının seçimi). Bir numara ayarlamak için alana girin.

Hücreleri değiştirme- Getirilen kısıtlamalara kadar bir çözüm bulma sürecinde değerleri değişen hücreleri ve Hedef hücreyi ayarla alanında belirtilen hücrenin değerini optimize etme koşulu karşılanana kadar hücreleri göstermeye yarar.

Tahmin- Hedef hücre belirle alanında başvurulan formülü etkileyen hücreleri otomatik olarak aramak için kullanılır. Arama sonucu, Hücreleri değiştir alanında görüntülenir.

Sınırlamalar- görev için sınır koşullarının bir listesini görüntülemeye yarar.

a ekle- Kısıtlama ekle iletişim kutusunu görüntülemeye yarar.

Düzenle- Kısıtlamayı Düzenle iletişim kutusunu görüntüler.

Sil- Belirtilen kısıtlamayı kaldırmaya yarar.

Yürüt- Soruna bir çözüm aramaya başlar.

Kapat- Soruna çözüm aramaya başlamadan iletişim penceresinden çıkmaya yarar.

Çözüm arama parametreleri,burada optimize edilmiş modeli yükleyebilir veya kaydedebilir ve bir çözüm bulmak için sağlanan seçenekleri belirtebilirsiniz.

Yeniden kur- İletişim kutusunun alanlarını temizlemeye ve arama parametrelerinin varsayılan değerlerini geri yüklemeye yarar.

Optimizasyon sorununu çözmek için aşağıdaki adımları izleyin.

1. Menüde Hizmettakım seç Bir çözüm arayın.

2. Sahada Hedef hücreyi ayarlayınoptimize edilecek modelin bulunduğu hücrenin adresini veya adını girin.

3. Etkileyen hücrelerin değerlerini değiştirerek hedef hücrenin değerini en üst düzeye çıkarmak için radyo düğmesini maksimum değer.

Etkileyen hücrelerin değerlerini değiştirerek hedef hücrenin değerini en aza indirmek için radyo düğmesini
minimum değer.

Etkileyen hücrelerin değerlerini değiştirerek hedef hücredeki değeri belirli bir sayıya ayarlamak için radyo düğmesini anlamve gerekli numarayı ilgili alana girin.

4. sahada Hücreleri değiştirmevirgülle ayırarak değiştirilecek hücrelerin adlarını veya adreslerini girin. Değiştirilecek hücreler doğrudan veya dolaylı olarak hedef hücreye bağlanmalıdır. 200'e kadar değişken hücrenin kurulumuna izin verilir.

Model formülünü etkileyen tüm hücreleri otomatik olarak bulmak için Tahmin.

5. Sahada Sınırlamalarbir çözüm bulmaya getirilen tüm kısıtlamaları girin.

6. düğmesine basın Yürüt.

Orijinal verileri geri yüklemek için anahtarı konumuna getirin.

Aşama C. Optimizasyon problemine bulunan çözümün analizi.

Çözümün sonucuyla ilgili son mesajı görüntülemek için iletişim kutusunu kullanın Çözüm arama sonuçları.

Çözüm Sonuçları İletişim Kutusuaşağıdaki alanları içerir:

Orijinal değerleri geri yükleyin- modelin etkileyen hücrelerinin orijinal değerlerini geri yüklemeye yarar.

Raporlar- kitabın ayrı bir sayfasına yerleştirilecek raporun türünü belirtmeye yarar.

Sonuçlar.Bir hedef hücre ve etkileyen model hücrelerin bir listesinden, bunların kaynak ve hedef değerlerinden, kısıtlama formüllerinden ve uygulanan kısıtlamalarla ilgili ek bilgilerden oluşan bir rapor oluşturmak için kullanılır.

İstikrar.Çözümün formüldeki küçük değişikliklere duyarlılığı hakkında bilgi içeren bir rapor oluşturmak için kullanılır (alan Hedef hücreyi ayarlayın,iletişim penceresi Bir çözüm arayın)veya kısıtlama formüllerinde.

Sınırlamalar.Bir hedef hücre ve etkileyen model hücrelerin, bunların değerlerinin ve alt ve üst sınırlarının bir listesinden oluşan bir rapor oluşturmak için kullanılır. Bu rapor, birden çok tamsayı ile sınırlı değerlere sahip modeller için oluşturulmaz. Alt sınır, etki eden bir hücrenin içerebileceği en küçük değerdir, diğer yandan kalan etki eden hücrelerin değerleri sabittir ve empoze edilen kısıtlamaları karşılar. Buna göre en yüksek değere üst limit denir.

Komut dosyasını kaydet- bir iletişim kutusu görüntülemeye yarar Komut dosyasını kaydetmek,mS Excel komut dosyası yöneticisini kullanarak ileride kullanmak üzere sorunu çözmek için komut dosyasını kaydedebileceğiniz. Aşağıdaki bölümlerde, MS Excel kullanarak birkaç özel doğrusal optimizasyon modelini ve çözümlerinin örneklerini ele alacağız.

2.4 Üretim planlamasının görevi

Sorunun formülasyonu.Şirket ürün üretmeli n Türler: ve 1, ve 2, ... ve nve üretilen her ürünün miktarı talebi aşmamalıdır β 1, β 2, ..., β n ve aynı zamanda planlanan değerlerden daha az olmamalıdır b 1, b 2, ..., b nsırasıyla. Ürünlerin imalatı için gider m hammadde türleri s l, s 2, ..., s mrezervleri sırasıyla γ değerleri ile sınırlandırılmıştır. 1 , γ 2 ,..., γ m.İmalat için olduğu bilinmektedir. ben-nci ürün gider ve ijbirimleri j inci hammaddeler. Ürün satışından elde edilen kar u 1, ve 2, ... ve nsırasıyla eşit s 1, s 2, ..., s s.Ürünlerin üretiminin kar maksimum olacak şekilde planlanması ve aynı zamanda her bir ürünün üretim planının yerine getirilmesi ancak talebin aşılmaması gerekmektedir.

Matematiksel model.Gösteren x 1, x 2, ... x nöğe sayısı u 1, ve 2, ... ve n,işletme tarafından üretilmiştir. Planın getirdiği kâr (amaç işlevi) şuna eşit olacaktır:

z \u003d z (x 1, x 2, ..., x n) \u003d c 1 x 1 + c 2 x 2 + ... + c n x n → maks. Planın uygulanmasına ilişkin kısıtlamalar şu şekilde yazılacaktır: x ben ≥ β beni \u003d 1,2, ..., n için talebi aşmamak için ürünlerin üretimini sınırlandırmak gerekir: x ben ≤β i için ben\u003d 1,2, ... n. Ve son olarak, hammaddelere ilişkin kısıtlamalar bir eşitsizlikler sistemi biçiminde yazılacaktır:

α 11 x 1 + α 12 x 2 + ... + α 1n x n ≤b 1

α 21 x 1 + α 22 x 2 + ... + α 2n x n ≤b 2

................................................

α m1 x 1 + α m2 x 2 + ... + α mn x n ≤b m

şartıyla x 1, x 2, ... x nnegatif değildir.

Örnek 2.1:

Sorunun belirli bir örneğini düşünün. üretim planlamasıve MS Excel kullanarak çözmek için gereken bir dizi eylem vereceğiz.

Görev.Şirket, iki tür betonarme ürün üretmektedir: merdiven basamakları ve balkon plakaları. Tek kat merdiven üretimi için 3,5 metreküp kullanmanız gerekir. beton ve 1 paket donatı ve döşeme üretimi için - 1 metreküp. beton ve 2 paket donatı. Her ürün birimi 1 adam-gün işçiliğe karşılık gelir. 1 kat merdiven satışından elde edilen kar 200 ruble ve bir tabak - 100 ruble. Şirket 150 kişiyi istihdam etmektedir ve şirketin günde 350 metreküpten fazla üretim yapmadığı biliniyor. beton ve 240 paketten fazla donatı teslim edilmez. Üretilen ürünlerden elde edilen kazancın maksimize edilmesi için böyle bir üretim planının hazırlanması gerekmektedir.

Karar.

1. MS Excel çalışma kitabının sayfasındaki görev parametreleri tablosunu doldurun (Şekil 2.2).

2. Bir problem modeli oluşturun ve değişken değerler için hücreleri doldurun (orijinal olarak hücreler x (ve x zkeyfi sayısal değerlerle doldurulur, örneğin, değer 10), amaç işlevi (hücre bir formül içerir) ve kısıtlamalar (hücreler formül içerir)
(şekil 2.2)

3. Komutu çalıştırın Çözüm için Servis Aramasıve iletişim kutusunun alanlarında gerekli değerleri ayarlayın Bir çözüm arayın,pencereye kısıtlamalar eklemek Kısıtlamalar ekleniyor.

Yorum Yap.Pencerede Kısıtlamalar eklemekgerekirse, model değişkenlerinin tamsayı değerine kısıtlamalar koymak mümkündür.

4. Düğmeye bas Yürütve pencerede parametreleri ayarlayın Çözüm arama sonuçları(değiştirmek Bulunan çözümü kaydedinveya Orijinal değerleri geri yükleyinve Rapor türü).

Yorum Yap:Formüllerde, kısıtlamalarda veya yanlış model parametrelerinde hata olması durumunda, bu pencerede şu mesajlar görünebilir: "Hedef hücre değerleri yakınsamıyor", "Arama çözüm bulamıyor" veya "Doğrusal model koşulları karşılanmıyor" . Bu durumda, anahtar konumuna ayarlanmalıdır. Orijinal değerleri geri yükleyin,sayfadaki verileri kontrol edin ve bir çözüm bulmak için prosedürü tekrarlayın.

5. Sonuç olarak, görev değişkenlerine sahip hücreler, optimum plana karşılık gelen değerleri (günde 80 kat merdiven ve 70 döşeme levhası) ve hedef işlev hücresinde karşılık gelen kar değeri (23.000 ruble) içerecektir. bu plana (Şekil 2.3)

6. Elde edilen çözüm tatmin ediciyse, en uygun planı kaydedebilir ve ayrı bir sayfada görüntülenen arama sonuçlarına aşina olabilirsiniz.

Bir egzersiz:

Kontrol. 2.1. Kuruluş, ortak bir bileşen deposu kullanarak TV'ler, müzik setleri ve hoparlör sistemleri üretir. Depodaki şasi stoğu 450 adettir., Kinescopes - 250 adet, Hoparlörler - 800 adet, Güç kaynakları - 450 adet, Panolar - 600 adettir. Her ürün için tabloda belirtilen bileşen sayısı tüketilir:

Bir TV setinin üretiminden elde edilen kâr 90 $, bir stereo sistem - 50 ve bir ses sistemi - 45'tir. Tüm ürünlerin üretiminden elde edilecek kârın elde edileceği üretim hacimlerinin optimal oranını bulmak gerekir. maksimum.

MS Excel'de doğrusal programlama problemlerini çözme

MS Excel'de optimizasyon sorunlarını çözme aracı, "Çözüm ara" eklentisidir. . Çözüm bulma prosedürü, hedef adı verilen bir hücrede bulunan formülün optimum değerini bulmanızı sağlar. Bu prosedür, hedef hücredeki bir formülle doğrudan veya dolaylı olarak ilişkili bir grup hücreyle çalışır. Prosedür, hedef hücrede bulunan formülden istenen sonucu elde etmek için etkilenen hücrelerdeki değerleri değiştirir.

Bu eklenti yüklüyse, "Araçlar" menüsünden "Çözüm ara" başlatılır. Böyle bir öğe yoksa, "Hizmet - Eklentiler ..." komutunu çalıştırın ve "Çözüm ara" eklentisinin yanındaki kutuyu işaretleyin.

Optimizasyon probleminin çözümü üç aşamadan oluşmaktadır.

A. Optimizasyon probleminin bir modelinin oluşturulması.

B. Optimizasyon problemine bir çözüm arayın.

C. Optimizasyon problemi için bulunan çözümün analizi.

Gelin bu aşamalara daha yakından bakalım.

Aşama A.

Model oluşturma aşamasında bilinmeyenlerin tanımları girilir, problemin ilk verilerini içeren aralıklar çalışma sayfasına doldurulur, amaç fonksiyonu için formül girilir.

Aşama B.

"Araçlar - Çözüm Bul" komutu, sırayla aşağıdaki alanları içeren "Çözüm Bul" iletişim kutusunu açar:

"Hedef hücreyi ayarla" - değeri maksimize edilmesi, simge durumuna küçültülmesi veya belirtilen sayıya eşit olarak ayarlanması gereken hedef hücreyi belirtmeye yarar. Bu hücre bir formül içermelidir.

"Eşit" - hedef hücre değeri için optimizasyon seçeneğini seçmeye yarar (maksimize etme, küçültme veya belirli bir sayının seçimi). Bir numara ayarlamak için alana girin.

"Hücrelerin değiştirilmesi" - getirilen kısıtlamalara ve "Hedef hücre belirle" alanında belirtilen hücrenin değerini optimize etme koşulu karşılanana kadar bir çözüm bulma sürecinde değerleri değişen hücreleri göstermeye yarar .

Tahmin - Hedef Hücreyi Ayarla alanında başvurulan formülü etkileyen hücreleri otomatik olarak bulmak için kullanılır. Arama sonucu "Hücrelerin değiştirilmesi" alanında görüntülenir.

"Kısıtlamalar" - görev için sınır koşullarının bir listesini görüntülemeye yarar.

Ekle - Kısıtlama Ekle iletişim kutusunu görüntüler.

Değiştir - Kısıtlamayı Değiştir iletişim kutusunu görüntüler.

"Sil" - belirtilen kısıtlamayı kaldırmaya yarar.

"Çalıştır" - soruna bir çözüm aramaya başlamak için kullanılır.

"Kapat" - soruna bir çözüm aramaya başlamadan iletişim penceresinden çıkmaya yarar. Aynı zamanda "Seçenekler, Ekle, Değiştir veya Kaldır" butonlarına basıldıktan sonra açılan diyalog pencerelerinde yapılan ayarlar kaydedilir.

Seçenekler - En iyileştirilmiş modeli yükleyebileceğiniz veya kaydedebileceğiniz ve bir çözüm bulma seçeneklerini belirtebileceğiniz Çözücü Seçenekleri iletişim kutusunu görüntüler.

"Geri Yükle" - iletişim kutusunun alanlarını temizlemek ve arama çözümü parametrelerinin varsayılan değerlerini geri yüklemek için kullanılır.

Optimizasyon sorununu çözmek için aşağıdaki adımları izleyin.

1. "Araçlar" menüsünde "Çözüm Bul" komutunu seçin.

2. "Hedef Hücreyi Ayarla" kutusuna, optimize edilecek modelin formülünü içeren hücrenin adresini veya adını girin.

3. Etkileyen hücrelerin değerlerini değiştirerek hedef hücrenin değerini en üst düzeye çıkarmak için anahtarı maksimum değere karşılık gelen konuma ayarlayın.

Etkileyen hücrelerin değerlerini değiştirerek hedef hücrenin değerini en aza indirmek için anahtarı minimum değere karşılık gelen konuma ayarlayın.

Etkileyen hücrelerin değerlerini değiştirerek hedef hücredeki değeri belirli bir sayıya ayarlamak için, değer vermek için radyo düğmesini seçin ve ilgili alana gerekli sayıyı girin.

4. "Hücreleri Değiştir" alanına, değiştirilen hücrelerin adlarını veya adreslerini virgülle ayırarak girin. Değiştirilecek hücreler doğrudan veya dolaylı olarak hedef hücreye bağlanmalıdır. 200'e kadar değişken hücrenin kurulumuna izin verilir.

Modelin formülünü etkileyen tüm hücreleri otomatik olarak bulmak için Tahmin Et düğmesini tıklayın.

5. "Kısıtlamalar" alanına, bir çözüm bulmak için herhangi bir kısıtlama girin.

6. "Çalıştır" düğmesini tıklayın.

Orijinal verileri geri yüklemek için, "Orijinal değerleri geri yükle" konumuna geçişe tıklayın.

Aşama C.

Çözümün sonucu hakkında bir özet mesaj görüntülemek için "Arama Sonuçları" iletişim kutusunu kullanın.

Çözüm Arama Sonuçları iletişim kutusu aşağıdaki alanları içerir:

"Orijinal değerleri geri yükle" - modelin etkilenen hücrelerinin orijinal değerlerini geri yüklemeye hizmet eder.

"Raporlar" - kitabın ayrı bir sayfasına yerleştirilecek raporun türünü belirtmeye yarar.

Sonuçlar - bir hedef hücre ve modelin etkileyen hücrelerinin bir listesinden, bunların kaynak ve hedef değerlerinin yanı sıra kısıtlama formülleri ve uygulanan kısıtlamalar hakkında ek bilgilerden oluşan bir rapor oluşturmak için kullanılır.

Esneklik - Çözümün formüldeki (Hedef Hücre Alanı, Çözüm Bul iletişim kutusu) veya kısıtlama formülündeki küçük değişikliklere duyarlılığı hakkında bilgi içeren bir rapor oluşturmak için kullanılır.

Kısıtlamalar - bir hedef hücre ve etkileyen model hücrelerin bir listesinden, bunların değerlerinden ve alt ve üst sınırlarından oluşan bir rapor oluşturmak için kullanılır. Bu rapor, bir tam sayı kümesiyle sınırlı değerlere sahip modeller için oluşturulmaz. Alt sınır, etki eden bir hücrenin içerebileceği en küçük değerdir, diğer yandan kalan etki eden hücrelerin değerleri sabittir ve empoze edilen kısıtlamaları karşılar. Buna göre en yüksek değere üst limit denir.

"Senaryoyu Kaydet" - Gelecekte MS Excel Senaryo Yöneticisini kullanarak sorunu çözmek için senaryoyu kaydedebileceğiniz Senaryoyu Kaydetme diyalog kutusunu görüntülemeye yarar.

Doğrusal optimizasyonun olası görevlerinden ve modellerinden biri, üretim planlama sorunudur.

İşletme aşağıdaki türlerde ürünler üretmelidir: , ayrıca üretilen her bir ürünün miktarı talebi geçmemeli ve aynı zamanda sırasıyla planlanan değerlerden az olmamalıdır. Ürünlerin imalatı için gider mhammadde türleri , rezervleri buna göre sınırlı olan İmalat için olduğu bilinmektedir. ben-ro ürünleri birimlere gider jinci hammaddeler. Ürün satışından elde edilen kar sırasıyla . Ürünlerin üretiminin kar maksimum olacak şekilde planlanması ve aynı zamanda her bir ürünün üretim planının yerine getirilmesi ancak talebin aşılmaması gerekmektedir.

Daha önce çözülmüş bir problem örneğini kullanarak Excel'de doğrusal programlamayı düşünelim.

Bir görev.Nikolai Kuznetsov küçük bir mekanik fabrikayı işletiyor. Önümüzdeki ay, belirli marjinal karının sırasıyla 2.500 ve 3.500 ruble olduğu tahmin edilen iki ürün (A ve B) üretmeyi planlıyor. Her iki ürünün de imalatı, işleme, hammadde ve işçilik maliyetini gerektirir. A ürününün her bir biriminin imalatı için 3 saat makine işleme, 16 birim hammadde ve 6 birim işçilik ayrılmıştır. B birimi için karşılık gelen gereksinimler 10, 4 ve 6'dır. Nikolay, önümüzdeki ay 330 saat işleme, 400 birim hammadde ve 240 birim işçilik sağlayabileceğini öngörüyor. Üretim sürecinin teknolojisi, her belirli ayda en az 12 birim B ürünü üretilecek şekildedir. Tanımlamanız gerekiyor Kar marjını en üst düzeye çıkarmak için Nikolay'ın önümüzdeki ay üretmesi beklenen A ve B ürünlerinin sayısı.

Notu formatta, formatta örnek olarak indirin

1. Oluşturulan matematiksel modeli kullanalım. Bu model:

Büyüt: Z \u003d 2500 * x 1 + 3500 * x 2

Şu şartla ki: 3 * x 1 + 10 * x 2 ≤ 330

16 * x 1 + 4 * x 2 ≤ 400

6 * x 1 + 6 * x 2 ≤ 240

2. Bir ekran formu oluşturalım ve içine ilk verileri girelim (Şekil 1).

Şekil: 1. Doğrusal programlama görevinin veri girişi için ekran formu

C7 hücresindeki formüle dikkat edin. Bu, amaç fonksiyonu formülüdür. Benzer şekilde, C16: C18 hücreleri, kısıtlamaların sol tarafını hesaplamak için formüller içerir.

3. "Bir çözüm ara" eklentisinin kurulu olup olmadığını kontrol edin (Şekil 2), bu öğeyi atlayın.

Şekil: 2. Çözüm arama eklentisi yüklenir; Veri sekmesi, Analiz grubu

Excel şeridinde Çözüm Bul Eklentisini bulamazsanız, Microsoft Office Düğmesine ve ardından Excel Seçenekleri'ne (Şekil 3) tıklayın.

Şekil: 3. Excel seçenekleri

Eklentiler satırını seçin ve ardından "Microsoft Excel Eklentilerini Yönet" penceresinin en altında "Git" i seçin (Şek. 4).

Şekil: 4. Excel eklentileri

"Eklentiler" penceresinde, "Bir çözüm ara" kutusunu işaretleyin ve Tamam'a tıklayın (şek. 5). (Eklentiler alanında çözüm ara listelenmiyorsa, eklentiyi bulmak için Gözat'a tıklayın. Bilgisayarınızda bir çözüm bulmak için Eklentinin yüklü olmadığını belirten bir mesaj görürseniz, Evet'e tıklayın. yükle.)

Şekil: 5. "Çözüm ara" eklentisinin etkinleştirilmesi

Bir çözüm bulmak için eklentiyi yükledikten sonra, Çözüm bul komutu Veri sekmesindeki Analiz grubunda kullanılabilir hale gelir (Şekil 2).

4. Bir sonraki adım, "Çözüm ara" Excel penceresini doldurmaktır (Şek. 6)

Şekil: 6. "Çözüm ara" penceresini doldurma

"Hedef hücreyi ayarla" alanında, hedef işlev değeri $ C $ 7 olan hücreyi seçin. Amaç işlevini maksimize edip etmemeyi seçiyoruz. "Hücrelerin değiştirilmesi" alanında, istenen değişkenlerin değerlerine sahip hücreleri seçin $ C $ 4: $ D $ 4 (sıfır veya boşken). "Ekle" butonunu kullanan "Kısıtlamalar" alanına modelimizin tüm kısıtlamalarını yerleştiriyoruz. "Çalıştır" a basıyoruz. Görünen pencerede "Çözüm arama sonucu" üç rapor türünü de seçin (Şekil 7) ve Tamam'a tıklayın. Bu raporlar, alınan çözümü analiz etmek için gereklidir. Raporlarda sunulan veriler hakkında daha fazla bilgi edinebilirsiniz.

Şekil: 7. Rapor türlerini seçme

Maksimize edilmiş amaç fonksiyonunun değerleri ana sayfada göründü - 130.000 ruble. ve değişken parametreler x 1 \u003d 10 ve x 2 \u003d 30. Bu nedenle, marjinal geliri maksimize etmek için, Nikolai önümüzdeki ay 10 birim A ürünü ve 30 birim B ürünü üretmelidir.

Çözüm Arama Sonucu penceresi yerine başka bir şey görünürse, Excel bir çözüm bulamaz. "Çözüm ara" penceresinin doğru doldurulup doldurulmadığını kontrol edin. Ve bir küçük numara daha. Çözüm aramanızın doğruluğunu azaltmaya çalışın. Bunu yapmak için, "Bir çözüm ara" penceresinde, Parametreler'e tıklayın (Şekil 8.) ve hesaplama hatasını, örneğin 0.001'e kadar artırın. Bazen, yüksek doğruluktan dolayı, Excel'in 100 yinelemede bir çözüm bulmak için zamanı yoktur. Bir çözüme yönelik arama parametreleri hakkında daha fazla bilgi edinebilirsiniz.

Şekil: 8. Hesaplama hatasında artış

Amacı: "Çözüm ara" eklentisini kullanarak Excel'deki doğrusal programlama sorunlarını çözmeyi öğrenin.

Kısa teorik bilgi

Optimizasyon görevleri, çeşitli pratik faaliyet alanlarında yaygın olarak kullanılmaktadır: ulaşım sistemlerinin işleyişini düzenlerken, endüstriyel işletmeleri yönetirken, karmaşık sistemler tasarlarken. Sistem analizinin birçok ortak problem sınıfı, özellikle optimal planlama problemleri, çeşitli kaynakların tahsisi, envanter yönetimi, zamanlama, girdi-çıktı dengesi doğrusal programlama modelleri çerçevesine uyar.

Doğrusal programlama probleminin (LPP) ifadesi.

Birçok değişken vardır X \u003d (x 1, x 2, ..., x n). Amaç işlevi, kontrol edilen parametrelere doğrusal olarak bağlıdır:

Doğrusal şekiller olan kısıtlamalar var

Nerede (2)

Doğrusal fonksiyonun maksimumunu (minimum) belirlemek gerekir

(x 1, x 2, ..., x n) noktasının, doğrusal eşitsizlikler sistemi tarafından belirlenen bazı D kümelerine ait olması koşuluyla

(4)

Doğrusal programlama probleminin eşitsizlikler sistemini (4) karşılayan herhangi bir değer seti (x 1 *, x 2 *, ..., x n *), bu problem için kabul edilebilir bir çözümdür. Ek olarak, eşitsizlik

c 1 x 1 o + c 2 x 2 o + .. + c n x n o ≥ c 1 x 1 + c 2 x 2 + .. + c n x n

x 1, x 2, ..., x n değerlerinin tümü için, x 1 o .. x n o değeri doğrusal programlama problemine en uygun çözümdür.

Matematiksel bir model oluşturma ve LPP'yi çözme örneği.

Bir görev. Üretimi için üç tür kaynağın gerekli olduğu dört tip A, B, C ve D ürünlerinin üretilmesi için hangi miktarda gerekli olduğunun belirlenmesi gerekir: işçilik, hammadde ve finans. Bu türden bir ürün birimi üretmek için gereken her tür kaynağın miktarına tüketim oranı denir. Her bir ürün türünün bir biriminin satışından elde edilen kârın yanı sıra tüketim oranları da Tablo 1'de gösterilmektedir. Mevcut kaynağın mevcudiyeti de burada verilmiştir.

Tablo 1.

Kaynak	Bir	B	C	D	işaret	kullanılabilirlik
emek

Aşağıdaki tanımları sunduğumuz matematiksel bir model oluşturalım:

x ben -i-th tipi ürün sayısı, i \u003d 1,2,3,4

b j -j-th türünün mevcut kaynak miktarı, j \u003d 1,2,3

bir ji - i-inci ürünün serbest bırakılması için j-inci kaynağın tüketim oranı

c ben - i-inci tip üretim biriminin satışından elde edilen kar.

Tablo 1'den görülebileceği gibi, bir üretim biriminin serbest bırakılması için Bir 6 birim hammadde gereklidir, yani tüm ürünlerin serbest bırakılması için Bir gerekli 6 x 1hammadde birimleri, nerede x 1 - ürün sayısı Bir... Diğer ürün türleri için bağımlılıkların benzer olduğu göz önüne alındığında, hammaddeler üzerindeki kısıtlama şöyle görünecektir:

6x 1+ 5x 2+ 4x 3+ 3x 4≤ 110

Bu kısıtlamada, sol taraf gerekli kaynağa eşittir ve sağ taraf mevcut kaynak miktarını gösterir.

Benzer şekilde, diğer kaynak türleri için kısıtlamalar oluşturabilir ve amaç işlevi için bir bağımlılık yazabilirsiniz. O zaman problemin matematiksel modeli şöyle görünecektir:

x 1+ x 2+ x 3+ x 4≤ 16

6x 1+ 5x 2+ 4x 3+ 3x 4≤ 110

4x 1+ 6x 2+ 10x 3+ 13x 4≤ 100

x i≥ 0, ben \u003d 1,2,3,4

1. Problemin koşullarını girmek için Excel'de bir form oluşturun (Şekil 1). B3 Hücreleri: E3 hesaplanan değerleri gösterecektir x i.

şekil 1. Sorunlu koşulları girme formu

2. Amaç işlevinin katsayılarını ve kısıtlamaları forma girelim. Matematiksel modelden bağımlılıkları sunalım. Girilen veriler Şekil 2'de gösterilmektedir.

incir. 2. Sorunun ilk verileri

F6 hücresinde, amaç fonksiyonunun formülü, F9-F11'de matematiksel modelden kısıtların sol kısımlarına yazılır. İncirde. Şekil 3, formüllerin sunum modunu gösterir. Aşağıdaki eylem sırasını kullanarak bu moda geçebilirsiniz: düğmesine basın Microsoft Office, Tıklayın Excel seçenekleri,sekmeyi aç bunlara ek olarakve kutuyu işaretleyin Formülleri gösterin, değerlerini değil.

Şekil 3. Formül sunum modu.

3. Bir çözüm eklentisi için aramayı yükleyin Veri│Analiz│Bir çözüm bulmak.

4. sahada Hedef hücreyi ayarlayın İmleci alana yerleştirdiğimiz ve F6 hücresine sol tıkladığımız hedef hücreye bir referans girin.

5. Kutuyu işaretleyerek arama yönünü seçin eşit maksimum değer.

6. İmleci alana yerleştirin Hücreleri değiştirmeve fare ile değiştirilmiş B3: E3 hücrelerinin adlarını girin. Bu hücrelerde, bir çözüm arayışının bir sonucu olarak, çözüm görüntülenecektir - değişkenlerin değerleri x i.,amaç fonksiyonunun verilen kısıtlamalar altında maksimum bir değere sahip olduğu.

7. Aranan değişkenlere kısıtlamalar getirelim: x ben ≥0 (varsayılan alt sınır 0'dır, üretilen ürün sayısı negatif olamaz). Ayrıca kaynaklara da kısıtlamalar getireceğiz. (rezervlerinden daha fazla kaynak kullanılamaz). Düğmesine tıklayın a ekle, görünen pencerede Bir kısıtlama eklemeksol alanda, fareyi kullanarak, açılır listeden B3 hücresine bir bağlantı girin, işareti seçin ≥, sağ alanda, B4 hücresine tıklayın (Şekil 4). Aynı şekilde başka kısıtlamalar da getiriyoruz.

Şekil 4. Kısıtlama ekleme penceresi.

Şekil 5, tamamlanan Çözüm arama penceresini göstermektedir.

Şekil 5 Tamamlandı Çözüm arama penceresi

8. Ardından, düğmesine tıklayın Yürüt.Solution Search Results iletişim kutusu görüntülenir (Şekil 6). Çözüm bulundu. Tüm kısıtlamalar ve optimallik koşulları karşılanır. Bulunan çözümü kaydediyoruz. Bu pencerede ayrıca üç tür rapor alabilirsiniz: sonuçlara, kararlılığa ve sınırlara göre, raporlar yeni çalışma sayfalarında oluşturulur.

şek.6. Çözüm arama sonuçları penceresi

Soruna en uygun çözümün sonuçları tabloda gösterilmektedir (Şekil 7).

şek.7. Optimal çözüm sonuçları

Böylece en uygun çözümü elde ettik (10; 0; 6; 0), yani. 10 birim A ürünü ve 6 birim C ürünü üretilmesi tavsiye edilir. Maksimum kâr 1320 para birimidir, tüm işçilik ve mali kaynaklar 84 birim hammadde kullanılır, 26 birim hammadde stokta kalır.

Laboratuvar çalışması için görevler.

Bir matematiksel model oluşturun ve ortaya çıkan doğrusal programlama problemini Excel'de Çözüm ara eklentisini kullanarak çözün.

Malların taşınması için A ve B tipi makineler kullanılmaktadır.Her iki tipteki makinelerin taşıma kapasitesi aynıdır ve h ton'a eşittir.Bir sefer için makine A tüketir bir 11kg yağlayıcı ve bir 12l yakıt, B arabası - bir 21kg yağlayıcı ve bir 22l yakıt. Bazda d 1kg yağlayıcı ve d 2l yakıt. Bir A vagonunun nakliyesinden elde edilen kar, 1'denovmak., arabalar B - 2'denovmak. Yükün taşınması gereklidir (ilk veriler aşağıdaki tabloda verilmiştir).

Kargo taşımacılığından elde edilen geliri en üst düzeye çıkarmak için her iki türden kaç makine kullanılmalıdır.

Seçenek No.

Laboratuvar çalışması yapmak için talimatlar.

1. Teorik materyali inceleyin.
2. Verilen örneği izleyin.
3. Seçeneğinizi son basamağa göre seçin.
4. Problemin matematiksel bir modelini oluşturun.
5. Çözüm Bul'u kullanarak en uygun çözümü bulun.
6. Alınan kararlarla ilgili sonuçlar çıkarın, kararın sonuçları, sürdürülebilirlik ve limitler hakkında raporlar oluşturun.
7. Bir laboratuvar raporu oluşturun.

1. Baş sayfa.
2. Sorunun sözlü ifadesi.
3. Problemin matematiksel formülasyonu.
4. Bir çözüm arama penceresi tamamlandı
5. Çözüm arama sonuçları (tablo).
6. Elde edilen çözümlere ilişkin sonuçlar.

Kaynakların listesi

1. Gelman V.Ya. Excel ile matematiksel problemlerin çözümü: Workshop. - SPb.: Peter, 2003
2. Kuritsky B.Ya. Excel kullanarak en uygun çözümleri bulmak. - SPb .: BHV-Saint Petersburg, 1997
3. Pazyuk K.T. Ekonomide matematiksel yöntemler ve modeller. - Habarovsk: KSTU Yayınevi, 2002
4. John Walkenbach. MS Office Excel 2007 - Kullanıcı Kutsal Kitabı, Yayıncı: Williams, 2008

Giriş

4.1. İlk veri

4.2. Hesaplama formülleri

4.3. Çözüm Bul İletişim Kutusunu Tamamlama

4.4. Çözüm sonuçları

Sonuç

Referanslar

Giriş

doğrusal programlama excel optimizasyon problemi

Elektrik enerjisi endüstrisindeki ve ulusal ekonominin diğer sektörlerindeki çok çeşitli sorunların çözümü, belirli bir "nesnel fonksiyon" (CF) yardımıyla matematiksel olarak tanımlanan karmaşık bir dizi bağımlılığın optimizasyonuna dayanmaktadır. Enerji santralleri için yakıt maliyetini, santralden tüketiciye nakliyesi sırasında meydana gelen elektrik kayıplarını ve diğer birçok sorunlu görevi belirlemek için benzer işlevler yazılabilir. Bu gibi durumlarda, CF'yi değişkenlerine uygulanan belirli kısıtlamalar altında bulmak gerekir. CF doğrusal olarak bileşiminde yer alan değişkenlere bağlıysa ve tüm kısıtlamalar doğrusal bir denklemler ve eşitsizlikler sistemi oluşturuyorsa, optimizasyon probleminin bu özel formuna "doğrusal programlama problemi" denir.

Ders çalışmasının konusu, Microsoft Excel hesap tablolarını kullanma ve doğrusal programlamada optimizasyon problemlerini çözme konusunda pratik beceriler kazanmak için genel enerji alanından alınan "taşıma problemi" örneğini kullanarak "MS Excel'de doğrusal programlama problemlerini çözme" dir. .

1. Sorunu çözmek için ilk veriler

İlk veriler şunları içerir - kömür havzalarının (UB) ve enerji santrallerinin (ES) aralarındaki ulaşım bağlantılarının göstergesi ile birlikte yerleşimi, yıllık üretkenlik ve UB yakıtının özel fiyatı, kurulu kapasite, kullanım saatlerinin sayısı hakkında bilgi içeren tablolar santraldeki kurulu kapasite ve özgül tüketim yakıt, UB ile santral arasındaki mesafeler ve UB-ES rotaları boyunca akaryakıt nakliyesinin birim maliyeti.

Şekil 1. İlk veri

2. MS Excel elektronik tabloları hakkında kısa bilgi

Şekil: 2. Uygulama penceresinin görünümü

Tablo süreçleri, elektronik tablolar oluşturmak ve verilerini değiştirmek için kullanılan yazılım paketleridir. Elektronik tabloların kullanılması verilerle çalışmayı basitleştirir, özel programlama kullanmadan hesaplamayı otomatikleştirmenize olanak tanır. En yaygın kullanım ekonomik ve muhasebe hesaplamalarındadır. MS Excel, kullanıcıya şunları yapma yeteneği sağlar:

.Yerleşik işlevler içeren karmaşık formüller kullanın.

2.Hücre ve tablo ilişkilerini düzenleyin, böylece kaynak tablolardaki verileri değiştirmek, sonuç tablolarındaki sonuçları otomatik olarak değiştirir.

.Pivot tablolar oluşturun.

.Tablolardaki verileri sıralayın ve filtreleyin.

.Verileri birleştirin (birkaç tablodaki verileri tek bir tabloda birleştirin).

.Komut dosyalarını kullanın - aynı tabloda nihai toplamların oluşturulduğu kaynak veri dizileri olarak adlandırılır.

.Formüllerdeki hatalar için otomatik bir arama gerçekleştirin.

.Verileri koruyun.

.Veri yapılandırmayı kullanın (tabloların bölümlerini gizleyin ve gösterin).

.Otomatik tamamlamayı uygulayın.

.Makroları uygulayın.

.Diyagramlar oluşturun.

.Otomatik düzeltme ve yazım denetleyiciyi kullanın.

.Stiller, şablonlar, otomatik formatlama kullanın.

.Diğer uygulamalarla veri alışverişi.

Anahtar kavramlar:

.Çalışma kitabı - bir dosyada saklanan temel belgeler.

2.Sayfa (hacim: 256 sütun, 65536 satır).

.Hücre, veri yerleştirmenin en küçük yapısal birimidir.

.Hücre adresi - hücrenin tablodaki konumunu tanımlar.

.Formül, hesaplamalar için matematiksel bir gösterimdir.

.Bağlantı - formülün bir parçası olarak hücre adresinin girilmesi.

.İşlev, belirli hesaplama işlemlerinin performansını gösteren matematiksel bir gösterimdir. Bir isim ve argümanlardan oluşur.

Veri girişi:

Veriler aşağıdaki türlerde olabilir -

· Sayılar.

· Metin.

· Fonksiyonlar.

· Formüller.

Girebilirsin -

· Hücrelere.

· Formül çubuğuna.

Girdikten sonra ekranda bir hücrede ######## görünüyorsa, sayı uzundur ve hücreye sığmıyorsa, hücrenin genişliğini artırmanız gerekir.

Formüller - hücrelerdeki değerlerin birbiriyle nasıl ilişkili olduğunu belirleyin. Şunlar. Hücredeki veriler doldurularak elde edilmez, otomatik olarak hesaplanır. Bir formülde başvurulan hücrelerin içeriğini değiştirdiğinizde, hesaplanan hücredeki sonuç da değişir. Tüm formüller \u003d ile başlar. Ayrıca takip edebilir -

· Hücre başvurusu (örneğin, A6).

· İşlev.

· Aritmetik işleç (+, -, /, *).

· Karşılaştırma operatörleri (\u003e,<, <=, =>, =).

Formülleri doğrudan bir hücreye girebilirsiniz, ancak formül çubuğunu kullanarak girmek daha uygundur.

Fonksiyonlar belirli görevleri gerçekleştirmek için standart formüllerdir. İşlevler yalnızca formüllerde kullanılır.

Yöntem: Ekle - İşlev veya formül çubuğunda = ... Son kullanılan on işlevin listesini içeren bir iletişim kutusu görüntülenir. Listeyi genişletmek için seçin Diğer fonksiyonlar ..., işlevlerin türlere (kategorilere) göre gruplandırıldığı, işlevin amacının bir açıklamasının ve parametrelerinin verildiği başka bir iletişim kutusu açılacaktır.

MS Excel elektronik tablolarıyla çalışmanın tam bir açıklaması ders kitaplarında ve kılavuzlarda (özel) bulunabilir.

3. Problemin matematiksel formülasyonu

Belirtilen güç kaynağı bölgesindeki elektrik santrali için minimum yakıt maliyetleri kriterine göre, santralin ihtiyaçları üzerindeki kısıtlamaları ve verimliliği dikkate alarak, üç kömür havzasından optimum yakıt tedariklerini belirlemek gerekir. UB.

Problemin ilk verileri ve çözümü sırasında belirlenecek değişkenler Tablo 3 şeklinde sunulabilir.

Veri tanımı:

İÇİNDE ub1 , İÇİNDE ub2 , İÇİNDE ub3 - kömür havzalarının üretkenliği, bin ton;

FROM ub1 , FROM ub2 , FROM ub3 - kömür havzalarında yakıt maliyeti, USD / ton;

L -de - UB ile ES arasındaki demiryolu hattının uzunluğu, km;

FROM -de UB'den ES'ye güzergah boyunca akaryakıt taşımacılığının birim maliyeti, c.u. / ton * km (C 11\u003d C 12\u003d C 13\u003d C 21\u003d C 22\u003d C 23\u003d C 31\u003d C 32\u003d C 33);

İÇİNDE -de - UB'den elektrik santraline teslim edilen yakıt hacmi, bin ton;

İÇİNDE ES1 , İÇİNDE ES2 , İÇİNDE ES3 - sırasıyla birinci, ikinci, üçüncü santrallerin yıllık yakıt talebi, bin ton;

İÇİNDE -de - problem çözme sürecinde belirlenecek amaç fonksiyon değişkenlerinin parametreleridir;

Optimum yakıt miktarını (B -de ), UB'den ES'nin her birine teslim edildiğinde, üç ES için toplam yakıt maliyetleri minimum olacaktır.

Problemi çözme sürecinde optimize edilecek hedef fonksiyon, her üç santral için toplam yakıt maliyetleri olacaktır.

4. Doğrusal programlama problemini çözme

.1 İlk veriler

Şekil: 4. İlk veriler

4.2 Hesaplamalar için formüller

Şekil 5. Ara hesaplamalar

4.3 Çözüm Bul İletişim Kutusunu Tamamlama

Şekil: 6. Optimizasyon süreci.

Şekil 6.1 Sınırları ayarlama (yakıt\u003e 0 olmalıdır).

Şekil 6.2. Limitleri ayarlama (getirilen miktar \u003d tüketilen yakıt miktarı).

Şekil 6.3. Sınırları ayarlama (yıllık sevkiyat, UB1 üretimini aşmayın).

Şekil 6.4. Sınırları ayarlama (yıllık sevkiyat, UB2 üretimini aşmayın).

Şekil 6.5. Sınırları ayarlama (yıllık sevkiyat, UB3 üretimini aşmayın).

.4 Çözüm Sonuçları

Şekil 8. Problemi çözmenin sonuçları

Cevap: Teslim edilen yakıt miktarı (bin ton):

UB1'den ES4 118,17 ton;

UB1'den ES6 545.66 ton;

UB2'den ES5 19.66 ton;

UB2'den ES6 180,34 ton;

UB3'ten ES5 277,94 ton;

UB3'ten ES6 526.00tn'dir;

ES4 toplam 118.17 ton;

ES5 toplam 297.60 ton;

ES6 toplam 1252,00 tn;

Yakıt maliyetleri (c.u.):

ES4 - 496 314.00 için.

ES5 - 227064.75 için.

ES6 - 23099064.78 için.

Tüm ES için toplam maliyet - 23822443.53 c.u.;

Sonuç

MS Excel elektronik tabloları hakkında kısa bilgi. Doğrusal bir programlama problemini çözme. Bir "taşıma sorunu" örneğini kullanarak, Microsoft Excel araçlarını kullanarak bir ekonomik optimizasyon sorununun çözümü. MS Word belgesinin tasarımının özellikleri.

Kurs çalışması, Microsoft Excel kullanarak genel enerji alanından alınan bir "taşıma problemi" örneğini kullanarak, ekonomik bir optimizasyon probleminin çözümünün ele alındığı bir MS Word dokümanının tasarımında nasıl oluşturulacağını ve çalışılacağını gösterir. .