Excel'de üstel diyagram. Excel kullanarak tahmin sorunlarını çözme

Fiyat eğilimlerini görsel olarak göstermek için bir eğilim çizgisi kullanılır. Teknik analiz unsuru, analiz edilen göstergenin ortalama değerlerinin geometrik bir görüntüsüdür.

Excel'de bir grafiğe eğilim çizgisinin nasıl ekleneceğine bir göz atalım.

Bir grafiğe bir eğilim çizgisi eklemek

Örneğin 2000 yılından bu yana ortalama petrol fiyatlarını açık kaynaklardan alalım. Analiz için verileri tabloya giriyoruz:

Excel'deki bir eğilim çizgisi, yaklaşık bir işlevin grafiğidir. Ne için - istatistiksel verilere dayalı tahminler yapmak. Bunun için hattın uzatılması ve değerlerinin belirlenmesi gerekmektedir.

R2 \u003d 1 ise, yaklaşım hatası sıfırdır. Örneğimizde, doğrusal bir uyum seçmek düşük güven ve kötü bir sonuç verdi. Tahmin yanlış olacaktır.

Dikkat!!! Trend çizgisi, aşağıdaki grafik ve tablo türlerine eklenemez:

• taç yaprağı;
• dairesel;
• yüzey;
• halka şeklinde;
• ses;
• yığılmış.

Excel'de eğilim çizgisi denklemi

Yukarıdaki örnekte, yalnızca algoritmayı açıklamak için doğrusal bir yaklaşım seçilmiştir. Güvenilirliğin değerinin gösterdiği gibi, seçim tamamen başarılı olmadı.

Kullanıcı girdisindeki değişikliklerin eğilimini en doğru şekilde gösteren ekran türünü seçmelisiniz. Seçenekleri bulalım.

Doğrusal yaklaşım

Geometrik görüntüsü düzdür. Bu nedenle, sabit bir oranda yükselen veya düşen bir metriği göstermek için doğrusal bir uyum kullanılır.

Yöneticinin 10 ay boyunca imzaladığı koşullu sözleşme sayısını düşünün:

Excel tablosundaki verilere dayanarak, bir dağılım grafiği oluşturalım (çizgi türünü göstermeye yardımcı olacaktır):

Grafiği seçin - "eğilim çizgisi ekle". Parametrelerde doğrusal türü seçin. Yaklaşımın doğruluğunun değerini ve Excel'deki eğilim çizgisinin denklemini ekleyin ("Parametreler" penceresinin altındaki kutuları işaretlemeniz yeterlidir).

Sonucu alıyoruz:

Not! Doğrusal yaklaşım türü ile, veri noktaları mümkün olduğunca düz çizgiye yakın yerleştirilir. Bu görünüm aşağıdaki denklemi kullanır:

y \u003d 4,503x + 6,1333

• 4.503 eğim göstergesidir;
• 6.1333 - ofsetler;
• y - değerler dizisi,
• x - dönem numarası.

Grafikteki düz çizgi, yöneticinin işinin kalitesindeki istikrarlı artışı göstermektedir. Yaklaşımın doğruluğunun değeri 0.9929'a eşittir, bu da hesaplanan düz çizginin ilk verilerle iyi bir çakışma olduğunu gösterir. Tahminler doğru olmalıdır.

Örneğin 11. periyotta sonuçlandırılan sözleşmelerin sayısını tahmin etmek için denklemde x yerine 11 koymanız gerekir. Hesaplamalar sırasında 11. periyotta bu yöneticinin 55-56 sözleşme yapacağını öğreniyoruz.

Üstel eğilim çizgisi

Bu tür, girdi değerleri sürekli artan bir oranda değiştiğinde faydalı olacaktır. Sıfır veya negatif özelliklerin varlığında üstel yaklaşım uygulanmaz.

Excel'de üstel bir eğilim çizgisi oluşturalım. Örneğin, X bölgesindeki üretken elektrik arzının koşullu değerlerini alalım:

Bir program oluşturuyoruz. Üstel bir çizgi ekleyin.

Denklem aşağıdaki gibidir:

y \u003d 7,6403e ^ -0,084x

• 7.6403 ve -0.084 sabittir;
• e, doğal logaritmanın temelidir.

Yaklaşık güvenilirliğin göstergesi 0,938 idi - eğri verilere karşılık gelir, hata minimumdur ve tahminler doğru olacaktır.

Excel'de logaritmik eğilim çizgisi

Göstergedeki aşağıdaki değişiklikler için kullanılır: önce hızlı büyüme veya düşüş, sonra - göreceli istikrar. Optimize edilmiş eğri, değerin bu davranışına iyi uyum sağlar. Logaritmik trend, piyasaya yeni sürülen yeni bir ürünün satışını tahmin etmek için uygundur.

İlk aşamada, üreticinin görevi müşteri tabanını genişletmektir. Bir ürünün müşterisi olduğunda, saklanması ve servise alınması gerekir.

Koşullu bir ürünün satışlarını tahmin etmek için bir grafik oluşturalım ve logaritmik bir eğilim çizgisi ekleyelim:

R2, minimum yaklaşım hatasını gösteren 1'e (0,9633) değer olarak yakındır. Sonraki dönemlerde satış hacimlerini tahmin edeceğiz. Bunu yapmak için denklemdeki x yerine nokta numarasını yazmanız gerekir.

Örneğin:

Periyot	14	15	16	17	18	19	20
Tahmin	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

Tahmin rakamlarını hesaplamak için formun bir formülü kullanıldı: \u003d 272.14 * LN (B18) +287.21. B18, dönem numarasıdır.

Excel'de polinom eğilim çizgisi

Bu eğri, değişken artış ve azalma ile karakterizedir. Polinomlar (polinomlar) için derece belirlenir (maksimum ve minimum değerlerin sayısı ile). Örneğin, bir uç (minimum ve maksimum) ikinci derece, iki uç nokta üçüncü derece, üçü dördüncü derece.

Excel'deki polinom eğilimi, kararsız bir değere sahip büyük bir veri kümesini analiz etmek için kullanılır. İlk değerler kümesi (petrol fiyatları) örneğine bakalım.

Yaklaşım güvenilirliğinin böyle bir değerini (0.9256) elde etmek için 6. dereceyi koymak gerekliydi.

Trend çizgisine sahip grafik örneklerini indirin

Ancak bu eğilim, az çok doğru tahminler yapmayı mümkün kılıyor.

Selamlar sevgili yoldaşlar! Bugün öznel ticaret yöntemlerinden birini analiz edeceğiz - trend çizgilerini kullanarak ticaret. Şu soruları ele alalım:

1) Trend nedir (bu bir başlangıç \u200b\u200bnoktası olarak önemlidir)
2) Trend çizgileri oluşturma
3) Pratik ticarette kullanın
4) Yöntemin öznelliği

1) Trend nedir
_________________
Bir trend çizgisi oluşturmaya geçmeden önce, doğrudan trendin kendisiyle ilgilenmeniz gerekir. Akademik tartışmalara girmeyeceğiz ve basitleştirmek için aşağıdaki formülü kabul edeceğiz:

Bir eğilim (yukarı doğru), her biri bir öncekinden daha yüksek (ve düşük) daha yüksek olan yükselen en yüksek ve en düşükler dizisidir.

Bir trend (düşüş trendi), bir sonraki düşük (ve yüksek) bir öncekinin ALTINDA olduğu, düşen (azalan) yüksek ve düşükler dizisidir.

Bir trend çizgisi, iki yüksek (trend düşüş trendi ise) veya iki düşük (trend yükseliyorsa) arasında çizilen bir çizgidir. Yani aslında trend çizgisi bize grafikte bir trend olduğunu gösteriyor! Ancak mevcut olmayabilir (bir daire durumunda).

2) Trend çizgileri oluşturma
____________________________

Bu en zor soru! Bir trend çizgisi oluşturmak için NASIL DOĞRU hakkında birçok tartışma sayfası gördüm! Ama sadece inşa etmemeliyiz, aynı zamanda ticaret yapmalıyız ...

Bir trend çizgisi oluşturmak için, en az iki yüksek (düşüş trendi) veya iki düşük (yükseliş trendi) olması gerekir. Bu uç noktaları bir çizgi ile birleştirmeliyiz.

Çizgi çizerken aşağıdaki kurallara uymak önemlidir:

- Trend çizgisinin eğimi önemlidir. Eğim açısı ne kadar dikse, güvenilirlik o kadar azdır.
- İki nokta kullanarak bir çizgi çizmek en uygunudur. Üç veya daha fazla nokta üzerine inşa ederseniz, trend çizgisinin güvenilirliği azalır (çökmesi muhtemeldir).
- Hiçbir koşulda hat oluşturmaya çalışmayın. Çizemezseniz, büyük olasılıkla trend yoktur. Sonuç olarak, bu enstrüman mevcut piyasa koşullarında kullanıma uygun değildir.

Bu kurallar trend çizgilerini doğru bir şekilde çizmenize yardımcı olacaktır!

3) Trend çizgileri boyunca ticaret yapın
____________________________

Temelde farklı iki olasılığımız var:
A) Çizgiyi trend yönünde girmek için destek (direnç) seviyesi olarak kullanın
B) Trendin kırılmasında (tersine çevrilmesi) oynamak için Forex trend çizgisini kullanın.

Her iki yöntem de, "onları nasıl doğru pişireceğinizi" biliyorsanız, iyidir.

Böylece iki noktadan bir çizgi çizdik. Fiyat çizgiye temas ettiği anda mevcut trend doğrultusunda pazara girmemiz gerekiyor. Girmek için, "limitli veya satış limitli" emirleri kullanıyoruz.

Burada her şey basit ve açık. Hatırlanması gereken tek şey, fiyat trend çizgisini ne kadar sık \u200b\u200btest ederse, ondan başlayarak, bir sonraki dokunuşun bir satır kırılma olasılığı o kadar yüksek olmasıdır!

Trend çizgisini kırmak için oynamak istiyorsak, biraz farklı davranmamız gerekir:
1) Çizgiye dokunmayı beklemek
2) Bir ribaund bekleniyor
3) Ortaya çıkan onay kutusuna bir satın alma-durdurma (veya satış durdurma) emri koyun
Resme dikkat edin.

Bir kene oluşana kadar bekledik ve maksimumda bir satın alma durdurma emri verdik.

Bir süre sonra sipariş tetiklendi ve pazara girdik.
Doğal bir soru ortaya çıkıyor - piyasaya hemen girmek neden imkansızdı?
Mesele şu ki, trend çizgisini test etmenin başarılı olup olmayacağını bilmiyoruz. Ve "tik" i bekledikten sonra, başarı şansımızı keskin bir şekilde artırırız (yanlış sinyalleri filtreleriz).

4) Yöntemin öznelliği
_________________________

Basit görünüyor? Aslında bu yöntemi kullanırken şu zorluklarla karşılaşacağız:
A) Çizgi eğiminin açısı (her zaman farklı eğimlerle eğilim çizgileri oluşturabilirsiniz.
B) Trend çizgisinin dökümü olarak kabul edilen şey nedir (fiyat bunu bir kırılma olarak kabul etmek için çizgiyi kaç puan veya yüzde "kırmalıdır")?
C) Bir hat ne zaman "modası geçmiş" olarak değerlendirilmeli ve yenisi inşa edilmelidir?

Resme dikkat edin.

Kırmızı çizgi stil seçeneklerinden birini gösterir. Deneyimsiz bir tüccar böyle bir çizgi çekebilir (ve bedelini ödeyebilir).

Bu durumda pratik deneyim önemlidir. Yani her şeyi birkaç basit yapım kuralına indirgemek mümkün değildir. Bu nedenle trend çizgisi göstergesi yoktur. Daha doğrusu var olabilir, ancak onları "çarpık" ve yanlış bir şekilde oluşturur. Bu teknik başlangıçta tüccarın deneyimi ve becerisi için "keskinleştirilmiştir".

Kişisel olarak, eğilim çizgilerini bağımsız bir araç olarak nadiren kullanıyorum. Ancak yine de basit bir nedenden dolayı onlardan bahsediyorum. Mesele şu ki, diğer birçok tüccar bunları kullanıyor. Bu nedenle, biz (siz ve ben) rakiplerimizin tekniklerinin farkında olmalıyız.

Ticaretinizde bu araca ihtiyaç olup olmadığı size kalmış!

İyi şanslar ve başarılı ticaretler. Arthur.
blog-forex.org

İlgili girişler:

Trend ticaret konsepti (video)

Trend kalıpları (rakamlar)

Bu konuyla ilgili video:

Bölüm 10. Programa göre formüllerin seçimi. Trend çizgisi

Önceki12345678910111213141516Sonraki

Yukarıda ele alınan problemler için, bir denklem veya bir denklem sistemi oluşturmak mümkündü.

Ancak çoğu durumda, pratik problemleri çözerken sadece deneysel (ölçüm sonuçları, istatistiksel, referans, deneysel) veriler vardır. Bunları kullanarak, belirli bir yakınlık ölçüsü ile, bir çözüm bulmak, modellemek, çözümleri değerlendirmek ve tahminler için kullanılabilecek deneysel bir formül (denklem) geri yüklemeye çalışırlar.

Ampirik Formül Seçim Süreci P (x) deneyimli bağımlılık için F (x) aranan yaklaşım(kenar yumuşatma). Excel, bilinmeyen bağımlılıklar için grafikler ve çok sayıda bilinmeyenli bağımlılıklar için gruptaki işlev çiftlerini kullanır. İstatistiksel LINEST ve TREND, LGRFPRIBL ve GROWTH.

Bu bölümde, Excel grafikleri kullanılarak deneysel verilerin yaklaştırılması tartışılmaktadır: verilere dayanarak, bir grafik oluşturulur ve onunla eşleştirilir. trend çizgisi yani deneysel bağımlılığa maksimum yakınlık derecesiyle yaklaşan bir yaklaştırma işlevi.

Seçilen işlevin yakınlık derecesi tahmin edilir belirleme katsayısı R 2 ... Başka teorik değerlendirme yoksa, katsayılı bir fonksiyon R 2eğilim çizgisi kullanılarak formüllerin seçilmesinin, hem deneysel formülün biçimini oluşturmanıza hem de bilinmeyen parametrelerin sayısal değerlerini belirlemenize izin verdiğini unutmayın.

Excel, 5 tür yaklaştırma işlevi sağlar:

1. Doğrusal - y \u003d cx + b... Bu, verilerin büyümesini ve azalmasını sabit bir oranda yansıtan en basit işlevdir.

2. Polinom - y \u003d c 0 + c 1 x + c 2 x 2 +… + c 6 x 6... İşlev, dönüşümlü olarak artan ve azalan verileri açıklar. 2. dereceden bir polinomun bir ekstremumu (min veya maks), 3. derece - 2'ye kadar ekstremma, 4. derece - 3'e kadar vb. Olabilir.

3. Logaritmik - y \u003d cln x + b... Bu işlev, hızla artan (azalan) verileri açıklar ve ardından stabilize olur.

4. Güç - y \u003d cx b, (x\u003e 0 ve y\u003e 0). İşlev, verileri sürekli artan (azalan) bir büyüme oranıyla yansıtır.

5. Üstel - y \u003d ce bx, (e Doğal logaritmanın temelidir). İşlev, hızla büyüyen (azalan) verileri açıklar ve daha sonra stabilize olur.

Çizim

Regresyon analizi

Regresyon denklemi Y itibaren X işlevsel bağımlılık denir y \u003d f (x)ve grafiği bir regresyon çizgisidir.

Excel, oldukça kabul edilebilir kalitede çizelgeler ve grafikler oluşturmanıza olanak tanır. Excel'in özel bir aracı vardır - kullanıcının rehberliği altında bir grafik veya grafik oluşturma sürecinin dört aşamasını da uyguladığı Grafik Sihirbazı.

Kural olarak, çizim, üzerine çizilmesi gereken verileri içeren aralığın seçimi ile başlar. Böyle bir başlangıç, olay örgüsünün ilerideki seyrini basitleştirir. Bununla birlikte, orijinal verilere sahip aralık, diyaloğun ikinci aşamasında aşağıdakilerle bölünebilir: DİYAGRAM MASTER... Excel 2003'te DİYAGRAMLARIN USTASI Menüde yer alan buton veya sekme tıklanarak grafik oluşturulabilir INSERT ve açılan listede öğeyi bulun DİYAGRAM.Excel 2007'de sekmeyi de buluyoruz INSERT(şek. 31).

Şekil: 31. DİYAGRAMLARIN USTASI Excel 2007'de

Bu verilerin bitişik satırlarda (sütunlar veya satırlar) olduğu bir dizi kaynak veri seçmenin en kolay yolu, aralığın sol üst hücresine tıklamak ve ardından fare işaretçisini aralığın sağ alt hücresine sürüklemektir. Bitişik olmayan satırlarda bulunan verileri seçerken, fare imleci Ctrl tuşunu basılı tutarken seçilen satırlar boyunca sürüklenir. Veri serilerinden birinde adı olan bir hücre varsa, seçilen satırların geri kalanında boş olsa bile karşılık gelen bir hücre olmalıdır.

Regresyon analizi için en iyisi bir Nokta grafiği kullanmaktır (Şekil 30). Excel, bunu oluştururken, seçilen kaynak veri aralığının ilk satırını, grafiklerinin çizilmesi gereken (tüm işlevler için aynı küme) işlevlerin bağımsız değişkeninin bir dizi değeri olarak algılar. Aşağıdaki satırlar, işlevlerin kendi değer kümeleri olarak algılanır (her satır, seçilen aralığın ilk satırında bulunan bağımsız değişkenin verilen değerlerine karşılık gelen işlevlerden birinin değerlerini içerir).

Excel 2007'de eksen başlıkları menü sekmesine yerleştirilir YERLEŞİM (şek. 32).

Şekil: 32. Excel 2007'de grafik ekseni başlıklarını ayarlama

Matematiksel bir model elde etmek için, grafikte bir eğilim çizgisi çizmeniz gerekir. Excel 2003 ve 2007'de, grafik noktalarına sağ tıklamanız gerekir. Ardından Excel 2003'te, içinden seçtiğimiz öğelerin bir listesini içeren bir sekme görünecektir. TREND SATIRI EKLE (şek. 33).

Şekil: 33. TREND SATIRI EKLE

Öğeye tıkladıktan sonra TREND SATIRI EKLEbir pencere görünecek TREND HATTI(şek. 34). TÜR sekmesinde, aşağıdaki çizgi türlerini seçebilirsiniz: doğrusal, logaritmik, üstel, kuvvet, polinom, doğrusal filtreleme.

Şekil: 34. Pencere TREND HATTI Excel 2003'te

Sekmede SEÇENEKLER (Şekil 35), ŞEMADA DENKLEMİ GÖSTER öğelerinin karşısındaki onay kutusunu ayarlayın, ardından bu bağımlılığın matematiksel modeli grafikte görünecektir. Ayrıca öğenin karşısındaki kutuyu da işaretleyin DİYAGRAMDA YAKLAŞIMIN GÜVENİLİRLİĞİNİN DEĞERİNİ GÖSTERİN (R ^ 2).Yaklaşık güven değeri 1'e ne kadar yakınsa, seçilen eğri grafikteki noktalara o kadar yaklaşır. Ardından, düğmeye tıklayın tamam... Trend çizgisi, karşılık gelen denklem ve yaklaşım güvenilirliğinin değeri grafikte görünecektir.

Şekil: 35. Sekme SEÇENEKLER

Excel 2007'de, grafiğin noktalarına sağ tıkladıktan sonra, menü öğelerinin bir listesi görünür. TREND ÇİZGİSİ EKLE SEÇME (şek. 36).

Şekil: 36. TREND SATIRI EKLE

Şekil: 37. Sekme TREND ÇİZGİSİ PARAMETRELERİ

Gerekli onay kutularını ayarlayın ve düğmeyi tıklayın KAPAT.

Trend çizgisi, karşılık gelen denklem ve yaklaşım güvenilirliğinin değeri grafikte görünecektir.

Fiyat eğilimlerini görsel olarak göstermek için bir eğilim çizgisi kullanılır. Teknik analiz unsuru, analiz edilen göstergenin ortalama değerlerinin geometrik bir görüntüsüdür.

Excel'de bir grafiğe eğilim çizgisinin nasıl ekleneceğine bir göz atalım.

Bir grafiğe bir eğilim çizgisi eklemek

Örneğin 2000 yılından bu yana ortalama petrol fiyatlarını açık kaynaklardan alalım. Analiz için verileri tabloya giriyoruz:

Excel'deki bir eğilim çizgisi, yaklaşık bir işlevin grafiğidir. Ne için - istatistiksel verilere dayalı tahminler yapmak. Bunun için hattın uzatılması ve değerlerinin belirlenmesi gerekmektedir.

R2 \u003d 1 ise, yaklaşım hatası sıfırdır. Örneğimizde, doğrusal bir uyum seçmek düşük güven ve kötü bir sonuç verdi. Tahmin yanlış olacaktır.

Dikkat!!! Trend çizgisi, aşağıdaki grafik ve tablo türlerine eklenemez:

• taç yaprağı;
• dairesel;
• yüzey;
• halka şeklinde;
• ses;
• yığılmış.



Excel'de eğilim çizgisi denklemi

Yukarıdaki örnekte, yalnızca algoritmayı açıklamak için doğrusal bir yaklaşım seçilmiştir. Güvenilirliğin değerinin gösterdiği gibi, seçim tamamen başarılı olmadı.

Kullanıcı girdisindeki değişikliklerin eğilimini en doğru şekilde gösteren ekran türünü seçmelisiniz. Seçenekleri bulalım.

Doğrusal yaklaşım

Geometrik görüntüsü düzdür. Bu nedenle, sabit bir oranda yükselen veya düşen bir metriği göstermek için doğrusal bir uyum kullanılır.

Yöneticinin 10 ay boyunca imzaladığı koşullu sözleşme sayısını düşünün:

Excel tablosundaki verilere dayanarak, bir dağılım grafiği oluşturalım (çizgi türünü göstermeye yardımcı olacaktır):

Grafiği seçin - "eğilim çizgisi ekle". Parametrelerde doğrusal türü seçin. Yaklaşımın doğruluğunun değerini ve Excel'deki eğilim çizgisinin denklemini ekleyin ("Parametreler" penceresinin altındaki kutuları işaretlemeniz yeterlidir).

Sonucu alıyoruz:

Not! Doğrusal yaklaşım türü ile, veri noktaları mümkün olduğunca düz çizgiye yakın yerleştirilir. Bu görünüm aşağıdaki denklemi kullanır:

y \u003d 4,503x + 6,1333

• 4.503 eğim göstergesidir;
• 6.1333 - ofsetler;
• y - değerler dizisi,
• x - dönem numarası.

Grafikteki düz çizgi, yöneticinin işinin kalitesindeki istikrarlı artışı göstermektedir. Yaklaşımın doğruluğunun değeri 0.9929'a eşittir, bu da hesaplanan düz çizginin ilk verilerle iyi bir çakışma olduğunu gösterir. Tahminler doğru olmalıdır.

Örneğin 11. periyotta sonuçlandırılan sözleşmelerin sayısını tahmin etmek için denklemde x yerine 11 koymanız gerekir. Hesaplamalar sırasında 11. periyotta bu yöneticinin 55-56 sözleşme yapacağını öğreniyoruz.

Üstel eğilim çizgisi

Bu tür, girdi değerleri sürekli artan bir oranda değiştiğinde faydalı olacaktır. Sıfır veya negatif özelliklerin varlığında üstel yaklaşım uygulanmaz.

Excel'de üstel bir eğilim çizgisi oluşturalım. Örneğin, X bölgesindeki üretken elektrik arzının koşullu değerlerini alalım:

Bir program oluşturuyoruz. Üstel bir çizgi ekleyin.

Denklem aşağıdaki gibidir:

y \u003d 7,6403e ^ -0,084x

• 7.6403 ve -0.084 sabittir;
• e, doğal logaritmanın temelidir.

Yaklaşık güvenilirliğin göstergesi 0,938 idi - eğri verilere karşılık gelir, hata minimumdur ve tahminler doğru olacaktır.

Excel'de logaritmik eğilim çizgisi

Göstergedeki aşağıdaki değişiklikler için kullanılır: önce hızlı büyüme veya düşüş, sonra - göreceli istikrar. Optimize edilmiş eğri, değerin bu davranışına iyi uyum sağlar. Logaritmik trend, piyasaya yeni sürülen yeni bir ürünün satışını tahmin etmek için uygundur.

İlk aşamada, üreticinin görevi müşteri tabanını genişletmektir. Bir ürünün müşterisi olduğunda, saklanması ve servise alınması gerekir.

Koşullu bir ürünün satışlarını tahmin etmek için bir grafik oluşturalım ve logaritmik bir eğilim çizgisi ekleyelim:

R2, minimum yaklaşım hatasını gösteren 1'e (0,9633) değer olarak yakındır. Sonraki dönemlerde satış hacimlerini tahmin edeceğiz. Bunu yapmak için denklemdeki x yerine nokta numarasını yazmanız gerekir.

Örneğin:

Periyot	14	15	16	17	18	19	20
Tahmin	1005,4	1024,18	1041,74	1058,24	1073,8	1088,51	1102,47

Tahmin rakamlarını hesaplamak için formun bir formülü kullanıldı: \u003d 272.14 * LN (B18) +287.21. B18, dönem numarasıdır.

Excel'de polinom eğilim çizgisi

Bu eğri, değişken artış ve azalma ile karakterizedir. Polinomlar (polinomlar) için derece belirlenir (maksimum ve minimum değerlerin sayısı ile). Örneğin, bir uç (minimum ve maksimum) ikinci derece, iki uç nokta üçüncü derece, üçü dördüncü derece.

Excel'deki polinom eğilimi, kararsız bir değere sahip büyük bir veri kümesini analiz etmek için kullanılır. İlk değerler kümesi (petrol fiyatları) örneğine bakalım.

Yaklaşım güvenilirliğinin böyle bir değerini (0.9256) elde etmek için 6. dereceyi koymak gerekliydi.

Ancak bu eğilim, az çok doğru tahminler yapmayı mümkün kılıyor.

Teorik arka plan

Uygulamada, çeşitli süreçleri modellerken - özellikle ekonomik, fiziksel, teknik, sosyal - fonksiyonların yaklaşık değerlerini hesaplamanın bir veya başka bir yöntemi, bazı sabit noktalarda bilinen değerlerinden yaygın olarak kullanılır.

Yaklaşık fonksiyonlarla ilgili bu tür sorunlar sıklıkla ortaya çıkar:

• deney sonucunda elde edilen tablo verilerine göre incelenen işlemin karakteristik değerlerinin değerlerini hesaplamak için yaklaşık formüller oluştururken;
• sayısal entegrasyon, farklılaştırma, diferansiyel denklemleri çözme vb. için;
• dikkate alınan aralığın ara noktalarındaki fonksiyonların değerlerini hesaplamak gerektiğinde;
• sürecin karakteristik değerlerinin değerlerini, dikkate alınan aralığın dışında belirlerken, özellikle tahmin ederken.

Tabloda verilen belirli bir süreci simüle etmek için, en küçük kareler yöntemine dayalı olarak bu süreci yaklaşık olarak tanımlayan bir fonksiyon inşa ederseniz, buna yaklaşık bir fonksiyon (regresyon) adı verilir ve yaklaşık fonksiyonlar oluşturma problemi bir yaklaşıklık problemidir.

Bu makale MS Excel paketinin bu tür problemleri çözme yeteneklerini tartışmakta, ayrıca tabloya özgü fonksiyonlar için (regresyon analizinin temeli olan) regresyonları oluşturmak (oluşturmak) için yöntemler ve teknikler verilmektedir.

Excel, regresyonları çizmek için iki seçeneğe sahiptir.

1. Seçilen regresyonları (eğilim çizgileri - eğilim çizgileri), incelenen süreç karakteristiği için veri tablosu temelinde oluşturulan diyagrama eklemek (yalnızca yapılandırılmış bir diyagram varsa kullanılabilir);
2. Doğrudan kaynak veri tablosundan regresyonları (eğilim çizgileri) elde etmek için Excel çalışma sayfasının yerleşik istatistiksel işlevlerini kullanın.

Bir grafiğe eğilim çizgileri ekleme

Belirli bir süreci açıklayan ve bir diyagramla temsil edilen bir veri tablosu için, Excel şunları yapmanızı sağlayan etkili bir regresyon analiz aracına sahiptir:

• en küçük kareler yöntemini temel alarak inşa edin ve diyagrama, incelenen süreci değişen doğruluk dereceleri ile modelleyen beş tür regresyon ekleyin;
• oluşturulan regresyon denklemini diyagrama ekleyin;
• grafikte görüntülenen verilerle seçilen regresyonun ne derece eşleşeceğini belirleyin.

Excel grafiğinin verilerine dayanarak, denklem tarafından verilen doğrusal, polinom, logaritmik, kuvvet, üstel regresyon türleri elde etmenizi sağlar:

y \u003d y (x)

burada x, genellikle bir doğal sayı dizisinin (1; 2; 3; ...) değerlerini alan ve örneğin, incelenen sürecin (özellikler) çalışma süresinin geri sayımını üreten bağımsız bir değişkendir.

1 ... Doğrusal regresyon, sabit bir oranda artan veya azalan modelleme özellikleri için iyidir. Bu, üzerinde çalışılan sürecin inşa edilmesi gereken en basit modelidir. O

y \u003d mx + b

burada m, apsis eksenine doğrusal regresyonun eğim açısının tanjantıdır; b - doğrusal regresyonun ordinat ekseni ile kesişme noktasının koordinatı.

2 ... Polinom eğilim çizgisi, birkaç farklı uç noktaya (en yüksek ve en düşük) sahip özellikleri tanımlamak için kullanışlıdır. Polinom derecesinin seçimi, incelenen karakteristiğin ekstremma sayısı ile belirlenir. Bu nedenle, ikinci dereceden bir polinom, yalnızca bir maksimum veya minimuma sahip bir süreci iyi tanımlayabilir; üçüncü dereceden polinom - ikiden fazla ekstrema; dördüncü derecenin polinomu - üçten fazla ekstrema vb.

Bu durumda trend çizgisi denkleme göre çizilir:

y \u003d c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

burada c0, c1, c2, ... c6 katsayıları sabittir ve değerleri inşaat sırasında belirlenir.

3 ... Logaritmik eğilim çizgisi, değerleri önce hızla değişen ve ardından kademeli olarak stabilize olan özellikleri simüle etmek için başarıyla kullanılır.

Denkleme göre oluşturulmuştur:

y \u003d c ln (x) + b

4 ... Bir güç kanunu eğilim çizgisi, incelenen bağımlılığın değerleri, büyüme oranındaki sabit bir değişiklikle karakterize edilirse iyi sonuçlar verir. Bu tür bir ilişkinin bir örneği, muntazam hızlanan araç hareketinin bir grafiğidir. Veriler sıfır veya negatif değerler içeriyorsa, bir güç eğilim çizgisi kullanamazsınız.

Denkleme göre oluşturulmuştur:

y \u003d c xb

burada b, c katsayıları sabittir.

5 ... Verilerdeki değişim oranı sürekli arttığında üstel bir eğilim çizgisi kullanılmalıdır. Sıfır veya negatif değerler içeren veriler için bu tür bir yaklaşım da geçerli değildir.

Denkleme göre oluşturulmuştur:

y \u003d c ebx

burada b, c katsayıları sabittir.

Bir eğilim çizgisi seçerken, Excel, yaklaşımın doğruluğunu karakterize eden R2 değerini otomatik olarak hesaplar: R2'nin değeri bire ne kadar yakınsa, eğilim çizgisi incelenen sürece o kadar güvenilir bir şekilde yaklaşır. Gerekirse, R2 değeri her zaman grafikte görüntülenebilir.

Formül ile belirlenir:

Bir veri serisine bir eğilim çizgisi eklemek için:

• bir dizi veriye dayalı bir grafiği etkinleştirin, yani grafik alanının içini tıklayın. Grafik öğesi ana menüde görünecektir;
• bu öğeye tıkladıktan sonra, ekranda Trend çizgisi ekle komutunu seçmeniz gereken bir menü görünecektir.

Aynı eylemler, fare imlecini veri serilerinden birine karşılık gelen grafiğin üzerine getirip sağ fare düğmesini tıklatarak kolayca gerçekleştirilebilir; Açılan içerik menüsünde Trend çizgisi ekle komutunu seçin. Trend çizgisi iletişim kutusu, Tip sekmesi genişletilmiş olarak ekranda görünecektir (Şekil 1).

Bundan sonra gerekli:

Tür sekmesinde gerekli eğilim çizgisi türünü seçin (varsayılan olarak Doğrusal tür seçilidir). Polinom tipi için Derece alanında, seçili polinomun derecesini belirtin.

1 ... Seri Üzerinde Çizilen kutusu, söz konusu grafiğin tüm veri serilerini listeler. Belirli bir veri serisine bir eğilim çizgisi eklemek için, Dizide Çizilen alanında adını seçin.

Gerekirse, Parameters sekmesine giderek (Şekil 2), trend çizgisi için aşağıdaki parametreleri ayarlayabilirsiniz:

• yaklaşık (düzleştirilmiş) eğri alanının Adı'ndaki eğilim çizgisinin adını değiştirin.
• tahmin alanında tahmin için dönem sayısını (ileri veya geri) ayarlayın;
• eğilim çizgisinin denklemini diyagram alanında görüntüleyin; bunun için Denklemi diyagramda göster onay kutusunu etkinleştirmeniz gerekir;
• yaklaşık güvenilirlik değerini (R ^ 2) diyagrama yerleştirmek için onay kutusunu etkinleştirmeniz gereken diyagram alanında R2 yaklaşık güvenilirliğinin değerini görüntüleyin;
• eğilim çizgisinin Y ekseni ile kesişme noktasını ayarlayın, bunun için eğrinin Y ekseni ile bir noktada kesişimini etkinleştirmeniz gerekir onay kutusu
• iletişim kutusunu kapatmak için Tamam düğmesine tıklayın.

Önceden oluşturulmuş bir eğilim çizgisini düzenlemeye başlamanın üç yolu vardır:

trend çizgisini seçtikten sonra Biçim menüsünden Seçilen trend çizgisi komutunu kullanın;

trend çizgisine sağ tıklanarak çağrılan bağlam menüsünden Trend çizgisini biçimlendir komutunu seçin;

trend çizgisine çift tıklayarak.

Eğilim Çizgisi Formatı iletişim kutusu (Şekil 3) ekranda üç sekme içeren görünecektir: Görünüm, Tür, Parametreler ve son ikisinin içeriği Eğilim Çizgisi iletişim kutusunun benzer sekmeleriyle tamamen örtüşür (Şekil 1-2). Görünüm sekmesinde çizgi türünü, rengini ve kalınlığını ayarlayabilirsiniz.

Zaten oluşturulmuş bir trend çizgisini silmek için, silinecek trend çizgisini seçin ve Sil tuşuna basın.

Değerlendirilen regresyon analizi aracının avantajları şunlardır:

• bunun için bir veri tablosu oluşturmadan grafikler üzerinde bir eğilim çizgisi çizmenin göreceli kolaylığı;
• oldukça geniş bir önerilen eğilim çizgisi türleri listesi ve bu liste en sık kullanılan regresyon türlerini içerir;
• Çalışılan sürecin davranışını gelişigüzel (sağduyu dahilinde) ileriye doğru ve geriye doğru adım sayısı için tahmin etme yeteneği;
• eğilim çizgisinin denklemini analitik bir biçimde elde etme yeteneği;
• gerekirse, yaklaşıklığın güvenilirliğinin bir değerlendirmesini elde etme olasılığı.

Dezavantajlar aşağıdaki noktaları içerir:

bir eğilim çizgisinin inşası, yalnızca bir dizi veriye dayalı bir şema varsa gerçekleştirilir;

Çalışılan karakteristik için elde edilen trend çizgisi denklemlerine dayalı veri serisi oluşturma süreci biraz karışıktır: aranan regresyon denklemleri, orijinal veri serilerinin değerlerindeki her değişiklikle, ancak yalnızca diyagram alanı içinde güncellenirken, veri serileri eski çizgi denklemine göre oluşturulur. eğilim değişmeden kalır;

pivotChart raporlarında, bir grafiğin veya bağlantılı bir PivotTable raporunun görünümünü değiştirdiğinizde, mevcut eğilim çizgileri korunmaz; yani, eğilim çizgilerini çizmeden veya PivotChart raporunu başka bir şekilde biçimlendirmeden önce, rapor düzeninin gereksinimlerinizi karşıladığından emin olmalısınız.

Trend çizgileri, grafikler, çubuk grafikler, düz normalleştirilmemiş alan grafikleri, çubuk, dağılım, kabarcık ve hisse senedi grafiklerinde sunulan veri serilerini desteklemek için kullanılabilir.

3-B, Normalleştirilmiş, Radar, Pasta ve Halka grafiklerinde veri serilerine eğilim çizgileri ekleyemezsiniz.

Yerleşik Excel işlevlerini kullanma

Excel ayrıca, grafik alanının dışındaki eğilim çizgilerini çizmek için bir regresyon analizi aracı sağlar. Bu amaç için bir dizi çalışma sayfası istatistiksel işlevi kullanılabilir, ancak bunların tümü yalnızca doğrusal veya üstel regresyonların oluşturulmasına izin verir.

Excel, doğrusal regresyon oluşturmak için çeşitli işlevlere sahiptir, özellikle:

• AKIM;
• LINEST;
• TILT ve CUT.

Üstel bir eğilim çizgisi oluşturmak için çeşitli işlevler, özellikle:

• BÜYÜME;
• LGRFPRIBL.

TREND ve GROWTH işlevlerini kullanarak regresyon oluşturma yöntemlerinin pratik olarak aynı olduğu unutulmamalıdır. LINEST ve LGRFPRIBL çift işlevler için de aynısı söylenebilir. Bu dört işlev için, dizi formülleri gibi Excel özellikleri, regresyon sürecini biraz karmaşık hale getiren bir değerler tablosu oluşturmak için kullanılır. Ayrıca, bizim görüşümüze göre, doğrusal regresyonun inşasının, eğim ve INTERCEPT fonksiyonlarını kullanarak gerçekleştirilmesinin en kolay olduğunu unutmayın; burada, birincisi doğrusal regresyonun eğimini belirler ve ikincisi, ordinat eksenindeki regresyon tarafından kesilen segmenttir.

Yerleşik regresyon analizi aracının faydaları şunları içerir:

• eğilim çizgilerini belirleyen tüm yerleşik istatistiksel işlevler için incelenen karakteristiğin aynı tür veri dizisi oluşumunun oldukça basit bir süreci;
• oluşturulan veri serilerine göre trend çizgilerini çizmek için standart teknik;
• İleri veya geri doğru gerekli adım sayısı için çalışılan sürecin davranışını tahmin etme yeteneği.

Dezavantajı, Excel'in diğer (doğrusal ve üstel) eğilim çizgisi türlerini oluşturmak için yerleşik işlevlere sahip olmamasıdır. Bu durum genellikle, çalışılan sürecin yeterince doğru bir modelinin seçilmesine ve gerçeğe yakın tahminlerin elde edilmesine izin vermez. Ayrıca, TREND ve GROWTH işlevlerini kullanırken eğilim çizgisi denklemleri bilinmemektedir.

Yazarların, farklı derecelerde tamlık ile regresyon analizinin gidişatını sunmak için makalenin amacını belirlemediğine dikkat edilmelidir. Ana görevi, belirli örnekler kullanarak Excel'in yaklaştırma problemlerini çözmedeki yeteneklerini göstermektir; Excel'in regresyon ve tahmin oluşturmak için hangi etkili araçlara sahip olduğunu gösterin; derin regresyon analizi bilgisine sahip olmayan bir kullanıcı tarafından bile bu tür sorunların ne kadar kolay çözülebileceğini göstermek için.

Belirli problemleri çözme örnekleri

Excel paketinin listelenen araçlarını kullanarak belirli sorunları çözmeyi düşünelim.

Problem 1

1995-2002 yılları arasında bir nakliye şirketinin karına ilişkin bir veri tablosu ile. aşağıdakileri yapmanız gerekir.

1. Bir diyagram oluşturun.
2. Grafiğe doğrusal ve polinom (ikinci dereceden ve kübik) eğilim çizgileri ekleyin.
3. Trend çizgisi denklemlerini kullanarak, 1995-2004 için her trend çizgisi için kurumsal karlar hakkında tablo verileri elde edin.
4. İşletmenin 2003 ve 2004 karı için bir tahmin yapın.

Sorunun çözümü

1. Excel çalışma sayfasının A4: C11 hücre aralığında, Şekil 2'de gösterilen çalışma sayfasını girin. 4.
2. B4: C11 hücre aralığını seçtikten sonra bir diyagram oluşturuyoruz.
3. Oluşturulan grafiği etkinleştiririz ve yukarıda açıklanan yönteme göre, Trend çizgisi iletişim kutusundaki trend çizgisi türünü seçtikten sonra (bkz. Şekil 1), sırasıyla grafiğe doğrusal, ikinci dereceden ve kübik trend çizgileri ekleriz. Aynı iletişim penceresinde, Parametreler sekmesini açın (bkz.Şekil 2), Yaklaşık (düzleştirilmiş) eğri alanının Adı alanına eklenen trendin adını girin ve Tahmin: dönemler alanına 2 değerini ayarlayın, çünkü iki kişi için bir kar tahmini yapılması planlandı önümüzdeki yıllar. Regresyon denklemini ve yaklaşık güven değeri R2'yi diyagram alanında görüntülemek için, denklemi ekranda göstermek için onay kutularını açın ve yaklaşık güven değerini (R ^ 2) diyagrama yerleştirin. Daha iyi görsel algı için, Eğilim Çizgisi Formatı iletişim kutusunun Görünüm sekmesini kullandığımız, inşa edilen eğilim çizgilerinin türünü, rengini ve kalınlığını değiştiririz (bkz. Şekil 3). Eklenen trend çizgileri ile ortaya çıkan diyagram, Şekil 2'de gösterilmiştir. beş.
4. 1995-2004 için her trend çizgisi için kurumsal karlar hakkında tablo verileri elde etmek. Şekil 2'de gösterilen eğilim çizgisi denklemlerini kullanalım. 5. Bunu yapmak için, D3: F3 aralığındaki hücrelerde, seçilen trend çizgisinin türü hakkında metin bilgilerini girin: Doğrusal eğilim, İkinci dereceden eğilim, Kübik eğilim. Ardından, D4 hücresine doğrusal regresyon formülünü girin ve dolgu işaretleyicisini kullanarak, bu formülü D5: D13 hücre aralığına göreceli referanslarla kopyalayın. D4: D13 hücre aralığından doğrusal bir regresyon formülüne sahip her hücrenin, karşılık gelen hücreyi A4: A13 aralığından bağımsız değişken olarak aldığına dikkat edilmelidir. Benzer şekilde, ikinci dereceden regresyon için E4: E13 hücre aralığı doldurulur ve kübik regresyon için hücre aralığı F4: F13 doldurulur. Böylelikle işletmenin 2003 ve 2004 karı için tahmin yapılmıştır. üç eğilim kullanarak. Elde edilen değerler tablosu Şekil 2'de gösterilmiştir. 6.

Problem 2

1. Bir diyagram oluşturun.
2. Grafiğe logaritmik, kuvvetli ve üstel eğilim çizgileri ekleyin.
3. Elde edilen trend çizgilerinin denklemlerini ve bunların her biri için yaklaşım güvenilirliği R2 değerlerini türetiniz.
4. Trend çizgisi denklemlerini kullanarak, 1995-2002 için her bir trend çizgisi için kurumsal karlarla ilgili tablo verileri elde edin.
5. Bu trend çizgilerini kullanarak şirketin 2003 ve 2004 karını tahmin edin.

Sorunun çözümü

Problem 1 çözümünde verilen metodolojiyi takiben, eklenen logaritmik, güç ve üstel trend çizgileri içeren bir diyagram elde ederiz (Şekil 7). Ayrıca, trend çizgilerinin elde edilen denklemlerini kullanarak, 2003 ve 2004 için tahmin edilen değerler de dahil olmak üzere, işletmenin karı için değerler tablosunu dolduruyoruz. (şek. 8).

İncirde. 5 ve şek. logaritmik bir eğilime sahip modellerin yaklaşık güvenilirliğin en küçük değerine karşılık geldiği görülebilir.

R2 \u003d 0.8659

En büyük R2 değerleri, polinom eğilimi olan modellere karşılık gelir: ikinci dereceden (R2 \u003d 0,9263) ve kübik (R2 \u003d 0,933).

Sorun 3

Görev 1'de verilen, 1995-2002 yılları arasında bir nakliye şirketinin karına ilişkin veri tablosu ile aşağıdaki işlemleri gerçekleştirmelisiniz.

1. TREND ve GROWTH işlevlerini kullanarak doğrusal ve üstel eğilim çizgisi için veri serileri alın.
2. EĞİLİM ve BÜYÜME işlevlerini kullanarak, şirketin 2003 ve 2004 karını tahmin edin.
3. İlk veriler ve alınan veri serileri için bir şema oluşturun.

