Generička formula
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Sažetak
Da biste pronašli maksimalnu vrijednost određenog radnog dana (tj. Ponedjeljak, utorak, srijeda itd.), Možete koristiti jednostavnu formulu niza koja se temelji na funkcijama MAX, IF i TEXT. U prikazanom primjeru formula u ćeliji F5 je:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Gdje su datumi (B5: B15) i vrijednosti (C5: C15) imenovani rasponi.
Napomena: ovo je formula niza i mora se unijeti Control + Shift + Enter.
Obrazloženje
Funkcionirajući iznutra prema van, funkcija TEXT koristi se za izdvajanje vrijednosti radnog dana za svaki datum:
=TEXT(dates,"ddd")
Rezultat je ovakav niz:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
koji se zatim uspoređuje s tekstom u F4, "Ponedjeljak". Rezultat je drugi niz koji sadrži samo TRUE i FALSE vrijednosti:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Imajte na umu da svaka ISTINA odgovara ponedjeljku. Taj se niz vraća unutar funkcije IF kao logički test. Djeluje kao filtar za prikazivanje vrijednosti ostalim danima u tjednu. Konačni rezultat iz IF, koji se vraća izravno u MAX funkciju, izgleda ovako:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX automatski zanemaruje FALSE vrijednosti i vraća najveću preostalu vrijednost, 94.
S AGREGATE
Za malo geeky formulu koja ne zahtijeva kontrolu + shift + enter, možete koristiti funkciju AGGREGATE poput ove:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
To je formula koja se koristi u ćeliji F6 u prikazanom primjeru. Ovdje dajemo AGREGATE 14 za argument funkcije (LARGE) i 6 za argument opcije (zanemarimo pogreške). Zatim gradimo logički izraz pomoću funkcije TEXT za provjeru svih datuma ponedjeljkom. Rezultat ove operacije je niz TRUE / FALSE vrijednosti, koje postaju nazivnik izvornih vrijednosti. Kada se koristi u matematičkoj operaciji, FALSE izračunava nulu i baca # DIV / 0! pogreška. TRUE izračunava vrijednost 1 i vraća izvornu vrijednost. Konačni niz vrijednosti i pogrešaka djeluje poput filtra. AGGREGATE ignorira sve pogreške i vraća najveću (maksimum) od preživjelih vrijednosti.
MAKSIFIKATI
Funkcija MAXIFS, dostupna u programu Excel Office 365, može vratiti maksimalnu vrijednost pomoću jednog ili više kriterija bez potrebe za formulom niza. Međutim, MAXIFS je funkcija koja se temelji na rasponu i neće dopustiti drugim funkcijama poput TEXT da obrađuju vrijednosti u rasponima kriterija. Međutim, podacima možete dodati pomoćni stupac, generirati vrijednosti dana u tjednu pomoću TEXT, a zatim upotrijebiti MAXIFS sa pomoćnim stupcem kao raspon kriterija.
