Katsayıların en küçük kareler belirleme yöntemi. Deneysel verilerin yaklaşıklığı

Masa 4

x n

y n

Masa 5

ben

0,705

0,495

0,426

0,357

0,368

0,406

0,549

0,768

Tablo 6

№0

x

0,1

0,2

0,3

0,4

0,5

0,6

0,7

0,8

y

3,030

3,142

3,358

3,463

3,772

3,251

3,170

3,665

№ 1

3,314

3,278

3,262

3,292

3,332

3,397

3,487

3,563

№ 2

1,045

1,162

1,264

1,172

1,070

0,898

0,656

0,344

№ 3

6,715

6,735

6,750

6,741

6,645

6,639

6,647

6,612

№ 4

2,325

2,515

2,638

2,700

2,696

2,626

2,491

2,291

№ 5

1.752

1,762

1,777

1,797

1,821

1,850

1,884

1,944

№ 6

1,924

1,710

1,525

1,370

1,264

1,190

1,148

1,127

№ 7

1,025

1,144

1,336

1,419

1,479

1,530

1,568

1,248

№ 8

5,785

5,685

5,605

5,545

5,505

5,480

5,495

5,510

№ 9

4,052

4,092

4,152

4,234

4,338

4,468

4,599

Örnek.

Değişkenlerin değerlerine ilişkin deneysel veriler X ve de tabloda verilmektedir.

Hizalamalarının bir sonucu olarak, fonksiyon

kullanma en küçük kareler yöntemi, bu verilere doğrusal bir bağımlılıkla yaklaşın y=ax+b(parametreleri bul a ve b). İki çizgiden hangisinin daha iyi olduğunu bulun (en küçük kareler yöntemi anlamında) deneysel verileri hizalar. Çizim yapmak.

En küçük kareler yönteminin özü (LSM).

Problem, iki değişkenli fonksiyonun hangi lineer bağımlılık katsayılarını bulmaktır. a ve b en küçük değeri alır. Yani verilen veriler a ve b deneysel verilerin bulunan düz çizgiden sapmalarının karelerinin toplamı en küçük olacaktır. En küçük kareler yönteminin bütün noktası budur.

Böylece, örneğin çözümü, iki değişkenli bir fonksiyonun ekstremumunu bulmaya indirgenir.

Katsayıları bulmak için formüllerin türetilmesi.

İki bilinmeyenli iki denklem sistemi derlenir ve çözülür. Fonksiyonların kısmi türevlerini bulma değişkenlere göre a ve b, bu türevleri sıfıra eşitliyoruz.

Ortaya çıkan denklem sistemini herhangi bir yöntemle çözeriz (örneğin ikame yöntemi veya Cramer yöntemi) ve en küçük kareler yöntemini (LSM) kullanarak katsayıları bulmak için formüller elde edin.

verilerle a ve b işlev en küçük değeri alır. Bu gerçeğin kanıtı verilir sayfanın sonundaki metnin altında.

En küçük kareler yönteminin tamamı budur. Parametreyi bulmak için formül a toplamları ,, ve parametreyi içerir n- deneysel veri miktarı. Bu toplamların değerlerinin ayrı ayrı hesaplanması önerilir. katsayı b hesaplamadan sonra bulundu a.

Orijinal örneği hatırlamanın zamanı geldi.

Çözüm.

Örneğimizde n=5. Gerekli katsayıların formüllerinde yer alan tutarların hesaplanmasında kolaylık olması için tabloyu dolduruyoruz.

Tablonun dördüncü satırındaki değerler, her sayı için 2. satırın değerleri ile 3. satırın değerlerinin çarpılmasıyla elde edilir. i.

Tablonun beşinci satırındaki değerler, her bir sayı için 2. sıradaki değerlerin karesi alınarak elde edilir. i.

Tablonun son sütununun değerleri, satırlar arasındaki değerlerin toplamıdır.

Katsayıları bulmak için en küçük kareler yönteminin formüllerini kullanırız. a ve b. Tablonun son sütunundaki karşılık gelen değerleri içlerinde değiştiriyoruz:

Sonuç olarak, y=0.165x+2.184 istenen yaklaşık düz çizgidir.

Hangi satırları bulmak için kalır y=0.165x+2.184 veya orijinal verilere daha iyi yaklaşır, yani en küçük kareler yöntemini kullanarak bir tahminde bulunur.

En küçük kareler yönteminin hatasının tahmini.

Bunu yapmak için, orijinal verilerin bu satırlardan kare sapmalarının toplamlarını hesaplamanız gerekir. ve , daha küçük bir değer, en küçük kareler yöntemi açısından orijinal verilere daha iyi yaklaşan bir çizgiye karşılık gelir.

O zamandan beri, hat y=0.165x+2.184 orijinal verilere daha iyi yaklaşır.

En küçük kareler yönteminin (LSM) grafik gösterimi.

Grafiklerde her şey harika görünüyor. Kırmızı çizgi bulunan çizgidir y=0.165x+2.184, mavi çizgi , pembe noktalar orijinal verilerdir.

Uygulamada, çeşitli süreçleri modellerken - özellikle ekonomik, fiziksel, teknik, sosyal - fonksiyonların yaklaşık değerlerini bazı sabit noktalarda bilinen değerlerinden hesaplamanın bir veya başka bir yöntemi yaygın olarak kullanılmaktadır.

Bu tür fonksiyonların yaklaşım sorunları sıklıkla ortaya çıkar:

deney sonucunda elde edilen tablo verilerine göre incelenen sürecin karakteristik miktarlarının değerlerini hesaplamak için yaklaşık formüller oluştururken;

sayısal entegrasyon, türev alma, diferansiyel denklemleri çözme vb.;

dikkate alınan aralığın ara noktalarında fonksiyonların değerlerini hesaplamak gerekirse;

özellikle tahmin yaparken, dikkate alınan aralığın dışındaki sürecin karakteristik miktarlarının değerlerini belirlerken.

Bir tablo tarafından belirtilen belirli bir süreci modellemek için, en küçük kareler yöntemine dayalı olarak bu süreci yaklaşık olarak açıklayan bir fonksiyon oluşturulursa, buna yaklaşıklık fonksiyonu (regresyon) adı verilir ve yaklaşık fonksiyonların oluşturulması görevinin kendisi olacaktır. bir yaklaşım problemi olsun.

Bu makale, MS Excel paketinin bu tür sorunları çözme olanaklarını tartışır, ayrıca, tablo halinde verilen fonksiyonlar için (regresyon analizinin temeli olan) regresyonları oluşturmak (oluşturmak) için yöntem ve teknikler verilir.

Excel'de regresyon oluşturmak için iki seçenek vardır.

İncelenen süreç özelliği için bir veri tablosu temelinde oluşturulmuş bir grafiğe seçilen regresyonların (eğilim çizgileri) eklenmesi (yalnızca bir grafik oluşturulmuşsa kullanılabilir);

Doğrudan kaynak veri tablosundan regresyonlar (eğilim çizgileri) almanıza olanak tanıyan Excel çalışma sayfasının yerleşik istatistiksel işlevlerini kullanma.

Bir Grafiğe Trend Çizgileri Ekleme

Belirli bir süreci açıklayan ve bir diyagramla temsil edilen bir veri tablosu için Excel, şunları yapmanızı sağlayan etkili bir regresyon analizi aracına sahiptir:

en küçük kareler yöntemi temelinde inşa edin ve diyagrama, incelenen süreci değişen doğruluk dereceleriyle modelleyen beş tür regresyon ekleyin;

diyagrama yapılandırılmış regresyonun bir denklemini ekleyin;

grafikte görüntülenen verilerle seçilen regresyonun uyum derecesini belirleyin.

Excel, grafik verilerine dayanarak, denklem tarafından verilen doğrusal, polinom, logaritmik, üstel, üstel regresyon türlerini elde etmenizi sağlar:

y = y(x)

burada x, genellikle bir dizi doğal sayının (1; 2; 3; ...) değerlerini alan ve örneğin incelenen sürecin zamanının geri sayımını üreten bağımsız bir değişkendir (özellikler) .

1 . Doğrusal regresyon, sabit bir oranda artan veya azalan özellikleri modellemede iyidir. Bu, incelenen sürecin en basit modelidir. Aşağıdaki denkleme göre inşa edilmiştir:

y=mx+b

burada m, x eksenine doğrusal regresyonun eğiminin tanjantıdır; b - doğrusal regresyonun y ekseni ile kesişme noktasının koordinatı.

2 . Bir polinom eğilim çizgisi, birkaç farklı uç noktaya (yüksekler ve alçaklar) sahip olan özellikleri tanımlamak için kullanışlıdır. Polinom derecesinin seçimi, incelenen özelliğin ekstrema sayısı ile belirlenir. Bu nedenle, ikinci dereceden bir polinom, yalnızca bir maksimum veya minimuma sahip bir süreci iyi tanımlayabilir; üçüncü dereceden polinom - en fazla iki ekstrem; dördüncü dereceden polinom - en fazla üç ekstrema, vb.

Bu durumda, trend çizgisi aşağıdaki denkleme göre oluşturulur:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

burada c0, c1, c2,... c6 katsayıları, değerleri inşaat sırasında belirlenen sabitlerdir.

3 . Logaritmik trend çizgisi, değerleri ilk başta hızla değişen ve ardından yavaş yavaş stabilize olan özelliklerin modellenmesinde başarıyla kullanılmaktadır.

y = c ln(x) + b

4 . Güç trend çizgisi, incelenen bağımlılığın değerleri, büyüme oranındaki sabit bir değişiklik ile karakterize edilirse, iyi sonuçlar verir. Böyle bir bağımlılığın bir örneği, arabanın düzgün bir şekilde hızlandırılmış hareketinin bir grafiği olarak hizmet edebilir. Verilerde sıfır veya negatif değerler varsa, güç trend çizgisi kullanamazsınız.

Aşağıdaki denkleme göre inşa edilmiştir:

y = cxb

burada b, c katsayıları sabittir.

5 . Verilerdeki değişim oranı sürekli artıyorsa üstel bir eğilim çizgisi kullanılmalıdır. Sıfır veya negatif değerler içeren veriler için bu tür bir yaklaşım da geçerli değildir.

Aşağıdaki denkleme göre inşa edilmiştir:

y=cebx

burada b, c katsayıları sabittir.

Bir eğilim çizgisi seçerken Excel, yaklaşımın doğruluğunu karakterize eden R2 değerini otomatik olarak hesaplar: R2 değeri bire ne kadar yakınsa, eğilim çizgisi incelenen sürece o kadar güvenilir bir şekilde yaklaşır. Gerekirse, R2 değeri her zaman diyagramda görüntülenebilir.

Formül tarafından belirlenir:

Bir veri serisine trend çizgisi eklemek için:

veri serisi temelinde oluşturulan grafiği etkinleştirin, yani grafik alanına tıklayın. Grafik öğesi ana menüde görünecektir;

bu öğeye tıkladıktan sonra, trend çizgisi ekle komutunu seçmeniz gereken ekranda bir menü görünecektir.

Veri serilerinden birine karşılık gelen grafiğin üzerine gelip sağ tıklarsanız, aynı eylemler kolayca uygulanır; görüntülenen bağlam menüsünde Eğilim çizgisi ekle komutunu seçin. Trend çizgisi iletişim kutusu, Tip sekmesi açılmış olarak ekranda görünecektir (Şekil 1).

Bundan sonra ihtiyacınız olan:

Tür sekmesinde, gerekli eğilim çizgisi türünü seçin (varsayılan olarak Doğrusal seçilidir). Polinom türü için Derece alanında, seçilen polinomun derecesini belirtin.

1 . Yerleşik Seriler alanı, söz konusu grafikteki tüm veri serilerini listeler. Belirli bir veri serisine eğilim çizgisi eklemek için, Yerleşik seri alanında adını seçin.

Gerekirse, Parametreler sekmesine (Şekil 2) giderek, trend çizgisi için aşağıdaki parametreleri ayarlayabilirsiniz:

Yaklaşan (düzleştirilmiş) eğrinin adı alanında eğilim çizgisinin adını değiştirin.

Tahmin alanında tahmin için dönem sayısını (ileri veya geri) ayarlayın;

onay kutusunu etkinleştirmeniz gereken grafik alanında trend çizgisinin denklemini görüntüleyin, denklemi grafikte gösterin;

diyagram alanında yaklaşık güvenilirlik R2 değerini görüntüleyin, bunun için onay kutusunu etkinleştirmeniz gerekir, diyagrama yaklaşıklık güvenilirliğinin (R^2) değerini koyun;

Eğilim çizgisinin Y ekseni ile kesişme noktasını ayarlayın; bunun için Eğrinin bir noktada Y ekseni ile kesişimi onay kutusunu etkinleştirmeniz gerekir;

iletişim kutusunu kapatmak için Tamam düğmesini tıklayın.

Halihazırda oluşturulmuş bir trend çizgisini düzenlemeye başlamanın üç yolu vardır:

trend çizgisini seçtikten sonra Format menüsünden Seçilen trend çizgisi komutunu kullanın;

trend çizgisine sağ tıklayarak çağrılan içerik menüsünden Trend Çizgisini Biçimlendir komutunu seçin;

trend çizgisine çift tıklayarak.

Ekranda Trend Çizgisini Biçimlendir iletişim kutusu belirecektir (Şekil 3), üç sekme içerir: Görünüm, Tür, Parametreler ve son ikisinin içeriği Trend Çizgisi iletişim kutusunun benzer sekmeleriyle tamamen örtüşür (Şekil 1-2 ). Görünüm sekmesinde çizgi türünü, rengini ve kalınlığını ayarlayabilirsiniz.

Halihazırda oluşturulmuş bir trend çizgisini silmek için silinecek trend çizgisini seçin ve Sil tuşuna basın.

Dikkate alınan regresyon analizi aracının avantajları şunlardır:

bunun için bir veri tablosu oluşturmadan grafikler üzerinde bir eğilim çizgisi çizmenin göreli kolaylığı;

önerilen eğilim çizgisi türlerinin oldukça geniş bir listesi ve bu liste en sık kullanılan regresyon türlerini içerir;

rasgele (sağduyu dahilinde) bir dizi ileri ve geri adım için incelenen sürecin davranışını tahmin etme olasılığı;

analitik bir biçimde trend çizgisinin denklemini elde etme olasılığı;

Gerekirse, yaklaşımın güvenilirliğine ilişkin bir değerlendirme elde etme olasılığı.

Dezavantajlar aşağıdaki noktaları içerir:

bir trend çizgisinin inşası, yalnızca bir dizi veri üzerine kurulmuş bir grafik varsa gerçekleştirilir;

Bunun için elde edilen trend çizgisi denklemlerine dayanarak incelenen karakteristik için veri serisi oluşturma süreci biraz karmaşıktır: istenen regresyon denklemleri, orijinal veri serisinin değerlerindeki her değişiklikle, ancak yalnızca grafik alanı içinde güncellenir. , eski çizgi denklemi trendi temelinde oluşturulan veri serisi değişmeden kalırken;

PivotChart raporlarında, grafik görünümünü veya ilişkili PivotTable raporunu değiştirdiğinizde, mevcut eğilim çizgileri korunmaz, bu nedenle eğilim çizgileri çizmeden veya PivotChart raporunu başka bir şekilde biçimlendirmeden önce rapor düzeninin gereksinimlerinizi karşıladığından emin olmalısınız.

Grafik, histogram, düz normalleştirilmemiş alan grafikleri, çubuk, dağılım, kabarcık ve hisse senedi grafikleri gibi grafiklerde sunulan veri serilerine trend çizgileri eklenebilir.

3-B, Standart, Radar, Pasta ve Donut grafiklerindeki veri serilerine eğilim çizgileri ekleyemezsiniz.

Yerleşik Excel İşlevlerini Kullanma

Excel ayrıca, grafik alanı dışındaki eğilim çizgilerini çizmek için bir regresyon analizi aracı sağlar. Bu amaçla bir dizi istatistiksel çalışma sayfası işlevi kullanılabilir, ancak bunların tümü yalnızca doğrusal veya üstel regresyonlar oluşturmanıza izin verir.

Excel, özellikle doğrusal regresyon oluşturmak için çeşitli işlevlere sahiptir:

AKIM;

• EĞİM ve KESİM.

Üstel bir eğilim çizgisi oluşturmak için çeşitli işlevlerin yanı sıra, özellikle:

LGRFP yakl.

