Genel formül
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Özet
Bir aralıktaki son 3 sayısal değerin ortalamasını almak için, son n sayısal değeri ORTALAMA işlevine beslemek için işlevlerin bir kombinasyonunu temel alan bir dizi formülü kullanabilirsiniz. Gösterilen örnekte, D6'daki formül şöyledir:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
burada "veri", B5: B13 olarak adlandırılan aralıktır.
Not: Bu bir dizi formülüdür ve kontrol + shift + enter ile girilmelidir.
Açıklama
ORTALAMA işlevi, bir dizide sunulan sayıların ortalamasını hesaplayacaktır, bu nedenle bu formüldeki neredeyse tüm çalışma, bir aralıktaki son 3 sayısal değerden oluşan bir dizi oluşturmaktır. Baştan sona çalışarak, IF işlevi sayısal değerleri "filtrelemek" için kullanılır:
IF(ISNUMBER(data),ROW(data))
ISNUMBER işlevi, sayısal değerler için TRUE, diğer değerler için (boşluklar dahil) FALSE döndürür ve SATIR işlevi satır numaralarını döndürür, bu nedenle bu işlemin sonucu, sayısal girişlere karşılık gelen bir dizi satır numarasıdır:
(5;6;FALSE;8;9;10;FALSE;12;13)
Bu dizi, k için dizi sabiti (1,2,3) ile LARGE fonksiyonuna gider. BÜYÜK, YANLIŞ değerleri otomatik olarak yok sayar ve sayısal değerlere sahip son 3 satıra karşılık gelen en büyük 3 sayıya sahip bir dizi döndürür:
(13,12,10)
Bu dizi, arama değeri olarak ARA işlevine gider. Arama dizisi SATIR işlevi tarafından sağlanır ve sonuç dizisi "veri" olarak adlandırılan aralıktır:
LOOKUP((13,12,10), ROW(data), data))
LOOKUP, "veriler" deki karşılık gelen değerleri içeren bir dizi döndürür ve ORTALAMA:
=AVERAGE((100,92,90))
Daha az değeri işleme
Sayısal değerlerin sayısı 3'ün altına düşerse, bu formül #SAYI hatasını döndürür çünkü BÜYÜK istendiği gibi 3 değer döndüremeyecektir. Bunu halletmenin bir yolu, sabit kodlu dizi sabitini (1,2,3) DOLAYLI kullanılarak oluşturulan dinamik bir diziyle değiştirmektir:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Burada MIN, dizinin üst sınırını 3'e veya sayısal değerlerin gerçek sayısını (hangisi daha küçükse) ayarlamak için kullanılır.
Not: Bu zekice yaklaşımla chandoo.org'da Sajan'ın benzer bir soruya verdiği yanıtla karşılaştım.
