Yöntem en küçük kareler(LSM), incelenen verilerden seçilen fonksiyonun karesel sapmalarının toplamını en aza indirmeye dayanır. Bu makalede, doğrusal bir işlev kullanarak mevcut verileri yaklaşık olarak hesaplıyoruzy = A X + B .

en küçük kareler yöntemi(İngilizce) Sıradan En az kareler , OLS) bilinmeyen parametreleri tahmin etmesi açısından regresyon analizinin temel yöntemlerinden biridir. regresyon modelleriÖrnek verilere göre.

Yalnızca bir değişkene bağlı olarak işlevlere göre yaklaşımı göz önünde bulundurun:

• Doğrusal: y=ax+b (bu makale)
• : y=a*Ln(x)+b
• : y=a*xm
• : y=a*EXP(b*x)+c
• : y=ax 2 +bx+c

Not: Bu makalede 3. dereceden 6. dereceye kadar bir polinomla yaklaşım durumları ele alınmaktadır. Burada bir trigonometrik polinom ile yaklaşım ele alınmaktadır.

doğrusal bağımlılık

2 değişkenin ilişkisi ile ilgileniyoruz X Ve y. bir varsayım var y bağlıdır X doğrusal yasaya göre y = balta + B. Araştırmacı, bu ilişkinin parametrelerini belirlemek için gözlemler yaptı: x i'nin her değeri için, y i ölçümü yapıldı (örnek dosyaya bakın). Buna göre 20 çift değer olsun (х i ; y i).

Not: adım adım değişirse X sabittir, o zaman inşa etmek dağılım grafikleri kullanılabilir, değilse, o zaman grafik tipini kullanmanız gerekir noktalı .

Diyagramdan değişkenler arasındaki ilişkinin doğrusala yakın olduğu açıktır. Birçok düz çizgiden hangisinin değişkenler arasındaki ilişkiyi en "doğru" tanımladığını anlamak için, çizgilerin karşılaştırılacağı kriteri belirlemek gerekir.

Böyle bir kriter olarak şu ifadeyi kullanırız:

Nerede ŷ Ben = A * x ben + B ; n – değer çifti sayısı (bizim durumumuzda n=20)

Yukarıdaki ifade, gözlemlenen y i ve ŷ i değerleri arasındaki mesafelerin karelerinin toplamıdır ve genellikle SSE ( toplam ile ilgili kare Hatalar (artıklar), karesel hataların toplamı (artıklar)) .

en küçük kareler yöntemi böyle bir çizgi seçmektir ŷ = balta + B, bunun için yukarıdaki ifade minimum değeri alır.

Not:İki boyutlu uzaydaki herhangi bir çizgi, 2 parametrenin değerleri ile benzersiz bir şekilde belirlenir: A (eğim) ve B (vardiya).

Kare mesafelerin toplamı ne kadar küçük olursa, karşılık gelen çizginin mevcut verilere o kadar iyi yaklaştığına ve ayrıca x değişkeninden y değerlerini tahmin etmek için kullanılabileceğine inanılmaktadır. Gerçekte değişkenler arasında bir ilişki olmasa veya ilişki doğrusal olmasa bile, en küçük kareler yine de "en iyi" doğruyu seçecektir. Bu nedenle, LSM değişkenlerin gerçek bir ilişkisinin varlığı hakkında hiçbir şey söylemez, yöntem sadece bu tür fonksiyon parametrelerini seçmenize izin verir. A Ve B , bunun için yukarıdaki ifade minimumdur.

Çok karmaşık olmayan matematiksel işlemler yaptıktan sonra (daha fazla ayrıntı için bkz.), parametreleri hesaplayabilirsiniz. A Ve B :

Formülden de görülebileceği gibi, parametre A kovaryans oranıdır ve MS EXCEL'de parametreyi hesaplamak için A Aşağıdaki formülleri kullanabilirsiniz (bkz. örnek dosya sayfası Doğrusal):

= COVAR(B26:B45;C26:C45)/ VAR.G(B26:B45) veya

