4.1. Yerleşik işlevleri kullanma

hesaplama regresyon katsayıları fonksiyon kullanılarak gerçekleştirilir

LINEST(Değerler_y; Değerler_x; Konst; İstatistik),

Değerler_y- y değerleri dizisi,

Değerler_x- isteğe bağlı değerler dizisi x eğer dizi X atlandığında, bunun aynı boyutta bir dizi (1;2;3;...) olduğu varsayılır. Değerler_y,

Konst- sabitin gerekli olup olmadığını gösteren bir boole değeri b 0'a eşitti. Konst anlamı var DOĞRU veya atlanmış, o zaman b olağan şekilde hesaplanır. eğer argüman Konst YANLIŞ, o zaman b 0 olduğu varsayılır ve değerler a ilişki kurulacak şekilde seçilmiştir. y=ax.

İstatistik- ek regresyon istatistiklerinin döndürülmesinin gerekip gerekmediğini gösteren bir boole değeri. eğer argüman İstatistik anlamı var DOĞRU, ardından fonksiyon LINEST ek regresyon istatistikleri döndürür. eğer argüman İstatistik anlamı var YANLIŞ veya atlanırsa, işlev LINEST sadece katsayısını döndürür a ve kalıcı b.

Unutulmamalıdır ki fonksiyonların sonucu SATIR() bir dizi değerdir - bir dizi.

Hesaplama için korelasyon katsayısı işlev kullanılır

KOREL(Dizi1;dizi2),

korelasyon katsayısının değerlerinin döndürülmesi, burada Dizi1- değerler dizisi y, dizi2- değerler dizisi x. Dizi1 ve dizi2 aynı boyutta olmalıdır.

ÖRNEK 1. Bağımlılık y(x) tabloda sunulmaktadır. İnşa etmek regresyon hattı ve hesapla korelasyon katsayısı.

MS Excel sayfasına bir değerler tablosu girelim ve bir dağılım grafiği oluşturalım. Çalışma sayfası Şekil 1'de gösterilen formu alacaktır. 2.

Regresyon katsayılarının değerlerini hesaplamak için a ve b hücreleri seç A7: B7, fonksiyon sihirbazına ve kategoriye dönelim istatistiksel bir işlev seç LINEST. Şekilde gösterildiği gibi görünen iletişim kutusunu doldurun. 3 ve basın TAMAM.

Sonuç olarak, hesaplanan değer yalnızca hücrede görünecektir. A6(Şek. 4). Bir hücrede bir değerin görünmesi için B6 düzenleme moduna girmeniz gerekir (tuş F2) ve ardından tuş kombinasyonuna basın CTRL+SHIFT+ENTER.

Hücre başına korelasyon katsayısının değerini hesaplamak için C6 aşağıdaki formül tanıtıldı:

C7=KOREL(B3:J3;B2:J2).

Regresyon katsayılarını bilmek a ve b fonksiyonun değerlerini hesapla y=balta+b verilen için x. Bunu yapmak için formülü tanıtıyoruz

B5=$A$7*B2+$B$7

ve aralığa kopyalayın С5:J5(Şek. 5).

Şemada regresyon doğrusunu çizelim. Grafikte deneysel noktaları seçin, sağ tıklayın ve komutu seçin İlk veri. Görüntülenen iletişim kutusunda (Şekil 5) sekmesini seçin Kürek çekmek ve düğmeye tıklayın Ekle. Giriş alanlarını Şekil 2'de gösterildiği gibi doldurun. 6 ve düğmesine basın TAMAM. Deneysel veri grafiğine bir regresyon çizgisi eklenecektir. Varsayılan olarak, grafiği, düzleştirme çizgileriyle bağlanmamış noktalar olarak görüntülenecektir.

Pirinç. 6

Regresyon çizgisinin görünümünü değiştirmek için aşağıdaki adımları uygulayın. Çizgi grafiği gösteren noktalara sağ tıklayın, komutu seçin Grafik türü ve dağılım grafiğinin türünü Şekil 1'de gösterildiği gibi ayarlayın. 7.

Çizgi tipi, rengi ve kalınlığı aşağıdaki gibi değiştirilebilir. Diyagramdaki satırı seçin, farenin sağ tuşuna basın ve içerik menüsünde komutu seçin Veri Serisi Formatı… Ardından, örneğin Şekil 2'de gösterildiği gibi ayarları yapın. sekiz.

Tüm dönüşümlerin bir sonucu olarak, bir grafik alanında bir deneysel veri grafiği ve bir regresyon çizgisi elde ederiz (Şekil 9).

4.2. Bir eğilim çizgisi kullanma.

MS Excel'de çeşitli yaklaşık bağımlılıkların oluşturulması, bir grafik özelliği olarak uygulanır - eğilim çizgisi.

