HEDEF ARA ve ÇÖZÜCÜ
HEDEF ARA
Hedef ara komutu bir fonksiyonun tersinin bulunmasında kullanılır. Hedef ara işlemi, y=f(x) gibi bir fonksiyonda y değeri verildiğinde x değerinin bulunmasıdır. Bu işlem, f(x) fonksiyonun ters fonksiyonu bulunup hesap yapmak demek değildir. Hedef ara işleminde, x değişkenine bir sistem dahilinde çeşitli değerler verilerek istenilen y değeri bulunur. Burada yapılan işlem bir iterasyon (yineleme) işlemidir.
Hedef ara komutunu çalıştırmak için önce bir hücreye herhangi bir x değeri yazılır; örneğin sıfır. Başka bir hücreye y fonksiyonu, x değerinin bulunduğu hücreye başvuru yapılarak yazılır. Bu yazımdan sonra elektronik tablo hemen x değerine bağlı olarak y değerini hesaplar. Sonra hedef ara komutu şu şekilde verilir: x hücresindeki değeri değiştirerek y hücresinde istenilen değeri bul. Komut x hücresindeki değeri değiştirerek y hücresindeki istenilen değeri bulmaya çalışır; bazen bulur, bazen bulamaz. Ayrıca y değerinin x değerine doğrudan doğruya bağlı olmasına gerek yok; dolaylı şekilde de bağlı olabilir. Örneğin x değeri ile g(x) fonksiyonu hesaplanır.
Sonra g(x) fonksiyonu ile h((g(x)) fonksiyonu hesaplanır. En sonunda h((g(x)) fonksiyonundan y=f(h((g(x))) hesaplanabilir. Bu işlemler için birkaç hücre kullanılabilir. Bu şekildeki bağımlılık dolaylı bağımlıktır.
Basit bir örnek olmak üzere y=sin(x) ifadesinde y=0,5 olduğu zaman x değerini bulmaya çalışalım. Şekil x de B2 hücresine x değeri yazılır. Burada yazılan değer iterasyonun başlangıç değeridir. Örnekte başlangıç değeri olarak 0 alınmıştır. B3 hücresine ise SİN(RADYAN(B2)) yazılır; sonuç 0 dır.
Problemde B3 hücresindeki değerin 0,5 olması isteniyor.
“Araçlar” menüsünde “Hedef Ara” komutu verildiğinde, şekil x de görülen iletişim kutusu ekrana gelir. İletişim kutusunda üç soru sorulmaktadır ve bu soruların cevapları iletişim kutusunda bulunan üç metin kutusuna yazılacaktır. “Ayarlanacak hücre” B3 hücresidir. Sonuç değerinin 0,5 olması istendiğinden “Sonuç hücre” kısmına 0,5 yazılır ve “Değiştirilecek hücre”
kısmına ise B2 yazılır. Tamam düğmesine basılarak komut verilir.
Şekil x
Komut istenilen işlemi yaptıktan sonra ekran şekil x de görülen iletişim kutusu gelir. Komut bir çözüm bulmuştur. Çözüm değerleri kabul edilirse taman düğmesine basılır ve bulunan çözüm değerleri hücrelerde kalır.
Çözüm kabul edilmez ise “İptal” düğmesine basılarak hücrelerdeki değerler özgün değerlere dönüşür; yani x=0, y=0 haline gelir,
Şekil x
Hedef arama işlemi devam ederken ekranda beliren “Hedef Arama Durumu” iletişim kutusunda “Duraklat” düğmesine basıldığında işlem durur ve istenirse “Adımla” düğmesi ile adım adım devam eder. Yalnız işlemlerin çoğu çok hızla yapıldığından bu düğmeye basmak zor olabilir ancak uzun süren işlemlerde bu düğme kullanılır. Örneğin daha önceki problemin hedefi 1,5 olarak verelim. Bu durumda problemin çözümü yoktur.
İşlem durdurulduğunda şekil x de görülen iletişim kutusu ekran geldi.
İşlemin devamı için “Devam” veya “Adımla” düğmesine basmak gerekir.
Çözüm bulunamaz ise “Hedef Arama Durumu” iletişim kutusu “B3 hücresi ile hedef arama çözüm bulamadı” mesajını verir.
Şekil x
Hedef ara komutunu kullanırken program bakımından dikkat edilecek noktalar şunlardır: a) Değişecek hücre, ayarlanacak hücreye doğrudan doğruya veya dolaylı olarak etkilemesi gerekir; yani değişecek hücredeki değer değiştiğinde ayarlanacak hücredeki değerinde değişmesi gerekir, b) değişecek hücre bir formül içermemelidir, c) çözümü kabul edilip sonradan fikir değiştirilirse özgün duruma dönüş, “Düzen” menüsünden geri al komutu veya “Standart Araç” çubuğundaki geri al düğmesi ile yapılır, Hedef ara komutunu kullanırken sayısal hesap yönünden dikkat edilmesi gereken noktalar şunlardır: a) Problemin çözümü olmayabilir, b) problemin çözümü olmasına karşın seçilen başlangıç değeri ile çözüm bulunamayabilir.
Bu durumda başlangıç değerini değiştirmek gerekir, c) problemin çözümü birden fazla ise seçilen başlangıç değerleri ile farklı farklı çözümler bulunur, örnek olarak yukarıda verilen problemde başlangıç değeri x=130 alındığında çözüm değeri olarak x=149,9760 bulunur.
Hedef ara komutu kullanılırken komuta ait iki ayarlamanın önceden yapılması gerekir. Birinci ayarlama: Komut işlem yaparken en fazla kaç iterasyon (yineleme) yapacağıdır ki bu durum çözüm bulunmadığı zaman iterasyon belirli sayıda kesilmesi için önemlidir. Bu sayı aşıldığında çözüm bulunamadı mesajını verilerek işlem bırakır.
İkinci ayarlama ise iterasyon sırasında iki hesaplama arasındaki en büyük farkın ne olacağıdır; yani çözümün kaç basamak doğruluk ile hesaplanacağıdır. Bu durum hesap duyarlılığı bakımından önemlidir.
Nitekim yukarıda yapılan hesaplamada y değerinin 0,5 istenmesine karşın 0,49991 olarak sağlatılmış ve x=30 yerine x=29,9942 bulunmuştur.
Yukarıda bahsedilen ayarlamalar “Araçlar” menüsünden “Seçenekler”
komutu ile yapılır. Seçenekler komutu gelen iletişim kutusundan
“Hesaplama” sekmesi seçildiğinde ekrana şekil x de görülen sekme gelir. Bu sekmeden bulunan “Yineleme” kısmında “En fazla yineleme” ve “En büyük
değişiklik” metin kutularına değer girerek yukarıda bahsedilen ayarlamalar yapılır.
Şekil x
Hedef ara komutu denklemlerin köklerinin bulunmasında da kulanılır.
Örneğin x2=e-x+1 denkleminin kökünü hedef ara komutu ile bulmaya çalışalım. Verilen denklem y=f(x)=x2-e-x-1 şeklinde yazıp y değerini sıfır yapan x değerini bulacağız. Şekil x de başlangıç değeri olarak x=0 alınıp y değeri hesaplanmıştır. Sonra hedef ara komutu verilerek şekil x de ikinci şekilde çözüm bulunmuştur.
Şekil x
ÇÖZÜCÜ
Çözücünün çalışabilmesi için yüklü olması gerekir. Araçlar menüsünde
“Çözücü” seçeneği var ise çözücü yüklüdür. Aksi halde “Araçlar”
menüsünden “Eklentiler” komutu ile eklenecek programların listesi görüntülenir. Liste onay kutularından oluşmaktadır. “Çözücü eklentisine”
onay verildikten sonra “Tamam” düğmesi tıklanarak çözücü programa eklenir. “Eklentiler” komutunun listesinde “Çözücü” eklentisi bulunmuyorsa Excel programını baştan kurmak gerekir.
Çözücü genel anlamda “Hedef ara” komutunun çok gelişmişidir. Dolayısıyla hedef ara komutu çözücünün çok özel bir halidir.
Çözücünün amacı; belirli kısıtlamalar altında birden fazla hücrenin değerini değiştirerek belli bir hücrenin uygun değerini (optimum değerini) bulmaktır. İşletmelerde karı maksimize etme, maliyeti minimize etme gibi durumlarda bu problem ile karşılaşılır. Bu tip problemlere optimizasyon (uygun değeri bulma) problemleri adı verilir. Uygun değeri (optimum değeri) bulunacak fonksiyona amaç fonksiyonu adı verilir.
Amaç fonksiyonu ve kısıtlamalar doğrusal ise böyle probleme doğrusal optimizasyon aksi halde doğrusal olmayan optimizasyon adı verilir. Bazı problemlerde değişkenlerin tam sayı olması istenir; örneğin çalışan insan sayının optimize edilmesinde. Bu tip optimizasyona “Tam sayı”
optimizasyonu adı verilir.
Çözücüyü çalıştırmadan önce şunların belirlenmesi gerekir: a) Hedef hücre:
Bu hücrenin değeri değişerek; en büyük, en küçük veya belirli değere eşit olacaktır. Amaç fonksiyonunun yazıldığı hücredir. Bu hücrenin formül içermesi gerekir. “Hedef Hücre” belirtilmez ise çözücü sadece kısıtlamaları sağlayan bir çözüm üretir. b) Değişken hücreler: Bu hücrelerin değerleri değişerek hedef hücre değerinin istenilen şekilde ayarlanmasını sağlarlar.
Bu hücreler doğruda doğruya ve dolaylı olarak hedef hücre ile ilişkili olması gerekir. En fazla 200 değişken hücre seçilebilir. c) Kısıtlamalar: Hedef hücre değeri ayarlanırken, bu hücrelerin değerleri belirli şartları sağlaması gerekir. Kısıtlamalar hedef hücre ve değişken hücrelere de uygulanabilir.
Örnek olarak şu problemi ele alalım: f=4x+4y+6z fonksiyonu aşağıda verilen kısıtlamalar altında maksimize edilecek.
x+z≤1; y+z≤1; x,y,z ≥0
Şekil x de görüldüğü gibi x, y ve z değişkenlerinin değerlerini sıfır olarak B1, B2 ve B3 hücrelerinde tanımlayalım. Maksimize edilecek f fonksiyonu
D2 hücresinde ve ilk iki kısıtlama ise F1, F2 hücrelerinde tanımlanmıştır.
Bu durumda hedef hücre D2 olup bu hücredeki değer maksimize edilecektir.
Değişken hücreler B1, B2, B3 dir. Kısıtlamaların bulunduğu hücreler B1, B2, B3, F1 ve F2 dir.
Şekil x
Araçlar menüsünden “Çözücü” çağrıldığında ekrana şekil xx de görülen
“Çözücü parametreleri” isimli iletişim kutusunun boş hali gelir. Şekilde görülen iletişim kutusu probleme uygun olarak doldurulmuştur.
İletişim kutusunda “Hedef Hücre” metin kutusuna D2 olarak yazılır. Daha sonra “Eşittir:” seçenek düğmelerinden “En Büyük” düğmesi seçilir.
“Değişen Hücreler” metin kutusuna B1, B2 ve B3 hücrelerinin isimleri yazılır. Yazımda hücreler arasına “;” konur. Buraya B1:B3 erimi de yazılabilir. Yazımda başvuru adreslerinin mutlak veya göreceli olması önemli değildir. Adresler imleç ile tablodan işaretlenirse program otomatik olarak mutlak başvuru adresini kullanır.
Şekil xx
Kısıtlamaların yazılışına gelince: “Ekle” düğmesi tıklandığında ekrana şekil x de görülen “Kısıtlama Ekle” isimli” iletişim kutusu gelir. İletişim kutusunda önce kısıtlamaya ait hücre başvurusu veya hücre erimi girilir;
şekilde örnek olarak F1 hücresi yazılmıştır. Sonra açılır liste kutusunda uygun matematik işaret seçilir. Liste kutusunda; büyük-eşit, küçük-eşit, eşit, tam sayı, ikili seçenekleri bulunmaktadır. Son olarak “Kısıtlama”
metin kutusuna kısıtlamanın değeri yazılır. Buraya formül veya hücre referansı yazılabilir. Ekle komutuna basıldığında kısıtlama eklenir ikinci kısıtlama için boş bir “Kısıtlama Ekle” kutusu gelir. Kısıtlama girişi bitince
“Tamam” düğmesi tıklanarak şekil xx de görülen “Çözücü Parametreleri”
iletişim kutusuna geri dönülür.
Şekil x
Herhangi bir kısıtlama değiştirilmek istendiğinde şekil xx de görülen
“Çözücü Parametreleri” iletişim kutusunda önce değiştirilecek kısıtlamanın üzeri tıklanarak kısıtlama ters renkte görüntülenir sonra “Değiştir”
düğmesi tıklanarak “Kısıtlama Değiştir” iletişim kutusu ekrana gelir. Bu kutu şekil x de görülen “Kısıtlama Ekle” iletişim kutusu aynısıdır.
Değişiklikler bu kutu üzerinde yapılır. “Tamam” düğmesine basılarak değişiklik bitirilir.
Bir kısıtlama iptal edilecek ise kısıtlamanın üzeri tıklanarak kısıtlama ters renkli görüntülendikten sonra “Sil” düğmesi kullanılır. “Değiştir” veya “Sil”
düğmesini tıklamadan önce herhangi bir kısıtlama üzeri tıklanarak ters renkli olarak görüntülenmemişse “Değiştirilecek Kısıtlama yok” veya
“Silinecek Kısıtlama yok” mesajlarını veren bilgi kutusu ekrana gelir.
İletişim kutusunun bütün girişleri bittikten sonra kutudaki “Çöz” düğmesi tıklandığında çözüm yapılır ve şekil x de görülen “Çözücü Sonuçları” isimli iletişim kutusu ekrana gelir. Çözüm sonunda değişecek hücre değerleri değişmiş çözüm değerlerini almıştır. Şekil x de görüldüğü x=1, y=1 ve z=0 olmuş f(x,y,z) in maksimum değeri ise 8 dir. “Çözümü sakla” seçenek düğmesi otomatik olarak işaretli bulunmaktadır. “Tamam” düğmesi tıklanırsa çözüm değerleri saklanarak çalışma tablosuna dönülür. İstenirse
“Özgün değerleri yeniden yükle” düğmesi tıklanarak özgün değerler geri dönülür.
Şekil x de görülen “Yanıt”, “Duyarlılık” ve “Sınırlamalar” raporları çözüm sırasında yapılan işlemler ve çözümün duyarlılığı raporlarıdır. Bu raporları yazdırmak istenirse raporların isimleri üzerine tıkanarak isimler ters renkte görüntülenir sonra “Tamam” düğmesi tıklanır. Her bir rapor ayrı bir çalışma sayfasına yazılır.
İkinci örnek olarak, üç eksenli gerilme halinde asal gerilmelerin bulunduğu düzlemleri bulurken karşımıza çıkan doğrusal olmayan bir denklem takımının çözümünü verelim.
Bir üç eksenli bir gerilme halinde σx=80 MPa, σy=40 MPa, σz=60 MPa, τxy=- 10 MPa, τxz=-30 MPa, τyz=25 MPa olsun Bu gerilme halinde σ1=109,3965 MPa asal gerilmesini bulunduğu düzlemin doğrultman kosinüsleri x,y ve z yi veren denklemler
2 2 2
29,3965 10 30 0 10 69,3965 25 0
1
x y z
x y z
x y z
dir. Bu doğrusal olamayan denklem takımını çözücü ile çözeceğiz. Amaç fonksiyonu yukarıda verilen üçüncü denklem; birinci ve ikinci denklemler ise kısıtlamalardır. Bu bilgiler bir çalışma sayfasına şekil x de görüldüğü gibi yazılır. B1, B2, B3 hücreleri değişken hücreler olup ilk değerleri sıfırdır.
B5 hücresi ise hedef hücredir. Kısıt-1 ve Kısıt-2 yukarıdaki birinci ve ikinci denklemler olup kısıt-1 de +B1*D1+B2*E1+B3*F1 ve kısıt-2 de ise +B1*D2+B2*E2+B3*F2 formülleri bulunmaktadır.
Şekil x
Çözücü çağrıldığında ekrana gelen iletişim kutusu şekil x de görüldüğü gibi hedef hücreye 1 değeri verilerek doldurulur.
Şekil x
“Çöz” komutu verildiğinde bulunan sonuç şekil x de görülmektedir.
Şekil x
Başlangıç şartı olarak x=y=z=0 yerine x=1, y=-1 ve z=-1 verildiğinde şekil x de görülen farklı çözüm elde edilir.
Şekil x
Şekil xx de görülen “Çözücü Parametreleri” iletişim kutusunda bazı düğmelerin işlevlerini açıklayalım.
“Eşittir” seçenek düğmelerinden biri seçilerek amaç fonksiyonun alması gereken değer belirlenir. Şayet “Değer” düğmesi seçilir ise yanında bulunan metin kutusu açılarak amaç fonksiyonun alacağı değer yazılır.
“Çöz” düğmesinin işlevi bellidir. Çözüm bittikten sonra çözücü ayarları (çözücü parametreleri ve çözücü seçenekleri) aynen korunur. Çalışma kitabında her sayfasında tanımlanan ayarlar o sayfa için korunur. Kısaca her sayfa için farklı ayarlar korunur. Sayfanın kopyası alındığında ayarlarda kopyalanır. “Kapat” düğmesi ile işlev yapmadan iletişim kutusunda çıkılır. Bu çıkıştan önce yapılan ve değiştirilen ayarların hepsi korunur. “Tümünü Sıfırla” düğmesi ile ayarların hepsi sıfırlanır. Bir problemde birden fazla çözücü seçenekleri ile çalışılacak ise her bir biri bir model olarak sayfanın bir yerine kaydedilir. İstenildiği zaman yüklenir.
Bunun nasıl yapılacağı ileride açıklanacaktır.
“Yardım” düğmesi ile iletişim kutusunda bulanan parametreler hakkında açıklayıcı bilgiler gelir.
“Seçenekler” düğmesi ile şekil x de görülen “Çözücü Seçenekleri” iletişim kutusu ekrana gelir. Bu kutuda bulunan “Zaman Sınırı” ve “Yineleme”
çözücünün çözüm için kullanacağı maksimum yineleme (iterasyon) sayısı ve zamandır. Çözücü, problemin sonucunu verilen zaman ve yineleme sayısında bulamazsa işlemi durdurur ve devam etmek için izin ister. Bu kutulara yazılacak en büyük değer 32767 dir.
Şekil x
“Duyarlılık”, “Tolerans” ve “Yakınsama” alınan sonuçların hassasiyeti ile ilgilidir. “Hesaplamalar”, “Türevler” ve “Ara” kısmında bulunan seçenek düğmeleri hesaplamalarda kullanılan yöntemler ile ilgilidir. Normal problemler için kutularda verilen (varsayılan) değerler yeterlidir.
Onay kutularında “Yineleme Sonucun Göster” düğmesine onay verildiğinde işlemin ara hesaplarını adım adım görüntüler. “Negatif Olmadığını Varsay”
kutusuna onay verildiğinde, değişken hücrelerdeki bütün değerlerin 0 veya sıfırdan büyük olacak şekilde çözüm aranır. Bu şart kısıtlama ile de verilebilir. Bu şart birçok problemde kullanıldığında bu seçenek ile kısıtlama girişlerinden tasarruf edilir.
Çözülmesi istenen problem doğrusal ise “Doğrusal Model Varsay” seçeneği işlemi hızlandırır. Değişen hücre ile hedef hücresindeki sayılarlar birbirlerinden çok farlı ise “Otomatik Ölçek Kullan” onayı verilir. Örneğin milyarlık yatırımlara karşı küçük sayılarla ifade edilen faiz oranı gibi, Aynı problem için farklı çözücü ayarları kullanılacak ise “Model Kaydet”
düğmesi ile bu ayarlar saklanır. Sakladığı yer, çalışma sayfasında kullanıcının gösterdiği bir hücre erimidir. Gerektiğinde “Model Yükle”
düğmesi ile sonradan yüklenir. Yüklenirken ekrana gelen “Model yükle”
iletişim kutusunda modelin saklandığı hücre erimi yazılır ve imleç ile gösterilir.
kullanılırsa kısa zamanda sonuç alınır. Ayrıca çözümden uzak başlangıç değerleri ile çözüm bulanamayabilir. Çözücü beklenen sonuçtan farklı bir sonuç buluyorsa farklı başlangıç değerleri ile problemi tekrar çözmek faydalıdır.
