Generieke formule
(=MAX(IF(TEXT(dates,"ddd")="Mon",values)))
Samenvatting
Om de maximale waarde op een bepaalde weekdag te vinden (dwz maandag, dinsdag, woensdag, enz.), Kunt u een eenvoudige matrixformule gebruiken die is gebaseerd op de functies MAX, IF en TEXT. In het getoonde voorbeeld is de formule in cel F5:
=MAX(IF(TEXT(dates,"ddd")=F4,values))
Waar datums (B5: B15) en waarden (C5: C15) benoemde bereiken zijn.
Opmerking: dit is een matrixformule en moet worden ingevoerd met Control + Shift + Enter.
Uitleg
Werkend van binnen naar buiten, wordt de TEXT-functie gebruikt om een weekdagwaarde voor elke datum te extraheren:
=TEXT(dates,"ddd")
Dit resulteert in een array als deze:
("Mon";"Tue";"Wed";"Thu";"Fri";"Mon";"Tue";"Wed";"Thu";"Fri";"Mon")
die vervolgens wordt vergeleken met de tekst in F4, "Mon". Het resultaat is een andere array, die alleen TRUE en FALSE waarden bevat:
(TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE)
Merk op dat elke WAAR overeenkomt met een maandag. Deze array wordt binnen de ALS-functie geretourneerd als de logische test. Het fungeert als een filter om waarden op andere dagen van de week te filteren. Het uiteindelijke resultaat van IF, dat rechtstreeks naar de MAX-functie wordt geretourneerd, ziet er als volgt uit:
=MAX((85;FALSE;FALSE;FALSE;FALSE;94;FALSE;FALSE;FALSE;FALSE;52))
MAX negeert automatisch FALSE-waarden en retourneert de hoogste resterende waarde, 94.
Met AGGREGATE
Voor een iets meer geeky formule waarvoor geen controle + shift + enter vereist is, kunt u de AGGREGATE-functie als volgt gebruiken:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")=F4),1)
Dit is de formule die wordt gebruikt in cel F6 in het getoonde voorbeeld. Hier geven we AGGREGATE 14 voor het functieargument (LARGE) en 6 voor optieargument (negeer fouten). Vervolgens bouwen we een logische uitdrukking op met de functie TEXT om alle datums voor maandag te controleren. Het resultaat van deze bewerking is een reeks TRUE / FALSE-waarden, die de noemer worden van de oorspronkelijke waarden. Bij gebruik in een wiskundige bewerking, evalueert FALSE als nul en gooit een # DIV / 0! fout. WAAR evalueert als 1 en retourneert de oorspronkelijke waarde. De laatste reeks waarden en fouten werkt als een filter. AGGREGATE negeert alle fouten en retourneert de grootste (maximum) van de overgebleven waarden.
MAXIFS
De MAXIFS-functie, beschikbaar in Excel Office 365, kan een maximale waarde retourneren op basis van een of meer criteria zonder dat een matrixformule nodig is. MAXIFS is echter een op afstand gebaseerde functie en staat niet toe dat andere functies zoals TEXT waarden in criteriabereiken verwerken. U kunt echter een hulpkolom aan de gegevens toevoegen, weekdagwaarden genereren met TEXT en vervolgens MAXIFS gebruiken met de hulpkolom als criteriumbereik.
