Skip to content

Excel Çözücü Nedir?

5 de Mayıs de 2021

Excel Çözücü eklentisi matematiksel optimizasyon gerçekleştirir. Bu genellikle karmaşık modelleri verilere uydurmak veya sorunlara yinelemeli çözümler bulmak için kullanılır. Örneğin, bir denklem kullanarak bazı veri noktalarına bir eğri uydurmak isteyebilirsiniz. Çözücü, verilere en iyi uyumu sağlayan denklemdeki sabitleri bulabilir. Diğer bir uygulama, gerekli çıktıyı bir denklemin konusu yapmak için bir modeli yeniden düzenlemenin zor olduğu durumdur.

Çözücü Excel’de Nerede?

Çözücü eklentisi Excel’e dahildir, ancak her zaman varsayılan kurulumun parçası olarak yüklenmez. Yüklenip yüklenmediğini kontrol etmek için, VERİ sekmesini arayın ve Çözücü içindeki ikon Analiz Bölüm.

VERİ sekmesinin altında Çözücü bulamazsanız, eklentiyi yüklemeniz gerekir:

  1. Seçin DOSYA sekmesini seçin ve ardından Seçenekler.

    Excel'de Seçenekleri Seçme.

  2. İçinde Seçenekler diyalog kutusu seç Eklentiler sol taraftaki sekmelerden.

    Excel'de Eklentileri seçme.

  3. Pencerenin alt kısmındaki Excel Eklentileri -den Yönetin açılır ve seçin Git…

    Excel'de eklentileri yönetin

  4. Yanındaki onay kutusunu işaretleyin. Çözücü Eklentisi ve seçin TAMAM MI.

    Çözücü eklentisini seçin

  5. Çözücü komut şimdi görünmelidir VERİ sekmesi. Çözücü kullanmaya hazırsınız.

    Veri sekmesinde Excel Çözücü komutunu bulma

Çözücüyü Excel’de Kullanma

Çözücünün ne yaptığını anlamak için basit bir örnekle başlayalım. 50 birim karelik alana sahip bir çembere hangi yarıçapın vereceğini bilmek istediğimizi hayal edin. Bir dairenin alanı için denklemi biliyoruz (A = pi r2). Elbette, belirli bir alan için gerekli yarıçapı verecek şekilde bu denklemi yeniden düzenleyebiliriz, ancak örnek olarak bunu nasıl yapacağımızı bilmediğimizi varsayalım. Yarıçapı içinde olan bir e-tablo oluşturun B1 ve içindeki alanı hesapla B2 denklemi kullanarak = pi () * B1 ^ 2.

Excel'de daire alanı denklemi

Değeri manuel olarak ayarlayabiliriz B1 a kadar B2 50’ye yeterince yakın bir değer gösterir. Ne kadar doğru olmamız gerektiğine bağlı olarak, bu pratik bir yaklaşım olabilir. Bununla birlikte, çok kesin olmamız gerekirse, gerekli ayarlamaları yapmak uzun zaman alacaktır. Aslında, Çözücü’nin yaptığı temelde budur. Belirli hücrelerdeki değerlerde ayarlamalar yapar ve hedef hücredeki değeri kontrol eder:

  1. Seçiniz VERİ sekme ve Çözücüyüklemek için Çözücü Parametreleri diyalog kutusu

  2. Hedef Belirleyin Alan olacak hücre, B2. Bu, doğru değere ulaşana kadar diğer hücreleri ayarlayarak kontrol edilecek değerdir.

    Basit bir örnek için Excel çözücü seçenekleri

  3. İçin düğmeyi seçin Değeri: ve 50 değerini ayarlayın. Bu, B2’nin ulaşması gereken değerdir.

    Alanın değerini seçme.

  4. Başlıklı kutuda Değişken Hücreleri Değiştirerek: yarıçapı içeren hücreyi girin, B1.

    Değişken Hücreleri Değiştirerek alanına bir değer ekleme.

  5. Diğer seçenekleri varsayılan oldukları gibi bırakın ve seçin Çöz. Optimizasyon gerçekleştirilir, B1’in değeri B2 50 olana kadar ayarlanır ve Çözücü Sonuçları diyalog görüntülenir.

    Excel Çözücü sonuçları

  6. Seçiniz TAMAM MI çözümü korumak için.

    Çözücü Sonuçlarını kabul etmek için Tamam'ı seçmek.

