Bu makalede, Microsoft Excel'de DOT işlevinin formül sözdizimi ve kullanımı açıklanmaktadır.
Açıklama
DOT işlevi, verilerinize en iyi uyan doğruyu hesaplamak için "en küçük kareler" yöntemini kullanarak doğrunun istatistiğini hesaplar ve ardından doğruyu tanımlayan bir diziyi verir. Ayrıca polinom, logaritma, üstel ve kuvvet serisi de dahil, bilinmeyen parametrelerde doğrusal olan başka model türleri için istatistiği hesaplamak amacıyla DOT işlevini diğer işlevlerle de birleştirebilirsiniz. Bu işlev bir değer dizisi verdiği için dizi formülü olarak girilmiş olmalıdır. Bu makalede, örneklerden sonra yönergeleri bulabilirsiniz.
Doğrunun denklemi:
y = mx + b
– veya –
y = m1x1 + m2x2 + ... + b
Birden fazla x değerleri aralığı varsa bağımlı y değerleri, bağımsız x değerlerinin bir işlevidir. m değerleri her x değerine karşılık gelen katsayılardır ve b sabit değerdir. y, x ve m değerlerinin vektör olabileceğini unutmayın. DOT işlevinin verdiği dizi {mn;mn-1,...,m1;b}. DOT ek regresyon istatistiklerini de verebilir.
Söz dizimi
DOT (Bilinen_y'ler, [bilinen_x'ler], [sabit], [konum])
DOT işlevinin söz diziminde aşağıdaki bağımsız değişkenler bulunur:
Söz dizimi
known_y Gerekli Bilgiler. Y = mx + b ilişkisinde önceden bildiğiniz y değerleri kümesi.
- known_y aralığı tek bir sütundaysa, known_x'lerin her sütunu ayrı bir değişken olarak yorumlanır.
- known_y aralığı tek bir satırda yer alıyorsa, her known_x satırı ayrı bir değişken olarak yorumlanır.
known_x İsteğe bağlı. Y = mx + b ilişkisinde önceden bildiğiniz isteğe bağlı x değerleri kümesi.
- known_x aralığı bir veya birden çok değişken kümesi içerebilir. Yalnızca bir değişken kullanılırsa, known_y'ler ve known_x'ler eşit boyutları olmak koşuluyla herhangi bir şeklin aralığı olabilir. Birden fazla değişken kullanılıyorsa, known_y bir vektör olmalıdır (bir satır yüksekliğinde veya bir sütun genişliğinde bir aralık).
- known_x belirtilmezse, known_y'ninkiyle aynı büyüklükte olan {1,2,3,...} dizisi olmalıdır.
sabit İsteğe bağlı. B sabitinin 0 olup olmayacağını belirten mantıksal bir değerdir.
- Sabit DOĞRU'ysa veya belirtilmemişse, b normal şekilde hesaplanır.
- Sabit YANLIŞ'sa, b 0'a ayarlanır ve m değerleri, y = mx'e uyacak şekilde ayarlanır.
İstatistikler İsteğe bağlı. Ek gerileme istatistiklerinin verilip verilmeyeceğini belirleyen mantıksal değer.
- Konum DOĞRU ise, DOT ek regresyon istatistiklerini verir; Sonuç olarak, verilen dizi {mn;mn-1,...,m1;b; sen,sen-1,...,se1,seb; r2, sey; F,sd; ssreg,ssresid}.
-
Konum YANLIŞ veya atlanmışsa, DOT m katsayısını ve b sabitini verir.
Ek regresyon istatistikleri aşağıdaki gibi olur.
| İstatistik | Açıklama |
|---|---|
| sh1;sh2;...;shn | m1;m2;...;mn katsayıları için standart hata değerleri. |
| shb | b sabiti için standart hata değeri (sabit YANLIŞ olduğunda shb = #N/A). |
| r2 | Benzerlik katsayısıdır. Gerçek ve tahmin edilen y değerlerini karşılaştırır 0'dan 1'e kadar değeri düzenler. Değer 1 ise, örnekte mükemmel bir korelasyon vardır (tahmin edilen y değeri ile gerçek y değeri arasında fark yoktur). Diğer uç değerde, benzerlik katsayısı 0 ise, regresyon denklemi öngörülen y değerinde yararlı değildir. 2'nin nasıl hesaplandığı hakkında bilgi için bu konudaki "Açıklamalar" bölümüne bakın. |
| shy | y tahmin değeri için standart hata. |
| F | F istatistik veya F gözlenen değer. Rastlantıyla oluşan bağımlı ve bağımsız değişkenler arasındaki ilişkinin gözlenip gözlenmediğini belirlemek için F istatistiğini kullanın. |
| sd | Serbestlik derecesi. Serbestlik derecelerini, istatistiksel tabloda F- kritik değerlerini bulmanıza yardım etmesi için kullanın. Modelin güvenlik düzeyini belirlemek için DOT tarafından verilen F istatistik değerleri için tabloda bulduğunuz değerleri karşılaştırın. sd'nin nasıl hesaplandığı hakkında bilgi için bu konu içinde, aşağıdaki "Açıklamalar" bölümüne bakın. Aşağıdaki Örnek 4'te, F ve sd'nin kullanılışı gösterilmektedir. |
| ssreg | Regresyon karelerinin toplamı. |
| ssresid | Karelerin toplamının artığıdır. ssreg ve ssresid hesaplaması hakkında bilgi edinmek için bu konudaki "Notlar" bölümüne bakın. |
Aşağıdaki örnek ek regresyon istatistiklerinin verildiği sırayı gösterir.
Notlar
Herhangi bir düz doğrunun eğimini ve y kesişim noktasını tanımlayabilirsiniz:
Eğim (m):
Genellikle m olarak yazılan bir doğrunun eğimini bulmak için, doğru üzerinde iki nokta alın, (x1,y1) ve (x2,y2); Eğim (y2 - y1)/(x2 - x1)'e eşittir.
Y-kesişim noktası (b):
Genellikle b olarak yazılan bir doğrunun y-kesişim noktası, doğrunun y eksenini kestiği noktadaki y değeridir.
Doğrunun denklemi y = mx + b'dir. m ve b değerlerini bir kere bilirseniz, denklemdeki y veya x değerlerini yerine koyarak doğrudaki herhangi bir noktayı hesaplayabilirsiniz. EĞİLİM işlevini de kullanabilirsiniz.Yalnızca bir tane bağımsız x değişkeniniz varsa, aşağıdaki formülleri kullanarak doğrudan eğimi ve y kesişim noktası değerlerini elde edebilirsiniz:
Eğim:
=İNDİS(DOT(known_y's,known_x's),1)
Y-kesişim noktası:
=İNDİS(DOT(known_y's,known_x's),2)DOT tarafından hesaplanan doğrunun doğruluğu verilerinizdeki dağılım derecesine bağlıdır. Veri ne kadar doğrusalsa, DOT modeli o kadar doğru olur. DOT, verilerinize en iyi uyan doğruyu belirlemek için en küçük kareler yöntemini kullanır. Yalnızca bir tane bağımsız x değişkeniniz varsa, m ve b değerlerinin hesaplamaları aşağıdaki formülleri esas alır:
burada x ve y örnek ortalamalardır; yani, x = ORTALAMA(bilinen x'ler) ve y = ORTALAMA(known_y'lar).Çizgi ve eğri uydurma işlevleri DOT ve LOT, verilerinize uyan en iyi doğruyu veya üstel eğriyi hesaplayabilir. Ancak, verilerinize en uygun iki sonuca karar vermelisiniz. Düz bir çizgi için EĞİLİM(known_y'ler)known_x veya üstel bir eğri için BÜYÜME(known_y'ler, known_x'ler) hesaplayabilirsiniz. Bu işlevler, new_x bağımsız değişkeni olmadan, gerçek veri noktalarınızdaki çizgi veya eğri boyunca tahmin edilen bir y-değerleri dizisi döndürür. Bundan sonra, tahmin edilen değerleri gerçek değerlerle karşılaştırabilirsiniz. Görsel bir karşılaştırma için her ikisinin de grafiğini çıkarmak isteyebilirsiniz.
Regresyon çözümlemesinde, Excel, her bir nokta için o noktanın tahmin edilen y değeriyle gerçek y değeri arasındaki karesi alınmış farkı hesaplar. Bu kareleri alınmış farkların toplamına kareler toplamının artığı, yani ssresid denir. Daha sonra Excel, toplam kareler toplamı olan sstotal değerini hesaplar. Sabit bağımsız değişken = DOĞRU olduğunda veya belirtilmediğinde, toplam kareler toplamı gerçek y değerleriyle y değerlerinin ortalaması arasındaki farkların karelerinin toplamıdır. Sabit bağımsız değişken = YANLIŞ olduğunda, karelerin toplamı gerçek y değerlerinin karelerinin toplamıdır (tek tek her y değerinden ortalama y değeri çıkarılmadan). Karelerin regresyon toplamı olan ssreg şöyle bulunabilir: ssreg = sstotal - ssresid. Toplam kareler toplamıyla karşılaştırıldığında kareler toplamının artığı ne kadar küçük olursa, benzerlik katsayısı değeri olan r2 (r2, değişkenler arasındaki ilişkiyi açıklayan regresyon çözümlemesinden denklemi sonuçlandıran bir göstergedir) o kadar büyük olur. r2 değeri ssreg/sstotal oranına eşittir.
Bazı durumlarda, bir veya daha fazla X sütunu (Y'lerin ve X'lerin sütunlarda olduğunu varsayın), diğer X sütunlarının varlığında ek bir tahmini değere sahip olmayabilir. Başka bir deyişle, bir veya daha fazla X sütununun ortadan kaldırılması, eşit doğrulukta tahmin edilen Y değerlerine yol açabilir. Bu durumda, bu yedekli X sütunları regresyon modelinden çıkarılmalıdır. Bu olguya "eşdoğrusallık" adı verilir, çünkü yedekli X sütunları yedekli olmayan X sütunlarının katlarının toplamı olarak ifade edilebilir. DOT işlevi eşdoğrusallığı denetler ve tüm artıklı X sütunlarını tanımladığında bunları regresyon modelinden kaldırır. Kaldırılan X sütunları, DOT çıktısında 0 se değerlerine ek olarak 0 katsayıya sahip olarak tanınabilir. Bir veya daha fazla sütun yedekli olarak kaldırılırsa, sd'nin etkilenmesinin nedeni, sd'nin tahmine dayalı olarak kullanılan X sütunun sayısına bağlı olmasıdır. sd'nin hesaplanmasıyla ilgili ayrıntılar için Örnek 4'e bakın. Artık X sütunları kaldırıldığı için sd'yi değiştirirse, sey ve F değerleri de etkilenir. Eşdoğrusallık pratikte nispeten nadir olmalıdır. Bununla birlikte, ortaya çıkma olasılığının daha yüksek olduğu bir durum, bazı X sütunlarının bir deneydeki bir deneğin belirli bir grubun üyesi olup olmadığının göstergesi olarak yalnızca 0 ve 1 değerleri içermesidir. Sabit = DOĞRU ise veya atlanırsa, DOT işlevi kesmeyi modellemek için tüm 1 değerleri içeren ek bir X sütununu etkili bir şekilde ekler. Erkek ise her konu için 1 (erkekse 0) içeren bir sütununuz varsa ve kadınsa her konu için 1 (kadın değilse 0) içeren bir sütununuz varsa, bu son sütun gereksizdir çünkü buradaki girdiler, "erkek göstergesi" sütunundaki girdinin, DOT işlevi tarafından eklenen tüm 1 değerlerinin ek sütunundaki girdiden çıkarılmasıyla elde edilebilir.
Eşdoğrusallık nedeniyle modelden hiçbir X sütunu çıkarılmadığında, sd'nin değeri aşağıdaki gibi hesaplanır: known_x'lerin k sütunu varsa ve sabit = DOĞRU ise veya atlanmışsa, sd = n – k – 1. Sabit = YANLIŞ ise, sd = n - k. Her iki durumda da, eşdoğrusallık nedeniyle çıkarılan her X sütunu sd'nin sayısını 1 artırır.
Dizi sabiti ( known_x'ler gibi) bağımsız değişken olarak girildiğinde, aynı satırda yer alan değerleri ayırmak için virgül ve satırları ayırmak için noktalı virgül kullanın. Ayırıcı karakterler bölgesel ayarlara dayanarak farklı olabilir.
Denklemi belirlemek için kullandığınız y değerleri aralık dışındaysa regresyon denklemi tarafından öngörülen y değerlerinin geçerli olmayabileceğini unutmayın.
DOT işlevinde kullanılan temel algoritma KESMENOKTASI ve EĞİM işlevlerinde kullanılan temel algoritmadan farklıdır. Bu algoritmalar arasındaki farklılık veri tanımsız ve aynı doğrultu üzerindeyse farklı sonuçlara neden olabilir. Örneğin, known_y bağımsız değişkenine ait veri noktaları 0 ve known_x bağımsız değişkenine ait veri noktaları da 1 ise:
- DOT 0 değerini verir. DOT işlevinin algoritması aynı doğrultu üzerindeki verilerin akılcı sonuçlarını vermek için tasarlanmıştır; bu durumda da en az bir yanıt bulunabilir.
- EĞİM ve KESMENOKTASI #SAYI/0! hatası verir. EĞİM ve KESMENOKTASI işlevlerinin algoritması tek bir yanıt aramak için tasarlanmıştır; bu durumdaysa birden çok yanıt vardır.
Diğer regresyon türleri için istatistiği hesaplamak amacıyla LOT'u kullanmaya ek olarak, diğer regresyon türlerinin aralığını hesaplamak üzere, DOT için x ve y dizisi olarak x ve y değişkenlerini girerek DOT'u kullanabilirsiniz. Örneğin:
=DOT(ydeğerleri, xdeğerleri^SÜTUN($A:$C))
formun kübik yuvarlamasını hesaplamak için (3 sırasının polinomu) tek y-değerleri sütununuz ve tek x-değerleri sütununuz olduğunda çalışır:
y = m1*x + m2*x^2 + m3*x^3 + b
Bu formülü başka tür regresyonları hesaplamak için uyarlayabilirsiniz, ancak bazı durumlarda çıktı değerlerinin ve başka istatistiklerin uyarlanması gerekir.FTEST işlevinin verdiği F-test değeri DOT işlevinin verdiği F-test değerinden farklıdır. DOT F istatistiğini verirken FTEST de olasılığı verir.
Örnekler
Örnek 1 - Eğim ve Y Kesişim Noktası
Aşağıdaki tabloda yer alan örnek verileri kopyalayın ve yeni bir Excel çalışma sayfasının A1 hücresine yapıştırın. Formüllerin sonuçları göstermesi için, bunları seçip F2 tuşuna basın ve sonra Enter tuşuna basın. Gerekirse, tüm verileri görmek için sütun genişliğini ayarlayabilirsiniz.
| Bilinen y | Bilinen x |
|---|---|
| 1 | 0 |
| 9 | 4 |
| 5 | 2 |
| 7 | 3 |
| Sonuç (eğim) | Sonuç (y-kesişim noktası) |
| 2 | 1 |
| Formül (A7:B7 hücrelerindeki dizi formülü) | |
| =DOT(A2:A5,B2:B5,,YANLIŞ) |
Örnek 2 - Basit Doğrusal Regresyon
Aşağıdaki tabloda yer alan örnek verileri kopyalayın ve yeni bir Excel çalışma sayfasının A1 hücresine yapıştırın. Formüllerin sonuçları göstermesi için, bunları seçip F2 tuşuna basın ve sonra Enter tuşuna basın. Gerekirse, tüm verileri görmek için sütun genişliğini ayarlayabilirsiniz.
| Ay | Satış |
|---|---|
| 1 | 3.100 TL |
| 2 | 4.500 TL |
| 3 | 4.400 TL |
| 4 | 5.400 TL |
| 5 | 7.500 TL |
| 6 | 8.100 TL |
| Formül | Sonuç |
| =TOPLA(DOT(B1:B6, A1:A6)*{9,1}) | 11.000 TL |
| 1 - 6 arası aylarda yapılan satışlara dayanarak, dokuzuncu aydaki satış tahminini hesaplar. |
Örnek 3 - Birden Çok Doğrusal Regresyon
Aşağıdaki tabloda yer alan örnek verileri kopyalayın ve yeni bir Excel çalışma sayfasının A1 hücresine yapıştırın. Formüllerin sonuçları göstermesi için, bunları seçip F2 tuşuna basın ve sonra Enter tuşuna basın. Gerekirse, tüm verileri görmek için sütun genişliğini ayarlayabilirsiniz.
| Kaplanan alan (x1) | Bürolar (x2) | Girişler (x3) | Yaş (x4) | Biçilen değer (y) |
|---|---|---|---|---|
| 2310 | 2 | 2 | 20 | 142.000 TL |
| 2333 | 2 | 2 | 12 | 144.000 TL |
| 2356 | 3 | 1,5 | 33 | 151.000 TL |
| 2379 | 3 | 2 | 43 | 150.000 TL |
| 2402 | 2 | 3 | 53 | 139.000 TL |
| 2425 | 4 | 2 | 23 | 169.000 TL |
| 2448 | 2 | 1,5 | 99 | 126.000 TL |
| 2471 | 2 | 2 | 34 | 142.900 TL |
| 2494 | 3 | 3 | 23 | 163.000 TL |
| 2517 | 4 | 4 | 55 | 169.000 TL |
| 2540 | 2 | 3 | 22 | 149.000 TL |
| -234,2371645 | ||||
| 13,26801148 | ||||
| 0,996747993 | ||||
| 459,7536742 | ||||
| 1732393319 | ||||
| Formül (A19'da girilen dinamik dizi formülü) | ||||
| =DOT(E2:E12,A2:D12,DOĞRU,DOĞRU) |
Örnek 4 - F ver2 İstatistiklerini Kullanma
Önceki örnekte, bağımsız değişkenler ve satış fiyatı arasındaki güçlü ilişkiyi belirleyecek benzerlik katsayısı veya r2, 0.99675 ( DOT için çıktıda A17 hücresine bakın). Rastlantıyla oluşan örneğin büyük r2 değeriyle bu sonuçları belirlemek için F istatistiğini kullanabilirsiniz.
Bir an için aslında değişkenler arasında ilişki olmadığını, ancak güçlü bir ilişkiyi kanıtlamak için istatistiksel çözümlemelere neden olan 11 işyeri binasının nadir örneğini çizdiğinizi varsayın. "Alfa" terimi ilişkisi olan son hatalı olasılık için kullanılır.
DOT işlevinden elde edilen çıktısındaki F ve sd, rastlantıyla daha yüksek bir F değerinin ortaya çıkması olasılığını değerlendirmek için kullanılabilir. F, yayımlanmış F dağılımı tablolarındaki kritik değerlerle karşılaştırılabilir veya rastlantıyla daha büyük bir F değerinin ortaya çıkması olasılığını hesaplamak için Excel'in FDAĞ işlevi kullanılabilir. Uygun F dağılımının serbestlik derecesi v1 ve v2'dir. n, veri noktalarının sayısı ise ve sabit = DOĞRU ise veya atlanmışsa, v1 = n – sd – 1 ve v2 = sd. (Sabit = YANLIŞ ise, v1 = n – sd ve v2 = sd.) FDAĞ işlevi — FDAĞ(F;v1;v2) söz dizimiyle — rastlantıyla daha yüksek bir F değerinin ortaya çıkması olasılığını verir. Bu örnekte, sd = 6 (hücre B18) ve F = 459,753674 (hücre A18).
Alfa değeri olarak 0,05, v1 = 11 – 6 – 1 = 4 ve v2 = 6 kabul edildiğinde, F'nin kritik düzeyi 4,53'tür. F = 459,753674'ün 4,53'ten çok daha yüksek olması nedeniyle, bu kadar yüksek bir F değerinin rastlantıyla ortaya çıkması pek olası değildir. (Alfa = 0,05 ile, known_y ve known_x arasında bir ilişki olmadığı hipotezi, F kritik düzey olan 4,53'ü aştığında reddedilecektir.) Excel'de FDAĞ işlevini kullanarak, bu kadar yüksek bir F değerinin rastlantıyla ortaya çıkması olasılığını elde edebilirsiniz. Örneğin, FDAĞ(459,753674, 4, 6) = 1,37E-7, aşırı ölçüde düşük bir olasılıktır. F'nin kritik düzeyini tabloda bularak veya FDAĞ işlevini kullanarak, bu bölgedeki işyeri binalarının biçilen değerinin tahmin edilmesinde regresyon denkleminin yararlı olduğu sonucuna varabilirsiniz. Önceki paragrafta hesaplanan v1 ve v2 için doğru değerler kullanılmasının kritik olduğunu unutmayın.
Örnek 5 - T-istatistiklerini Hesaplama
Başka varsayım sınaması, Örnek 3'teki işyeri binasının tahmin edilen biçilen değerinde her bir eğim katsayısının yararlı olup olmayacağını belirleyecek. Örneğin, istatistiksel anlamda yaş katsayısını sınamak için -234,24 (yaş eğim katsayısı) 13,268'e (A15 hücresindeki yaş katsayılarının tahmin edilen standart hatası) bölün. Aşağıdaki t gözlenen değerdir:
t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7
t'nin mutlak değeri yeterince yüksekse, eğim katsayısının, Örnek 3'teki işyeri binasının biçilen değerinin tahmininde yararlı olduğu sonucuna varılabilir. Aşağıdaki tablo, 4 t-gözlem değerinin mutlak değerlerini göstermektedir.
İstatistik kılavuzundaki bir tabloya başvurursanız, t kritiğinin, iki kuyruklu ve 6 serbestlik derecesinde olduğunu ve Alfa = 0,05'in 2,447 olduğunu bulacaksınız. Bu kritik değer, Excel'de TTERS işlevi kullanılarak da bulunabilir. TTERS(0.05;6) = 2.447. t (17,7) mutlak değeri 2,447'den büyük olduğu için, işyeri binasının biçilen değerini tahmin ederken yaş önemli bir değişkendir. Diğer bağımsız değişkenlerin her biri istatistiksel anlamlılık açısından benzer şekilde test edilebilir. Aşağıda her bağımsız değişken için t gözlenen değerleri bulunmaktadır.
| Değişken | t gözlenen değer |
|---|---|
| Kaplanan alan | 5,1 |
| Büro sayısı | 31,3 |
| Giriş sayısı | 4,8 |
| Yaş | 17,7 |
Bu değerlerin mutlak değeri 2,447'den büyüktür; bu nedenle, regresyon denkleminde kullanılan tüm değişkenler bu alandaki işyeri binalarının öngörülen biçilen değerinde kullanışlı olur.