Doğrusal bir trend oluşturun. Excel'de bir eğilim işlevi oluşturma. Mevsimsellik olmadan hızlı tahmin

En sık trend belirir doğrusal bağımlılık formun araştırılan miktarının

burada y, çalışılan değişken (örneğin üretkenlik) veya bağımlı değişkendir;
x, tahmin döneminde yılın konumunu (ikinci, üçüncü vb.) belirleyen bir sayı veya bağımsız bir değişkendir.

İki parametre arasındaki ilişkinin doğrusal bir yaklaşımıyla, doğrusal bir fonksiyonun ampirik katsayılarını bulmak için çoğunlukla en küçük kareler yöntemi kullanılır. Yöntemin özü, doğrusal fonksiyon"en uygun", ölçülen parametrenin sapmalarının karelerinin minimum toplamına karşılık gelen grafiğin noktalarından geçer. Böyle bir durum şuna benzer:

burada n, çalışılan popülasyonun hacmidir (gözlem birimlerinin sayısı).

Pirinç. 5.3. En küçük kareler yöntemini kullanarak trend oluşturma

b ve a sabitlerinin değerleri veya X değişkeninin katsayısı ve denklemin serbest terimi aşağıdaki formülle belirlenir:

Masada. 5.1, verilere dayalı doğrusal bir trend hesaplamanın bir örneğini gösterir.

Tablo 5.1. Lineer trend hesaplama

Titreşimleri yumuşatma yöntemleri.

Komşu değerler arasında güçlü tutarsızlıklar olduğu için, regresyon yöntemiyle elde edilen eğilimin analiz edilmesi zordur. Tahmin yaparken, bir dizi, komşu değerlerde geniş bir dalgalanma dağılımına sahip veriler içerdiğinde, kişi bunları belirli kurallara göre düzeltmeli ve ardından tahminde anlam aramalıdır. Titreşimleri yumuşatma yöntemine git
şunları içerir: hareketli ortalama yöntemi (n noktalı bir ortalama hesaplanır), üstel düzeltme yöntemi. Onları düşünelim.

"Hareketli ortalamalar" (MSA) yöntemi.

MM, bir eğilimi vurgulamak için bir dizi değeri yumuşatmanıza olanak tanır. Bu yöntem, sabit sayıda değerin ortalamasını (genellikle aritmetik ortalama) alır. Örneğin, üç noktalı bir hareketli ortalama. Ocak, Şubat ve Mart (10 + 12 + 13) verilerinden oluşan değerlerin ilk üçlüsü alınır ve ortalaması 35: 3 = 11.67 olarak belirlenir.

Ortaya çıkan 11.67 değeri, aralığın merkezine yerleştirilir, yani. Şubat satırında. Sonra “bir ay kaydırıyoruz” ve Şubat'tan Nisan'a (12 + 13 + 16) kadar ikinci üç rakamı alıyoruz ve ortalamayı 41: 3 = 13.67 olarak hesaplıyoruz ve bu şekilde verileri şu şekilde işleriz: tüm seri Ortaya çıkan ortalamalar temsil eder yeni sıra bir trend oluşturmak ve buna yaklaşmak için veriler. Hareketli ortalamayı hesaplamak için ne kadar çok puan alınırsa, dalgalanmalar o kadar güçlü yumuşatılır. Bir trend oluşturmanın MVA'sından bir örnek Tablo'da verilmiştir. 5.2 ve Şek. 5.4.

Tablo 5.2 Üç noktalı hareketli ortalama yöntemi kullanılarak trendin hesaplanması

İlk verilerin dalgalanmalarının doğası ve hareketli ortalama yöntemiyle elde edilen veriler, Şekil 2'de gösterilmektedir. 5.4. İlk değerler serisinin (3. sıra) ve üç noktalı hareketli ortalamaların (4. sıra) grafiklerinin karşılaştırmasından, dalgalanmaların düzeltilebileceği görülebilir. Nasıl Daha noktalar, hareketli ortalamanın hesaplama aralığına dahil olacak, trend ne kadar net görünecek (satır 1). Ancak aralığı genişletme prosedürü, nihai değerlerin sayısında bir azalmaya yol açar ve bu, tahminin doğruluğunu azaltır.

