Excel derslerimin çoğu gibi, bu da bir arkadaşımdan aldığım sorgulardan birinden ilham aldı. Excel'de hareketli ortalamayı hesaplamak istedi ve ondan bunu çevrimiçi aramasını (veya bununla ilgili bir YouTube videosu izlemesini) istedim.
Ama sonra, kendim yazmaya karar verdim (üniversitede biraz istatistik ineği olduğum gerçeği de küçük bir rol oynadı).
Şimdi, size Excel'de hareketli ortalamanın nasıl hesaplanacağını söylemeden önce, size hızlı bir şekilde hareketli ortalamanın ne anlama geldiğine ve ne tür hareketli ortalamaların bulunduğuna dair bir genel bakış vereyim.
Excel'de hareketli ortalamanın nasıl hesaplandığını gösterdiğim kısma geçmek isterseniz buraya tıklayın.
Not: İstatistik konusunda uzman değilim ve bu eğitimdeki amacım hareketli ortalamalarla ilgili her şeyi kapsamak değil. Size yalnızca Excel'de hareketli ortalamaların nasıl hesaplanacağını göstermeyi amaçlıyorum (hareketli ortalamaların ne anlama geldiğine dair kısa bir girişle).Hareketli Ortalama nedir?
Ortalama bir değerin ne olduğunu bildiğinizden eminim.
Üç günlük günlük sıcaklık verilerim varsa, bana son üç günün ortalamasını kolayca söyleyebilirsin (ipucu: Bunu yapmak için Excel'deki ORTALAMA işlevini kullanabilirsiniz).
Hareketli Ortalama (yuvarlanan ortalama veya hareketli ortalama olarak da adlandırılır), ortalamanın zaman aralığını aynı tuttuğunuz, ancak yeni veriler eklendikçe hareket etmeye devam ettiğiniz zamandır.
Örneğin 3. Günde size 3 günlük hareketli ortalama sıcaklığı sorarsam, bana 1., 2. ve 3. Günlerin ortalama sıcaklık değerini vereceksiniz ve 4. Günde size 3 günlük hareketli ortalama sıcaklığı soracağım. , bana 2., 3. ve 4. Günlerin ortalamasını vereceksiniz.
Yeni veriler eklendikçe, süreyi (3 gün) aynı tutarsınız ancak hareketli ortalamayı hesaplamak için en son verileri kullanırsınız.
Hareketli ortalama teknik analiz için yoğun olarak kullanılır ve birçok banka ve borsa analisti bunu günlük olarak kullanır (aşağıda Market Realist sitesinden aldığım bir örnek).
Hareketli Ortalama Türleri
Üç tür hareketli ortalama vardır:
- Basit hareketli ortalama (SMA)
- Ağırlıklı hareketli ortalama (WMA)
- Üstel hareketli ortalama (EMA)
Basit Hareketli Ortalama (SMA)
Bu, verilen süredeki veri noktalarının basit ortalamasıdır.
Günlük sıcaklık örneğimizde, yalnızca son 10 günün ortalamasını aldığınızda, 10 günlük basit hareketli ortalamayı verir.
Bu, verilen süre içinde veri noktalarının ortalaması alınarak elde edilebilir. Excel'de bunu ORTALAMA işlevini kullanarak kolayca yapabilirsiniz (bu, bu öğreticide daha sonra ele alınacaktır).
Ağırlıklı Hareketli Ortalama (WMA)
Diyelim ki hava her geçen gün daha da soğuyor ve sıcaklık trendini elde etmek için 10 günlük hareketli ortalamayı kullanıyorsunuz.
10. Gün sıcaklığı, 1. Gün'e kıyasla eğilimin daha iyi bir göstergesi olma olasılığı daha yüksektir (çünkü sıcaklık her geçen gün düşüyor).
Bu nedenle, 10. Gün değerine daha fazla güvenirsek daha iyi durumda oluruz.
Bunu hareketli ortalamamıza yansıtmak için en son verilere daha fazla, geçmiş verilere daha az ağırlık verebilirsiniz. Bu şekilde, yine de trendi elde edersiniz, ancak en son verilerden daha fazla etkilenirsiniz.
Buna ağırlıklı hareketli ortalama denir.
Üstel Hareketli Ortalama (EMA)
Üstel hareketli ortalama, en son verilere daha fazla ağırlık verildiği ve eski veri noktaları için katlanarak azaldığı bir ağırlıklı hareketli ortalama türüdür.
Aynı zamanda Üstel Ağırlıklı Hareketli Ortalama (EWMA) olarak da adlandırılır.
WMA ve EMA arasındaki fark, WMA ile herhangi bir kritere göre ağırlık atayabilmenizdir. Örneğin, 3 noktalı bir hareketli ortalamada, en son veri noktasına %60, ortadaki veri noktasına %30 ve en eski veri noktasına %10 ağırlık yaşı atayabilirsiniz.
EMA'da, en son değere daha yüksek bir ağırlık verilir ve ağırlık, daha önceki değerler için katlanarak azalmaya devam eder.
Bu kadar istatistik dersi yeter.
Şimdi, Excel'de hareketli ortalamaların nasıl hesaplanacağını görelim.
Excel'de Veri Analizi Araç Paketini Kullanarak Basit Hareketli Ortalamayı (SMA) Hesaplama
Microsoft Excel, basit hareketli ortalamaları hesaplamak için yerleşik bir araca sahiptir.
buna denir Veri Analizi Araç Paketi.
Veri Analizi araç paketini kullanmadan önce, Excel şeridinde olup olmadığını kontrol etmeniz gerekir. İlk önce etkinleştirmek için birkaç adım atmanız için iyi bir şans var.
Veri sekmesinde zaten Veri Analizi seçeneğiniz varsa, aşağıdaki adımları atlayın ve hareketli ortalamaları hesaplama adımlarına bakın.Veri sekmesine tıklayın ve Veri Analizi seçeneğini görüp görmediğinizi kontrol edin. Göremiyorsanız, şeritte kullanılabilir hale getirmek için aşağıdaki adımları izleyin.
- Dosya sekmesine tıklayın
- Seçenekler'e tıklayın
- Excel Seçenekleri iletişim kutusunda, Eklentiler'e tıklayın.
- İletişim kutusunun alt kısmında, açılır menüden Excel Eklentileri'ni seçin ve ardından Git'e tıklayın.
- Açılan Eklentiler iletişim kutusunda, Analiz Araç Paketi seçeneğini işaretleyin
- Tamam'ı tıklayın.
Yukarıdaki adımlar Veri Analizi Araç Paketini etkinleştirecektir ve bu seçeneği şimdi Veri sekmesinde göreceksiniz.
Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve son üç aralığın hareketli ortalamasını hesaplamak istediğinizi varsayalım.
Aşağıda, basit bir hareketli ortalamayı hesaplamak için Veri Analizini kullanma adımları verilmiştir:
- Veri sekmesine tıklayın
- Veri Analizi seçeneğine tıklayın
- Veri Analizi iletişim kutusunda Hareketli Ortalama seçeneğine tıklayın (ulaşmak için biraz kaydırmanız gerekebilir).
- Tamam'ı tıklayın. Bu, 'Hareketli Ortalama' iletişim kutusunu açacaktır.
- Giriş Aralığında, hareketli ortalamasını hesaplamak istediğiniz verileri seçin (bu örnekte B2:B11)
- Aralık seçeneğinde 3 girin (üç noktalı hareketli ortalamayı hesapladığımız için)
- Çıktı aralığında, sonuçları istediğiniz hücreyi girin. Bu örnekte, çıktı aralığı olarak C2 kullanıyorum
- Tamam'ı tıklayın
Yukarıdaki adımlar, aşağıda gösterildiği gibi size hareketli ortalama sonucunu verecektir.
C sütunundaki ilk iki hücrenin sonucu #YOK hatası olarak aldığını unutmayın. Bunun nedeni, üç noktalı hareketli ortalama olması ve ilk sonucu vermesi için en az üç veri noktasına ihtiyaç duymasıdır. Böylece gerçek hareketli ortalama değerleri üçüncü veri noktasından sonra başlar.
Ayrıca, tüm bu Veri Analizi araç paketinin hücrelere bir ORTALAMA formülü uygulandığını fark edeceksiniz. Yani bunu Veri Analizi araç paketi olmadan manuel olarak yapmak istiyorsanız, kesinlikle yapabilirsiniz.
Bununla birlikte, veri analizi araç paketi ile yapılması daha kolay olan birkaç şey vardır. Örneğin, hareketli ortalamanın grafiğinin yanı sıra standart hata değerini almak istiyorsanız, tek yapmanız gereken bir kutucuğu işaretlemek ve çıktının bir parçası olacaktır.
Excel'de Formülleri Kullanarak Hareketli Ortalamaları (SMA, WMA, EMA) Hesaplama
ORTALAMA formülünü kullanarak hareketli ortalamaları da hesaplayabilirsiniz.
Aslında, ihtiyacınız olan tek şey hareketli ortalama değerse (standart hata veya grafik değil), formül kullanmak Veri Analizi Araç Paketini kullanmaktan daha iyi (ve daha hızlı) bir seçenek olabilir.
Ayrıca, Veri analizi Araç Paketi yalnızca Basit Hareketli Ortalama (SMA) verir, ancak WMA veya EMA'yı hesaplamak istiyorsanız, yalnızca formüllere güvenmeniz gerekir.
Formülleri Kullanarak Basit Hareketli Ortalamayı Hesaplama
Aşağıda gösterildiği gibi veri kümeniz olduğunu ve 3 noktalı SMA'yı hesaplamak istediğinizi varsayalım:
C4 hücresine aşağıdaki formülü girin:
=ORTALAMA(B2:B4)
Bu formülü tüm hücreler için kopyalayın ve size her gün için SMA verecektir.
Unutmayın: Formülleri kullanarak SMA'yı hesaplarken, formüldeki referansların göreceli olduğundan emin olmanız gerekir. Bu, formülün =ORTALAMA(B2:B4) veya =ORTALAMA($B2:$B4) olabileceği, ancak =ORTALAMA($B$2:$B$4) veya =ORTALAMA(B$2:B$4 olamayacağı anlamına gelir) ). Referansın satır numarası kısmı dolar işareti olmadan olmalıdır. Mutlak ve göreli referanslar hakkında daha fazla bilgiyi buradan okuyabilirsiniz.
3 noktalı Basit Hareketli Ortalama (SMA) hesapladığımız için ilk iki hücre (ilk iki gün için) boştur ve üçüncü günden itibaren formülü kullanmaya başlarız. İsterseniz ilk iki değeri olduğu gibi, üçüncü değerden itibaren SMA değerini kullanabilirsiniz.
Formülleri Kullanarak Ağırlıklı Hareketli Ortalamayı Hesaplama
WMA için değerlere atanacak ağırlıkları bilmeniz gerekir.
Örneğin, aşağıdaki veri kümesi için 3 noktalı WMA'yı hesaplamanız gerektiğini varsayalım, burada en son değere %60, kendinden öncekine %30 ve kendinden öncekine %10 ağırlık verilir.
Bunu yapmak için aşağıdaki formülü C4 hücresine girin ve tüm hücrelere kopyalayın.
=0.6*B4+0.3*B3+0.1*B2
3 puanlık Ağırlıklı Hareketli Ortalama (WMA) hesapladığımız için ilk iki hücre (ilk iki gün için) boştur ve üçüncü günden itibaren formülü kullanmaya başlarız. İsterseniz ilk iki değeri olduğu gibi, WMA değerini üçüncü değerden itibaren kullanabilirsiniz.
Formülleri Kullanarak Üstel Hareketli Ortalamayı Hesaplama
Üstel Hareketli Ortalama (EMA), en son değere daha yüksek ağırlık verir ve ağırlıklar, önceki değerler için katlanarak azalmaya devam eder.
Üç noktalı hareketli ortalama için EMA'yı hesaplama formülü aşağıdadır:
EMA = [Son Değer - Önceki EMA Değeri] * (2 / N+1) + Önceki EMA
… bu örnekte N 3 olur (üç noktalı bir EMA hesapladığımız için)
Not: İlk EMA değeri için (EMA'yı hesaplamak için önceki bir değeriniz olmadığında), değeri olduğu gibi alın ve EMA değeri olarak kabul edin. Daha sonra bu değeri ileriye dönük olarak kullanabilirsiniz.Aşağıdaki veri setine sahip olduğunuzu ve üç dönemlik EMA'yı hesaplamak istediğinizi varsayalım:
C2 hücresine, B2'deki ile aynı değeri girin. Bunun nedeni, EMA'yı hesaplamak için önceden bir değer olmamasıdır.
C3 hücresine aşağıdaki formülü girin ve tüm hücreler için kopyalayın:
=(B3-C2)*(2/4)+C2
Bu örnekte, basit tuttum ve mevcut EMA'yı hesaplamak için en son değeri ve önceki EMA değerini kullandım.
Bunu yapmanın bir başka popüler yolu, önce Basit Hareketli Ortalamayı hesaplamak ve ardından gerçek en son değer yerine onu kullanmaktır.
Bir Sütun Grafiğine Hareketli Ortalama Trend Çizgisi Ekleme
Bir veri kümeniz varsa ve bunu kullanarak bir çubuk grafik oluşturuyorsanız, birkaç tıklamayla hareketli ortalama eğilim çizgisini de ekleyebilirsiniz.
Aşağıda gösterildiği gibi bir veri kümeniz olduğunu varsayalım:
Aşağıda, bu verileri kullanarak ve bu grafiğe üç parçalı hareketli ortalama trend çizgisi ekleyerek bir çubuk grafik oluşturma adımları verilmiştir:
- Veri kümesini seçin (başlıklar dahil)
- Ekle sekmesini tıklayın
- Grafik grubunda, 'Sütun veya Çubuk Grafik Ekle' simgesine tıklayın.
- Kümelenmiş Sütun grafiği seçeneğine tıklayın. Bu, grafiği çalışma sayfasına ekleyecektir.
- Grafik seçiliyken Tasarım sekmesine tıklayın (bu sekme yalnızca grafik seçildiğinde görünür)
- Grafik Düzenleri grubunda, 'Grafik Öğesi Ekle'yi tıklayın.
- İmleci 'Trend Çizgisi' seçeneğinin üzerine getirin ve ardından 'Diğer Trend Çizgisi Seçenekleri'ne tıklayın
- Trend Çizgisini Biçimlendir bölmesinde, 'Hareketli Ortalama' seçeneğini seçin ve dönem sayısını ayarlayın.
Bu kadar! Yukarıdaki adımlar, sütun grafiğinize hareketli bir trend çizgisi ekler.
Birden fazla hareketli ortalama eğilim çizgisi eklemek isterseniz (örneğin 2 dönem için bir ve 3 dönem için bir tane), 5'ten 8'e kadar olan adımları tekrarlayın.
Aynı adımları çizgi grafiğe hareketli ortalama trend çizgisi eklemek için de kullanabilirsiniz.
Hareketli Ortalama Trend Çizgisini Biçimlendirme
Normal bir çizgi grafiğinden farklı olarak, hareketli bir ortalama eğilim çizgisi çok fazla biçimlendirmeye izin vermez. Örneğin, trend çizgisinde belirli bir veri noktasını vurgulamak istiyorsanız bunu yapamazsınız.
Eğilim çizgisinde biçimlendirebileceğiniz birkaç şey şunları içerir:
- Renk çizginin. Bunu, grafikteki her şeyi açık renkli hale getirerek ve trend çizgisini parlak bir renkle öne çıkararak trend çizgilerinden birini vurgulamak için kullanabilirsiniz.
- NS kalınlık çizginin
- NS şeffaflık çizginin
Hareketli ortalama eğilim çizgisini biçimlendirmek için, üzerine sağ tıklayın ve ardından Eğilim Çizgisini Biçimlendir seçeneğini seçin.
Bu, sağdaki Biçim Eğilim Çizgisi bölmesini açacaktır. Bu bölmedeki tüm biçimlendirme seçenekleri (farklı bölümlerde - Dolgu ve Çizgi, Efektler ve Eğilim Çizgisi Seçenekleri).
