Excel'de bir regresyon oluşturma. Excel'de regresyon: denklem, örnekler. Doğrusal Regresyon

Kanımca, bir öğrenci olarak ekonometri, üniversitemin duvarları içinde tanışmayı başardığım en uygulamalı bilimlerden biridir. Bunun yardımıyla, gerçekten de uygulamalı sorunları kurumsal ölçekte çözmek mümkündür. Bu çözümlerin ne kadar etkili olacağı ise üçüncü soru. Sonuç olarak, bilginin çoğu teori olarak kalacak, ancak ekonometri ve regresyon analizi yine de özel bir dikkatle incelenmeye değer.

Gerilemeyi ne açıklar?

MS Excel'in bu sorunları çözmemizi sağlayan işlevlerini ele almaya başlamadan önce, size regresyon analizinin özünde neleri içerdiğini kısaca açıklamak istiyorum. Böylece sınava girmeniz daha kolay olacak ve en önemlisi konuyu çalışmak daha ilginç olacaktır.

Umarım, matematikten bir fonksiyon kavramına aşinasınızdır. Fonksiyon, iki değişken arasındaki ilişkidir. Bir değişken değiştiğinde diğerine bir şey olur. Sırasıyla X ve Y değişikliklerini değiştiriyoruz. Fonksiyonlar çeşitli yasaları tanımlar. İşlevi bilerek, X için rasgele değerleri değiştirebilir ve Y'nin nasıl değiştiğini görebiliriz.

Bu çok önemlidir, çünkü regresyon, görünüşte sistematik olmayan ve kaotik süreçleri belirli bir işlevin yardımıyla açıklama girişimidir. Yani örneğin Rusya'da dolar kuru ile işsizlik arasındaki ilişkiyi ortaya koymak mümkün.

Bu düzenlilik tespit edilebilirse, o zaman hesaplamalar sırasında elde ettiğimiz fonksiyona göre, doların ruble karşısında N'inci döviz kurunda işsizlik oranının ne olacağına dair bir tahmin yapabileceğiz.
Bu ilişki korelasyon olarak adlandırılacaktır. Regresyon analizi, ele aldığımız değişkenler (dolar kuru ve iş sayısı) arasındaki ilişkinin sıkılığını açıklayacak olan korelasyon katsayısının hesaplanmasını içerir.

Bu katsayı pozitif veya negatif olabilir. Değerleri -1 ile 1 arasında değişir. Buna göre, yüksek bir negatif veya pozitif korelasyon gözlemleyebiliriz. Pozitif ise, dolardaki artışı yeni işlerin ortaya çıkması takip edecek. Negatif ise, döviz kurundaki artışı istihdamda bir düşüş izleyecektir.

Regresyon birkaç türdendir. Doğrusal, parabolik, üstel, üstel vb. olabilir. Hangi regresyonun bizim durumumuza özel olarak karşılık geleceğine, hangi modelin korelasyonumuza mümkün olduğunca yakın olacağına bağlı olarak model seçimini yapıyoruz. Bunu problem örneği üzerinde ele alalım ve MS Excel'de çözelim.

MS Excel'de doğrusal regresyon

Doğrusal regresyon problemlerini çözmek için Veri Analizi işlevine ihtiyacınız vardır. Sizin için etkinleştirilmemiş olabilir, bu nedenle etkinleştirmeniz gerekir.

"Dosya" düğmesine tıklayın;
"Seçenekler" öğesini seçin;
Sol taraftaki sondan bir önceki "Eklentiler" sekmesine tıklayın;

Aşağıda "Yönetim" yazısını ve "Git" düğmesini göreceğiz. Üzerine basıyoruz;
"Analiz Paketi"ni işaretleyin;
"Tamam"a basıyoruz.

Görev örneği

Toplu analiz fonksiyonu etkinleştirildi. Aşağıdaki problemi çözelim. İşletmenin topraklarındaki acil durumların sayısı ve istihdam edilen işçi sayısı hakkında birkaç yıl boyunca bir veri örneğine sahibiz. Bu iki değişken arasındaki ilişkiyi belirlememiz gerekiyor. Çalışan sayısı olan X açıklayıcı değişkeni ve acil durum sayısı olan Y açıklayıcı değişkeni vardır. İlk verileri iki sütuna dağıtalım.

