Obecný vzorec
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
souhrn
Chcete-li průměrovat posledních 5 datových bodů, můžete použít funkci PRŮMĚR spolu s funkcemi POČET a POSUN. Tento přístup můžete použít k průměrování posledních N datových bodů: poslední 3 dny, posledních 6 měření atd. V zobrazeném příkladu je vzorec v F6:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Poznámka: Záporná hodnota výšky nebude v tabulkách Google fungovat. Další informace naleznete níže.
Vysvětlení
Funkci OFFSET lze použít ke konstrukci dynamických obdélníkových rozsahů na základě počátečního odkazu a daných řádků, sloupců, výšky a šířky. Argumenty řádků a sloupců fungují jako „posuny“ od výchozí reference. Argumenty výška a šířka (oba volitelné) určují, kolik řádků a sloupců obsahuje konečný rozsah. V tomto příkladu je OFFSET nakonfigurován takto:
- reference = C3
- řádky = COUNT (A: A)
- cols = 0
- výška = -5
- width = (není k dispozici)
Výchozí reference je poskytována jako buňka C3 nad skutečnými daty. Protože chceme, aby OFFSET vrátil rozsah pocházející z poslední položky ve sloupci C, použijeme funkci COUNT k spočítání všech hodnot ve sloupci C, abychom získali požadovaný offset řádku. COUNT počítá pouze číselné hodnoty, takže nadpis v řádku 3 je automaticky ignorován.
S 8 číselnými hodnotami ve sloupci C je vzorec OFFSET vyřešen na:
OFFSET(C3,8,0,-5)
S těmito hodnotami začíná OFFSET na C3, posunuje 8 řádků na C11, poté používá -5 k rozšíření obdélníkového rozsahu nahoru „zpět“ o 5 řádků a vytvoření rozsahu C7: C11.
Nakonec funkce OFFSET vrátí rozsah C7: C11 do funkce PRŮMĚR, která vypočítá průměr hodnot v tomto rozsahu.
Excel vs. listy
Zvláštní podivnost s tímto vzorcem spočívá v tom, že nebude fungovat s Tabulkami Google, protože funkce OFFSET v Tabulkách neumožňuje zápornou hodnotu pro argumenty výšky nebo šířky. Dokumentace Excel také uvádí, že výška nebo šířka nemohou být záporné, ale zdá se, že záporné hodnoty fungovaly dobře v Excelu od 90. let.
Abyste se vyhnuli záporným hodnotám výšky nebo šířky, můžete použít vzorec jako tento:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Všimněte si, že v tomto případě je výchozím odkazem C4. Obecná forma je:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
kde A1 je první buňka v číslech, která chcete průměrovat.
