Özet
Hareketli veya hareketli ortalamayı hesaplamak için, göreceli referanslarla ORTALAMA işlevine dayalı basit bir formül kullanabilirsiniz. Gösterilen örnekte, E7'deki formül şöyledir:
=AVERAGE(C5:C7)
Formül kopyalandıkça, geçerli gün ve önceki iki günün satış değerine göre 3 günlük hareketli ortalama hesaplar.
Aşağıda, değişken dönemleri işleyen OFFSET işlevine dayalı daha esnek bir seçenek bulunmaktadır.
Ortalamalar hakkında
Hareketli ortalama (hareketli ortalama olarak da adlandırılır), belirli aralıklarda veri alt kümelerine dayalı bir ortalamadır. Belirli aralıklarla bir ortalama hesaplamak, rastgele dalgalanmaların etkisini azaltarak verileri düzleştirir. Bu, özellikle bir grafikte genel eğilimleri görmeyi kolaylaştırır. Bir hareketli ortalamayı hesaplamak için kullanılan aralık ne kadar büyükse, hesaplanan her ortalamaya daha fazla veri noktası dahil edildiğinden, o kadar fazla düzgünleştirme gerçekleşir.
Açıklama
Örnekte gösterilen formüllerin tümü, ORTALAMA işlevini, her belirli aralık için göreceli bir referans ayarıyla kullanır. E7'deki 3 günlük hareketli ortalama, ORTALAMA geçerli günü ve önceki iki günü içeren bir aralık beslenerek hesaplanır:
=AVERAGE(C5:C7) // 3-day average
5 günlük ve 7 günlük ortalamalar aynı şekilde hesaplanır. Her durumda, ORTALAMA için sağlanan aralık, gerekli gün sayısını içerecek şekilde genişletilir:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Tüm formüller, ORTALAMA işlevine sağlanan aralık için göreceli bir referans kullanır. Formüller sütunun altına kopyalandıkça, her ortalama için gereken değerleri içerecek şekilde aralık her satırda değişir.
Değerler bir çizgi grafikte çizildiğinde, yumuşatma etkisi açıktır:
Yetersiz veri
Formülleri tablonun ilk satırından başlatırsanız, ilk birkaç formül tam bir ortalamayı hesaplamak için yeterli veriye sahip olmayacaktır, çünkü aralık ilk veri satırının üzerine uzanacaktır:
Çalışma sayfasının yapısına ve tüm ortalamaların aynı sayıda değeri temel almasının önemli olup olmadığına bağlı olarak bu bir sorun olabilir veya olmayabilir. ORTALAMA işlevi, metin değerlerini ve boş hücreleri otomatik olarak yok sayar, böylece daha az değerle ortalama hesaplamaya devam eder. Bu nedenle E5 ve E6'da "çalışır".
Yetersiz veriyi açıkça belirtmenin bir yolu, mevcut satır numarasını kontrol etmek ve n'den az değer olduğunda #NA ile iptal etmektir. Örneğin, 3 günlük ortalama için şunları kullanabilirsiniz:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Formülün ilk bölümü, 1 ile başlayan "normalleştirilmiş" bir satır numarası oluşturur:
ROW()-ROW($C$5)+1 // relative row number
5. satırda sonuç 1, 6. satırda sonuç 2, vb.
Geçerli satır numarası 3'ten küçük olduğunda formül #YOK döndürür. Aksi takdirde, formül daha önce olduğu gibi hareketli bir ortalama verir. Bu, ilk tam döneme ulaşılıncaya kadar #YOK çıktı veren Hareketli Ortalamanın Analiz Araç Paketi sürümünün davranışını taklit eder.
Ancak, dönem sayısı arttıkça, sonunda verilerin üzerindeki satırlarınız tükenecek ve AVERAGE içine gerekli aralığı giremeyeceksiniz. Örneğin, gösterildiği gibi çalışma sayfasıyla 7 günlük hareketli bir ortalama ayarlayamazsınız, çünkü C5'in 6 satır üstüne uzanan bir aralık giremezsiniz.
OFFSET ile değişken dönemler
Hareketli bir ortalamayı hesaplamanın daha esnek bir yolu OFFSET işlevidir. OFFSET, dinamik bir aralık oluşturabilir, bu, dönem sayısının değişken olduğu bir formül oluşturabileceğimiz anlamına gelir. Genel biçim şöyledir:
=AVERAGE(OFFSET(A1,0,0,-n,1))
burada n, her ortalamaya dahil edilecek dönem sayısıdır. Yukarıdaki gibi OFSET, ORTALAMA işlevine aktarılan bir aralık döndürür. Aşağıda, bu formülü çalışırken görebilirsiniz, burada "n", adlandırılmış E2 aralığıdır. OFSET, C5 hücresinden başlayarak önceki satırlara kadar uzanan bir aralık oluşturur. Bu, negatif n'ye eşit bir yükseklik kullanılarak gerçekleştirilir. E5 başka bir sayı ile değiştirildiğinde, hareketli ortalama tüm satırlarda yeniden hesaplanır:
Aşağı kopyalanan E5'teki formül:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Yukarıdaki orijinal formülde olduğu gibi, OFFSET'li sürümde de E5'te kaç dönem verildiğine bağlı olarak ilk birkaç satırda yetersiz veri sorunu yaşanacaktır.
Gösterilen örnekte ortalamalar başarıyla hesaplanır çünkü ORTALAMA işlevi metin değerlerini ve boş hücreleri otomatik olarak yok sayar ve C5'in üzerinde başka sayısal değer yoktur. Dolayısıyla, E5'te ORTALAMA'ya aktarılan aralık C1: C5 iken, ortalamaya yalnızca bir değer vardır, 100. Bununla birlikte, dönemler arttıkça, OFSET, verilerin başlangıcını aşan bir aralık oluşturmaya devam edecek ve sonunda çalışma sayfasının en üstünde ve bir #REF hatası döndürüyor.
Çözümlerden biri, aralığın boyutunu mevcut veri noktalarının sayısı ile "sınırlamak" tır. Bu, aşağıda görüldüğü gibi yükseklik için kullanılan sayıyı kısıtlamak için MİN işlevi kullanılarak yapılabilir:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Bu oldukça korkutucu görünüyor ama aslında oldukça basit. OFFSET'e aktarılan yüksekliği MIN işlevi ile sınırlıyoruz:
MIN(ROW()-ROW($C$5)+1,n)
MIN içinde, ilk değer göreceli bir satır numarasıdır ve şu şekilde hesaplanır:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
MIN'e verilen ikinci değer, dönem sayısıdır, n. Göreceli satır numarası n'den küçük olduğunda, MIN geçerli satır numarasını yükseklik için OFFSET'e döndürür. Satır numarası n'den büyük olduğunda, MIN, n'yi döndürür. Başka bir deyişle, MIN, iki değerden daha küçük olanı döndürür.
OFFSET seçeneğinin güzel bir özelliği, n'nin kolayca değiştirilebilmesidir. N'yi 7 olarak değiştirirsek ve sonuçları çizersek, şöyle bir grafik elde ederiz:
Not: Yukarıdaki KAYDIR formülleriyle ilgili bir tuhaflık, E-Tablolar'daki KAYDIR işlevi yükseklik veya genişlik için negatif bir değere izin vermeyeceği için Google E-Tablolar'da çalışmayacak olmasıdır. Ekteki e-tabloda, Google sayfaları için geçici çözüm formülleri bulunmaktadır.