"Veri" sekmesine gidin ve "Veri Analizi" ni seçin

Görünen listeden "Regresyon" u seçin. Y ve X giriş aralıklarında uygun değerleri seçin.

"Tamam"a basıyoruz. Analiz yapılır ve yeni sayfada sonuçları göreceğiz.

Bizim için en anlamlı değerler aşağıdaki şekilde işaretlenmiştir.

Çoklu R, belirleme katsayısıdır. Karmaşık bir hesaplama formülü vardır ve korelasyon katsayımıza ne kadar güvenebileceğimizi gösterir. Buna göre, bu değer ne kadar büyük olursa, güven o kadar yüksek, modelimiz bir bütün olarak o kadar başarılı olur.

Y-Intercept ve X1 Intersection regresyonumuzun katsayılarıdır. Daha önce de belirtildiği gibi, regresyon bir fonksiyondur ve belirli katsayıları vardır. Böylece fonksiyonumuz şöyle görünecektir: Y = 0.64 * X-2.84.

Bize ne veriyor? Bu bize bir tahminde bulunma fırsatı veriyor. Diyelim ki bir işletme için 25 çalışanı işe almak istiyoruz ve acil durum sayısının ne kadar olacağını kabaca hayal etmemiz gerekiyor. Bu değeri fonksiyonumuza koyarız ve Y = 0.64 * 25 - 2.84 sonucunu alırız. Yaklaşık 13 olağanüstü hal meydana geleceğiz.

Nasıl çalıştığını görelim. Aşağıdaki resme bir göz atın. İlgili çalışanlar için gerçek değerler, elde ettiğimiz fonksiyonda değiştirilir. Değerlerin gerçek oyunculara ne kadar yakın olduğunu görün.

Ayrıca y ve x alanını vurgulayarak, "ekle" sekmesine tıklayarak ve dağılım grafiğini seçerek bir korelasyon alanı oluşturabilirsiniz.

Noktalar dağınık ama genellikle ortada düz bir çizgi varmış gibi yukarı doğru hareket ediyor. Ayrıca bu satırı, MS Excel'deki "Düzen" sekmesine gidip "Trend Çizgisi" öğesini seçerek de ekleyebilirsiniz.

Görünen satıra çift tıklayın ve daha önce söylenenleri göreceksiniz. Korelasyon alanınızın nasıl göründüğüne bağlı olarak regresyon türünü değiştirebilirsiniz.

Noktaların düz bir çizgi yerine bir parabol çizdiğini görebilir ve farklı bir regresyon türü seçmek isteyebilirsiniz.

Çözüm

Umarız, bu makale size regresyon analizinin ne olduğu ve ne için olduğu hakkında daha iyi bir anlayış sağlamıştır. Bütün bunlar büyük pratik öneme sahiptir.

Regresyon analizi, istatistiksel araştırmaların en popüler yöntemlerinden biridir. Bağımsız değişkenlerin bağımlı değişken üzerindeki etki derecesini belirlemek için kullanılabilir. Microsoft Excel'in işlevselliği, bu tür analizleri gerçekleştirmek için tasarlanmış araçlara sahiptir. Ne olduklarına ve nasıl kullanılacağına bir göz atalım.

Ancak regresyon analizi yapmanızı sağlayan fonksiyonu kullanabilmeniz için öncelikle Analiz Paketini aktif hale getirmeniz gerekmektedir. Ancak o zaman bu prosedür için gerekli araçlar Excel şeridinde görünecektir.

Şimdi sekmeye gittiğimizde "Veri", araç kutusundaki şeritte "Analiz" yeni bir düğme göreceğiz - "Veri analizi".

Regresyon analizi türleri

Birkaç tür regresyon vardır:

parabolik;
güç;
logaritmik;
üstel;
gösteri;
hiperbolik;
doğrusal regresyon.

Son tür regresyon analizinin Excel'de uygulanması hakkında daha sonra daha ayrıntılı olarak konuşacağız.

Excel'de Doğrusal Regresyon

