Generisk formel
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Sammendrag
For å finne maksimumsverdien på en gitt ukedag (dvs. mandag, tirsdag, onsdag osv.), Kan du bruke en enkel matriseformel basert på funksjonene MAX, IF og TEXT. I eksemplet vist er formelen i celle F5:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Hvor datoer (B5: B15) og verdier (C5: C15) er navngitt områder.
Merk: dette er en matriseformel og må angis med Control + Shift + Enter.
Forklaring
TEKST-funksjonen fungerer fra innsiden og ut og brukes til å trekke ut en ukedagsverdi for hver dato:
=TEXT(dates,"ddd")
Dette resulterer i en matrise som dette:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
som deretter sammenlignes med teksten i F4, "Mon". Resultatet er en annen matrise som bare inneholder SANNE og FALSE verdier:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Merk at hvert SANT tilsvarer en mandag. Denne matrisen returneres i IF-funksjonen som den logiske testen. Det fungerer som et filter for å skjerme ut verdier andre dager i uken. Det endelige resultatet fra IF, som returneres direkte til MAX-funksjonen, ser slik ut:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX ignorerer automatisk FALSE verdier og returnerer den høyeste gjenværende verdien, 94.
Med AGGREGATE
For en litt mer nerdete formel som ikke krever kontroll + shift + enter, kan du bruke AGGREGATE-funksjonen slik:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Dette er formelen som brukes i celle F6 i eksemplet vist. Her gir vi AGGREGATE 14 for funksjonsargumentet (LARGE) og 6 for alternativargument (ignorer feil). Deretter bygger vi et logisk uttrykk ved hjelp av TEKST-funksjonen for å sjekke alle datoer for mandager. Resultatet av denne operasjonen er en matrise med SANNE / FALSE verdier, som blir nevner for de opprinnelige verdiene. Når den brukes i en matematikkoperasjon, blir FALSE evaluert som null, og kaster et # DIV / 0! feil. TRUE vurderes som 1 og returnerer den opprinnelige verdien. Det endelige utvalget av verdier og feil fungerer som et filter. AGGREGATE ignorerer alle feil og returnerer den største (maksimale) av de gjenlevende verdiene.
MAXIFS
MAXIFS-funksjonen, tilgjengelig i Excel Office 365, kan returnere en maksimal verdi ved hjelp av ett eller flere kriterier uten behov for en matriseformel. MAXIFS er imidlertid en rangbasert funksjon, og tillater ikke andre funksjoner som TEKST å behandle verdier i kriterieområder. Du kan imidlertid legge til en hjelpekolonne i dataene, generere ukedagsverdier med TEKST, og deretter bruke MAXIFS med hjelpekolonnen som et kriterieområde.
