Excel - data opsplitsen vanuit start- en einddatum

[BaddoLJ]

Goedemorgen,

Ik heb een Excel-document waarin ik verschilldende begin- en einddata heb, met hierbij een gemiddelde waarde per dag. Nu wil ik deze gegevens splitsen per dag, zodat aan iedere dag binnen deze tijdsperiodes deze waarde wordt toegekend.

Bijvoorbeeld: een tijdsperiode van 20 januari tot en met 7 februari, met een gemiddelde waarde van 15. Nu moeten alle dagen van 20 jan t/m 7 feb deze waarde toegekend krijgen. Enzovoort met de volgende tijdsperiode.

Wat ik al gevonden of geprobeerd heb

Via een Aantal.Als kan ik 1 tijdsperiode splitsen per datum. Maar een tweede tijdsperiode toevoegen levert een foutmelding op, daarnaast zal ik via dit systeem uiteindelijk 100 formules achter elkaar moeten plakken, en dat lijkt me niet handig.

Alvast bedankt!

[Belindo]

Hoe is je data gestructureerd? Staat de periode in 1 cel (bijvoorbeeld '2020-01-20 - 2020-02-07' of heb je twee kolommen, een voor Start- en een voor Einddatum?

Tevens neem ik aan dat je de waardes per dag in een andere tabel wilt hebben waar je al alle losse dagen hebt? Want je kunt in je brontabel niet automagisch rijen bijbedenken.

Heb je een voorbeeld van hoe je data er nu uitziet? En hoe het moet worden?

[TheFes]

Je kunt datums gewoon van elkaar aftrekken, dus bijvoorbeeld 2-1-2020 - 1-1-2020 geeft 1
Als je wil dat dat dat als 2 dagen telt, moet je dan dus nog 1 dag er bij optellen

Vervolgens kun je je gemiddelde waarde (15 in jouw voorbeeld) delen door het resultaat van bovenstaande berekening.

Dus stel, je gemiddelde waarde staat in A1, je begindatum in A2 en je einddatum in A3 dan wordt je berekening

code:

=(A3-A2+1)/A1

[BaddoLJ]

Belindo schreef op maandag 22 juni 2020 @ 10:45:
Hoe is je data gestructureerd? Staat de periode in 1 cel (bijvoorbeeld '2020-01-20 - 2020-02-07' of heb je twee kolommen, een voor Start- en een voor Einddatum?

Tevens neem ik aan dat je de waardes per dag in een andere tabel wilt hebben waar je al alle losse dagen hebt? Want je kunt in je brontabel niet automagisch rijen bijbedenken.

Heb je een voorbeeld van hoe je data er nu uitziet? En hoe het moet worden?

Ik heb de begin- en einddatum in 2 aparte kolommen staan. In een tweede tabblad heb ik alle datums van dit jaar staan, met hierachter (wat moet worden) alle waardes die op deze dag van toepassing zijn.

[Belindo]

Oh, dan ben je er al bijna. Je hebt dus een kolom met de begin datum, een kolom met de einddatum en de waarde. Vervolgens heb je een lijst met alle losse datums, waar je nu alleen nog de waarde voor moet hebben.

In dat geval kun je een INDEX() en MATCH() formule gebruiken met de [match_type] 1, ofwel 'less than'. Je zoekt dan in je kolom met Start Datum op welke rij match met de losse datum, en retourneert dan de waarde.

code:

=INDEX(G:G,MATCH(B4,E:E,1))

Waar G:G je kolom met de waarde is, B4 je losse datum is en E:E je kolom met Start Datum is.

Plaatje:

Dit voorbeeld retourneert voor 01-01 t/m 08-01 de waarde 68.92 omdat deze datum binnen de range van rij 3 vallen.

[ Voor 6% gewijzigd door Belindo op 22-06-2020 11:00 ]

[BaddoLJ]

Belindo schreef op maandag 22 juni 2020 @ 10:59:
Oh, dan ben je er al bijna. Je hebt dus een kolom met de begin datum, een kolom met de einddatum en de waarde. Vervolgens heb je een lijst met alle losse datums, waar je nu alleen nog de waarde voor moet hebben.

In dat geval kun je een INDEX() en MATCH() formule gebruiken met de [match_type] 1, ofwel 'less than'. Je zoekt dan in je kolom met Start Datum op welke rij match met de losse datum, en retourneert dan de waarde.

code:

=INDEX(G:G,MATCH(B4,E:E,1))

Waar G:G je kolom met de waarde is, B4 je losse datum is en E:E je kolom met Start Datum is.

Plaatje:
[Afbeelding]
Dit voorbeeld retourneert voor 01-01 t/m 08-01 de waarde 68.92 omdat deze datum binnen de range van rij 3 vallen.

Wow thanks! Nu ben ik er bijna, het enige probleem is dat ik ook te maken heb met overlappende tijdsperiodes. Bijvoorbeeld 01-01 t/m 08-01 (Waarde: 33) en 06-01 t/m 11-01 (Waarde: 25). Dan moet er op de data 06-01 t/m 08-01 de waarde 58 gegeven worden, gezien de overlap.

Hoe krijg ik dat voor elkaar?

[Belindo]

Daar ga ik zo even mee aan de slag als mijn Excel klaar is met wat ie nu aan het doen is (ben aan het werk )

De INDEX MATCH combinatie retourneert simpelweg de eerste waarde die voldoet aan de criteria, wellicht is het met een SUMIFS op te lossen, maar dat ga ik zo even proberen.

[BaddoLJ]

Belindo schreef op maandag 22 juni 2020 @ 11:13:
Daar ga ik zo even mee aan de slag als mijn Excel klaar is met wat ie nu aan het doen is (ben aan het werk )

De INDEX MATCH combinatie retourneert simpelweg de eerste waarde die voldoet aan de criteria, wellicht is het met een SUMIFS op te lossen, maar dat ga ik zo even proberen.

Haha top, alvast bedankt!

[Belindo]

Yup, met SUMIFS lukt dat. Je doet dan een SUM op de waarde, waar de datum binnen de range valt, in geval van een overlappende range telt ie de boel bij elkaar op.

code:

=SUMIFS(H:H,F:F,"<=" &B2,G:G,">="&B2)

waar H:H je Waarde is, F:F je startdatum, G:G je einddatum en B2 de datum die je wilt opzoeken.

Wederom plaatje:

Je ziet dat hij voor 5 t/m 7 januari 61.95 retourneert, omdat deze datums in zowel de range 01-01/0701 als 05-01/11-01 vallen en daarom 19.67 en 42.28 bij elkaar worden opgeteld.

[BaddoLJ]

Belindo schreef op maandag 22 juni 2020 @ 11:52:
Yup, met SUMIFS lukt dat. Je doet dan een SUM op de waarde, waar de datum binnen de range valt, in geval van een overlappende range telt ie de boel bij elkaar op.

code:

=SUMIFS(H:H,F:F,"<=" &B2,G:G,">="&B2)

waar H:H je Waarde is, F:F je startdatum, G:G je einddatum en B2 de datum die je wilt opzoeken.

Wederom plaatje:
[Afbeelding]
Je ziet dat hij voor 5 t/m 7 januari 61.95 retourneert, omdat deze datums in zowel de range 01-01/0701 als 05-01/11-01 vallen en daarom 19.67 en 42.28 bij elkaar worden opgeteld.

Woooo yes helemaal super. Dit is perfect en precies wat ik nodig heb. Enorm bedankt!