Generisk formel
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Resumé
For at finde den maksimale værdi på en given hverdag (dvs. mandag, tirsdag, onsdag osv.) Kan du bruge en simpel matrixformel baseret på MAX-, IF- og TEXT-funktionerne. I det viste eksempel er formlen i celle F5:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Hvor datoer (B5: B15) og værdier (C5: C15) er navngivet områder.
Bemærk: dette er en matrixformel og skal indtastes med Control + Shift + Enter.
Forklaring
Arbejder indefra og ud, TEKST-funktionen bruges til at udtrække en ugedagsværdi for hver dato:
=TEXT(dates,"ddd")
Dette resulterer i en matrix som denne:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
som derefter sammenlignes med teksten i F4, "Man". Resultatet er en anden matrix, der kun indeholder SANDE og FALSKE værdier:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Bemærk, at hver SAND svarer til en mandag. Denne matrix returneres inden for IF-funktionen som den logiske test. Det fungerer som et filter til screening af værdier på andre ugedage. Det endelige resultat fra IF, som returneres direkte til MAX-funktionen, ser sådan ud:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX ignorerer automatisk FALSE-værdier og returnerer den højeste tilbageværende værdi, 94.
Med AGGREGATE
For en lidt mere nørdet formel, der ikke kræver kontrol + shift + enter, kan du bruge AGGREGATE-funktionen som denne:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Dette er formlen, der anvendes i celle F6 i det viste eksempel. Her giver vi AGGREGATE 14 for funktionsargumentet (LARGE) og 6 for optionargument (ignorer fejl). Derefter bygger vi et logisk udtryk ved hjælp af TEKST-funktionen til at kontrollere alle datoer for mandage. Resultatet af denne operation er en matrix af SAND / FALSK-værdier, der bliver nævneren for de oprindelige værdier. Når den bruges i en matematikoperation, vurderes FALSE som nul og kaster et # DIV / 0! fejl. TRUE vurderes som 1 og returnerer den oprindelige værdi. Den sidste række værdier og fejl fungerer som et filter. AGGREGATE ignorerer alle fejl og returnerer den største (maksimale) af de overlevende værdier.
MAKSIFER
MAXIFS-funktionen, der er tilgængelig i Excel Office 365, kan returnere en maksimal værdi ved hjælp af et eller flere kriterier uden behov for en matrixformel. MAXIFS er dog en rækkebaseret funktion og tillader ikke andre funktioner som TEKST at behandle værdier i kriterieområder. Du kan dog tilføje en hjælpekolonne til dataene, generere hverdagsværdier med TEXT og derefter bruge MAXIFS med hjælpekolonnen som et kriterieområde.