ÖRNEK 2. Deney sonucunda, bazı tablo bağımlılıkları belirlendi.

Yaklaşık bir bağımlılık seçin ve oluşturun. Tablo ve uygun analitik bağımlılıkların grafiklerini oluşturun.

Problemin çözümü şu aşamalara ayrılabilir: başlangıç ​​verilerinin girilmesi, bir dağılım grafiğinin oluşturulması ve bu çizime bir trend çizgisinin eklenmesi.

Bu süreci ayrıntılı olarak ele alalım. İlk verileri çalışma sayfasına girelim ve deneysel verileri çizelim. Ardından, grafikteki deneysel noktaları seçin, sağ tıklayın ve komutu kullanın Ekle ben eğilim çizgisi(Şek. 10).

Görüntülenen iletişim kutusu, yaklaşık bir bağımlılık oluşturmanıza olanak tanır.

Bu pencerenin ilk sekmesi (Şekil 11), yaklaşık bağımlılığın türünü gösterir.

İkincisi (Şekil 12) inşaat parametrelerini tanımlar:

yaklaşık bağımlılığın adı;

İleriye (geriye) tahmin n birimler (bu parametre, trend çizgisini uzatmak için kaç birim ileri (geri) gerektiğini belirler);

eğrinin çizgiyle kesiştiği noktanın gösterilip gösterilmeyeceği y=sabit;

yaklaşık fonksiyonun diyagramda gösterilip gösterilmeyeceği (denklemi diyagram parametresinde gösterin);

Standart sapma değerinin diyagrama yerleştirilip yerleştirilmeyeceği (yaklaşım güvenilirliğinin değerini diyagrama yerleştirecek parametre).

İkinci dereceden bir polinomu yaklaşık bağımlılık olarak seçelim (Şekil 11) ve bu polinomu grafik üzerinde açıklayan bir denklem türetelim (Şekil 12). Ortaya çıkan diyagram, Şek. on üç.

Benzer şekilde, trend çizgileri gibi bağımlılıkların parametrelerini seçebilirsiniz

doğrusal y=a∙x+b,

logaritmik y=bir ln(x)+b,

üstel y=a∙eb,

güç y=bir x b,

polinom y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d ve benzerleri, 6. dereceden polinom dahil olmak üzere,

Doğrusal filtreleme.

4.3. Karar Vericiyi Kullanmak

Metod ile parametre seçiminin MS Excel'de uygulanması oldukça ilgi çekicidir. en küçük kareler Bir karar bloğu kullanarak. Bu teknik, herhangi bir türdeki bir fonksiyonun parametrelerini seçmenize izin verir. Bu olasılığı aşağıdaki problem örneğinde ele alalım.

ÖRNEK 3. Deney sonucunda tabloda sunulan z(t) bağımlılığı

Bağımlılık katsayılarını seçin Z(t)=4'te +Bt 3 +Ct 2 +Dt+K en küçük kareler yöntemiyle

Bu problem, beş değişkenli bir fonksiyonun minimumunu bulma problemine eşdeğerdir.

Optimizasyon problemini çözme sürecini düşünün (Şekil 14).

