Cookies op Tweakers

Tweakers is onderdeel van DPG Media en maakt gebruik van cookies, JavaScript en vergelijkbare technologie om je onder andere een optimale gebruikerservaring te bieden. Ook kan Tweakers hierdoor het gedrag van bezoekers vastleggen en analyseren. Door gebruik te maken van deze website, of door op 'Cookies accepteren' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt? Bekijk dan ons cookiebeleid.

Meer informatie
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
  • Laatst online: 14:46

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:
https://tweakers.net/i/SwmBSBFUGExNSir_TpLBJ50q3M0=/full-fit-in/4920x3264/filters:max_bytes(3145728):no_upscale():strip_icc():fill(white):strip_exif()/f/image/gfru547aNeYGFgiQMNdI0aV3.jpg?f=user_large
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


  • Belindo
  • Registratie: december 2012
  • Laatst online: 14:46

Belindo

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

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?

Coding in the cold; <brrrrr />


  • TheFes
  • Registratie: juni 2001
  • Laatst online: 07:39
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:
1
=(A3-A2+1)/A1

  • 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.

Acties:
  • +1Henk 'm!

  • Belindo
  • Registratie: december 2012
  • Laatst online: 14:46

Belindo

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

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:

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]

Coding in the cold; <brrrrr />


  • 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?

  • Belindo
  • Registratie: december 2012
  • Laatst online: 14:46

Belindo

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

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.

Coding in the cold; <brrrrr />


  • 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:
  • Beste antwoord
  • +1Henk 'm!

  • Belindo
  • Registratie: december 2012
  • Laatst online: 14:46

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 />


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


Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Microsoft Xbox Series X LG CX Google Pixel 5a 5G Sony XH90 / XH92 Samsung Galaxy S21 5G Sony PlayStation 5 Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2021 Hosting door True