automatisierte Mittelwertberechnung inkl Bildung von Zahlenblöcken und Zwischenergebnissen geschlossen

[excel-looser]

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.

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]

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]

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

 

[excel-looser]

Hallo Snoopy, vielen Dank ,es war scheinbar übermotiviert meinerseits die Importfunktion zu nutzen, nun hab ich die Daten so vorliegen wie du und kann deinen Quelltext verwenden. Aber...
im Bild (siehe unten) sieht man einmal wie mein Excel 2010 aussieht wenn ich auf Visual Basic gehe (neues Dokument) und darunter wie es aus deinem Excel-Dokument aussieht.

Nun wollte ich gern in das von mir neu erstellte Dokument deinen Quelltext kopieren, einen Button erstellen und mit der Funktion verknüpfen, so das ich am Quelltext nix verändern muss.

Würdest du mir bitte Schritt-für-Schritt erklären, wie das geht? Ich besorge mir noch die Video2Brain Excel VBA DVD, aber im Moment bin ich zu blöde all deine Programmierleistungen in die neuen Dokumente zu übernehmen.

Vielen Dank mal wieder für die Unterstützung,
Paul =)

http/dl.dropbox.com/u/3490929/VBA_Excel_einfuegen.jpg

 

[snoopy78]

Hallo,

Button erzeugen:
- Entwicklertools -> Einfügen -> ActiveX-Steuerelement-Button
- Größe aufziehen
- Rechte Maustaste auf Button -> Code anzeigen
- Anschließend kannst du Code einfach kopieren (ohne "sub ..." und "End sub")
- Entwurfsmodus in Excel wieder ausschalten

Ich hab mal kurz geschaut, was dieses Video2Brain ist. Da würde ich jetzt eher weniger davon halten.
Hast du eigentlich schonmal programmiert?
Es gibt genug Einführungskurse im Netz (z.B. google "vba einführung excel", gleich der erste Treffer). Du solltest halt erst mal klein anfangen. z.B. ein einfaches Hello World und einfache Schleifen.

Ansonsten würdest du auch hier im Board entsprechende eBooks finden
http://www.freesoft-board.to/f365/upload-sammlung-for-dummies-369-ebook-collection-engl-458702.html

Bis dann
snoopy78

 

[excel-looser]

Hallo,
vielen Dank soweit, ich habe das Projekt erfolgreich einen riesen Schritt in Richtung Abschluss bringen können.

Nun noch eine Frage die sich vermutlich mit einer Excel-Funktion lösen lässt, ich weiss es aber nicht.

Wie kann ich Excel überreden eine Tabelle quasi "umzukehren". Dh ich möchte das jede Zeile in einer neuen Tabelle zu einer Spalte wird und jede Spalte zu einer Zeile. Das mache ich gern auch "zu Fuß" per Copy-und-Paste per Hand, nur würde ich halt nicht gern jeden einzelnen Wert kopieren, sondern eine ganze Spalte und sie dann sozusagen als Zeile einfügen. Geht das? Wenn ja, wie?

Vielen Dank,
viele Grüße!

 

[snoopy78]

Hallo,

Strg+c -> einfügen (Dropdown-Menü) -> Inhalte einfügen -> transponieren anwählen -> OK

Ich muss noch was korigieren. Mir ist ein Fehler im Quelltext unterlaufen, worauf mich Cpt. Nemo hingewiesen hat (Vielen Dank nochmal für den Hinweis, man lernt halt nie aus).
Es geht um die Deklaration von Variabeln:
Falsch ist folgendes:

Dim i, Zeile As Long
Dim Mittelwert1, Mittelwert2, Mittelwert3 As Double

i, Mittelwert1, Mittelwert2 werden hier als Variant definiert

Richtig ist folgendes:

Dim i [COLOR='Red']As Long[/COLOR], Zeile As Long
Dim Mittelwert1 [COLOR='Red']As Double[/COLOR], Mittelwert2 [COLOR='Red']As Double[/COLOR], Mittelwert3 As Double

Bis dann
snoopy78

 

[excel-looser]

Danke für den Hinweis mit dem Fehler, welche Konsequenz hat dieser? Wird dadurch was falsch berechnet? LG

 

[snoopy78]

Hallo,

Bedanken mußt du dich bei Cpt. Nemo. Ich wußte es ja selber nicht.

Zu den Auswirkungen (@Cpt. Nemo: bitte korrigiere mich, wenn es falsch ist)
Die Variable ist halt einfach als nichts bzw. Variant definiert.
In höheren Sprachen kann das ziemlich massive Auswirkungen haben. Wenn ich mich nicht täusche bis zum Überschreiben von fremden Speicherbereichen (abhängig auch vom OS)
In VBA sind die Auswirkungen eher nicht spürbar, da es ziemlich geduldig ist. Das einzige was mir jetzt einfallen würde wäre, dass du vom Compiler keine Fehlermeldung kriegst, wenn einer Variable falsche Datentypen zuordnen willst bzw. auch der Inhalt evtl. nicht dem entspricht, was man erwartet. Und die Fehlersuche ist dann halt entsprechend aufwendig.

Bis dann
snoopy78

 

[cpt. Nemo]

Bedanken mußt du dich bei Cpt. Nemo. Ich wußte es ja selber nicht.

Gerne geschehen!

OK, das ist prinzipiell richtig, was du geschrieben hast. Für alle: Ich habe snoopy78 eine pm geschrieben, weil die Auswirkungen wirklich nicht sichtbar sind. VBA ist dort wirklich sehr strapazierfähig .

In VB .NET ist diese Schreibweise

Dim A, B, C as Integer

absolut OK, da werden in der Tat alle drei Variablen als Integer deklariert. Hier hinkt VBA (leider) auch in den neuesten Versionen hinterher, ich denke aus Kompatibilitätsgründen.

Und ja, es ist möglich jegliche Variable ohne Typ zu deklarieren, die wird dann automatisch Variant, sucht sich dann je nach Gelegenheit den Typ selber heraus. Aber das ist etwa so, als wenn man nur ein Universal-Waschmittel verwenden würde. Irgendwann merkt man dann doch bei schwarzen oder bunten Sachen, dass ein Spezialwaschmittel bessere Erfolge erzielt.

 

[totsch]

Wie kann ich Excel überreden eine Tabelle quasi "umzukehren". Dh ich möchte das jede Zeile in einer neuen Tabelle zu einer Spalte wird und jede Spalte zu einer Zeile.

Hi,

wie @snoopy78 schon bereits geschrieben hat über Inhalte einfügen -> transponieren - entspricht statische Umkehr.

Diese Zeilen-/Spalten-Umkehrung, kannst du aber auch dynamisch gestalten mit der Funktion MTRANS >>

Ausgang ist der grüne Bereich (=Ursprungsbereich mit 4 Spalten und 6 Zeilen). Für die Umkehr musst du also einen Bereich markieren mit genau umgekehrter Spalten-/Zeilenanzahl.
Also gelben Bereich (= Zielbereich mit 6 Spalten und 4 Zeilen) markieren und dann in der Bearbeitungsleiste die Formel eingeben =MTRANS(A16)
Da es sich hierbei um eine Matrixformel handelt, musst du diese Formel mit Strg+Shift+Enter abschliessen. Dadurch werden die geschweiften Klammern automatisch erzeugt >> {=MTRANS(A16)}

In den markierten Zellen der Zieltabelle A9:F12 (gelb) erscheinen daraufhin die Werte aus A16 (grün) - verknüpft und transponiert, d.h. wenn du im grünen Bereich Änderungen vornimmst, werden diese im gelben Bereich automatisch übernommen.

Gruss Totsch

 

[cpt. Nemo]

Moin,

die Frage von excel-looser "Wird dadurch was falsch berechnet?" hat mir doch keine Ruhe gelassen . Ich habe mal ein Beispiel erstellt. Angenommen, du brauchst -aus welchen Gründen auch immer- einen eingegebenen Wert als Ganzzahl, was dem INTEGER-Variablentyp entspricht. In Zelle A1 steht beispielsweise 5,4, in B1 10. Zwei Prozeduren tun praktisch das gleiche, aber die Deklaration ist verschieden. -> Einfach mal sehen, welches Ergebnis wobei herauskommt, auch mit verschiedenen Werten in den Zellen ...

Sub richtig()
Dim a As Integer, b As Integer
a = Cells(1, 1).Value
b = Cells(1, 2).Value
MsgBox "Multiplikation von GANZZAHLEN" & vbCrLf _
& "A1 * B1 => " & a & " * " & b & " = " & a * b, _
vbExclamation + vbOKOnly, "Richtiges Ergebnis"
End Sub

Sub falsch()
Dim a, b As Integer
a = Cells(1, 1).Value
b = Cells(1, 2).Value
MsgBox "Multiplikation von GANZZAHLEN" & vbCrLf _
& "A1 * B1 => " & a & " * " & b & " = " & a * b, _
vbExclamation + vbOKOnly, "Falsches Ergebnis"
End Sub

Wie gesagt, das Ergebnis was falsch aussieht ist das richtige, weil ja nur Ganzzahlen berücksichtigt werden sollen.

 

[excel-looser]

Vielen Dank allerseits, ihr helft mir wirklich sehr. Ich gehe mal davon aus das die Mittelwerte nicht im Ergebniss beeinflusst werden. Der Begriff "transponieren" war mir bis dato nicht bekannt und tut genau das was ich möchte, hat mir wiedermal viel Zeit und Fehlerquellen erspart. Ich werde weiter"basteln" und mich vertrauensvoll an Euch Profis wenden, wenn ich eine Frage habe. Danke nochmal!