Örnekler ise bir boole işlevinin kullanılması. Microsoft Excel Özelliği: Bir Çözüm Bulma

Doğrusal olmayan denklemleri ve sistemleri çözme "

Amaç: Ms Excel 2007'nin doğrusal olmayan denklemleri ve sistemleri çözmedeki yeteneklerini keşfetmek. Paketi kullanarak doğrusal olmayan denklemleri ve sistemleri çözme becerilerinin kazanılması.

1. Egzersiz. Polinom x'in köklerini bulun 3 - 0.01x 2 - 0.7044x + 0.139104 \u003d 0.

İlk önce denklemi grafik olarak çözelim. F (x) \u003d 0 denkleminin grafik çözümünün, f (x) fonksiyonunun grafiğinin apsis ile kesişme noktası olduğu bilinmektedir, yani. fonksiyonun kaybolduğu böyle bir x değeri.

Polinomumuzu -1 ile 1 arasındaki aralıkta 0.2'lik bir adımla tablo haline getirelim. Hesaplama sonuçları Şek., Formül B2 hücresine girildiğinde: \u003d A2 ^ 3 - 0.01 * A2 ^ 2 - 0.7044 * A2 + 0.139104. Grafik, fonksiyonun Öküz eksenini üç kez geçtiğini ve üçüncü derece polinomun üçten fazla gerçek köke sahip olmadığını gösterdiğinden, soruna grafiksel bir çözüm bulundu. Başka bir deyişle, kökler yerelleştirildi, yani. bu polinomun köklerinin bulunduğu aralıklar belirlenir: [-1, -0.8] ve.

Şimdi polinomun köklerini, komutunu kullanarak ardışık yaklaşımlarla bulabilirsiniz. Veri → Verilerle çalışma → What-If analizi → Parametre seçimi.

Fonksiyonun ilk yaklaşımlarını ve değerlerini girdikten sonra, komuta başvurabilirsiniz. Veri → Verilerle çalışma → What-If analizi → Parametre seçimi ve iletişim kutusunu aşağıdaki gibi doldurun.

Tarlada Hücrede ayarladenklemin sol tarafının değerini hesaplayan formülün girildiği hücreye bir referans verilir (denklem, sağ tarafı bir değişken içermeyecek şekilde yazılmalıdır). Tarlada Değer denklemin sağ tarafına giriyoruz ve alana Hücre değerlerini değiştirme değişken için ayrılan hücreye bir referans verilmiştir. İletişim kutusunun alanlarına hücre referansları girmenin Parametrelerin seçimi klavyeden değil, ilgili hücreye tıklayarak daha kullanışlıdır.

Tamam düğmesine bastıktan sonra, bir çözüm aramanın başarıyla tamamlandığına dair bir mesajla birlikte parametre seçiminin sonucu iletişim kutusu görüntülenir, kökün yaklaşık değeri A14 hücresine yerleştirilecektir.

Kalan iki kökü aynı şekilde bulun. Hesaplama sonuçları A15 ve A16 hücrelerine yerleştirilecektir.

Görev 2. Denklemi çözün e x - (2x - 1) 2 = 0.

Doğrusal olmayan denklemin köklerini yerelleştirelim.

Bunu yapmak için, onu f (x) \u003d g (x) biçiminde temsil ediyoruz, yani. e x \u003d (2x - 1) 2 veya f (x) \u003d e x, g (x) \u003d (2x - 1) 2 ve grafiksel olarak çözün.

F (x) \u003d g (x) denkleminin grafik çözümü, f (x) ve g (x) çizgilerinin kesişme noktası olacaktır.

F (x) ve g (x) grafiklerini oluşturalım. Bunu yapmak için, bağımsız değişkenin değerlerini A3: A18 aralığına girin. B3 hücresine, f (x): \u003d EXP (A3) işlevinin değerlerini hesaplamak için formülü ve g (x): \u003d (2 * A3-1) ^ 2'yi hesaplamak için C3'e girin.

Hesaplama sonuçları ve f (x) ve g (x) grafiğini çizme:

Grafik, f (x) ve g (x) çizgilerinin iki kez kesiştiğini göstermektedir, yani. bu denklemin iki çözümü var. Bunlardan biri önemsizdir ve tam olarak hesaplanabilir:

İkincisi için kök izolasyon aralığını belirleyebilir: 1.5< x < 2.

Şimdi, ardışık yaklaşımlar yöntemiyle bir segmentteki denklemin kökünü bulabilirsiniz.

İlk yaklaşımı H17 \u003d 1.5 hücresine ve denklemin kendisini, ilk yaklaşıma göre I17 \u003d EXP (H17) - (2 * H17-1) ^ 2 hücresine girelim.

ve iletişim kutusunu doldurun Parametre seçimi.

Bir çözüm aramasının sonucu H17 hücresinde görüntülenecektir.

Görev3 . Denklem sistemini çözün:

Denklem sistemlerini çözmek için yukarıda açıklanan yöntemleri kullanmadan önce, bu sistem için grafiksel bir çözüm bulacağız. Sistemin her iki denkleminin de örtük olarak verildiğini ve bu denklemlere karşılık gelen grafikleri oluşturmak için verilen denklemleri y değişkenine göre çözmek gerektiğini unutmayın.

Sistemin ilk denklemi için elimizde:

Ortaya çıkan işlevin ODV'sini bulalım:

Bu sistemin ikinci denklemi bir daireyi tanımlar.

Sistemin denklemleriyle tanımlanan satırları oluşturmak için hücrelere girilmesi gereken formülleri içeren MS Excel çalışma sayfasının parçası. Gösterilen çizgilerin kesişme noktaları, doğrusal olmayan denklemler sistemi için grafiksel bir çözümdür.

Verilen sistemin iki çözümü olduğunu görmek zor değil. Bu nedenle, Ox ve Oy eksenleri boyunca kök izolasyon aralığını önceden belirledikten sonra, sisteme çözüm bulma prosedürü iki kez gerçekleştirilmelidir. Bizim durumumuzda, ilk kök (-0.5; 0) x ve (0.5; 1) y aralıklarında bulunur ve ikincisi (0; 0.5) x ve (-0.5; -1) y'dir. Aşağıdaki gibi ilerleyeceğiz. X ve y değişkenlerinin başlangıç \u200b\u200bdeğerlerini, sistemin denklemlerini ve hedef fonksiyonunu temsil eden formülleri tanıtalım.

Şimdi, beliren diyalog kutularını doldurarak, Veri → Analiz → Çözüm ara komutunu iki kez kullanacağız.

Sistemin elde edilen çözümünü grafik olanla karşılaştırarak sistemin doğru çözüldüğünden emin oluyoruz.

Kendi kendine yardım atamaları

1. Egzersiz... Bir polinomun köklerini bulun

Ödev 2... Doğrusal olmayan denklemin çözümünü bulun.

Ödev 3... Doğrusal olmayan denklemler sisteminin çözümünü bulun.

Zaten bildiğiniz gibi, formüller Microsoft Excel bir işlevin değerini bağımsız değişkenleriyle belirlemenize izin verir. Bununla birlikte, fonksiyonun değeri bilindiğinde ve argümanın bulunması gerektiğinde (yani denklemi çözmek için) bir durum ortaya çıkabilir. Bu tür sorunları çözmek için özel bir işlev var Hedef Arama .

Parametre araması.

Özel fonksiyon Hedef Arama değeri biliniyorsa, bir işlevin parametresini (bağımsız değişken) tanımlamanıza izin verir. Bir parametre seçildiğinde, etkileyen hücrenin (parametrenin) değeri, bu hücreye bağlı olan formül belirtilen değeri döndürene kadar değiştirilir.

Basit bir örnek kullanarak bir parametre bulma prosedürünü düşünelim: denklemi çözün 10 * x - 10 / x \u003d 15 ... Burada parametre (bağımsız değişken) - x ... Bir hücre olsun A3 ... Bu hücreye fonksiyon tanımının kapsamına giren herhangi bir sayıyı girelim (örneğimizde bu sayı sıfıra eşit olamaz). Bu değer başlangıç \u200b\u200bdeğeri olarak kullanılacaktır. Bırak olsun 3 ... Formülü tanıtıyoruz \u003d 10 * A3-10 / A3 gerekli değerin elde edilmesi gereken bir hücreye, örneğin, B3 ... Şimdi komutu seçerek parametre arama işlevini başlatabilirsiniz Hedef Arama menüde Araçlar ... Arama parametrelerini girin:

Tarlada Hücreyi ayarla istediğiniz formülü içeren hücreye bir başvuru girin.
Arama sonucunu alana girin Değer vermek .
Tarlada Hücreyi değiştirerek Eşleştirilecek değeri içeren hücreye bir başvuru girin.
Anahtara tıklayın tamam .

İşlevin sonunda, ekranda arama sonuçlarının görüntüleneceği bir pencere görünecektir. Bulunan parametre, kendisi için ayrılmış hücrede görünecektir. Örneğimizde denklemin iki çözümü olduğu ve parametrenin yalnızca biriyle eşleştiği gerçeğine dikkat edin - bunun nedeni parametrenin yalnızca gerekli değer döndürülene kadar değişmesidir. Bu şekilde bulunan ilk argüman bize arama sonucu olarak döndürülür. Örneğimizde başlangıç \u200b\u200bdeğeri olarak belirtirsek -3 , sonra denklemin ikinci çözümü bulunacaktır: -0,5 .

En uygun başlangıç \u200b\u200bdeğerini doğru bir şekilde belirlemek oldukça zordur. Daha sık olarak, istenen parametre hakkında bazı varsayımlar yapabiliriz, örneğin, parametrenin tamsayı olması gerekir (daha sonra denklemimize ilk çözümü elde ederiz) veya pozitif olmayan (ikinci çözüm).

Sınır koşulları empoze edilen bir parametre bulma görevine özel bir Microsoft Excel eklentisi yardımcı olacaktır. Çözücü .

Bir çözüm arayın.

Microsoft Excel eklentisi Çözücü tipik bir kurulumda otomatik olarak kurulmaz:

Menüde Araçlar takım seç Eklentiler ... İletişim kutusu Eklentiler komut içermiyor Çözücü , düğmesine basın Araştır ve eklenti dosyasını içeren sürücüyü ve klasörü belirtin Solver.xla (genellikle bu klasördür Kitaplık \\ Çözücü ) veya programı çalıştırın microsoft kurulumları Office, dosyayı bulamazsa.
İletişim kutusunda Eklentiler kutuyu kontrol et Çözücü .

Çö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ücrede bir formülle ilişkili bir grup hücre ile çalışır. Prosedür, hedef hücredeki formüle dayalı olarak en iyi sonucu elde edene kadar etkilenen hücrelerdeki değerleri değiştirir. Değerler kümesini daraltmak için, diğer etkileyen hücrelere bağlantıları olabilecek kısıtlamalar uygulanır. Akademik performansı en üst düzeye çıkaran eğitim seanslarının sayısı gibi hedef hücrenin uç noktasıyla eşleşen bir etkileme hücresinin değerini belirlemek için çözüm aramasını da kullanabilirsiniz.

İletişim kutusunda Çözücü iletişim kutusundaki ile aynı Hedef Arama , hedef hücreyi, değerini ve hedefe ulaşmak için değiştirilmesi gereken hücreleri belirtmeniz gerekir. Optimizasyon problemlerini çözmek için, hedef hücre maksimum veya minimum değere eşit olarak belirtilmelidir.

Düğmeye tıklarsanız Tahmin Excel, formülün kendisini etkileyen tüm hücreleri bulmaya çalışır.

Tuşa tıklayarak sınır koşulları ekleyebilirsiniz. Ekle .

Düğmeye tıklayarak Seçenekler , bir çözüm bulma koşullarını değiştirebilirsiniz: bir çözüm bulmak için maksimum süre, yineleme sayısı, çözümün doğruluğu, optimum çözümden sapmalar için tolerans, ekstrapolasyon yöntemi (doğrusal veya karesel), optimizasyon algoritma vb.

Bir önceki örneğe dönelim: İkinci (pozitif olmayan) çözümü elde etmek için sınır koşulunu eklemek yeterlidir A3 ... Parametre seçiminde olduğu gibi, ekranda gerekli çözüm için arama sonuçlarına ilişkin bir raporun görüntüleneceği bir pencere görünecektir. Çözümün kendisi, kendisine yönelik hücrelerde (hücrede) gösterilecektir. A3 değer görüntülenir -0.50 ).

Microsoft Excel eklentisi Çözücü ayrıca denklem sistemlerini veya eşitsizlikleri çözmenize olanak tanır. Basit bir örnek ele alalım: Denklem sistemini çözmeye çalışalım
x + y \u003d 2
x - y \u003d 0

Microsoft Excel'deki en ilginç özelliklerden biri Çözüm Bulmaktır. Aynı zamanda, bu aracın kullanıcılar arasında en popüler olarak sınıflandırılamayacağına dikkat edilmelidir. bu ek... Ama boşuna. Sonuçta, bu işlev, başlangıç \u200b\u200bverilerini kullanarak, arayarak, mevcut olanların en uygun çözümünü bulur. Microsoft Excel'de Çözüm Bul özelliğini nasıl kullanacağımızı öğrenelim.

Çözüm ara'nın bulunduğu bantta uzun süre arama yapabilirsiniz, ancak yine de bu aracı bulamazsınız. Basitçe, bu işlevi etkinleştirmek için, program ayarlarında etkinleştirmeniz gerekir.

Microsoft Excel 2010 ve sonraki sürümlerde Çözümleri ara özelliğini etkinleştirmek için "Dosya" sekmesine gidin. 2007 sürümü için, pencerenin sol üst köşesindeki Microsoft Office düğmesine tıklayın. Açılan pencerede "Parametreler" bölümüne gidin.

Parametreler penceresinde "Eklentiler" öğesine tıklayın. Geçişten sonra, pencerenin alt kısmında, "Kontrol" parametresinin karşısında, "Excel Eklentileri" değerini seçin ve "Git" düğmesine tıklayın.

Eklentileri olan bir pencere açılır. İhtiyacımız olan eklentinin adının önüne bir onay işareti koyarız - "Çözüm arayın". "Tamam" düğmesine tıklayın.

Bundan sonra, Veri sekmesindeki Excel şeridinde Çözümleri ara işlevini başlatmak için bir düğme görünecektir.

Masayı hazırlamak

Şimdi işlevi etkinleştirdiğimize göre, nasıl çalıştığını görelim. Bunu hayal etmenin en kolay yolu, belirli bir örnek vermektir. Yani, işletme çalışanlarının bir ücret tablosuna sahibiz. Her çalışan için ayrı bir sütunda belirtilen ücretlerin belirli bir katsayı ile ürünü olan primi hesaplamamız gerekiyor. Aynı zamanda, prime ayrılan toplam fon miktarı 30.000 ruble. Bu miktarın bulunduğu hücre, hedefin adını taşır, çünkü amacımız bu sayı için verileri tam olarak seçmektir.

Prim miktarını hesaplamak için kullanılan katsayı, Çözüm ara işlevini kullanarak hesaplamamız gerekir. İçinde bulunduğu hücreye istenen hücre denir.

Hedef hücre ve hedef hücre, bir formül kullanılarak birbiriyle ilişkilendirilmelidir. Bizim özel durumumuzda, formül hedef hücrede bulunur ve şu biçime sahiptir: "\u003d C10 * $ G $ 3", burada $ G $ 3, aranan hücrenin mutlak adresidir ve "C10" ikramiyenin hesaplandığı toplam maaş, işletmenin çalışanları.

Çözüm Bul Aracını Başlatma

Tablo hazırlandıktan sonra, "Veri" sekmesindeki "Analiz" araç kutusundaki şeritte bulunan "Çözüm ara" düğmesine tıklayın.

Verileri girmeniz gereken parametreler penceresi açılır. "Hedef işlevini optimize edin" alanına, tüm çalışanlar için toplam prim tutarının yerleştirileceği hedef hücrenin adresini girmeniz gerekir. Bu, koordinatları manuel olarak yazarak veya veri giriş alanının solunda bulunan düğmeye tıklayarak yapılabilir.

Bundan sonra, parametreler penceresi küçültülecek ve tablonun istediğiniz hücresini seçebilirsiniz. Ardından, parametreler penceresini tekrar genişletmek için girilen verilerle formun solundaki aynı düğmeye tekrar tıklamanız gerekir.

Hedef hücrenin adresinin bulunduğu pencerenin altında, içinde olacak değerlerin parametrelerini ayarlamanız gerekir. Maksimum, minimum veya belirli bir değer olabilir. Bizim durumumuzda bu son seçenek olacaktır. Bu nedenle anahtarı “Değerler” konumuna getirdik ve solundaki alana 30.000 sayısını yazıyoruz hatırladığımız gibi bu sayı şartlara göre tüm çalışanlar için toplam prim miktarıdır işletmenin.

Aşağıda "Değişken hücreleri değiştirme" alanı bulunmaktadır. Burada, bonus miktarının hesaplanacağı temel maaşı çarparak, hatırladığımız gibi, katsayının bulunduğu istenen hücrenin adresini belirtmeniz gerekir. Adres, hedef hücre için yaptığımız gibi yazılabilir.

"Kısıtlamalara göre" alanında, veriler için belirli sınırlamalar koyabilirsiniz, örneğin, değerleri tam veya negatif yapmayın. Bunu yapmak için, "Ekle" düğmesine tıklayın.

Bundan sonra, kısıtlama ekleme penceresi açılır. "Hücrelere referans" alanına, kısıtlamanın uygulandığı hücrelerin adresini yazın. Bizim durumumuzda bu, katsayılı istenen hücredir. Ardından gerekli işareti koyarız: "küçük veya eşit", "büyük veya eşit", "eşit", "tam sayı", "ikili", vb. Bizim durumumuzda, katsayıyı pozitif bir sayı yapmak için büyüktür veya eşittir işareti seçeceğiz. Buna göre "Kısıtlama" alanında 0 sayısını belirtin. Başka bir kısıtlama yapılandırmak istiyorsak "Ekle" butonuna tıklayın. Aksi takdirde, girilen kısıtlamaları kaydetmek için "Tamam" düğmesine tıklayın.

Gördüğünüz gibi, bundan sonra, çözüm arama parametreleri penceresinin ilgili alanında kısıtlama belirir. Ayrıca, hemen aşağıdaki ilgili parametrenin yanındaki kutuyu işaretleyerek değişkenleri negatif olmayan yapabilirsiniz. Burada ayarlanan parametrenin, kısıtlamalarda öngördüklerinizle çelişmemesi tavsiye edilir, aksi takdirde bir çelişki ortaya çıkabilir.

"Parametreler" butonuna tıklanarak ek ayarlar yapılabilir.

Burada kısıtlamanın kesinliğini ve çözümün sınırlarını ayarlayabilirsiniz. Gerekli veriler girildiğinde, "Tamam" düğmesine tıklayın. Ancak bizim durumumuz için bu parametreleri değiştirmeye gerek yoktur.

Tüm ayarlar yapıldıktan sonra, "Bir çözüm bul" düğmesine tıklayın.

Ayrıca hücrelerdeki Excel programı gerekli hesaplamaları yapar. Sonuçların çıktısı ile eş zamanlı olarak, bulunan çözümü kaydedebileceğiniz veya anahtarı uygun konuma getirerek orijinal değerleri geri yükleyebileceğiniz bir pencere açılır. Seçilen seçenekten bağımsız olarak, "Seçenekler iletişim kutusuna geri dön" kutusunu işaretleyerek, bir çözüm arama ayarlarına geri dönebilirsiniz. Onay kutuları ve anahtarlar ayarlandıktan sonra, "Tamam" düğmesine tıklayın.

Herhangi bir nedenle, çözüm aramanın sonuçları sizi tatmin etmezse veya program bunları hesaplarken bir hata verirse, bu durumda, bu durumda, yukarıda açıklandığı gibi, parametreler iletişim kutusuna geri döneriz. Bir yerde bir hata olabileceğinden, girilen tüm verileri gözden geçiriyoruz. Hata bulunmadıysa, "Bir çözüm yöntemi seçin" parametresine gidin. Burada üç hesaplama yönteminden birini seçebilirsiniz: "Doğrusal olmayan sorunlara OPG yöntemiyle bir çözüm arayın", "Tek yönlü yöntemle doğrusal sorunlara bir çözüm arayın" ve "Bir çözüm için evrimsel arama". Varsayılan olarak, ilk yöntem kullanılır. Sorunu başka bir yöntem seçerek çözmeye çalışıyoruz. Başarısız olursa, son yöntemi kullanarak tekrar deneyin. Eylemlerin algoritması yukarıda açıklananla aynıdır.

Gördüğünüz gibi, Bir çözüm bul işlevi, doğru kullanıldığında kullanıcının çeşitli hesaplamalarda zamanından önemli ölçüde tasarruf sağlayabilecek oldukça ilginç bir araçtır. Ne yazık ki, bu eklenti ile doğru bir şekilde çalışabilmek şöyle dursun, her kullanıcı onun varlığının farkında değildir. Bazı yönlerden, bu araç işlevi andırıyor ama aynı zamanda önemli farklılıkları da var.

Eğer excel hücresi aynı hücreye bir bağlantı içeren bir formül (belki doğrudan değil, ancak dolaylı olarak - diğer bağlantılardan oluşan bir zincir aracılığıyla) sunulur, ardından döngüsel bir referans (döngü) olduğunu söylerler. Uygulamada, döngüsel referanslar, yinelemeli bir işlemin uygulanması söz konusu olduğunda, yineleme ilişkileri ile hesaplanır. Her zamanki gibi excel modu bir döngü algılar ve ortaya çıkan durumla ilgili ortadan kaldırılmasını gerektiren bir mesaj verir. Döngüsel başvurular sonsuz sayıda hesaplama oluşturduğundan, Excel hesaplama yapamaz. Bu durumdan çıkmanın iki yolu vardır: döngüsel referansları ortadan kaldırmak veya döngüsel referanslara sahip formüller kullanarak hesaplamalara izin vermek (ikinci durumda, döngü tekrarlarının sayısı sonlu olmalıdır).

Döngüsel referansları kullanarak Newton yöntemini kullanarak bir denklemin kökünü bulma problemini düşünün. Örnek olarak ikinci dereceden bir denklem alın: x 2 - 5x + 6 \u003d 0grafik temsili, 'de gösterilmiştir. Bu (ve diğer herhangi bir) denklemin kökünü yalnızca bir Excel hücresi kullanarak bulabilirsiniz.

