Generieke formule
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Samenvatting
Om per maand te middelen, kunt u een formule gebruiken die is gebaseerd op de functie GEMIDDELDE.ALS, met behulp van de functie EOMONTH. In het getoonde voorbeeld is de formule in F4:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Deze formule gebruikt de genoemde bereiken "bedragen" (D5: D104) en "datums" (C5: C104).
Uitleg
De functie AVERAGEIFS kan bereiken gemiddelde waarden op basis van meerdere criteria. In dit geval configureren we AVERAGEIFS op gemiddelde bedragen per maand met behulp van twee criteria: (1) wedstrijddatums groter dan of gelijk aan de eerste dag van de maand, (2) wedstrijddatums kleiner dan of gelijk aan de laatste dag van de maand. Als we datums voor januari 2016 hard coderen in de formule met behulp van de DATUM-functie, zou het er zo uitzien.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Maar we willen datums niet hard coderen, we willen dat Excel deze datums voor ons genereert. Normaal gesproken is dit vervelend, want als je maandnamen toevoegt als tekst (bijvoorbeeld "januari", "februari", "maart", enz.) In kolom F, moet je extra moeite doen om datums te creëren die je voor criteria kunt gebruiken .
In dit geval gebruiken we echter een eenvoudige truc om dingen gemakkelijker te maken: in kolom F, in plaats van maandnamen te typen, voegen we werkelijke datums toe voor de eerste van elke maand (1/1/2016, 2/1/2016, 3 / 1/2016, etc.), en gebruik een aangepaste datumnotatie ("mmm") om de maandnamen weer te geven.
Dit maakt het gemakkelijk om de criteria op te bouwen die we nodig hebben voor AVERAGEIFS. Om datums te matchen die groter zijn dan of gelijk zijn aan de eerste van de maand, gebruiken we:
">="&E4
En om datums te matchen die kleiner zijn dan of gelijk zijn aan de laatste dag van de maand, gebruiken we:
"<="&EOMONTH(E4,0)
EOMONTH retourneert automatisch de laatste dag van dezelfde maand omdat we nul opgeven voor het argument maanden.
Opmerking: aaneenschakeling met een ampersand (&) is nodig bij het bouwen van criteria op basis van een celverwijzing.
Draaitafel oplossing
Een draaitabel is een uitstekende oplossing wanneer u gegevens op jaar, maand, kwartaal enzovoort moet samenvatten, omdat draaitabellen bedieningselementen bieden voor automatisch groeperen op datum. Zie deze video voor een vergelijking van formules versus draaitabellen naast elkaar: Waarom draaitabellen.
