Messwerte: Mittelwert bilden und Diagramm zeichnen

  • Excel

Es gibt 6 Antworten in diesem Thema. Der letzte Beitrag () ist von Lanjan.

    Messwerte: Mittelwert bilden und Diagramm zeichnen

    Servus,

    ich bin ein relativer Neuling in Sachen VBA, bisher habe ich nur mit C gearbeitet, aber in der Officeumgebung kann man das ja vergessen und das ist auch ok so... :thumbsup: .



    Ich zerbreche mir nun schon seit mehrern Tagen den Kopf über folgende Aufgabe:

    Aus einem Prüfstand erhält man mehrere Messungen (z.B. 6 Messungen a 3 Messfahrten = 18 Mitschriebe), diese Messungen werden in einer .CSV-Datei gespeichert. Die Werte werden nun in eine .XLS-Datei kopiert und mittels einem Makro soll für den Benutzer die entsprechenden Diagramme erzeugt werden.

    Programmablauf (geplant):

    Der Benutzer startet das Makro und wird gefragt über wieviele Messfahrten ein Mittelwert gebildet werden soll (Bsp. 3). Nun soll das Makro den Mittelwert für die Messfahrten 1, 2 und 3, dann für 4, 5 und 6, dann ... etc. Anschließend wird für jeden Mittelwert ein Diagramm erstellt.

    Was ich bisher habe:

    -Über ne MessageBox kann ich die Anzahl für das Zusammenfassen der Messfahrten abfragen, easy.

    -Dann prüft mein Makro erstmal nach ob es schon am Ende der Tabelle ist (zwei Leerzeichen hintereinander).

    -Da zwischen jeder Messfahrt ein Leerzeichen ist, wird nun der Abstand (Anzahl Zeilen) zwischen den Messfahrten ermittelt und dieser in ein Array gespeichert (nicht jede Messfahrt hat gleich viele Einträge, mal 20 mal 19 oder auch 21).

    Was fehlt:

    -Nun will ich aus der Messfahrt 1, 2 und 3 jeweils den ersten Eintrag für den Weg als Mittelwert in eine seperaten Tabelle abspeichern (aus welcher dann die Diagramm gebildet werden). Dann den zweiten Eintrag, dritten, etc. bis zum JEWEILS letzten.

    Und da liegt mein Problem. Die Messfahrten sind ja unterschiedlich lang und daher kann es sein, dass es bei den letzten Einträgen bei einer Messfahrt keine Daten mehr gibt die verrechnet werden könnten.

    Wie bekommt man so etwas halbwegs geschickt hin ? Klar ich könnte nun zig Abfragen machen und Schleifen basteln, aber das kann ja nicht der goldene Weg sein. Vorallem qualmt mir der Kopf und droht auch langsam zu explodieren. Vielleicht gibts ja sogar so ein Programm schon fertig ?

    Die Geschichte mit den Diagrammen bekomme ich, denke ich, schon hin. Nur den Mittelwert anständig zu bilden, da fehlt mir noch der letzte Kniff. ;(



    Im Anhang findet ihr ein Messprotokoll wie es aussehen sollte (5. Messfahrt ist etwas kürzer) inkl. dem Makro "diagramm" wo ich bisher erstellt habe.

    Ist nicht ganz einfach die Aufgabenstellung, aber vielleicht kann mir ja jemand ein wenig weiterhelfen. Wäre jedenfalls super :thumbup:

    PS: An der Formatierung der Tabelle kann ich noch etwas rumschrauben, falls das mein Problem vereinfachen sollte.
    Dateien

    • beispiel.xls

      (59,9 kB, 557 mal heruntergeladen, zuletzt: )

    Lösungsansatz um die Mittelwerte auf einen Schlag zu ermitteln:

    1. Daten erweitern um Laufende Nummer je Messungsreihe

    2. Daten via LfdNr (Zeile) und Messungsreihe (Spalte) mit Mittelwertfunktion pivotieren

    3. Mit Indexfunktion auf den Mittelwert zugreifen

    Und lass die Leerzeilen weg...

    Der Vorschlag von DoSchwob ist sicherlich sehr gut und bietet wohl mehr Möglichkeiten, als einfach nur "immer 3 Messungen". Man kann dann z. B. auch "Messungen 1, 3, 5" auswählen. Ich würde das also an deiner Stelle mal ausprobieren.

    Nichtsdestotroz habe ich hier etwas, was deine Aufgabe bewältigt. Mein Ansatz liegt darin, dass man in Excel-VBA einige der Excel-Funktionen (z. B. MAX, MIN, ANZAHL, MITTELWERT, ZÄHLENWENN, VERLGEICH) anwenden kann.

    Visual Basic-Quellcode

    1. Sub MittelwerteBestimmen()
    2. Dim AnzMessungen As Integer, vonNr As Integer, bisNr As Integer, Grenze As Integer, MaxAnz As Integer
    3. Dim i As Integer, j As Integer, k As Integer, Weg As Double, Kraft As Double
    5. AnzMessungen = InputBox(Prompt:="Wieviel Messungen sollen zusammengefasst werden?", Title:="InputBox")
    6. Sheets("Tabelle1").Select
    7. Range("E:F").ClearContents
    8. Range("E1") = "Weg-Mittelwert"
    9. Range("F1") = "Kraft-Mittelwert"
    10. vonNr = WorksheetFunction.Min(Range("A:A"))
    11. bisNr = WorksheetFunction.Max(Range("A:A"))
    12. Grenze = ((bisNr - vonNr) \ AnzMessungen + 1) * AnzMessungen
    13. ReDim Messungen(bisNr - vonNr), Startzeile(bisNr - vonNr)
    14. 'Messungen ist ein Array mit den jeweiligen Anzahlen der Zeilen pro Messung-Nr
    15. 'Startzeile ist ein Array mit den jeweiligen Zeilennumern, in der die Nr startet.
    16. MaxAnz = 0
    17. 'MaxAnz ist die maximale Anzahl der Messungen
    18. For i = vonNr To bisNr
    19. Messungen(i - vonNr) = WorksheetFunction.CountIf(Range("A:A"), i)
    20. If Messungen(i - vonNr) > MaxAnz Then MaxAnz = Messungen(i - vonNr)
    21. Startzeile(i - vonNr) = WorksheetFunction.Match(i, Range("A:A"), 0)
    22. Next
    23. For i = 0 To Grenze - 1 Step AnzMessungen
    24. For j = 0 To MaxAnz - 1
    26. 'Berechnet die Weg-Mittelwerte und trägt sie in Spalte E ein.
    27. 'In Spalte D werden die Werte, von denen der Mittelwert berechnet werden soll, eingetragen
    28. 'und dann der Mittelwert berechnet.
    29. Range("D1:D" & AnzMessungen).ClearContents
    30. For k = 0 To AnzMessungen - 1
    31. If i + k <= bisNr - vonNr Then
    32. If j < Messungen(i + k) Then
    33. Range("D" & k + 1) = Range("B" & Startzeile(i + k) + j)
    34. End If
    35. End If
    36. Next
    37. Range("E" & Startzeile(i) + j).Select
    38. '"> 0" beduetet, dass der Mittelwert berechnet wird, auch wenn nur 1 Wert vorhanden ist.
    39. '"= AnzMessungen" stattdessen, bewirkt, dass der Mittelwert nur berechnet wird,
    40. 'wenn alle Werte vorhanden sind.
    41. If WorksheetFunction.Count(Range("D1:D" & AnzMessungen)) > 0 Then
    42. Weg = WorksheetFunction.Average(Range("D1:D" & AnzMessungen))
    43. Range("E" & Startzeile(i) + j) = Weg
    44. End If
    46. 'Berechnet die Kraft-Mittelwerte und trägt sie in Spalte F ein.
    47. 'In Spalte D werden die Werte, von denen der Mittelwert berechnet werden soll, eingetragen
    48. 'und dann der Mittelwert berechnet.
    49. Range("D1:D" & AnzMessungen).ClearContents
    50. For k = 0 To AnzMessungen - 1
    51. If i + k <= bisNr - vonNr Then
    52. If j < Messungen(i + k) Then
    53. Range("D" & k + 1) = Range("C" & Startzeile(i + k) + j)
    54. End If
    55. End If
    56. Next
    57. Range("F" & Startzeile(i) + j).Select
    58. '"> 0" beduetet, dass der Mittelwert berechnet wird, auch wenn nur 1 Wert vorhanden ist.
    59. '"= AnzMessungen" stattdessen, bewirkt, dass der Mittelwert nur berechnet wird,
    60. 'wenn alle Werte vorhanden sind.
    61. If WorksheetFunction.Count(Range("D1:D" & AnzMessungen)) > 0 Then
    62. Kraft = WorksheetFunction.Average(Range("D1:D" & AnzMessungen))
    63. Range("F" & Startzeile(i) + j) = Kraft
    64. End If
    66. Next
    67. Next
    68. Range("D1:D" & AnzMessungen).ClearContents
    69. End Sub

    Sorry dass ich mich solange nicht mehr gemeldet habe,

    @roddy: Danke für den Code, habe ich gleich mal ausprobiert uns auf den ersten Blick sieht das echt gut aus. Allerdings gibt nen Fehler, wenn beide Messungen nicht gleich viele Zeilen haben. Dann habe hat man wieder das Problem mit der Zuordnung der Messwerte. z.B. Messung 5 und 6 haben am Ende die gleichen Werte, und wenn man nun von beiden Messungen den Mittelwert bildet, dann verrringert sich am Ende der Weg.

    @DoSchwob: Ich würde deinen Tipp ja gerne mal ausprobieren, aber ich frag nun einfach mal ganz unbedarft: Was ist "pivotieren" und mit welcher "Indexfunktion" soll ich den Zugriff auf den Mittelwert gestalten? "Pivotieren" ist laut Duden, das Umspielen eines Gegners beim Basketball, aber ich denke nicht, dass dies in deinem Sinne war.



    Diese Mittelwertgeschichte nervt. Die Steuerung könnte ich auch so programmieren, dass sie mir pro Messung X Werte ausgibt. Aber dann habe ich das Problem, dass man entweder zu wenig Messungreihen erhält (Kraftmaximum nicht erreicht) oder das man zuviele Werte erhält (ständig das Kraftmaximum) bzw. der Antrieb vorher abschält.

    Man bräuchte ne "intelligente Mittelwertbildung". Vielleicht meint ja "DoSchwob" genau dies?



    PS: Gruß aus dem schwäbischen Ehningen.

    Tag, ich bins nochmal.



    Eigentlich dachte ich, dass alles fertig ist und soweit funktioniert. Als ich das VBA-Programm heute mal ausprobierte kam folgende Fehlermeldung:
    Laufzeitfehler 1004

    Die Methode 'Location' für das Objekt '_Chart' ist fehlgeschlagen.

    Vermutlich, kann das zweite Diagramm nicht gezeichnet werden, weil es schon ein Diagramm mit diesen Titel (Diagramm1) gibt. Soweit so klar. Aber wenn ich den Debugger beende und das gleiche Makro mit den gleichen Werten noch einmal starte, läuft es ohne weiteres durch und zeichnet mir alle Diagramme. 8|

    Das Programm bleibt, bei der erstmaligen Ausführung, bei "ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Diagramm" & Zaehler" hängen. Weil die Variable "Zähler" = 1 ist und es schon ein Diagramm mit diesem Titel existiert. Frage: Wieso ist der Zähler inkorrekt bei der erstmaligen Ausführung, arbeitet aber korrekt beim zweiten Aufruf ?( .

    Hier mal der Quellcode für das Zeichnen der Diagramme:

    Visual Basic-Quellcode

    1. Zaehler = 1
    2. For i = 0 To Grenze - AnzMessungen Step AnzMessungen
    3. Charts.Add
    4. ActiveChart.ChartType = xlLine
    5. ActiveChart.SetSourceData Source:=Sheets("Tabelle1").Range("H2")
    6. ActiveChart.SeriesCollection.NewSeries
    7. ActiveChart.SeriesCollection(1).XValues = "=Tabelle1!R" & Startzeile(i) & "C5:R" & Startzeile(i) + Abstand & "C5"
    8. ActiveChart.SeriesCollection(1).Values = "=Tabelle1!R" & Startzeile(i) & "C6:R" & Startzeile(i) + Abstand & "C6"
    9. ActiveChart.SeriesCollection(1).Name = "Kraft [N]"
    10. ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Diagramm" & Zaehler
    11. With ActiveChart
    12. .HasTitle = True
    13. .ChartTitle.Characters.Text = "Kraft-/Wegdiagramm " & Zaehler
    14. .Axes(xlCategory, xlPrimary).HasTitle = True
    15. .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Weg [Mikrometer]"
    16. .Axes(xlValue, xlPrimary).HasTitle = True
    17. .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Kraft [N]"
    18. End With
    19. ActiveChart.HasDataTable = False
    20. Zaehler = Zaehler + 1
    21. Next


    Danke Danke Danke für eure Hilfe, vorallem danke roddy, dein Quellcode habe ich zum großen Teil übernommen. Ich habe die Steuerung nun doch überreden können, immer die gleiche Anzahl an Messwerten pro Messung auszuspucken. :thumbsup:

    Ich trau mich ja fast gar nicht, aber ich hätte noch eine Frage. :rolleyes:



    Und zwar möchte ich in den Kraft-/Wegdiagrammen jeweiles einen bestimmten Datensatz markieren.

    zum Beispiel soll von 25 Werten, der 18. Wert irgendwie markiert werden, weil dort etwas tolles passiert (was sag ich nicht :whistling: ). Beim Nächsten Diagramm ist es vielleicht der 15. Wert, beim Übernächsten mal der 20. Wert.



    Zur Erfassung, welcher Wert nun besonders ist, habe ich mehrer Möglichkeiten. Ich kann den entsprechenden Wert von der Steuerung am Ende meiner Messwerte nocheinmal ausgeben, oder ich häng hinter dem entsprechenden Messschritt ein "*". Mehr fällt mir gerade nicht ein.

    Problematisch für mich ist eigentlich nur, eine passende Darstellung zu finden. Wenn ich im Diagramm eine 2. Datenreihe hinzufüge wo der besondere Wert seperat behandelt wird, dann kann ich zwar eine "1-Punkt-Linie" erzeugen, aber die steht nur in der Y-Achse (Kraft) korrekt. Der Bezug zum Weg übernimmt sie nicht.



    Kann mir jemand weiterhelfen? Evtl. auch noch zum Problem, meiner vorrigen Frage ?( Wäre jedenfalls super!