Döngüsel hesaplamayı etkinleştirmek için araçlar menüsü / Seçenekler / Hesaplamalar sekmesi onay kutusunu aç Yineleme, gerekirse, alandaki döngü tekrarlarının sayısını değiştirin Yineleme sayısını sınırla ve sahadaki hesaplamaların doğruluğu Bağıl hata (varsayılan olarak değerleri sırasıyla 100 ve 0.0001'dir). Bu ayarlara ek olarak, hesaplama seçeneğini de seçiyoruz. otomatik olarak veya elle... Ne zaman otomatik hesaplama Excel, hesaplanırken hemen nihai sonucu verir elle, her bir yinelemenin sonucunu gözlemleyebilirsiniz.

Şekil: 8. Fonksiyon grafiği

Rasgele bir hücre seçelim, yeni bir isim verelim, diyelim ki - Xve buna, Newton yöntemiyle hesaplamaları belirten tekrar eden bir formül ekleyin:

nerede F ve F1 sırasıyla fonksiyon ve türevinin değerlerini hesaplamak için ifadeler ayarlayın. İkinci dereceden denklemimiz için, formülü girdikten sonra, değer hücrede görünecektir. 2 denklemin köklerinden birine karşılık gelir (). Bizim durumumuzda, ilk yaklaşım belirtilmedi, yinelemeli hesaplama süreci hücrede depolanan varsayılan değerle başladı X ve sıfıra eşittir. İkinci kökü nasıl elde edersiniz? Bu genellikle ilk tahmin değiştirilerek yapılabilir. Her durumda başlangıç \u200b\u200bayarlarını belirleme sorununu farklı yollarla çözebilirsiniz. IF işlevinin kullanımına dayalı bir teknik göstereceğiz. Hesaplamaların netliğini artırmak için hücrelere anlamlı isimler () verildi.

2.2. Parametre seçimi

Formülün istenen sonucunu bildiğinizde, ancak bu sonucu elde etmek için gereken değerleri bilmiyorsanız, aracı kullanabilirsiniz. Parametre seçimikomutu seçerek Parametre seçimi menüde Hizmet... Bir seçenek belirlediğinizde, Excel belirli bir hücredeki değeri, o hücreye başvuran formülle yapılan hesaplama istenen sonucu verene kadar değiştirir.

Örnek olarak aynı ikinci dereceden denklemi alın x 2 -5x + 6 \u003d 0... Denklemin köklerini bulmak için aşağıdakileri yapın:

Excel, parametreyi seçmek için yinelemeli (sıralı) bir işlem kullanır. Yineleme sayısı ve hassasiyet menüde ayarlanır Araçlar / Seçenekler / Hesaplamalar sekmesi... Excel karmaşık bir parametre seçme görevi gerçekleştiriyorsa, Duraklat iletişim kutusunda Parametre seçim sonucu hesaplamayı durdurun ve ardından düğmesine basın Adımtekrar yinelemek ve sonucu görmek için. Adım adım modda bir sorunu çözerken bir düğme belirir Devam et - normal parametre seçim moduna dönmek için.

Örneğe geri dönelim. Yine şu soru ortaya çıkıyor: ikinci kök nasıl alınır? Önceki durumda olduğu gibi, bir başlangıç \u200b\u200byaklaşımı ayarlamak gerekir. Şu şekilde yapılabilir ():

Şekil: 11. İkinci kökü bulmak

Ancak, tüm bunlar biraz daha basit bir şekilde yapılabilir. İkinci kökü bulmak için sabiti C2 hücresine ilk yaklaşım olarak koymak yeterlidir () 5 ve bundan sonra süreci başlat Parametre seçimi.

2.3. Bir çözüm bulmak

Takım Parametre seçimi bilinmeyen bir parametreye bağlı olarak belirli bir hedef değer bulma sorunlarını çözmek için uygundur. Daha karmaşık görevler için şu komutu kullanın Bir çözüm bulmak (Çözücü), menü öğesi üzerinden erişilir Servis / Çözüm Arama.

Çözülebilecek görevler Bir çözüm bulgenel ortamda şu şekilde formüle edilir:

Bulmak:
x 1, x 2, ..., x n
öyle ki:
F (x 1, x 2, ..., x n)\u003e (Maks; Min; \u003d Değer)
kısıtlamalarla:
G (x 1, x 2, ..., x n)\u003e (Ј Değer; i Değer; \u003d Değer)

Aranan değişkenler - işçi hücreleri excel çalışma sayfası - düzenlenmiş hücreler olarak adlandırılır. Amaç fonksiyonu F (x 1, x 2, ..., x n)bazen sadece bir hedef olarak anılan, çalışma sayfasındaki bir hücrede bir formül olarak belirtilmelidir. Bu formül, kullanıcı tanımlı işlevler içerebilir ve ayarlanabilir hücrelere (referans) bağlı olmalıdır. Problemi belirleme anında amaç fonksiyonu ile ne yapılacağı belirlenir. Seçeneklerden birini seçebilirsiniz:

amaç fonksiyonunun maksimumunu bul F (x 1, x 2, ..., x n);
amaç fonksiyonunun minimumunu bul F (x 1, x 2, ..., x n);
amacına ulaşmak F (x 1, x 2, ..., x n) sabit bir değere sahipti: F (x 1, x 2, ..., x n) \u003d bir.

Fonksiyonlar G (x 1, x 2, ..., x n) kısıtlamalar denir. Hem eşitlikler hem de eşitsizlikler şeklinde belirtilebilirler. Düzenlenmiş hücrelere ek kısıtlamalar getirilebilir: nonngativite ve / veya tamsayı, ardından aranan çözüm pozitif ve / veya tamsayılar aralığında aranır.

Bu formülasyon, çeşitli denklemlerin ve denklem sistemlerinin çözümü, doğrusal ve doğrusal olmayan programlama problemleri dahil olmak üzere en geniş optimizasyon problemleri yelpazesini kapsar. Bu tür sorunları formüle etmek genellikle çözmekten daha kolaydır. Ve sonra, belirli bir optimizasyon problemini çözmek için, özel olarak tasarlanmış bir yöntem gereklidir. Çözücü cephaneliğinde bu tür sorunları çözmek için güçlü araçlara sahiptir: genelleştirilmiş gradyan yöntemi, simpleks yöntemi, dal ve sınır yöntemi.

Yukarıda, ikinci dereceden bir denklemin köklerini bulmak için, döngüsel referanslar () ve araç kullanılarak Newton'un yöntemi uygulandı (bölüm 1.4) Parametre seçimi (). Nasıl kullanılacağını görelim Bir çözüm bulmak aynı ikinci dereceden denklem örneğini kullanarak.

Bir diyalog açtıktan sonra Bir çözüm bulmak () aşağıdakileri yapmanız gerekir:

tarlada Hedef hücreyi ayarlayın Optimize edilmiş fonksiyonun değerlerini hesaplamak için formülü içeren hücrenin adresini girin, örneğimizde hedef hücre C4'tür ve içindeki formül şu şekildedir: \u003d C3 ^ 2-5 * C3 + 6;
hedef hücre değerini en üst düzeye çıkarmak için radyo düğmesini ayarlayın maksimum değer 8. konuma gelindiğinde, anahtar en aza indirmek için kullanılır. minimum değer, bizim durumumuzda, anahtarı değere ayarlayın ve değeri girin 0 ;
tarlada Hücreleri değiştirme değiştirilecek hücrelerin adreslerini girin, yani Amaç fonksiyonunun (C3) argümanları, bunları ";" ile ayırarak (veya tuş basılıyken fareyle tıklayarak Сtrl ilgili hücrelerde), çözümü etkileyen tüm hücreleri otomatik olarak aramak için düğmesini kullanın. Tahmin;
tarlada Sınırlamalar düğmeyi kullanarak a ekle arama sonucunun uyması gereken tüm kısıtlamaları girin: örneğimiz için, kısıtlamalar koymanıza gerek yoktur;
bir çözüm bulma sürecini başlatmak için düğmeye basın Yürüt.

Elde edilen çözümü kaydetmek için anahtarı kullanın Bulunan çözümü kaydedin açılan iletişim penceresinde Çözüm arama sonuçları... Ardından çalışma sayfası, gösterilen formu alacaktır. Ortaya çıkan çözüm, C4 hücresinde (işlev bağımsız değişkeni) belirtilen ilk yaklaşım seçimine bağlıdır. C4 hücresine ilk yaklaşım olarak şuna eşit bir değer girin: 1,0 , sonra kullanarak Bir çözüm bul ikinci kökü eşit bul 2,0 .

Çalışmayı yöneten seçenekler Bir çözüm bulpencerede ayarla Seçenekler (düğmeye tıklarsanız pencere açılır Seçenekler pencere Bir çözüm bulmak), aşağıdaki ():

Maksimum süre - bir çözüm bulma süreci için ayrılan süreyi sınırlar (varsayılan değer 100 saniyedir, bu yaklaşık 10 kısıtlı problemler için yeterlidir, eğer problem büyük boyuttaysa, zaman artırılmalıdır).
Yineleme sayısını sınırla - maksimum yineleme sayısını ayarlayarak arama süresini sınırlamanın başka bir yolu. Varsayılan değer 100'dür ve çoğu zaman, çözüm 100 yinelemede elde edilmezse, sayılarında bir artışla (alana 32767 saniyeyi geçmeyen bir süre girebilirsiniz) sonuç alma olasılığı düşüktür. İlk tahmini değiştirmeyi denemek ve arama sürecini yeniden başlatmak daha iyidir.
Bağıl hata - hücrenin hedef değerle eşleştiği kesinliği veya belirtilen sınırlara yaklaşımı belirtir (0'dan 1'e ondalık kesir).
Hata payı - sadece tamsayı kısıtlamaları olan problemler için% olarak ayarlayın. Bir çözüm bulmak bu tür problemlerde, önce optimal tamsayı olmayan çözümü bulur ve sonra çözümün optimal olandan bu parametrenin gösterdiği yüzde sayısından daha fazla farklılık göstermeyeceği en yakın tamsayı noktasını bulmaya çalışır.
Yakınsama - Son beş yineleme için hedef hücredeki değerdeki göreceli değişiklik bu parametrede belirtilen sayıdan (0'dan 1'e kadar olan aralıktan kesir) daha az olduğunda arama durur.
Doğrusal model - amaç işlevi ve kısıtlamalar doğrusal işlevler olduğunda bu onay kutusu etkinleştirilmelidir. Bu, çözüm bulma sürecini hızlandırır.
Negatif olmayan değerler - bu bayrak değişkenler üzerinde kısıtlamalar ayarlamak için kullanılabilir; bu, alt sınırlarına özel kısıtlamalar koymadan pozitif değerler aralığında çözümler aramanıza olanak tanır.
Otomatik ölçeklendirme - bu bayrak, girdi değişkenlerinin değerlerinin ölçeği ve amaç işlevi ve kısıtlamaları, muhtemelen büyüklük sırasına göre farklılık gösterdiğinde etkinleştirilmelidir. Örneğin, değişkenler parçalar halinde belirlenir ve maksimum karı belirleyen amaç işlevi milyarlarca ruble ile ölçülür.
Yineleme sonuçlarını göster - bu onay kutusu, ekranda her bir yinelemenin sonuçlarını gösteren adım adım bir arama sürecini etkinleştirmenize olanak tanır.
Tahminler - bu grup, her bir boyutlu aramada değişkenlerin değerlerinin ilk tahminlerini elde etmek için kullanılan dış değerleme yöntemini - doğrusal veya ikinci dereceden - belirtmeye yarar. Doğrusal teğet vektör boyunca doğrusal ekstrapolasyon kullanmaya hizmet eder. İkinci dereceden Doğrusal olmayan problemleri çözerken daha iyi sonuçlar veren ikinci dereceden ekstrapolasyonu kullanmaya hizmet eder.
Farklar (türevler) - bu grup, amaç ve sınırlayıcı fonksiyonların kısmi türevlerini hesaplamak için kullanılan sayısal farklılaştırma yöntemini göstermeye yarar. Parametre Doğrudan kısıtlamaların değişim oranının nispeten düşük olduğu çoğu görevde kullanılır. Parametre Merkez süreksiz türevi olan fonksiyonlar için kullanılır. Bu yöntem daha fazla hesaplama gerektirir, ancak daha doğru bir çözüm elde etmenin mümkün olmadığına dair bir mesaj görüntülenirse uygulaması haklı çıkarılabilir.
Arama yöntemi - bir optimizasyon algoritması seçmeye yarar. Newton yöntemi daha önce tartışıldı. İÇİNDE Eşlenik gradyan yöntemi daha az bellek talep edilir, ancak Newton'un yönteminden daha fazla yineleme gerçekleştirilir. Bu method Görev yeterince büyükse ve hafızadan tasarruf edilmesi gerekiyorsa ve ayrıca yinelemeler ardışık yaklaşımlarda çok az fark veriyorsa kullanılmalıdır.

bir çözüm bulduktan sonra bir Excel çalışma kitabını kaydederken, iletişim kutularına girilen tüm değerler Bir çözüm bulmakçalışma sayfası verileriyle birlikte kaydedilir. Çalışma kitabındaki her çalışma sayfasıyla bir dizi parametre değeri kaydedilebilir Bir çözüm bul;
bir Excel çalışma sayfası içinde birkaç optimizasyon modelini dikkate almak gerekiyorsa (örneğin, bir işlevin maksimum ve minimumunu veya birkaç işlevin maksimum değerlerini bulmak), bu modelleri düğmeyi kullanarak kaydetmek daha uygundur. Seçenekler / Modeli Kaydet pencere Bir çözüm bulmak... Kaydedilen modelin aralığı, hedef hücre, değiştirilen hücreler, kısıtlamaların her biri ve iletişim kutusunun tüm değerleri hakkında bilgi içerir. Seçenekler... Belirli bir optimizasyon problemini çözmek için bir model seçimi, düğme kullanılarak gerçekleştirilir. Parametreler / Yük modeli diyalog Bir çözüm bulmak;
arama parametrelerini kaydetmenin başka bir yolu da onları adlandırılmış komut dosyaları olarak kaydetmektir. Bunu yapmak için düğmeye tıklayın Komut dosyasını kaydet iletişim kutusu Çözümler için arama sonuçları.

Değiştirilen hücrelere en uygun değerleri eklemeye ek olarak Bir çözüm bulmak sonuçları üç rapor şeklinde sunmanıza olanak tanır: Sonuçlar, Sürdürülebilirlik ve Sınırlar... Bir veya daha fazla rapor oluşturmak için, diyalog penceresinde adlarını seçmelisiniz Çözüm arama sonuçları... Her birini daha ayrıntılı olarak ele alalım.

Şekil: 15. Sürdürülebilirlik raporu

), hedef hücrenin kısıtlamalar ve değişkenlerdeki değişikliklere ne kadar duyarlı olduğu hakkında bilgi içerir. Bu rapor, biri değiştirilebilir hücreler, diğeri de kısıtlamalar için olmak üzere iki bölümden oluşmaktadır. Her bölümdeki sağ sütun, duyarlılık bilgilerini içerir. Değiştirilebilir her hücre ve kısıtlamalar ayrı bir satırda listelenmiştir. Değiştirilebilir hücreler bölümü, karşılık gelen değiştirilebilir hücredeki değer bir birim artırıldığında tüm hücrenin nasıl tepki verdiğini gösteren normalleştirilmiş bir gradyan değeri içerir. Benzer şekilde, kısıtlama bölümündeki Lagrange çarpanı, karşılık gelen kısıtlama değeri bir birim artırıldığında hedef hücrenin nasıl tepki verdiğini gösterir. Tam sayı kısıtlamalarını kullanırken, Excel iletiyi görüntüler Esneklik ve Sınırlar raporları, tamsayı kısıtlamaları olan sorunlar için geçerli değildir... İletişim kutusu Çözüm arama seçenekleri onay kutusu işaretlendi Doğrusal model, daha sonra sürdürülebilirlik raporu birkaç ek bilgi sütunu içerir.) üç tablo içerir: birincisi, hesaplama başlamadan önce amaç işlevi hakkında bilgi içerir, ikincisi, hesaplamanın çözülmesi sonucunda elde edilen bilinmeyen değişkenlerin değerlerini içerir. sorun ve üçüncüsü kısıtlamalar için en uygun çözümün sonuçlarını içerir. Bu rapor aynı zamanda durum ve fark gibi her kısıtlamanın parametreleri hakkında bilgi içerir. Bir durum üç durumda olabilir: bağlı, ilişkisiz veya tamamlanmamış. Fark değeri, çözüm elde edildiğinde kısıtlama hücresinde görüntülenen değer ile kısıtlama formülünün sağ tarafında belirtilen sayı arasındaki farktır. İlişkili bir kısıt, fark değerinin sıfır olduğu bir kısıtlamadır. Bağlanmamış bir sınırlama, sıfır olmayan bir marj değeri ile karşılanan bir kısıtlamadır.

Sınırlar raporu, değiştirilen hücrelerin değerlerinin görev kısıtlamalarını ihlal etmeden artırılabileceği veya azaltılabileceği sınırlar hakkında bilgi içerir. Değiştirdiğiniz her hücre için, bu rapor en iyi değeri ve hücrenin kısıtlamaları ihlal etmeden kabul edebileceği en küçük değerleri içerir.