• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Van minuut naar uurwaardes Deel 2

Status
Niet open voor verdere reacties.
W

Woffels

Gebruiker
Lid geworden
8 jan 2006
Berichten
249
Ik wil van een groot aantal metingen gemiddelde uur waardes maken.
Op tabblad raw staan minuutwaardes gemeten met een online fijnstof sensor die zijn waardes als csv op het web zet. Deze csv (tabblad raw) haal ik binnen met een query van het web, dus die groeit en groeit en wil ik periodiek makkelijk kunnen bijwerken. Zoals je ziet geeft de sensor elke minuut een waarde, maar het kan ook zijn dat de sensor uit staat en/of een tijdje geen waardes geeft of wel eens een minuut over slaat. Dus het staat niet altijd vast dat de sensor elk uur 60 waardes geeft en staat dus soms ook uit.
Op tabblad hr maak ik er uurgemiddeldes van met een formule. Dus ook als er minder waardes zijn dat uur, moet er toch het uurgemiddelde staan van de in dat uur gemeten minuut waardes. Ook als er hele dagen of uren wegvallen moeten de cellen in de c kolom gevuld worden met waarde 0. Je ziet bijvoorbeeld dat er op 15-04-2021 geen metingen zijn geweest (raw-tab) en er waarde nul staat in de cellen van kolom C van blad hr. Dit wil ik zo, om later meerdere sensoren naast elkaar te kunnen leggen en dan moeten ze in synch zijn. In het voorbeeld gaat het nu alleen om 1 waarde (PM2.5), maar er zijn veel meer kolommen waar ik dit op wil toepassen, maar voor het voorbeeld even niet relevant en te groot om hier te uploaden.
Het blijft dus over de tijd groeien. Op zich werkt het wel goed, alleen neemt het laden en berekenen van de nieuwe data behoorlijk wat tijd in beslag, terwijl ik best wel een krachtige PC heb.
Is dit niet op te lossen met een VBA functie en hoe en wordt het dan sneller?

Trouwens, hier zie je de online sensoren ohnics.online/kaart
Wij zijn een collectief/startup die vanuit onze hobby een sensoren project hebben mogen doen in Maastricht, Nijmegen en Lent.
 

Bijlagen

  • Minuut naar uur.xlsx
    1,7 MB · Weergaven: 31
Zet de macro in de macromodule van Blad4:

Code: 
Sub M_snb()
    sn = Blad4.Cells(1).CurrentRegion
    
    y = DateDiff("h", sn(2, 1), sn(UBound(sn), 1))
    With CreateObject("scripting.dictionary")
       For j = 1 To y
          x0 = .Item(Format(sn(2, 1) + (j - 1) / 24, "yyyymmddhh"))
       Next
       
       For j = 2 To UBound(sn)
          If sn(j, 2) > 0 Then .Item(Format(sn(j, 1), "yyyymmddhh")) = (.Item(Format(sn(j, 1), "yyyymmddhh")) + sn(j, 2)) / 2
       Next
       
       Blad4.Cells(1, 6).Resize(, 2) = Array("time", "PM2.5")
       Blad4.Cells(2, 6).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
     End With
End Sub

Op basis van de nieuwe tabel kun je met een draaitabel gaan werken.
 
[OFF TOPIC...]

Snb, méén je dit???
Blad4.Cells(2, 6).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items)
Klik om te vergroten...
Kán je een Dict ook zo in 1x naar de sheet wegschrijven ipv dmv door de Keys en Items heen te moeten lussen??? Wowwww... wéér wat van je geleerd. Tnx!
 
Draaitabel?
 

Bijlagen

  • Minuut naar uur (AC).xlsb
    1,6 MB · Weergaven: 19
@Redhead

Die variant komt van HSV (ook niet onbekend hier)

@JVeer

Index heeft dezefde beperkingen als transpose, want evenmin door MS aangepast aan de Rows.count en columns.count van het xlsx, xlsb en xlsm formaat.
 
Laatst bewerkt:
@snb, bizarre manier om het gemiddelde te berekenen ???
Met Application.index heb ik voorlopig betere ervaringen dan met transpose, dus voorlopig gaat mijn voorkeur nog steeds die kant uit.
Voor de grap zitten er nu 100.000 gegevens in die dictionary
 

Bijlagen

  • Minuut naar uur (AC) (version 1).xlsb
    1.010,2 KB · Weergaven: 18
Deze csv (tabblad raw) haal ik binnen met een query van het web, dus die groeit en groeit en ...
Klik om te vergroten...
Trouwens, hier zie je de online sensoren ohnics.online/kaart
Klik om te vergroten...
toch eerder een job voor de PQ-boys/girls. ?
Die andere parameters kunnen tegelijkertijd dan verwerkt worden.
 
Laatst bewerkt:
@cow18
Om boven de 60000 uit te komen zitten we al in februari 2028.
Ik denk dat die metingen voor die tijd al gestopt zijn.
Dus die excercitie lijkt voor voor het antwoord op de vraag overbodig.
 
Hierbij ook nog een optie met Power Query.
 

Bijlagen

  • Minuut naar uur (AC).xlsx
    1,8 MB · Weergaven: 17
