automatisierte Mittelwertberechnung inkl Bildung von Zahlenblöcken und Zwischenergebnissen

[excel-looser]

automatisierte Mittelwertberechnung inkl Bildung von Zahlenblöcken und Zwischenergebnissen

Hallo Excel-Profi`s und Enthusiasten!
Ich würde mich über Hilfestellung bei folgendem Problem freuen (es wird etwas komplexer).

Ich habe eine Datei mit u.a. 3 Spalten. Jede Spalte enthält viele Tausend Werte eines Sensors. Die Werte der Sensoren wurden zeitgleich gemessen, dh die Werte einer Zeile entsprechen dem gleichen Zeitpunkt.

Ich möchte: :read:
Einen automatischen Zeileneinschub alle 500 Zeilen bis zum Ende der Datei, beginnend bei Zeile 8.

In jeden Zeileneinschub soll der Mittelwert für die vorangehenden 500 Werte einer Spalte geschrieben werden. Dies für alle 3 Spalten. Idealerweise wird die neue Zeile mit den Zwischenwerten auf irgendeine Weise markiert (farbig unterlegt, um eins versetzt, noch ein Zeileneinschub o.ä.).

Falls das möglich ist, wäre die absolute Ideallösung das die neuen Zeilen untereinander in "Sheet2" geschrieben werden, also das ich quasi eine gleich aussehende Tabelle bekomme, nur das diese 500fach gekürzt und zusammengefasst ist.

Leider arbeite ich zum ersten Mal in meinem Leben mit Excel, wo ich sonst eigentlich kein Computermuffel bin. Insgesamt möchte ich auf diesem Wege knapp eine Million Werte zusammenfassen und sortieren und später sollen weitere Studenten die eventuell hier gefundene Programmierung nutzen, so das die Mühe also auf jeden Fall mehreren Menschen helfen würde. :tata:

Ich bedanke mich recht herzlich schonmal bei allen die das hier auch nur zu Ende gelesen haben und hoffe sehr es findet sich eine Lösung. Vielen Dank!

PS: Office 2007, Win 7 64bit
PSPS: Dieses Problem ist ähnlich dem geschlossenen Topic:
http://www.freesoft-board.to/f306/problem-mittelwertberechnung-mit-excel-vba-321470.html - dort gibt's aber keine Lösung für mich

BEISPIEL Daten:
http://dl.dropbox.com/u/3490929/beispiel-daten.jpg

 

[snoopy78]

Hallo,

Variante 1:
Das ganze per Hand machen --> dürfte sehr umfangreich sein

Variante 2: VBA

Schaut ungefähr so aus:

Sub Mittelwert_Berechnen()

Dim tab01, tab02 As String
Dim i, Zeile As Long
Dim Mittelwert1 As Double
Dim Mittelwert1_Anzahl As Integer

tab01 = "Tabelle1"
tab02 = "Tabelle2"

Mittelwert1 = 0
Mittelwert1_Anzahl = 0

Zeile = 2 'Startzeile
For j = 1 To 2000
    For i = 1 To 500
        
        If Worksheets(tab01).Cells(Zeile, 3).Value <> "" Then
            Mittelwert1 = Mittelwert1 + Worksheets(tab01).Cells(Zeile, 3).Value
            Mittelwert1_Anzahl = Mittelwert1_Anzahl + 1
        End If
        Zeile = Zeile + 1
    Next i
    
    Worksheets(tab02).Cells(j, 3).Value = Mittelwert1 / Mittelwert1_Anzahl
    
Next j

End Sub

Du könntest auch die von angesprochene Zeile einfügen, dann wird aber vom zählen her etwas komplizierter und ist auch nicht so übersichtlich. Ansonsten mußte es halt noch um den zweiten Mittelwert anpassen.

Bis dann
snoopy78

 

[excel-looser]

Wow, das ging schnell, vielen Dank für die umfangreiche Antwort und die Codezeilen. Nun bin ich leider kein Programmierer und mir nicht im klaren darüber was ich mit diesem Code nun tun muss, damit er ausgeführt wird und ich testen kann?
Wenn ich den Code richtig verstanden habe, werden nur 2 der drei Spalten von ganz rechts aus ausgewertet?
Vielen Dank,
viele Grüße

 

[snoopy78]

Hallo,

Den Code mußt du im VBA-Editor reinkopieren. Wenn du noch nie was in der Richtung gemacht hast, wäre es sinnvoll dass du die Datei hier einfach reinhängst und ich das für dich mache oder du dich in das Thema evtl. etwas einliest (kann man immer mal wieder brauchen)

Der Code wertet aktuell nur Spalte 3 aus. die anderen Spalten gehen analog. ich hab es nur nicht weiter ausgebaut, da ich nicht genau wußte, wie du deine Ausgabe haben möchtest. Z.B. was du in der 1. und 2. Spalte stehen haben willst, ...

Auch wäre es intressant, welche Dimensionen deine Meßwerte haben können. Weil man danach die Vabriabeln dklarienen muss.

Bis dann
snoopy78

 

[excel-looser]

Danke!

Hallo Snoopy,
vielen Dank für deine Unterstützung. Ich hänge mal eine der 60 Dateien (die vom Prinzip her alle gleich sind) mit ran. Ich lese mich gern ein, finde ich den VBA Editor in Excel? Kannst du eine gute Webseite oder Lektüre empfehlen mit der ich mir die nötigsten Basics aneignen kann?

Nochmal kurz zur Erklärung meines Ziels:
Spalte 1 (Datum) = no action
Spalte 2 (Zeit) = idealerweise wird hieran die Auswertungsfrequenz (nämlich alle 10s ein Mittelwert für die vorangehenden 10s in Spalte 3,4,5) festgemacht (entspricht den erwähnten 500 Zeilen-Blöcken)
Spalte 3,4,5 = Werte von denen ich in 10s-Blöcken Mittelwerte brauche

Zusatz: Eine Liste der 10s-Mittelwerte irgendwo zusammengefasst in der Datei (ganz am Ende, neues Sheet etc)

Die Beispieldatei: http://dl.dropbox.com/u/3490929/2010-05-31_2010-05-28_VD_Tuebinger.txt

Vielen Dank!!!
Viele Grüße:read:

PS: Die 3 Spalten sind numerische Werte (Newton), die vom Messprogramm aus irgendwelchen Gründen mit umgekehrtem Vorzeichen versehen wurden.

 

[snoopy78]

Hallo,

VBA ist Teil von allen MS Office-Anwqendungen und eine abgespeckte Variante von VB (aktuell Version müßte .net, brauche ich aber eigentlich nie)

Aufrufen kann man den Editor über Entwicklertools und dann Code anzeigen.

Als Seite kann ich dir vb@rchiv Visual Basic Tipps, Sourcecode, Free-ActiveX, WinAPI empfehlen.
Dort kann man sehr schnell die Befehlsreferenz bzw. auch Lösungen zu Problemen finden. Ansonsten ist auch google immer sehr hilfreich und auch die eingebaute Hilfe kann ich sehr empfehlen (wobei man hier meisten den Befehlwissen muss, den man braucht)
Als Anfang würde ich dir empfehlen, vielleicht den Code zum Mittelwert versuchen zu verstehen.

Hier nun der Code

Private Sub CommandButton1_Click()
    
Dim tab01, tab02 As String
Dim i, Zeile As Long
Dim Mittelwert1, Mittelwert2, Mittelwert3 As Double
Dim Mittelwert1_Anzahl, Mittelwert2_Anzahl, Mittelwert3_Anzahl As Long

tab01 = "Tabelle1"
tab02 = "Tabelle2"

Worksheets(tab02).Range("A1:D6000").Value = ""


Zeile = 1 'Startzeile
For j = 1 To 10000
    Mittelwert1 = 0
    Mittelwert1_Anzahl = 0
    Mittelwert2 = 0
    Mittelwert2_Anzahl = 0
    Mittelwert3 = 0
    Mittelwert3_Anzahl = 0
    
    'Startzeit eintragen
    Worksheets(tab02).Cells(j, 1).Value = Worksheets(tab01).Cells(Zeile, 1).Value
    For i = 1 To 500
        If Worksheets(tab01).Cells(Zeile, 2).Value <> "" Then
            Mittelwert1 = Mittelwert1 + Worksheets(tab01).Cells(Zeile, 2).Value
            Mittelwert1_Anzahl = Mittelwert1_Anzahl + 1
            
            Mittelwert2 = Mittelwert2 + Worksheets(tab01).Cells(Zeile, 3).Value
            Mittelwert2_Anzahl = Mittelwert2_Anzahl + 1
            
            Mittelwert3 = Mittelwert3 + Worksheets(tab01).Cells(Zeile, 4).Value
            Mittelwert3_Anzahl = Mittelwert3_Anzahl + 1
            
            
        End If
        Zeile = Zeile + 1
    Next i
    
    If Mittelwert1 <> 0 Then
        Worksheets(tab02).Cells(j, 2).Value = Mittelwert1 / Mittelwert1_Anzahl * (-1)
        Worksheets(tab02).Cells(j, 3).Value = Mittelwert2 / Mittelwert2_Anzahl * (-1)
        Worksheets(tab02).Cells(j, 4).Value = Mittelwert3 / Mittelwert3_Anzahl * (-1)
    Else
        Exit Sub
    End If
Next j

End Sub

Ich hab mir allerdings die Zeit gespart ihn ordentlich zu kommentieren (das sollte man eigentlich nicht machen. Abewr wenn du es versuchst zu verstehen, dann Schreib doch die Kommentare dazu (angeführt mit ')

Ich hab die Zusammenfassung nun gleich in ein neues Sheet geschrieben. Folgende Gründe dazu:
- es ist einfacher und ging schneller
- Alle 500 Zeile eine Zeile einfügen ist nicht gearde übersichtlich
- es verändert die ursprüngliche Datenbasis nicht (nicht sehr wissenschaftlich)

Wenn du es noch weiter ausbauen willst, dann kannst du ja mal versuchen die .txt automatisch importieren zulassen (Dateinamen aus einer Liste, Eingabefeld, Pfad in einer Zelle, ...).

Schick mir deine Mail-Adresse und ich kann dir auch das Excel-file schicken.

Bis dann
snoopy78

PS: Vom Studium her kann ich mich erinnern, dass irrgend ein Messgerät den Effekt mit den negativen Zahlen auch hatte. Das hatte aber einen logischen Grund, ich weiß aber nicht mehr was es war (DMS???)
das minus hab ich übrigens gleich weggemacht

 

[excel-looser]

Programmfragen

Hallo Snoopy,
vielen Dank nochmal für deine ausführliche Unterstützung.

Ich habe noch mehrere Fragen, die ich am Besten durchnummeriere.
1. Du hast in der Excel Datei irgendwie den Datenimport anders gestaltet als ich, bei mir wurden Datum und Zeit und getrennte Spalten geschrieben und ausserdem wird bei deiner Excel Tabelle die Zeit ohne die hunderttausendstel Sekunden eingelesen. Welche Schritte führen dich bei der Bsp-Datei zu der Excel-Datei?
(für alle die hier eventuell nachvollziehen wollen hier die Excel-Datei:
yuhu: Downloadlink entfernt (SCHON MAL DIE REGELN GELESEN ? )

2. Ich kann auf der von dir empfohlenen Webseite bei den Befehlsreferenzen leider "Dim" nicht finden, was bedeutet es? Definiert es die Variablen?
3. Was macht die Zeile "tab01 = "Tabelle1"" und warum gibt es diese nur bis tab02? Handelt es sich hier um die Definition der Worksheets in einem Workbook?

Ich habe noch mehr fragen, denke das sich aber aus den Antworten zu o.g. Fragen vielleicht schon einige Antworten ergeben, ich frage dann weiter...

Wie immer recht herzlichen Dank für deine Zeit,
viele Grüße und einen schönen Sonntag abend noch

 

[snoopy78]

Hallo,

zu 1.
Ich öffne die .txt gehe auf alles Markieren --> Kopieren
dann in die Excel, Zelle A1 markieren, einfügen (Strg+v)
Anschließend lösche ich die ersten 7 Zeilen.
die Uhrzeit ist halt so formtiert, dass hh:mm:ss angezeigt wird. Du kannst aber auch Datum und Tausendstelsekunden anzeigen lassen (TT.MM.JJJJ hh:mm:ss,000). Hunderttausendstel hab ich jetzt nicht geschafft, vermute aber, dass Excel daiese im Dateiformat nicht mehr unterstützt.

zu 2.
Dim sagt nur, dass hier anschließend Variablen definiert werden (für sowas kann man auch einfach F1 benutzen)

Zu 3.
das hab ich mir einfach so angewöhnt, dass ich am Anfang einfach die Tabellenname in eine Variable schreibe und im Code dann nur noch diese benutze. Wenn man dann mal die Tabelle umbennt, muss man nicht alles ersetzten.
Geht aber auch über die Nummern der Tabellen, ...

Bis dann
snoopy78