Anzeige
Archiv - Navigation
1276to1280
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Funktion Zahlen rechnen Zeitbaschnitt

Funktion Zahlen rechnen Zeitbaschnitt
Michael
Hallo Experten,
Ich brauche zu folgendem Problem Hilfe. Wie im Excel dargestellt:
https://www.herber.de/bbs/user/81584.xlsm
habe ich eine Messung die über zwei Tage und verschiedenen Uhrzeiten geht (In der realität sin es mehr Messdaten). Was nun das Ziel ist eine Berechnung der wie unten angezeigten Std. Abweichung für verschiedene Tage und Zeitpunkte zu Berechnen. Dabei sollen pro Tag die Std. Abw. in verschiedenen Zeitintervallen berechnet werden, z.B.: Std. Abw. 8:00-9:00, 9:00-10:00, 10:00-11:00, ... Das soll auch für verschiedene Tage gemacht werden. Am Ende wird der Mittelwert über alle Messungen durchgeführt. Ein Beispiel ist im angehängten Excel gezeigt. Dabei zeigt die unten angehängte Version einen ersten Schritt der nun mit den Zeitpunkten erweitert werden soll.
Danke für die Hilfe.
Viele Grüße,
Michael
Option Explicit
Private objX() As Variant, objY() As Variant, lngCount As Long Function StdDev(strSheet As String) As Double Dim dblAverage As Double Dim dblSumStdDev As Double Dim GetLastValue As Double Dim varElement As Variant Dim wsFnc As WorksheetFunction Call prcDatenObjekt_erzeugen(strSheet:=strSheet) GetLastValue = objY(UBound(objY)) For varElement = 1 To lngCount - 1 dblAverage = dblAverage + (objY(varElement + 1) - objY(varElement)) / objY(varElement) Next dblAverage = dblAverage / (lngCount - 1) For varElement = 1 To lngCount - 1 dblSumStdDev = dblSumStdDev + ((objY(varElement + 1) - objY(varElement)) / objY( _ varElement) - dblAverage) ^ 2 Next dblSumStdDev = (dblSumStdDev / (lngCount - 2)) ^ 0.5 dblSumStdDev = (GetLastValue / 100) * (dblSumStdDev * 100) StdDev = dblSumStdDev Erase objX, objY End Function
Sub prcDatenObjekt_erzeugen(ByVal strSheet As String)
Dim arrX, arrY, lngX As Long
With Sheets(strSheet)
arrX = .Cells(5, 1).Resize(Application.WorksheetFunction.Count(.Range(.Cells(5, 1), _
.Cells(Rows.Count, 1).End(xlUp))))
arrY = .Cells(5, 1).Resize(Application.Count(.Range(.Cells(5, 1), _
.Cells(Rows.Count, 1).End(xlUp)))).Offset(, 1)
End With
lngCount = 0
For lngX = LBound(arrX) To UBound(arrX)
lngCount = lngCount + 1
ReDim Preserve objX(0 To lngCount)
ReDim Preserve objY(0 To lngCount)
objX(lngCount) = arrX(lngX, 1) * 1
objY(lngCount) = arrY(lngX, 1) * 1
Next lngX
End Sub

AW: Funktion Zahlen rechnen Zeitbaschnitt
26.08.2012 16:55:00
Dieter
Hallo Michael,
wenn ich deine Anforderung richtig verstanden habe, dann kannst du das mit dem folgenden VBA-Programm machen:
Sub Standardabweichung_für_Intervalle()
Dim anfIntervall As Double
Dim anfTag As Long
Dim anfZeile As Long
Dim rngBereich As Range
Dim endIntervall As Double
Dim endTag As Long
Dim endZeile As Long
Dim letzteZeile As Long
Dim stunde As Long
Dim tag As Long
Dim ws As Worksheet
Dim z As Long
Dim zeile As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
ws.Range("G:I").ClearContents
ws.Range("G1") = "Tag"
ws.Columns("G").NumberFormat = "dd.mm.yyyy"
ws.Range("H1") = "Intervall"
ws.Range("I1") = "Std.Abw."
ws.Columns("I").NumberFormat = "0.0000000"
letzteZeile = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If letzteZeile  tag Or ws.Cells(z, "B") >= endIntervall Then
endZeile = z - 1
Exit For
End If
Next z
Set rngBereich = ws.Range(ws.Cells(anfZeile, "D"), ws.Cells(endZeile, "D"))
ws.Cells(zeile, "I") = WorksheetFunction.StDev(rngBereich)
zeile = zeile + 1
Next stunde
Next tag
End Sub
Viele Grüße
Dieter

Anzeige
AW: Funktion Zahlen rechnen Zeitbaschnitt
26.08.2012 17:55:49
Michael
Hallo Dieter,
Danke für deine Antwort. Da ich erst von der relativen Abweichung die Std. Abweichung berechne würde ich gerne meinen Ansatz verwenden. Ist es möglich deinen Ansatz in meine Funktion einzubauen? Ich würde mir auch gerne die Flexibilität behalten das ich weiter immer das Datenblatt angeben kann wo er es berechnen soll.
Danke für deine Hilfe.
Viele Grüße,
Michael

AW: Funktion Zahlen rechnen Zeitbaschnitt
27.08.2012 14:48:05
Dieter
Hallo Michael,
als benutzerdefinierte Funktion könnte das so aussehen:
Function Stdabw_Intervall(tag As Long, anfIntervall As Double, endIntervall As Double) As  _
Double
Dim anfZeile As Long
Dim rngBereich As Range
Dim endZeile As Long
Dim letzteZeile As Long
Dim ws As Worksheet
Dim zeile As Long
Set ws = Application.Caller.Parent
letzteZeile = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If letzteZeile = anfIntervall Then
anfZeile = zeile
Exit For
End If
End If
Next zeile
For zeile = anfZeile To letzteZeile
If ws.Cells(zeile, "A")  tag Or ws.Cells(zeile, "B") >= endIntervall Then
endZeile = zeile - 1
Exit For
End If
Next zeile
If endZeile = 0 Then endZeile = letzteZeile
Set rngBereich = ws.Range(ws.Cells(anfZeile, "D"), ws.Cells(endZeile, "D"))
Stdabw_Intervall = WorksheetFunction.StDev(rngBereich)
End Function
Im Tabellenblatt rufst du das folgendermaßen auf (Tagesdatum in G2, Uhrzeit des Intervallanfangs in H2, Uhrzeit des Intervallendes in I2):
=Stdabw_Intervall(G2;H2;I2)
Viele Grüße
Dieter

Anzeige
AW: Funktion Zahlen rechnen Zeitbaschnitt
30.08.2012 16:47:52
Michael
Hallo Dieter,
Danke für deine Antwort. Leider habe ich diese übersehen :). Es hilft mir noch nicht ganz da ich egentlich nur das Arbeitsblatt angeben möchte und keine Bereiche mehr. Ich habe mal im Excel meinen Lösungsweg angezeigt:
https://www.herber.de/bbs/user/81648.xlsm
Und würde gerne das so in dieser Funktion eingebaut haben:
Option Explicit
Private objX() As Variant, objY() As Variant, lngCount As Long
Function StdDev(strSheet As String) As Double
Dim dblAverage As Double
Dim dblSumStdDev As Double
Dim GetLastValue As Double
Dim varElement As Variant
Dim wsFnc As WorksheetFunction
Call prcDatenObjekt_erzeugen(strSheet:=strSheet)
GetLastValue = objY(UBound(objY))
For varElement = 1 To lngCount - 1
dblAverage = dblAverage + (objY(varElement + 1) - objY(varElement)) / objY(varElement)
Next
dblAverage = dblAverage / (lngCount - 1)
For varElement = 1 To lngCount - 1
dblSumStdDev = dblSumStdDev + ((objY(varElement + 1) - objY(varElement)) / objY( _
varElement) - dblAverage) ^ 2
Next
dblSumStdDev = (dblSumStdDev / (lngCount - 2)) ^ 0.5
dblSumStdDev = (GetLastValue / 100) * (dblSumStdDev * 100)
StdDev = dblSumStdDev
Erase objX, objY
End Function
Sub prcDatenObjekt_erzeugen(ByVal strSheet As String)
Dim arrX, arrY, lngX As Long
With Sheets(strSheet)
arrX = .Cells(2, 1).Resize(Application.WorksheetFunction.Count(.Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))))
arrY = .Cells(2, 1).Resize(Application.Count(.Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp)))).Offset(, 3)
End With
lngCount = 0
For lngX = LBound(arrX) To UBound(arrX)
lngCount = lngCount + 1
ReDim Preserve objX(0 To lngCount)
ReDim Preserve objY(0 To lngCount)
objX(lngCount) = arrX(lngX, 1) * 1
objY(lngCount) = arrY(lngX, 1) * 1
Next lngX
End Sub
Meinst du das ist möglich?
Danke.
VG,
Michael

Anzeige
AW: Funktion Zahlen rechnen Zeitbaschnitt
01.09.2012 15:58:44
Dieter
Hallo Michael,
jetzt ist mir überhaupt nicht mehr klar, was du eigentlich willst.
Ich hatte gedacht, dass du eine benutzerdefinierte Funktion haben wolltest, die zu einem Zeitintervall die Standardabweichung der in dieses Intervall fallenden Werte berechnet. Dazu musst du natürlich die Grenzen des Zeitintervalls übergeben, es sei denn diese Grenzen stehen immer in fester relativer Position zu derjenigen Zelle, von der aus die Funktion aufgerufen wird.
Viele Grüße
Dieter

AW: Funktion Zahlen rechnen Zeitbaschnitt
02.09.2012 00:02:31
Michael
Hallo Dieter,
ja das möchte ich auch. Aber zuerst berechne ich die relative Änderung. Dann möchte ich für jede Stunde die Std. Abweichung berechnen und dann den Mittelwert aller Std. Abweichungen. Da ich schon Angefangen hatte eine Fkt. zu schreiben (siehe Fkt in meiner vorherigen Mail) würde ich gerne deinen Input in diese einbauen. Also was ich am Ende haben möchte ist der Mittelwert aller Std. Abweichungen (wie gesgat die für jede Std berechnet wurden). Hoffe es ist etwas klarer?
VG,
Michael

Anzeige
AW: Funktion Zahlen rechnen Zeitbaschnitt
03.09.2012 09:31:32
Dieter
Hallo Michael,
eine benutzerdefinierte Funktion kann nur Werte in diejenigen Zellen zurückgeben, von denen aus sie aufgerufen wurde.
In deinem Fall würde die Funktion dann nur den Mittelwert der stündlichen Standardabweichungen zurückgeben. Bei dem Datenmaterial der Datei 81648.xls ist das der Mittelwert aus 12 stündlichen Standardabweichungen.
Die zugehörige benutzerdefinierte Funktion könnte so aussehen:
Function Gesamt_Mittelwert() As Variant
Dim aktStunde As Long
Dim aktDatum As Date
Dim anfZeile As Long
Dim anz As Long
Dim mw As Double
Dim rngBereich As Range
Dim endZeile As Long
Dim letzteZeile As Long
Dim ws As Worksheet
Dim zeile As Long
Set ws = Application.Caller.Parent
aktDatum = ws.Range("A2")
aktStunde = Hour(ws.Range("B2"))
anfZeile = 3
letzteZeile = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If letzteZeile  aktDatum Or _
Hour(ws.Cells(zeile, "B")) > aktStunde Or _
zeile = letzteZeile Then
' Neuer Stundenabschnitt
If zeile = letzteZeile Then
endZeile = zeile
Else
endZeile = zeile - 1
End If
Set rngBereich = ws.Range(ws.Cells(anfZeile, "D"), ws.Cells(endZeile, "D"))
mw = mw + WorksheetFunction.StDev(rngBereich)
anz = anz + 1
anfZeile = zeile
aktDatum = ws.Cells(anfZeile, "A")
aktStunde = Hour(ws.Cells(anfZeile, "B"))
End If
Next zeile
If anz > 0 Then
Gesamt_Mittelwert = mw / anz
Else
Gesamt_Mittelwert = CVErr(xlErrValue)
End If
End Function
Du rufst sie in einer Zelle folgendermaßen auf:
=Gesamt_Mittelwert()
Viele Grüße
Dieter

Anzeige
AW: Funktion Zahlen rechnen Zeitbaschnitt
04.09.2012 17:21:36
Michael
Hallo Dieter,
Danke für die neue Fkt.! Was ich möchte
Ist jeden Schritt ab der relativen differenz
Berechnen also ab A4 in der Fkt berechnen.
Ist das moeglich?
Vg
Michael

AW: Funktion Zahlen rechnen Zeitbaschnitt
04.09.2012 18:25:00
Dieter
Hallo Michael,
es tut mir leid, aber meine Möglichkeiten sind ausgeschöpft.
Ggf. musst du eine neue Anfrage stellen.
Viele Grüße
Dieter

AW: Funktion Zahlen rechnen Zeitbaschnitt
26.08.2012 18:57:49
Josef

Hallo Michael,
nicht böse sein, aber bei dir fällt mir wieder ein Zitat von Ramses ein.
Zitat: Ihr wollt auf den Mond fliegen, dabei habt ihr noch nicht mal richtig laufen gelernt!
Ich hab dir in einem früheren Beitrag schon mal erklärt, wie man eine UDF einsetzt, man übergibt ALLE Parameter direkt der Funktion, dann brauche ich keine Sub die ich wiederum aus der Funktion aus aufrufen muss und man hat auch keine Probleme bei der Berechnung.
Und auch habe ich dir schon einmal geschrieben, dass diese Berechnungen ganz einfach mit Excel-Standardfunktionen zu lösen sind.
Sheet1

 ABCDEFGHIJK
1DateTimeMessungDiff.Std. Abw.Mittelwert DatumStundeStd. Abw. 
223.08.201208:00:081530,7185 8:00-9:000,0001864 23.08.201280,000203048 
323.08.201208:01:061531,08490,0002390,000203048  23.08.201290,000216133 
423.08.201208:02:081530,23-0,00056   23.08.2012100,000158140 
523.08.201208:03:081530,2309:00-10:00  23.08.2012110,000140018 
623.08.201208:04:091530,47430,000160,000216133  23.08.2012120,000124315 
723.08.201208:05:081530,23-0,00016   24.08.201280,004883901 
823.08.201208:06:091530,23010:00-11:00  24.08.201290,000216133 
923.08.201208:07:061530,2300,000140018  24.08.2012100,000158140 
1023.08.201208:08:081530,230   24.08.2012110,000140018 
1123.08.201208:09:061529,9858-0,00016   24.08.2012120,000124315 
1223.08.201208:10:081530,230,00016       
1323.08.201208:11:061530,230       
1423.08.201208:12:081530,35217,98E-05       

Formeln der Tabelle
ZelleFormel
J2{=STABW.S(WENN(($A$3:$A$655=H2)*(STUNDE($B$3:$B$655)=I2); $D$3:$D$655))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

« Gruß Sepp »

Anzeige
AW: Funktion Zahlen rechnen Zeitbaschnitt
26.08.2012 19:51:57
Michael
Hallo Sepp,
Danke für deine Antwort. Das Problem ist das ich es gerne in einer UDF verpackt hätte. Das was ich geschickt hatte war nur ein kleiner Ausschnitt. Aber um das laufen zu lernen muss man in VBA einfach mal etwas Hilfe bekommen ;). Deshalb wäre ich sehr froh wenn du mir dabei helfen könntest.
VG
Michael

AW: Funktion Zahlen rechnen Zeitbaschnitt
26.08.2012 20:17:36
Josef

Hallo Michael,
ich helfe gerne, aber du solltest zuerst beschreiben, was du genau berechnen willst.
"Das Problem ist das ich es gerne in einer UDF verpackt hätte"
Du bist dir aber bewusst, dass UDF's meist weniger Performant sind als Standardfunktionen.

« Gruß Sepp »

Anzeige
AW: Funktion Zahlen rechnen Zeitbaschnitt
26.08.2012 20:22:38
Michael
Hallo Sepp,
danke für die schnelle Antwort. Ich möchte im Prinzip genau das machen was du gezeigt hast. Einmal die relative Abweichung berechnen und dann pro Std die Std. Ab der relativen Abweichung berechnen. Am Ende dann den Mittelwert aller Std. Abweichungen. Hoffe das es etwas klarer ist? Nochmals meine Zahlentabelle ist sehr viel größer als hier illustriert. Mit ist bewusst das die UDF nicht so performant sind :).
Danke nochmals.
Viele Grüße
Michael

AW: Funktion Zahlen rechnen Zeitbaschnitt
26.08.2012 21:35:31
Josef

Hallo Michael,
sorry, dazu fehlt mir im Moment die richtige Idee, außerdem finde ich es sinnlos, eine Funktion zu schreiben für ein schon gelöstes problem.

« Gruß Sepp »

Anzeige
AW: Funktion Zahlen rechnen Zeitbaschnitt
26.08.2012 22:49:01
Michael
Hallo Sepp,
Schade das du so denkst. Ich finde nicht das das Problem gelost ist da ich es in VBA gelöst haben wollte.
VG
Michael

AW: Funktion Zahlen rechnen Zeitbaschnitt
27.08.2012 18:37:57
Josef

Hallo Micheal,
wenn du dein problem gelöst haben willst, dann solltest du mal mit einfachen Worten beschreiben, was du willst!
Aus deiner Funktion werde ich nämlich nicht schlau! In dieser Funktion wird die Spalte B, also die Uhrzeit ab Zeile 5 ausgewertet.
Ist das so gewollt?
Warum ab Zeile 5 und nicht ab Zeile 2, wo die Daten beginnen?
Sollten nicht eigentlich die Messwerte in Spalte C verarbeitet werden?
So lange ich darauf keine plausiblen Antworten erhalte, kann ich schlecht eine Lösung erstellen, weil ich immer noch nicht weiß, was du erreichen willst.

« Gruß Sepp »

Anzeige
AW: Funktion Zahlen rechnen Zeitbaschnitt
27.08.2012 22:03:07
Michael
Hallo Josef,
ok ich versuche mal es einfacher zu erklären. Meine Fuktion erzeugt ein Objekt das alle daten hat. dieses objekt wird dann in der funktion StdDev aufgerufen und berechnet an den vorgegebenen Werten die neu definierte std. abweichung. Wie du richtig erkannt hast sollte die Funktion so aussehen:
Function StdDev(strSheet As String) As Double
Dim dblAverage As Double
Dim dblSumStdDev As Double
Dim GetLastValue As Double
Dim varElement As Variant
Dim wsFnc As WorksheetFunction
Call prcDatenObjekt_erzeugen(strSheet:=strSheet)
GetLastValue = objY(UBound(objY))
For varElement = 1 To lngCount - 1
dblAverage = dblAverage + (objY(varElement + 1) - objY(varElement)) / objY(varElement)
Next
dblAverage = dblAverage / (lngCount - 1)
For varElement = 1 To lngCount - 1
dblSumStdDev = dblSumStdDev + ((objY(varElement + 1) - objY(varElement)) / objY( _
varElement) - dblAverage) ^ 2
Next
dblSumStdDev = (dblSumStdDev / (lngCount - 2)) ^ 0.5
dblSumStdDev = (GetLastValue / 100) * (dblSumStdDev * 100)
StdDev = dblSumStdDev
Erase objX, objY
End Function
Sub prcDatenObjekt_erzeugen(ByVal strSheet As String)
Dim arrX, arrY, lngX As Long
With Sheets(strSheet)
arrX = .Cells(2, 2).Resize(Application.WorksheetFunction.Count(.Range(.Cells(2, 2), _
.Cells(Rows.Count, 1).End(xlUp))))
arrY = .Cells(2, 2).Resize(Application.Count(.Range(.Cells(2, 2), _
.Cells(Rows.Count, 1).End(xlUp)))).Offset(, 1)
End With
lngCount = 0
For lngX = LBound(arrX) To UBound(arrX)
lngCount = lngCount + 1
ReDim Preserve objX(0 To lngCount)
ReDim Preserve objY(0 To lngCount)
objX(lngCount) = arrX(lngX, 1) * 1
objY(lngCount) = arrY(lngX, 1) * 1
Next lngX
End Sub
Ich möchte nun in die Fkt StdDev deine Lösung implementieren wie du sie in deine Antwort mit Ramses Zitat in Excel dargestellt hast. Also pro Std jedes Tages die Std. Abweichung der relativen Abweichung berechnen. Die Berechnung der Std. Abweichung der relativen Änderung ist bereits in meiner Fkt drinne. Das einzige ist das ich pro Std für jeden Tag die Std Abweichung bestimmen. Wenn alle Std. Abweichungen bestimmt sind, soll der Mittelwert da drüber bestimmt werden. Ich hoffe es ist nun klarer?
Danke nochmals für deine Hilfe.
VG
Michael

AW: Funktion Zahlen rechnen Zeitbaschnitt
27.08.2012 22:48:52
Josef

Hallo Michael,
und was wäre das erwartete Ergebnis für deine Tabelle?

« Gruß Sepp »

AW: Funktion Zahlen rechnen Zeitbaschnitt
27.08.2012 23:13:48
Michael
Hallo Josef,
Danke für die schnelle Antwort. Anbei ein Excel wie das alles aussehen soll.
https://www.herber.de/bbs/user/81611.xlsm
Hoffe das beantwortet deine Frage?
VG
Michael

AW: Funktion Zahlen rechnen Zeitbaschnitt
27.08.2012 23:58:30
Josef

Hallo Michael,
sorry, keine weitere Ideen.

« Gruß Sepp »

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige