Excel formula: vidējās pēdējās 3 skaitliskās vērtības -

Vispārēja formula

(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))

Kopsavilkums

Lai vidēji aprēķinātu diapazona pēdējās 3 skaitliskās vērtības, varat izmantot masīva formulu, kas balstīta uz funkciju kombināciju, lai pēdējās n skaitliskās vērtības ievadītu funkcijā AVERAGE. Parādītajā piemērā formula D6 ir:

(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))

kur "dati" ir nosauktais diapazons B5: B13.

Piezīme: šī ir masīva formula, un tā jāievada ar vadības taustiņu + Shift + Enter.

Paskaidrojums

Funkcija AVERAGE aprēķinās masīvā uzrādīto skaitļu vidējo vērtību, tāpēc gandrīz viss šīs formulas darbs ir ģenerēt diapazona pēdējo 3 skaitlisko vērtību masīvu. Strādājot no iekšpuses, IF funkciju izmanto, lai "filtrētu" skaitliskās vērtības:

IF(ISNUMBER(data),ROW(data))

Funkcija ISNUMBER skaitliskajām vērtībām atgriež vērtību TRUE un citām vērtībām (ieskaitot tukšās) FALSE, savukārt funkcija ROW atgriež rindu numurus, tāpēc šīs operācijas rezultāts ir masīvu rindu numuri, kas atbilst skaitliskiem ierakstiem:

(5;6;FALSE;8;9;10;FALSE;12;13)

Šis masīvs nonāk funkcijā LARGE ar masīva konstanti (1,2,3) k. LARGE automātiski ignorē FALSE vērtības un atgriež masīvu ar lielākajiem 3 skaitļiem, kas atbilst pēdējām 3 rindām ar skaitliskām vērtībām:

(13,12,10)

Šis masīvs tiek izmantots funkcija LOOKUP kā uzmeklēšanas vērtība. Uzmeklēšanas masīvu nodrošina funkcija ROW, un rezultātu masīvs ir nosauktais diapazons "dati":

LOOKUP((13,12,10), ROW(data), data))

Pēc tam LOOKUP atgriež masīvu, kas satur atbilstošās vērtības "datos", kas tiek ievadīts AVERAGE:

=AVERAGE((100,92,90))

Apstrādājot mazāk vērtību

Ja skaitlisko vērtību skaits nokrītas zem 3, šī formula atgriezīs kļūdu #NUM, jo LARGE nevarēs atgriezt 3 vērtības, kā prasīts. Viens no veidiem, kā to paveikt, ir aizstāt cieti kodētu masīva konstanti (1,2,3) ar dinamisku masīvu, kas izveidots, izmantojot INDIRECT šādi: