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

Excel'de yöntemi kullanma

o Çözücü Eklentisini Etkinleştirme

o Sorunlu koşullar

o Karar

Excel'de yöntemi kullanma

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.

1. Sekmeye gidin "Dosya".

2. Bölümün adına tıklayın "Seçenekler".

3. Açılan pencerede alt bölümdeki seçimi durdurun. "Eklentiler".

4. Blokta "Kontrol" pencerenin alt kısmında bulunan anahtarı konumuna getirin. Excel Eklentileri(farklı bir değere sahipse) ve düğmesine tıklayın "Gitmek ...".

5. Küçük bir pencere açılır. İçindeki parametrenin yanına bir onay işareti koyuyoruz "Çözüm arayın"... düğmesine tıklayın "TAMAM".

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

Ders: Excel'de bir çözüm bulma

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ıl aynı zamanda eşittir 0 ... Bu yüzden 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.

1. İlk değerin solunda x bir sayı koy 1 ... Bu, katsayının ilk değerinin yaklaşık değeri olacaktır. n.

2. Sütunun sağında y bir sütun daha ekle - nx... Bu sütunun ilk hücresine, katsayı ile çarpma formülünü yazın. n ilk değişkenin hücresi başına x... Aynı zamanda bu değer değişmeyeceği için katsayılı alana bağlantıyı mutlak yapıyoruz. düğmesine tıklayın Girmek.

3. Bir doldurma tutamacı kullanarak bu formülü aşağıdaki sütundaki tüm tablo aralığına kopyalayın.

4. Ayrı bir hücrede değerlerin karelerinin farklarının toplamını hesaplayın y ve nx... Bunu yapmak için düğmeye tıklayın "Ekle işlevi".

5. Açılan "İşlev Sihirbazı" bir kayıt arıyorum "SUMKRAZN"... Seçin ve düğmeye tıklayın "TAMAM".

6. Argümanlar penceresi açılır. alanında "Array_x" y... alanında "dizi_y" sütunun hücre aralığını giriyoruz nx... Değerleri girmek için, imleci alana yerleştirmeniz ve sayfada karşılık gelen aralığı seçmeniz yeterlidir. Girdikten sonra butona tıklayın "TAMAM".

7. Sekmeye gidin "Veri"... Araç kutusundaki şeritte "Analiz" butona bas "Çözüm arayın".

8. Bu araç için parametreler penceresi açılır. alanında "Hedef işlevi optimize et" hücrenin adresini formülle belirtiyoruz "SUMKRAZN"... parametrede "Önce" anahtarı konumuna getirdiğinizden emin olun. "minimum"... alanında "Hücreleri değiştirme" adresi katsayı değeriyle belirtin n... düğmesine tıklayın "Bir çözüm bul".

9. Çözüm, katsayı hücresinde görüntülenecektir. n... Fonksiyonun en küçük karesi olacak olan bu değerdir. Sonuç kullanıcıyı memnun ederse, düğmesine basın "TAMAM" ek bir pencerede.

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, ancak çok daha karmaşık durumlar var. Ancak Microsoft Excel araç takımı, hesaplamaları olabildiğince basitleştirmek için tasarlanmıştır.

http://multitest.semico.ru/mnk.htm

Genel Hükümler

Nasıl daha az sayı mutlak değerde, düz çizgi (2) o kadar iyidir. Doğru (2) seçiminin doğruluğunun bir özelliği olarak, karelerin toplamını alabiliriz.

S için minimum koşullar

	(6)
	(7)

Denklemler (6) ve (7) aşağıdaki gibi yazılabilir:

	(8)
	(9)

Denklemlerden (8) ve (9) a ve b'yi x i ve y i deneysel değerlerinden bulmak kolaydır. (8) ve (9) denklemleriyle tanımlanan (2) doğrusuna en küçük kareler yöntemiyle elde edilen doğru denir (bu isim S karelerinin toplamının bir minimumu olduğunu vurgular). Düz çizginin (2) belirlendiği (8) ve (9) denklemlerine normal denklemler denir.

Basit ve belirtebilirsiniz genel yol normal denklemlerin oluşturulması. Deneysel noktaları (1) ve denklemi (2) kullanarak, a ve b için denklem sistemini yazabiliriz.

Bu denklemlerin her birinin sol ve sağ taraflarını ilk bilinmeyen a'nın katsayısıyla (yani x 1, x 2, ..., xn ile) çarparız ve ortaya çıkan denklemleri ekleriz, sonuç ilk normal denklemdir ( 8).

Bu denklemlerin her birinin sol ve sağ taraflarını ikinci bilinmeyen b'nin katsayısı ile çarpıyoruz, yani. 1 ile ve elde edilen denklemleri ekleyin, sonuç ikinci normal denklemdir (9).

Normal denklemleri elde etmenin bu yöntemi geneldir: örneğin fonksiyon için uygundur.

sabit bir değer vardır ve deneysel verilerden belirlenmelidir (1).

k için denklem sistemi şu şekilde yazılabilir:

En küçük kareler yöntemini kullanarak doğru (2)'yi bulun.

Çözüm. Bulduk:

X ben = 21, y ben = 46.3, x ben 2 = 91, x ben y ben = 179.1.

(8) ve (9) 91a + 21b = 179.1 denklemlerini yazıyoruz,

21a + 6b = 46.3, dolayısıyla buluruz
a = 0.98 b = 4.3.

Bu en çok bulur geniş uygulamaçeşitli bilim ve uygulama alanlarında. Fizik, kimya, biyoloji, ekonomi, sosyoloji, psikoloji vb. olabilir. Kaderin iradesiyle, sık sık ekonomi ile uğraşmak zorunda kalıyorum ve bu nedenle bugün size adı verilen harika bir ülkeye bir bilet vereceğim. Ekonometri=) ... Nasıl istemezsin?! Orası çok iyi - sadece karar vermeniz gerekiyor! ... Ama muhtemelen kesinlikle istediğiniz şey, sorunları nasıl çözeceğinizi öğrenmektir. en küçük kareler yöntemi... Ve özellikle azimli okuyucular onları sadece hatasız değil, aynı zamanda ÇOK HIZLI çözmeyi öğrenecekler ;-) Ama önce genel sorun bildirimi+ ilgili örnek:

Bazı konu alanlarında nicel bir ifadeye sahip göstergeler araştırılsın. Aynı zamanda, göstergenin göstergeye bağlı olduğuna inanmak için her türlü neden vardır. Bu varsayım hem bilimsel bir hipotez olabilir hem de temel sağduyuya dayalı olabilir. Bununla birlikte, bilimi bir kenara bırakmak ve daha çok ağız sulandıran alanları, yani marketleri keşfetmek. ile belirtelim:

- bir bakkalın perakende alanı, metrekare,
- bakkalın yıllık cirosu, milyon Rub.

Ne olduğu çok açık daha büyük alan mağaza, cirosu çoğu durumda daha fazla olacaktır.

Bir tef ile gözlemledikten / deney yaptıktan / hesapladıktan / dans ettikten sonra, elimizde sayısal veriler olduğunu varsayalım:

Bakkallarda bence her şey açık: - 1. mağazanın alanı, - yıllık cirosu, - 2. mağazanın alanı, - yıllık cirosu vb. Bu arada, sınıflandırılmış materyallere erişime sahip olmak hiç gerekli değildir - ciro hakkında oldukça doğru bir tahmin şu şekilde elde edilebilir: matematiksel istatistik... Ancak, dikkatimizi dağıtmayalım, ticari casusluğun seyri - zaten ödendi =)

Tablo verileri ayrıca noktalar şeklinde yazılabilir ve bizim için olağan şekilde tasvir edilebilir. kartezyen sistem .

Önemli bir soruya cevap verelim: Nitel bir çalışma için kaç puana ihtiyacınız var?

Daha büyük daha iyi. İzin verilen minimum set 5-6 noktadan oluşur. Ek olarak, az miktarda veri ile örnek, “anormal” sonuçlar içeremez. Bu nedenle, örneğin, küçük bir elit mağaza, "meslektaşlarına" daha fazla büyüklükte sipariş vermede yardımcı olabilir, böylece Genel desen, bulmak istediğiniz!

Basitçe söylemek gerekirse - bir işlev seçmemiz gerekiyor, Takvim noktalara mümkün olduğunca yakın geçen ... Bu işlev denir yaklaşma (yaklaştırma - yaklaştırma) veya teorik fonksiyon ... Genel olarak konuşursak, hemen bariz bir "meydan okuyan" ortaya çıkar - grafiği TÜM noktalardan geçen yüksek dereceli bir polinom. Ancak bu seçenek zordur ve çoğu zaman basitçe yanlıştır. (grafik her zaman “büküleceğinden” ve ana eğilimi zayıf bir şekilde yansıtacağından).

Bu nedenle, aranan işlev yeterince basit olmalı ve aynı zamanda bağımlılığı yeterince yansıtmalıdır. Tahmin edebileceğiniz gibi, bu tür işlevleri bulma yöntemlerinden biri denir. en küçük kareler yöntemi... İlk olarak, genel hatlarıyla özüne bir göz atalım. Bazı fonksiyonların deneysel verilere yaklaşmasına izin verin:


Bu yaklaşımın doğruluğu nasıl değerlendirilir? Deneysel ve fonksiyonel değerler arasındaki farkları (sapmaları) hesaplayalım (çizim okuyorum)... Akla gelen ilk düşünce, toplamın ne kadar büyük olduğunu tahmin etmektir, ancak sorun şu ki, farklılıklar negatif olabilir. (Örneğin, ) ve böyle bir toplamanın sonucu olarak sapmalar birbirini yok edecektir. Bu nedenle, yaklaşımın doğruluğunun bir tahmini olarak, toplamı kabul etmek için yalvarır. modüller sapmalar:

veya daraltılmış: (aniden, kim bilmiyor: - bu toplam simgesi ve - yardımcı bir değişken - 1'den değerler alan "sayaç".

Deney noktalarını farklı fonksiyonlarla yaklaştırarak, farklı değerler elde edeceğiz ve bu toplamın nerede daha az olduğu açıktır - bu fonksiyon daha doğrudur.

Böyle bir yöntem var ve buna denir en küçük modül yöntemi... Ancak pratikte çok daha yaygın hale geldi. en küçük kareler yöntemi olası negatif değerlerin modül tarafından değil, sapmaların karesi alınarak ortadan kaldırıldığı:

, bundan sonra çabalar böyle bir fonksiyonun seçimine yönlendirilir, böylece sapmaların karelerinin toplamı olabildiğince küçüktü. Aslında, bu nedenle yöntemin adı.

Ve şimdi başka birine dönüyoruz önemli nokta: yukarıda belirtildiği gibi, seçilen işlev oldukça basit olmalıdır - ancak bu tür birçok işlev de vardır: doğrusal , hiperbolik, üstel, logaritmik, ikinci dereceden vesaire. Ve elbette, burada hemen "faaliyet alanını azaltmak" istiyorum. Araştırma için hangi işlev sınıfını seçmeli? İlkel ama etkili bir numara:

- Puan çekmenin en kolay yolu çizim üzerinde ve konumlarını analiz edin. Düz bir çizgide olma eğilimindeyseler, o zaman şunları aramalısınız: düz bir çizginin denklemi optimal değerlerle ve. Başka bir deyişle, görev SUCH katsayılarını bulmaktır - böylece sapmaların karelerinin toplamı en küçük olur.

Noktalar, örneğin, birlikte yer alıyorsa abartma, o zaman lineer bir fonksiyonun kötü bir yaklaşıklık vereceği a priori açıktır. Bu durumda hiperbol denklemi için en "uygun" katsayıları arıyoruz. - minimum kareler toplamını verenler .

Şimdi, her iki durumda da bahsettiğimize dikkat edin. iki değişkenli fonksiyonlar kimin argümanları aranan bağımlılıkların parametreleri:

Ve özünde, standart bir sorunu çözmemiz gerekiyor - bulmak için iki değişkenli minimum fonksiyon.

Örneğimizi hatırlayalım: "mağaza" noktalarının düz bir çizgide yer alma eğiliminde olduğunu ve buna inanmak için her türlü neden olduğunu varsayalım. Doğrusal ilişki perakende alanından ciro. SÖZ "a" ve "bs" katsayılarını bulalım, böylece sapmaların karelerinin toplamı en küçüğüydü. Her şey her zamanki gibi - ilk 1. dereceden kısmi türevler... Buna göre doğrusallık kuralı doğrudan miktar simgesinin altında ayırt edebilirsiniz:

Bu bilgiyi bir deneme veya ders kitabı için kullanmak isterseniz, kaynak listesindeki bağlantı için çok minnettar olacağım, bu tür ayrıntılı hesaplamaları birkaç yerde bulacaksınız:

Standart bir sistem oluşturalım:

Her denklemi "iki" azaltıyoruz ve ayrıca toplamları "parçalıyoruz":

Not : Toplam simgesi için neden “a” ve “bie”nin çıkarılabileceğini kendi başınıza analiz edin. Bu arada, resmi olarak bu toplam ile yapılabilir.

Sistemi "uygulanmış" bir biçimde yeniden yazalım:

bundan sonra problemimizi çözme algoritması çizilmeye başlar:

Noktaların koordinatlarını biliyor muyuz? Biliyoruz. Tutarlar bulabilir miyiz? Kolayca. En basitini oluşturuyoruz iki bilinmeyenli iki lineer denklem sistemi("A" ve "bh"). Sistemi çözüyoruz, örneğin, Cramer yöntemi, bunun sonucunda durağan bir nokta elde ederiz. Kontrol etme ekstremum için yeterli koşul, bu noktada fonksiyonun tam olarak ulaşır asgari... Doğrulama, ek hesaplamalarla ilişkilidir ve bu nedenle onu perde arkasında bırakacağız. (gerekirse eksik çerçeve görüntülenebilir)... Son sonucu çıkarıyoruz:

İşlev en iyi yol (en azından herhangi bir diğer doğrusal fonksiyonla karşılaştırıldığında) deneysel noktaları yakınlaştırır ... Kabaca söylemek gerekirse, grafiği bu noktalara mümkün olduğunca yakın çalışır. gelenekte Ekonometri elde edilen yaklaşıklık işlevi de denir eşleştirilmiş doğrusal regresyon denklemi .

İncelenen sorunun büyük bir pratik önem... Örneğimizdeki durumda, denklem cironun ne olduğunu tahmin etmenizi sağlar ("Oyun") perakende alanının bir veya daha fazla değeriyle mağazada olacak (bu veya bu "x" değeri)... Evet, elde edilen tahmin yalnızca bir tahmin olacaktır, ancak çoğu durumda oldukça doğru olacaktır.

"Gerçek" sayılarla sadece bir problemi analiz edeceğim, çünkü içinde zorluk yok - tüm hesaplamalar seviyede Okul müfredatı 7-8 sınıf. Vakaların yüzde 95'inde sizden sadece doğrusal bir fonksiyon bulmanız istenecek, ancak makalenin en sonunda optimal hiperbol, üs ve diğer bazı fonksiyonların denklemlerini bulmanın artık zor olmadığını göstereceğim.

Aslında, söz verilen çörekleri dağıtmaya devam ediyor - bu tür örnekleri sadece doğru bir şekilde değil, aynı zamanda hızlı bir şekilde nasıl çözeceğinizi de öğreniyorsunuz. Standardı dikkatlice inceliyoruz:

Görev

İki gösterge arasındaki ilişkiyi incelemenin bir sonucu olarak, aşağıdaki sayı çiftleri elde edildi:

En küçük kareler yöntemini kullanarak ampirik sonuca en iyi yaklaşan doğrusal fonksiyonu bulun. (Tecrübeli) veri. Kartezyen dilinde bir çizim yapın dikdörtgen sistem koordinatlar deneysel noktaları ve yaklaşık fonksiyonun grafiğini oluşturur ... Ampirik ve teorik değerler arasındaki sapmaların karelerinin toplamını bulun. İşlevin daha iyi olup olmayacağını anlayın (en küçük kareler yöntemi açısından) deneysel noktaları yakınlaştırın.

“X” anlamlarının doğal olduğuna ve bunun biraz sonra bahsedeceğim karakteristik anlamlı bir anlama sahip olduğuna dikkat edin; ama tabii ki kesirli olabilirler. Ayrıca belirli bir problemin içeriğine bağlı olarak hem "x" hem de "oyun" değerleri tamamen veya kısmen negatif olabilir. Pekala, "yüzsüz" bir görevimiz var ve başlıyoruz çözüm:

Sisteme bir çözüm olarak optimal fonksiyonun katsayılarını buluyoruz:

Daha derli toplu bir gösterim amacıyla, toplamanın 1'den 1'e kadar gerçekleştirildiği zaten açık olduğundan, "sayaç" değişkeni atlanabilir.

Gerekli miktarları tablo şeklinde hesaplamak daha uygundur:


Hesaplamalar bir mikro hesap makinesinde yapılabilir, ancak Excel'i kullanmak çok daha iyidir - hem daha hızlı hem de hatasız; kısa bir video izleyin:

Böylece, aşağıdakileri elde ederiz sistem:

Burada ikinci denklemi 3 ile çarpabilir ve terim terim 1. denklemden 2.'yi çıkar... Ancak bu şanstır - pratikte sistemler genellikle bir hediye değildir ve bu gibi durumlarda tasarruf sağlar Cramer yöntemi:
, bu da sistemin benzersiz bir çözümü olduğu anlamına gelir.

Hadi kontrol edelim. İstemediğimi anlıyorum, ama neden tamamen önlenebilecekleri hataları atlayayım? Bulunan çözümü sistemin her denkleminin sol tarafına yerleştiririz:

Karşılık gelen denklemlerin sağ tarafları elde edilir, bu da sistemin doğru şekilde çözüldüğü anlamına gelir.

Böylece, gerekli yaklaşıklık fonksiyonu: -'den tüm lineer fonksiyonların deneysel verilere en iyi şekilde yaklaşan odur.

farklı Düz mağazanın cirosunun alanına bağımlılığı, bulunan bağımlılık ters ("daha fazla - daha az" ilkesi) ve bu gerçek olumsuz tarafından hemen ortaya çıkar. eğim... İşlev belirli bir göstergede 1 birimlik bir artışla bağımlı göstergenin değerinin azaldığını bize bildirir. ortalama 0,65 birim ile. Söylediği gibi, karabuğdayın fiyatı ne kadar yüksekse, o kadar az satılır.

Yaklaşım fonksiyonunun grafiğini çizmek için iki değerini buluruz:

ve çizimi yürütün:


Oluşturulan hat denir eğilim çizgisi (yani, doğrusal bir trend çizgisi, yani genel durumda bir trendin mutlaka düz bir çizgi olması gerekmez)... "Trendde olmak" tabirini herkes bilir ve bu terimin ek yorumlara ihtiyacı olmadığını düşünüyorum.

Sapmaların karelerinin toplamını hesaplayalım ampirik ve teorik değerler arasında Geometrik olarak, "kızıl" bölümlerin uzunluklarının karelerinin toplamıdır. (iki tanesi o kadar küçük ki onları göremiyorsunuz bile).

Hesaplamaları bir tabloda özetleyelim:


Yine manuel olarak yapılabilir, her ihtimale karşı 1. madde için bir örnek vereceğim:

ancak iyi bilinen bir şekilde hareket etmek çok daha verimlidir:

Tekrar edelim: elde edilen sonucun anlamı nedir?İtibaren tüm lineer fonksiyonların işlev gösterge en küçüğüdür, yani ailesinde en iyi yaklaşımdır. Ve bu arada, sorunun son sorusu tesadüfi değil: ya önerilen üstel fonksiyon deneysel noktalara yaklaşmak daha iyi olacak mı?

Karşılık gelen sapma karelerinin toplamını bulalım - ayırt etmek için onları "epsilon" harfiyle belirteceğim. Teknik tamamen aynı:


Ve yine, sadece her itfaiyeci için 1. nokta için hesaplamalar:

Excel'de standart işlevi kullanıyoruz tecrübe (sözdizimi için Excel Yardımına bakın).

Çıktı:, bu, üstel fonksiyonun deneysel noktalara düz çizgiden daha kötü yaklaştığı anlamına gelir .

Ancak burada "daha kötüsünün" olduğunu belirtmek gerekir. henüz demek değil, Yanlış olan ne. Şimdi bu üstel işlevi çizdim - ve aynı zamanda noktalara da yaklaşıyor - o kadar ki, analitik araştırma olmadan hangi fonksiyonun daha doğru olduğunu söylemek zor.

Bu, çözümü tamamlar ve argümanın doğal değerleri sorusuna geri dönüyorum. Çeşitli çalışmalarda, kural olarak, ekonomik veya sosyolojik, doğal "x" sayısı ay, yıl veya diğer eşit zaman aralıkları. Örneğin, bunun gibi bir problem düşünün.

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.

Matematiksel istatistiklere göre, en az 5-6 nesne üzerindeki veriler incelenirse sonuçlar aşağı yukarı 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, 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 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, en küçük değerine sahip olanı tercih edilmelidir. dikkate alınan tüm noktalarda toplamı ei. 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.

V matematiksel gösterimşuna benziyor:

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. Kesinlikle bulmana izin vermeyecek kesin sonuç, ancak belirli bir bölgedeki bir mağazadan krediyle satın almanın işe yarayıp yaramayacağı konusunda 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, aynı veya daha fazla verilen y değerlerine sahip bir aralık olarak satırlar veya sütunlar. 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ücrelerinde, formülü giriyoruz: "katsayı k değer ile çarpılır X". Örneğin, D1 hücresine "= A1 * B1", D2 hücresine "= A1 * B2" gireriz, vb.

4. k katsayısının bire eşit olduğuna ve f (x) = y = 1 * x fonksiyonunun çözümümüzün ilk yaklaşımı olduğuna inanıyoruz. 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ı, uygun hücre referanslarını şu formüle yönlendirerek manuel olarak yapabiliriz: "= (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... vb. Sonunda yanıldık. ve çok zaman kaybettiğimizi anlayın Excel'de, farkların karelerinin toplamını hesaplamak için, bizim için her şeyi yapacak özel bir formül "SUMKVRAZN" vardır. A2 hücresine girin ve ilk verileri ayarlayın. : ölçülen Y değerlerinin aralığı (C sütunu) ve hesaplanan Y değerlerinin aralığı (D sütunu).

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şık olarak yaklaşmak için, verileri doğrusal, üstel, kuvvet ve polinom işlevi kullanarak tanımlamanıza izin veren özel araçlar vardır, bu nedenle 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 mesele ö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,

En küçük kareler yöntemi (OLS), seçilen fonksiyonun çalışılan verilerden sapmalarının karelerinin toplamını en aza indirmeye dayanır. Bu makalede, kullanarak mevcut verileri yaklaşık olarak hesaplıyoruz. doğrusal fonksiyon y = a x + B .

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

Yalnızca bir değişkene bağlı olarak fonksiyonlara göre yaklaşımı düşünün:

• Doğrusal: y = ax + b (bu makale)
• : y = a * Ln (x) + b
• : y = bir * x m
• : y = a * DP (b * x) + c
• : y = eksen 2 + bx + c

Not: Bu makalede 3. dereceden 6. dereceye kadar bir polinom ile yaklaşıklık durumları ele alınmıştır. Trigonometrik polinom yaklaşımı burada tartışılmaktadır.

Doğrusal bağımlılık

2 değişkenin ilişkisiyle ilgileniyoruz NS ve y... diye bir varsayım var y bağlıdır NS lineer olarak y = balta + B... Bu ilişkinin parametrelerini belirlemek için araştırmacı gözlemler yaptı: her x i değeri için bir y i ölçümü yapıldı (örnek dosyaya bakın). Buna göre 20 çift değer olsun (x i; y i).

Not: Adım adım değişiklik yapılırsa NS sabit, sonra inşa etmek dağılım grafikleri kullanılabilir, değilse, grafik türü kullanılmalıdır Puan .

Değişkenler arasındaki ilişkinin doğrusala yakın olduğu diyagramdan açıkça görülmektedir. 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 çiftlerinin sayısı (bizim durumumuzda n = 20)

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

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

Not:İki boyutlu uzaydaki herhangi bir çizgi, 2 parametrenin değerleriyle 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 x değişkeninden y değerlerini tahmin etmek için daha fazla kullanılabileceğine inanılmaktadır. Gerçekte değişkenler arasında bir ilişki olmasa veya ilişki doğrusal olmasa bile, OLS'nin yine de “en iyi” çizgiyi seçeceği açıktır. Bu nedenle, OLS gerçek bir değişken ilişkisinin varlığı hakkında hiçbir şey söylemez, yöntem sadece fonksiyonun bu tür parametrelerini seçmenize izin verir. a ve B bunun için yukarıdaki ifade minimumdur.

Çok karmaşık matematiksel işlemler yapmadan (detaylara bakın), parametreleri hesaplayabilirsiniz. a ve B :

Formülden de görebileceğiniz gibi, parametre a kovaryans oranını temsil eder ve bu nedenle parametreyi hesaplamak için MS EXCEL'de a aşağıdaki formülleri kullanabilirsiniz (bkz. örnek dosya sayfası Doğrusal):

= KOVAR (B26: B45; C26: C45) / DISP.G (B26: B45) veya

= KOVARIASYON.B (B26: B45; C26: C45) / EKRAN.B (B26: B45)

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

Son olarak, DOT () işlevi, her iki parametreyi de aynı anda değerlendirmenize olanak tanır. Formül girmek için DOT (C26: C45; B26: B45) 2. satırdaki hücreleri seçmek ve tuşuna basmak gerekir. CTRL + VARDİYA + GİRMEK(hakkında makaleye bakın). Sol hücredeki değer döndürülecek a , Sağdaki - B .

Not: Girdiyi karıştırmamak için dizi formülleri ek olarak INDEX () işlevini kullanmanız gerekecektir. formül = İNDEKS (ÇİZGİ (C26: C45; B26: B45); 1) veya sadece = DOT (C26: C45; B26: B45)çizgi eğiminden sorumlu parametreyi döndürür, yani. a ... formül = İNDEKS (ÇİZGİ (C26: C45; B26: B45); 2) doğrunun Y ekseniyle kesişmesinden sorumlu parametreyi döndürür, yani. B .

Parametreleri hesapladıktan sonra, dağılım grafiği karşılık gelen satırı çizebilirsiniz.

En küçük kareler yöntemini kullanarak düz bir çizgi çizmenin başka bir yolu da grafik aracıdır. eğilim çizgisi... Bunu yapmak için diyagramı seçin, menüden seçin Düzen sekmesi, v grup analizi Tıklayın eğilim çizgisi, sonra Doğrusal yaklaşım .

Diyalog kutusundaki "Diyagramda denklemi göster" kutusuna bir onay işareti koyarak, yukarıda bulunan parametrelerin diyagramdaki değerlerle eşleştiğinden emin olabilirsiniz.

Not: Parametrelerin eşleşmesi için diyagramın bir tipi olmalıdır. Gerçek şu ki, bir diyagram oluştururken Takvim X ekseni değerleri kullanıcı tarafından belirtilemez (kullanıcı yalnızca noktaların konumunu etkilemeyen etiketler belirleyebilir). X değerleri yerine 1. sıra kullanılır; 2; 3; … (Kategorileri numaralandırmak için). Bu nedenle, eğer inşa ederseniz eğilim çizgisi gibi bir diyagramda Takvim, o zaman X'in gerçek değerleri yerine, bu dizinin değerleri kullanılacak ve bu da yanlış bir sonuca yol açacaktır (tabii ki X'in gerçek değerleri dizi 1 ile çakışmadıkça) ; 2; 3; ...).