Aşağıda, örnek olarak, sokaktaki ortalama günlük hava sıcaklığını ve ilgili iş günü için mağaza müşteri sayısını gösteren bir tablo bulunmaktadır. Hava sıcaklığı şeklindeki hava koşullarının bir perakende kuruluşunun katılımını tam olarak nasıl etkileyebileceğini regresyon analizi yardımıyla öğrenelim.

Genel lineer regresyon denklemi şuna benzer: Y = a0 + a1x1 + ... + axk. bu formülde Y etkisini incelemeye çalıştığımız değişken anlamına gelir. Bizim durumumuzda bu, alıcıların sayısıdır. Anlam X değişkeni etkileyen çeşitli faktörlerdir. Seçenekler A regresyon katsayılarıdır. Yani, belirli bir faktörün önemini belirlerler. dizin k aynı faktörlerin toplam sayısını gösterir.

Analiz sonuçları analizi

Regresyon analizinin sonuçları, ayarlarda belirtilen yerde tablo şeklinde görüntülenir.

Ana göstergelerden biri, R Meydanı. Modelin kalitesini gösterir. Bizim durumumuzda bu katsayı 0,705 veya yaklaşık %70,5'tir. Bu kabul edilebilir bir kalite seviyesidir. 0,5'ten küçük bir ilişki kötüdür.

Bir başka önemli gösterge, çizginin kesiştiği hücrede bulunur. "Y kavşağı" ve sütun "katsayılar". Burada Y'nin hangi değere sahip olacağı belirtilir ve bizim durumumuzda bu, diğer tüm faktörler sıfıra eşit olan alıcı sayısıdır. Bu tabloda bu değer 58.04'tür.

Grafiğin kesişme noktasındaki değer "Değişken X1" Ve "katsayılar" Y'nin X'e bağımlılık düzeyini gösterir. Bizim durumumuzda bu, mağaza müşterisi sayısının sıcaklığa bağımlılık düzeyidir. 1.31 katsayısı oldukça yüksek bir etki göstergesi olarak kabul edilir.

Gördüğünüz gibi, Microsoft Excel kullanarak bir regresyon analizi tablosu oluşturmak oldukça kolaydır. Ancak çıktıda elde edilen verilerle yalnızca eğitimli bir kişi çalışabilir ve bunların özünü anlayabilir.

Bu, bazı değişkenlerin diğerlerine bağımlılığını göstermenin en yaygın yoludur, örneğin, GSYİH düzeyi değerden dış yatırım veya dan Merkez Bankası kredi oranı veya dan önemli enerji kaynaklarının fiyatları.

Modelleme, bu bağımlılığın büyüklüğünü (katsayıları) göstermenizi sağlar, bu sayede doğrudan bir tahmin yapabilir ve bu tahminlere dayanarak bir tür planlama yapabilirsiniz. Ayrıca, regresyon analizine dayalı olarak, nihai sonucu etkileyen öncelikli nedenleri harekete geçirmeyi amaçlayan yönetsel kararlar vermek mümkündür ve modelin kendisi bu öncelik faktörlerini vurgulamaya yardımcı olacaktır.

Doğrusal regresyon modelinin genel görünümü:

Y=a 0 +a 1 x 1 +...+a k x k

Nerede A - regresyon parametreleri (katsayıları), X - etkileyen faktörler, k model faktörlerinin sayısıdır.

İlk veri

İlk veriler arasında, son parametre Y'nin (örneğin, GSYİH) birkaç ardışık veya birbirine bağlı değerinden ve etkisini incelediğimiz göstergelerin (örneğin, yabancı yatırım) aynı sayıda değerinden oluşacak belirli bir veri setine ihtiyacımız var.

Yukarıdaki şekil, bu aynı ilk verileri içeren bir tabloyu göstermektedir, Y, ekonomik olarak aktif nüfusun bir göstergesidir ve işletme sayısı, sermayeye yapılan yatırım miktarı ve nüfusun geliri, yani x'leri etkileyen faktörlerdir.

Şekilden modellemenin sadece dinamik serilerle ilgili olabileceği, yani zaman içinde ardışık olarak kaydedilen moment serileriyle ilgili olabileceği gibi hatalı bir sonuç da çıkarılabilir, ancak durum böyle değildir, yapı bağlamında aynı başarı ile modellenebilir, örneğin tabloda belirtilen değerler yıllara göre değil bölgelere göre kırılabilir.

Yeterli doğrusal modeller oluşturmak için, başlangıç verilerinin güçlü düşüşlere veya çökmelere sahip olmaması arzu edilir, bu gibi durumlarda yumuşatma yapılması istenir, ancak bir dahaki sefere yumuşatma hakkında konuşacağız.

Analiz paketi

Doğrusal bir regresyon modelinin parametreleri, En Küçük Kareler Yöntemi (OLS) kullanılarak manuel olarak da hesaplanabilir, ancak bu oldukça zaman alıcıdır. Biraz daha hızlı, bu, programın hesaplamaları kendisinin yapacağı Excel'deki formüller kullanılarak aynı yöntemle hesaplanabilir, ancak yine de formülleri manuel olarak yazmanız gerekir.

Excel'in bir eklentisi var Analiz paketi, analiste yardımcı olmak için oldukça güçlü bir araçtır. Bu araç seti, diğer şeylerin yanı sıra, aynı OLS'ye göre regresyon parametrelerini sadece birkaç tıklamayla hesaplayabilir, aslında bu aracın nasıl kullanılacağı daha sonra tartışılacaktır.

Analiz Paketini Etkinleştirme

Varsayılan olarak, bu eklenti devre dışıdır ve onu sekme menüsünde bulamazsınız, bu yüzden nasıl etkinleştireceğinize adım adım bir göz atalım.

Excel'de, sol üstteki sekmeyi etkinleştirin Dosya, açılan menüde öğeyi arayın Seçenekler ve üzerine tıklayın.

Açılan pencerede solda öğeyi arayın eklentiler ve etkinleştirin, alttaki bu sekmede, varsayılan olarak yazılacağı bir açılır kontrol listesi olacaktır. Excel eklentileri, açılır listenin sağında bir düğme olacak Gitmek, üzerine tıklamanız gerekiyor.

Bir açılır pencere sizden mevcut eklentileri seçmenizi isteyecektir, içinde yanındaki kutuyu işaretlemeniz gerekir. Analiz paketi ve aynı zamanda, her ihtimale karşı, çözüm bulmak(ayrıca yararlı bir şey) ve ardından düğmeye tıklayarak seçimi onaylayın TAMAM.

Analiz Paketini Kullanarak Doğrusal Regresyon Parametrelerini Bulma Talimatları

Analiz Paketi eklentisini etkinleştirdikten sonra, her zaman ana menü sekmesinde bulunacaktır. Veri bağlantının altında Veri analizi

Aktif araç penceresinde Veri analizi olasılıklar listesinden arayın ve seçin gerileme

Ardından, regresyon modelinin parametrelerini hesaplamak için başlangıç verilerini ayarlamak ve seçmek için bir pencere açılacaktır. Burada başlangıç verilerinin aralıklarını, yani açıklanan parametreyi (Y) ve onu etkileyen faktörleri (X) belirtmeniz gerekir, aşağıdaki şekilde olduğu gibi, geri kalan parametreler prensip olarak ayar için isteğe bağlıdır.

Kaynak verileri seçtikten ve Tamam düğmesine tıkladıktan sonra, Excel hesaplamaları etkin çalışma kitabının yeni bir sayfasında görüntüler (ayarlarda aksi belirtilmemişse), bu hesaplamalar şöyle görünür:

Anahtar hücreleri sarı ile doldurdum, her şeyden önce dikkat etmeniz gereken onlardır, diğer parametreler de önemlidir, ancak ayrıntılı analizleri belki ayrı bir gönderi gerektirir.

Bu yüzden, 0,865 - Bu R2- modelin hesaplanan parametrelerinin %86,5'inin, yani modelin kendisinin, çalışılan parametredeki bağımlılığı ve değişiklikleri açıkladığını gösteren belirleme katsayısı - Yçalışılan faktörlerden - X'ler. abartılı ise modelin kalitesinin bir göstergesidir ve ne kadar yüksekse o kadar iyidir. 1'den fazla olamayacağı açıktır ve R2 0,8'in üzerinde olduğunda iyi kabul edilir ve 0,5'ten küçükse böyle bir modelin makullüğü güvenle sorgulanabilir.

Şimdi devam edelim model katsayıları:
2079,85 - Bu bir 0- modelde kullanılan tüm faktörlerin 0'a eşit olması durumunda Y'nin ne olacağını gösteren bir katsayı, bunun modelde tanımlanmayan diğer faktörlere bir bağımlılık olduğu anlaşılmaktadır;
-0,0056 - bir 1- x 1 faktörünün Y üzerindeki etkisinin ağırlığını gösteren bir katsayı, yani bu modeldeki işletme sayısı, ekonomik olarak aktif nüfus göstergesini yalnızca -0,0056 ağırlıkla etkiler (oldukça küçük bir etki derecesi). Eksi işareti, bu etkinin olumsuz olduğunu, yani ne kadar paradoksal olursa olsun, ne kadar çok işletme, ekonomik olarak o kadar az aktif nüfus olduğunu gösterir;
-0,0026 - bir 2- sermayeye yapılan yatırım hacminin ekonomik olarak aktif nüfusun büyüklüğü üzerindeki etki katsayısı, modele göre bu etki de negatiftir;
0,0028 - 3- nüfusun gelirlerinin ekonomik olarak aktif nüfusun büyüklüğü üzerindeki etki katsayısı, burada etki pozitiftir, yani modele göre gelirdeki bir artış, ekonomik olarak aktif nüfusun büyüklüğünde bir artışa katkıda bulunacaktır.

Hesaplanan katsayıları modelde topluyoruz:

Y = 2079,85 - 0,0056x 1 - 0,0026x 2 + 0,0028x 3

Aslında bu, örnekte kullanılan ilk veriler için tam olarak buna benzeyen bir doğrusal regresyon modelidir.

Model Tahmini Değerler ve Tahmin

Yukarıda tartıştığımız gibi, model sadece incelenen parametrenin etkileyen faktörler üzerindeki bağımlılıklarının büyüklüğünü göstermek için değil, aynı zamanda bu etkileyen faktörleri bilerek bir tahmin yapmak mümkün olacak şekilde inşa edilmiştir. Bu tahmini oldukça basit hale getirmek için, sonuçtaki model denkleminde ilgili x'lerin yerine etkileyen faktörlerin değerlerini değiştirmeniz yeterlidir. Aşağıdaki şekilde Excel'de ayrı bir sütunda bu hesaplamalar yapılmıştır.

Gerçek değerler (gerçekte gerçekleşenler) ve aynı şekilde model için hesaplanan değerler, farkı ve dolayısıyla modelin hatasını göstermek için grafikler şeklinde gösterilir.

Bir kez daha tekrar ediyorum, model üzerinde bir tahmin yapmak için, bilinen etkileyen faktörlerin olması gerekir ve eğer bir zaman serisinden ve buna bağlı olarak gelecek için, örneğin gelecek yıl veya ay için bir tahminden bahsediyorsak, o zaman tam da bu gelecekte etkileyen faktörlerin ne olacağını bulmak her zaman mümkün olmaktan uzaktır. Bu gibi durumlarda, etkileyen faktörler için bir tahmin yapmak da gereklidir, çoğu zaman bu, otoregresif bir model kullanılarak yapılır - etkileyen faktörlerin incelenen nesne ve zaman olduğu, yani göstergenin geçmişte ne olduğuna bağlılığının modellendiği bir model.

Bir sonraki makalede otoregresif bir modelin nasıl oluşturulacağını ele alacağız ve şimdi, etkileyen faktörlerin değerlerinin gelecek dönemde (2008 örneğinde) ne olacağını bildiğimizi varsayacağız, bu değerleri hesaplamalarda değiştirerek, 2008 için tahminimizi alacağız.

MS Excel paketi, doğrusal bir regresyon denklemi oluştururken işin çoğunu çok hızlı bir şekilde yapmanızı sağlar. Sonuçların nasıl yorumlanacağını anlamak önemlidir. Bir regresyon modeli oluşturmak için Araçlar\Veri Analizi\Regresyon'u seçin (Excel 2007'de bu mod, Veri/Veri Analizi/Gerileme bölümünde bulunur). Ardından, elde edilen sonuçları analiz için bir bloğa kopyalayın.