In dit artikel leren we hoe u datums groepeert op het aantal dagen in Excel.
Scenario:
Het probleem is wanneer we een set datumvelden in de draaitabel hebben. En de gebruiker wil het datumveld groeperen op een aantal dagen in plaats van jaren of discrete datums. Bijvoorbeeld het groeperen van de import en export van producten in elke 60 dagen in Excel. Of het groeperen van werknemersgegevens op maanden of weken in Excel. Voor dit soort problemen gebruiken we draaitabellen en groeperingsopties. Er is nog een andere manier voor de gebruikers die de aggregatiefunctie gebruiken. Laten we deze twee processen een voor een begrijpen.
Groeperen in draaitabel in Excel
Selecteer gegevens > Invoegen > Draaitabel > (Voeg een nieuwe draaitabel in) > Ga naar draaitabel voeg datumveld toe aan rijen > Klik met de rechtermuisknop op datumwaarde > Groeperen… > Kies maanden of dagen > Klik op Ok om te bevestigen
Alternatieve manier met aggregatiefunctie
Voeg de startdatum toe en verhoog de cel met elk interval van 60. Gebruik de geaggregeerde Excel-formule met meerdere criteria.
Voorbeeld :
Al deze dingen kunnen verwarrend zijn om te begrijpen. Laten we eens kijken hoe we de functie kunnen gebruiken aan de hand van een voorbeeld. Hier hebben we een voorbeeld van verkoop- en winstgegevens per staten. We moeten de gegevens groeperen op 60 dagen (ongeveer 2 maanden).
Sorteer de gegevens op datumveld. Selecteer de tabel en voeg een draaitabel voor gegevens in. Voeg datum toe aan rijen en verkoop en winst aan waarden.
Zoals u kunt zien is het datumveld in jaarformaat. Maar we willen groeperen op 60 dagen. Dus klik met de rechtermuisknop op datumwaarde> Groep selecteren …
Klik OK.
De datum is gegroepeerd op basis van een interval van 60 dagen, samen met de verkoop- en winstwaarde.
Dit is de gemakkelijkste manier om datums per maand te groeperen. Maar als u de bovenstaande gegevens nodig hebt met behulp van de sumifs-formule, kost het meer tijd, maar het is handig om functies efficiënt te gebruiken.
Alternatieve manier met de SUMIFS-formule
Maak voor dezelfde gegevens een nieuwe tabel met de startdatum en tel 59 dagen op bij de startdatum, zoals hieronder weergegeven.
En gebruik in de A5-cel =B4+1 om de volgende datum te krijgen. Kopieer en plak nu de B4-celformule totdat deze de einddatum krijgt. En kopieer en plak de A5-celformule tot het einddatumbereik.
Gebruik nu de sumifs-formule
=SUMIFS( sombereik, datumkolom uit tabel , ">=" & A4 , datumkolom uit tabel , "<=" & B4 )
Filter de lege of 0-waardecel om hetzelfde veld te krijgen dat we hebben verkregen met behulp van de draaitabel.
Terwijl de draaitabel u verschillende geaggregeerde functies biedt, zoals gemiddelde, telling, som, max, min, var, stdev, maar Excel-functies AVERAGEIFS, COUNTIFS, SUMIFS, MAXIFS en MINIFS om uit te kiezen. MAXIFS en MINIFS zijn updatefuncties boven Excel 2016 of nieuwe Excel 365.
Hier zijn alle observatienotities met behulp van de uitgelegde methode in Excel
Opmerkingen:
- Geef de voorkeur aan of gebruik de draaitabelmethode, deze is eenvoudig en heeft veel functies om uit te kiezen.
- Excel slaat datums en tijd op als getallen en wordt door de functie bij de berekening gebruikt. Het wordt dus aanbevolen om datums als celverwijzing te gebruiken of de DATE-functie te gebruiken in plaats van een direct argument aan de functie te geven. Controleer de validatie van datumwaarden, anders retourneert de functie #WAARDE! Fout.
- Gebruik aanhalingstekens met sumifs-formule voor ">=" en "<=".
- MAXIFS en MINIFS nieuwe en bijgewerkte versie van MIN en MAX met criteria. Of gebruik de matrixformule met MAX en MIN op hun plaats voor MINIFS en MAXIFS oudere versies van Excel.
Ik hoop dat dit artikel over het groeperen van datums op aantal dagen in Excel verhelderend is. Vind hier meer artikelen over het berekenen van datumwaarden en gerelateerde Excel-formules. Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook. We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite.
Excel-draaitabellen : Draaitabellen zijn een van de krachtigste tools en iemand die alle functies van draaitabellen kent, kan zijn productiviteit exponentieel verhogen. In dit artikel zullen we alles over draaitabellen in detail leren.
Vakanties tellen tussen datums in Excel : tel niet-werkdagen tussen de twee opgegeven datums met behulp van de DATEDIF- en NETWORKDAYS-functie in Excel.
Voorwaardelijke opmaak voor draaitabel : Voorwaardelijke opmaak in draaitabellen is hetzelfde als de voorwaardelijke opmaak op normale gegevens. Maar u moet voorzichtig zijn bij het conditioneel opmaken van draaitabellen, aangezien de gegevens dynamisch veranderen.
Hoe datum naar tekst in Excel te converteren : In dit artikel leerden we hoe je tekst omzet in datum, maar hoe zet je een Excel datum om in tekst. Om een Excel-datum om te zetten in tekst hebben we een aantal technieken.
Extraheer dagen tussen datums en negeer jaren in Excel : tel de dagen tussen de twee datums met behulp van verschillende functies en wiskundige bewerkingen in Excel.
Tel geboortedata per maand in Excel : tel het aantal datums dat in een bepaalde maand ligt met behulp van de SOMPRODUCT- en MAAND-functie in Excel.
Hoe de NETWORKDAYS-functie in Excel te gebruiken? : Retourneert de werkdagen tussen de twee opgegeven datums met behulp van de functie NETWERKDAGEN.
Populaire artikels :
Hoe de IF-functie in Excel te gebruiken? : De IF-instructie in Excel controleert de voorwaarde en retourneert een specifieke waarde als de voorwaarde WAAR is of retourneert een andere specifieke waarde als ONWAAR.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? : Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de SUMIF-functie in Excel te gebruiken? : Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
Hoe de AANTAL.ALS-functie in Excel te gebruiken? : Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.