Bu basit örnek, çözücünün nasıl çalıştığını gösterdi. Bu durumda, çözümü başka yollardan daha kolay alabilirdik. Daha sonra Çözücü’nün başka bir yol bulması zor olan çözümler sunduğu bazı örneklere bakacağız.

Excel Çözücü Eklentisini Kullanarak Karmaşık Bir Modeli Uydurma

Excel, bir veri kümesi boyunca düz bir çizgi sığdırarak doğrusal regresyon gerçekleştirmek için yerleşik bir işleve sahiptir. Doğrusal olmayan birçok yaygın işlev doğrusallaştırılabilir, bu da doğrusal regresyonun üstel işlevler gibi işlevlere uymak için kullanılabileceği anlamına gelir. Daha karmaşık işlevler için Çözücü, ‘en küçük kareleri küçültme’ gerçekleştirmek için kullanılabilir. Bu örnekte, formun bir denklemini uydurmayı ele alacağız ax ^ b + cx ^ d aşağıda gösterilen verilere.

Excel Çözücü kullanarak uyacak verileri modelleyin

Bu, aşağıdaki adımları içerir:

  1. Veri kümesini A sütunundaki x değerleri ve B sütunundaki y değerleri ile düzenleyin.

  2. Elektronik tabloda herhangi bir yerde 4 katsayı değerini (a, b, c ve d) oluşturun, bunlara rastgele başlangıç ​​değerleri verilebilir.

  3. 2. adımda oluşturulan katsayılara ve A sütunundaki x değerlerine atıfta bulunan ax ^ b + cx ^ d formundaki bir denklemi kullanarak, uydurulmuş Y değerlerinden bir sütun oluşturun. katsayılar mutlak, x değerlerine yapılan referanslar göreceli olmalıdır.

    Excel'de doğrusal olmayan en küçük kareler modeli kurulumu

  4. Gerekli olmasa da, her iki y sütununu tek bir XY dağılım grafiğindeki x değerlerine karşı çizerek denklemin ne kadar iyi uyduğuna dair görsel bir gösterge elde edebilirsiniz. Orijinal veri noktaları için işaretçiler kullanmak mantıklıdır, çünkü bunlar gürültülü ayrık değerlerdir ve takılan denklem için bir çizgi kullanmak anlamlıdır.

  5. Daha sonra, veriler ile uydurulmuş denklemimiz arasındaki farkı ölçmenin bir yoluna ihtiyacımız var. Bunu yapmanın standart yolu, kareleri alınmış farkların toplamını hesaplamaktır. Üçüncü bir sütunda, her satır için, Y’nin orijinal veri değeri, uyan denklem değerinden çıkarılır ve sonucun karesi alınır. Yani, içinde D2değer şu şekilde verilir: = (C2-B2) ^ 2. Tüm bu kare değerlerin toplamı daha sonra hesaplanır. Değerlerin karesi olduğu için yalnızca pozitif olabilirler.

    Excel'de eğri uydurma modeline eklenen kare farkların toplamı

  6. Artık Çözücü kullanarak optimizasyonu gerçekleştirmeye hazırsınız. Ayarlanması gereken dört katsayı vardır (a, b, c ve d). Ayrıca, karesel farklılıkların toplamını en aza indirecek tek bir hedef değeriniz vardır. Çözücüyü yukarıdaki gibi başlatın ve çözücü parametrelerini aşağıda gösterildiği gibi bu değerleri referans alacak şekilde ayarlayın.

    Excel çözücü parametreleri

  7. Seçeneğinin işaretini kaldırın Kısıtlanmamış Değişkenleri Negatif Olmayan Yapbu, tüm katsayıları pozitif değerler almaya zorlar.

    Kısıtlanmamış Değişkenleri Negatif Olmayan Yap etiketli kutuyu işaretleyerek

  8. Seçiniz Çöz ve sonuçları gözden geçirin. Grafik, uyumun iyiliğine dair iyi bir gösterge verecek şekilde güncellenecektir. Çözücü ilk denemede iyi bir uyum sağlamazsa, tekrar çalıştırmayı deneyebilirsiniz. Uyum iyileştiyse, mevcut değerlerden çözmeyi deneyin. Aksi takdirde, çözmeden önce uyumu manuel olarak iyileştirmeyi deneyebilirsiniz.

    Excel Çözücü kullanarak doğrusal olmayan en küçük kareler çözümü

  9. İyi bir uyum elde edildikten sonra çözücüden çıkabilirsiniz.