Dan lijkt mij een eenvoudige formule in de tabel (=floor(A2;1/24) + draaitabel toch het simpelst.
 
@AlexCEL, Het lastige zit hem in de ontbrekende data. Deze moeten ook zichtbaar gemaakt worden. Is met een draaitabel overigens eenvoudig te doen door in de Field Settings Show items with no data aan te vinken.
 

Bijlagen

  • Minuut naar uur (AC) (1).xlsb
    1,7 MB · Weergaven: 18
@venA, in bijlage opgelost door gewenste urenoverzicht ook als query in te laden en daarna de twee query's samen te voegen tot een totaaltabel.
 

Bijlagen

  • Minuut naar uur (AC) (2).xlsx
    1,8 MB · Weergaven: 15
@snb,
die 100.000 was eerder een reactie op jouw #6, niet op de initiele vraag van TS, eigenlijk het afzoeken van de limieten van MS.
Misschien ook een beetje duiding van de problematiek aan JVEER.

Met onderstaande snelle aanpassing, de syntax kan beter, los van de vraag van TS, wordt een dictionary van 1.048.000 records, waarvan er ééntje inzit met een string van 32.000 karakters, weggeschreven !
Het macro duurt wel ongeveer 90 sec.

Code: 
Sub M_snb()
   t = Timer
   sn = Blad4.Cells(1).CurrentRegion
   r = ActiveSheet.Rows.Count - 3
   s0 = WorksheetFunction.Rept("x", 32000)
   y = Application.Max(r, 100000, DateDiff("h", sn(2, 1), sn(UBound(sn), 1)))   'TEST !!!! om boven de 64.000 uit te komen
   With CreateObject("scripting.dictionary")
      For j = 1 To y + 1
         s = Format(sn(2, 1) + (j - 1) / 24, "yyyy-mm-dd hh:00")   'aanmaak datum+uur vanaf kleinste tot grootste
         .Item(s) = Array(s, 0, 0, 0)                           'array met 4 elementen, eerste is datum+uur
         If j = y Then .Item(s) = Array(s, s0, 0, 0)            'als 2e grap steek ik er ergens een string met een lengte van 32.000 in !!!!!!!!!
      Next
etc
.
 
Laatst bewerkt:
@snb
Bij jouw eerste oplossing (#2) komt niet het werkelijke gemiddelde in de kolom van PM2.5 te staan.

Time----------PM2.5_snb---Werkelijk_gem
2021041317
2021041318
2021041319 17,25852051 30,36923077
2021041320 6,598837349 7,925
2021041321 6,399997077 6,625
2021041322 3,971781973 5,41
2021041323 3,004956975 3,466666667

Ik zie even niet waar het mis gaat, kan je me op weg helpen?
 
Laatst bewerkt:
Code: 
For j = 2 To UBound(sn)
          If sn(j, 2) > 0 Then .Item(Format(sn(j, 1), "yyyymmddhh")) = (.Item(Format(sn(j, 1), "yyyymmddhh")) + sn(j, 2)) / 2
       Next
stel 60 waarnemingen in dat uur en stel heel stom dat die gewoon een oplopende reeks 1, 2, 3, 4, 5, ..., , 60 is.
Dan doet die loop
1e loop (0+1)/2 = .5
2e loop (.5+2)/2 = 1.25
3e loop (1.25+3)/2 = 2.125
4e loop (2.125+4)/2 = 3,0625
etc
dus een "soort" van voortschrijdend gemiddelde, waarbij de laatste waarden van de reeks eigenlijk teveel gewicht krijgen, dan normaal.
 
Ik wil het gemiddelde van alle waardes over het hele uur, ongeacht hoeveel metingen er gedaan zijn. Achteraf bepaal ik wel of een uurwaarde valide is aan de hand van het aantal metingen.

Code: 
If sn(j, 2) > 0 Then .Item(Format(sn(j, 1), "yyyymmddhh")) = (.Item(Format(sn(j, 1), "yyyymmddhh")) + sn(j, 2)) / 2

Als ik die /2 aan het einde van de regel weghaal, krijg je de som alle metingen in dat uur, dat delen door een tellertje zou het gemiddelde moeten zijn. Ik ben alleen niet zo'n held als het gaat om arrays, dus als iemand me op weg kan helpen.
 
Laatst bewerkt:
Code: 
tellertje = 60                                               '60 metingen per uur ???
   For j = 2 To UBound(sn)
      If sn(j, 2) > 0 Then .Item(Format(sn(j, 1), "yyyymmddhh")) = .Item(Format(sn(j, 1), "yyyymmddhh")) + sn(j, 2) / tellertje
   Next
 
Tellertje moet wel alle metingen tellen en dat zijn er niet altijd 60.
 
@cow18
In jouw code #7 maak je al wel een tellertje, maar laat je met een formule op het blad het gemiddelde bepalen door totaal/tellertje te doen. Ik wil het gemiddelde uit de code laten komen en de uiteindelijke tabel moet dan bestaat uit twee kolommen:
Datum/tijd en een kolom met het berekende gemiddelde.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan