Regresyon, Microsoft Excel'de büyük miktarda veriyi analiz etmek ve tahmin ve tahminler yapmak için kullandığımız bir Analiz Aracıdır.
Geleceği tahmin etmek ister misiniz? Hayır, astroloji öğrenmeyeceğiz. Rakamlarla ilgileniyoruz ve bugün Excel'de regresyon analizini öğreneceğiz.
Gelecekteki tahminleri tahmin etmek için şunları inceleyeceğiz:
- EXCEL FONKSİYONLARINI KULLANARAK REGRESYON ANALİZİ (MANUEL REGRESYON BULMA)
- EXCEL'İN ANALİZ TOOLPAK EKLENTİSİNİ KULLANARAK REGRESYON ANALİZİ
- EXCEL'DE REGRESYON TABLOSU
Haydi Yapalım şunu…
Senaryo:
Alkolsüz içecekler sattığınızı varsayalım. Tahmin edebilirseniz ne kadar havalı olur:
- Bir önceki yılın verilerine göre gelecek yıl ne kadar alkolsüz içecek satılacak?
- Hangi alanlara odaklanmak gerekiyor?
- Ve stratejinizi değiştirerek satışlarınızı nasıl artırabilirsiniz?
Kârlı bir şekilde harika olacak. Doğru mu?… Biliyorum. Öyleyse başlayalım.
Satılan satıcı ve alkolsüz içeceklerin 11 kaydı var.
Şimdi bu verilere dayanarak 2000 alkolsüz içecek satışına ulaşmak için gereken satıcı sayısını tahmin etmek istiyorsunuz.
Regresyon denklemi, bu tür yakın tahminler yapmak için bir araçtır. Bunun için önce Regresyonu bilmemiz gerekir.
EXCEL FONKSİYONLARINI KULLANARAK REGRESYON ANALİZİ (MANUEL REGRESYON BULMA)
Bu bölüm, regresyonu, sadece excel regresyon prosedürünü anlatmaktan daha iyi anlamanızı sağlayacaktır.
Tanıtım:
Basit Doğrusal Regresyon:
İki değişken arasındaki ilişkinin incelenmesine Basit Doğrusal Regresyon denir. Bir değişkenin diğer bağımsız değişkene bağlı olduğu yer. Bağımlı değişken genellikle Driven, Response ve Target değişkeni gibi adlarla adlandırılır. Ve bağımsız değişken genellikle Sürüş, Tahmin veya sadece Bağımsız değişken olarak telaffuz edilir. Bu isimler onları açıkça tanımlıyor.
Şimdi bunu senaryonuzla karşılaştıralım. Bunu başarmak için gereken satıcı sayısını bilmek istiyorsunuz. 2000 satış. Yani burada bağımlı değişken satıcı sayısı ve bağımsız değişken alkolsüz içecekler satılmaktadır.
Bağımsız değişken çoğunlukla şu şekilde gösterilir: x ve bağımlı değişken olarak y.
Bizim durumumuzda alkolsüz içecekler satılmaktadır. x ve satıcı sayısı y.
Eğer tayin edersek kaç meşrubat satılacağını bilmek istiyorsak 200 satıcı, o zaman senaryo tam tersi olacaktır.
Hareketli.
Lineer Regresyon Denklemin “Basit” Matematiği:
Bu basit değil. Ancak Excel bunu yapmayı kolaylaştırdı.
En yakın 12. tahmini elde etmek için 11 vakanın tümü için gerekli satıcı sayısını tahmin etmemiz gerekiyor.
Diyelimki:
Satılan Meşrubat (şimdiki değeri) x
Numara Satıcıların y
tahmin edilen y (satıcı sayısı) olarak da adlandırılır Regresyon Denklemi, olabilir
| x*Eğim+Kesme (rahatla, ben hallettim) |
Şimdi nerede olduğunu merak ediyor olmalısın durum eğimi alacak ve kesişecek misin? Endişelenme, excel'in onlar için işlevleri var. Eğimi nasıl bulacağınızı ve manuel olarak nasıl keseceğinizi öğrenmenize gerek yoktur.
İsterseniz bunun için ayrı bir eğitim hazırlarım. Bana yorum bölümünde bildirin. Bunlar bazı önemli veri analizi araçlarıdır.
Şimdi hesaplamamıza geçelim:
Aşama 1: Bu küçük masayı hazırlayın
Adım 2: Regresyon çizgisinin eğimini bulun
Eğimler için Excel İşlevi
| =eğim(bilinen_y'ler,bilinen_x'ler) |
Bilinen_y'leriniz menzil içinde B2: B12 ve bilinen_x'ler aralıkta C2:C12
Hücrede B16, aşağıdaki formülü yaz
| =eğim(B2:B12, C2:C12) |
(Not: Eğim, regresyon denkleminde x katsayısı olarak da adlandırılır)
Alacaksın 0.058409. 2 ondalık basamağa kadar yuvarladığınızda şunu elde edersiniz: 0.06.
Aşama 3: Regresyon Doğrusunun Kesişmesini Bulun
Kesişme için Excel işlevi
| =KESİNTİSİZ(bilinen_y'ler, bilinen_x'ler) |
bizim ne olduğunu biliyoruz bilinen x'ler ve y'ler
Hücrede B17, bu formülü yaz
| =KESME(B2: B12, C2:C12) |
-1.1118969 değerini alacaksınız. 2 ondalık basamağa yuvarlama. Alacaksın -1.11.
Lineer Regresyon Denklemimiz = x*0.06 + (-1.11)'dir. Artık x hedefine bağlı olarak olası y'yi kolayca tahmin edebiliriz.
4. Adım: D2'ye aşağıdaki formülü yazın
| =C2*$B$16+$B$17(Regresyon Denklemi) |
bir değer elde edeceksiniz 13.55.
D2 ila D13'ü seçin ve CTRL+D aralıktaki formülü doldurmak için D2:D13
Hücrede D13 Gerekli sayıda satıcınız var.
| Dolayısıyla, hedefe ulaşmak için 2000 Meşrubat Satışları, insanları parçalara ayırmak yasa dışı olduğu için 115.71 satıcıya veya 116 demeye ihtiyacınız var. |
Şimdi bunu kullanarak, Excel'de What-If analizini kolayca yapabilirsiniz. Sadece satış sayısını değiştirin ve size bu satış hedefine ulaşmak için gereken çok sayıda satıcıyı gösterecektir.
Öğrenmek için etrafında oynayın:
Satışları artırmak için ne kadar işgücüne ihtiyacınız var?
Satıcı sayınızı artırırsanız kaç satış artacak?
Tahmininizi Daha Güvenilir Hale Getirin:
2000 satış yapmak için 116 satıcıya ihtiyacınız olduğunu biliyorsunuz.
Analitikte hiçbir şey söylenmez ve inanılmaz. Tahmininize bir güvenilirlik yüzdesi vermelisiniz. Denkleminizin sertifikasını vermek gibidir.
Korelasyon Katsayısı Formülü:
Bir sonraki sorulacak şey, bu iki değişkenin ne kadar ilişkili olduğudur. Statik terimlerle, korelasyon katsayısını söylemeniz gerekir.
Korelasyon için Excel işlevi
| =KOREL(dizi1, dizi2) |
Sizin durumunuzda, bilinen_x'ler ve Bilinen_y'ler, bağımsız olarak dizi1 ve dizi2'dir.
B18'e bu formülü girin
| =KOREL((B2: B12, C2:C12) |
Sahip olacaksın 0.919090. B2 hücresini yüzde olarak biçimlendirin. şimdi var 92% korelasyon.
Şimdi, bu ne 92% anlamına geliyor. Demek ki, orada 92% satıcı sayısını artırırsanız satış şansınız artar ve 92% Satıcı sayısını azaltırsanız satışlar azalır. denir Pozitif Korelasyon Katsayısı.
R Squire (R^2) :
R Squire değeri, regresyon denkleminizin yüzde kaça kadar tesadüf olmadığını söyler. Sağlanan verilere göre ne kadar doğru.
R squire için Excel işlevi RSQ.
| RSQ(bilinen_y'ler, Bilinen_x'ler) |
Bizim durumumuzda, B19 hücresinde R squire değeri alacağız.
B19'a bu formülü girin
| =RSQ(B2: B12, C2:C12) |
Yani r Kare değerinin %84'üne sahibiz. Bu, gerilememizin çok iyi bir açıklamasıdır. Verilerimizin %84'ünün tesadüf olmadığını söylüyor. Y (satıcı sayısı) X'e (alkolsüz içecek satışı) çok bağlıdır.
Gerilememizi sağlamak için bu veriler üzerinde yapabileceğimiz daha birçok test var. Ancak manuel olarak karmaşık ve uzun bir prosedür olacaktır. Bu nedenle excel, Analysis Toolpak'ı sağlar. Bu aracı kullanarak bu regresyon analizini saniyeler içinde yapabiliriz.
EXCEL'İN ANALİZ TOOLPAK EKLENTİSİNİ KULLANARAK EXCEL'DE GERİLEME
Regresyon denklemlerinin ne olduğunu zaten biliyorsanız ve sadece sonuçlarınızı hızlı bir şekilde istiyorsanız o zaman bu kısım tam size göre. Ancak, regresyon denklemlerini kolayca anlamak istiyorsanız, EXCEL FONKSİYONLARINI KULLANARAK REGRESYON ANALİZİ'ne (MANUEL REGRESYON BULMA) gidin.
Excel, Analysis Toolpak'ında analiz için bir dizi araç sağlar. Varsayılan olarak, Veri sekmesinde bulunmaz. Eklemeniz gerekiyor. O halde önce onu ekleyelim.
Excel 2016'ya Analiz Araç Paketi Ekleme
Excel'de veri analizinin nerede olduğunu bilmiyorsanız aşağıdaki adımları izleyin.
Adım 1: Excel Seçeneklerine gidin: Dosya? Seçenekler? Eklentiler
Adım 2: Eklentiler'e tıklayın. Kullanılabilir eklentilerin bir listesini göreceksiniz.
Analysis ToolPak'i seçin ve pencerenin altında yönet'i bulun. Yönet bölümünde Excel Eklentileri'ni seçin ve GO'ya tıklayın.
Eklentiler penceresi açılacaktır. Burada Analysis ToolPak'i seçin. Ardından tamam düğmesine tıklayın.
Artık Veri Sekmesi'nden veri analizi ToolPak'in tüm işlevlerine erişebilirsiniz.
Regresyon için Analysis ToolPak'i Kullanma
Adım 1: Veri sekmesine gidin, Veri Analizini Bulun. Sonra üzerine tıklayın.
Bir diyalog kutusu açılacaktır.
Adım 2: Analiz Araçları listesinde 'Gerileme'yi bulun ve Tamam düğmesine basın.
gerileme giriş penceresi açılacaktır. Bir dizi kullanılabilir giriş seçeneği göreceksiniz. Ancak şimdilik sadece Y Aralığı ve X Aralığı üzerinde yoğunlaşacağız ve diğer her şeyi varsayılana bırakacağız.
Adım 4: Girdiler Sağlayın:
Satıcı Sayısı (şimdiki değeri) Y
Alkolsüz içeceklerin satışı x
Buradan
- Y Aralığı= B2:B11
Ve
- X Aralığı = C2:C11
Çıkış aralığı için aynı sayfada E4'ü seçtim. Aynı çalışma kitabındaki yeni bir çalışma sayfasında veya tamamen yeni bir çalışma kitabında sonuç almak için yeni bir çalışma sayfası seçebilirsiniz. Girişlerinizle işiniz bittiğinde, Tamam düğmesine basın.
Sonuçlar:
Verilerinizden çeşitli bilgiler size sunulacak. Bunalmayın. Tüm yemekleri tüketmenize gerek yok.
Yalnızca gerekli satıcı sayısını tahmin etmemize yardımcı olacak sonuçlarla ilgileneceğiz.
Adım 5: Tahmin için regresyon denklemini biliyoruz. y, yani
x*Eğim+Kesme
sadece yerini bulmamız gerekiyor Eğim ve Tutmak sonuçlarda.
Ve işte buradalar.
Kesişme Katsayısı açıkça belirtilmiştir.
Eğim şu şekilde yazılır:X Değişken 1', bazen X'in katsayısı olarak da bahsedilir. Onları yuvarlarsak, -1.11 Engelleme olarak ve Eğim olarak 0.06.
6. Adım: Sonuçlardan, Regresyon denklemini sürdürebiliriz. Ve bu olurdu
=x*(0.06) + (-1.11)
Bu tabloyu excel'de hazırlayın.
Şimdilik, x E2 hücresinde bulunan 2000'dir.
F2 hücresine bu formülü girin
=E2*F21+F20
bir sonuç alacaksınız 115.7052757.
Yuvarlamak bize verecek 116 Gerekli Satıcı.
Böylece, regresyon denklemini manuel olarak ve Analysis ToolPak kullanarak nasıl oluşturacağımızı öğrendik. Gelecekteki istatistikleri tahmin etmek için bu denklemi nasıl kullanabilirsiniz?
Şimdi Analysis Toolpak tarafından verilen regresyon çıktısını anlayalım.
Regresyon Çıktısını Anlamak:
Excel'deki analiz araç paketini kullanarak regresyon analizi yaparsanız ve anlamını yorumlayamazsanız hiçbir faydası yoktur.
Özet Bölümü:
Adından da anlaşılacağı gibi, verilerin bir özetidir.
-
- Çoklu R: Regresyon denkleminin verilere ne kadar uygun olduğunu söyler. Korelasyon katsayısı olarak da adlandırılır.
Bizim durumumuzda, 0.919090619 veya 0.92 (hesabı yuvarlamak). Bu, satıcı sayımızı artırırsak satışlarda %92'lik bir artış şansı olduğu anlamına gelir.
-
- R Kare: Bulunan regresyonun güvenilirliğini söyler. Bize kaç gözlemin regresyon çizgimizin parçası olduğunu söyler. Bizim durumumuzda, 0.844727566 veya 0.85'tir. Bu, regresyonumuzun %85 oranında uygun olduğu anlamına gelir.
- Düzeltilmiş R Kare: Ayarlanmış kare, R karesinin daha doğrulanmış bir versiyonudur. Temelde Çoklu Regresyon Analizinde kullanışlıdır.
- Standart hata: R. Squire size regresyon çizgisinin yanına kaç tane veri noktasının düştüğünü söylerken, standart hata size bir veri noktasının regresyon çizgisinden ne kadar uzağa gidebileceğini söyler.
Bizim durumumuzda, 6.74.
- Gözlem: Bu, örneğimizde 11 olan gözlem sayısıdır.
Anova Bölümü:
Bu bölüm lineer regresyonda pek kullanılmaz.
- df. Bu bir özgürlük derecesidir. Regresyonu manuel olarak hesaplarken kullanılır.
- SS. Karelerin toplamı. Bu sadece varyansların karelerinin toplamıdır. R squire değerlerini bulmak için kullanılır.
- HANIM. Bu kare değeri anlamına gelir.
- ve 5. F ve F'nin Önemi. F'nin önemi (eğimin p-değeri) F testinden daha küçükse, sıfır hipotezini atabilir ve hipotezinizi kanıtlayabilirsiniz. Basit bir dille, değiştirildiğinde x'in y üzerinde bir etkisi olduğu sonucuna varabilirsiniz.
Bizim durumumuzda F 48.96264 ve F'nin Önemi 0.000063'tür. Bu, regresyonumuzun verilere uyduğu anlamına gelir.
Regresyon Bölümü:
Bu bölümde, regresyon denklemimiz için en önemli iki değere sahibiz.
- Kesişme: Burada, x'in Y üzerinde nerede kesildiğini söyleyen bir kesişimimiz var. Bu, regresyon denkleminin önemli bir parçasıdır. Bizim durumumuzda -1.11'dir.
- X değişkeni 1 (Eğim). x katsayısı da denir. Regresyon çizgisinin tanjantını tanımlar.
EXCEL'DE REGRESYON TABLOSU
Excel'de bir regresyon grafiği çizmek kolaydır. Sadece şu adımları izleyin. Excel 2016, 2013 ve 2010'da Regresyon Grafiği eklemek için bu basit adımları izleyin.
Aşama 1. Bilinen x'lerinizi ilk sütunda ve y'leri ikinci sütunda bilin.
Bizim durumumuzda Bilinen x'lerin Satılan Alkolsüz İçecekler olduğunu biliyoruz. Ve bilinen_y'ler Satıcıdır.
Adım 2. Bilinen x ve y aralığınızı seçin.
Aşama 3: Ekle sekmesine gidin ve dağılım grafiğine tıklayın.
Buna benzeyen bir grafiğiniz olacak.
Adım 4. Eğilim çizgisini ekleyin: Düzene gidin ve analiz bölümünde trend çizgisi seçeneğini bulun.
Trend çizgisi seçeneği altında, Doğrusal Trend Çizgisi'ne tıklayın.
Grafiğiniz bu şekilde görünecek.
Bu sizin regresyon grafiğiniz.
Şimdi aşağıdaki verileri eklerseniz ve seçilen verileri genişletirseniz. Grafiğinizde bir değişiklik göreceksiniz.
Örneğimiz için Satılan Meşrubat'a 2000 ekledik ve Satıcıları boş bıraktık. Ve grafiğin aralığını genişlettiğimizde, elimizde bu olacak.
2000 adet alkolsüz içecek satışını grafiksel olarak yapmak için gerekli satıcı sayısını verecektir. Bu da grafikte 120'nin biraz altında. Ve regresyon denklemimizden bunun 116 olduğunu biliyoruz.
Bu yazımda her şeyi Excel Regresyon Analizi altında ele almaya çalıştım. Excel 2016'da regresyonu açıkladım. Excel 2010 ve excel 2013'te regresyon, excel 2016'dakiyle aynı.
Bu konuyla ilgili daha fazla sorgu için yorumlar bölümünü kullanın. Bir soru sorun, bir fikir verin veya sadece gramer hatalarımdan bahsedin. Her şey açığız. Sadece yorum bölümünü kullanmaktan çekinmeyin.
Excel'de MOD işlevi nasıl hesaplanır
Excel'de Ortalama işlevi nasıl hesaplanır
Standart Sapma Grafiği Nasıl Oluşturulur
Microsoft Excel 2016'da Tanımlayıcı İstatistikler
Excel NORMDAĞ İşlevi Nasıl Kullanılır
Pareto Grafiği ve Analizi Nasıl Kullanılır?
Popüler Makaleler:
Verimliliğinizi Arttıracak 50 Excel Kısayolu
Excel'de DÜŞEYARA İşlevi nasıl kullanılır?
Excel 2016'da EĞERSAY işlevi nasıl kullanılır?
Excel'de SUMIF İşlevi nasıl kullanılır?