Všeobecný vzorec
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Zhrnutie
Ak chcete spriemerovať posledných 5 dátových hodnôt v rozsahu stĺpcov, môžete použiť funkciu PRIEMER spolu s funkciami POČET a POSUN. V zobrazenom príklade je vzorec v F6:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Vysvetlenie
Funkciu OFFSET možno použiť na vytvorenie dynamických rozsahov pomocou počiatočnej bunky a zadaný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, ktoré sú voliteľné, určujú, koľko riadkov a stĺpcov obsahuje konečný rozsah. Chceme, aby OFSET vrátil rozsah, ktorý začína na poslednom vstupe a rozširuje sa „dozadu“, takže zadávame argumenty takto:
referencia - počiatočná referencia je D5 - bunka priamo napravo od vzorca a prvá bunka v rozsahu hodnôt, s ktorými pracujeme.
riadky - pre argument riadky použijeme 0, pretože chceme zostať v rovnakom riadku.
stĺpce - pre argument stĺpcov použijeme funkciu COUNT na spočítanie všetkých hodnôt v rozsahu, potom odčítame 5. Týmto sa posunie začiatok rozsahu o 5 stĺpcov doľava.
výška - použijeme 1, pretože ako konečný výsledok chceme 1-radový rozsah.
šírka - používame 5, pretože chceme výsledný rozsah s 5 stĺpcami.
Pre vzorec v C5 funkcia OFFSET vráti konečný rozsah F5: J5. Ide o funkciu PRIEMER, ktorá vráti priemer z 5 hodnôt v rozsahu
Menej ako 5 hodnôt
Ak existuje menej ako 5 hodnôt, vzorec vráti kruhovú referenčnú chybu, pretože rozsah sa rozšíri späť do bunky, ktorá obsahuje vzorec. Ak chcete zabrániť tejto chybe, môžete vzorec upraviť nasledovne:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Tu používame funkciu MIN na „zachytenie“ situácií, keď je menej ako 5 hodnôt, a na prípadné použitie skutočného počtu.
