Обща формула
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Обобщение
За да осредните последните 5 стойности на данни в диапазон от колони, можете да използвате функцията AVERAGE заедно с функциите COUNT и OFFSET. В показания пример формулата във F6 е:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Обяснение
Функцията OFFSET може да се използва за конструиране на динамични диапазони с помощта на начална клетка и зададени редове, колони, височина и ширина.
Аргументите на редовете и колоните функционират като "отмествания" от началната препратка. Аргументите за височина и ширина, и двата незадължителни, определят колко редове и колони включва крайният диапазон. Искаме OFFSET да върне диапазон, който започва от последния запис и разширява "назад", така че предоставяме аргументи, както следва:
справка - началната препратка е D5 - клетката директно вдясно от формулата и първата клетка в диапазона от стойности, с които работим.
редове - използваме 0 за аргумента редове, защото искаме да останем в същия ред.
колони - за аргумента колони използваме функцията COUNT, за да преброим всички стойности в диапазона, след което изваждаме 5. Това премества началото на колоните от 5 колони вляво.
височина - използваме 1, тъй като искаме 1-редови диапазон като краен резултат.
width - използваме 5, тъй като искаме окончателен диапазон с 5 колони.
За формулата в C5, OFFSET връща краен диапазон от F5: J5. Това влиза във функцията AVERAGE, която връща средната стойност от 5 стойности в диапазона
По-малко от 5 стойности
Ако има по-малко от 5 стойности, формулата ще върне кръгова референтна грешка, тъй като обхватът ще се простира обратно в клетката, която съдържа формулата. За да предотвратите тази грешка, можете да адаптирате формулата, както следва:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Тук използваме функцията MIN за „улавяне“ на ситуации, при които има по-малко от 5 стойности, и използваме действителното броене, когато има такива.
