Bendroji formulė
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Santrauka
Norėdami apskaičiuoti vidutines 3 paskutines skaitines reikšmes diapazone, galite naudoti masyvo formulę, pagrįstą funkcijų deriniu, kad paskutinės n skaitinės vertės būtų įtrauktos į funkciją AVERAGE. Parodytame pavyzdyje formulė D6 yra:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
kur „duomenys“ yra įvardytas diapazonas B5: B13.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Funkcija AVERAGE apskaičiuos matricoje pateiktų skaičių vidurkį, todėl beveik visas šios formulės darbas yra generuoti masyvą iš 3 paskutinių skaitinių reikšmių diapazone. Dirbant iš vidaus, IF funkcija naudojama „filtruoti“ skaitines vertes:
IF(ISNUMBER(data),ROW(data))
Funkcija ISNUMBER grąžina SUSIS skaitinėms reikšmėms ir NETIESA kitoms reikšmėms (įskaitant tuščius), o funkcija ROW pateikia eilutės numerius, todėl šios operacijos rezultatas yra masyvo eilutės numeriai, atitinkantys skaitinius įrašus:
(5;6;FALSE;8;9;10;FALSE;12;13)
Šis masyvas eina į LARGE funkciją su masyvo konstanta (1,2,3) k. LARGE automatiškai ignoruoja FALSE reikšmes ir pateikia masyvą su didžiausiais 3 skaičiais, kurie atitinka 3 paskutines eilutes su skaitinėmis reikšmėmis:
(13,12,10)
Šis masyvas patenka į funkciją LOOKUP kaip paieškos reikšmę. Paieškos masyvą teikia funkcija ROW, o rezultatų masyvas yra pavadintas diapazonas „duomenys“:
LOOKUP((13,12,10), ROW(data), data))
Tada LOOKUP pateikia masyvą, kuriame yra atitinkamos reikšmės "duomenyse", kuris perduodamas į AVERAGE:
=AVERAGE((100,92,90))
Tvarkyti mažiau vertybių
Jei skaitinių verčių skaičius nukris žemiau 3, ši formulė grąžins klaidą #NUM, nes LARGE negalės grąžinti 3 reikšmių, kaip reikalaujama. Vienas iš būdų tai padaryti yra pakeisti sunkiai užkoduotą masyvo konstantą (1,2,3) dinaminiu masyvu, sukurtu naudojant INDIRECT taip:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Čia MIN naudojamas nustatyti viršutinę masyvo ribą į 3 arba faktinį skaitinių verčių skaičių, atsižvelgiant į tai, kuris dydis yra mažesnis.
Pastaba: aš patyriau šį protingą požiūrį svetainėje chandoo.org, atsakydamas į Sajaną į panašų klausimą.
