Bir grafiğe trend çizgisi nasıl eklenir. Microsoft Excel'deki tahmin araçları

Öngörü çok önemli unsur ekonomiden mühendisliğe kadar hemen hemen her faaliyet alanı. var çok sayıda yazılım bu alanda uzmanlaşmıştır. Ne yazık ki, tüm kullanıcılar normalin ne olduğunu bilmiyor. elektronik tablo işlemcisi Excel'in cephaneliğinde tahmin gerçekleştirmek için etkinlikleri açısından daha düşük olmayan araçlar bulunur. profesyonel programlar. Bu araçların neler olduğunu ve pratikte nasıl tahmin yapılacağını öğrenelim.

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

Yöntem 1: trend çizgisi

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

Son 12 yıldaki bu göstergeye ilişkin verilere dayanarak işletmenin 3 yıldaki kâr miktarını tahmin etmeye çalışalım.

Yöntem 2: TAHMİN deyimi

Tablo verileri için ekstrapolasyon şu şekilde yapılabilir: standart fonksiyon excel TAHMİN ETMEK. Bu bağımsız değişken istatistiksel araçlar kategorisine aittir ve aşağıdaki sözdizimine sahiptir:

TAHMİN(X, bilinen_y değerleri, bilinen x değerleri)

"X" fonksiyon değeri belirlenecek argümandır. Bizim durumumuzda argüman, tahminin yapılması gereken yıl olacaktır.

"Bilinen y değerleri" fonksiyonun bilinen değerlerinin tabanıdır. Bizim durumumuzda rolü, önceki dönemlerin kâr miktarıdır.

"Bilinen x değerleri" fonksiyonun bilinen değerlerine karşılık gelen argümanlardır. Onların rolünde, önceki yılların karları hakkında bilgilerin toplandığı yılların numaralandırmasına sahibiz.

Doğal olarak, zaman periyodu bir argüman olarak hareket etmek zorunda değildir. Örneğin, sıcaklık olabilir ve fonksiyonun değeri, ısıtıldığında suyun genleşme seviyesi olabilir.

Bu şekilde hesaplama yapılırken doğrusal regresyon yöntemi kullanılır.

Operatörü kullanmanın nüanslarına bakalım TAHMİN ETMEK belirli bir örnek üzerinde. Aynı tabloyu ele alalım. 2018 için kar tahminini bilmemiz gerekecek.

Ancak unutmayın ki trend çizgisi yapımında olduğu gibi tahmin periyodundan önceki süre veri tabanının biriktiği tüm periyodun %30'unu geçmemelidir.

Yöntem 3: TREND operatörü

Tahmin için başka bir işlevi kullanabilirsiniz - AKIM. Aynı zamanda istatistiksel operatörler kategorisine aittir. Sözdizimi bir aracınkine çok benzer TAHMİN ETMEK ve şöyle görünüyor:

TREND(bilinen_y-değerleri, bilinen_x-değerleri, yeni_x-değerleri, [sabit])

Gördüğünüz gibi argümanlar "Bilinen y değerleri" Ve "Bilinen x değerleri" operatörün benzer öğelerine tam olarak karşılık gelir TAHMİN ETMEK ve bağımsız değişken "Yeni x değerleri" argümanla eşleşir "X"önceki enstrüman ek olarak, AKIM ek bir argüman var "Devamlı", ancak zorunlu değildir ve yalnızca sabit çarpanların varlığında kullanılır.

Bu işleç en etkin şekilde şu durumlarda kullanılır: doğrusal bağımlılık fonksiyonlar.

Bu aracın aynı veri dizisiyle nasıl çalışacağını görelim. Sonuçları karşılaştırmak için 2019 yılını tahmin noktası olarak tanımlayalım.

Yöntem 4: BÜYÜME operatörü

Excel'de tahmin yapabileceğiniz bir diğer fonksiyon ise BÜYÜME operatörüdür. Aynı zamanda istatistiksel enstrüman grubuna aittir, ancak öncekilerden farklı olarak, hesaplama yaparken doğrusal bağımlılık yöntemini değil, üstel yöntemi kullanır. Bu aracın sözdizimi şöyle görünür:

BÜYÜME(bilinen_y-değerleri, bilinen_x-değerleri, yeni_x-değerleri, [sabit])

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

Yöntem 5: DOT operatörü

Şebeke DOT hesaplamada doğrusal yaklaşım yöntemini kullanır. Aracın kullandığı doğrusal ilişki yöntemi ile karıştırılmamalıdır. AKIM. Sözdizimi şöyle görünür:

DOT(bilinen_y'ler, bilinen_x'ler, yeni_x'ler, [sabit], [istatistikler])

Son iki bağımsız değişken isteğe bağlıdır. İlk ikisine önceki yöntemlerden aşinayız. Ancak bu işlevde yeni değerlere işaret eden bir bağımsız değişkenin eksik olduğunu fark etmiş olabilirsiniz. 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ı ayrı ayrı hesaplamamız ve sonucu kârın son gerçek değerine eklememiz gerekir. operatörün hesaplanması DOT yıl sayısı ile çarpılır.

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

Yöntem 6: LFPRIB operatörü

Bakacağımız son araç, LGRFPRIBL. Bu işleç, üstel yaklaşım yöntemine dayalı olarak hesaplamalar gerçekleştirir. Sözdizimi aşağıdaki yapıya sahiptir:

LGFPRIB(bilinen_y-değerleri, bilinen_x-değerleri, yeni_x-değerleri, [sabit], [istatistikler])

Gördüğünüz gibi, tüm bağımsız değişkenler karşılık gelen öğeleri tamamen tekrar eder. önceki fonksiyon. Tahmini hesaplama algoritması biraz değişecektir. İşlev, gelir miktarının bir dönemde, yani bir yılda kaç kez değiştiğini gösteren üstel bir trend hesaplayacaktır. Son fiili dönem ile ilk planlanan dönem arasındaki kâr farkını bulmamız ve bunu planlanan dönem sayısıyla çarpmamız gerekecek. (3) ve sonuca son fiili periyodun toplamını ekleyin.

Üstel yaklaşım yöntemi kullanılarak hesaplanan 2019 yılında öngörülen kar miktarı 4639,2 bin ruble olacak ve bu da önceki yöntemlerle hesaplanarak elde edilen sonuçlardan çok farklı değil.

Excel programında tahmin yapmanın hangi yollarla mümkün olduğunu öğrendik. Grafiksel olarak bu, bir eğilim çizgisi kullanılarak ve analitik olarak bir dizi yerleşik istatistiksel işlev kullanılarak yapılabilir. Aynı verilerin bu operatörler tarafından işlenmesi sonucunda farklı bir sonuç elde edilebilmektedir. Ama hepsi kullandıkları için bu şaşırtıcı değil farklı yöntemler hesaplama. Dalgalanma küçükse, tüm bu seçenekler aşağıdakiler için geçerlidir: özel durum nispeten güvenilir sayılabilir.

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

Model olarak trend

Bu fenomeni tanımlayan bir model kurarsak, oldukça basit ve çok kullanışlı araç etkileyen faktörlerin önemini veya yeterliliğini kontrol etmek için herhangi bir karmaşık hesaplama veya zaman maliyeti gerektirmeyen tahmin için.

Peki, model olarak trend nedir? Bu, göstergenin (Y) zamandaki (t) değişime regresyon bağımlılığını ifade eden denklemin hesaplanmış katsayıları kümesidir. Yani, bu, daha önce düşündüklerimizle tamamen aynı gerilemedir, burada yalnızca zaman göstergesi etkileyen faktördür.

Önemli!

Hesaplamalarda t genellikle yıl, ay veya hafta anlamına gelmez, ancak seri numarası incelenen istatistiksel popülasyondaki dönem - dinamik bir seri. Örneğin, zaman serileri birkaç yıl boyunca incelenirse ve veriler aylık olarak kaydedilirse, 1'den 12'ye ve tekrar baştan sıfırlanabilir ay numaralandırmasının kullanılması temelde yanlıştır. Serinin çalışması, örneğin Mart'tan itibaren başlarsa, t'nin değeri olarak 3'ü (yılın üçüncü ayı) kullanırsa, bu çalışılan popülasyondaki ilk değerse, o zaman sıra numarası olmalıdır. 1 olmak

Lineer trend modeli

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

Lineer trend denkleminin genel görünümü:

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

a 0'ın sıfır regresyon katsayısı olduğu, yani, etkileyen faktör sıfır ise Y ne olacaktır, a 1, çalışılan gösterge Y'nin etkileme faktörü t'ye bağımlılık derecesini ifade eden regresyon katsayısıdır, Ɛ bir rastgele bileşen veya standart hata aslında Y'nin gerçek değerleri ile hesaplananlar arasındaki farktır. t etkileyen tek faktördür - zaman.

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

Modelin katsayılarını hesaplayabilirsiniz. standart Yöntem en küçük kareler (LSM). Tüm bu hesaplamalarla Microsoft Excel tek başına bir patlama ile başa çıkıyor, ayrıca, doğrusal bir trend modeli veya hazır bir tahmin elde etmek için, aşağıda ayrı ayrı analiz edeceğimiz beş yol var.

Doğrusal bir trend elde etmenin grafiksel yolu

Bu ve diğer tüm örneklerde, aynı zaman serisini kullanacağız - yıllık olarak hesaplanan ve kaydedilen GSYİH seviyesi, bizim durumumuzda çalışma 2004'ten 2012'ye kadar olan dönemi kapsayacaktır.

İlk verilere bir sütun daha ekleyelim, 2004'ten 2012'ye kadar belirtilen süre için kaydedilen tüm GSYİH değerlerinin seri numaralarını t olarak adlandıracağız ve artan sırada sayılarla işaretleyelim. – 9 yaşında veya 9 dönem.

Excel boş bir alan ekleyecek - gelecekteki grafik için işaretleme, bu grafiği seçin ve menü çubuğunda görünen sekmeyi etkinleştirin - Yapıcı, bir düğme arıyorum veri seç, açılan pencerede düğmesine basın Eklemek. Bir açılır pencere, grafik çizmek için verileri seçmenizi isteyecektir. alan değeri olarak satır adı grafiğin adına en çok karşılık gelen metni içeren hücreyi seçin. sahada X değerleri t sütununun hücre aralığını gösterir - etkileyen faktör. sahada Y değerleri bilinen GSYİH (Y) değerlerine sahip sütundaki hücrelerin aralığını gösterir - incelenen gösterge.

Belirtilen alanları doldurduktan sonra, birkaç kez OK düğmesine basın ve tamamlanmış dinamik grafiği elde edin. Şimdi, grafik çizgisinin kendisine sağ tıklayın ve beliren bağlam menüsünden öğeyi seçin. Trend çizgisi ekle

Bir trend çizgisi oluşturmak için parametreleri ayarlamak için bir pencere açılacaktır, burada model türleri arasında seçeceğiz Doğrusal, P öğelerinin önüne bir onay işareti koyun Diyagram üzerinde denklem oluşturma Ve Yaklaşım güven değeri R2'yi şemaya yerleştirin, bu, grafikte önceden oluşturulmuş trend çizgisini ve ayrıca model ekranının matematiksel versiyonunu hazır bir denklem ve model kalite göstergesi şeklinde görüntülemek için yeterli olacaktır. R2. Tahmini grafikte görüntülemekle ilgileniyorsanız, incelenen gösterge arasındaki boşluğu görsel olarak değerlendirmek için alanda belirtin. İleriye dönük tahmin ilgi dönemi sayısı.

Aslında, bu yöntemle ilgili olan tek şey elbette, görüntülenen doğrusal trend denkleminin modelin kendisi olduğunu, model için hesaplanan değerleri elde etmek için bir formül olarak kullanılabilecek ve buna göre ekleyebilirsiniz. makaleye ekli örnekte yaptığımız kesin tahmin değerleri (grafikte gösterilen tahmin yalnızca yaklaşık olarak tahmin edilebilir).

DOT Formülünü Kullanarak Doğrusal Bir Trend Oluşturma

Bu yöntemin özü, işlevi kullanarak doğrusal eğilim katsayılarını bulmaktır. DOT, daha sonra, bu etkileme katsayılarını denklemde yerine koyarak, tahmine dayalı bir model elde ederiz.

İki bitişik hücreyi seçmemiz gerekiyor (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) işlevi “= DOT (” yazarak çağırırız, ardından Excel, bu işlevler için neyin gerekli olduğuna dair ipuçlarını görüntüler, yani:

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 vurgulanır) ve noktalı virgül koyun
2. X'i etkileyen faktörlerin aralığını belirtin (bizim durumumuzda bu, t göstergesidir, ekran görüntüsünde yeşil renkle vurgulanan dönemlerin seri sayısıdır) ve noktalı virgül koyun
3. fonksiyon için bir sonraki gerekli parametre, sabiti hesaplamanın gerekli olup olmadığını belirlemektir, çünkü başlangıçta sabit (katsayı) olan bir modeli düşünüyoruz. 0 ), ardından "DOĞRU" veya "1" ve bir noktalı virgül koyarız
4. o zaman istatistik parametrelerinin hesaplanmasının gerekli olup olmadığını belirtmeniz gerekir (bu seçeneği düşünüyor olsaydık, başlangıçta birkaç satır daha düşük olan "formülün altında" aralığını seçmemiz gerekirdi). İstatistik parametrelerini hesaplama ihtiyacını belirtin, yani katsayılar için standart hata değeri, determinizm katsayısı, standart hata Y için, Fisher kriteri, serbestlik derecesi vb., yalnızca ne anlama geldiklerini anladığınızda anlam ifade eder, bu durumda ya "DOĞRU" ya da "1" koyarız. Öğrenmeye çalıştığımız basitleştirilmiş modelleme durumunda, formülü yazmanın bu aşamasında "YANLIŞ" veya "0" koyar ve sonuna bir kapatma parantezi ")" ekleriz.
5. formülü "canlandırmak", yani gerekli tüm parametreleri yazdıktan sonra çalışmasını sağlamak için Enter düğmesine basmak yeterli değildir, sırayla üç tuşa basmanız gerekir: Ctrl, Shift, Enter

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

Hesaplanan değerleri almak için Y modele göre ve buna göre bir tahmin almak için Excel hücresindeki formülü değiştirmeniz yeterlidir ve bunun yerine T gerekli dönem numarasına sahip 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, X olarak dönemin seri numarasını belirttiğiniz ve Y olarak bir durumda - gerçek GSYİH ve diğerinde - hesaplanan (ekran görüntüsünde, şemada) iki grafik oluşturabilirsiniz. Sağdaki).

Analiz Araç Paketindeki Regresyon Aracını Kullanarak Doğrusal Bir Trend Oluşturma

Makalede, aslında, bu yöntem tam olarak açıklanmaktadır, tek fark, ilk verilerimizde yalnızca bir etkileyen faktör olmasıdır. X (dönem numarası - T ).

Yukarıdaki şekilde görüldüğü gibi, bilinen GSYİH değerlerine sahip veri aralığı olarak vurgulanır giriş aralığı Y ve karşılık gelen periyot numaralarıyla aralık t - X giriş aralığı olarak. Analiz Paketi tarafından yapılan hesaplamaların sonuçları şu adreste görüntülenir: ayrı sayfa ve benim tarafımdan sarıya boyanmış hücrelerle ilgilendiğimiz bir dizi tabloya (aşağıdaki şekle bakın) benziyor ve yeşil renkler. Yukarıdaki makalede açıklanan sıraya benzetilerek, elde edilen katsayılardan doğrusal bir trend modeli oluşturulur. y=169 572,2+138 454,3*t, hangi tahminlerin yapıldığı temelinde.

TREND işlevi aracılığıyla doğrusal bir eğilimle tahmin

Bu yöntem, model parametrelerini hesaplamak ve elde edilen katsayıları bir tahmin elde etmek için bir formül olarak manuel olarak bir hücrede değiştirmek gibi daha önce gerekli olan adımları atlaması bakımından öncekilerden farklıdır, bu işlev sadece temel alınarak hazır hesaplanmış bir tahmin değeri verir. bilinen ilk veriler

Hedef hücreye (sonucu görmek istediğimiz hücreye) işaretini koyuyoruz. eşittir ve sihirli işlevi " yazarak çağırın. AKIM(”, o zaman seçmeniz gerekir, yani noktalı virgül koyduktan sonra ve bilinen X değerlerine sahip, yani t periyot sayısına sahip bir aralık seçiyoruz., bilinen GSYİH değerlerine sahip sütuna karşılık gelen, yine bir noktalı virgül koyun ve tahmin yaptığımız dönemin numarasının bulunduğu hücreyi seçin (ancak bizim durumumuzda, dönem sayısı referans olarak gösterilemez) hücreye, ancak doğrudan formüldeki bir sayı ile), sonra başka bir noktalı virgül koyun ve belirtin DOĞRU veya 1 , katsayının hesaplanması için onay olarak 0 nihayet koymak kapanış parantezi ve tuşuna basın Girmek.

Bu yöntemin dezavantajı, ne model denklemini ne de katsayılarını göstermemesidir, bu nedenle şu veya bu modele dayanarak şu ve bu tür bir tahmin aldığımız ve bunun olmadığı söylenemez. modelin kalite parametrelerinin yansıması. , alınan tahmini dikkate almanın mantıklı olup olmadığını söylemenin mümkün olacağı aynı belirleme katsayısı.

TAHMİN işlevi aracılığıyla doğrusal bir trendle tahmin

Bu işlevin özü, öncekiyle tamamen aynıdır, tek fark, formüldeki başlangıç ​​​​verilerini belirleme sırasındadır ve bir katsayının varlığı veya yokluğu için bir ayar yoktur. 0 (yani fonksiyon, bu katsayının zaten var olduğunu ima eder)

Yukarıdaki şekilden de görebileceğiniz gibi hedef hücreye " yazıyoruz. =TAHMİN(” ve ardından belirtin dönem numaralı hücre, bunun için değeri doğrusal bir eğilime, yani tahmine göre hesaplamak, ardından noktalı virgül koymak ve ardından seçmek için gerekli olan bilinen Y değerleri aralığı, yani bilinen GSYİH değerlerine sahip sütun, ardından bir noktalı virgül koyun ve vurgulayın bilinen X değerlerine sahip aralık, yani periyot numaralarıyla t, bilinen GSYİH değerlerine sahip sütuna karşılık gelen ve son olarak ayarlanan kapanış parantezi ve tuşuna basın Girmek.

Elde edilen sonuçlar, yukarıdaki yöntemde olduğu gibi, yalnızca doğrusal bir trend modeli kullanılarak tahmin değerinin hesaplanmasının nihai sonucudur, herhangi bir hata veya matematiksel olarak modelin kendisi vermez.

Makaleyi özetlemek

Kendimiz için belirlediğimiz mevcut hedefe bağlı olarak, yöntemlerden her birinin diğerleri arasında en kabul edilebilir olduğunu söyleyebiliriz. İlk üç yöntem hem anlam hem de sonuç olarak birbiriyle kesişir ve modelin ve kalitesinin tanımlanmasının gerekli olduğu az çok ciddi işler için uygundur. Buna karşılık, son iki yöntem de birbiriyle aynıdır ve örneğin "Gelecek yıl için satış tahmini nedir?" Sorusuna olabildiğince çabuk yanıt verecektir.

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

Excel'de bir grafiğe nasıl trend çizgisi ekleneceğini görelim.

Grafiğe trend çizgisi ekleme

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

Excel'deki bir eğilim çizgisi, yaklaşık bir fonksiyonun grafiğidir. Neden gerekli - istatistiksel verilere dayalı tahminler yapmak. Bunun için çizgiyi uzatmak ve değerlerini belirlemek gerekir.

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

Dikkat!!! Eğilim çizgisi eklenemez aşağıdaki türler grafikler ve çizelgeler:

• taç yaprağı;
• dairesel;
• yüzey;
• halka şeklinde;
• hacim;
• birikimi ile.

Excel'de Eğilim Çizgisi Denklemi

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

Kullanıcı girişindeki eğilimi en doğru şekilde gösteren ekran türünü seçmelisiniz. Seçeneklere bir göz atalım.

Doğrusal yaklaşım

Geometrik gösterimi düz bir çizgidir. Bu nedenle, sabit bir oranda artan veya azalan bir göstergeyi göstermek için doğrusal bir yaklaşım kullanılır.

Yönetici tarafından 10 ay boyunca imzalanan koşullu sözleşme sayısını göz önünde bulundurun:

Şuradaki verilere göre: Excel elektronik tablo bir dağılım grafiği oluşturalım (doğrusal türün gösterilmesine yardımcı olacaktır):

Grafiği seçin - "bir trend çizgisi ekleyin". Parametrelerde lineer tipi seçin. Excel'de yaklaşım güvenilirliğinin değerini ve trend çizgisinin denklemini ekliyoruz ("Parametreler" penceresinin altındaki kutuları işaretlemeniz yeterlidir).

Sonucu elde ederiz:

Not! Doğrusal yaklaşım tipinde, veri noktaları mümkün olduğu kadar düz bir çizgiye yakın yerleştirilir. Bu tip aşağıdaki denklemi kullanır:

y = 4,503x + 6,1333

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

Grafikteki düz çizgi, yöneticinin iş kalitesinde sürekli bir artışı göstermektedir. Yaklaşım güvenilirlik değeri 0,9929'dur ve bu, hesaplanan düz çizgi ile orijinal veriler arasında iyi bir uyum olduğunu gösterir. Tahminler doğru olmalıdır.

Örneğin 11. periyotta imzalanan sözleşme sayısını tahmin etmek için, denklemde x yerine 11 sayısını yazmanız gerekir. Hesaplamalar sırasında 11. periyotta bu menajerin 55-56 sözleşme imzalayacağını öğreniyoruz.

üstel trend çizgisi

Bu tip, giriş değerleri sürekli artan bir oranda değişiyorsa faydalı olacaktır. Üstel yaklaşım, sıfır veya negatif özelliklerin varlığında uygulanmaz.

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

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

Denklem aşağıdaki forma sahiptir:

y = 7.6403е^-0.084x

• burada 7.6403 ve -0.084 sabittir;
• e, doğal logaritmanın tabanıdır.

Yaklaşım güvenilirlik endeksi 0,938 idi - eğri verilere karşılık gelir, hata minimumdur, tahminler doğru olacaktır.

Eğilim Çizgisini Excel'de Günlüğe Kaydet

Göstergede aşağıdaki değişiklikler için kullanılır: önce hızlı bir artış veya azalma, ardından göreceli kararlılık. Optimize edilmiş eğri, miktarın bu "davranışına" iyi uyum sağlar. Logaritmik eğilim, piyasaya yeni sürülen yeni bir ürünün satışını tahmin etmek için uygundur.

Açık İlk aşamaÜreticinin görevi müşteri tabanını artırmaktır. Ürünün kendi alıcısı varken elinde bulundurmalı, sunmalıdır.

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

R2, minimum yaklaşım hatasını gösteren 1'e (0,9633) yakın bir değerdir. Sonraki dönemlerde satış hacimlerini tahmin edeceğiz. Bunu yapmak için, denklemde x yerine dönem sayısını yazmanız gerekir.

Örneğin:

Tahmin rakamlarını hesaplamak için aşağıdaki formül kullanıldı: =272,14*LN(B18)+287,21. B18, dönem numarasıdır.

Excel'de Polinom Eğilim Çizgisi

Bu eğri artan ve azalan değişkenlere sahiptir. Polinomlar (polinomlar) için derece belirlenir (maksimum ve minimum değerlerin sayısına göre). Örneğin, bir ekstremum (minimum ve maksimum) ikinci derecedir, iki ekstremum üçüncü derecedir, üçü dördüncü derecedir.

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

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

Trend çizgisi içeren grafik örneklerini indirin

Ancak böyle bir eğilim, az çok doğru tahminler yapmanızı sağlar.

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

1) Trend nedir (bu başlangıç ​​noktası olarak önemlidir)
2) Trend çizgileri oluşturmak
3) Pratik ticarette kullanım
4) Yöntemin öznelliği

1) Trend nedir?
_________________
Bir trend çizgisinin inşasına geçmeden önce, trendin kendisiyle doğrudan ilgilenmek gerekir. Akademik tartışmalara girmeyeceğiz ve basit olması için aşağıdaki formülü kabul edeceğiz:

Bir trend (yukarı doğru), her bir sonraki yüksek (ve düşük) bir öncekinden daha yüksek olan, artan inişler ve çıkışlar dizisidir.

Bir trend (aşağı doğru), birbirini takip eden her bir düşük (ve yüksek) seviyenin bir öncekinden DÜŞÜK olduğu, düşen (azalan) yüksek ve düşük seviyeler dizisidir.

Trend çizgisi, iki yüksek (eğilim aşağıysa) veya iki dip (eğilim yukarıysa) arasında çizilen bir çizgidir. Yani aslında trend çizgisi bize grafikte bir trend olduğunu gösteriyor! Ancak olmayabilir (bir daire söz konusu olduğunda).

2) Trend çizgileri oluşturmak
____________________________

Bu en zor soru! Pek çok sayfada sadece DOĞRU bir trend çizgisi NASIL çizilir hakkında tartışmalar gördüm! Ama sadece inşa etmemiz değil, ticaretini de yapmamız gerekiyor ...

Bir trend çizgisi çizmek için en az iki yüksek (aşağı trend) veya iki düşük (yukarı trend) olmalıdır. Bu uç noktaları bir çizgi ile birleştirmeliyiz.

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

- Trend çizgisinin açısı önemlidir. Eğim açısı ne kadar dikse, o kadar az güvenilirdir.
- İki noktada bir çizgi oluşturmak en uygunudur. Üç veya daha fazla nokta üzerine inşa ederseniz, trend çizgisinin güvenilirliği azalır (kırılma olasılığı yüksektir).
- Hiçbir koşulda çizgi oluşturmaya çalışmayın. Çizmek mümkün değilse, büyük olasılıkla trend yoktur. Bu nedenle, bu araç mevcut piyasa koşullarında kullanıma uygun değildir.

Bu kurallar, trend çizgilerini doğru bir şekilde oluşturmanıza yardımcı olacaktır!

3) Trend çizgileriyle ticaret yapın
____________________________

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

Nasıl "düzgün pişirileceğini" biliyorsanız, her iki yöntem de iyidir.

Böylece iki nokta üzerine bir doğru oluşturduk. Fiyat çizgiye değdiği anda mevcut trend doğrultusunda piyasaya girmeliyiz. Girmek için "alış limiti veya satış limiti" tipi emirleri kullanırız.

Burada her şey basit ve net. Hatırlanması gereken tek şey, fiyat trend çizgisini ne kadar sık ​​test ederse, ondan geri dönerse, bir sonraki dokunuşun çizgiyi kırma olasılığı o kadar yüksek olur!

Trend çizgisini kırmaya çalışmak istiyorsak biraz farklı davranmalıyız:
1) Çizginin dokunuşunu bekliyoruz
2) Bir toparlanma bekliyoruz
3) Onay kutusuna bir satın alma durdurma emri (veya satış durdurma emri) verin
Çizime dikkat edin.

Onay işaretinin görünmesini bekledik ve maksimumda bir satın alma durdurma emri verdik.

Bir süre sonra sipariş çalıştı ve pazara girdik.
doğar meşru soru– pazara hemen girmek neden imkansızdı?
Mesele şu ki, trend çizgisi testinin başarılı olup olmayacağını bilmiyoruz. Ve "tik" i bekledikten sonra, başarı şansımızı önemli ölçüde artırıyoruz (yanlış sinyalleri ayıklıyoruz).

4) Yöntemin öznelliği
_________________________

Her şey basit görünüyor? Aslında, kullanarak Bu method, aşağıdaki zorluklarla karşılaşacağız:
A) Çizginin eğimi (her zaman farklı eğimlerle trend çizgileri çizebilirsiniz.
B) Trend çizgisinin kırılması olarak kabul edilen nedir (fiyatın bunu bir kırılma olarak kabul etmesi için çizgiyi kaç puan veya yüzde "kırması" gerekir)?
C) Bir hat ne zaman “modası geçmiş” kabul edilmeli ve yeni bir hat inşa edilmelidir?

Çizime dikkat edin.

Kırmızı çizgi, stillerden birini gösterir. Deneyimsiz bir tüccar böyle bir çizgi çizebilir (ve bunun için ödeme yapabilir).

Bu durumda, pratik deneyim önemlidir. Yani her şeyi birkaç kişiye indirgemek mümkün değil. Basit kurallar yapı. Bu nedenle trend çizgisi göstergesi mevcut değil. Daha doğrusu var olabilir ama onları "çarpık" ve yanlış bir şekilde inşa ediyor. Bu teknik başlangıçta tüccarın deneyimi ve becerisi için "bilenmişti".

Kişisel olarak, trend çizgilerini nadiren kullanırım. bağımsız araç. Ama yine de, onlardan basit bir nedenden dolayı bahsediyorum. Gerçek şu ki, diğer birçok tüccar bunları kullanıyor. Bu nedenle, biz (siz ve ben) rakiplerimizin tekniklerinden haberdar olmalıyız.

Ticaretinizde bu araca ihtiyacınız var mı - bu size kalmış!

İyi şanslar ve mutlu ticaret. Artur.
blog-forex.org

İlgili Mesajlar:

Trend ticaret konsepti (video)

Trend modelleri (rakamlar)

Bu konu için video:

Bölüm 10. Programa göre formül 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 yalnızca deneysel (ölçüm sonuçları, istatistiksel, referans, deneysel) veriler vardır. Bunları kullanarak, belirli bir yakınlık ölçüsüyle, bir çözüm bulmak, modellemek, çözümleri değerlendirmek ve tahminler yapmak için kullanılabilecek ampirik bir formülü (denklem) geri yüklemeye çalışırlar.

Ampirik bir formül seçme süreci P(x) deneyimli bağımlılık için f(x) isminde yaklaşım(yumuşatma). Tek bilinmeyenli bağımlılıklar için Excel grafikleri ve birçok bilinmeyenli bağımlılıklar için gruptaki işlev çiftlerini kullanır. istatistiksel DOT ve TREND, LGRFP ve BÜYÜME.

Bu bölümde, kullanarak deneysel verilerin yaklaşıklığını ele alıyoruz. Excel çizelgeleri: verilere göre bir grafik çizilir, seçilir trend çizgisi , yani deneysel bağımlılığa maksimum yakınlık derecesi ile yaklaşan yaklaşık fonksiyon.

Seçilen fonksiyonun yakınlık derecesi tahmin edilir belirleme katsayısı R 2 . Başka teorik değerlendirme yoksa, katsayılı bir fonksiyon seçin R2 1 eğilimi. Trend çizgisini kullanan formül seçiminin hem ampirik 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şıklık işlevi sağlar:

1. Doğrusal - y=cx+b. Bu en basit fonksiyon, verilerin sabit bir oranda büyümesini ve azalmasını yansıtır.

2. Polinom – y=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 polinom bir uç noktaya (minimum veya maks) sahip olabilir, 3. derece bir polinom en fazla 2 uç noktaya sahip olabilir, 4. derece bir polinom en fazla 3 uç noktaya sahip olabilir, vb.

3. Logaritmik - y=c ln x+b. Bu işlev, hızla artan (azalan) verileri tanımlar ve ardından sabitlenir.

4. Güç - y=cx b, (X>0 ve y>0). İşlev, verileri sürekli artan (azalan) bir büyüme oranıyla görüntüler.

5. Üstel – y=ce bx, (e doğal logaritmanın tabanıdır). İşlev, hızla büyüyen (azalan) verileri tanımlar ve bu veriler daha sonra dengelenir.

Teorik arka plan

Uygulamada, çeşitli süreçleri - özellikle ekonomik, fiziksel, teknik, sosyal - modellerken, bazı sabit noktalarda bilinen değerlerinden fonksiyonların yaklaşık değerlerini hesaplamak için şu veya bu yöntem yaygın olarak kullanılmaktadır.

Bu tür fonksiyonların yakınlaştırılmasıyla ilgili problemler sıklıkla ortaya çıkar:

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

Bu makalede, MS Excel paketinin bu tür sorunları çözmek için yetenekleri, ek olarak, tablo halinde belirtilen işlevler için (temeli oluşturan) regresyonlar oluşturmak (oluşturmak) için yöntemler ve teknikler tartışılmaktadır. regresyon analizi).

Excel'de regresyon oluşturmak için iki seçenek vardır.

1. Seçilen regresyonları (eğilim çizgilerini) incelenen süreç özelliği için bir veri tablosuna dayalı olarak oluşturulmuş bir grafiğe ekleme (yalnızca bir grafik oluşturulmuşsa kullanılabilir);
2. Yerleşik çalışan istatistiklerini kullanma Excel sayfası, doğrudan kaynak veri tablosu temelinde regresyonların (trend çizgileri) elde edilmesini sağlar.

Grafiğe Eğilim Çizgileri Ekleme

Belirli bir süreci açıklayan ve bir diyagramla temsil edilen bir veri tablosu için, Excel'in aşağıdakileri yapmanızı sağlayan etkili bir regresyon analizi 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;
• diyagrama oluşturulmuş regresyonun bir denklemini ekleyin;
• seçilen regresyonun grafikte görüntülenen verilerle uyum derecesini belirleyin.

y = y(x)

burada x, genellikle bir doğal sayılar dizisinin (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, sabit bir oranda artan veya azalan özelliklerin modellenmesinde iyidir. Bu, incelenen sürecin en basit modelidir. O

y=mx+b

burada m, x eksenine doğrusal regresyonun eğiminin tanjantıdır; b - doğrusal regresyonun y ekseni ile kesişme noktasının koordinatı.

2 . Bir polinom eğilim çizgisi, birkaç farklı uç noktaya (yüksekler ve alçaklar) sahip özellikleri tanımlamak için kullanışlıdır. Polinom derecesinin seçimi, incelenen özelliğin ekstremum sayısına göre 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 ekstremum; dördüncü derecenin polinomu - üçten fazla ekstremum 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 trend çizgisi, değerleri ilk başta hızla değişen ve daha sonra kademeli olarak sabitlenen karakteristiklerin modellenmesinde başarıyla kullanılmaktadır.

Denkleme uygun olarak inşa edilmiştir:

y = c ln(x) + b

4 . Güç trend çizgisi verir iyi sonuçlar, incelenen bağımlılığın değerleri, büyüme oranındaki sürekli bir değişiklik ile karakterize edilirse. Böyle bir bağımlılığa bir örnek, arabanın eşit şekilde hızlandırılmış hareketinin bir grafiği olarak hizmet edebilir. Verilerde sıfır veya negatif değerler varsa, güç trend çizgisi kullanamazsınız.

Denkleme uygun olarak inşa edilmiştir:

y = cxb

burada b, c katsayıları sabittir.

5 . Verilerdeki değişim oranı sürekli artıyorsa, ü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 uygun olarak inşa edilmiştir:

y=cebx

burada b, c katsayıları sabittir.

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

Formül ile belirlenir:

Bir veri serisine trend çizgisi eklemek için:

• veri serisi temelinde oluşturulan grafiği etkinleştirin, yani grafik alanı içinde 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.

Veri serilerinden birine karşılık gelen grafiğin üzerine gelir ve sağ tıklarsanız, aynı eylemler kolayca uygulanır; görüntülenen bağlam menüsünde Trend çizgisi ekle komutunu seçin. Trendline iletişim kutusu, Type (Tür) sekmesi açık olarak ekranda görünecektir (Şekil 1).

Bundan sonra ihtiyacınız var:

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

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

• Yaklaşan (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ı (ileriye veya geriye doğru) ayarlayın;
• grafik alanında, denklemi grafikte göster onay kutusunu etkinleştirmeniz gereken trend çizgisinin denklemini görüntüleyin;
• diyagram alanında yaklaşık güvenilirlik R2 değerini görüntüleyin, bunun için onay kutusunu etkinleştirmeniz gerekir diyagramda yaklaşık güvenilirlik (R^2) değerini koyun;
• Eğrinin bir noktada Y ekseni ile kesişmesi için onay kutusunu etkinleştirmeniz gereken Y ekseni ile eğilim çizgisinin kesişme noktasını ayarlayın;
• iletişim kutusunu kapatmak için Tamam düğmesine tıklayın.

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

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

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

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

3-B, Standart, Radar, Pasta ve Halka grafiklerdeki veri serilerine eğilim çizgileri ekleyemezsiniz.

Yerleşik Excel İşlevlerini Kullanma

Excel ayrıca grafik alanı dışında trend çizgileri çizmek için bir regresyon analizi aracı sağlar. Bu amaçla bir dizi istatistiksel çalışma sayfası işlevi kullanılabilir, ancak bunların tümü yalnızca doğrusal veya üstel regresyonlar oluşturmanıza izin verir.

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

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

Özellikle üstel bir trend çizgisi oluşturmak için çeşitli fonksiyonların yanı sıra:

• YÜKSEKLİK;
• LGRFYaklaşık.

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

• eğilim çizgilerini belirleyen tüm yerleşik istatistiksel işlevler için incelenen özelliğin veri serilerinin aynı türden oluşumunun oldukça basit bir süreci;
• oluşturulan veri serisine dayalı olarak trend çizgileri oluşturmak için standart bir teknik;
• gerekli sayıda ileri veya geri adım için incelenen sürecin davranışını tahmin etme yeteneği.

Yazarların, makalenin amacını regresyon analizinin gidişatını farklı derecelerde bütünlükle sunmak olarak belirlemediklerine dikkat edilmelidir. Başlıca görevi, somut örnekler Excel paketinin yaklaşım problemlerini çözmedeki yeteneklerini göstermek; nasıl, göster etkili araçlar regresyonlar ve tahminler oluşturmak için Excel'e sahiptir; regresyon analizi konusunda derin bilgiye sahip olmayan bir kullanıcı tarafından bile bu tür problemlerin ne kadar kolay çözülebileceğini göstermektedir.

Belirli problemleri çözme örnekleri

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

Görev 1

1995-2002 için bir motorlu taşıt işletmesinin kârına ilişkin bir veri tablosu ile. aşağıdakileri yapmanız gerekir.

1. Bir grafik oluşturun.
2. Grafiğe doğrusal ve polinom (ikinci dereceden ve kübik) trend çizgileri ekleyin.
3. Trend çizgisi denklemlerini kullanarak, 1995-2004 için her bir trend çizgisi için işletmenin kârına ilişkin tablo verilerini elde edin.
4. 2003 ve 2004 yılları için işletme için bir kar tahmini yapın.

sorunun çözümü

1. Excel çalışma sayfasının A4:C11 hücre aralığında, Şekil 1'de gösterilen çalışma sayfasına giriyoruz. 4.
2. B4:C11 hücre aralığını seçtikten sonra bir grafik oluşturuyoruz.
3. Oluşturulan grafiği etkinleştiriyoruz ve yukarıda açıklanan yöntemi kullanarak Trend Çizgisi iletişim kutusunda (bkz. Şekil 1) trend çizgisi türünü seçtikten sonra dönüşümlü olarak grafiğe doğrusal, ikinci dereceden ve kübik trend çizgileri ekliyoruz. Aynı iletişim kutusunda, Parametreler sekmesini açın (bkz. Şekil 2), Yaklaşan (düzleştirilmiş) eğrinin Adı alanına eklenecek eğilimin adını girin ve İleriye dönük tahmin: dönemler alanında, ayarlayın 2 yıl sonrasına ilişkin kar tahmini yapılması planlandığı için değeri 2'dir. Regresyon denklemini ve R2 yaklaşım güvenilirliğinin değerini diyagram alanında görüntülemek için Denklemi ekranda göster onay kutularını etkinleştirin ve yaklaşık güvenilirlik (R^2) değerini diyagrama yerleştirin. Daha iyi görsel algı için, Trend Line Format diyalog kutusunun View sekmesini kullandığımız, çizilen trend çizgilerinin tipini, rengini ve kalınlığını değiştiriyoruz (bkz. Şekil 3). Eklenen trend çizgileri ile ortaya çıkan grafik, şekil 2'de gösterilmektedir. 5.
4. 1995-2004 için her trend çizgisi için işletmenin karı hakkında tablo verileri elde etmek. Şekil 2'de sunulan trend çizgilerinin denklemlerini kullanalım. 5. Bunu yapmak için D3: F3 aralığındaki hücrelere şunu girin: metin bilgisi seçilen trend çizgisinin türü hakkında: 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şaretçisini kullanarak bu formülü kopyalayın c göreceli bağlantılar D5:D13 hücre aralığına. D4:D13 hücre aralığından bir doğrusal regresyon formülüne sahip her hücrenin, bağımsız değişken olarak A4:A13 aralığından karşılık gelen bir hücreye sahip olduğuna dikkat edilmelidir. Benzer şekilde, ikinci dereceden regresyon için E4:E13 hücre aralığı doldurulur ve kübik regresyon için F4:F13 hücre aralığı doldurulur. Böylece işletmenin 2003 ve 2004 yılı karı için bir tahmin yapılmıştır. üç trend ile. Ortaya çıkan değer tablosu, Şek. 6.

Görev 2

1. Bir grafik oluşturun.
2. Grafiğe logaritmik, üstel ve üstel trend çizgileri ekleyin.
3. Elde edilen trend çizgilerinin denklemlerini ve bunların her biri için yaklaşım güvenilirliği R2'nin değerlerini elde edin.
4. Trend çizgisi denklemlerini kullanarak, 1995-2002 için her trend çizgisi için işletmenin kârına ilişkin tablo verilerini elde edin.
5. Bu trend çizgilerini kullanarak 2003 ve 2004 yılları için işletme için bir kar tahmini yapın.

sorunun çözümü

Problem 1'in çözümünde verilen metodolojiyi takiben, eklenmiş logaritmik, üstel ve üstel trend çizgileri olan bir diyagram elde ederiz (Şekil 7). Ayrıca, elde edilen trend çizgisi denklemlerini kullanarak, 2003 ve 2004 yılları için öngörülen değerler de dahil olmak üzere işletmenin karı için değerler tablosunu dolduruyoruz. (Şek. 8).

Şek. 5 ve şek. logaritmik trendli modelin yaklaşıklık güvenilirliğinin en düşük değerine karşılık geldiği görülmektedir.

R2 = 0,8659

En yüksek R2 değerleri, polinom eğilimi olan modellere karşılık gelir: ikinci dereceden (R2 = 0.9263) ve kübik (R2 = 0.933).

Görev 3

Görev 1'de verilen 1995-2002 yılları için bir motorlu taşıt işletmesinin kârına ilişkin bir veri tablosu ile aşağıdaki adımları gerçekleştirmelisiniz.

1. TREND ve BÜYÜME fonksiyonlarını kullanarak lineer ve üstel trend çizgileri için veri serisi elde edin.
2. TREND ve BÜYÜME fonksiyonlarını kullanarak 2003 ve 2004 yılları için işletme için bir kar tahmini yapın.
3. İlk veriler ve alınan veri serileri için bir diyagram 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ı hakkında 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üntülenen İşlev Sihirbazı iletişim kutusunda, İstatistiksel kategorisinden TREND işlevini seçin ve ardından Tamam düğmesine tıklayın. Aynı işlem düğmesine basılarak da gerçekleştirilebilir (Ekle işlevi) standart panel aletler.
3. 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_değerler_x alanında - B4:B11 hücre aralığı;
4. girilen formülü bir dizi formülü yapmak için + + tuş kombinasyonunu kullanın.

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

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 beliren İşlev Argümanları 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. kombinasyonu kullanarak bu formülü bir dizi formülüne dönüştürün Ctrl tuşları+ Shift + Enter.
4. Girilen formül şöyle görünecektir: =(TREND(C4:C11;B4:B11;B12:B13)) ve D12:D13 hücre aralığı TREND fonksiyonunun tahmin edilen değerleriyle doldurulacaktır (bkz. 9).

Benzer şekilde, 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 bir veri serisi doldurulur.

Şekil 10, formül 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.

Görev 4

1'den 11'e kadar olan bir süre için bir motorlu taşıt işletmesinin sevk hizmeti tarafından hizmet başvurularının alınmasına ilişkin bir veri tablosu ile içinde bulunduğumuz ay aşağıdakileri yapmanız gerekir.

1. Doğrusal regresyon için veri serisini alın:EĞİM ve KESMEK fonksiyonlarını kullanarak; DOT işlevini kullanarak.
2. için bir dizi veri alın üstel regresyon LGRFPRIB işlevini kullanarak.
3. Yukarıdaki işlevleri kullanarak, mevcut ayın 12'sinden 14'üne kadar olan süre için sevk hizmetine yapılan 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ü

TREND ve BÜYÜME işlevlerinden farklı olarak, yukarıda listelenen işlevlerin (EĞİM, KESME, DOT, LGRFPRIB) hiçbirinin gerileme olmadığına dikkat edin. Bu işlevler, gerekli regresyon parametrelerini belirleyerek yalnızca yardımcı bir rol oynar.

SLOPE, INTERCEPT, LINEST, LGRFPRIB fonksiyonları kullanılarak oluşturulan lineer ve üstel regresyonlar için, dış görünüş TREND ve BÜYÜME işlevlerine karşılık gelen doğrusal ve üstel regresyonların aksine, denklemleri her zaman bilinir.

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

y=mx+b

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

1. kaynak tabloyu A4:B14 hücre aralığına girin;
2. m parametresinin değeri C19 hücresinde belirlenir. İstatistik kategorisinden Eğim işlevini seçin; 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 girilecek: =EĞİM(B4:B14;A4:A14);
3. benzer bir yöntem kullanılarak, D19 hücresindeki b parametresinin değeri belirlenir. Ve içeriği şöyle görünecektir: = KESME (B4:B14;A4:A14).Böylece, doğrusal bir regresyon oluşturmak için gerekli olan 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 şekilde giriyoruz: = $ C * A4 + $ D. Bu formülde C19 ve D19 hücreleri mutlak referanslarla yazılmıştır (hücre adresi olası kopyalama ile değişmemelidir). Mutlak referans işareti $, imleci hücre adresine getirdikten 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. İstenilen veri serisini alıyoruz (Şekil 12). İstek sayısının bir tam sayı olması nedeniyle, Hücre Biçimi penceresinin Sayı sekmesinde ondalık basamak sayısı ile sayı biçimini 0 olarak ayarlamalısınız.

y=mx+b

Bunun için:

1. C20:D20: =(DOT(B4:B14;A4:A14)) hücre aralığına DOT işlevini bir dizi formülü olarak girin. Sonuç olarak, C20 hücresindeki m parametresinin değerini ve D20 hücresindeki b parametresinin değerini alırız;
2. D4 hücresine şu formülü girin: =$C*A4+$D;
3. Doldurma işaretçisini kullanarak bu formülü D4:D17 hücre aralığına kopyalayın ve istenen veri serisini elde edin.

y=bmx

Şek. Şekil 13, formüllerin yanı sıra gerekli hücre aralıkları ile kullandığımız fonksiyonları görebileceğimiz bir tablo göstermektedir.

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

Belirli hacim/beden ürünler için zamanlama ölçümü yoksa ne yapılmalı? Yoksa ölçüm sayısı yetersiz mi ve yakın gelecekte ek gözlemler yapılamıyor mu? En iyi yol Bu sorunun çözümü, MS Excel'de eğilim çizgileri kullanılarak hesaplanan bağımlılıkların (gerileme denklemleri) oluşturulmasıdır.

Gerçek bir durumu ele alalım: Bir depoda, bir siparişin kutulu toplanması için işçilik maliyetlerinin miktarını belirlemek amacıyla, 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 toplanması için harcanan sürenin belirlenmesi gerekli hale geldi. Ek kronometrik çalışmaların yapılmasının imkansız olması nedeniyle, bu sipariş hacimlerini seçmek için harcanan süre, MS Excel'deki regresyon denklemleri kullanılarak hesaplanmıştır. Bunu yapmak için tablo 1, tablo 2'ye dönüştürüldü.

Dağılım grafiği seçimi, şek. 1

Sonraki adım: fare imleci grafikteki noktalardan birinin üzerine getirildi ve farenin sağ tuşu kullanılarak çağrıldı. bağlam menüsü, öğenin seçildiği yer: "eğilim çizgisi ekle" (İncir. 2).

Trend çizgisi ekleme, şek. 2

Görünen trend çizgisi formatı ayar penceresinde (Şek. 3) sıralı olarak seçildi: çizgi tipi doğrusal / güç ve aşağıdaki onay kutuları ayarlandı: "denklemi şemada göster" ve "şemaya yaklaşık güven değerini koy (R ^ 2)" (determinasyon katsayısı).

Trend çizgisi formatı, şek. 3

Sonuç olarak, Şek. 4 ve 5.

Lineer hesaplanan bağımlılık, şek. 4

Güç hesaplanan bağımlılık, şek. 5

Grafiklerin görsel bir 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şıklık güvenilirliğinin (R^2) değeri, her iki bağımlılık durumunda da aynı değer olan 0,97'dir. Belirleme katsayısı 1'e yaklaştıkça trend çizgisinin gerçeğe daha yakın olduğu bilinmektedir. Mal miktarındaki değişiklik nedeniyle sipariş işleme için harcanan süredeki değişimin %97 olduğu da belirtilebilir. bu nedenle, içinde bu durum gerekli değil: sonraki zaman maliyetlerinin hesaplanması için ana olanı seçmek için bağımlılığı hesaplayan.

Ana olanı alalım - doğrusal olarak hesaplanmış bir bağımlılık. Ardından, mal miktarına bağlı olarak harcanan zamanın değerleri aşağıdaki formüle göre belirlenir: y = 54,511x + 0,1489. Daha önce kronometrik gözlemleri yapılan mal miktarına ilişkin bu hesaplamaların sonuçları aşağıdaki tablo 3'te sunulmaktadır.

Regresyon denklemi ile hesaplanan zaman maliyetlerinin kronometrik gözlem verilerinden hesaplanan zaman maliyetlerinden ortalama sapmasını belirleyelim: (-0.05+0.10-0.05+0.01)/4=0.0019. Dolayısıyla, regresyon denklemine göre hesaplanan zaman maliyetleri, kronometrik gözlem verilerine göre hesaplanan zaman maliyetlerinden sadece 0,19%. Veri tutarsızlığı önemsizdir.

y = 54.511x + 0.1489 formülüne göre, daha önce kronometrik gözlemleri yapılmamış olan mal miktarı için zaman maliyetlerini belirleyeceğiz. (tablo 4).

Böylece, MS Excel'de eğilim çizgileri kullanılarak hesaplanan bağımlılıkların oluşturulması - Bu harika yolçeşitli nedenlerle kronometrik gözlemlerin kapsamadığı operasyonlarda harcanan sürenin belirlenmesi.