Bir Modeli Yinelemeli Çözme

Bazen, bazı girdiler açısından bir çıktı veren nispeten basit bir denklem vardır. Ancak sorunu tersine çevirmeye çalıştığımızda basit bir çözüm bulmak mümkün değildir. Örneğin, bir aracın tükettiği güç yaklaşık olarak P = av + bv ^ 3 burada v hız, a yuvarlanma direnci katsayısı ve b aerodinamik sürükleme katsayısıdır. Bu oldukça basit bir denklem olmasına rağmen, aracın belirli bir güç girişi için ulaşacağı hızın bir denklemini vermek için yeniden düzenlemek kolay değildir. Bununla birlikte, bu hızı yinelemeli olarak bulmak için Çözücü kullanabiliriz. Örneğin, 740 W’lık bir güç girişi ile elde edilen hızı bulun.

  1. Hız, a ve b katsayıları ve bunlardan hesaplanan güç ile basit bir hesap tablosu oluşturun.

    Belirli bir hızda araç gücü için basit hesap tablosu

  2. Çözücüyü başlatın ve güce girin, B5amaç olarak. Nesnel bir değer belirleyin 740 ve hızı seçin, B2, değişecek değişken hücreler olarak. Seçiniz çözmek çözümü başlatmak için.

    Basit yinelemeli çözüm için çözücü parametreleri

  3. Çözücü, güç 740’a çok yakın olana kadar hızın değerini ayarlar ve ihtiyacımız olan hızı sağlar.

    Excel Çözücü kullanarak yinelemeli çözüm

  4. Modelleri bu şekilde çözmek, karmaşık modelleri tersine çevirmekten genellikle daha hızlı ve daha az hataya açık olabilir.

Çözümleyicide bulunan farklı seçenekleri anlamak oldukça zor olabilir. Mantıklı bir çözüm elde etmekte zorluk yaşıyorsanız, o zaman değiştirilebilir hücrelere sınır koşulları uygulamak genellikle yararlıdır. Bunlar, ötesinde ayarlanmaması gereken sınırlayıcı değerlerdir. Örneğin, önceki örnekte hız sıfırdan az olmamalıdır ve ayrıca bir üst sınır ayarlamak da mümkün olacaktır. Bu, aracın daha hızlı gidemeyeceğinden oldukça emin olduğunuz bir hız olacaktır. Değişken değişken hücreler için sınırlar belirleyebiliyorsanız, bu aynı zamanda çok noktalı gibi diğer daha gelişmiş seçeneklerin daha iyi çalışmasını sağlar. Bu, değişkenler için farklı başlangıç ​​değerlerinden başlayarak bir dizi farklı çözüm çalıştıracaktır. Çözme Yöntemini seçmek de zor olabilir. Simplex LP yalnızca doğrusal modeller için uygundur, sorun doğrusal değilse, bu koşulun karşılanmadığını belirten bir mesajla başarısız olur. Diğer iki yöntemin her ikisi de doğrusal olmayan yöntemlere uygundur. GRG Doğrusal Olmayan en hızlısıdır ancak çözümü büyük ölçüde ilk çalıştırma koşullarına bağlı olabilir. Değişkenlerin sınırlara sahip olmasını gerektirmeyen esnekliğe sahiptir. Evrimsel çözücü genellikle en güvenilir olanıdır, ancak tüm değişkenlerin hem üst hem de alt sınırlara sahip olmasını gerektirir, bu da önceden hesaplanması zor olabilir. Excel Çözücü eklentisi, birçok pratik soruna uygulanabilen çok güçlü bir araçtır. Excel’in gücüne tam olarak erişmek için Çözücü’yü Excel makrolarıyla birleştirmeyi deneyin.