değerler olsun ANCAK, AT, İle, D ve İle hücrelerde depolanır A7: E7. Fonksiyonun teorik değerlerini hesaplayın Z(t)=At4+Bt3+Ct2+Dt+K verilen için t(B2:J2). Bunu yapmak için hücrede B4 fonksiyonun değerini ilk noktaya girin (hücre B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Bu formülü aralığa kopyalayın С4:J4 ve apsisleri hücrelerde saklanan noktalarda fonksiyonun beklenen değerini alın B2:J2.

hücreye B5 deneysel ve hesaplanan noktalar arasındaki farkın karesini hesaplayan bir formül sunuyoruz:

B5=(B4-B3)^2,

ve aralığa kopyalayın С5:J5. bir hücrede F7 toplam ikinci dereceden hatayı (10) saklayacağız. Bunu yapmak için formülü tanıtıyoruz:

F7 = TOPLA(B5:J5).

komutunu kullanalım. Service®Çözüm arayın ve optimizasyon problemini kısıtlamalar olmadan çözün. Şekil 2'de gösterilen iletişim kutusundaki uygun giriş alanlarını doldurun. 14 ve düğmesine basın Çalıştırmak. Bir çözüm bulunursa, Şekil 2'de gösterilen pencere. on beş.

Karar bloğunun sonucu hücrelerin çıktısı olacaktır. A7: E7parametre değerleri fonksiyonlar Z(t)=At4+Bt3+Ct2+Dt+K. hücrelerde B4:J4 alırız beklenen fonksiyon değeri başlangıç ​​noktalarında. bir hücrede F7 tutulacak toplam kare hatası.

Aralığı seçerseniz, deneysel noktaları ve takılı çizgiyi aynı grafik alanında görüntüleyebilirsiniz. B2:J4, aramak Grafik Sihirbazı ve sonra biçimlendir görünüm alınan çizelgeler.

Pirinç. 17, hesaplamalar yapıldıktan sonra MS Excel çalışma sayfasını görüntüler.

5. REFERANSLAR

1. Alekseev E.R., Chesnokova O.V., Mathcad12, MATLAB7, Maple9 paketlerinde hesaplamalı matematik problemlerini çözme. – NT Press, 2006.–596s. :hasta. – (Öğretici)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, mühendislik ve matematik problemlerini çözüyor. –M., BİNOM, 2008.–260s.

3. I. S. Berezin ve N. P. Zhidkov, Hesaplama Yöntemleri, Moskova: Nauka, 1966.

4. Garnaev A.Yu., MS EXCEL ve VBA'nın ekonomi ve finansta kullanımı. - St. Petersburg: BHV - Petersburg, 1999.-332p.

5. B. P. Demidovich, I A. Maron, V. Z. Shuvalova, Sayısal yöntemler analiz.–M.: Nauka, 1967.–368s.

6. Korn G., Korn T., Bilim adamları ve mühendisler için matematik el kitabı.–M., 1970, 720p.

7. Alekseev E.R., Chesnokova O.V. Uygulama için metodik talimatlar laboratuvar işi MS Excel'de. Tüm uzmanlık öğrencileri için. Donetsk, DonNTU, 2004. 112 s.

Özellikler arasındaki stokastik ilişkileri incelemek için kullanılan yöntemlerden biri regresyon analizidir.
Regresyon analizi bulmak için kullanılan regresyon denkleminin türevidir. ortalama değer bir rastgele değişken (özellik-sonuç), eğer başka (veya diğer) değişkenlerin (özellik-faktörleri) değeri biliniyorsa. Aşağıdaki adımları içerir:

1. bağlantı biçiminin seçimi (analitik regresyon denkleminin türü);
2. denklem parametrelerinin tahmini;
3. analitik regresyon denkleminin kalitesinin değerlendirilmesi.

Parametre tahmini için en yaygın olarak kullanılan en küçük kareler yöntemi (LSM).
En küçük kareler yöntemi, regresyon denkleminin parametrelerinin en iyi (tutarlı, verimli ve yansız) tahminlerini verir. Ancak, yalnızca rastgele terim (u) ve bağımsız değişken (x) hakkındaki belirli varsayımlar karşılanırsa (bkz. OLS varsayımları).

En küçük kareler yöntemiyle doğrusal bir çift denklemin parametrelerini tahmin etme problemi aşağıdakilerden oluşur: etkin özelliğin gerçek değerlerinin kare sapmalarının toplamının - y i hesaplanan değerlerden - minimum olduğu parametrelerin bu tür tahminlerini elde etmek.
resmen OLS kriterişöyle yazılabilir: .

En küçük kareler yöntemlerinin sınıflandırılması

1. En küçük kareler yöntemi.
2. Maksimum olabilirlik yöntemi (normal bir klasik doğrusal regresyon modeli için, regresyon artıklarının normalliği varsayılır).
3. Hata otokorelasyonu ve değişen varyans durumunda GLSM'nin genelleştirilmiş en küçük kareler yöntemi kullanılır.
4. Ağırlıklı en küçük kareler yöntemi (heteroskedastik artıklarla özel bir GLSM durumu).

özü örnekleyin grafiksel olarak klasik en küçük kareler yöntemi. Bunu yapmak için, gözlemsel verilere (x i , y i , i=1;n) dayalı bir dağılım grafiği oluşturacağız. dikdörtgen sistem koordinatlar (böyle bir dağılım grafiğine korelasyon alanı denir). Korelasyon alanının noktalarına en yakın olan doğruyu bulmaya çalışalım. En küçük kareler yöntemine göre doğru, korelasyon alanı ile bu doğru arasındaki noktalar arasındaki dikey uzaklıkların karelerinin toplamı minimum olacak şekilde seçilir.

Bu sorunun matematiksel gösterimi: .
y ben ve x ben =1...n değerleri bizim tarafımızdan bilinmektedir, bunlar gözlemsel verilerdir. S fonksiyonunda bunlar sabitlerdir. Bu fonksiyondaki değişkenler - , parametrelerinin gerekli tahminleridir. 2 değişkenli bir fonksiyonun minimumunu bulmak için, bu fonksiyonun her bir parametreye göre kısmi türevlerini hesaplamak ve bunları sıfıra eşitlemek gerekir, yani. .
Sonuç olarak, 2 normallik bir sistem elde ederiz. lineer denklemler:
Bu sistemi çözerek gerekli parametre tahminlerini buluyoruz:

Regresyon denkleminin parametrelerinin hesaplanmasının doğruluğu, toplamlar karşılaştırılarak kontrol edilebilir (hesaplamaların yuvarlanması nedeniyle bazı tutarsızlıklar olabilir).
Parametre tahminlerini hesaplamak için Tablo 1'i oluşturabilirsiniz.
Regresyon katsayısı b'nin işareti ilişkinin yönünü gösterir (b > 0 ise ilişki doğrudandır, b ise ilişki doğrudandır.<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Biçimsel olarak, a parametresinin değeri, sıfıra eşit x için y'nin ortalama değeridir. İşaret faktörü sıfır değerine sahip değilse ve olamazsa, a parametresinin yukarıdaki yorumu mantıklı değildir.

Özellikler arasındaki ilişkinin sıkılığının değerlendirilmesi doğrusal çift korelasyon katsayısı kullanılarak gerçekleştirilir - r x,y . Aşağıdaki formül kullanılarak hesaplanabilir: . Ek olarak, doğrusal çift korelasyon katsayısı, regresyon katsayısı b cinsinden belirlenebilir: .
Doğrusal çift korelasyon katsayısının kabul edilebilir değerleri aralığı -1 ila +1 arasındadır. Korelasyon katsayısının işareti ilişkinin yönünü gösterir. r x, y >0 ise bağlantı doğrudandır; eğer rx, y<0, то связь обратная.
Bu katsayı, modülde birliğe yakınsa, özellikler arasındaki ilişki oldukça yakın bir doğrusal ilişki olarak yorumlanabilir. Modülü bir ê r x , y ê =1 ise, özellikler arasındaki ilişki fonksiyonel doğrusaldır. Eğer x ve y özellikleri lineer olarak bağımsızsa, o zaman r x,y 0'a yakındır.
Tablo 1, r x,y'yi hesaplamak için de kullanılabilir.

Elde edilen regresyon denkleminin kalitesini değerlendirmek için teorik belirleme katsayısı hesaplanır - R 2 yx:

,
burada d 2, regresyon denklemi tarafından açıklanan y varyansıdır;
e 2 - artık (regresyon denklemi ile açıklanamayan) varyans y ;
s 2 y - toplam (toplam) varyans y .
Belirleme katsayısı, toplam varyasyon (dağılım) y'de, regresyon (ve dolayısıyla faktör x) ile açıklanan, ortaya çıkan y özelliğinin varyasyon (dağılım) payını karakterize eder. R 2 yx belirleme katsayısı 0 ile 1 arasında değerler alır. Buna göre, 1-R 2 yx değeri, modelde dikkate alınmayan diğer faktörlerin ve spesifikasyon hatalarının etkisinin neden olduğu y varyansının oranını karakterize eder.
Eşleştirilmiş doğrusal regresyon ile R 2 yx = r 2 yx .

4.1. Yerleşik işlevleri kullanma

hesaplama regresyon katsayıları fonksiyon kullanılarak gerçekleştirilir

LINEST(Değerler_y; Değerler_x; Konst; İstatistik),

Değerler_y- y değerleri dizisi,

Değerler_x- isteğe bağlı değerler dizisi x eğer dizi X atlandığında, bunun aynı boyutta bir dizi (1;2;3;...) olduğu varsayılır. Değerler_y,

Konst- sabitin gerekli olup olmadığını gösteren bir boole değeri b 0'a eşitti. Konst anlamı var DOĞRU veya atlanmış, o zaman b olağan şekilde hesaplanır. eğer argüman Konst YANLIŞ, o zaman b 0 olduğu varsayılır ve değerler a ilişki kurulacak şekilde seçilmiştir. y=ax.

İstatistik- ek regresyon istatistiklerinin döndürülmesinin gerekip gerekmediğini gösteren bir boole değeri. eğer argüman İstatistik anlamı var DOĞRU, ardından fonksiyon LINEST ek regresyon istatistikleri döndürür. eğer argüman İstatistik anlamı var YANLIŞ veya atlanırsa, işlev LINEST sadece katsayısını döndürür a ve kalıcı b.

Unutulmamalıdır ki fonksiyonların sonucu SATIR() bir dizi değerdir - bir dizi.

Hesaplama için korelasyon katsayısı işlev kullanılır

KOREL(Dizi1;dizi2),

korelasyon katsayısının değerlerinin döndürülmesi, burada Dizi1- değerler dizisi y, dizi2- değerler dizisi x. Dizi1 ve dizi2 aynı boyutta olmalıdır.

ÖRNEK 1. Bağımlılık y(x) tabloda sunulmaktadır. İnşa etmek regresyon hattı ve hesapla korelasyon katsayısı.

MS Excel sayfasına bir değerler tablosu girelim ve bir dağılım grafiği oluşturalım. Çalışma sayfası Şekil 1'de gösterilen formu alacaktır. 2.

Regresyon katsayılarının değerlerini hesaplamak için a ve b hücreleri seç A7: B7, fonksiyon sihirbazına ve kategoriye dönelim istatistiksel bir işlev seç LINEST. Şekilde gösterildiği gibi görünen iletişim kutusunu doldurun. 3 ve basın TAMAM.

Sonuç olarak, hesaplanan değer yalnızca hücrede görünecektir. A6(Şek. 4). Bir hücrede bir değerin görünmesi için B6 düzenleme moduna girmeniz gerekir (tuş F2) ve ardından tuş kombinasyonuna basın CTRL+SHIFT+ENTER.

Hücre başına korelasyon katsayısının değerini hesaplamak için C6 aşağıdaki formül tanıtıldı:

C7=KOREL(B3:J3;B2:J2).

Regresyon katsayılarını bilmek a ve b fonksiyonun değerlerini hesapla y=balta+b verilen için x. Bunu yapmak için formülü tanıtıyoruz

B5=$A$7*B2+$B$7

ve aralığa kopyalayın С5:J5(Şek. 5).

Şemada regresyon doğrusunu çizelim. Grafikte deneysel noktaları seçin, sağ tıklayın ve komutu seçin İlk veri. Görüntülenen iletişim kutusunda (Şekil 5) sekmesini seçin Kürek çekmek ve düğmeye tıklayın Ekle. Giriş alanlarını Şekil 2'de gösterildiği gibi doldurun. 6 ve düğmesine basın TAMAM. Deneysel veri grafiğine bir regresyon çizgisi eklenecektir. Varsayılan olarak, grafiği, düzleştirme çizgileriyle bağlanmamış noktalar olarak görüntülenecektir.

Regresyon çizgisinin görünümünü değiştirmek için aşağıdaki adımları uygulayın. Çizgi grafiği gösteren noktalara sağ tıklayın, komutu seçin Grafik türü ve dağılım grafiğinin türünü Şekil 1'de gösterildiği gibi ayarlayın. 7.

Çizgi tipi, rengi ve kalınlığı aşağıdaki gibi değiştirilebilir. Diyagramdaki satırı seçin, farenin sağ tuşuna basın ve içerik menüsünde komutu seçin Veri Serisi Formatı… Ardından, örneğin Şekil 2'de gösterildiği gibi ayarları yapın. sekiz.

Tüm dönüşümlerin bir sonucu olarak, bir grafik alanında bir deneysel veri grafiği ve bir regresyon çizgisi elde ederiz (Şekil 9).

4.2. Bir eğilim çizgisi kullanma.

MS Excel'de çeşitli yaklaşık bağımlılıkların oluşturulması, bir grafik özelliği olarak uygulanır - eğilim çizgisi.

ÖRNEK 2. Deney sonucunda, bazı tablo bağımlılıkları belirlendi.

Yaklaşık bir bağımlılık seçin ve oluşturun. Tablo ve uygun analitik bağımlılıkların grafiklerini oluşturun.

Problemin çözümü şu aşamalara ayrılabilir: başlangıç ​​verilerinin girilmesi, bir dağılım grafiğinin oluşturulması ve bu çizime bir trend çizgisinin eklenmesi.

Bu süreci ayrıntılı olarak ele alalım. İlk verileri çalışma sayfasına girelim ve deneysel verileri çizelim. Ardından, grafikteki deneysel noktaları seçin, sağ tıklayın ve komutu kullanın Ekle ben eğilim çizgisi(Şek. 10).

Görüntülenen iletişim kutusu, yaklaşık bir bağımlılık oluşturmanıza olanak tanır.

Bu pencerenin ilk sekmesi (Şekil 11), yaklaşık bağımlılığın türünü gösterir.

İkincisi (Şekil 12) inşaat parametrelerini tanımlar:

yaklaşık bağımlılığın adı;

İleriye (geriye) tahmin n birimler (bu parametre, trend çizgisini uzatmak için kaç birim ileri (geri) gerektiğini belirler);

eğrinin çizgiyle kesiştiği noktanın gösterilip gösterilmeyeceği y=sabit;

yaklaşık fonksiyonun diyagramda gösterilip gösterilmeyeceği (denklemi diyagram parametresinde gösterin);

Standart sapma değerinin diyagrama yerleştirilip yerleştirilmeyeceği (yaklaşım güvenilirliğinin değerini diyagrama yerleştirecek parametre).

İkinci dereceden bir polinomu yaklaşık bağımlılık olarak seçelim (Şekil 11) ve bu polinomu grafik üzerinde açıklayan bir denklem türetelim (Şekil 12). Ortaya çıkan diyagram, Şek. on üç.

Benzer şekilde, trend çizgileri gibi bağımlılıkların parametrelerini seçebilirsiniz

doğrusal y=a∙x+b,

logaritmik y=bir ln(x)+b,

üstel y=a∙eb,

güç y=bir x b,

polinom y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d ve benzerleri, 6. dereceden polinom dahil olmak üzere,

Doğrusal filtreleme.

4.3. Seçenekler aracının analizini kullanma: Bir çözüm bulma.

Seçenek analiz aracını kullanarak en küçük kareler yöntemiyle fonksiyonel bağımlılık parametrelerinin seçiminin MS Excel'de uygulanması oldukça ilgi çekicidir: Bir çözüm arayın. Bu teknik, herhangi bir türdeki bir fonksiyonun parametrelerini seçmenize izin verir. Bu olasılığı aşağıdaki problem örneğinde ele alalım.

ÖRNEK 3. Deney sonucunda tabloda sunulan z(t) bağımlılığı

Bağımlılık katsayılarını seçin Z(t)=4'te +Bt 3 +Ct 2 +Dt+K en küçük kareler yöntemiyle

Bu problem, beş değişkenli bir fonksiyonun minimumunu bulma problemine eşdeğerdir.

Optimizasyon problemini çözme sürecini düşünün (Şekil 14).

değerler olsun ANCAK, AT, İle, D ve İle hücrelerde depolanır A7: E7. Fonksiyonun teorik değerlerini hesaplayın Z(t)=At4+Bt3+Ct2+Dt+K verilen için t(B2:J2). Bunu yapmak için hücrede B4 fonksiyonun değerini ilk noktaya girin (hücre B2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Bu formülü aralığa kopyalayın С4:J4 ve apsisleri hücrelerde saklanan noktalarda fonksiyonun beklenen değerini alın B2:J2.

hücreye B5 deneysel ve hesaplanan noktalar arasındaki farkın karesini hesaplayan bir formül sunuyoruz:

B5=(B4-B3)^2,

ve aralığa kopyalayın С5:J5. bir hücrede F7 toplam ikinci dereceden hatayı (10) saklayacağız. Bunu yapmak için formülü tanıtıyoruz:

F7 = TOPLA(B5:J5).

komutunu kullanalım. Service®Çözüm arayın ve optimizasyon problemini kısıtlamalar olmadan çözün. Şekil 2'de gösterilen iletişim kutusundaki uygun giriş alanlarını doldurun. 14 ve düğmesine basın Çalıştırmak. Bir çözüm bulunursa, Şekil 2'de gösterilen pencere. on beş.

Karar bloğunun sonucu hücrelerin çıktısı olacaktır. A7: E7parametre değerleri fonksiyonlar Z(t)=At4+Bt3+Ct2+Dt+K. hücrelerde B4:J4 alırız beklenen fonksiyon değeri başlangıç ​​noktalarında. bir hücrede F7 tutulacak toplam kare hatası.

Aralığı seçerseniz, deneysel noktaları ve takılı çizgiyi aynı grafik alanında görüntüleyebilirsiniz. B2:J4, aramak Grafik Sihirbazı ve ardından ortaya çıkan grafiklerin görünümünü biçimlendirin.

Pirinç. 17, hesaplamalar yapıldıktan sonra MS Excel çalışma sayfasını görüntüler.

En küçük kareler yöntemi, iki sayı dizisiyle en yakından eşleşen doğrusal bir denklem oluşturmaya yönelik matematiksel bir prosedürdür. Bu yöntemin amacı, toplam karesel hatayı en aza indirmektir. Excel, bu yöntemi hesaplamalarda uygulamak için kullanılabilecek araçlara sahiptir. Nasıl yapıldığını görelim.

En küçük kareler yöntemi (LSM), bir değişkenin diğerine bağımlılığının matematiksel bir açıklamasıdır. Tahmin için kullanılabilir.

Çözücü eklentisini etkinleştirin

OLS'yi Excel'de kullanmak için eklentiyi etkinleştirmeniz gerekir. "Çözüm Arayın", varsayılan olarak devre dışıdır.

şimdi fonksiyon Çözüm Bulmak Excel'de etkinleştirilir ve araçları şeritte görünür.

Sorunun koşulları

LSM uygulamasını belirli bir örnek üzerinde tanımlayalım. İki sıra numaramız var x ve y , sırası aşağıdaki resimde gösterilmektedir.

Bu bağımlılık, işlev tarafından en doğru şekilde tanımlanabilir:

Aynı zamanda bilinmektedir ki x=0 y aynı zamanda eşit 0 . Bu nedenle, bu denklem bağımlılıkla açıklanabilir. y=nx .

Farkın minimum kareler toplamını bulmalıyız.

Karar

Yöntemin doğrudan uygulamasının açıklamasına geçelim.

Görüldüğü gibi en küçük kareler yönteminin uygulanması oldukça karmaşık bir matematiksel işlemdir. En basit örnekle uygulamalı olarak gösterdik ama çok daha karmaşık durumlar var. Ancak Microsoft Excel araç takımı, hesaplamaları olabildiğince basitleştirmek için tasarlanmıştır.

Belirli bir fonksiyonun daha basit olanlarla yaklaşık temsiline izin verdiği için birçok uygulaması vardır. LSM, gözlemlerin işlenmesinde son derece yararlı olabilir ve rastgele hatalar içeren diğer ölçümlerin sonuçlarından bazı miktarları tahmin etmek için aktif olarak kullanılır. Bu makalede, Excel'de en küçük kareler hesaplamalarının nasıl uygulanacağını öğreneceksiniz.

Sorunun belirli bir örnek üzerinde ifadesi

X ve Y'nin iki göstergesi olduğunu varsayalım. Ayrıca, Y, X'e bağlıdır. OLS, regresyon analizi açısından bizi ilgilendirdiği için (Excel'de yöntemleri yerleşik işlevler kullanılarak uygulanır), hemen devam etmeliyiz. belirli bir sorunu düşünmek için.

Öyleyse, X bir bakkalın metrekare cinsinden ölçülen satış alanı ve Y, milyonlarca ruble olarak tanımlanan yıllık ciro olsun.

Bir veya daha fazla perakende alanı varsa, mağazanın ne kadar ciroya (Y) sahip olacağına dair bir tahmin yapmak gerekir. Açıkça görülüyor ki, hipermarket tezgahtan daha fazla mal sattığı için Y = f (X) fonksiyonu artıyor.

Tahmin için kullanılan ilk verilerin doğruluğu hakkında birkaç söz

Diyelim ki n mağaza için verilerle oluşturulmuş bir tablomuz var.

Matematiksel istatistiklere göre, en az 5-6 nesne üzerindeki veriler incelenirse sonuçlar az çok doğru olacaktır. Ayrıca "anormal" sonuçlar kullanılamaz. Özellikle elit bir küçük butik, “masmarket” sınıfındaki büyük mağazaların cirosundan kat kat daha fazla ciroya sahip olabilir.

Yöntemin özü

Tablo verileri Kartezyen düzlemde M 1 (x 1, y 1), ... Mn (x n, y n) noktaları olarak görüntülenebilir. Şimdi problemin çözümü, grafiği M 1, M 2, .. Mn noktalarına mümkün olduğunca yakın geçen bir y = f (x) yaklaşık fonksiyonunun seçimine indirgenecektir.

Tabii ki, yüksek dereceli bir polinom kullanabilirsiniz, ancak bu seçeneğin uygulanması sadece zor değil, aynı zamanda tespit edilmesi gereken ana eğilimi yansıtmayacağından sadece yanlıştır. En makul çözüm, deneysel verilere en iyi yaklaşan y = ax + b düz çizgisini ve daha kesin olarak katsayıları - a ve b'yi aramaktır.

doğruluk puanı

Herhangi bir yaklaşım için, doğruluğunun değerlendirilmesi özellikle önemlidir. x ben noktası için fonksiyonel ve deneysel değerler arasındaki farkı (sapma) e ile belirtin, yani. e ben = y ben - f (x i).

Açıkçası, yaklaşıklığın doğruluğunu değerlendirmek için, sapmaların toplamını kullanabilirsiniz, yani, X'in Y'ye bağımlılığının yaklaşık bir temsili için düz bir çizgi seçerken, en küçük değerine sahip olanı tercih edilmelidir. ele alınan tüm noktalarda toplam e i. Ancak, her şey o kadar basit değil, çünkü olumlu sapmaların yanı sıra pratikte olumsuz olanlar da olacak.

Sapma modüllerini veya karelerini kullanarak problemi çözebilirsiniz. İkinci yöntem en yaygın kullanılanıdır. Regresyon analizi de dahil olmak üzere birçok alanda kullanılır (Excel'de uygulaması iki yerleşik işlev kullanılarak gerçekleştirilir) ve uzun süredir etkili olduğu kanıtlanmıştır.

en küçük kareler yöntemi

Excel'de, bildiğiniz gibi, seçilen aralıkta bulunan tüm değerlerin değerlerini hesaplamanıza izin veren yerleşik bir otomatik toplam işlevi vardır. Böylece (e 1 2 + e 2 2 + e 3 2 + ... e n 2) ifadesinin değerini hesaplamamıza hiçbir şey engel olmayacaktır.

Matematiksel gösterimde, bu şöyle görünür:

Karar başlangıçta düz bir çizgi kullanılarak yaklaşık olarak verildiğinden, elimizde:

Bu nedenle, X ve Y arasındaki belirli bir ilişkiyi en iyi tanımlayan düz bir çizgi bulma görevi, iki değişkenli bir fonksiyonun minimumunu hesaplamak anlamına gelir:

Bu, yeni a ve b değişkenlerine göre sıfır kısmi türevleri eşitlemeyi ve 2 bilinmeyenli iki denklemden oluşan ilkel bir sistemi çözmeyi gerektirir:

2'ye bölme ve toplamları manipüle etme gibi basit dönüşümlerden sonra şunu elde ederiz:

Örneğin, Cramer yöntemiyle çözerek, belirli a * ve b * katsayılarına sahip durağan bir nokta elde ederiz. Bu minimumdur, yani mağazanın belirli bir alan için hangi ciroya sahip olacağını tahmin etmek için, söz konusu örnek için bir regresyon modeli olan y = a * x + b * düz çizgisi uygundur. Tabii ki, kesin sonucu bulmanıza izin vermeyecek, ancak belirli bir alan için kredili bir mağaza satın almanın işe yarayıp yaramayacağı hakkında bir fikir edinmenize yardımcı olacaktır.

Excel'de en küçük kareler yöntemi nasıl uygulanır

Excel'in en küçük karelerin değerini hesaplama işlevi vardır. Şu forma sahiptir: TREND (bilinen Y değerleri; bilinen X değerleri; yeni X değerleri; sabit). Excel'de OLS hesaplama formülünü tablomuza uygulayalım.

Bunu yapmak için, Excel'de en küçük kareler yöntemi kullanılarak yapılan hesaplama sonucunun görüntülenmesi gereken hücreye “=” işaretini girin ve “TREND” işlevini seçin. Açılan pencerede, aşağıdakileri vurgulayarak uygun alanları doldurun:

• Y için bilinen değerler aralığı (bu durumda ciro verileri);
• aralık x 1 , …x n , yani perakende alanının boyutu;
• ve cironun boyutunu bulmanız gereken x'in bilinen ve bilinmeyen değerleri (çalışma sayfasındaki konumları hakkında bilgi için aşağıya bakın).

Ayrıca formülde mantıksal bir "Const" değişkeni vardır. Karşılık gelen alana 1 girerseniz, bu, b \u003d 0 olduğu varsayılarak hesaplamaların yapılması gerektiği anlamına gelir.

Birden fazla x değeri için tahmini bilmeniz gerekiyorsa, formülü girdikten sonra "Enter" tuşuna basmamalısınız, ancak "Shift" + "Control" + "Enter" ("Enter") kombinasyonunu yazmanız gerekir. ) klavyede.

Bazı özellikler

Regresyon analizine aptallar bile erişebilir. Bilinmeyen değişkenler dizisinin değerini tahmin etmeye yönelik Excel formülü - "TREND" - en küçük kareler yöntemini hiç duymamış olanlar tarafından bile kullanılabilir. Sadece çalışmasının bazı özelliklerini bilmek yeterlidir. Özellikle:

• Y değişkeninin bilinen değerlerinin aralığını bir satır veya sütunda düzenlerseniz, bilinen x değerlerine sahip her satır (sütun) program tarafından ayrı bir değişken olarak algılanır.
• TREND penceresinde x'i bilinen aralık belirtilmemişse, fonksiyonun Excel'de kullanılması durumunda, program onu, sayısı verilen değerlere sahip aralığa karşılık gelen tam sayılardan oluşan bir dizi olarak kabul edecektir ​y değişkeninin
• "Öngörülen" değerler dizisinin çıktısını almak için trend ifadesi bir dizi formülü olarak girilmelidir.
• Yeni x değerleri belirtilmemişse, TREND işlevi bunları bilinenlere eşit kabul eder. Belirtilmezlerse, dizi 1 bağımsız değişken olarak alınır; 2; 3; 4;…, zaten verilen parametreler y ile aralıkla orantılıdır.
• Yeni x değerlerini içeren aralık, verilen y değerlerine sahip aralıkla aynı veya daha fazla satır veya sütuna sahip olmalıdır. Başka bir deyişle, bağımsız değişkenlerle orantılı olmalıdır.
• Bilinen x değerlerine sahip bir dizi birden çok değişken içerebilir. Ancak, sadece birinden bahsediyorsak, verilen x ve y değerlerine sahip aralıkların orantılı olması gerekir. Birkaç değişken olması durumunda, verilen y değerlerine sahip aralığın bir sütuna veya bir satıra sığması gerekir.

TAHMİN işlevi

Birkaç fonksiyon kullanılarak gerçekleştirilir. Bunlardan birinin adı "ÖNCELİK". TREND'e benzer, yani en küçük kareler yöntemi kullanılarak yapılan hesaplamaların sonucunu verir. Ancak, yalnızca Y değerinin bilinmediği bir X için.

Artık, bir göstergenin gelecekteki değerinin değerini doğrusal bir eğilime göre tahmin etmenize olanak tanıyan aptallar için Excel formüllerini biliyorsunuz.