Toon posts:

Excel - data opsplitsen vanuit start- en einddatum

Pagina: 1
Acties:

Vraag


  • BaddoLJ
  • Registratie: Juni 2020
  • Laatst online: 22-06-2020
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!

Beste antwoord (via BaddoLJ op 22-06-2020 12:11)


  • Belindo
  • Registratie: December 2012
  • Nu online

Belindo

▶ ─🔘─────── 15:02

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:
1
=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.

Coding in the cold; <brrrrr />

Alle reacties


  • BaddoLJ
  • Registratie: Juni 2020
  • Laatst online: 22-06-2020
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.

  • BaddoLJ
  • Registratie: Juni 2020
  • Laatst online: 22-06-2020
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:
1
=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?

  • BaddoLJ
  • Registratie: Juni 2020
  • Laatst online: 22-06-2020
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 :p)

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!

Acties:
  • +1Henk 'm!

  • BaddoLJ
  • Registratie: Juni 2020
  • Laatst online: 22-06-2020
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:
1
=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!
Pagina: 1


Tweakers maakt gebruik van cookies

Tweakers plaatst functionele en analytische cookies voor het functioneren van de website en het verbeteren van de website-ervaring. Deze cookies zijn noodzakelijk. Om op Tweakers relevantere advertenties te tonen en om ingesloten content van derden te tonen (bijvoorbeeld video's), vragen we je toestemming. Via ingesloten content kunnen derde partijen diensten leveren en verbeteren, bezoekersstatistieken bijhouden, gepersonaliseerde content tonen, gerichte advertenties tonen en gebruikersprofielen opbouwen. Hiervoor worden apparaatgegevens, IP-adres, geolocatie en surfgedrag vastgelegd.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Toestemming beheren

Hieronder kun je per doeleinde of partij toestemming geven of intrekken. Meer informatie vind je in ons cookiebeleid.

Functioneel en analytisch

Deze cookies zijn noodzakelijk voor het functioneren van de website en het verbeteren van de website-ervaring. Klik op het informatie-icoon voor meer informatie. Meer details

janee

    Relevantere advertenties

    Dit beperkt het aantal keer dat dezelfde advertentie getoond wordt (frequency capping) en maakt het mogelijk om binnen Tweakers contextuele advertenties te tonen op basis van pagina's die je hebt bezocht. Meer details

    Tweakers genereert een willekeurige unieke code als identifier. Deze data wordt niet gedeeld met adverteerders of andere derde partijen en je kunt niet buiten Tweakers gevolgd worden. Indien je bent ingelogd, wordt deze identifier gekoppeld aan je account. Indien je niet bent ingelogd, wordt deze identifier gekoppeld aan je sessie die maximaal 4 maanden actief blijft. Je kunt deze toestemming te allen tijde intrekken.

    Ingesloten content van derden

    Deze cookies kunnen door derde partijen geplaatst worden via ingesloten content. Klik op het informatie-icoon voor meer informatie over de verwerkingsdoeleinden. Meer details

    janee