Všeobecný vzorec
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Zhrnutie
Ak chcete spriemerovať posledných 5 údajových bodov, môžete použiť funkciu PRIEMER spolu s funkciami POČET a POSUN. Tento prístup môžete použiť na spriemerovanie posledných N dátových bodov: posledné 3 dni, posledných 6 meraní atď. V zobrazenom príklade je vzorec v F6:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Poznámka: Záporná hodnota výšky nebude v tabuľkách Google fungovať. Ďalšie informácie nájdete nižšie.
Vysvetlenie
Funkciu OFFSET je možné použiť na vytvorenie dynamických obdĺžnikových rozsahov na základe počiatočného odkazu a daných riadkov, stĺpcov, výšky a šírky. Argumenty riadkov a stĺpcov fungujú ako „posuny“ od počiatočnej referencie. Argumenty výška a šírka (obe voliteľné) určujú, koľko riadkov a stĺpcov obsahuje konečný rozsah. V tomto príklade je OFFSET nakonfigurovaný takto:
- referencia = C3
- riadky = COUNT (A: A)
- cols = 0
- výška = -5
- šírka = (neposkytuje sa)
Počiatočná referencia je poskytnutá ako bunka C3 nad skutočnými údajmi. Pretože chceme, aby OFFSET vrátil rozsah pochádzajúci z poslednej položky v stĺpci C, použijeme funkciu COUNT na spočítanie všetkých hodnôt v stĺpci C, aby sme dostali požadovaný posun riadku. COUNT počíta iba číselné hodnoty, takže nadpis v riadku 3 je automaticky ignorovaný.
S 8 číselnými hodnotami v stĺpci C je vzorec OFFSET rozložený na:
OFFSET(C3,8,0,-5)
Pri týchto hodnotách OFFSET začína na C3, ofsetuje 8 riadkov na C11, potom použije -5 na rozšírenie obdĺžnikového rozsahu o „dozadu“ o 5 riadkov a vytvorenie rozsahu C7: C11.
Nakoniec funkcia OFFSET vráti rozsah C7: C11 do funkcie AVERAGE, ktorá počíta priemer hodnôt v tomto rozsahu.
Excel vs. hárky
Zvláštnym problémom tohto vzorca je, že nebude fungovať s Tabuľkami Google, pretože funkcia OFFSET v Tabuľkách neumožňuje zápornú hodnotu pre argumenty výšky alebo šírky. V dokumentácii k programu Excel sa tiež uvádza, že výška alebo šírka nemôžu byť záporné, ale zdá sa, že záporné hodnoty fungujú v programe Excel od 90. rokov dobre.
Ak sa chcete vyhnúť negatívnym hodnotám výšky alebo šírky, môžete použiť nasledujúci vzorec:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Poznámka C4 je v tomto prípade východiskovou referenciou. Všeobecná forma je:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
kde A1 je prvá bunka v číslach, ktoré chcete spriemerovať.
