Helpmij.nl
Helpmij.nl
Helpmij.nl

Quote

Pagina 1 van 2 1 2 LaatsteLaatste
Weergeven resultaten 1 tot 20 van 35

Onderwerp: Van minuut naar uurwaardes Deel 2

  1. #1
    Senior Member
    Geregistreerd
    8 januari 2006
    Vraag is niet opgelost

    Van minuut naar uurwaardes Deel 2

    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.
    Bijgevoegde bestanden Bijgevoegde bestanden

  2. #2
    Giga Honourable Senior Member
    Verenigingslid
    snb's avatar
    Geregistreerd
    12 juni 2008
    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.
    VBA voor smarties
    VBA is een taal die je moet leren met een grammatica- en een woordenboek.

    http://www.helpmij.nl/forum/announcement.php?f=5
    Plaats svp geen bestanden op andere sites; nadat het bestand daar verwijderd is wordt een forumdraad onbegrijpelijk voor anderen.

  3. #3
    Giga Senior Ginger's avatar
    Geregistreerd
    29 december 2006
    Locatie
    Delft
    [OFF TOPIC...]

    Snb, méén je dit???
    Blad4.Cells(2, 6).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items)
    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!
    ____________________________
    GINGER AlsoKnownAs REDHEAD
    + + + + + + + + + + + + + + + + + + + + + + + + + +
    Test een nieuwe VBA-code altijd éérst uit op een
    kopie van het bestand ipv direct in het orgineel...!

    + + + + + + + + + + + + + + + + + + + + + + + + + +

  4. #4
    Mega Honourable Senior Member AlexCEL's avatar
    Geregistreerd
    3 april 2014
    Locatie
    Groningen
    Draaitabel?
    Bijgevoegde bestanden Bijgevoegde bestanden
    -- een (excel) voorbeeldbestandje zegt meer dan 1000 woorden --

  5. #5
    Giga Senior JVeer's avatar
    Geregistreerd
    27 februari 2019
    @Redhead, in de laatste bijlage van Cow in onderstaand draadje staat ook nog een manier met application.index(en ook met transpose)😁. Index werkt met heel veel data beter dan transpose

    https://www.helpmij.nl/forum/showthr...den-in-kolom-A

  6. #6
    Giga Honourable Senior Member
    Verenigingslid
    snb's avatar
    Geregistreerd
    12 juni 2008
    @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 aangepast door snb : 21 juli 2021 om 10:12
    VBA voor smarties
    VBA is een taal die je moet leren met een grammatica- en een woordenboek.

    http://www.helpmij.nl/forum/announcement.php?f=5
    Plaats svp geen bestanden op andere sites; nadat het bestand daar verwijderd is wordt een forumdraad onbegrijpelijk voor anderen.

  7. #7
    Giga Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    @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
    Bijgevoegde bestanden Bijgevoegde bestanden

  8. #8
    Giga Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    Deze csv (tabblad raw) haal ik binnen met een query van het web, dus die groeit en groeit en ...
    Trouwens, hier zie je de online sensoren ohnics.online/kaart
    toch eerder een job voor de PQ-boys/girls. ?
    Die andere parameters kunnen tegelijkertijd dan verwerkt worden.
    Laatst aangepast door cow18 : 21 juli 2021 om 08:50

  9. #9
    Giga Honourable Senior Member
    Verenigingslid
    snb's avatar
    Geregistreerd
    12 juni 2008
    @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.
    VBA voor smarties
    VBA is een taal die je moet leren met een grammatica- en een woordenboek.

    http://www.helpmij.nl/forum/announcement.php?f=5
    Plaats svp geen bestanden op andere sites; nadat het bestand daar verwijderd is wordt een forumdraad onbegrijpelijk voor anderen.

  10. #10
    Mega Honourable Senior Member AlexCEL's avatar
    Geregistreerd
    3 april 2014
    Locatie
    Groningen
    Hierbij ook nog een optie met Power Query.
    Bijgevoegde bestanden Bijgevoegde bestanden
    -- een (excel) voorbeeldbestandje zegt meer dan 1000 woorden --

  11. #11
    Giga Honourable Senior Member
    Verenigingslid
    snb's avatar
    Geregistreerd
    12 juni 2008
    Dan lijkt mij een eenvoudige formule in de tabel (=floor(A2;1/24) + draaitabel toch het simpelst.
    VBA voor smarties
    VBA is een taal die je moet leren met een grammatica- en een woordenboek.

    http://www.helpmij.nl/forum/announcement.php?f=5
    Plaats svp geen bestanden op andere sites; nadat het bestand daar verwijderd is wordt een forumdraad onbegrijpelijk voor anderen.

  12. #12
    Giga Honourable Senior Member
    Geregistreerd
    2 maart 2013
    @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.
    Bijgevoegde bestanden Bijgevoegde bestanden
    Je kan een paard naar het water leiden, maar je kan het niet dwingen te drinken.

  13. #13
    Mega Honourable Senior Member AlexCEL's avatar
    Geregistreerd
    3 april 2014
    Locatie
    Groningen
    @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.
    Bijgevoegde bestanden Bijgevoegde bestanden
    -- een (excel) voorbeeldbestandje zegt meer dan 1000 woorden --

  14. #14
    Giga Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    @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 aangepast door cow18 : 21 juli 2021 om 11:09

  15. #15
    Senior Member
    Geregistreerd
    8 januari 2006
    @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 aangepast door Woffels : 21 juli 2021 om 15:16

  16. #16
    Giga Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    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.

  17. #17
    Senior Member
    Geregistreerd
    8 januari 2006
    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 aangepast door Woffels : 21 juli 2021 om 16:23

  18. #18
    Giga Senior cow18's avatar
    Geregistreerd
    24 mei 2008
    Locatie
    Alveringem, België
    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

  19. #19
    Senior Member
    Geregistreerd
    8 januari 2006
    Tellertje moet wel alle metingen tellen en dat zijn er niet altijd 60.

  20. #20
    Senior Member
    Geregistreerd
    8 januari 2006
    @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.

Berichtenregels

  • U mag geen nieuwe vragen starten.
  • U mag niet reageren op berichten.
  • U mag geen bijlagen versturen.
  • U mag uw berichten niet bewerken.
  •  
Helpmij.nl
Helpmij.nl

Helpmij.nl

Regels
Help

Helpmij.nl en business

Partners
Sponsoren