Excel'de trend çizgisi. Bir trend çizgisi oluşturmak. Tablolar ve grafikler bizi nasıl aldatıyor?

Trend, bir göstergenin zaman içindeki yükselişini veya düşüşünü tanımlayan bir kalıptır. Bir grafikte herhangi bir zaman serisini (zaman içinde değişken bir göstergenin kaydedilen değerlerinin bir listesi olan istatistiksel veriler) tasvir ederseniz, genellikle belirli bir açı vurgulanır - eğri ya yavaş yavaş artar ya da azalır, bu gibi durumlarda gelenekseldir zaman serisinin (sırasıyla yükselme veya düşme) eğiliminde olduğunu söylemek.

Model olarak trend

Bu olguyu açıklayan bir model oluşturursak oldukça basit ve çok basit olduğu ortaya çıkıyor. kullanışlı araç Herhangi bir karmaşık hesaplama gerektirmeyen veya etkileyen faktörlerin önemini veya yeterliliğini kontrol etmek için harcanan zamanı gerektirmeyen tahminler için.

Peki model olarak trend nedir? Bu, göstergenin (Y) zamandaki (t) değişime olan regresyon bağımlılığını ifade eden bir dizi hesaplanmış denklem katsayılarıdır. Yani bu, daha önce düşündüğümüz gerilemenin tamamen aynısıdır, yalnızca buradaki etkileyici faktör zaman göstergesidir.

Önemli!

Hesaplamalarda t genellikle yıl, ay veya hafta sayısını değil, incelenen istatistiksel popülasyondaki dönemin seri numarasını (zaman serisini) ifade eder. Örneğin, bir zaman serisi birkaç yıl boyunca inceleniyorsa ve veriler aylık olarak kaydediliyorsa, o zaman 1'den 12'ye ve yine baştan itibaren sıfır temelli ay numaralandırması kullanmak temelde yanlıştır. Ayrıca, bir serinin çalışmasına örneğin Mart ayında t'nin değeri olarak 3'ün (yılın üçüncü ayı) başlaması da yanlıştır; eğer bu, incelenen popülasyondaki ilk değerse, o zaman serisi de yanlıştır; sayısı 1 olmalıdır.

Doğrusal trend modeli

Diğer herhangi bir regresyon gibi, bir eğilim de doğrusal olabilir (etkileyen faktör t'nin derecesi 1'e eşittir) veya doğrusal olmayabilir (derece birden büyük veya küçüktür). Doğrusal regresyon her zaman en doğru olmasa da en basit yöntem olduğundan, bu tür bir eğilimi daha ayrıntılı olarak ele alacağız.

Denklemin genel formu doğrusal eğilim:

Y(t) = a 0 + a 1 *t + Ɛ

0'ın sıfır regresyon katsayısı olduğu durumda, yani, etkileyen faktör sıfıra eşitse Y ne olacaktır, a 1, çalışılan Y göstergesinin etkileme faktörü t'ye bağımlılık derecesini ifade eden bir regresyon katsayısıdır; rastgele bir bileşen veya standart hata, aslında gerçekte arasındaki farkı temsil eder mevcut değerler Y ve hesaplandı. Etkileyen tek faktör zamandır; zaman.

Göstergenin büyüme veya düşme eğilimi ne kadar belirgin olursa, a 1 katsayısı da o kadar büyük olacaktır. Buna göre, a 0 sabitinin ɐ rastgele bileşeniyle birlikte zamana ek olarak kalan regresyon etkilerini, yani tüm diğer olası etkileyici faktörleri yansıttığı varsayılmaktadır.

Model katsayılarını hesaplayabilirsiniz standart yöntem en küçük kareler (LSS). Bütün bu hesaplamalarla Microsoft Excel patlamayla tek başına başa çıkabilir ve doğrusal bir trend modeli veya hazır bir tahmin elde etmek için, aşağıda ayrı ayrı tartışacağımız beşe kadar yöntem vardır.

Doğrusal bir eğilim elde etmenin grafiksel yöntemi

Bu ve diğer tüm örneklerde aynı dinamik seriyi kullanacağız - yıllık olarak hesaplanan ve kaydedilen GSYİH düzeyi; bizim durumumuzda çalışma 2004'ten 2012'ye kadar olan dönemde gerçekleştirilecektir.

Orijinal veriye t diyeceğimiz bir sütun daha ekleyelim ve onu artan sayılarla işaretleyelim. seri numaraları 2004'ten 2012'ye kadar belirtilen dönemde kaydedilen tüm GSYİH değerleri. – 9 yıl veya 9 dönem.

Excel boş bir alan ekleyecektir - gelecekteki grafiği işaretlemek, bu grafiği seçmek ve menü çubuğunda görünen sekmeyi etkinleştirmek - Oluşturucu, bir düğme arıyorum Veri seç, açılan pencerede düğmeye basın Eklemek. Bir açılır pencere, grafik oluşturmak için verileri seçmenizi ister. Alan değeri olarak Seri adı grafiğin adıyla en iyi eşleşen metni içeren hücreyi seçin. Tarlada X değerleri t sütunundaki hücrelerin aralığını belirtin – etkileyen faktör. Tarlada Y değerleriİncelenen gösterge olan GSYİH'nin (Y) bilinen değerlerine sahip sütun hücrelerinin aralığını belirtiyoruz.

Belirtilen alanları doldurduktan sonra birkaç kez Tamam düğmesine basın ve hazır bir dinamik grafiği elde edin. Şimdi farenin sağ tuşuyla ve beliren menüden grafik çizgisinin kendisini seçin. içerik menüsü Bir öğe seçin Bir trend çizgisi ekleyin

Seçtiğimiz model türleri arasından bir trend çizgisi oluşturmak için parametreleri yapılandırmak üzere bir pencere açılacaktır. Doğrusal, P öğelerinin yanına bir onay işareti koyun diyagram üzerinde bir denklem oluşturma Ve Yaklaşık güvenilirlik değeri R2'yi diyagrama yerleştirin Bu, önceden oluşturulmuş trend çizgisinin grafikte görüntülenmesinin yanı sıra, modeli hazır bir denklem biçiminde göstermenin matematiksel bir versiyonu ve modelin kalitesinin bir göstergesi için yeterli olacaktır. R2. İncelenen göstergeler arasındaki boşluğu görsel olarak değerlendirmek amacıyla tahmini bir grafik üzerinde görüntülemek istiyorsanız, alanda belirtin için ileriye dönük tahmin ilgi dönemlerinin sayısı.

Aslında bu yöntemle ilgili her şey bu kadar, elbette görüntülenen doğrusal eğilim denkleminin modelin kendisi olduğunu ve bunun modelden hesaplanan değerleri elde etmek için bir formül olarak kullanılabileceğini ve buna göre ekleyebilirsiniz. kesin değerler tahmin (grafikte görüntülenen tahmin yalnızca yaklaşık olarak tahmin edilebilir), makaleye ekli örnekte yaptığımız da budur.

LINEST formülünü kullanarak doğrusal bir trend oluşturma

Bu yöntemin özü, fonksiyonu kullanarak doğrusal eğilim katsayılarını aramaya dayanır. DÜZ Daha sonra bu etkileyici katsayıları denklemde yerine koyarak tahmine dayalı bir model elde ederiz.

İki bitişik hücreyi seçmemiz gerekecek (ekran görüntüsünde bunlar A38 ve B38 hücreleridir), ardından üstteki formül çubuğunda (yukarıdaki ekran görüntüsünde kırmızıyla vurgulanmıştır), ardından "=LINEST(" yazarak işlevi çağırırız. hangi Excel bu işlevler için nelerin gerekli olduğuna dair ipuçları görüntüleyecektir:

1. açıklanan Y göstergesinin bilinen değerlerine sahip bir aralık seçin (bizim durumumuzda GSYİH, ekran görüntüsünde aralık mavi renkle vurgulanmıştır) ve noktalı virgül koyun
2. X'i etkileyen faktörlerin aralığını belirtin (bizim durumumuzda bu, ekran görüntüsünde yeşil renkle vurgulanan t göstergesi, nokta seri numarasıdır) ve noktalı virgül koyun
3. fonksiyon için bir sonraki gerekli parametre, başlangıçta sabit (katsayısı) olan bir modeli düşündüğümüzden, sabitin hesaplanmasının gerekip gerekmediğini belirlemektir. 0 ), ardından “DOĞRU” veya “1” ve noktalı virgül koyun
4. Daha sonra istatistik parametrelerinin hesaplanmasının gerekli olup olmadığını belirtmemiz gerekiyor (eğer bu seçeneği düşünüyor olsaydık, başlangıçta birkaç satır aşağıda “formül için” bir aralık ayırmamız gerekirdi). İstatistiksel parametreleri hesaplama ihtiyacını belirtin, yani standart değer katsayı hataları, belirleme katsayısı, Y için standart hata, Fisher kriteri, serbestlik derecesi vb., yalnızca ne anlama geldiklerini anladığınızda anlamlı olurlar; bu durumda ya "DOĞRU" ya da "1" ayarını yaparız. Öğrenmeye çalıştığımız basitleştirilmiş modelleme durumunda, formülü yazmanın bu aşamasında “FALSE” veya “0” olarak ayarlayın ve kapanış parantezinden sonra “)” ekleyin.
5. formülü "canlandırmak", yani gerekli tüm parametreleri belirledikten sonra çalışmasını sağlamak için Enter düğmesine basmak yeterli değildir, üç tuşa sırayla basmanız gerekir: Ctrl, Shift, Enter

Yukarıdaki ekran görüntüsünde görebileceğiniz gibi formül için seçtiğimiz hücreler, hücredeki doğrusal eğilim için hesaplanan regresyon katsayıları değerleri ile doldurulmuştur. B38 katsayı bulunur 0 ve hücrede A38- parametreye bağımlılık katsayısı T (veya X ), yani 1 . Elde edilen değerleri denklemde değiştiririz doğrusal fonksiyon ve alıyoruz bitmiş model V matematiksel ifade – y = 169.572,2+138.454,3*t

Hesaplanan değerleri elde etmek için e modele göre ve buna göre bir tahmin almak için formülü bir Excel hücresinde değiştirmeniz yeterlidir ve bunun yerine T gerekli dönem numarasını içeren hücreye bir bağlantı belirtin (ekran görüntüsündeki hücreye bakın) D25).

Ortaya çıkan modeli gerçek verilerle karşılaştırmak için iki grafik oluşturabilirsiniz; burada X olarak dönemin seri numarasını belirtirsiniz ve Y olarak, bir durumda - gerçek GSYİH ve diğerinde - hesaplanır (ekran görüntüsünde, sağdaki diyagram).

Analiz Paketindeki Regresyon aracını kullanarak doğrusal bir trend oluşturma

Aslında makale bu yöntemi tam olarak açıklamaktadır, tek fark, ilk verilerimizde yalnızca bir etkileyici faktörün bulunmasıdır. X (dönem numarası – T ).

Yukarıdaki resimde görebileceğiniz gibi, bilinen GSYİH değerlerine sahip veri aralığı olarak vurgulandı giriş aralığı Y ve buna karşılık gelen t periyot numaralarıyla aralık – X giriş aralığı olarak. Analiz Paketi kullanılarak yapılan hesaplamaların sonuçları kullanıcıya sunulur. ayrı sayfa ve sarıya boyadığım hücrelerle ilgilendiğimiz bir dizi tabloya benziyor (aşağıdaki şekle bakın) ve yeşil renkler. Yukarıdaki makalede açıklanan prosedüre benzer şekilde, elde edilen katsayılardan doğrusal bir trend modeli oluşturulur. y=169 572,2+138 454,3*t tahminlerin yapıldığı temel alınarak.

TREND işlevi aracılığıyla doğrusal bir eğilim kullanarak tahmin yapma

Bu yöntem, daha önce gerekli olan model parametrelerinin hesaplanması adımlarını atlaması ve bir tahmin elde etmek için elde edilen katsayıları bir formül olarak bir hücreye manuel olarak yerleştirmesi bakımından öncekilerden farklıdır; bu işlev, tam olarak buna dayalı olarak hazır hesaplanmış bir tahmin değeri üretir; bilinen kaynak verileri.

Hedef hücreye (sonucu görmek istediğimiz hücreye) bir işaret koyuyoruz eşittir ve “sihirli işlevi” yazarak çağırın AKIM(", o zaman vurgulamanız gerekir, yani noktalı virgül koyduktan sonra ve bilinen X değerlerine (yani t dönem sayılarına) sahip bir aralık seçin Bilinen GSYİH değerlerine sahip bir sütuna karşılık gelen, yine noktalı virgül koyun ve tahmin yaptığımız dönemin numarasını içeren hücreyi seçin (ancak bizim durumumuzda dönem numarası bir bağlantıyla gösterilemez) hücreye, ancak doğrudan formüldeki bir sayıya göre), sonra başka bir noktalı virgül koyun ve belirtin DOĞRU veya 1 , katsayıyı hesaplamak için onay olarak 0 sonunda koyduk parantez kapatma ve tuşuna basın Girmek.

Eksi Bu method ne model denklemini ne de katsayılarını göstermemesidir, bu nedenle, modelin kalite parametrelerinin bir yansıması olmadığı gibi, şu veya bu modele dayanarak şöyle bir tahmin aldığımızı söyleyemeyiz, Bununla birlikte, ortaya çıkan tahminin dikkate alınmasının anlamlı olup olmadığının söylenebileceği belirleme katsayısı.

TAHMİN işlevini kullanarak doğrusal bir eğilim kullanarak tahmin yapma

Bu fonksiyonun özü bir öncekiyle tamamen aynıdır, tek fark, ilk verilerin formüle yazılma sırası ve bir katsayının varlığı veya yokluğu için bir ayar bulunmamasıdır. 0 (yani fonksiyon bu katsayının her durumda var olduğunu ima eder)

Yukarıdaki şekilden de görebileceğiniz gibi şunu yazıyoruz: =TAHMİN("ve sonra belirtin dönem numarası içeren hücre Değeri doğrusal bir eğilime, yani bir tahmine göre hesaplamak için gerekli olan, ardından noktalı virgül koyup sonra seçiyoruz bilinen Y değerleri aralığı, yani bilinen GSYİH değerlerine sahip sütun ardından noktalı virgül koyun ve vurgulayın bilinen X değerlerine sahip aralık, yani dönem sayıları t ile bilinen GSYİH değerlerine sahip sütuna karşılık gelen ve son olarak belirledik parantez kapatma ve tuşuna basın Girmek.

Elde edilen sonuçlar, yukarıdaki yöntemde olduğu gibi, yalnızca doğrusal bir eğilim modeli kullanılarak tahmin edilen değerin hesaplanmasının nihai sonucudur; matematiksel açıdan herhangi bir hata veya modelin kendisini göstermez.

Makaleyi özetlemek gerekirse

Kendimiz için belirlediğimiz mevcut hedefe bağlı olarak yöntemlerin her birinin diğerleri arasında en kabul edilebilir olabileceğini söyleyebiliriz. İlk üç yöntem hem anlam hem de sonuç açısından birbiriyle kesişir ve modelin ve kalitesinin tanımlanmasının gerekli olduğu az çok ciddi çalışmalar için uygundur. Buna karşılık, son iki yöntem de birbiriyle aynıdır ve size, örneğin "Gelecek yıl için satış tahmini nedir?" sorusuna mümkün olan en kısa sürede yanıt verecektir.

Belirli hacim/boyuttaki ürünler için zamanlama ölçümleri yoksa ne yapmalıyım? Yoksa ölçüm sayısı yetersiz ve yakın gelecekte ek gözlemler yapılamayacak mı? En iyi yol Bu sorunun çözümü, MS Excel'deki eğilim çizgilerini kullanarak hesaplanmış bağımlılıklar (regresyon denklemleri) oluşturmaktır.

Gerçek bir durumu ele alalım: Bir depoda, bir siparişin kutudan toplanmasına ilişkin işçilik maliyeti miktarını belirlemek için, zamanlama gözlemleri. Bu gözlemlerin sonuçları aşağıdaki Tablo 1'de sunulmaktadır.

Daha sonra, 0,6 ve 0,9 m3 mal/siparişin seçilmesi için gereken sürenin belirlenmesi ihtiyacı ortaya çıktı. Ek zamanlama çalışmalarının yapılmasının mümkün olmaması nedeniyle bu sipariş hacimlerinin seçimi için harcanan süre MS Excel'deki regresyon denklemleri kullanılarak hesaplandı. Bunu başarmak için Tablo 1, Tablo 2'ye dönüştürüldü.

Bir dağılım grafiğinin seçilmesi, şek. 1

Sonraki adım: fare imleci grafikteki noktalardan birinin üzerine yerleştirildi ve sağ düğme fare, öğenin seçildiği içerik menüsü çağrıldı: “trend çizgisi ekle” (İncir. 2).

Bir trend çizgisi ekleme, şek. 2

Trend çizgisi formatını ayarlamak için görünen pencerede (Şek. 3) sırasıyla seçilmiştir: çizgi tipi doğrusal/güç ve aşağıdaki maddeler kontrol edilmiştir: “denklemi diyagramda göster” ve “yaklaşım güvenirlik değerini (R^2) diyagrama yerleştir” (determinasyon katsayısı).

Trend çizgisi formatı, şek. 3

Sonuç olarak Şekil 1'de sunulan grafikler elde edildi. 4 ve 5.

Doğrusal hesaplanmış bağımlılık, Şekil 1. 4

Güç yasası hesaplanmış bağımlılığı, Şekil 1. 5

Grafiklerin görsel analizi, elde edilen bağımlılıkların yakınlığını açıkça gösterir. Ayrıca belirleme katsayısı olarak da adlandırılan yaklaşımın güvenirlik değeri (R^2), her iki bağımlılık durumunda da aynı değer olan 0,97'dir. Belirleme katsayısı 1'e ne kadar yakınsa trend çizgisinin gerçeğe o kadar karşılık geldiği bilinmektedir. Ayrıca sipariş işleme için harcanan zamandaki değişimin %97'sinin mal miktarındaki değişiklikle açıklandığı da ifade edilebilir. Bu nedenle bu durumdaönemli değil: zaman maliyetlerinin sonraki hesaplanması için hangi hesaplanan bağımlılığın ana olarak seçilmesi gerekir.

Ana bağımlılık olarak doğrusal hesaplanmış bağımlılığı alalım. Daha sonra mal miktarına bağlı olarak zaman maliyetleri aşağıdaki formülle belirlenecektir: y = 54,511x + 0,1489. Daha önce zamanlama gözlemleri yapılmış olan mal miktarına ilişkin bu hesaplamaların sonuçları aşağıdaki Tablo 3'te sunulmaktadır.

Regresyon denklemi kullanılarak hesaplanan harcanan zamanın, zaman tutma gözlemlerinden hesaplanan harcanan zamandan ortalama sapmasını belirleyelim: (-0,05+0,10-0,05+0,01)/4=0,0019. Dolayısıyla, regresyon denklemi kullanılarak hesaplanan zaman harcaması, zaman tutma verilerinden hesaplanan zaman harcamasından yalnızca şu farkla farklılık gösterir: 0,19%. Veriler arasındaki tutarsızlık önemsizdir.

y = 54,511x + 0,1489 formülünü kullanarak, daha önce zamanlama gözlemlerinin yapılmadığı mal miktarı için zaman maliyetlerini belirleyeceğiz. (Tablo 4).

Böylece, MS Excel'deki eğilim çizgilerini kullanarak hesaplanmış bağımlılıklar oluşturmak - Bu harika yolÇeşitli nedenlerden dolayı zaman tutma gözlemlerinin kapsamına girmeyen operasyonlar için harcanan zamanın belirlenmesi.

Teorik bilgiler

Pratikte modelleme yaparken çeşitli süreçler- özellikle ekonomik, fiziksel, teknik, sosyal - bazı sabit noktalarda fonksiyonların yaklaşık değerlerini bilinen değerlerinden hesaplamak için bir veya başka bir yöntem yaygın olarak kullanılmaktadır.

Bu tür fonksiyon yaklaşımı problemi sıklıkla ortaya çıkar:

• deney sonucunda elde edilen tablo verilerini kullanarak, incelenen sürecin karakteristik miktarlarının değerlerini hesaplamak için yaklaşık formüller oluştururken;
• sayısal entegrasyon, türev, çözüm diferansiyel denklemler vesaire.;
• fonksiyon değerlerini hesaplamak gerekiyorsa ara noktalar dikkate alınan aralık;
• dikkate alınan aralığın dışındaki bir sürecin karakteristik miktarlarının değerlerini belirlerken, özellikle tahmin yaparken.

Bir süreci simüle etmek gerekirse, tablo tarafından verilen yaklaşık olarak tanımlayan bir fonksiyon oluşturun. bu süreç en küçük kareler yöntemine dayalı olarak buna yaklaşıklık fonksiyonu (regresyon) adı verilecek ve yaklaşıklık fonksiyonları oluşturma görevinin kendisi de yaklaşıklık problemi olarak adlandırılacaktır.

Bu makale, MS Excel paketinin bu tür sorunları çözme konusundaki yeteneklerini tartışmakta, ayrıca tablo halindeki regresyonları oluşturmak (oluşturmak) için yöntemler ve teknikler sağlamaktadır. belirtilen işlevler(bunun temeli regresyon analizi).

Excel'in regresyon oluşturmak için iki seçeneği vardır.

1. İncelenen süreç karakteristiği için bir veri tablosu temelinde oluşturulan bir diyagrama seçilen regresyonların (eğilim çizgileri) eklenmesi (yalnızca bir diyagram oluşturulmuşsa kullanılabilir);
2. Çalışanın yerleşik istatistiksel işlevlerini kullanma Excel sayfası doğrudan kaynak veri tablosuna dayalı olarak regresyonlar (eğilim çizgileri) elde etmenize olanak tanır.

Grafiğe trend çizgileri ekleme

Bir süreci tanımlayan ve bir diyagramla temsil edilen bir veri tablosu için Excel'in aşağıdakileri yapmanıza olanak tanıyan etkili bir regresyon analiz aracı vardır:

• en küçük kareler yöntemini temel alarak inşa edin ve diyagrama, incelenen süreci değişen doğruluk dereceleriyle modelleyen beş tür regresyon ekleyin;
• oluşturulan regresyon denklemini diyagrama ekleyin;
• Seçilen regresyonun grafikte görüntülenen verilere uygunluk derecesini belirleyin.

Veri tabanlı Excel grafikleri Denklemde belirtilen doğrusal, polinom, logaritmik, güç, üstel regresyon türlerini elde etmenizi sağlar:

y = y(x)

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

1 . Doğrusal regresyon, değerleri sabit bir oranda artan veya azalan özellikleri modellemek için iyidir. Bu, incelenen süreç için oluşturulacak en basit modeldir. O

y = mx + b

burada m, doğrusal regresyon eğiminin x eksenine teğetidir; b - doğrusal regresyonun ordinat ekseni ile kesişme noktasının koordinatı.

2 . Bir polinom eğilim çizgisi, birkaç farklı uç noktaya (maksimum ve minimum) sahip özellikleri tanımlamak için kullanışlıdır. Polinom derecesinin seçimi, incelenen özelliğin ekstremum sayısına göre belirlenir. Dolayısıyla, ikinci dereceden bir polinom, yalnızca bir maksimumu veya minimumu olan bir süreci iyi tanımlayabilir; üçüncü dereceden polinom - en fazla iki ekstrema; dördüncü dereceden polinom - en fazla üç ekstrema vb.

Bu durumda trend çizgisi aşağıdaki denkleme göre oluşturulur:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

burada c0, c1, c2,... c6 katsayıları inşaat sırasında değerleri belirlenen sabitlerdir.

3 . Logaritmik eğilim çizgisi, değerleri başlangıçta hızla değişen ve daha sonra yavaş yavaş sabitlenen özelliklerin modellenmesinde başarıyla kullanılır.

Denkleme göre oluşturulmuştur:

y = c ln(x) + b

4 . Güç eğilim çizgisi şunu verir: iyi sonuçlar incelenen bağımlılığın değerleri karakterize edilirse sürekli değişim büyüme oranı. Böyle bir bağımlılığın bir örneği, bir arabanın eşit şekilde hızlandırılmış hareketinin grafiğidir. Verilerde sıfır veya negatif değerler varsa güç trend çizgisi kullanamazsınız.

Denkleme göre oluşturulmuştur:

y = cxb

burada b, c katsayıları sabittir.

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

Denkleme göre oluşturulmuştur:

y = c ebx

burada b, c katsayıları sabittir.

Bir satır seçerken Excel trendi yaklaşımın güvenilirliğini karakterize eden R2 değerini otomatik olarak hesaplar: R2'nin değeri birliğe ne kadar yakınsa, trend çizgisi incelenen sürece o kadar güvenilir bir şekilde yaklaşır. Gerektiğinde R2 değeri her zaman grafikte görüntülenebilir.

Formülle belirlenir:

Bir veri serisine trend çizgisi eklemek için:

• bir dizi veriye dayalı olarak bir grafiği etkinleştirin, yani grafik alanının içine tıklayın. Diyagram öğ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ğ tıklatarak kolayca uygulanabilir; Görüntülenen içerik menüsünde Trend çizgisi ekle komutunu seçin. Tür sekmesi açılmış olarak ekranda Trend Çizgisi iletişim kutusu görünecektir (Şekil 1).

Bundan sonra ihtiyacınız var:

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

1 . Yerleşik seriler alanı, söz konusu grafikteki tüm veri serilerini listeler. Belirli bir veri serisine trend çizgisi eklemek için Oluşturulan seriler alanında bu çizginin adını seçin.

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

• Yaklaşık (düzleştirilmiş) eğrinin adı alanında trend çizgisinin adını değiştirin.
• Tahmin alanında tahmin için dönem sayısını (ileri veya geri) ayarlayın;
• trend çizgisinin denklemini grafik alanında görüntüleyin; bunun için "denklemi grafikte göster" onay kutusunu etkinleştirmeniz gerekir;
• yaklaşık güvenilirlik değeri R2'yi diyagram alanında görüntüleyin; bunun için Yaklaşım güvenilirlik değerini diyagrama yerleştir (R^2) onay kutusunu etkinleştirmeniz gerekir;
• trend çizgisinin Y ekseni ile kesişme noktasını ayarlayın; bunun için eğrinin Y ekseni ile bir noktada kesişmesi için onay kutusunu etkinleştirmeniz gerekir;
• İletişim kutusunu kapatmak için Tamam düğmesini tıklayın.

Zaten çizilmiş bir trend çizgisini düzenlemeye başlamanın üç yolu vardır:

daha önce trend çizgisini seçtikten sonra Format menüsünden Seçilen trend çizgisi komutunu kullanın;

trend çizgisine sağ tıklanarak çağrılan içerik menüsünden Trend çizgisini formatla komutunu seçin;

çift ​​tıklama trend çizgisi boyunca.

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

Daha önce çizilmiş bir trend çizgisini silmek için silinecek trend çizgisini seçin ve Sil tuşuna basın.

Dikkate alınan regresyon analiz aracının avantajları şunlardır:

• bir veri tablosu oluşturmadan grafikler üzerinde bir trend çizgisi oluşturmanın göreceli kolaylığı;
• önerilen trend çizgisi türlerinin oldukça geniş bir listesi ve bu liste en sık kullanılan regresyon türlerini içerir;
• incelenen sürecin davranışını keyfi (sağduyu sınırları dahilinde) ileri ve geri adımlarla tahmin etme yeteneği;
• trend çizgisi denklemini analitik biçimde elde etme yeteneği;
• Gerekirse, yaklaşımın güvenilirliğine ilişkin bir değerlendirme elde etme olasılığı.

Dezavantajları aşağıdakileri içerir:

bir trend çizgisinin oluşturulması yalnızca bir dizi veri üzerine kurulu bir diyagram varsa gerçekleştirilir;

elde edilen eğilim çizgisi denklemlerine dayanarak incelenen karakteristik için veri serisi oluşturma süreci biraz karmaşıktır: gerekli regresyon denklemleri, orijinal veri serisinin değerlerindeki her değişiklikle birlikte, ancak yalnızca grafik alanı içinde güncellenir. eski çizgi denklemi temelinde oluşturulan veri serisi değişmeden kalırken;

raporlarda pivot çizelgeleri grafik görünümü veya ilgili rapor değiştiğinde Pivot tablo mevcut eğilim çizgileri kaydedilmez; bu, eğilim çizgilerini çizmeden veya PivotChart raporunuzu başka bir şekilde biçimlendirmeden önce rapor düzeninin gerekli gereksinimleri karşıladığından emin olmanız gerektiği anlamına gelir.

Eğilim çizgileri, grafik, histogram, standartlaştırılmamış düz alan grafikleri, çubuk grafikler, dağılım grafikleri, kabarcık grafikleri ve hisse senedi grafikleri gibi grafiklerde sunulan veri serilerini desteklemek için kullanılabilir.

3B, normalleştirilmiş, radar, pasta ve halka grafiklerindeki veri serilerine trend çizgileri ekleyemezsiniz.

Excel'in yerleşik işlevlerini kullanma

Excel'de ayrıca grafik alanının dışındaki trend çizgilerini çizmek için bir regresyon analiz aracı da bulunur. Bu amaç için kullanılabilecek çok sayıda istatistiksel çalışma sayfası işlevi vardır, ancak bunların tümü yalnızca doğrusal veya üstel regresyonlara izin verir.

Excel'in doğrusal regresyon oluşturmak için çeşitli işlevleri vardır, özellikle:

• AKIM;
• LINEST;
• EĞİM ve KESME.

Üstel bir trend çizgisi oluşturmak için çeşitli işlevlerin yanı sıra, özellikle:

• YÜKSEKLİK;
• LGRFPRIBL.

TREND ve BÜYÜME işlevlerini kullanarak regresyon oluşturma tekniklerinin neredeyse aynı olduğunu belirtmek gerekir. Aynı şey LINEST ve LGRFPRIBL işlev çifti için de söylenebilir. Bu dört işlev için bir değer tablosu oluşturmak, regresyon oluşturma sürecini biraz karmaşıklaştıran dizi formülleri gibi Excel özelliklerini kullanır. Ayrıca, bizim görüşümüze göre doğrusal regresyon oluşturmanın en kolay şekilde SLOPE ve INTERCEPT işlevleri kullanılarak gerçekleştirildiğine dikkat edin; bunlardan birincisi doğrusal regresyonun eğimini belirler ve ikincisi, y üzerindeki regresyon tarafından kesilen parçayı belirler. -eksen.

Regresyon analizi için yerleşik işlevler aracının avantajları şunlardır:

• eğilim çizgilerini tanımlayan tüm yerleşik istatistiksel işlevler için incelenen karakteristiğe ilişkin veri serilerinin oluşturulmasına yönelik oldukça basit, tekdüze bir süreç;
• oluşturulan veri serilerine dayalı trend çizgileri oluşturmak için standart metodoloji;
• Gerekli sayıda ileri veya geri adımla incelenen sürecin davranışını tahmin etme yeteneği.

Dezavantajları arasında Excel'in diğer (doğrusal ve üstel hariç) eğilim çizgileri türlerini oluşturmak için yerleşik işlevlere sahip olmaması yer alır. Bu durum çoğu zaman yeterince seçim yapılmasına izin vermez kesin model incelenen sürecin yanı sıra gerçeğe yakın tahminler elde etmek. Ayrıca TREND ve BÜYÜME fonksiyonları kullanıldığında trend çizgilerinin denklemleri bilinmemektedir.

Yazarların, regresyon analizinin gidişatını herhangi bir bütünlük derecesiyle sunmaya çalışmadıklarına dikkat edilmelidir. Onun asıl görevi spesifik örnekler olasılıkları göster Excel paketi yaklaşım problemlerini çözerken; neyi göstermek etkili araçlar Excel, regresyonlar ve tahminler oluşturmak için kullanılır; regresyon analizi konusunda kapsamlı bilgiye sahip olmayan bir kullanıcı tarafından bile bu tür problemlerin nasıl nispeten kolay çözülebileceğini göstermektedir.

Çözüm örnekleri özel görevler

Listelenen Excel araçlarını kullanarak belirli sorunları çözmeye bakalım.

Sorun 1

Bir motorlu taşımacılık işletmesinin 1995-2002 dönemine ilişkin kârı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. Eğilim çizgisi denklemlerini kullanarak, 1995-2004 yılları için her bir eğilim çizgisi için işletme karlarına ilişkin tablo halinde veriler elde edin.
4. İşletmenin 2003 ve 2004 yılı karı için bir tahmin yapın.

Sorunun çözümü

1. Excel çalışma sayfasının A4:C11 hücreleri aralığına, Ş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 diyagramı etkinleştiriyoruz ve yukarıda açıklanan yönteme göre Trend Çizgisi iletişim kutusunda trend çizgisi türünü seçtikten sonra (bkz. Şekil 1), diyagrama dönüşümlü olarak doğrusal, karesel ve kübik trend çizgileri ekliyoruz. Aynı iletişim kutusunda, Parametreler sekmesini açın (bkz. Şekil 2), yaklaşık (düzleştirilmiş) eğrinin adı alanına, eklenen trendin adını girin ve İleriye yönelik tahmin: dönemler alanına, değeri 2, çünkü iki yıl sonrası için kar tahmini yapılması planlanıyor. Regresyon denklemini ve yaklaşım güvenilirlik değeri R2'yi diyagram alanında görüntülemek için denklemi ekranda göster onay kutularını etkinleştirin ve yaklaşım güvenilirlik değerini (R^2) diyagrama yerleştirin. Daha iyi görsel algı için, Trend Çizgisi Formatı iletişim kutusunun Görünüm sekmesini kullandığımız oluşturulan trend çizgilerinin türünü, rengini ve kalınlığını değiştiriyoruz (bkz. Şekil 3). Eklenen trend çizgileri ile ortaya çıkan diyagram, Şekil 1'de gösterilmektedir. 5.
4. 1995-2004 yılları için her bir trend çizgisi için işletme karlarına ilişkin tablo halinde veri elde etmek. Şekil 2'de sunulan trend çizgisi denklemlerini kullanalım. 5. Bunu yapmak için D3:F3 aralığındaki hücrelere girin metin bilgisi seçilen trend çizgisinin türü hakkında: Doğrusal trend, Karesel trend, Kübik trend. Daha sonra D4 hücresine doğrusal regresyon formülünü girin ve doldurma işaretini kullanarak bu formülü kopyalayın c göreceli bağlantılar D5:D13 hücre aralığına. D4:D13 hücre aralığından doğrusal regresyon formülüne sahip her hücrenin, A4:A13 aralığından karşılık gelen bir hücreye argüman olarak sahip olduğuna dikkat edilmelidir. Benzer şekilde, ikinci dereceden regresyon için E4:E13 hücre aralığını doldurun ve kübik regresyon için F4:F13 hücre aralığını doldurun. Böylece işletmenin 2003 ve 2004 yılı kârına ilişkin bir tahmin derlendi. üç trendi kullanıyor. Ortaya çıkan değer tablosu Şekil 2'de gösterilmektedir. 6.

Sorun 2

1. Bir diyagram oluşturun.
2. Grafiğe logaritmik, güç ve üstel eğilim çizgileri ekleyin.
3. Elde edilen trend çizgilerinin denklemlerini ve bunların her biri için R2 yaklaşımının güvenilirlik değerlerini türetin.
4. Trend çizgisi denklemlerini kullanarak, 1995-2002 yılları için her bir trend çizgisi için işletmenin kârına ilişkin tablo halinde veriler elde edin.
5. Bu trend çizgilerini kullanarak şirketin 2003 ve 2004 karı hakkında bir tahmin yapın.

Sorunun çözümü

Problem 1'in çözümünde verilen metodolojiyi takip ederek, logaritmik, güç ve üstel eğilim çizgilerinin eklendiği bir diyagram elde ediyoruz (Şekil 7). Daha sonra, elde edilen trend çizgisi denklemlerini kullanarak, 2003 ve 2004 yılları için öngörülen değerleri de içeren, işletmenin karı için bir değerler tablosu dolduruyoruz. (Şekil 8).

İncirde. 5 ve Şek. logaritmik eğilime sahip modelin şuna karşılık geldiği açıktır: en küçük değer yaklaşım güvenilirliği

R2 = 0,8659

R2'nin en yüksek değerleri polinom eğilimi olan modellere karşılık gelir: ikinci dereceden (R2 = 0,9263) ve kübik (R2 = 0,933).

Sorun 3

Görev 1'de verilen, bir motorlu taşımacılık kuruluşunun 1995-2002 dönemine ilişkin kârına ilişkin veri tablosuyla aşağıdaki adımları uygulamanız gerekir.

1. TREND ve BÜYÜME işlevlerini kullanarak doğrusal ve üstel eğilim çizgileri için veri serileri elde edin.
2. TREND ve BÜYÜME işlevlerini kullanarak işletmenin 2003 ve 2004 yılı kârına ilişkin bir tahmin yapın.
3. Orijinal veriler ve elde edilen veri serileri için bir diyagram oluşturun.

Sorunun çözümü

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

1. işletmenin kârına ilişkin bilinen verilere karşılık gelen TREND fonksiyonunun değerleriyle doldurulması gereken D4:D11 hücre aralığını seçin;
2. Ekle menüsünden İşlev komutunu çağırın. Görüntülenen İşlev Sihirbazı iletişim kutusunda İstatistik kategorisinden TREND işlevini seçin ve ardından Tamam düğmesine tıklayın. Aynı işlem (Ekle işlevi) düğmesine basılarak da yapılabilir. standart paneli aletler.
3. Görüntülenen İşlev Bağımsız Değişkenleri iletişim kutusunda Bilinen_değerler_y alanına C4:C11 hücre aralığını girin; Bilinen_değerler_x alanında - B4:B11 hücre aralığı;
4. Girilen formülün bir dizi formülü haline gelmesi için ++ tuş birleşimini kullanın.

Formül çubuğuna girdiğimiz formül şu şekilde görünecektir: =(TREND(C4:C11,B4:B11)).

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

İşletmenin 2003 ve 2004 yılı kârına ilişkin tahmin yapmak. gerekli:

1. TREND fonksiyonu tarafından tahmin edilen değerlerin girileceği D12:D13 hücre aralığını seçin.
2. TREND işlevini çağırın ve beliren İşlev Bağımsız Değişkenleri iletişim kutusunda Bilinen_değerler_y alanına - C4:C11 hücre aralığını girin; Bilinen_değerler_x alanında - B4:B11 hücre aralığı; ve New_values_x alanında - B12:B13 hücre aralığı.
3. kombinasyonunu kullanarak bu formülü bir dizi formülüne dönüştürün Ctrl tuşları+ Üst Karakter + Enter.
4. Girilen formül şu şekilde görünecektir: =(TREND(C4:C11;B4:B11;B12:B13)) ve D12:D13 hücre aralığı, TREND fonksiyonunun öngörülen değerleriyle doldurulacaktır (bkz. 9).

Veri serisi, doğrusal olmayan bağımlılıkların analizinde kullanılan ve doğrusal karşılığı TREND ile tamamen aynı şekilde çalışan BÜYÜME işlevi kullanılarak benzer şekilde doldurulur.

Şekil 10'da formül görüntüleme modundaki tablo gösterilmektedir.

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

Sorun 4

Bir motorlu taşıt işletmesinin sevkıyat hizmeti tarafından cari ayın 1'inden 11'ine kadar olan süre için hizmet taleplerinin alınmasına ilişkin veri tablosu ile aşağıdaki işlemleri gerçekleştirmelisiniz.

1. Doğrusal regresyon için veri serilerini alın:EĞİM ve KESME işlevlerini kullanma; DOT işlevini kullanarak.
2. LGRFPRIBL işlevini kullanarak üstel regresyon için bir dizi veri elde edin.
3. Yukarıdaki işlevleri kullanarak, içinde bulunulan ayın 12'sinden 14'üne kadar olan dönem için sevk hizmetine başvuruların alınmasına ilişkin bir tahmin yapın.
4. Orijinal ve alınan veri serileri için bir diyagram oluşturun.

Sorunun çözümü

TREND ve BÜYÜME işlevlerinden farklı olarak, yukarıda listelenen işlevlerin (EĞİM, KESME NOKTASI, DİZGİ, LGRFPRIB) hiçbirinin regresyon olmadığını unutmayın. Bu işlevler yalnızca yardımcı bir rol oynar; gerekli parametreler gerileme.

EĞİM, KESME NOKTASI, DOĞRU, LGRFPRIB fonksiyonları kullanılarak oluşturulan doğrusal ve üstel regresyonlar için, dış görünüş EĞİLİM ve BÜYÜME fonksiyonlarına karşılık gelen doğrusal ve üstel regresyonların aksine, denklemleri her zaman bilinir.

1 . Hadi yapalım doğrusal regresyon, denkleme sahip:

y = mx+b

regresyon eğimi m, SLOPE işlevi tarafından belirlenir ve serbest terim b, KESMENOKTASI 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 girin;
2. m parametresinin değeri C19 hücresinde belirlenecektir. Kategoriden seç İstatistiksel fonksiyon Eğim; bilinen_değerler_y alanına B4:B14 hücre aralığını ve bilinen_değerler_x alanına A4:A14 hücre aralığını girin. Formül C19 hücresine girilecektir: =EĞİM(B4:B14,A4:A14);
3. Benzer bir teknik kullanılarak D19 hücresindeki b parametresinin değeri belirlenir. İçeriği şu şekilde görünecektir: =SEGMENT(B4:B14,A4:A14).Böylece, doğrusal regresyon oluşturmak için gereken m ve b parametrelerinin değerleri sırasıyla C19, D19 hücrelerinde saklanacaktır;
4. Daha sonra, doğrusal regresyon formülünü C4 hücresine şu biçimde girin: =$C*A4+$D. Bu formülde C19 ve D19 hücreleri mutlak referanslarla yazılmıştır (olası kopyalama sırasında hücre adresi değişmemelidir). İmza mutlak referans$, klavyeden veya imleci hücre adresinin üzerine getirdikten sonra 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 elde ediyoruz (Şekil 12). İstek sayısının tam sayı olması nedeniyle Hücre Formatı penceresinin Sayı sekmesinde ondalık basamak sayısını içeren sayı formatını 0 olarak ayarlamanız gerekmektedir.

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

y = mx+b

DOT işlevini kullanarak.

Bunun için:

1. DOT işlevini C20:D20: =(LINEST(B4:B14,A4:A14)) hücre aralığına bir dizi formülü olarak girin. Sonuç olarak, C20 hücresinde m parametresinin değerini ve D20 hücresinde b parametresinin değerini elde ederiz;
2. formülü D4 hücresine girin: =$C*A4+$D;
3. doldurma işaretini kullanarak bu formülü D4:D17 hücre aralığına kopyalayın ve istediğiniz veri serisini elde edin.

3 . Aşağıdaki denklemle üstel bir regresyon oluşturuyoruz:

y = bmx

LGRFPRIBL işlevi kullanılarak benzer şekilde gerçekleştirilir:

C21:D21 hücre aralığında LGRFPRIBL fonksiyonunu bir dizi formülü olarak giriyoruz: =( LGRFPRIBL (B4:B14,A4:A14)). Bu durumda m parametresinin değeri C21 hücresinde, b parametresinin değeri D21 hücresinde belirlenecek;

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

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

İncirde. Şekil 13'te gerekli hücre aralıklarıyla kullandığımız fonksiyonları ve formülleri görebileceğiniz bir tablo gösterilmektedir.

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

Mevcut verilere dayanarak bir olayı tahmin etmek için zaman yoksa trend çizgisini kullanabilirsiniz. Bunu kullanarak grafiğin oluşturulduğu verilerin hangi dinamiklere sahip olduğunu görsel olarak anlayabilirsiniz. Microsoft yazılım paketinin harika bir özelliği var Excel yeteneği Excel'deki bu araç - trend çizgisini kullanarak oldukça doğru bir tahmin oluşturmanıza yardımcı olacaktır. Bu analiz aracını oluşturmak oldukça basittir; aşağıda verilmiştir Detaylı Açıklama süreç ve trend çizgilerinin türleri.

Excel'de eğilim çizgisi. Inşaat süreci

Trend çizgisi ana veri analizi araçlarından biridir

Bir trend çizgisi oluşturmak için üç aşamayı tamamlamak gerekir:
1. Bir tablo oluşturun;
2.
3. Trend çizgisinin türünü seçin.

Hepsini topladıktan sonra gerekli bilgi, nihai sonucu elde etmeye yönelik adımlara doğrudan geçebilirsiniz.

Öncelikle kaynak verileri içeren bir tablo oluşturmalısınız. Sonraki seç gerekli aralık ve “Ekle” sekmesine giderek “Grafik” işlevini seçin. İnşaattan sonra nihai sonuca başvurabilirsiniz Ek özellikler, başlıklar ve başlıklar şeklinde. Bunu yapmak için grafiğe sol tıklayın, "Tasarımcı" adlı sekmeyi seçin ve "Düzen"i seçin. Yapmanız gereken bir sonraki şey sadece başlığı girmek.

Bir sonraki adım trend çizgisinin kendisini oluşturmaktır. Bunu yapmak için grafiği tekrar vurgulamanız ve görev şeridindeki "Düzen" sekmesini seçmeniz gerekir. Sonraki bu menü"Trend çizgisi" düğmesine tıklamanız ve "doğrusal yaklaşım" veya "üstel yaklaşım" seçeneğini seçmeniz gerekir.

Çeşitli l varyasyonlarıeğilim çizgileri

Kullanıcı tarafından girilen verilerin özelliklerine bağlı olarak, aşağıda sunulan seçeneklerden birini seçmeye değer; trend çizgisi türlerinin açıklaması;
Üstel yaklaşım. Giriş verilerindeki değişim oranı sürekli olarak artarsa, o zaman bu özel satır en yararlısı olacaktır. Ancak tabloya girilen veriler sıfır veya negatif özellikler içeriyorsa, bu tip kabul edilemez.

Doğrusal yaklaşım. Bu çizgi doğası gereği düzdür ve genellikle fonksiyonun yaklaşık bir sabitte arttığı veya azaldığı temel durumlarda kullanılır.

Logaritmik yaklaşım. Değer başlangıçta doğru ve hızlı bir şekilde büyüyorsa veya tam tersine düşüyorsa, ancak daha sonra bir miktar değerden sonra dengeleniyorsa, bu trend çizgisi kullanışlı olacaktır.

Polinom yaklaşımı. Değişken artış ve azalışlar bu çizginin karakteristik özelliğidir. Ayrıca polinomların (polinomların) derecesi maksimum ve minimumların sayısına göre belirlenir.

Güç yaklaşımı. Bir değerin monoton artışını ve azalışını karakterize eder ancak veri negatif ve sıfır değerlere sahipse uygulanması imkansızdır.

Hareketli ortalama. Tüm salınım noktalarını yumuşatarak birinin diğerine doğrudan bağımlılığını açıkça göstermek için kullanılır. Bu, iki bitişik nokta arasındaki ortalama değer alınarak elde edilir. Böylece grafiğin ortalaması alınır ve puan sayısı kullanıcı tarafından “Puan” menüsünde seçilen değere düşürülür.

Nasıl kullanılır? D Ekonomik seçenekleri tahmin etmek için tam olarak polinom polinom derecesi çeşitli ilkelere dayanarak belirlenen bir çizgi: tahminin gerekli olduğu dönemde göstergenin ekonomik dinamiklerinin yanı sıra belirleme katsayısının maksimuma çıkarılması.

Oluşumun tüm aşamalarını takip ederek ve özellikleri anlayarak, gerçek tahminlere yalnızca belli belirsiz karşılık gelen bir ana trend çizgisi oluşturabilirsiniz. Ancak parametreleri belirledikten sonra tahminin daha gerçekçi bir resminden bahsedebiliriz.

Excel'de eğilim çizgisi. Parametre ayarlarıişlevsel çizgide

“Trend çizgisi” butonuna tıklayarak “” adlı gerekli menüyü seçin. Ekstra seçenekler" Görünen pencerede, "Trend çizgisi formatı"na tıklayın ve ardından "R^2 yaklaşım güvenilirlik değerini diyagrama yerleştirin" değerinin yanındaki kutuyu işaretleyin. Bundan sonra ilgili düğmeye tıklayarak menüyü kapatın. Diyagramın kendisinde R^2 = 0,6442 katsayısı görünür.

Bundan sonra yapılan değişiklikleri iptal ediyoruz. Grafiği seçip “Yerleşim” sekmesine tıklayarak, ardından “Trend çizgisi”ne ve ardından “Hayır”a tıklayın. Ardından, “Trend çizgisini formatla” işlevine giderek, polinomçizgisini çizin ve dereceyi değiştirerek R^2= 0,8321 değerini elde etmeye çalışın.

Formülleri görüntülemek veya standart tahminlerden farklı diğer tahmin çeşitlerini oluşturmak için değerlerle ve özellikle polinomlarla denemeler yapmaktan korkmamak yeterlidir. Böylece yalnızca bir tanesini kullanarak Excel programı, giriş verilerine dayanarak oldukça doğru bir tahmin oluşturabilirsiniz.

Hizmetin amacı. Hizmet, en küçük kareler yöntemini (LS) (eğilim denklemini bulma örneğine bakın) ve koşullu sıfırdan yöntemi kullanarak y t zaman serisinin eğilim parametrelerini çevrimiçi olarak hesaplamak için kullanılır. Bunun için bir denklem sistemi oluşturulmuştur:
a 0 n + a 1 ∑t = ∑y
a 0 ∑t + a 1 ∑t 2 = ∑y t

ve bunun gibi bir tablo:

T	sen	t 2	y 2	t y	YT)
1
...	...	...	...	...	...
N
TOPLAM	∑	∑	∑	∑	∑

Talimatlar. Veri miktarını belirtin (satır sayısı). Ortaya çıkan çözüm şu şekilde saklanır: Kelime dosyası ve Excel.

Satır sayısı (kaynak verileri)
Koşullu başlangıçtan itibaren zamanı sayma yöntemini kullanın(koordinatların kökenini dinamik serisinin ortasına aktarın)
",1);">

Bir zaman serisinin eğilimi, uzun vadeli etkiye sahip olan ve incelenen göstergenin genel dinamiklerini oluşturan bir dizi faktörü karakterize eder.

Koşullu başlangıçtan itibaren zamanı sayma yöntemi

Parametreleri tanımlamak için matematiksel fonksiyon Zaman serisindeki bir eğilimi analiz ederken, zamanı koşullu bir başlangıçtan itibaren sayma yöntemi kullanılır. ∑t i olacak şekilde zaman serisindeki gösterime dayanmaktadır. Bu durumda, tek sayıda seviyeye sahip bir dinamik serisinde, serinin ortasında yer alan seviyenin seri numarası sıfır değeriyle gösterilir ve zaman sayımının koşullu başlangıcı olarak alınır. Sonraki tüm seviyelerin +1'i ve önceki tüm seviyelerin -1'i. Örneğin, zamanı belirtirken şunlar olacaktır: –2, –1, 0, +1, +2. Çift sayıda düzeyde, satırın üst yarısının (ortadan) seri numaraları –1, –3, –5 sayılarıyla belirtilir ve satırın alt yarısı +1, + olarak gösterilir. 3, +5.

Örnek. İstatistiksel çalışma nüfus dinamikleri.

1. Zincirleme, temel ve ortalama dinamik göstergelerini kullanarak sayılardaki değişimi değerlendirin ve sonuçlarınızı yazın.
2. Analitik hizalama yöntemini kullanarak (düz çizgi ve parabol, OLS kullanarak katsayıların belirlenmesi), olgunun gelişimindeki ana eğilimi (Komi Cumhuriyeti nüfusu) belirleyin. Hataları ve yaklaşım katsayılarını kullanarak ortaya çıkan modellerin kalitesini değerlendirin.
3. Grafik Sihirbazını kullanarak doğrusal ve parabolik eğilim katsayılarını belirleyin. Nokta ver ve aralık tahminleri 2010 yılı numarası. Sonuçlarınızı yazın.

1990	1996	2001	2002	2003	2004	2005	2006	2007	2008
1249	1133	1043	1030	1016	1005	996	985	975	968

Analitik hizalama yöntemi

A) Doğrusal Denklem trend şu şekildedir: y = bt + a
1. En küçük kareler yöntemini kullanarak denklemin parametrelerini bulun. Koşullu bir başlangıçtan itibaren zamanı sayma yöntemini kullanıyoruz.
Doğrusal bir eğilim için en küçük kareler denklem sistemi şu şekildedir:
a 0 n + a 1 ∑t = ∑y
a 0 ∑t + a 1 ∑t 2 = ∑y t

T	sen	t 2	y 2	t y
-9	1249	81	1560001	-11241
-7	1133	49	1283689	-7931
-5	1043	25	1087849	-5215
-3	1030	9	1060900	-3090
-1	1016	1	1032256	-1016
1	1005	1	1010025	1005
3	996	9	992016	2988
5	985	25	970225	4925
7	975	49	950625	6825
9	968	81	937024	8712
0	10400	330	10884610	-4038

Verilerimiz için denklem sistemi şu şekli alacaktır:
10a 0 + 0a 1 = 10400
0a 0 + 330a 1 = -4038
İlk denklemden 0'ı ifade edip onu ikinci denklemde yerine koyuyoruz
0 = -12,236, 1 = 1040 elde ederiz
Trend denklemi:
y = -12,236 t + 1040

Mutlak yaklaşım hatasını kullanarak trend denkleminin kalitesini değerlendirelim.

%5-%7 arasındaki yaklaşım hatası şunu gösterir: iyi seçim orijinal verilere eğilim denklemleri.

b) parabolik hizalama
Trend denklemi y = 2 + bt + c'dedir
1. En küçük kareler yöntemini kullanarak denklemin parametrelerini bulun.
En küçük kareler denklem sistemi:
a 0 n + a 1 ∑t + a 2 ∑t 2 = ∑y
a 0 ∑t + a 1 ∑t 2 + a 2 ∑t 3 = ∑yt
a 0 ∑t 2 + a 1 ∑t 3 + a 2 ∑t 4 = ∑yt 2

T	sen	t 2	y 2	t y	t 3	4	t 2 yıl
-9	1249	81	1560001	-11241	-729	6561	101169
-7	1133	49	1283689	-7931	-343	2401	55517
-5	1043	25	1087849	-5215	-125	625	26075
-3	1030	9	1060900	-3090	-27	81	9270
-1	1016	1	1032256	-1016	-1	1	1016
1	1005	1	1010025	1005	1	1	1005
3	996	9	992016	2988	27	81	8964
5	985	25	970225	4925	125	625	24625
7	975	49	950625	6825	343	2401	47775
9	968	81	937024	8712	729	6561	78408
0	10400	330	10884610	-4038	0	19338	353824

Verilerimiz için denklem sistemi şu şekildedir:
10a 0 + 0a 1 + 330a 2 = 10400
0a 0 + 330a 1 + 0a 2 = -4038
330a 0 + 0a 1 + 19338a 2 = 353824
0 = 1,258, 1 = -12,236, 2 = 998,5 elde ederiz.
Trend denklemi:
y = 1,258t 2 -12,236t+998,5

Parabolik eğilim denklemi için yaklaşım hatası.

Hata %7'den az olduğundan bu denklem trend olarak kullanılabilir.

Parabolik hizalama için minimum yaklaşım hatası. Ek olarak, R2 belirleme katsayısı doğrusal olandan daha yüksektir. Bu nedenle tahmin için parabolik bir denklemin kullanılması gereklidir.

Aralık tahmini.
Tahmin edilen göstergenin ortalama kare hatasının kökünü belirleyelim.

m = 1 - trend denklemindeki etkileyen faktörlerin sayısı.
Uy = y n+L ± K
Nerede

L - kurşun süresi; y n+L - zamanın (n + L)'inci noktasındaki modele göre nokta tahmini; n, zaman serisindeki gözlemlerin sayısıdır; Sy, tahmin edilen göstergenin standart hatasıdır; T sekmesi - α anlamlılık düzeyi ve eşit serbestlik derecesi sayısı için Öğrenci testinin tablo değeri n-2.
Öğrenci tablosunu kullanarak Ttable'ı buluyoruz
T tablosu (n-m-1;α/2) = (8;0,025) = 2,306
Nokta tahmini, t = 10: y(10) = 1,26*10 2 -12,24*10 + 998,5 = 1001,89 bin kişi.

1001.89 - 71.13 = 930.76 ; 1001.89 + 71.13 = 1073.02
Aralık tahmini:
t = 9+1 = 10: (930,76;1073,02)