Vzorec aplikace Excel: Průměr posledních 3 číselných hodnot -

Obecný vzorec

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

souhrn

Chcete-li zprůměrovat poslední 3 číselné hodnoty v rozsahu, můžete použít vzorec pole založený na kombinaci funkcí k vložení posledních n číselných hodnot do funkce PRŮMĚR. V zobrazeném příkladu je vzorec v D6:

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

kde „data“ je pojmenovaný rozsah B5: B13.

Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.

Vysvětlení

Funkce PRŮMĚR vypočítá průměr čísel uvedených v poli, takže téměř veškerá práce v tomto vzorci spočívá v generování pole posledních 3 číselných hodnot v rozsahu. Při práci zevnitř ven se funkce IF používá k „filtrování“ číselných hodnot:

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

Funkce ISNUMBER vrací TRUE pro číselné hodnoty a FALSE pro jiné hodnoty (včetně mezer) a funkce ROW vrací čísla řádků, takže výsledkem této operace jsou čísla řádků pole, která odpovídají číselným položkám:

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

Toto pole jde do funkce VELKÉ s konstantou pole (1,2,3) pro k. LARGE automaticky ignoruje FALSE hodnoty a vrátí pole s největšími 3 čísly, která odpovídají posledním 3 řádkům s číselnými hodnotami:

(13,12,10)

Toto pole přejde do funkce LOOKUP jako vyhledávací hodnota. Vyhledávací pole je poskytováno funkcí ROW a výsledné pole je pojmenovaný rozsah „data“:

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

LOOKUP pak vrátí pole obsahující odpovídající hodnoty v "data", která je vložena do AVERAGE:

=AVERAGE((100,92,90))

Zpracování méně hodnot

Pokud počet číselných hodnot klesne pod 3, vrátí tento vzorec chybu # ČÍSLO, protože LARGE nebude moci vrátit 3 hodnoty podle požadavku. Jedním ze způsobů, jak to zvládnout, je nahradit pevně konstantu pole (1,2,3) dynamickým polem vytvořeným pomocí INDIRECT takto: