Excel'de en küçük kareler yönteminin uygulanması. Excel'de en küçük kareler yöntemi

En küçük kareler yöntemi (OLS), regresyon analizi alanına aittir. Belirli bir fonksiyonun daha basit olanlarla yaklaşık temsiline izin verdiği için birçok uygulaması vardır. OLS, gözlemlerin işlenmesinde son derece yararlı olabilir ve rastgele hatalar içeren diğerlerinin ölçümlerinin sonuçlarından bazı miktarları tahmin etmek için aktif olarak kullanılır. Bu makale size Excel'de en küçük kareler hesaplamalarını nasıl uygulayacağınızı gösterecektir.

Belirli bir örnek kullanarak sorunun ifadesi

X ve Y'nin iki göstergesi olduğunu varsayalım. Ve Y, X'e bağlıdır. OLS, regresyon analizi açısından bizi ilgilendirdiğinden (Excel'de yöntemleri yerleşik işlevler kullanılarak uygulanır), o zaman hemen gitmelisiniz. belirli bir sorunu ele almak için.

O halde X, bir bakkalın perakende alanı olsun. metrekare ve Y, milyonlarca ruble olarak tanımlanan yıllık cirodur.

Belirli bir perakende alanı varsa, mağazanın ne kadar ciroya (Y) sahip olacağına dair bir tahmin yapılması 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 verilerden oluşturulmuş bir tablomuz var.

Buna göre matematiksel istatistik, en az 5-6 nesne üzerindeki veriler incelenirse sonuçlar az çok doğru olacaktır. Ayrıca, "anormal" sonuçları kullanamazsınız. Özellikle elit bir küçük butik, "masmarket" sınıfındaki büyük perakende satış mağazalarının cirosundan kat kat daha fazla ciroya sahip olabilir.

Yöntem özü

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

Tabii ki, polinomu kullanabilirsiniz. yüksek derece, 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 veya daha doğrusu katsayıları - a ve b'yi bulmaktır.

Doğruluk değerlendirmesi

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

Açıkçası, yaklaşıklığın doğruluğunu tahmin etmek için, sapmaların toplamı kullanılabilir, yani X'in Y'ye bağımlılığının yaklaşık bir temsili için düz bir çizgi seçerken, kişinin içinde bulunduğuna öncelik verilmelidir. en küçük değer ele alınan tüm noktalarda toplam e i. Bununla birlikte, her şey o kadar basit değildir, çünkü pozitif sapmalarla birlikte, pratik olarak negatif sapmalar da olacaktır.

Problem, sapma modülleri veya kareleri kullanılarak çözülebilir. Son yöntem en yaygın kullanılanıdır. Regresyon analizi de dahil olmak üzere birçok alanda kullanılır (Excel iki yerleşik işlevi uygular) ve uzun süredir değerini kanıtlamış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ızı hiçbir şey engelleyemez.

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

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

Böylece, X ve Y niceliklerinin özgül bağımlılığını en iyi tanımlayan doğruyu bulma sorunu, iki değişkenli bir fonksiyonun minimumunu hesaplamaya indirgenir:

Bu, yeni a ve b değişkenlerine göre kısmi türevlerin sıfıra eşitlenmesini ve 2 bilinmeyenli iki denklemden oluşan ilkel bir sistemin çözülmesini gerektirir:

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

Örneğin, Cramer yöntemiyle çözerek, bazı 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 bölgedeki bir mağaza için kredili 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, OLS değerini hesaplamak için bir işleve sahiptir. Aşağıdaki 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'deki en küçük kareler yöntemiyle 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 büyüklüğü;
cironun boyutunu bulmanız gereken x'in hem bilinen hem de bilinmeyen değerleri (çalışma sayfasındaki konumları hakkında bilgi için aşağıya bakın).

Ayrıca formül, "Const" Boole değişkenini içerir. İlgili alana 1 girerseniz, bu, b = 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 klavyede "Shift" + "Control" + "Enter" kombinasyonunu yazmanız gerekir. ("Girmek").

Bazı özellikler

Regresyon analiziçaydanlıklar tarafından bile erişilebilir. 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. Ç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" penceresi x ile bilinen bir aralık içermiyorsa, işlev Excel'de kullanılıyorsa, 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şkeni.
Çıktı olarak “öngörülen” değerler dizisi almak için trend ifadesinin dizi formülü olarak girilmesi gerekir.
Yeni x değerleri belirtilmemişse, TREND işlevi bunları bilinenlere eşit olarak 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ütun 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, yalnızca birinden bahsediyorsak, verilen x ve y değerlerine sahip aralıkların orantılı olması gerekir. Birden çok değişken olması durumunda, verilen y değerlerine sahip aralığın bir sütuna veya bir satıra sığmasını istersiniz.

TAHMİN işlevi

Excel'de regresyon analizi, çeşitli işlevler kullanılarak gerçekleştirilir. Bunlardan birine "ÖNCELİK" denir. "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 Excel'de, belirli bir göstergenin gelecekteki değerini doğrusal bir eğilime göre tahmin etmenize olanak tanıyan aptallar için formülleri biliyorsunuz.

Eh, işte teftişe rapor ettiler, makale evde konferans için yazıldı - şimdi bloga yazabilirsiniz. Verilerimi işlerken, Excel'de denilen çok havalı ve gerekli bir eklenti hakkında yazmadan edemeyeceğimi fark ettim. Bu yüzden makale bu özel eklentiye ayrılacak ve size bir kullanım örneği kullanarak anlatacağım. en küçük kareler yöntemi(OLS) deneysel verileri tanımlarken bilinmeyen denklem katsayılarını aramak için.

Çözüm Bul eklentisi nasıl etkinleştirilir

İlk olarak, bu eklentiyi nasıl etkinleştireceğimizi bulalım.

1. "Dosya" menüsüne gidin ve "Excel Seçenekleri"ni seçin

2. Açılan pencerede "Çözüm ara"yı seçin ve "git"e tıklayın.

3. Bir sonraki pencerede, "çözüm arayın" öğesinin önüne bir onay işareti koyun ve "Tamam"ı tıklayın.

4. Eklenti etkinleştirildi - şimdi "Veri" menü öğesinde bulunabilir.

en küçük kareler yöntemi

Şimdi kısaca hakkında en küçük kareler yöntemi (OLS) ve nerede uygulanabilir.

Diyelim ki X değerinin Y değeri üzerindeki etkisini incelediğimiz bazı deneylerden sonra bir veri setimiz var.

Bu etkiyi matematiksel olarak açıklamak istiyoruz, böylece daha sonra bu formülü kullanabiliriz ve eğer X'in değerini bu kadar değiştirirsek, Y'nin değerini falan elde ederiz.

Çok basit bir örnek alacağım (bkz. şek.).

Noktaların birbiri ardına düz bir çizgideymiş gibi yerleştirildiği açıktır ve bu nedenle bağımlılığımızın doğrusal bir y = kx + b fonksiyonu ile tanımlandığını güvenle varsayıyoruz. Aynı zamanda, X sıfıra eşit olduğunda, Y değerinin de sıfıra eşit olduğundan kesinlikle eminiz. Bu, bağımlılığı tanımlayan işlevin daha da basit olacağı anlamına gelir: y = kx (okul müfredatını hatırlayın).

Genel olarak, k katsayısını bulmalıyız. Yapacağımız şey bu OLS "çözüm ara" eklentisini kullanarak.

Yöntem, (burada - dikkat: bunun hakkında düşünmeniz gerekir) deneysel olarak elde edilen ve karşılık gelen hesaplanan değerler arasındaki farkların karelerinin toplamının minimum olması gerçeğinden oluşur. Yani, X1 = 1 gerçekte ölçülen değer Y1 = 4,6 ve hesaplanan y1 = f (x1) 4 olduğunda, farkın karesi (y1-Y1) ^ 2 = (4-4,6) ^ 2 = olacaktır. 0.36 ... Aynısı ile: X2 = 2 olduğunda, gerçekte ölçülen değer Y2 = 8.1 ve hesaplanan y2 8 olduğunda, farkın karesi (y2-Y2) ^ 2 = (8-8.1) ^ 2 = 0.01 olacaktır. . Ve tüm bu karelerin toplamı mümkün olduğunca küçük olmalıdır.

Öyleyse, OLS'yi kullanma eğitimine başlayalım ve Çözüm Excel Eklentilerini Bulun .

Çözüm arama eklentisini uygulama

1. "Çözüm ara" eklentisini açmadıysanız, o zaman şu noktaya geri dönün: Çözüm eklentisi araması nasıl etkinleştirilir ve etkinleştirilir 🙂

2. A1 hücresine "1" değerini girin. Bu birim, fonksiyonel bağımlılığımız y = kx'in (k) katsayısının gerçek değerine ilk yaklaşım olacaktır.

3. B sütununda X parametresinin değerlerine, C sütununda - Y parametresinin değerlerine sahibiz D sütununun hücrelerine formülü giriyoruz: “k katsayısı X değeri ile çarpılır ”. Örneğin, D1 hücresine "= A1 * B1", D2 hücresine "= A1 * B2" gireriz, vb.

4. k katsayısının bire eşittir ve f (x) = y = 1 * x fonksiyonu, çözümümüzün ilk yaklaşımıdır. Y'nin ölçülen değerleri ile y = 1 * x formülü ile hesaplananlar arasındaki farkların karelerinin toplamını hesaplayabiliriz. Tüm bunları, aşağıdaki formüle uygun hücre referanslarını sürerek manuel olarak yapabiliriz: "= (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... vb. yanılmışız ve çok zaman kaybettiğimizi anlıyoruz.Excel'de, farklılıkların karelerinin toplamını hesaplamak için, bizim için her şeyi yapacak özel bir "SUMKVRAZN" formülü var. A2 hücresine girin ve ilk değeri ayarlayın. veri: ölçülen Y değerleri aralığı (sütun C) ve hesaplanan Y değerleri aralığı (sütun D).

4. Karelerin farklarının toplamı hesaplandı - şimdi “Veri” sekmesine gidip “Çözüm ara” seçeneğini seçiyoruz.

5. Açılan menüde değiştirilecek hücre olarak A1 hücresini (k katsayılı olan) seçin.

6. Hedef olarak A2 hücresini seçin ve "minimum değere eşit ayarla" koşulunu ayarlayın. Bunun hesaplanan ve ölçülen değerler arasındaki farkların karelerinin toplamını hesapladığımız hücre olduğunu ve bu toplamın minimum olması gerektiğini unutmayın. "Yürüt" e tıklayın.

7. Katsayısı k seçilir. Artık hesaplanan değerlerin ölçülen değerlere çok yakın olduğunu doğrulayabilirsiniz.

not

Genel olarak, elbette, Excel'deki deneysel verilere yaklaşmak için, verileri doğrusal, üstel, kuvvet ve polinom işlevi kullanarak tanımlamanıza izin veren özel araçlar vardır, böylece genellikle n olmadan yapabilirsiniz. çözüm arama eklentileri... Madenimde tüm bu yaklaşım yöntemlerinden bahsettim, o yüzden ilgileniyorsanız bir göz atın. Ama bazı egzotik işlevler söz konusu olduğunda bir bilinmeyen katsayılı veya optimizasyon sorunları, burada üst yapıçok uygun.

Çözüm eklentisini bulun diğer görevler için kullanılabilir, asıl şey özü anlamaktır: bir değer seçtiğimiz bir hücre var ve bilinmeyen bir parametreyi seçmek için bir koşulun ayarlandığı bir hedef hücre var.
Bu kadar! Bir sonraki yazıda size tatil hakkında bir peri masalı anlatacağım, bu yüzden makaleyi kaçırmamak için,

4.1. Yerleşik işlevleri kullanma

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

LINEST(Değerler_y; X-değerleri; Konst; İstatistik),

Değerler_y- bir dizi y değeri,

X-değerleri- isteğe bağlı değerler dizisi x eğer dizi x atlanırsa, 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'a eşit olarak ayarlanır ve değerler a ilişki kurulacak şekilde seçilir. y = eksen.

İstatistik- ek regresyon istatistiklerinin döndürülüp döndürülmeyeceğ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 UZANMAK veya atlanırsa, işlev LINEST sadece katsayısını döndürür a ve sabit 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. Yapı regresyon hattı ve hesapla korelasyon katsayısı.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

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

Regresyon katsayılarının değerlerini hesaplamak için a ve B hücreleri tahsis etmek A7: B7, fonksiyon sihirbazına ve kategoriye dönün istatistiksel işlevi seç LINEST... Görüntülenen iletişim kutusunu Şekil 2'de gösterildiği gibi doldurun. 3 ve basın Tamam.

Sonuç olarak, hesaplanan değer yalnızca hücrede görünecektir. A6(şekil 4). Değerin hücrede görünmesi için B6 düzenleme moduna girmek gereklidir (tuş F2) ve ardından tuş kombinasyonuna basın CTRL + ÜST KRKT + 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 C5: J5(şek. 5).

Şemada regresyon doğrusunu çizelim. Vurgulayalım deneysel noktalar grafikte sağ tıklayın ve komutu seçin İlk veri... Görüntülenen iletişim kutusunda (Şekil 5) sekmesini seçin Sıra ve düğmeye tıklayın Eklemek... 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 eklenir. Varsayılan olarak, grafiği düzleştirme çizgileriyle bağlanmayan noktalar olarak görüntülenecektir.

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

Çizgi tipini, rengini ve kalınlığını aşağıdaki gibi değiştirebilirsiniz. Diyagramda bir çizgi seçin, sağ tıklayın ve 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 alacağız (Ş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.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Yaklaşık bir bağımlılık seçin ve oluşturun. Tablo ve seçili analitik bağımlılık grafikleri oluşturun.

Sorunun çözümü şu adımlara ayrılabilir: başlangıç verilerinin girilmesi, bir nokta grafiğinin oluşturulması ve bu çizime bir eğilim çizgisi eklenmesi.

Bu süreci ayrıntılı olarak ele alalım. Ham 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 Eklemek ben trend girişimi(ş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.

İkincisinde (Şekil 12), inşaat parametreleri belirlenir:

· Yaklaşan bağımlılığın adı;

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

Düz bir çizgi ile bir eğrinin kesişme noktasının gösterilip gösterilmeyeceği y = sabit;

· Yaklaşım fonksiyonunu diyagramda göster veya gösterme (denklemi diyagramda gösterme seçeneği);

· Standart sapma değerinin diyagrama yerleştirilip yerleştirilmeyeceği (parametreyi diyagrama yaklaşıklık güvenilirliğinin değeri olarak yerleştirin).

İkinci dereceden bir polinomu yaklaşık bağımlılık olarak seçelim (Şekil 11) ve bu polinomu açıklayan denklemi grafikte türetelim (Şekil 12). Ortaya çıkan diyagram Şekil 2'de gösterilmektedir. on üç.

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

Doğrusal y=bir ∙ x+B,

Logaritmik y=bir ∙ln(x)+B,

üstel y=bir ∙ e b,

Güç yasası y=bir ∙ x b,

Polinom y=bir ∙ x 2 +b ∙ x+C, y=bir ∙ x 3 +b ∙ x 2 +c ∙ x + d ve benzerleri, 6. derece dahil bir polinoma kadar,

· Lineer filtrasyon.

4.3. Seçenek Analiz Aracını Kullanma: Bir Çözüm Bulmak.

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ğini kullanarak ele alalım.

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

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

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

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

Optimizasyon problemini çözme sürecini ele alalım (Şekil 14).

değerler olsun A, V, İLE, D ve İLE hücrelerde depolanır A7: E7... Fonksiyonun teorik değerlerini hesaplayalım Z(T)=4 + Bt 3 + Ct 2 + Dt + K'de 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 kopyalayalım C4: 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 C5: J5... bir hücrede F7 toplam kare hatasını (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ıtlama olmadan çözün. Şekil 2'de gösterilen iletişim kutusundaki giriş alanlarını doldurun. 14 ve düğmesine basın Uygulamak... Bir çözüm bulunursa, Şekil 2'de gösterilen pencere. 15.

Karar bloğunun çalışmasının sonucu hücrelere gönderilecek A7: E7parametre değerleri fonksiyonlar Z(T)=4 + Bt 3 + Ct 2 + Dt + K'de... hücrelerde B4: J4 almak beklenen fonksiyon değeri başlangıç noktalarında. bir hücrede F7 tutulacak toplam kare hatası.

Bir aralık seçerek aynı grafik alanında deneysel noktalar ve uygun bir çizgi çizebilirsiniz. B2: J4, aramak Grafik Sihirbazı ve sonra biçimlendir görünüm elde edilen grafikler.

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

4.1. Yerleşik işlevleri kullanma

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

LINEST(Değerler_y; X-değerleri; Konst; İstatistik),

Değerler_y- bir dizi y değeri,

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

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. Yapı regresyon hattı ve hesapla korelasyon katsayısı.

y		0.5		1.5		2.5		3.5
x		2.39	2.81	3.25	3.75	4.11	4.45	4.85	5.25

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

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 C5: 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 Sıra ve düğmeye tıklayın Eklemek... 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 eklenir. Varsayılan olarak, grafiği düzleştirme çizgileriyle bağlanmayan noktalar olarak görüntülenecektir.

Pirinç. 6

Tüm dönüşümlerin bir sonucu olarak, bir grafik alanında bir deneysel veri grafiği ve bir regresyon çizgisi alacağız (Ş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.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Yaklaşık bir bağımlılık seçin ve oluşturun. Tablo ve seçili analitik bağımlılık grafikleri oluşturun.

Sorunun çözümü şu adımlara ayrılabilir: başlangıç verilerinin girilmesi, bir nokta grafiğinin oluşturulması ve bu çizime bir eğilim çizgisi eklenmesi.

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.

İkincisinde (Şekil 12), inşaat parametreleri belirlenir:

· Yaklaşan bağımlılığın adı;

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

Düz bir çizgi ile bir eğrinin kesişme noktasının gösterilip gösterilmeyeceği y = sabit;

· Yaklaşım fonksiyonunu diyagramda göster veya gösterme (denklemi diyagramda gösterme seçeneği);

· Standart sapma değerinin diyagrama yerleştirilip yerleştirilmeyeceği (parametreyi diyagrama yaklaşıklık güvenilirliğinin değeri olarak yerleştirin).

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

Doğrusal y=bir ∙ x+B,

Logaritmik y=bir ∙ln(x)+B,

üstel y=bir ∙ e b,

Güç yasası y=bir ∙ x b,

Polinom y=bir ∙ x 2 +b ∙ x+C, y=bir ∙ x 3 +b ∙ x 2 +c ∙ x + d ve benzerleri, 6. derece dahil bir polinoma kadar,

· Lineer filtrasyon.

4.3. Karar bloğu kullanma

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

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

0,66	0,9	1,17	1,47	1,7	1,74	2,08	2,63	3,12
38,9	68,8	64,4	66,5	64,95	59,36	82,6	90,63	113,5

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

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

Optimizasyon problemini çözme sürecini ele alalım (Şekil 14).

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

Bu formülü aralığa kopyalayalım C4: 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 C5: J5... bir hücrede F7 toplam kare hatasını (10) saklayacağız. Bunu yapmak için formülü tanıtıyoruz:

F7 = TOPLA (B5: J5).

Bir aralık seçerek aynı grafik alanında deneysel noktalar ve uygun bir çizgi çizebilirsiniz. B2: J4, aramak Grafik Sihirbazı ve ardından ortaya çıkan grafiklerin görünümünü biçimlendirin.

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

5. REFERANSLAR

1. Alekseev ER, Chesnokova OV, Mathcad12, MATLAB7, Maple9 paketlerinde hesaplamalı matematik problemlerinin çözümü. - NT Press, 2006. – 596s. : hasta. - (Eğitici)

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

3. Berezin IS, Zhidkov NP, Hesaplama yöntemleri.-Moskova: Nauka, 1966.-632s.

4. Garnaev A.Yu., MS EXCEL ve VBA'yı ekonomi ve finansta kullanma. - SPb.: BHV - Petersburg, 1999. – 332s.

5. Demidovich BP, Maron I A., Shuvalova VZ, Sayısal analiz yöntemleri –M.: Nauka, 1967. – 368p.

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. Metodik talimatlar MS EXCEL'de laboratuvar çalışmasına. Tüm uzmanlık öğrencileri için. Donetsk, DonNTU, 2004.112 s.

En küçük kareler yöntemi, oluşturmak için matematiksel bir prosedürdür. Doğrusal Denklem ki bu, iki sıralı sayı kümesiyle en yakından eşleşir. Bu yöntemin amacı, toplam karesel hatayı en aza indirmektir. Excel'de uygulamak için kullanabileceğiniz araçlar vardır Bu method hesaplarken. Bunun nasıl yapıldığını görelim.

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

Çözücü eklentisini etkinleştirme

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

şimdi fonksiyon bir çözüm bulma Excel'de etkinleştirildi ve araçları şeritte göründü.

Sorunun koşulları

OLS'nin uygulamasını belirli bir örnekle açıklayalım. İki sıra numaramız var x ve y , sırası aşağıdaki resimde gösterilmektedir.

İşlev, bu bağımlılığı en doğru şekilde tanımlayabilir:

Ayrıca bilinmektedir ki, bunun için x = 0 y aynı zamanda eşittir 0 ... Böyle verilen denklem bağımlılık olarak tanımlanabilir y = nx .

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

Çözüm

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

Görüldüğü gibi en küçük kareler yönteminin uygulanması oldukça karmaşık bir matematiksel işlemdir. En basit örneği kullanarak eylemde gösterdik ve çok daha fazlası var zor vakalar... Bununla birlikte, araç takımı Microsoft Excel gerçekleştirilen hesaplamaları mümkün olduğunca basitleştirmek için tasarlanmıştır.