Sorunun çözümü

Görev 1'in çalışma sayfasını kullanalım (bkz. Şekil 4). TREND işleviyle başlayalım:

1. işletmenin karına ilişkin bilinen verilere karşılık gelen, TREND işlevinin değerleriyle doldurulması gereken D4: D11 hücre aralığını seçin;
2. ekle menüsünden İşlev komutunu çağırın. Görünen İşlev Sihirbazı iletişim kutusunda, İstatistik kategorisinden TREND işlevini seçin ve ardından Tamam düğmesine tıklayın. Aynı işlem, standart araç çubuğundaki (İşlev Ekle) düğmesine basılarak da gerçekleştirilebilir.
3. Görünen İşlev Bağımsız Değişkenleri iletişim kutusunda, Known_values_y alanına C4: C11 hücre aralığını girin; Bilinen_x alanında - B4: B11 hücre aralığı;
4. girilen formülü bir dizi formülü haline getirmek için + + tuş kombinasyonunu kullanın.

Formül çubuğuna girdiğimiz formül şöyle görünecektir: \u003d (TREND (C4: C11; B4: B11)).

Sonuç olarak, D4: D11 hücre aralığı, TREND işlevinin karşılık gelen değerleriyle doldurulur (Şekil 9).

Şirketin 2003 ve 2004 karını tahmin etmek. bu gerekli:

1. tREND işlevi tarafından tahmin edilen değerlerin girileceği D12: D13 hücre aralığını seçin.
2. tREND işlevini çağırın ve görünen İşlev Bağımsız Değişkenleri iletişim kutusunda Bilinen_değerler_y alanına C4: C11 hücre aralığını girin; Bilinen_x alanında - B4: B11 hücre aralığı; ve Yeni_x_değerleri alanı B12: B13 hücre aralığını içerir.
3. ctrl + Shift + Enter klavye kısayolunu kullanarak bu formülü bir dizi formülüne dönüştürün.
4. Girilen formül şöyle görünecektir: \u003d (TREND (C4: C11; B4: B11; B12: B13)) ve D12: D13 hücre aralığı, TREND işlevinin tahmin edilen değerleriyle doldurulacaktır (bkz. Şekil 9).

Benzer şekilde, doğrusal olmayan bağımlılıkların analizinde kullanılan ve doğrusal analog TREND ile tam olarak aynı şekilde çalışan BÜYÜME işlevi kullanılarak bir veri serisi doldurulur.

Şekil 10, formülleri görüntüleme modundaki tabloyu göstermektedir.

İlk veriler ve elde edilen veri serileri için, Şekil 2'de gösterilen diyagram. on bir.

Sorun 4

Karayolu taşımacılığı firmasının sevk servisi tarafından mevcut ayın 1'den 11'ine kadar olan süre boyunca hizmet başvurularının alınmasına ilişkin veri tablosu ile aşağıdaki işlemleri yapmanız gerekir.

1. Doğrusal regresyon için veri serilerini alın:tILT ve INTERCEPT işlevlerini kullanarak; DOT işlevini kullanarak.
2. LGRFPRIBL işlevini kullanarak üstel regresyon için bir veri serisi alın.
3. Yukarıdaki işlevleri kullanarak, cari ayın 12. ila 14. günü arasındaki dönem için gönderim hizmetindeki başvuruların alınması hakkında bir tahmin yapın.
4. Orijinal ve alınan veri serileri için bir diyagram oluşturun.

Sorunun çözümü

EĞİLİM ve BÜYÜME işlevlerinden farklı olarak, yukarıda listelenen işlevlerden hiçbirinin (EĞİM, KESME, DOT, LGRFPRIB) bir gerileme olmadığını unutmayın. Bu işlevler, regresyonun gerekli parametrelerini belirleyen yalnızca yardımcı bir rol oynar.

SLOPE, INTERCEPT, LINEST, LGRFPRIB fonksiyonları kullanılarak oluşturulan doğrusal ve üstel regresyonlar için, EĞİLİM ve BÜYÜME fonksiyonlarına karşılık gelen doğrusal ve üstel regresyonların aksine, denklemlerinin görünümü her zaman bilinir.

1 ... Denklemle doğrusal bir regresyon oluşturalım:

y \u003d mx + b

eĞİM ve KESMEN işlevleriyle, burada eğim m EĞİM işlevi tarafından belirlenir ve kesişme b KESME işlevi tarafından belirlenir.

Bunu yapmak için aşağıdaki eylemleri gerçekleştiriyoruz:

1. orijinal tabloyu A4: B14 hücre aralığına giriyoruz;
2. m parametresinin değeri C19 hücresinde belirlenecektir. Eğim İstatistik kategorisinden seçin; Bilinen_y alanına B4: B14 hücre aralığını ve bilinen_x alanına A4: A14 hücre aralığını girin. Formül C19 hücresine girilecektir: \u003d EĞİM (B4: B14; A4: A14);
3. benzer bir yöntemle, D19 hücresindeki b parametresinin değeri belirlenir. Ve içeriği şöyle görünecektir: \u003d INTERCEPT (B4: B14; A4: A14).Dolayısıyla, doğrusal regresyonu oluşturmak için gerekli olan m ve b parametrelerinin değerleri sırasıyla C19, D19 hücrelerinde depolanacaktır;
4. daha sonra C4 hücresine doğrusal regresyon formülünü şu şekilde giriyoruz: \u003d $ C * A4 + $ D. Bu formülde, C19 ve D19 hücreleri mutlak referanslarla yazılır (kopyalama mümkün olduğunda hücre adresi değişmemelidir). Mutlak referans işareti $, imleç hücre adresine yerleştirildikten sonra klavyeden veya F4 tuşu kullanılarak yazılabilir. Doldurma tutamacını kullanarak bu formülü C4: C17 hücre aralığına kopyalayın. Gerekli veri serisini alıyoruz (Şekil 12). Emir sayısının bir tam sayı olması nedeniyle, Hücreleri biçimlendir penceresinin Sayı sekmesinde sayı biçimini 0 ondalık basamakla ayarlayın.

2 ... Şimdi denklemle verilen doğrusal bir regresyon oluşturalım:

y \u003d mx + b

dOT işlevini kullanarak.

Bunun için:

1. dOT işlevini dizi formülü olarak C20: D20 hücre aralığına girin: \u003d (DOT (B4: B14; A4: A14)). Sonuç olarak, C20 hücresindeki m parametresinin değerini ve D20 hücresindeki b parametresinin değerini alıyoruz;
2. formülü D4 hücresine girin: \u003d $ C * A4 + $ D;
3. bu formülü doldurma tutamacını kullanarak D4: D17 hücre aralığına kopyalayın ve gerekli veri serisini alın.

3 ... Denklemle üstel bir regresyon oluşturuyoruz:

y \u003d bmx

lGRFPRIBL işlevi kullanılarak, aynı şekilde gerçekleştirilir:

c21: D21 hücre aralığında LGRFPRIBL işlevini bir dizi formülü olarak giriyoruz: \u003d (LGRFPRIBL (B4: B14; A4: A14)). Bu durumda, C21 hücresinde m parametresinin değeri belirlenir ve D21 hücresinde - b parametresinin değeri;

formül E4 hücresine girilir: \u003d $ D * $ C ^ A4;

doldurma işaretçisi kullanılarak bu formül, üstel regresyon için veri serilerinin yerleştirileceği E4: E17 hücre aralığına kopyalanır (bkz. Şekil 12).

İncirde. 13, formüllerin yanı sıra gerekli hücre aralıklarıyla kullandığımız fonksiyonları görebileceğiniz bir tablodur.

İlk veriler ve elde edilen veri serileri için, Şekil 2'de gösterilen diyagram. ondört.

Öngörü, ekonomiden mühendisliğe kadar hemen hemen her faaliyet alanında çok önemli bir unsurdur. Bu alanda uzmanlaşmış çok sayıda yazılım var. Ne yazık ki, tüm kullanıcılar, geleneksel bir Excel elektronik tablo işlemcisinin, profesyonel programlara göre etkinliklerinde çok düşük olmayan tahmin yapmak için cephanelik araçlarına sahip olduğunu bilmiyor. Gelin bu araçların ne olduğunu ve pratikte nasıl tahmin yapılacağını öğrenelim.

Herhangi bir tahminin amacı, gelecekte belirli bir noktada çalışılan nesne ile ilgili olarak mevcut eğilimi belirlemek ve beklenen sonucu belirlemektir.

Yöntem 1: eğilim çizgisi

Excel'deki en popüler grafik tahmin türlerinden biri, bir eğilim çizgisi oluşturarak gerçekleştirilen ekstrapolasyondur.

Bu göstergede geçen 12 yıla ait verilere dayanarak şirketin 3 yıllık kar miktarını tahmin etmeye çalışalım.

Yöntem 2: TAHMİN operatörü

Tablo veriler için ekstrapolasyon, standart Excel işlevi kullanılarak yapılabilir TAHMİN... Bu argüman istatistiksel bir araç olarak kategorize edilmiştir ve aşağıdaki sözdizimine sahiptir:

TAHMİN (X, bilinen_y'ler, bilinen_x'ler)

"X" Bir işlev değeri tanımlamak istediğiniz bir bağımsız değişkendir. Bizim durumumuzda, argüman, tahminin yapılması gereken yıl olacaktır.

"Bilinen y değerleri" - işlevin bilinen değerlerinin tabanı. Bizim durumumuzda rolü, önceki dönemlerdeki kar miktarı tarafından oynanır.

"Bilinen x değerleri" İşlevin bilinen değerlerine karşılık gelen argümanlardır. Rollerinde, önceki yılların karlarına ilişkin bilgilerin toplandığı yılların numarasına sahibiz.

Doğal olarak, argümanın bir zaman aralığı olması gerekmez. Örneğin, sıcaklık olabilir ve işlevin değeri ısıtıldığında suyun genleşme seviyesi olabilir.

Bu yöntemi hesaplarken doğrusal regresyon yöntemi kullanılır.

Operatörü kullanmanın nüanslarına bakalım TAHMİN belirli bir örnekle. Aynı masanın tamamını alalım. 2018 kar tahminini bilmemiz gerekecek.

Ancak, bir trend çizgisi oluşturmada olduğu gibi, tahmin döneminden önceki zaman aralığının, veri tabanının biriktirildiği tüm sürenin% 30'unu geçmemesi gerektiğini unutmayın.

Yöntem 3: TREND operatörü

Tahmin için bir işlev daha kullanılabilir - AKIM... Aynı zamanda istatistiksel operatörler kategorisine aittir. Sözdizimi bir aracınkine çok benzer TAHMİN ve şuna benzer:

TREND (Bilinen_y'ler; bilinen_x'ler; yeni_x'ler; [sabit])

Gördüğünüz gibi argümanlar "Bilinen y değerleri" ve "Bilinen x değerleri" tamamen operatörün benzer unsurlarına karşılık gelir TAHMİNve argüman "Yeni x değerleri" argümanla eşleşir "X" önceki araç. Üstelik içinde AKIM ek bir argüman var "Sabit", ancak isteğe bağlıdır ve yalnızca sabit faktörler mevcut olduğunda kullanılır.

Bu operatör, doğrusal bir fonksiyon bağımlılığı varlığında en etkili şekilde kullanılır.

Bakalım bu araç aynı veri seti ile nasıl çalışacak. Elde edilen sonuçları karşılaştırmak için 2019'u tahmin noktası olarak tanımlayacağız.

Yöntem 4: Operatör BÜYÜME

Excel'de tahmin yapabileceğiniz bir diğer işlev BÜYÜME operatörüdür. Aynı zamanda istatistiksel araç grubuna aittir, ancak öncekilerden farklı olarak doğrusal bir ilişki değil, üstel bir ilişki kullanır. Bu aracın sözdizimi şuna benzer:

BÜYÜME (Bilinen_y'ler; bilinen_x'ler; yeni_x'ler; [sabit])

Gördüğünüz gibi, bu fonksiyonun argümanları, operatörün argümanlarını tam olarak tekrarlıyor. AKIM, bu yüzden ikinci kez açıklamaları üzerinde durmayacağız, ancak bu aracı pratikte kullanmaya hemen geçeceğiz.

Yöntem 5: LINEST operatörü

Şebeke LINEST hesaplarken doğrusal yaklaşım yöntemini kullanır. Araç tarafından kullanılan doğrusal bağımlılık yöntemi ile karıştırılmamalıdır. AKIM... Sözdizimi şuna benzer:

LINEST (Bilinen_y'ler; bilinen_x'ler; yeni_x'ler; [sabit]; [istatistikler])

Son iki argüman isteğe bağlıdır. Önceki yöntemlerden ilk ikisine aşinayız. Ama muhtemelen bu işlevin yeni değerlere işaret edecek bir argümanın eksik olduğunu fark etmişsinizdir. Gerçek şu ki, bu araç yalnızca, bizim durumumuzda bir yıla eşit olan dönemin birimi başına gelir miktarındaki değişikliği belirler, ancak toplam toplamı ayrı ayrı hesaplamalıyız ve operatörün hesaplama sonucunu son gerçek kar değerine ekleyerek LINESTyıl sayısıyla çarpılır.

Gördüğünüz gibi, 2019'da doğrusal yaklaşım yöntemi ile hesaplanan tahmini kar değeri 4.614.9 bin ruble olacak.

Yöntem 6: Operatör LGRFPRIBL

Bakacağımız son araç olacak LGRFPRIBL... Bu operatör, üstel yaklaşım yöntemine göre hesaplamalar yapar. Söz dizimi aşağıdaki şekilde yapılandırılmıştır:

LGRFPRIBL (Bilinen_y'ler; bilinen_x'ler; yeni_x'ler; [sabit]; [istatistikler])

Gördüğünüz gibi, tüm argümanlar önceki fonksiyonun karşılık gelen öğelerini tamamen tekrar ediyor. Tahmin hesaplama algoritması biraz değişecek. İşlev, gelir miktarının bir dönemde, yani bir yılda kaç kat değişeceğini gösteren üstel bir eğilim hesaplayacaktır. Son fiili dönem ile ilk planlanan dönem arasındaki kâr farkını bulmamız, planlanan dönemlerin sayısıyla çarpmamız gerekecek (3) ve sonuca son gerçek dönemin toplamını ekleyin.

Üstel yaklaşım yöntemi kullanılarak hesaplanan 2019 yılında öngörülen kâr miktarı 4639,2 bin ruble olacak ve bu da önceki yöntemler hesaplanırken elde edilen sonuçlardan çok farklı olmayacak.

Excel programında hangi yollarla tahmin yapabileceğinizi öğrendik. Grafiksel olarak bu, bir eğilim çizgisi kullanılarak ve analitik olarak - bir dizi yerleşik istatistiksel işlev kullanılarak yapılabilir. Bu operatörler tarafından aynı verilerin işlenmesi farklı sonuçlara neden olabilir. Ancak, hepsi farklı hesaplama yöntemleri kullandığı için bu şaşırtıcı değildir. Dalgalanma küçükse, belirli bir duruma uygulanabilen bu seçeneklerin tümü, nispeten güvenilir kabul edilebilir.