Obecný vzorec
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
souhrn
Chcete-li průměrovat posledních 5 hodnot dat v řadě sloupců, můžete použít funkci PRŮMĚR spolu s funkcemi POČET a POSUN. V zobrazeném příkladu je vzorec v F6:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Vysvětlení
Funkci OFFSET lze použít ke konstrukci dynamických rozsahů pomocí počáteční buňky a zadaných řádků, sloupců, výšky a šířky.
Argumenty řádků a sloupců fungují jako „posuny“ od výchozí reference. Argumenty výška a šířka, volitelné, určují, kolik řádků a sloupců obsahuje konečný rozsah. Chceme, aby OFFSET vrátil rozsah, který začíná na posledním vstupu a rozšiřuje se „zpět“, takže zadáváme argumenty takto:
reference - počáteční reference je D5 - buňka přímo napravo od vzorce a první buňka v rozsahu hodnot, se kterými pracujeme.
řádky - pro argument řádky použijeme 0, protože chceme zůstat ve stejném řádku.
sloupce - u argumentu sloupce použijeme funkci POČET k spočítání všech hodnot v rozsahu, poté odečteme 5. Tím se začátek sloupce rozsahu 5 posune doleva.
výška - použijeme 1, protože chceme jako konečný výsledek 1řádkový rozsah.
width - používáme 5, protože chceme konečný rozsah s 5 sloupci.
U vzorce v C5 vrátí funkce OFFSET konečný rozsah F5: J5. Tím přejde do funkce PRŮMĚR, která vrátí průměr z 5 hodnot v rozsahu
Méně než 5 hodnot
Pokud existuje méně než 5 hodnot, vrátí vzorec chybu kruhového odkazu, protože rozsah se rozšíří zpět do buňky obsahující vzorec. Chcete-li této chybě zabránit, můžete vzorec upravit následovně:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Zde používáme funkci MIN k "zachycení" situací, kdy je méně než 5 hodnot, a použijeme skutečný počet, když existují.
