Veri Serisi Eğri Uydurma ve Tahmin Formülleri

Aynı veri serisini dört farklı tahmin modeliyle karşılaştırın​

Veri Serisi Eğri Uydurma ve Tahmin Formülleri çalışma kitabı, iki sütunlu örnek veriye logaritmik, doğrusal, üstel ve ikinci dereceden polinom modelleri uygular. Kaynağın eski başlığında interpolasyon sözcüğü geçse de dosyanın yaptığı iş tek bir ara noktayı iki komşu değer arasında oransal olarak hesaplamaktan daha geniştir. Excel formülleri, yirmi veri çiftinden model katsayıları bulur; seçilen bir x değeri için dört ayrı y tahmini ve her model için uyum göstergesi üretir. Bu nedenle dosyanın indirildiği sayfada eğri uydurma ve model karşılaştırması açıkça anlatılır. Kullanıcı, verinin ölçü birimini ve neyi temsil ettiğini dosyaya bakarak belirleyemez; örnek değerler anonim sayısal çiftlerdir.

Kaynak veri ve çalışma alanı​

Çalışma kitabında Sayfa1 adlı tek görünür sayfa vardır. A sütununda Independent Variable, B sütununda Dependent Variable başlıkları yer alır. A3:B22 aralığında yirmi x-y gözlemi bulunur. X değerleri yaklaşık 12,92 ile 13,36 arasında, y değerleri yaklaşık 19,90 ile 20,68 arasındadır. Bu dar aralık, seçilen 12,93 örnek sorgusunun gözlenen x aralığı içinde kalmasını sağlar. Dışarıdaki bir değere geçildiğinde aynı formüller sayı üretse de sonuç artık örnek aralığının dışına taşan bir tahmin olur; güvenilirlik aynı kabul edilmemelidir.
Sayfanın sağ tarafında dört model yan yana gösterilir. D:E bölümü Logaritmic, G:H bölümü Lineer, J:K bölümü Exponantional, M:O bölümü Polinom başlıklarını taşır. Bunlar özgün dosyadaki yazılışlarıdır. Her bölümde üst sırada katsayılar, yedinci ve sekizinci satırlarda x girişi ile hesaplanan y sonucu, onuncu satırda ise R2 uyum göstergesi vardır. Bu görünüm, aynı x değerinin farklı model denklemlerinde neden farklı y sonuçları verdiğini karşılaştırmayı kolaylaştırır. Dosyada çizilmiş bir Excel grafik nesnesi yoktur; karşılaştırma hücreler üzerinden yapılır.

Logaritmik modelin hesabı​

Logaritmik bölümde D3 ve E3 hücreleri regresyon katsayılarını taşır. D3 bir dizi formülüdür; katsayılar kaynak y değerleri ile x değerlerinin doğal logaritması kullanılarak oluşturulur. E8 formülü D3 katsayısını LN(D8) ile çarpar ve E3 sabitini ekler. Hazır D8 değeri 12,93 olduğunda Excel'de yeniden hesaplanan E8 sonucu yaklaşık 20,06449'dur. Bu modelin sonuç hücresini anlamak için D8'deki x değerinin pozitif olması gerekir; doğal logaritma sıfır veya negatif x için gerçek sayılı sonuç vermez. Örnek veriler bu koşulu sağlar, ancak kullanıcı kendi verisini eklerken bunu ayrıca kontrol etmelidir.
E10 hücresi kaynak y değerleri ile x değerlerinin logaritması arasındaki RSQ hesabını gösterir. Hazır örnekte değer yaklaşık 0,8033'tür. Bu sayı, logaritmik dönüşüme dayalı ilişkinin örnek veride ne kadar güçlü olduğuna dair bir göstergedir. Tek başına tahmin hatasının tüm özelliklerini vermez. Model seçerken x aralığı, artık hatalar ve verinin konusu da değerlendirilmelidir. Dosya bu kontroller için ayrı bir artık analizi tablosu üretmez.

Doğrusal modelin hesabı​

G:H bölümünde G3 ile H3, x ve y verilerinden doğrusal eğim ve sabiti hesaplar. H8 formülü G8 değerini G3 eğimiyle çarpıp H3 sabitini ekler. G8'de 12,93 bulunduğunda H8 yaklaşık 20,06528 sonucunu verir. Microsoft Excel'de G8 değeri 13,00 olarak değiştirilip tam yeniden hesaplama yapıldığında H8, doğrusal denklemle uyumlu yeni değere yükselmiştir. Bu test, çalışma kitabının yalnız önceden yazılmış bir çıktı görüntüsü olmadığını; giriş değiştiğinde hesaplanan değerin güncellendiğini doğrular.
Doğrusal bölümün H10 hücresindeki RSQ değeri yaklaşık 0,8019'dur. Bu gösterge kaynak A3:A22 ile B3:B22 sütunları arasındaki doğrusal ilişkinin karesel korelasyonudur. E10 ve H10 birbirine yakın olsa da tahmin denklemleri aynı değildir. Örnekte 12,93 için sonuçlar çok yakın görünür, fakat x değiştikçe logaritmik eğri ile düz çizgi farklı yönde açılabilir. Sayfa hangi modelin iş bağlamında doğru olduğunu otomatik seçmez; yalnız hesapları görünür kılar.

Üstel modelin hesabı​

J:K bölümünde LINEST, B3:B22 değerlerinin doğal logaritmasıyla A3:A22 x değerlerini kullanır. J3, dönüştürülmüş modelin eğimi; K3 ise sabitin EXP ile geri dönüştürülmüş biçimidir. K8 formülü K3 çarpı EXP(J8 çarpı J3) yapısını kullanarak y tahmini üretir. Hazır J8 değeri 12,93 için sonuç yaklaşık 20,06521'dir. Bu formül yapısı üstel eğri varsayar. Kaynak y verileri pozitif olduğu için logaritma adımı örnek dosyada çalışır; sıfır ya da negatif y değerleriyle aynı yöntem doğrudan kullanılamaz.
K10 hücresinde görünen yaklaşık 0,7998 R2 değeri dönüştürülmüş logaritmik y serisine bağlı RSQ hesabıdır. Diğer bölümlerdeki uyum değerlerinin tanımıyla bütünüyle aynı ölçekte yorumlanması yanıltıcı olabilir. Dosya dört sayıyı yan yana gösterse de model seçimini yalnız en büyük R2 değerini arayarak yapmak sağlıklı değildir. Kullanıcı kendi verisinde özellikle tahmin edilen ve gerçek y değerleri arasındaki farkları da incelemelidir. Sayfada bu farkların otomatik listesi bulunmaz.

İkinci dereceden polinom​

M:O bölümü x ve x'in karesi için ikinci dereceden bir model kurar. M3, N3 ve O3 katsayıları LINEST tabanlı hesapla elde edilir. N8 hücresindeki formül M8 değerinin karesini M3 ile, kendisini N3 ile çarpar; O3 sabitini ekler. Hazır M8 değeri 12,93 iken N8 yaklaşık 20,04359 üretir. Bu sonuç diğer üç modelin 20,06 civarındaki sonuçlarından biraz düşüktür. Çalışma kitabının bir kopyasında M8 13,00 yapıldığında N8 hesaplanan katsayılara uygun biçimde değişmiştir.
Polinom bölümünün N10 uyum göstergesi yaklaşık 0,8272'dir. Bu sayı örnek veride diğer bölüm değerlerinden yüksektir, ancak polinomun daha fazla esnekliğe sahip olması tek başına gelecek noktaları daha doğru tahmin edeceğini kanıtlamaz. Dar veri aralığının dışına gidildiğinde ikinci dereceden terim tahmini hızla değiştirebilir. Özellikle birden fazla x aralığının veya keskin rejim değişiminin bulunduğu veri kümeleri için ayrı model denetimi gerekir. Dosya güven aralığı, çapraz doğrulama veya otomatik model seçim raporu üretmez.

Excel formülleri ve hesaplama koşulları​

Sayfadaki modeller LINEST, LN, EXP, INDEX ve RSQ işlevlerini kullanır. Bazı katsayı hücreleri dizi formülüdür; bunların yalnız görünen sayısal sonucunu kopyalamak hesabı yeni dosyaya taşımaz. Özgün XLSX dosyasında harici çalışma kitabı bağlantısı, makro, veri doğrulama kuralı veya Excel grafik nesnesi bulunmaz. Sayfaya yerleştirilmiş küçük bir görsel vardır; bu görsel hesaplanan verinin grafiği değildir. Dosyanın tek görünür sayfasının gerçek Excel görüntüsü galeride sunulur. Sütun harfleri, satır numaraları, kaynak veriler ve dört modelin sonuçları bu görüntüde birlikte görülür.
Sorgu x değerleri D8, G8, J8 ve M8 hücrelerine ayrı ayrı yazılır. Dört modeli aynı nokta için karşılaştırmak isteyenler bu hücrelerde aynı değeri kullanmalıdır. Kaynak veri A3:B22 aralığında olduğundan yeni gözlemler eklenirse formüllerin başvuru aralıkları ayrıca güncellenmelidir. Yalnız A23 ve B23'e değer yazmak mevcut regresyonun kendiliğinden o satırı kapsadığı anlamına gelmez. X ve y sütunlarına yanlış türde veri, eksik değer veya logaritma için geçersiz sayı girilirse sonuç hata verebilir ya da model anlamını kaybedebilir.

Sonuçların kullanım sınırları​

Bu çalışma kitabı, farklı fonksiyon biçimlerinin aynı veri üzerinde nasıl tahmin ürettiğini öğrenmek ve örnek bir x değeri için sonuçları karşılaştırmak için uygundur. Kaynaktaki sayılar için birim, sektör veya gözlem yöntemi belirtilmediğinden sonuçlar fiziksel, ekonomik ya da finansal bir olguya aitmiş gibi sunulmaz. Dosya gözlenen iki nokta arasında doğrudan doğrusal interpolasyon yapan ayrı bir komut da içermez; regresyon katsayıları tüm yirmi gözlemden türetilir. Bu ayrım, eski kaynak başlığındaki “interpolasyon” ifadesinin yanlış beklenti oluşturmasını önler.
Örnek sorgu değeri 12,93 için dört hesaplanan sonucun yaklaşık 20,04 ile 20,07 arasında olması, yalnız bu veri kümesindeki yakınlığı gösterir. Başka verilerde ve başka x noktalarında fark büyüyebilir. Model sonuçları karar verme sürecinde kullanılacaksa veri kalitesi, ölçüm hatası, amaçlanan tahmin aralığı ve uygun hata ölçüsü ayrıca incelenmelidir. Özgün workbook hash ile korunmuş ve dosyanın kendisi değiştirilmeden taşınmıştır. İndirilen çalışma kitabının formülleri üzerinde deneme yapmak isteyenler önce bir kopya açarak kaynak verileri ve sorgu hücrelerini değiştirebilir.