Bekijk de onderstaande video om te zien hoe je onze site als een web app op je startscherm installeert.
Opmerking: Deze functie is mogelijk niet beschikbaar in sommige browsers.
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.
Sub M_snb()
c00 = "yyyymmdd hh.00"
sn = Blad4.Cells(1).CurrentRegion
y = DateDiff("h", sn(2, 1), sn(UBound(sn), 1))
With CreateObject("scripting.dictionary")
For j = 2 To y + 2
c01 = Format(sn(2, 1) + (j - 2) / 24, c00)
.Item(c01) = Array(0, 0)
Next
For j = 2 To UBound(sn)
c01 = Format(sn(j, 1), c00)
sp = .Item(c01)
.Item(c01) = Array(sp(0) + sn(j, 2), sp(1) + 1)
Next
ReDim st(.Count, 1)
st(0, 0) = "Time"
st(0, 1) = "Average"
For Each it In .keys
n = n + 1
sp = .Item(it)
st(n, 0) = it
If sp(0) > 0 And sp(1) > 0 Then st(n, 1) = sp(0) / sp(1)
Next
End With
Blad4.Cells(1, 6).Resize(UBound(st) + 1, 2) = st
End Sub
:thumb:Aangezien jij net als ik niets van VBA begrijp ...
Sub M_snb()
c00 = "yyyy/mm/dd hh:00"
sn = Blad4.Cells(1).CurrentRegion
y = DateDiff("h", sn(2, 1), sn(UBound(sn), 1))
With CreateObject("scripting.dictionary")
For j = 2 To y + 2
c01 = Format(sn(2, 1) + (j - 2) / 24, c00)
.Item(c01) = Array(0, 0, 0, 0, 0, 0)
Next
For j = 2 To UBound(sn)
c01 = Format(sn(j, 1), c00)
sp = .Item(c01)
.Item(c01) = Array(sp(0) + sn(j, 2), sp(1) + 1, sp(2) + sn(j, 3), sp(3) + 1, sp(4) + sn(j, 4), sp(5) + 1)
Next
ReDim st(.Count, 3)
st(0, 0) = "Time"
st(0, 1) = "Average" & sn(1, 2)
st(0, 2) = "Average " & sn(1, 3)
st(0, 3) = "Average " & sn(1, 4)
For Each it In .keys
n = n + 1
sp = .Item(it)
st(n, 0) = it
If sp(1) > 0 Then st(n, 1) = sp(0) / sp(1)
If sp(3) > 0 Then st(n, 2) = sp(0) / sp(3)
If sp(5) > 0 Then st(n, 3) = sp(0) / sp(5)
Next
End With
Blad4.Cells(1, 10).Resize(UBound(st) + 1, UBound(st, 2) + 1) = st
End Sub
If sp(1) > 0 Then st(n, 1) = sp(0) / sp(1)
If sp(3) > 0 Then st(n, 2) = sp(0) / sp(3)
If sp(5) > 0 Then st(n, 3) = sp(0) / sp(5)
If sp(1) > 0 Then st(n, 1) = sp(0) / sp(1)
If sp(3) > 0 Then st(n, 2) = sp(2) / sp(3)
If sp(5) > 0 Then st(n, 3) = sp(4) / sp(5)
Sub M_snb()
c00 = "yyyy-mm-dd hh:00"
sn = Blad4.Cells(1).CurrentRegion
ReDim sq(UBound(sn, 2) - 1)
y = DateDiff("h", sn(2, 1), sn(UBound(sn), 1))
With CreateObject("scripting.dictionary")
For j = 0 To y
.Item(Format(sn(2, 1) + j / 24, c00)) = sq
Next
For j = 2 To UBound(sn)
c01 = Format(sn(j, 1), c00)
sp = .Item(c01)
sp(0) = sp(0) + 1
For jj = 1 To UBound(sp)
sp(jj) = sp(jj) + sn(j, jj + 1)
Next
.Item(c01) = sp
Next
ReDim st(.Count, UBound(sn, 2) - 1)
st(0, 0) = "Time"
For j = 1 To UBound(st, 2)
st(0, j) = "Average " & sn(1, j + 1)
Next
For Each it In .keys
n = n + 1
sp = .Item(it)
st(n, 0) = it
For j = 1 To UBound(st, 2)
If sp(0) > 0 Then st(n, j) = sp(j) / sp(0)
Next
Next
End With
Blad4.Cells(1, UBound(sn, 2) + 3).Resize(UBound(st) + 1, UBound(st, 2) + 1) = st
End Sub
Sub M_snb()
Application.ScreenUpdating = False
c00 = "yyyy-mm-dd hh:00"
sn = Blad4.Cells(1).CurrentRegion
ReDim sq(UBound(sn, 2) - 1)
y = Int(24 * (sn(UBound(sn), 1) - sn(2, 1))) + 1
With CreateObject("scripting.dictionary")
For j = 0 To y
.Item(Format(sn(2, 1) + j / 24, c00)) = sq
Next
For j = 2 To UBound(sn)
c01 = Format(sn(j, 1), c00)
sp = .Item(c01)
sp(0) = sp(0) + 1
For jj = 1 To UBound(sp)
sp(jj) = sp(jj) + sn(j, jj + 1)
Next
.Item(c01) = sp
Next
ReDim st(.Count, UBound(sn, 2) - 1)
st1 = st
st(0, 0) = "Time"
st1(0, 0) = "Datum"
For j = 1 To UBound(st, 2)
st(0, j) = "Average " & sn(1, j + 1)
st1(0, j) = st(0, j)
Next
For Each it In .keys
If n = 0 Then sd = sq
If n > 0 Then
If Left(it, 10) <> Left(st(n, 0), 10) Then
st1(n, 0) = Left(st(n, 0), 10)
For j = 1 To UBound(sd)
If sd(0) > 0 Then st1(n, j) = sd(j) / sd(0)
Next
sd = sq
End If
End If
n = n + 1
sp = .Item(it)
st(n, 0) = it
sd(0) = sd(0) + sp(0)
For j = 1 To UBound(st, 2)
If sp(0) > 0 Then st(n, j) = sp(j) / sp(0)
sd(j) = sd(j) + sp(j)
Next
Next
End With
st1(n, 0) = Left(st(n, 0), 10)
For j = 1 To UBound(sd)
If sd(0) > 0 Then st1(n, j) = sd(j) / sd(0)
Next
With Blad4.Cells(1, UBound(sn, 2) + 3)
.Resize(UBound(st) + 1, UBound(st, 2) + 1) = st
.Offset(, UBound(st, 2) + 1).Resize(UBound(st) + 1, UBound(st, 2) + 1) = st1
.CurrentRegion.Columns(1).NumberFormat = "dd-mm-yyyy hh:mm"
.CurrentRegion.Offset(, 1).NumberFormat = "0.00"
.CurrentRegion.Columns(UBound(st, 2) + 2).NumberFormat = "dd-mm-yyyy"
End With
End Sub
We gebruiken essentiële cookies om deze site te laten werken, en optionele cookies om de ervaring te verbeteren.