= KOVARYASYON.B(B26:B45;C26:C45)/VAR.B(B26:B45)

Ayrıca parametreyi hesaplamak için A = formülünü kullanabilirsiniz EĞİM(C26:C45;B26:B45). parametre için B formülü kullan = KESME(C26:C45;B26:B45) .

Son olarak, LINEST() işlevi, her iki parametreyi aynı anda hesaplamanıza izin verir. Formül girmek için DOT(C26:C45;B26:B45) arka arkaya 2 hücre seçin ve tuşuna basın CTRL + VARDİYA + GİRMEK(hakkındaki makaleye bakın). Sol hücre değeri döndürür A , Sağdaki B .

Not: Girdi ile uğraşmamak için dizi formülleri ayrıca INDEX() işlevini kullanmanız gerekecektir. formül = DİZİN(DOT(C26:C45,B26:B45),1) veya sadece = DOT(C26:C45;B26:B45)çizginin eğiminden sorumlu parametreyi döndürür, yani A . formül = DİZİN(DOT(C26:C45,B26:B45),2)çizginin Y ekseni ile kesişmesinden sorumlu olan parametreyi döndürür, yani. B .

Parametreleri hesapladıktan sonra, dağılım grafiğiçizgi çekilebilir.

En küçük kareler yöntemini kullanarak düz bir çizgi çizmenin başka bir yolu da grafik aracıdır. trend çizgisi. Bunu yapmak için diyagramı seçin, menüden seçin Düzen sekmesi, V grup Analizi tıklamak trend çizgisi, Daha sonra Doğrusal yaklaşım .

Diyalog kutusundaki "denklemi diyagramda göster" kutusunu işaretleyerek, yukarıda bulunan parametrelerin diyagramdaki değerlerle eşleştiğinden emin olabilirsiniz.

Not: Parametrelerin eşleşmesi için grafik türü olmalıdır. Gerçek şu ki, bir diyagram oluştururken Takvim x ekseni değerleri kullanıcı tarafından ayarlanamaz (kullanıcı yalnızca noktaların konumunu etkilemeyen etiketleri belirtebilir). X değerleri yerine 1 dizisi kullanılır; 2; 3; … (kategori numaralandırma için). Bu nedenle, eğer bina trend çizgisi tip şemasında Takvim, o zaman X'in gerçek değerleri yerine bu dizinin değerleri kullanılacaktır, bu da yanlış bir sonuca yol açacaktır (tabii ki X'in gerçek değerleri 1; 2 dizisiyle eşleşmediği sürece) 3; ...).

Yaklaşık gösterime izin verdiği için birçok kullanımı vardır. verilen işlev diğerleri daha basit. LSM, gözlemlerin işlenmesinde son derece yararlı olabilir ve rastgele hatalar içeren diğerlerinin ölçüm sonuçlarından bazı miktarları tahmin etmek için aktif olarak kullanılır. Bu yazıda, Excel'de en küçük kareler hesaplamalarını nasıl uygulayacağınızı öğreneceksiniz.

Belirli bir örnek üzerinde sorunun ifadesi

Diyelim ki X ve Y olmak üzere iki gösterge var. Ayrıca 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), hemen devam etmeliyiz Belirli bir sorunu ele almak için.

O halde X, bir bakkalın satış alanı olsun, ölçülen metrekare ve Y, milyonlarca ruble olarak tanımlanan yıllık cirodur.

Bir veya daha fazla perakende alanı varsa, mağazanın ne kadar ciroya (Y) sahip olacağına dair bir tahmin yapılması gerekir. Açıkçası, 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ç kelime

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

Buna göre matematiksel istatistik, en az 5-6 nesne üzerindeki veriler incelenirse sonuçlar aşağı yukarı 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), ... 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 grafiği olan yaklaşık bir y = f (x) fonksiyonunun seçimine indirgenecektir.

Tabii ki, polinomu kullanabilirsiniz yüksek derece, ancak bu seçeneğin uygulanması yalnızca zor değil, aynı zamanda tespit edilmesi gereken ana eğilimi yansıtmayacağı için 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 i noktası için fonksiyonel ve deneysel değerler arasındaki farkı (sapma) e ben , yani. e ben = y ben - f (x ben).

Açıkçası, yaklaşımı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ğere sahip olanı tercih edilmelidir. dikkate 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 sorunu çözebilirsiniz. İkinci yöntem en yaygın olarak kullanılan yöntemdir. Regresyon analizi (Excel'de uygulaması iki yerleşik işlev kullanılarak gerçekleştirilir) dahil olmak üzere birçok alanda kullanılır ve etkili olduğu uzun süredir kanıtlanmıştır.

en küçük kareler yöntemi

Bildiğiniz gibi Excel'de, 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, 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 değişkenler a ve b'ye göre sıfır kısmi türevlere eşitlemeyi ve formun 2 bilinmeyenli iki denkleminden oluşan ilkel bir sistemi çözmeyi gerektirir:

2'ye bölme ve toplamları değiştirme dahil olmak üzere basit dönüşümlerden sonra şunu elde ederiz:

Örneğin, Cramer'in yöntemiyle çözerek, belirli katsayılara sahip durağan bir nokta elde ederiz a * ve b * . Bu minimumdur, yani mağazanın belirli bir alanla ne kadar ciro yapacağını tahmin etmek için y = a * x + b * düz çizgisi uygundur, ki bu Regresyon modeli söz konusu örnek için. Elbette kesin sonucu bulmanıza izin vermeyecek, ancak belirli bir alan için krediyle mağaza satın almanın karşılığını alıp almayacağı konusunda fikir edinmenize yardımcı olacaktır.

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

Excel, en küçük karelerin değerini hesaplamak için bir işleve sahiptir. Aşağıdaki biçime sahiptir: TREND (bilinen Y değerleri; bilinen X değerleri; yeni X değerleri; sabit). Excel'de OLS'yi hesaplamak için formülü 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ığı (içinde bu durum ticaret ciro verileri);
• x 1 , …x n aralığı, yani perakende satış alanının boyutu;
• ve cironun büyüklüğünü 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 "Const" mantıksal değişkeni vardır. Buna 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 tahmin 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 analizi, aptallar için bile erişilebilir olabilir. Bir dizi bilinmeyen değişkenin değerini tahmin etmek için kullanılan Excel formülü - "EĞİLİM" - en küçük kareler yöntemini hiç duymamış olanlar tarafından bile kullanılabilir. İşinin bazı özelliklerini bilmek yeterlidir. Özellikle:

• Y değişkeninin bilinen değerler aralığını bir satıra veya sütuna yerleştirirseniz, x'in bilinen değerlerine sahip her satır (sütun) program tarafından ayrı bir değişken olarak algılanacaktır.
• TREND penceresinde bilinen x aralığı belirtilmemişse, Excel'de işlevin kullanılması durumunda, program onu, sayısı verilen değerlerle aralığa karşılık gelen tamsayılardan oluşan bir dizi olarak kabul edecektir. y değişkeninin
• Bir "öngörülen" değerler dizisinin çıktısını almak için, trend ifadesinin bir dizi formülü olarak girilmesi gerekir.
• Yeni x değerleri belirtilmezse, TREND işlevi bunları bilinenlere eşit olarak kabul eder. Belirtilmemişlerse, dizi 1 bağımsız değişken olarak alınır; 2; 3; 4;…, zaten aralıkla orantılıdır verilen parametreler y.
• 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, x ve y'nin verilen 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

Çeşitli işlevler kullanılarak gerçekleştirilir. Bunlardan birine "TAHMİN" denir. TREND'e benzer, yani en küçük kareler yöntemini kullanarak hesaplamaların sonucunu verir. Ancak, yalnızca Y'nin değeri bilinmeyen bir X için.

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

En küçük kareler yöntemi (LSM)

sistem m lineer denklemler n bilinmeyenli şu şekildedir:

Üç durum mümkündür: m N. Önceki paragraflarda m=n durumu ele alınmıştı. Biçim

Eğer m>n ve sistem tutarlıysa, A matrisi en az m - n lineer bağımlı satıra sahiptir. Burada çözüm, herhangi bir lineer bağımsız denklem (varsa) n seçilerek ve X=A-1 CV formülünü uygulayarak, yani problemi önceden çözülmüş olana indirgeyerek elde edilebilir. Bu durumda, ortaya çıkan çözüm her zaman kalan m - n denklemlerini sağlayacaktır.

Bununla birlikte, bilgisayar kullanırken daha genel bir yaklaşım olan en küçük kareler yöntemini kullanmak daha uygundur.

Cebirsel En Küçük Kareler

Cebirsel en küçük kareler yöntemi, doğrusal denklem sistemlerini çözmek için bir yöntem olarak anlaşılmaktadır.

Öklid normunu en aza indirerek

Balta mı? B? > bilgi . (1.2)

Deneysel Veri Analizi

Bazı deneyleri ele alalım;

örneğin, sıcaklık Q(t) ölçülür. Ölçüm sonuçlarının bir dizi ile verilmesine izin verin

Deneyin koşullarının, ölçümlerin bilinen bir hatayla gerçekleştirildiğini varsayalım. Bu durumlarda, sıcaklık değişimi yasası Q(t) bazı polinomlar kullanılarak aranır.

P(t) = + + + ... +,

E(, ...,) değerinin eşitlikle tanımlandığı düşüncelerden bilinmeyen katsayıların belirlenmesi, ...

gauss cebirsel exel yaklaşımı

minimum değeri aldı. Kareler toplamı minimize edildiğinden bu yönteme veriye uyan en küçük kareler denir.

P(t)'yi ifadesiyle değiştirirsek, şunu elde ederiz:

Bir dizi tanımlama görevini, değeri minimum olacak şekilde ayarlayalım, yani. en küçük kareler yöntemini kullanarak bir dizi tanımlayın. Bunu yapmak için kısmi türevleri sıfıra eşitliyoruz:

m×n matrisini girerseniz A = (), i = 1, 2..., m; j = 1, 2, ..., n, burada

ben = 1, 2..., m; j = 1, 2, ..., n,

daha sonra yazılı eşitlik şeklini alır

Yazılı eşitliği matrislerle işlemler cinsinden yeniden yazalım. Tanım olarak, bir matrisin bir sütunla çarpımına sahibiz.

Devrik bir matris için benzer bir ilişki şuna benzer:

Aşağıdaki gösterimi sunuyoruz: Ax vektörünün i - inci bileşenini göstereceğiz Yazılı matris eşitliklerine göre,

Matris formunda, bu eşitlik şu şekilde yeniden yazılabilir:

AT x=A T B (1.3)

Burada A dikdörtgensel bir m×n matrisidir. Ayrıca, veri yaklaşımı problemlerinde, kural olarak, m > n. Denklem (1.3) normal denklem olarak adlandırılır.

Öklid vektör normunu kullanarak sorunu eşdeğer bir matris biçiminde yazmak en başından beri mümkündü:

Amacımız bu fonksiyonu x'te minimize etmektir. Çözüm noktasında bir minimuma ulaşabilmek için bu noktadaki x'e göre birinci türevlerin sıfıra eşit olması gerekir. Bu fonksiyonun türevleri

2A T B + 2A T Balta

ve bu nedenle çözüm, doğrusal denklem sistemini karşılamalıdır

(A T A)x = (A T B).

Bu denklemlere normal denklemler denir. A bir m × n matris ise, o zaman A>A - n × n bir matristir, yani normal denklem matrisi her zaman bir kare simetrik matristir. Ayrıca, (A>Ax, x) = (Ax, Ax) ? 0.

Yorum. Bazen (1.3) formundaki bir denklemin çözümü, Ax = B sisteminin çözümü olarak adlandırılır, burada A, en küçük kareler yöntemiyle dikdörtgensel bir m × n (m > n) matrisidir.

En küçük kareler problemi, veri noktalarından model eğrisine olan dikey mesafelerin en aza indirilmesi olarak grafiksel olarak yorumlanabilir (bkz. Şekil 1.1). Bu fikir, tüm yaklaşım hatalarının gözlemsel hatalara karşılık geldiği varsayımına dayanmaktadır. Açıklayıcı değişkenlerde de hatalar varsa, veriden modele olan Öklid mesafesini en aza indirmek daha uygun olabilir.

Excel'de OLS

Aşağıdaki Excel'de OLS'yi uygulamaya yönelik algoritma, tüm ilk verilerin zaten bilindiğini varsayar. Sistemin AЧX=B matris denkleminin her iki parçasını soldan sistemin А Т transpoze matrisiyle çarpıyoruz:

A T AX \u003d A T B

Sonra soldaki denklemin her iki tarafını da (A T A) -1 matrisiyle çarpıyoruz. Bu matris varsa, sistem tanımlanır. gerçeğini dikkate alarak

(A T A) -1 * (A T A) \u003d E, alıyoruz

X \u003d (A T A) -1 A T B.

Ortaya çıkan matris denklemi, m > n için n bilinmeyenli m doğrusal denklem sisteminin bir çözümüdür.

Yukarıdaki algoritmanın uygulanmasını düşünün özel örnek.

Örnek. Sistemi çözmek için gerekli olsun

Excel'de, bu sorun için formül görüntüleme modundaki çözüm sayfası şöyle görünür:

Hesaplama sonuçları:

İstenen X vektörü, E11:E12 aralığında bulunur.

Belirli bir doğrusal denklem sistemini çözerken, aşağıdaki işlevler kullanıldı:

1. DAKİKA - Bir dizide saklanan bir matrisin tersini döndürür.

Sözdizimi: NBR(dizi).

Dizi, eşit sayıda satır ve sütun içeren sayısal bir dizidir.

2. MULTIP - matrislerin çarpımını döndürür (matrisler dizilerde saklanır). Sonuç, dizi1 ile aynı sayıda satıra ve dizi2 ile aynı sayıda sütuna sahip bir dizidir.

Sözdizimi: MULT(dizi1, dizi2).

Dizi1, dizi2 -- çoğaltılmış diziler.

Dizi aralığının sol üst hücresine işlevi girdikten sonra, formülü içeren hücreden başlayarak diziyi seçin, F2 tuşuna basın ve ardından CTRL+SHIFT+ENTER tuşlarına basın.

3. TRANSPOSE - dikey bir hücre kümesini yatay bir hücre grubuna veya tam tersine dönüştürür. Bu işlevi kullanmanın sonucu, orijinal dizideki sütun sayısına eşit satır sayısına ve ilk dizideki satır sayısına eşit sütun sayısına sahip bir dizidir.

4.1. Yerleşik işlevleri kullanma

hesaplama regresyon katsayıları fonksiyonu kullanılarak gerçekleştirilen

DOT(Değerler_y; Değerler_x; konst; İstatistik),

Değerler_y- y değerleri dizisi,

Değerler_x- isteğe bağlı değer dizisi X eğer dizi X atlanırsa, 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 ihmal edildiyse, o zaman B olağan şekilde hesaplanır. eğer argüman konst YANLIŞ ise, o zaman B 0 olduğu varsayılır ve değerler A ilişki kurulacak şekilde seçilir y=balta

İstatistik- ek regresyon istatistiklerinin döndürülmesi gerekip gerekmediğini gösteren bir boole değeri. eğer argüman İstatistik anlamı var DOĞRU, ardından işlev DOT ek regresyon istatistikleri döndürür. eğer argüman İstatistik anlamı var YALAN veya atlandıysa, işlev DOT yalnızca katsayıyı döndürür A ve kalıcı B.

Unutulmamalıdır ki, fonksiyonların sonucu DOT() 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ğerlerini döndürmek, burada dizi1- değer dizisi y, dizi2- değer 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 dönelim ve kategoride istatistiksel bir işlev seçin DOT. Şekil 1'de 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 değerin hücrede görünmesi için B6 düzenleme moduna girmeniz gerekir (tuş F2) ve ardından tuş bileşimine 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 getirildi:

C7=KORREL(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).

Diyagramda regresyon doğrusunu çizelim. Grafikteki deneysel noktaları seçin, sağ tıklayın ve komutu seçin İlk veri. Görünen iletişim kutusunda (Şek. 5), sekmeyi seçin Sıra ve düğmeye tıklayın Eklemek. Giriş alanlarını Şekil 1'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 birbirine bağlı olmayan 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 tipi ve Şekil 1'de gösterildiği gibi dağılım grafiğinin türünü ayarlayın. 7.

Çizgi tipi, rengi ve kalınlığı aşağıdaki gibi değiştirilebilir. Diyagramdaki çizgiyi seçin, farenin sağ düğmesine basın ve içerik menüsünden komutu seçin Veri Serisi Biçimi… Ardından, örneğin Şekil 1'de gösterildiği gibi ayarları yapın. 8.

Tüm dönüşümlerin bir sonucu olarak, bir grafik alanda deneysel verilerin bir grafiğini ve bir regresyon çizgisini elde ederiz (Şekil 9).

4.2. Trend çizgisi kullanma.

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

ÖRNEK 2. Deney sonucunda, bir miktar tablo bağımlılığı belirlendi.

Yaklaşan 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, dağılım grafiğinin oluşturulması ve bu grafiğe 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. Eklemek ben trend çizgisi(Şek. 10).

Görünen 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) yapım parametrelerini tanımlar:

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

İleri (geri) tahmin açık N birimler (bu parametre, trend çizgisini uzatmak için kaç ileri (geri) birimin gerekli olduğunu belirler);

eğrinin çizgi ile kesişme noktasını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 (parametre, diyagrama yaklaşım güvenilirliğinin değerini koyar).

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

Benzer şekilde, ile 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 vb., 6. derece polinomuna kadar ve dahil,

Doğrusal filtreleme.

4.3. Seçenekler aracının analizinin kullanılması: Bir çözüm bulmak.

Seçenek analiz aracı kullanılarak en küçük kareler yöntemiyle fonksiyonel bağımlılık parametrelerinin seçiminin MS Excel'de uygulanması büyük ilgi çekicidir: Bir çözüm arayın. Bu teknik, herhangi bir türden bir fonksiyonun parametrelerini seçmenize izin verir. Aşağıdaki problem örneğinde bu olasılığı 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)=At 4 +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ğerlere izin ver A, İÇİNDE, İLE, D Ve İLE hücrelerde saklanan A7:E7. Fonksiyonun teorik değerlerini hesaplayın Z(T)=at4+Bt3+Ct2+Dt+K verilen için T(B2:J2). Bunun için hücrede B4 fonksiyonun değerini ilk noktada 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 C4:J4 ve apsisleri hücrelerde depolanan noktalarda fonksiyonun beklenen değerini elde edin 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 ikinci dereceden hatayı (10) saklayacağız. Bunu yapmak için formülü sunuyoruz:

F7 = TOPLA(B5:J5).

komutunu kullanalım Service®Bir çözüm arayın ve optimizasyon problemini kısıtlamalar olmadan çözün. Şekil 1'de gösterilen iletişim kutusundaki uygun giriş alanlarını doldurun. 14 ve düğmesine basın Koşmak. Bir çözüm bulunursa, Şekil 1'de gösterilen pencere açılır. 15.

Karar bloğunun sonucu hücrelere çıktı 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 yerleştirilmiş çizgiyi aynı grafik alanında görüntüleyebilirsiniz. B2:J4, Arama Grafik Sihirbazı ve ardından ortaya çıkan grafiklerin görünümünü biçimlendirin.

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