TREND ve BÜYÜME fonksiyonlarını kullanarak regresyon oluşturma tekniklerinin pratik olarak aynı olduğuna dikkat edilmelidir. Aynı şey, LINEST ve LGRFPRIBL fonksiyon çifti için de söylenebilir. Bu dört işlev için, bir değerler tablosu oluştururken, regresyon oluşturma sürecini biraz karmaşıklaştıran dizi formülleri gibi Excel özellikleri kullanılır. Ayrıca, bizim görüşümüze göre, lineer bir regresyon inşasının, ilkinin lineer regresyonun eğimini belirlediği ve ikincisinin regresyon tarafından kesilen segmenti belirlediği SLOPE ve KESME fonksiyonlarını kullanarak uygulanmasının en kolay olduğunu not ediyoruz. y ekseninde.

Regresyon analizi için yerleşik işlevler aracının avantajları şunlardır:

eğilim çizgilerini belirleyen tüm yerleşik istatistiksel işlevler için incelenen özelliğin aynı türde veri dizisi oluşturma işleminin oldukça basit bir süreci;

oluşturulan veri serilerine dayalı olarak trend çizgileri oluşturmak için standart bir teknik;

ileri veya geri gerekli sayıda adım için incelenen sürecin davranışını tahmin etme yeteneği.

Dezavantajları, Excel'in diğer (doğrusal ve üstel hariç) trend çizgileri türleri oluşturmak için yerleşik işlevlere sahip olmaması gerçeğini içerir. Bu durum genellikle incelenen sürecin yeterince doğru bir modelinin seçilmesine ve gerçeğe yakın tahminler elde edilmesine izin vermez. Ayrıca TREND ve BÜYÜME fonksiyonları kullanılırken trend çizgilerinin denklemleri bilinmez.

Yazarların, makalenin amacını, regresyon analizinin gidişatını değişen derecelerde tamlık ile sunmak olarak belirlemediğine dikkat edilmelidir. Başlıca görevi, Excel paketinin belirli örnekler kullanarak yaklaşım problemlerini çözmedeki yeteneklerini göstermektir; Excel'in regresyon ve tahmin oluşturmak için hangi etkili araçlara sahip olduğunu gösterin; regresyon analizi hakkında derin bilgiye sahip olmayan bir kullanıcı tarafından bile bu tür problemlerin ne kadar kolay çözülebileceğini göstermektedir.

Belirli sorunları çözme örnekleri

Excel paketinin listelenen araçlarını kullanarak belirli sorunların çözümünü düşünün.

Görev 1

1995-2002 yılları için bir motorlu taşıt işletmesinin kârına ilişkin bir veri tablosu ile. aşağıdakileri yapmanız gerekir.

Bir grafik oluşturun.

Grafiğe doğrusal ve polinom (kuadratik ve kübik) trend çizgileri ekleyin.

Eğilim çizgisi denklemlerini kullanarak, 1995-2004 yılları için her bir eğilim çizgisi için işletmenin kârına ilişkin tablo verilerini elde edin.

2003 ve 2004 için işletme için bir kar tahmini yapın.

sorunun çözümü

Excel çalışma sayfasının A4:C11 hücre aralığında, Şekil 2'de gösterilen çalışma sayfasına giriyoruz. dört.

B4:C11 hücre aralığını seçtikten sonra bir grafik oluşturuyoruz.

Oluşturulan grafiği etkinleştiriyoruz ve yukarıda açıklanan yöntemi kullanarak Trend Çizgisi iletişim kutusunda (bkz. Şekil 1) trend çizgisi türünü seçtikten sonra grafiğe dönüşümlü olarak doğrusal, ikinci dereceden ve kübik eğilim çizgileri ekliyoruz. Aynı iletişim kutusunda, Parametreler sekmesini açın (bkz. Şekil 2), Yaklaşan (düzeltilmiş) eğrinin adı alanına eklenecek eğilimin adını girin ve İleriye dönük tahmin: periyotlar alanına, 2 değeri, çünkü iki yıl ilerisi için bir kar tahmini yapılması planlanıyor. Şema alanında regresyon denklemini ve yaklaşıklık güvenilirliği R2 değerini görüntülemek için, Denklemi ekranda göster onay kutularını etkinleştirin ve yaklaşıklık güvenilirliğinin (R^2) değerini diyagrama yerleştirin. Daha iyi görsel algı için, Trend Çizgisi Formatı iletişim kutusunun Görünüm sekmesini kullandığımız çizilen trend çizgilerinin tipini, rengini ve kalınlığını değiştiririz (bkz. Şekil 3). Eklenen trend çizgileri ile ortaya çıkan grafik, Şek. 5.

1995-2004 yılları için her bir eğilim çizgisi için işletmenin karı hakkında tablo verileri elde etmek. Şekil 2'de sunulan trend çizgilerinin denklemlerini kullanalım. 5. Bunu yapmak için, D3:F3 aralığının hücrelerinde, seçilen eğilim çizgisinin türü hakkında metinsel bilgiler girin: Doğrusal eğilim, Kuadratik eğilim, Kübik eğilim. Ardından, D4 hücresine doğrusal regresyon formülünü girin ve dolgu işaretçisini kullanarak bu formülü D5:D13 hücre aralığına göreli referanslarla kopyalayın. D4:D13 hücre aralığından doğrusal bir regresyon formülüne sahip her hücrenin, argüman olarak A4:A13 aralığından karşılık gelen bir hücreye sahip olduğuna dikkat edilmelidir. Benzer şekilde, ikinci dereceden regresyon için E4:E13 hücre aralığı doldurulur ve kübik regresyon için F4:F13 hücre aralığı doldurulur. Böylece işletmenin 2003 ve 2004 yılı karı için bir tahmin yapılmıştır. üç eğilim ile. Ortaya çıkan değerler tablosu, Şek. 6.

Görev 2

Bir grafik oluşturun.

Grafiğe logaritmik, üstel ve üstel eğilim çizgileri ekleyin.

Elde edilen trend çizgilerinin denklemlerini ve bunların her biri için yaklaşık güvenilirlik R2'nin değerlerini elde edin.

Eğilim çizgisi denklemlerini kullanarak, 1995-2002 yılları için her bir eğilim çizgisi için işletmenin kârına ilişkin tablo verilerini elde edin.

Bu trend çizgilerini kullanarak 2003 ve 2004 için işletme için bir kar tahmini yapın.

sorunun çözümü

Problem 1'in çözümünde verilen metodolojiyi takip ederek, logaritmik, üstel ve üstel eğilim çizgilerinin eklendiği bir diyagram elde ederiz (Şekil 7). Ayrıca, elde edilen trend çizgisi denklemlerini kullanarak, 2003 ve 2004 için öngörülen değerler de dahil olmak üzere, işletmenin karı için değerler tablosunu dolduruyoruz. (Şek. 8).

Şek. 5 ve şek. logaritmik eğilime sahip modelin, yaklaşıklık güvenilirliğinin en düşük değerine karşılık geldiği görülebilir.

R2 = 0.8659

R2'nin en yüksek değerleri, polinom eğilimi olan modellere karşılık gelir: ikinci dereceden (R2 = 0.9263) ve kübik (R2 = 0.933).

Görev 3

Görev 1'de verilen 1995-2002 yılları için bir motorlu taşıt işletmesinin karı hakkında bir veri tablosu ile aşağıdaki adımları gerçekleştirmelisiniz.

TREND ve BÜYÜME işlevlerini kullanarak doğrusal ve üstel eğilim çizgileri için veri serileri alın.

TREND ve BÜYÜME fonksiyonlarını kullanarak 2003 ve 2004 yılları için işletme için bir kar tahmini yapın.

İlk veriler ve alınan veri serileri için bir diyagram oluşturun.

sorunun çözümü

Görev 1'in çalışma sayfasını kullanalım (bkz. Şekil 4). TREND işleviyle başlayalım:

işletmenin karı hakkında bilinen verilere karşılık gelen TREND işlevinin değerleriyle doldurulması gereken D4:D11 hücre aralığını seçin;

Ekle menüsünden İşlev komutunu çağırın. Görüntülenen İşlev Sihirbazı iletişim kutusunda, İstatistik kategorisinden TREND işlevini seçin ve ardından Tamam düğmesini tıklayın. Aynı işlem, standart araç çubuğunun düğmesine (Ekle işlevi) basılarak da gerçekleştirilebilir.

Görüntülenen İşlev Bağımsız Değişkenleri iletişim kutusunda, Bilinen_değerler_y alanına C4:C11 hücre aralığını girin; Bilinen_değerler_x alanında - B4:B11 hücrelerinin aralığı;

girilen formülü bir dizi formülü yapmak için + + tuş kombinasyonunu kullanın.

Formül çubuğuna girdiğimiz formül şöyle görünecektir: =(TREND(C4:C11;B4:B11)).

Sonuç olarak, D4:D11 hücre aralığı, TREND işlevinin karşılık gelen değerleriyle doldurulur (Şekil 9).

Şirketin 2003 ve 2004 yıllarına ilişkin kâr tahminini yapmak. gerekli:

TREND işlevi tarafından tahmin edilen değerlerin girileceği D12:D13 hücre aralığını seçin.

TREND işlevini çağırın ve görünen İşlev Bağımsız Değişkenleri iletişim kutusunda Bilinen_değerler_y alanına - C4:C11 hücre aralığını girin; Bilinen_değerler_x alanında - B4:B11 hücrelerinin aralığı; ve New_values_x alanında - B12:B13 hücre aralığı.

Ctrl + Shift + Enter klavye kısayolunu kullanarak bu formülü bir dizi formülüne dönüştürün.

Girilen formül şöyle görünecektir: =(TREND(C4:C11;B4:B11;B12:B13)) ve D12:D13 hücre aralığı TREND fonksiyonunun tahmin edilen değerleriyle doldurulacaktır (bkz. 9).

Benzer şekilde, bir veri serisi, doğrusal olmayan bağımlılıkların analizinde kullanılan ve doğrusal karşılığı TREND ile tamamen aynı şekilde çalışan BÜYÜME işlevi kullanılarak doldurulur.

Şekil 10, tabloyu formül görüntüleme modunda gösterir.

İlk veriler ve elde edilen veri serileri için Şekil 2'de gösterilen diyagram. on bir.

Görev 4

Bir motorlu taşıt işletmesinin sevk hizmeti tarafından cari ayın 1'den 11'ine kadar olan süre için hizmet başvurularının alınmasına ilişkin bir veri tablosu ile aşağıdaki eylemler gerçekleştirilmelidir.

Doğrusal regresyon için veri serileri elde edin: EĞİM ve KESME fonksiyonlarını kullanarak; DOĞRU işlevini kullanarak.

LYFFPRIB işlevini kullanarak üstel regresyon için bir veri serisi alın.

Yukarıdaki işlevleri kullanarak, cari ayın 12'sinden 14'üne kadar olan süre için sevk hizmetine yapılan başvuruların alınması hakkında bir tahmin yapın.

Orijinal ve alınan veri serisi için bir diyagram oluşturun.

sorunun çözümü

EĞİLİM ve BÜYÜME işlevlerinin aksine, yukarıda listelenen işlevlerin (eğim, KESİNTİ, DOĞRU, LGRFPRIB) hiçbirinin gerileme olmadığını unutmayın. Bu işlevler yalnızca gerekli regresyon parametrelerini belirleyen yardımcı bir rol oynar.

EĞİM, KESİNTİ, DOĞRU, LGRFPRIB işlevleri kullanılarak oluşturulan doğrusal ve üstel regresyonlar için, TREND ve BÜYÜME işlevlerine karşılık gelen doğrusal ve üstel regresyonların aksine, denklemlerinin görünümü her zaman bilinir.

1 . Denklemi içeren bir doğrusal regresyon oluşturalım:

y=mx+b

EĞİM ve KESME işlevleri kullanılarak, m regresyonunun eğimi EĞİM işlevi tarafından belirlenir ve sabit terim b - KESME işlevi tarafından belirlenir.

Bunu yapmak için aşağıdaki eylemleri gerçekleştiriyoruz:

kaynak tabloyu A4:B14 hücre aralığına girin;

m parametresinin değeri C19 hücresinde belirlenecektir. İstatistik kategorisinden Eğim işlevini seçin; Bilinen_değerler_y alanına B4:B14 hücrelerinin aralığını ve bilinen_değerler_x alanına A4:A14 hücrelerinin aralığını girin. Formül C19 hücresine girilecektir: =EĞİM(B4:B14;A4:A14);

benzer bir yöntem kullanılarak, D19 hücresindeki b parametresinin değeri belirlenir. Ve içeriği şöyle görünecektir: = KESİNTİLENDİRME(B4:B14;A4:A14). Böylece, doğrusal bir regresyon oluşturmak için gerekli olan m ve b parametrelerinin değerleri sırasıyla C19, D19 hücrelerinde saklanacaktır;

daha sonra C4 hücresine doğrusal regresyon formülünü şu şekilde giriyoruz: = $ C * A4 + $ D. Bu formülde C19 ve D19 hücreleri mutlak referanslarla yazılır (hücre adresi olası kopyalama ile değişmemelidir). Mutlak referans işareti $, imleci hücre adresine yerleştirdikten sonra klavyeden veya F4 tuşu kullanılarak yazılabilir. Doldurma tutamacını kullanarak bu formülü C4:C17 hücre aralığına kopyalayın. İstenen veri serisini elde ederiz (Şekil 12). İstek sayısı bir tamsayı olduğu için Hücre Formatı penceresinin Sayı sekmesinde sayı biçimini ondalık basamak sayısı 0 olacak şekilde ayarlamalısınız.

2 . Şimdi denklem tarafından verilen doğrusal bir regresyon oluşturalım:

y=mx+b

DOĞRU işlevini kullanarak.

Bunun için:

DOT işlevini bir dizi formülü olarak C20:D20: =(LINEST(B4:B14;A4:A14)) hücre aralığına girin. Sonuç olarak, C20 hücresindeki m parametresinin değerini ve D20 hücresindeki b parametresinin değerini alırız;

formülü D4 hücresine girin: =$C*A4+$D;

Doldurma işaretçisini kullanarak bu formülü D4:D17 hücre aralığına kopyalayın ve istenen veri serisini alın.

3 . Denklemi içeren bir üstel regresyon oluşturuyoruz:

LGRFPRIBL işlevinin yardımıyla benzer şekilde gerçekleştirilir:

C21:D21 hücre aralığında, dizi formülü olarak LGRFPRIBL işlevini girin: =( LGRFPRIBL (B4:B14;A4:A14)). Bu durumda, m parametresinin değeri C21 hücresinde, b parametresinin değeri ise D21 hücresinde belirlenecektir;

formül E4 hücresine girilir: =$D*$C^A4;

dolgu işaretçisi kullanılarak bu formül, üstel regresyon için veri serisinin yerleştirileceği E4:E17 hücre aralığına kopyalanır (bkz. Şekil 12).

Şek. 13, formüllerin yanı sıra gerekli hücre aralıkları ile kullandığımız fonksiyonları görebileceğimiz bir tabloyu göstermektedir.

Değer R 2 aranan belirleme katsayısı.

Bir regresyon bağımlılığı oluşturma görevi, R katsayısının maksimum değeri aldığı model (1) m katsayılarının vektörünü bulmaktır.

R'nin önemini değerlendirmek için aşağıdaki formülle hesaplanan Fisher F-testi kullanılır.

nerede n- numune boyutu (deney sayısı);

k, model katsayılarının sayısıdır.

F, veriler için bazı kritik değerleri aşarsa n ve k ve kabul edilen güven seviyesi, o zaman R'nin değeri anlamlı kabul edilir. F'nin kritik değerlerinin tabloları, matematiksel istatistiklerle ilgili referans kitaplarında verilmiştir.

Böylece, R'nin önemi yalnızca değeriyle değil, aynı zamanda deney sayısı ile modelin katsayılarının (parametrelerinin) sayısı arasındaki oranla da belirlenir. Aslında, basit bir lineer model için n=2 için korelasyon oranı 1'dir (düzlemdeki 2 noktadan her zaman tek bir düz çizgi çizebilirsiniz). Bununla birlikte, deneysel veriler rastgele değişkenlerse, böyle bir R değerine büyük bir dikkatle güvenilmelidir. Genellikle anlamlı bir R ve güvenilir regresyon elde etmek için deney sayısının model katsayılarının (n>k) sayısını önemli ölçüde aşmasının sağlanması amaçlanır.

Doğrusal bir regresyon modeli oluşturmak için şunları yapmalısınız:

1) deneysel verileri (çıktı değerini içeren sütun) içeren n satır ve m sütundan oluşan bir liste hazırlayın Y listede ilk veya son olmalıdır); örneğin, bir önceki görevin verilerini alalım, "periyot numarası" adlı bir sütun ekleyerek, 1'den 12'ye kadar olan periyot sayılarını numaralandıralım. (bunlar değerler olacaktır. X)

2) Veri/Veri Analizi/Regresyon menüsüne gidin

"Araçlar" menüsünde "Veri Analizi" öğesi eksikse, aynı menünün "Eklentiler" öğesine gitmeli ve "Analiz Paketi" kutusunu işaretlemelisiniz.

3) "Gerileme" iletişim kutusunda şunları ayarlayın:

giriş aralığı Y;

giriş aralığı X;

çıktı aralığı - hesaplama sonuçlarının yerleştirileceği aralığın sol üst hücresi (yeni bir çalışma sayfasına yerleştirilmesi önerilir);

4) "Tamam" ı tıklayın ve sonuçları analiz edin.

Yöntemin özü, söz konusu çözümün kalitesi için kriterin, en aza indirilmeye çalışılan karesel hataların toplamı olduğu gerçeğinde yatmaktadır. Bunu uygulamak için, mümkün olduğunca bilinmeyen bir rastgele değişkenin (daha fazla - çözümün doğruluğu daha yüksek) ve en iyisini seçmenin gerekli olduğu belirli bir beklenen çözüm kümesinin ölçümlerini yapmak gerekir. . Çözüm kümesi parametreleştirilirse, parametrelerin optimal değeri bulunmalıdır.

Hata kareleri neden en aza indirgeniyor ve hataların kendisi değil? Gerçek şu ki, çoğu durumda hatalar her iki yönde de meydana gelir: tahmin, ölçümden daha büyük veya daha küçük olabilir. Farklı işaretlerle hatalar eklersek, birbirlerini iptal edecekler ve sonuç olarak toplam bize tahminin kalitesi hakkında yanlış bir fikir verecektir. Genellikle, nihai tahminin ölçülen değerlerle aynı boyuta sahip olması için karekök, karesi alınmış hataların toplamından alınır.

LSM matematikte, özellikle olasılık teorisinde ve matematiksel istatistiklerde kullanılır. Bu yöntem, yararlı sinyali üzerine bindirilmiş gürültüden ayırmak gerektiğinde, filtreleme problemlerinde en büyük uygulamaya sahiptir.

Ayrıca, verilen bir fonksiyonun daha basit fonksiyonlarla yaklaşık temsili için matematiksel analizde kullanılır. LSM'nin bir başka uygulama alanı, denklem sayısından daha az bilinmeyenli denklem sistemlerinin çözümüdür.

Bu yazıda bahsetmek istediğim, LSM'nin çok beklenmedik birkaç uygulamasını daha buldum.

ÇUŞ'ler ve yazım hataları

Yazım hataları ve yazım hataları, otomatik çevirmenlerin ve arama motorlarının belasıdır. Gerçekten de eğer kelime sadece 1 harf fark ediyorsa program onu ​​başka bir kelime olarak kabul eder ve yanlış çevirir/arama yapar veya çeviremez/hiç bulamaz.

Ben de benzer bir problem yaşadım: Moskova evlerinin adreslerini içeren iki veri tabanı vardı ve bunların bir araya getirilmesi gerekiyordu. Ama adresler farklı bir üslupla yazılmıştı. Bir veritabanında KLADR standardı (Tüm Rusya adres sınıflandırıcısı) vardı, örneğin: "BABUSHKINA PILOT UL., D10K3". Ve başka bir veritabanında bir posta stili vardı, örneğin: “St. Pilot Babushkin, ev 10 bina 3. Her iki durumda da hata yok gibi görünüyor ve işlemi otomatikleştirmek inanılmaz derecede zor (her veritabanında 40.000 kayıt var!). Yeterince yazım hatası olmasına rağmen... Yukarıdaki 2 adresin aynı eve ait olduğu bilgisayar tarafından nasıl anlaşılır? MNC'nin benim için kullanışlı olduğu yer burasıdır.

Ne yaptım? İlk adreste bir sonraki harfi bulduktan sonra ikinci adreste de aynı harfi aradım. İkisi de aynı yerdeyse, o harf için hatanın 0 olduğunu varsaydım. Bitişik konumlardaysa, hata 1'di. 2 konum kaydırma varsa, hata 2'ydi, vb. Diğer adreste hiç böyle bir harf yoksa, hata n+1 olarak varsayılır, burada n 1. adresteki harf sayısıdır. Böylece karesel hataların toplamını hesapladım ve bu toplamın minimum olduğu kayıtları birleştirdim.

Elbette ev ve bina sayıları ayrı ayrı işlendi. Başka bir "bisiklet" icat edip etmediğimi bilmiyorum ya da gerçekten öyleydi, ama sorun hızlı ve verimli bir şekilde çözüldü. Acaba bu yöntem arama motorlarında kullanılıyor mu? Belki de kullanılır, çünkü kendine saygı duyan her arama motoru, tanıdık olmayan bir kelimeyle karşılaştığında, tanıdık kelimelerin yerini alır (“belki de demek istediniz ...”). Ancak, bu analizi bir şekilde farklı şekilde yapabilirler.

OLS ve resimlere, yüzlere ve haritalara göre arama

Bu yöntem aynı zamanda resimlere, çizimlere, haritalara ve hatta insanların yüzlerine göre arama yapmak için de uygulanabilir.

Bir fotoğraf:

Artık tüm arama motorları, görsellere göre arama yapmak yerine, aslında görsellere göre aramayı kullanıyor. Bu şüphesiz kullanışlı ve kullanışlı bir hizmettir, ancak bunu gerçek bir görsel arama ile tamamlamayı öneriyorum.

Örnek bir resim tanıtılır ve tüm görüntüler için karakteristik noktaların kare sapmalarının toplamı ile bir derecelendirme yapılır. Bu çok karakteristik noktaları belirlemek, başlı başına önemsiz bir görevdir. Bununla birlikte, oldukça çözülebilir: örneğin, yüzler için bunlar gözlerin köşeleri, dudaklar, burnun ucu, burun delikleri, kaşların kenarları ve merkezleri, öğrenciler vb.

Bu parametreleri karşılaştırarak örneğe en çok benzeyen bir yüz bulabilirsiniz. Böyle bir hizmetin çalıştığı siteleri zaten gördüm ve önerdiğiniz fotoğrafa en çok benzeyen bir ünlü bulabilir ve hatta sizi bir ünlüye dönüştüren ve geri döndüren bir animasyon oluşturabilirsiniz. Elbette aynı yöntem, suçluların kimlik görüntülerini içeren İçişleri Bakanlığı üslerinde de çalışıyor.

Fotoğraf: pixabay.com

Evet ve parmak izleri de aynı şekilde aranabilir. Harita arama, coğrafi nesnelerin doğal düzensizliklerine odaklanır - nehirlerin kıvrımları, dağ sıraları, kıyıların, ormanların ve tarlaların ana hatları.

İşte harika ve çok yönlü bir OLS yöntemi. Eminim siz sevgili okuyucular, bu yöntemin alışılmadık ve beklenmedik birçok uygulamasını kendiniz bulabileceksiniz.

Belirli bir fonksiyonun daha basit olanlarla yaklaşık bir 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ğere 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 hesaplamanın 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. 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 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 etmek için 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ütuna yerleştirirseniz, bilinen x değerlerine sahip her satır (sütun) program tarafından ayrı bir değişken olarak algılanacaktır.
• TREND penceresinde x ile bilinen aralık belirtilmemişse, fonksiyonun Excel'de kullanılması durumunda, program bunu, 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 kuklalar için Excel formüllerini biliyorsunuz.

En küçük kareler, düz çizgi denklemindeki katsayılar olan a ve b için değerler bularak bir dizi sıralı çifte en iyi uyan doğrusal bir denklem oluşturmak için matematiksel bir prosedürdür. En küçük kareler yönteminin amacı, y ve ŷ değerleri arasındaki toplam karesel hatayı minimize etmektir. Her nokta için ŷ hatasını belirlersek, en küçük kareler yöntemi en aza indirir:

burada n = çizgi etrafındaki sıralı çiftlerin sayısı. verilerle en alakalı olanıdır.

Bu kavram Şekilde gösterilmektedir

Şekle bakılırsa, verilere en iyi uyan doğru olan regresyon doğrusu, grafikteki dört noktanın toplam karesel hatasını en aza indirir. Aşağıdaki örnekte en küçük kareler yöntemini kullanarak bunu nasıl belirleyeceğinizi göstereceğim.

Son zamanlarda birlikte yaşayan ve banyo makyaj masasını paylaşan genç bir çift düşünün. Genç adam, masasının yarısının amansız bir şekilde küçüldüğünü, saç köpüğü ve soya komplekslerine zemin kaybettiğini fark etmeye başladı. Son birkaç aydır, adam masadaki öğelerin sayısının artma oranını yakından izliyor. Aşağıdaki tablo, kızın banyo masasında son birkaç ayda birikmiş olan eşyaların sayısını göstermektedir.

Amacımız madde sayısının zamanla artıp artmadığını bulmak olduğu için bağımsız değişken "Ay", bağımlı değişken "Madde Sayısı" olacaktır.

En küçük kareler yöntemini kullanarak, y eksenindeki a parçasının ve doğrunun eğimi olan b'nin değerlerini hesaplayarak verilere en uygun denklemi belirliyoruz:

a = y cf - bx cf

burada x cf, bağımsız değişken olan x'in ortalama değeridir, y cf, bağımsız değişken olan y'nin ortalama değeridir.

Aşağıdaki tablo, bu denklemler için gerekli hesaplamaları özetlemektedir.

Küvet örneğimiz için etki eğrisi aşağıdaki denklemle verilecektir:

Denklemimiz 0,976'lık pozitif bir eğime sahip olduğundan, adamın masadaki öğelerin sayısının zaman içinde ayda ortalama 1 öğe oranında arttığının kanıtı var. Grafik, sıralı çiftlerle etki eğrisini gösterir.

Gelecek yarı yıl (16. ay) için beklenen kalem sayısı aşağıdaki gibi hesaplanacaktır:

ŷ = 5,13 + 0,976x = 5,13 + 0,976(16) ~ 20.7 = 21 öğe

Bu yüzden kahramanımızın harekete geçmesinin zamanı geldi.

Excel'de TREND işlevi

Tahmin edebileceğiniz gibi, Excel'in bir değer hesaplama işlevi vardır. en küçük kareler yöntemi. Bu özelliğe TREND denir. Sözdizimi aşağıdaki gibidir:

TREND (bilinen Y değerleri; bilinen X değerleri; yeni X değerleri; const)

Y'nin bilinen değerleri - bir dizi bağımlı değişken, bizim durumumuzda tablodaki öğelerin sayısı

X'in bilinen değerleri - bir dizi bağımsız değişken, bizim durumumuzda bir aydır

yeni X değerleri – bunun için yeni X (ay) değerleri TREND işlevi bağımlı değişkenlerin beklenen değerini döndürür (madde sayısı)

const - isteğe bağlı. b sabitinin 0 olması gerekip gerekmediğini belirten bir Boole değeri.

Örneğin, şekil 16. ay için banyo masasında beklenen ürün sayısını belirlemek için kullanılan TREND fonksiyonunu göstermektedir.