Tahminler, orijinal verilerin değerlerinden veya hareketli ortalamalardan derlenen regresyon çizgisi tahminlerine dayalı olarak yapılmalıdır.

Pirinç. 5.4. Yılın aylarına göre satışlardaki değişimin niteliği:
ilk veriler (satır 3); hareketli ortalamalar (4. sıra); üstel düzeltme (2. sıra); gerileme eğilimi (1. sıra)

Üstel yumuşatma yöntemi.

Seri değerlerin yayılmasını azaltmak için alternatif bir yaklaşım, üstel düzeltme yöntemini kullanmaktır. Geçmişe giden dönemlerin her değerinin bir faktör (1 - α) kadar azaltılması nedeniyle yönteme "üstel yumuşatma" adı verilir.

Her düzleştirilmiş değer, aşağıdaki gibi bir formül kullanılarak hesaplanır:

St =aYt +(1−α)St−1,

burada St, geçerli düzleştirilmiş değerdir;
Yt, zaman serisinin mevcut değeridir; St - 1 - önceki düzleştirilmiş değer; α bir yumuşatma sabitidir, 0 ≤ α ≤ 1.

α sabitinin değeri ne kadar küçükse, belirli bir zaman serisindeki trend değişikliklerine o kadar az duyarlıdır.

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

m eğimin teğetidir doğrusal regresyon x eksenine; 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; ortaya çıktı bağlam menüsü 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 Line 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şım güvenilirliğinin (R^2) değerini diyagrama yerleştirin. Daha iyi bir görsel algı için, Trend Çizgisi Formatı iletişim kutusunun Görünüm 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çili trend çizgisinin türü hakkında: Doğrusal trend, Dörtlü trend, Kübik trend. 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 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 . Denklemi olan bir doğrusal regresyon oluşturalım:

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

Bir sonraki adım: fare imleci grafik noktalarından birine yerleştirildi ve sağ fare tuşuyla içerik menüsü çağrıldı, burada öğe seçildi: "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 zamanın belirlenmesi.

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

Doğrusal yaklaşım

Sonucu elde ederiz:

y = 4,503x + 6,1333

• 6.1333 - ofsetler;
• x dönem numarasıdır.

y = 7.6403е^-0.084x

Örneğin:

Herhangi bir analizin önemli bileşenlerinden biri, olayların ana eğilimini belirlemektir. Bu bilgilerle bir tahminde bulunabilirsiniz. Daha fazla gelişme durumlar. Bu, özellikle grafikteki trend çizgisi örneğinde belirgindir. Programın nasıl olduğunu öğrenelim. Microsoft Excel inşa edilebilir.

Excel'de Eğilim Çizgisi

Excel uygulaması, bir grafik kullanarak bir trend çizgisi çizme yeteneği sağlar. Aynı zamanda, oluşumu için ilk veriler önceden hazırlanmış bir tablodan alınır.

Çizdirme

Bir grafik oluşturmak için, oluşturulacağı temelde hazır bir tabloya sahip olmanız gerekir. Örnek olarak, belirli bir süre için doların ruble cinsinden değerine ilişkin verileri ele alalım.

1. Bir sütunun zaman periyotlarını (bizim durumumuzda tarihler) ve diğer sütunun dinamikleri grafikte gösterilecek bir değeri içerdiği bir tablo oluşturuyoruz.
2. Bu tabloyu seçin. "Ekle" sekmesine gidin. Orada, "Grafikler" araç bloğundaki şeritte "Grafik" düğmesine tıklayın. Sunulan listeden ilk seçeneği seçin.
3. Bundan sonra, program oluşturulacak, ancak yine de sonuçlandırılması gerekiyor. Grafiğin başlığını yapmak. Bunu yapmak için üzerine tıklayın. Görünen "Grafiklerle Çalışma" sekme grubunda "Düzen" sekmesine gidin. İçinde "Grafik Adı" düğmesine tıklayın. Açılan listede "Grafik üzerinde" öğesini seçin.
4. Grafiğin üzerinde görünen alana uygun gördüğümüz adı girin.
5. Sonra eksenleri imzalarız. Aynı "Düzen" sekmesinde, "Eksen Adları" şeridindeki düğmeyi tıklayın. “Ana yatay eksenin adı” ve “Eksenin altındaki isim” öğelerini sırayla inceliyoruz.
6. Görünen alana, üzerinde bulunan verilerin bağlamına göre yatay eksenin adını girin.
7. Dikey eksene bir isim vermek için "Düzen" sekmesini de kullanıyoruz. "Eksen Adları" düğmesine tıklayın. "Ana dikey eksenin adı" ve "Döndürülmüş başlık" açılır menüsünün öğeleri arasında sırayla hareket edin. Grafik türlerimiz için en uygun olan, eksen adının bu tür konumudur.
8. Dikey eksen adı için görünen alana istediğiniz adı girin.

Ders: Excel'de bir grafik nasıl yapılır

Trend çizgisi oluşturma

Şimdi doğrudan trend çizgisini eklemeniz gerekiyor.

1. "Düzen" sekmesinde olmak, "Analiz" araç bloğunda bulunan "Trend Çizgisi" düğmesine tıklayın. Açılan listeden "Üstel yaklaşım" veya "Doğrusal yaklaşım" öğesini seçin.
2. Bundan sonra, grafiğe bir trend çizgisi eklenir. Varsayılan olarak siyahtır.

Trend çizgisini ayarlama

Bir olasılık var ek ayarlarçizgiler.

1. Sırayla "Analiz", "Trend çizgisi" ve " menü öğelerinde "Düzen" sekmesine gidin. Ekstra seçenekler trend çizgileri...
2. Parametre penceresi açılır ve çeşitli ayarlar yapılabilir. Örneğin, altı seçenekten birini belirleyerek düzgünleştirme ve sığdırma türünü değiştirebilirsiniz:
   • polinom;
   • Doğrusal;
   • Güç;
   • logaritmik;
   • üstel;
   • Doğrusal filtreleme.

   Modelimizin güvenilirliğini belirlemek için “Yaklaşım güvenilirliğinin değerini şemaya yerleştirin” öğesinin yanındaki kutuyu işaretleyin. Sonucu görmek için "Kapat" düğmesine tıklayın.

   Bu gösterge 1'e eşitse, model maksimum düzeyde güvenilirdir. Düzey birden ne kadar uzaksa, güven o kadar az olur.

Güven düzeyinden memnun değilseniz, parametrelere geri dönebilir ve yumuşatma ve yaklaşım türünü değiştirebilirsiniz. Ardından, katsayıyı yeniden oluşturun.

tahmin

Trend çizgisinin ana görevi, üzerinde daha fazla gelişme tahmininde bulunmaktır.

1. Ayarlara geri dönelim. "Tahmin" ayar bloğunda, ilgili alanlarda, tahmin için trend çizgisini sürdürmek için kaç dönem ileri veya geri ihtiyacınız olduğunu belirtin. "Kapat" düğmesine tıklayın.
2. Tabloya geri dönelim. Çizginin uzadığını gösterir. Artık mevcut eğilimi korurken belirli bir tarih için hangi yaklaşık göstergenin tahmin edildiğini belirlemek için kullanılabilir.

Gördüğünüz gibi Excel'de trend çizgisi çizmek zor değil. Program, göstergeleri olabildiğince doğru şekilde gösterecek şekilde yapılandırılabilmesi için araçlar sağlar. Grafiğe dayanarak, belirli bir zaman dilimi için tahmin yapabilirsiniz.

Sorunu çözmenize yardımcı olabildiğimize sevindik.

Sorunun özünü ayrıntılı olarak açıklayarak yorumlarda sorunuzu sorun. Uzmanlarımız mümkün olan en kısa sürede yanıt vermeye çalışacaktır.

Bu makale size yardımcı oldu mu?

Grafiklere neden ihtiyaç duyulur? "Güzelleştirmek" için mi? Hiç de değil - diyagramın ana görevi, belirsiz sayıları anlaşılması kolay bir grafik biçimde sunmanıza izin vermektir. Böylece bir bakışta durum açıktı ve kuru istatistikleri inceleyerek zaman kaybetmeye gerek yoktu.

Grafiklerin bir diğer büyük avantajı da eğilimleri göstermeyi, yani gelecek için tahmin yapmayı çok daha kolay hale getirmesidir. Nitekim, tüm yıl boyunca işler kötüye gittiyse, önümüzdeki çeyrekte tablonun birdenbire tersine döneceğini düşünmek için hiçbir neden yok.

Çizelgeler ve grafikler bizi nasıl yanıltıyor?

Bununla birlikte, çizelgeler (özellikle büyük miktarda verinin görsel temsili söz konusu olduğunda), algılama için son derece uygun olmasına rağmen, her zaman açık olmaktan uzaktır.

Basit bir örnekle anlatmak istediğimi açıklayayım:

MS Excel'deki bir tabloya dayalı grafik

Bu tablo, belirli bir siteye günlük ortalama ziyaretçi sayısını ve ziyaretçi başına sayfa görüntüleme sayısını gösterir. Bir kullanıcı aynı anda birkaç sayfayı görüntüleyebileceğinden, her zaman ziyaretçilerden daha fazla sayfa görüntüleme olması mantıklıdır.

Gerçek şu ki daha az mantıklı değil daha fazla sayfa ziyaretçi göz atarsa, site o kadar iyi olur - kullanıcının dikkatini çeker ve daha derin okumasını sağlar.

Site sahibi şemamızdan ne görüyor? İşler onun için iyi gidiyor! Yaz aylarında ilgide mevsimsel bir düşüş yaşansa da sonbaharda rakamlar geri döndü ve hatta ilkbahardaki rakamların da üzerine çıktı. Sonuçlar? Aynı ruhla devam ediyoruz ve yakında başarıya ulaşacağız!

Diyagram açık mı? Epeyce. Ama belli mi? Hadi çözelim.

MS Excel'deki Eğilimleri Anlamak

Diyagramı olduğu gibi almak site sahibinin büyük bir hatasıdır. Evet, çıplak gözle "sonbahar" ın mavi ve turuncu çubuklarının "ilkbahar" a ve hatta "yaz" a kıyasla büyüdüğünü görebilirsiniz. Ancak sadece sütunların sayısı ve boyutu değil, aralarındaki ilişki de önemlidir. Yani, ideal olarak, genel büyümeyle birlikte, "turuncu" görüntüleme sütunları "mavi" olanlardan çok daha fazla büyümelidir, bu da sitenin yalnızca daha fazla okuyucu çekmekle kalmayıp aynı zamanda daha büyük ve daha ilginç hale gelmesi anlamına gelir.

Grafikte ne görüyoruz? "Sonbahar" ın turuncu sütunları en azından "ilkbahar" dan fazla değil, hatta daha az. Bu, başarıyı değil, tam tersini gösterir - ziyaretçiler gelir, ancak ortalama olarak daha az okur ve sitede oyalanmaz!

Alarmı çalmanın ve ... böyle bir şeyle tanışmanın zamanı geldi. trend çizgisi.

Neden bir trend çizgisine ihtiyacınız var?

"Basit bir şekilde" trend çizgisi, grafiğimizin oluşturulduğu özel algoritmalara dayanan ortalama değerler temelinde derlenen sürekli bir çizgidir. Başka bir deyişle, verilerimiz üç raporlama noktasından "-5"ten "0"a ve ardından "+5"e "sıçrarsa", sonunda neredeyse düz bir çizgi elde ederiz: durumun "artıları" açıkça "eksileri" dengeler.

Trend çizgisinin yönüne bağlı olarak, görmek çok daha kolaydır. gerçek durum vakalar ve aynı eğilimleri görmek ve dolayısıyla - gelecek için tahminler yapmak. Şimdi, işe koyulun!

MS Excel'de bir trend çizgisi nasıl çizilir

MS'te bir diyagrama ekleme Excel hattı akım

"Mavi" sütunlardan birine sağ tıklayın ve içerik menüsünden öğeyi seçin "Trend çizgisi ekle".

Grafik sayfası şimdi görüntüleniyor noktalı çizgi akım. Gördüğünüz gibi, diyagramın değerleriyle% 100 örtüşmüyor - ağırlıklı ortalamalar üzerine kurulu, sadece genel anlamda yönünü tekrarlıyor. Ancak bu, siteye yapılan ziyaret sayısında istikrarlı bir artış görmemizi engellemiyor - "yaz" düşüşü bile genel sonucu etkilemiyor.

"Ziyaretçiler" sütunu için eğilim çizgisi

Şimdi aynı numarayı "turuncu" çubuklarla tekrarlayalım ve ikinci trend çizgisini oluşturalım. Daha önce de söylediğim gibi: burada durum pek iyi değil. Eğilim, hesaplanan süre boyunca görüntüleme sayısının yalnızca artmadığını, hatta yavaş ama istikrarlı bir şekilde düşmeye başladığını açıkça gösteriyor.

Başka bir trend çizgisi, durumu netleştirmenizi sağlar

Önümüzdeki aylarda trend çizgisini zihinsel olarak sürdürerek, hayal kırıklığı yaratan bir sonuca varacağız - ilgilenen ziyaretçi sayısı azalmaya devam edecek. Kullanıcılar burada oyalanmadıkları için yakın gelecekte site ilgisinde bir düşüş kaçınılmaz olarak trafikte bir düşüşe neden olacaktır.

Bu nedenle, proje sahibinin bu yaz ne yaptığını ("ilkbaharda", programa göre her şey oldukça normaldi) acilen hatırlaması ve durumu düzeltmek için acilen önlemler alması gerekiyor.

Halihazırda mevcut olan verilere dayalı olarak bir olayı tahmin etmek için, eğer zaman yoksa, trend çizgisini kullanabilirsiniz. Bununla birlikte, grafiğin oluşturulduğu verilerin hangi dinamiklere sahip olduğunu görsel olarak anlayabilirsiniz. Microsoft yazılım paketinde, bu aracı kullanarak oldukça doğru bir tahmin - Excel'de bir trend çizgisi - oluşturmanıza yardımcı olacak harika bir Excel özelliği vardır. Bu analiz aracını oluşturmak oldukça basit, aşağıda Detaylı Açıklama süreç ve trend çizgisi türleri.

Excel'de Trend çizgisi. İnşaat süreci

Trend çizgisi, ana veri analiz araçlarından biridir.

Bir trend çizgisi oluşturmak için üç aşamayı tamamlamak gerekir, yani:
1. Bir tablo oluşturun;
2. Bir diyagram oluşturun;
3. Trend çizgisi tipini seçin.

hepsini topladıktan sonra gerekli bilgi, nihai sonucu elde etme yolundaki adımların uygulanmasına doğrudan ilerleyebilirsiniz.

İlk adım, orijinal verilerle bir tablo oluşturmaktır. Ardından, gerekli aralığı seçin ve "Ekle" sekmesine giderek "Grafik" işlevini seçin. İnşaattan sonra nihai sonuç uygulanabilir. Ek özellikler, başlıklar ve imzalar şeklinde. Bunu yapmak için, grafiğe sol tıklayın, "Yapıcı" adlı sekmeyi seçin ve "Düzen"i seçin. Bir sonraki adım, sadece başlığı girmektir.

Bir sonraki adım, trend çizgisinin kendisini oluşturmaktır. Bunun için grafiği tekrar seçip görev şeridinde "Düzen" sekmesini seçmeniz gerekiyor. Ardından, bu menüde "Trend Çizgisi" düğmesine tıklamanız ve "doğrusal yaklaşım" veya "üstel yaklaşım" seçeneğini seçmeniz gerekir.

Çeşitli varyasyonlar ltrend çizgileri

Kullanıcı tarafından girilen verilerin özelliklerine bağlı olarak, sunulan seçeneklerden birini seçmeye değer, trend çizgisi türlerinin açıklaması aşağıdadır.
Üstel yaklaşım. Giriş verilerinin değişim hızı sürekli olarak artarsa, o zaman verilen hatçok yardımcı olacaktır. Ancak tabloya girilen veriler sıfır veya negatif özellik içeriyorsa, bu tür kabul edilemez.

Doğrusal yaklaşım. Doğası gereği, bu çizgi düzdür ve genellikle temel durumlarda, fonksiyon yaklaşık sabitlikte arttığında veya azaldığında kullanılır.

Logaritmik yaklaşım. İlk başta değer doğru ve hızlı büyüyorsa veya tam tersi düşüyorsa, ancak değerden sonra dengeleniyorsa, bu trend çizgisi kullanışlı olacaktır.

Polinom yaklaşımı. Değişken artış ve azalma, bu çizginin karakteristik özellikleridir. Ayrıca, polinomların (polinomların) derecesi, maksimum ve minimum sayısına göre belirlenir.

Güç Yaklaşımı. Bir değerde monoton bir artış ve azalmayı karakterize eder, ancak verilerin negatif ve sıfır değerleri varsa uygulanması imkansızdır.

hareketli ortalama. Tüm salınım noktalarını yumuşatarak birinin diğerine doğrudan bağımlılığını görsel olarak göstermek için kullanılır. Bu, iki bitişik nokta arasındaki ortalama değeri vurgulayarak elde edilir. Böylece grafiğin ortalaması alınır ve puan sayısı kullanıcı tarafından "Puanlar" menüsünde seçilen değere düşürülür.

Nasıl kullanılır? Ekonomik seçenekleri tahmin etmek için, polinom derecesi birkaç ilke temelinde belirlenen polinom çizgisi kullanılır: belirleme katsayısının ve ayrıca göstergenin ekonomik dinamiklerinin hangi dönemde maksimize edilmesi tahmin gereklidir.

Tüm oluşum aşamalarını takip ederek ve özellikleri anladıktan sonra, gerçek tahminlere yalnızca uzaktan karşılık gelen tüm birincil trend çizgisini oluşturabilirsiniz. Ancak parametreleri ayarladıktan sonra, tahminin daha gerçekçi bir resminden bahsedebiliriz.

Excel'de Trend çizgisi. Parametre ayarıfonksiyonel çizgide

"Trend Çizgisi" düğmesine tıklayarak, "Gelişmiş Seçenekler" adlı gerekli menüyü seçin. Görünen pencerede, "Trend Line Format" üzerine tıklayın ve ardından "grafiğe R ^ 2 yaklaşım güvenilirliğinin değerini koyun" değerinin önüne bir onay işareti koyun. Bundan sonra, ilgili düğmeye tıklayarak menüyü kapatın. R^2= 0.6442 katsayısı grafiğin kendisinde görünür.

Bundan sonra, tanıtılan değişiklikleri iptal ediyoruz. Grafiği seçip "Düzen" sekmesine tıkladıktan sonra "Trend Çizgisi"ne tıklayın ve "Yok"a tıklayın. Ardından, "Trend Line Format" işlevine giderek, polinom çizgisine tıklayın ve dereceyi değiştirerek R ^ 2 = 0.8321 değerini elde etmeye çalışın.

Formülleri görüntülemek veya tahminlerin standart varyasyonlarından farklı başka formüller yapmak için, değerlerle ve özellikle polinomlarla deney yapmaktan korkmamak yeterlidir. Böylece sadece birini kullanarak excel programı, giriş verilerine dayalı olarak oldukça doğru bir tahmin oluşturabilirsiniz.

(10 510 kez ziyaret edildi, bugün 27 ziyaret)

Farklı grafiklerde Excel'deki eğilim çizgisi

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:

1. Tabloya göre bir grafik oluşturalım. Aralığı seçin - "Ekle" sekmesine gidin. Önerilen grafik türlerinden basit bir grafik seçeceğiz. Yatay - yıl, dikey - fiyat.
2. Grafiğin kendisine sağ tıklayın. "Trend Çizgisi Ekle"yi tıklayın.
3. Hat parametrelerini yapılandırmak için bir pencere açılır. Doğrusal türü seçelim ve yaklaşıklık güvenilirliğinin değerini grafiğe yerleştirelim.
4. Grafikte eğik bir çizgi belirir.

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 görünüm 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 başlangıç ​​verileri 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) öncekinden daha yüksek olan, yükselen yüksekler ve alçaklar 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) 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

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ı R2 . 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=c0+c1x+c2x2+…+c6x6. İş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=cxb, (X>0ve y>0). İşlev, verileri sürekli artan (azalan) bir büyüme oranıyla görüntüler.

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

5 fonksiyon tipinin tümü için, en küçük kareler yöntemiyle veri yaklaşımı kullanılır (F1 "trend çizgisi" için yardıma bakın).

Gibi örnek belirli bir firma için aşağıdaki istatistikler tarafından verilen satışların reklama bağımlılığını düşünün:

Bir fonksiyon oluşturmamız gerekiyor. en iyi yol bu ilişkiyi yansıtır. Ayrıca reklam yatırımları için satışların 6 bin ruble olarak tahmin edilmesi gerekiyor.

gelelim çözüme. Her şeyden önce, bu verileri Excel'e girin ve Şekil 1'deki gibi bir grafik oluşturun. 38. Gördüğünüz gibi grafik B2:J2 aralığını temel alıyor. Ardından, grafiğe sağ tıklayarak, Şekil 1'de gösterildiği gibi bir trend çizgisi ekleyin. 38.

X eksenini karşılık gelen reklam değerleriyle etiketlemek için (Şekil 38'deki gibi), açılır menüden öğeyi seçin (Şekil 38). VE benzer veriler. Aynı isimli açılan pencerede, sekmede Sıra, alanda P x ekseni etiketleri, X değerlerinin yazıldığı hücre aralığını belirtin (burada $B$1:$K$1):

Açılan ayarlar penceresinde (Şek. 39), sekmesinde Tip yaklaşım için seçin logaritmik trend çizgisi (grafik türüne göre). Yer imi Seçenekler grafikte denklemi ve belirleme katsayısını gösteren kutuları işaretleyin.

bastıktan sonra TAMAMŞekil l'deki gibi bir sonuç alacaksınız. 40. Belirleme katsayısı R2= 0.9846, bu iyi bir yakınlık derecesidir. Seçilen işlevin doğruluğunu onaylamak için (çünkü başka teorik değerlendirmeler yoktur), önümüzdeki 10 dönem için satışların gelişimini tahmin edin. Bunu yapmak için, trend çizgisine sağ tıklayın - formatı değiştirin - ardından alanda Tahmin: ilet: 10'u ayarlayın (Şek.

Tahmini ayarladıktan sonra, Şekil 1'deki gibi 10 gözlem periyodu için grafiğin eğrisindeki değişimi göreceksiniz. 42. Büyük olasılıkla reklam yatırımlarındaki artışla birlikte satışlardaki artışı yansıtıyor.

Elde edilen formüle göre hesaplama \u003d 237.96 * LN (6) + 5.9606 Excel'de 432 bin ruble değeri veriyor.

Excel, doğrusal regresyon kullanarak mevcut X ve Y değerleri çiftlerinden gelecekteki bir Y değerini hesaplayan bir TAHMİN() işlevine sahiptir. Y işlevi mümkün olduğu kadar doğrusal olmalıdır, yani türünden bir denklemle açıklanabilir c+bx.

Örneğimiz için tahmin işlevi şöyle olacaktır: =TAHMİN(K1;B2:J2;B1:J1). Bir yere yazın - 643,6 bin ruble değerinde almalısınız.

Bölüm 11. Kontrol görevleri

Önceki12345678910111213141516Sonraki

Halihazırda mevcut olan verilere dayalı olarak bir olayı tahmin etmek için, eğer zaman yoksa, trend çizgisini kullanabilirsiniz. Bununla birlikte, grafiğin oluşturulduğu verilerin hangi dinamiklere sahip olduğunu görsel olarak anlayabilirsiniz. Microsoft yazılım paketi, bu aracı kullanarak oldukça doğru bir tahmin - Excel'de bir trend çizgisi - oluşturmanıza yardımcı olacak harika bir Excel özelliğine sahiptir. Bu analiz aracını oluşturmak oldukça basittir, aşağıda süreç ve trend çizgisi türlerinin ayrıntılı bir açıklaması bulunmaktadır.

Excel'de Trend çizgisi. İnşaat süreci

Trend çizgisi, ana veri analiz araçlarından biridir.

Bir trend çizgisi oluşturmak için üç adımı tamamlamanız gerekir:
1. Bir tablo oluşturun;
2.
3. Trend çizgisi tipini seçin.

Gerekli tüm bilgileri topladıktan sonra, nihai sonucu elde etmeye yönelik adımların uygulanmasına doğrudan ilerleyebilirsiniz.

İlk adım, orijinal verilerle bir tablo oluşturmaktır. Ardından, gerekli aralığı seçin ve "Ekle" sekmesine giderek "Grafik" işlevini seçin. İnşaattan sonra, nihai sonuca başlıklar ve imzalar şeklinde ek özellikler uygulanabilir. Bunu yapmak için, grafiğe sol tıklayın, "Yapıcı" adlı sekmeyi seçin ve "Düzen"i seçin. Bir sonraki adım, sadece başlığı girmektir.

Bir sonraki adım, trend çizgisinin kendisini oluşturmaktır. Bunun için grafiği tekrar seçip görev şeridinde "Düzen" sekmesini seçmeniz gerekiyor. Ardından, bu menüde " Trend çizgisi" düğmesine tıklamanız ve "doğrusal yaklaşım" veya "üstel yaklaşım" seçeneğini seçmeniz gerekir.

Çeşitli varyasyonlar ltrend çizgileri

Kullanıcı tarafından girilen verilerin özelliklerine bağlı olarak, sunulan seçeneklerden birini seçmeye değer, trend çizgisi türlerinin açıklaması aşağıdadır.
Üstel Yaklaşım. Giriş verilerinin değişim hızı sürekli olarak artarsa, bu satır en yararlı olacaktır. Ancak tabloya girilen veriler sıfır veya negatif özellik içeriyorsa bu tip kabul edilemez.

Doğrusal yaklaşım. Doğası gereği, bu çizgi düzdür ve genellikle temel durumlarda, fonksiyon yaklaşık sabitlikte arttığında veya azaldığında kullanılır.

Logaritmik yaklaşım. Değer başlangıçta doğru ve hızlı bir şekilde büyürse veya tam tersi azalırsa, ancak değerlerden sonra dengelenirse, o zaman bu trend çizgisi kullanışlı olacaktır.

polinom yaklaşımı. Değişken artış ve azalma, bu çizginin karakteristik özellikleridir. Ayrıca, polinomların (polinomların) derecesi, maksimum ve minimum sayısına göre belirlenir.

Güç yaklaşımı. Bir değerde monoton bir artış ve azalmayı karakterize eder, ancak verilerin negatif ve sıfır değerleri varsa uygulanması imkansızdır.

hareketli ortalama. Tüm salınım noktalarını yumuşatarak birinin diğerine doğrudan bağımlılığını görsel olarak göstermek için kullanılır. Bu, iki bitişik nokta arasındaki ortalama değeri vurgulayarak elde edilir. Böylece grafiğin ortalaması alınır ve puan sayısı kullanıcı tarafından "Puanlar" 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 kullanılır polinom polinom derecesi birkaç ilke temelinde belirlenen bir çizgi: belirleme katsayısının maksimize edilmesi ve ayrıca tahminin gerekli olduğu dönemde göstergenin ekonomik dinamikleri.

Tüm oluşum aşamalarını takip ederek ve özellikleri anladıktan sonra, gerçek tahminlere yalnızca uzaktan karşılık gelen tüm birincil trend çizgisini oluşturabilirsiniz. Ancak parametreleri ayarladıktan sonra, tahminin daha gerçekçi bir resminden bahsedebiliriz.

Excel'de Trend çizgisi. Parametre ayarıfonksiyonel çizgide

Düğmeye tıklayarak " Trend çizgisi", "Ek Seçenekler" adlı gerekli menüyü seçin. Görünen pencerede " Trend çizgisini biçimlendir"ve ardından "Yaklaşım güvenilirliği R ^ 2'nin değerini grafiğe koy" değerinin önüne bir onay işareti koyun. Bundan sonra, ilgili düğmeye tıklayarak menüyü kapatın. R^2= 0.6442 katsayısı grafiğin kendisinde görünür.

Bundan sonra, tanıtılan değişiklikleri iptal ediyoruz. Grafiği seçip "Düzen" sekmesine tıkladıktan sonra " Trend çizgisi"Ve" Hayır "ı tıklayın. Ardından, " Trendline Format" işlevine giderek, üzerine tıklayın polinom satırını açın ve dereceyi değiştirerek R^2= 0.8321 değerini elde etmeye çalışın.

Formülleri görüntülemek veya tahminlerin standart varyasyonlarından farklı başka formüller yapmak için, değerlerle ve özellikle polinomlarla deney yapmaktan korkmamak yeterlidir. Böylece, yalnızca bir Excel programı kullanarak, girdi verilerine dayalı olarak oldukça doğru bir tahmin oluşturabilirsiniz.