Vzorec programu Excel: Priemer z posledných 3 číselných hodnôt

Všeobecný vzorec

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

Zhrnutie

Ak chcete spriemerovať posledné 3 číselné hodnoty v rozsahu, môžete použiť vzorec poľa založený na kombinácii funkcií na vloženie posledných n číselných hodnôt do funkcie PRIEMER. V zobrazenom príklade je vzorec v D6:

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

kde „údaj“ je pomenovaný rozsah B5: B13.

Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.

Vysvetlenie

Funkcia AVERAGE vypočíta priemer čísel prezentovaných v poli, takže takmer všetkou prácou v tomto vzorci je vygenerovanie poľa posledných 3 číselných hodnôt v rozsahu. Pri práci zvnútra smerom von sa funkcia IF používa na „filtrovanie“ číselných hodnôt:

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

Funkcia ISNUMBER vracia TRUE pre číselné hodnoty a FALSE pre ostatné hodnoty (vrátane medzier) a funkcia ROW vracia čísla riadkov, takže výsledkom tejto operácie sú čísla riadkov poľa, ktoré zodpovedajú číselným položkám:

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

Toto pole vstupuje do funkcie LARGE s konštantou poľa (1,2,3) pre k. LARGE automaticky ignoruje FALSE hodnoty a vráti pole s najväčšími 3 číslami, ktoré zodpovedajú posledným 3 riadkom s číselnými hodnotami:

(13,12,10)

Toto pole vstupuje do funkcie LOOKUP ako vyhľadávacia hodnota. Vyhľadávacie pole poskytuje funkcia ROW a výsledné pole je pomenovaný rozsah „data“:

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

LOOKUP potom vráti pole obsahujúce zodpovedajúce hodnoty v "data", ktoré sa načítajú do AVERAGE:

=AVERAGE((100,92,90))

Spracovanie menej hodnôt

Ak počet číselných hodnôt klesne pod 3, tento vzorec vráti chybu # ČÍSLO, pretože LARGE nebude môcť vrátiť 3 hodnoty, ako je požadované. Jedným zo spôsobov, ako to vyriešiť, je nahradiť pevne naprogramovanú konštantu poľa (1,2,3) dynamickým poľom vytvoreným pomocou INDIRECT takto: