[Excel] Overzicht van aantallen per maand

[Brittie]

Goedemiddag,

Ik hoop dat jullie mij kunnen helpen. Ik heb een overzicht van behandelingen die een begin- en een einddatum hebben. Deze data zijn (uiteraard) niet allemaal gelijk.
Nu heb ik een overzicht nodig van het aantal behandelingen dat per maand liep vanaf 1-1-2023 tot aan 31-3-2024. Sommige start- en/of einddata vallen in dit interval, maar vele vallen erbuiten. Ik heb al van alles geprobeerd, maar ik krijg het niet voor elkaar. Kan dit überhaupt? En zo ja, hoe dan?
Ik heb een tabel als:
naam soort behandeling begindatum einddatum

en dan iets van 500 cliënten (en dus rijen)

[se77en]

Wil je het aantal behandelingen dat tussen 1-1-2023 en 31-3-2024 plaatsvond of per maand, dus januari 2023, februari, maart 2023 tot en met maart 2024?

[RaZ]

Mooi klusje voor een zzp'ende programmeur met kennis van Excel en VBA. Moet wel lukken voor 2k in 1 weekie van 40 uur (wilde gok hoor).

Want die 500 clienten/rijen moet je rij voor rij opnieuw berekenen. Want volgens mij wil je een gemiddelde weten per client in 1 jaar tijd.

[ Voor 3% gewijzigd door RaZ op 28-08-2024 12:50 ]

[Sethro]

Als in Excel is zou ik gewoon een paar kolommen toevoegen met als titel januari 2023, februari 2023, en zo door tot maart 2024.

In die kolommen dan alle cellen vullen met de formule: ALS behandelstartdatum is kleiner of gelijk aan '31 januari 2023' EN behandeleinddatum is groter of gelijk aan '1 januari 2023' DAN 1 ANDERS 0.

En dan kun je gewoon het aantal 1-en tellen in de kolom januari 2023 om erachter te komen hoeveel behandelingen er in die maand liepen.

[ Voor 13% gewijzigd door Sethro op 28-08-2024 12:53 ]

[Sethro]

RaZ schreef op woensdag 28 augustus 2024 @ 12:49:
Mooi klusje voor een zzp'ende programmeur met kennis van Excel en VBA. Moet wel lukken voor 2k in 1 weekie van 40 uur (wilde gok hoor).

Want die 500 clienten/rijen moet je rij voor rij opnieuw berekenen. Want volgens mij wil je een gemiddelde weten per client in 1 jaar tijd.

Ben je op zoek naar werk? Dit kost max 15min als je beetje handig bent. 5min om 2 simpele formules te maken en door te trekken. 10min om te testen of het werkt.

[ Voor 13% gewijzigd door Sethro op 28-08-2024 12:56 ]

[F_J_K]

Welkom @Brittie

Het zou helpen als je een indicatie geeft van wat er concreet is geprobeerd. Enerzijds: dan hoeven anderen dat niet te proberen, anderzijds: het nodigt veel meer uit tot meedenken als er duidelijk moeite is gestoken in het zelf proberen.

Dat zijn redenen waarom dat het bij aanmaken van dit topic was aangeduid met '..'

Extra kolom, slim ALS(), misschien een draaitabel, etc. Niet mooi maar wel makkelijkste is voor een beginner misschien wat hulpkolommen: 'omvat januari', 'omvat februari', etc.

[RaZ]

Sethro schreef op woensdag 28 augustus 2024 @ 12:53:
[...]


Ben je op zoek naar werk? Dit kost max 15min als je beetje handig bent.

Haha nee. Die kennis heb ik niet zelf. Althans, niet met formules, maar in VBA lukt mij dat wel (na veel proberen dan hoor). Maar puur kijkend naar de vraag, omdat het gemiddeld per maand is, dan lijkt het mij er op dat je 1x in de maand elke keer iets moet herhalen zodat je een nieuw gemiddelde gaat krijgen als een behandeling pas in de toekomst gaat plaatsvinden.

Edit:
Maar als je met een formule de aantal behandelingen gaat optellen die tussen begin-datum en eind-datum liggen is dat niet zo moeilijk. Optellen en dan delen door 12, en dan weet het je aantal behandelingen per maand. Ik tik dat zelf liever in VBA dan ik met formules ga prutsen

[ Voor 20% gewijzigd door RaZ op 28-08-2024 13:07 ]

[se77en]

Sethro schreef op woensdag 28 augustus 2024 @ 12:49:
Als in Excel is zou ik gewoon een paar kolommen toevoegen met als titel januari 2023, februari 2023, en zo door tot maart 2024.

In die kolommen dan alle cellen vullen met de formule: ALS behandelstartdatum is kleiner of gelijk aan '31 januari 2023' EN behandeleinddatum is groter of gelijk aan '1 januari 2023' DAN 1 ANDERS 0.

En dan kun je gewoon het aantal 1-en tellen in de kolom januari 2023 om erachter te komen hoeveel behandelingen er in die maand liepen.

Dit is waarschijnlijk de juiste richting. Om te voorkomen dat je een lijst met laatste dagen van de maand moet genereren (wat voor februari onregelmatig is) zou ik gebruikmaken van MAAND(behandeldatum) en dan kan je als criterium =[maand voor overzicht] gebruiken.

[Brittie]

Dank voor alle reacties! Het gaat hier inderdaad om Excel. Ik heb overwogen om het zelf te programmeren (buiten Excel dus), maar dat is helaas geen optie.
Ik wil per maand weten hoeveel behandelingen er liepen. Ik had zelf ook al wat zitten spelen met extra kolommen, maar kreeg daaruit niet het gewenste effect. Ik wilde dit met een formule doen, zodat ik niet zelf handmatig voor alle 500 cliënten voor alle maanden in hoefde te vullen of de behandeling wel of niet 'actief' was in die maand. Ik heb daarmee dan ook geprobeerd dat de begindatum altijd op de eerste van de maand wordt gezet (dus begindatum 05-02 wordt 01-02) en de einddatum op de laatste dag van de maand (dus ipv 10-04, 30-04) maar bij deze laatste ging het mis. Hier kwam ik namelijk in de knoop met de verschillende lengtes van de maanden. Het is echter wel belangrijk dat de maand waarin het eindigde nog wordt meegeteld.
Ik had ook al wat zitten klooien met draaitabellen, maar omdat ik in eerste instantie alleen een begin- en einddatum had, lukte het me daar enkel maar de aantallen start en eind in betreffende maand te krijgen. Dat is echter helaas niet wat ik zoek.
Ook had ik geprobeerd of ik met DATEDIF wat kon, maar ook dat blijkt geen optie.
De opties voor het toevoegen van de kolommen lijkt dus toch de beste, en ik zal eens kijken of ik met MAAND ergens kom.
Een week heb ik niet, en al helemaal geen 2k, dus hoewel dit me schromelijk overdreven lijkt is het dus sowieso al geen optie. Het lijkt me dat hier toch relatief simpele oplossingen voor moeten zijn. Misschien niet de mooiste, maar dat geeft niet. Voor de organisatie zijn alleen de cijfers van belang, niet hoe ik ze uit Excel krijg.

[se77en]

Brittie schreef op woensdag 28 augustus 2024 @ 15:12:
Dank voor alle reacties! Het gaat hier inderdaad om Excel. Ik heb overwogen om het zelf te programmeren (buiten Excel dus), maar dat is helaas geen optie.
Ik wil per maand weten hoeveel behandelingen er liepen. Ik had zelf ook al wat zitten spelen met extra kolommen, maar kreeg daaruit niet het gewenste effect. Ik wilde dit met een formule doen, zodat ik niet zelf handmatig voor alle 500 cliënten voor alle maanden in hoefde te vullen of de behandeling wel of niet 'actief' was in die maand. Ik heb daarmee dan ook geprobeerd dat de begindatum altijd op de eerste van de maand wordt gezet (dus begindatum 05-02 wordt 01-02) en de einddatum op de laatste dag van de maand (dus ipv 10-04, 30-04) maar bij deze laatste ging het mis. Hier kwam ik namelijk in de knoop met de verschillende lengtes van de maanden. Het is echter wel belangrijk dat de maand waarin het eindigde nog wordt meegeteld.
Ik had ook al wat zitten klooien met draaitabellen, maar omdat ik in eerste instantie alleen een begin- en einddatum had, lukte het me daar enkel maar de aantallen start en eind in betreffende maand te krijgen. Dat is echter helaas niet wat ik zoek.
Ook had ik geprobeerd of ik met DATEDIF wat kon, maar ook dat blijkt geen optie.
De opties voor het toevoegen van de kolommen lijkt dus toch de beste, en ik zal eens kijken of ik met MAAND ergens kom.
Een week heb ik niet, en al helemaal geen 2k, dus hoewel dit me schromelijk overdreven lijkt is het dus sowieso al geen optie. Het lijkt me dat hier toch relatief simpele oplossingen voor moeten zijn. Misschien niet de mooiste, maar dat geeft niet. Voor de organisatie zijn alleen de cijfers van belang, niet hoe ik ze uit Excel krijg.

Heb je Excel op Engels of Nederlands staan?

[Brittie]

se77en schreef op woensdag 28 augustus 2024 @ 15:17:
[...]


Heb je Excel op Engels of Nederlands staan?

Mijn Excel staat op Nederlands, dus had het hier over DATUMVERSCHIL, maar kon net tijdens het typen even niet op deze naam komen

[Brittie]

se77en schreef op woensdag 28 augustus 2024 @ 12:45:
Wil je het aantal behandelingen dat tussen 1-1-2023 en 31-3-2024 plaatsvond of per maand, dus januari 2023, februari, maart 2023 tot en met maart 2024?

Het aantal behandelingen per maand dus, maar de behandelingen lopen maanden tot jaren. Ik wil dus het aantal lopende behandelingen per maand, waarbij behandelingen die in die maand afgerond zijn wel meetellen voor het aantal van die maand (dus 04-04 gestopt, moet nog wel meegeteld worden voor april)

[Lustucru]

Voor bv april is het niet meer dan: aantallen.als [begindatum]<1 mei EN (einddatum is leeg) of (einddatum >=1 april))

[dixet]

Brittie schreef op woensdag 28 augustus 2024 @ 15:12:
Ik heb daarmee dan ook geprobeerd dat de begindatum altijd op de eerste van de maand wordt gezet (dus begindatum 05-02 wordt 01-02) en de einddatum op de laatste dag van de maand (dus ipv 10-04, 30-04) maar bij deze laatste ging het mis.

Het einde van een maand kan je eenvoudig bepalen met de functie EOMONTH.

De eerste dag van een maand:

code:

=EOMONTH(TODAY();-1)+1

(lees: einde van de vorige maand plus 1 dag)

en de laatste

code:

=EOMONTH(TODAY();0)

(lees: einde van deze maand)

Door de behandelingen op deze manier aan het begin van de maand te laten starten kan je eenvoudig met COUNTIFS() tellen hoeveel behandelingen er in een maand vallen:

Ik heb even een setje met maar 15 clienten gemaakt maar het idee is het zelfde als voor 500 clienten: maak een rij met alle datums van 1/1/2023 t/m 1/4/2024.
Daaronder zet je de formule

code:

=COUNTIFS($E$2:$E$16;"<="&H1;$F$2:$F$16;">="&H1)

en die trek je door over alle maanden.

In kolommen C en D staan de echte datums, in kolom E en F is dit verschoven naar het begin of einde van de maand.
Vertaling: tel alle cellen die in $E$2:$E$16 een waarde hebben kleiner over gelijk aan de datum in H1, en die in $F$2:$F$16 een waarde hebben die groter is dan die in H1.

[jbdeiman]

@Lustucru Klopt, om te zien of het binnen een maand valt moet de startdatum voor de laatste dag van de maand liggen én de einddatum ná de eerste dag van de maand.

Ik zou het oplossen (omdat dit i.m.o. het duidelijkste overzicht geeft) door naast de rijen met cliéntnummer, start en einddatum een tabel te creëren met (genummerd) de maanden. Dan kan je een formule toepassen (deze komt uit Google sheets) waarbij je het volgende 'berekend':
- Als startdatum <= laatstedag van de maand *kolomheader* EN einddatum >= eerste dag van de maand "kolomheader", dan 1 anders 0 (formuie zag er bij mij zo uit)

=IF(AND(EOMONTH(DATE(2023;E$2;1);0) >= $B3;DATE(2023;E$2;1) <= $C3);1;0)

Dan krijg je een tabel met daarin een onder het maandnummer per cliént een 1 als deze in die maand aanwezig was, en anders een 0. Onder die tabel kan je dan weer simpel de totalen bepalen per maand.

Je zou dan ook nog in plaats van de kolomnaam = maandnummer, net als @dixet heeft een datum kunnen zetten en met de year() en month() functies daar het jaartal en de maand uit halen en daarmee bovenstaande functie aanpassen zodat je ook andere jaren kan verwerken.

[ Voor 18% gewijzigd door jbdeiman op 28-08-2024 15:55 ]

[Brittie]

jbdeiman schreef op woensdag 28 augustus 2024 @ 15:49:
@Lustucru Klopt, om te zien of het binnen een maand valt moet de startdatum voor de laatste dag van de maand liggen én de einddatum ná de eerste dag van de maand.

Ik zou het oplossen (omdat dit i.m.o. het duidelijkste overzicht geeft) door naast de rijen met cliéntnummer, start en einddatum een tabel te creëren met (genummerd) de maanden. Dan kan je een formule toepassen (deze komt uit Google sheets) waarbij je het volgende 'berekend':
- Als startdatum <= laatstedag van de maand *kolomheader* EN einddatum >= eerste dag van de maand "kolomheader", dan 1 anders 0 (formuie zag er bij mij zo uit)

=IF(AND(EOMONTH(DATE(2023;E$2;1);0) >= $B3;DATE(2023;E$2;1) <= $C3);1;0)

Dan krijg je een tabel met daarin een onder het maandnummer per cliént een 1 als deze in die maand aanwezig was, en anders een 0. Onder die tabel kan je dan weer simpel de totalen bepalen per maand.[Afbeelding]

Je zou dan ook nog in plaats van de kolomnaam = maandnummer, net als @dixet heeft een datum kunnen zetten en met de year() en month() functies daar het jaartal en de maand uit halen en daarmee bovenstaande functie aanpassen zodat je ook andere jaren kan verwerken.

Ik vind dit inderdaad wel een overzichtelijkere tabel. Zo kan ik het ook met de totalen gewoon weer in een staafgrafiek krijgen toch?!

Dit werkt nu! (wel de formule even naar het Nederlands vertaald). Met als enige kanttekening dat ik bij februari 2023 overal een 0 krijg, terwijl dit niet klopt. Februari 2024 snapt hij dan weer wel... Ik zelf kan het verschil niet vinden en kom er dus ook niet achter waarom februari 2023 een probleem geeft. Kijk ik scheel?

Update: Ik keek scheel. Ik was een $ vergeten, waardoor er iedere kolom naar een ander veld gekeken werd. Dit moet alleen in de rijen gebeuren. Het is nu opgelost en nu werkt het!

[ Voor 43% gewijzigd door Brittie op 28-08-2024 16:49 ]

[se77en]

dixet schreef op woensdag 28 augustus 2024 @ 15:48:
[...]

Het einde van een maand kan je eenvoudig bepalen met de functie EOMONTH.

De eerste dag van een maand:

code:

=EOMONTH(TODAY();-1)+1

(lees: einde van de vorige maand plus 1 dag)

en de laatste

code:

=EOMONTH(TODAY();0)

(lees: einde van deze maand)

Door de behandelingen op deze manier aan het begin van de maand te laten starten kan je eenvoudig met COUNTIFS() tellen hoeveel behandelingen er in een maand vallen:

Ik heb even een setje met maar 15 clienten gemaakt maar het idee is het zelfde als voor 500 clienten: maak een rij met alle datums van 1/1/2023 t/m 1/4/2024.
Daaronder zet je de formule

code:

=COUNTIFS($E$2:$E$16;"<="&H1;$F$2:$F$16;">="&H1)

en die trek je door over alle maanden.

In kolommen C en D staan de echte datums, in kolom E en F is dit verschoven naar het begin of einde van de maand.
Vertaling: tel alle cellen die in $E$2:$E$16 een waarde hebben kleiner over gelijk aan de datum in H1, en die in $F$2:$F$16 een waarde hebben die groter is dan die in H1.

[Afbeelding]

Lijkt me goed.
Iets soortgelijks.

jbdeiman schreef op woensdag 28 augustus 2024 @ 15:49:
@Lustucru Klopt, om te zien of het binnen een maand valt moet de startdatum voor de laatste dag van de maand liggen én de einddatum ná de eerste dag van de maand.

Ik zou het oplossen (omdat dit i.m.o. het duidelijkste overzicht geeft) door naast de rijen met cliéntnummer, start en einddatum een tabel te creëren met (genummerd) de maanden. Dan kan je een formule toepassen (deze komt uit Google sheets) waarbij je het volgende 'berekend':
- Als startdatum <= laatstedag van de maand *kolomheader* EN einddatum >= eerste dag van de maand "kolomheader", dan 1 anders 0 (formuie zag er bij mij zo uit)

=IF(AND(EOMONTH(DATE(2023;E$2;1);0) >= $B3;DATE(2023;E$2;1) <= $C3);1;0)

Dan krijg je een tabel met daarin een onder het maandnummer per cliént een 1 als deze in die maand aanwezig was, en anders een 0. Onder die tabel kan je dan weer simpel de totalen bepalen per maand.[Afbeelding]

Je zou dan ook nog in plaats van de kolomnaam = maandnummer, net als @dixet heeft een datum kunnen zetten en met de year() en month() functies daar het jaartal en de maand uit halen en daarmee bovenstaande functie aanpassen zodat je ook andere jaren kan verwerken.

Uiteraard vanaf kolom Q 2023 aanpassen naar 2024.

[jbdeiman]

se77en schreef op woensdag 28 augustus 2024 @ 16:36:
[...]


Lijkt me goed.
Iets soortgelijks.
[Afbeelding]

[Afbeelding]


[...]


Uiteraard vanaf kolom Q 2023 aanpassen naar 2024.

Natuurlijk, daarom zei ik ook al als je maand + jaar (1e dag v.d. maand) erin zou zetten in de header, dan kan je met de year en month (of jaar een maand) functie deze gebruiken.
Dan is het helemaal flexibel gemaakt.

@Brittie Ik zou in je functie (zoals je hem op de afbeelding hebt staan) 2023 vervangen door JAAR(veld) waarbij veld overeen komt (zelfde waarde heeft) als je gebruikt in de MAAND( ) functie. Dan hoef je bij een jaarovergang de formule niet aan te passen en kan je deze gewoon doortrekken naar 2025 straks (wanneer nodig).

[Brittie]

jbdeiman schreef op woensdag 28 augustus 2024 @ 16:46:
[...]

Natuurlijk, daarom zei ik ook al als je maand + jaar (1e dag v.d. maand) erin zou zetten in de header, dan kan je met de year en month (of jaar een maand) functie deze gebruiken.
Dan is het helemaal flexibel gemaakt.

@Brittie Ik zou in je functie (zoals je hem op de afbeelding hebt staan) 2023 vervangen door JAAR(veld) waarbij veld overeen komt (zelfde waarde heeft) als je gebruikt in de MAAND( ) functie. Dan hoef je bij een jaarovergang de formule niet aan te passen en kan je deze gewoon doortrekken naar 2025 straks (wanneer nodig).

Goeie! Heb ik nu gedaan.
Dank allemaal voor alle suggesties! Het is nu opgelost