Microsoft Excel

Herbers Excel/VBA-Archiv

Dynamische Rechnung und Diagrammbereiche

Betrifft: Dynamische Rechnung und Diagrammbereiche von: Torsten
Geschrieben am: 30.09.2020 17:13:04

Hallo zusammen,

ich habe gleich mehrere Fragen zu einem Problem.

1) Ich habe Rohdaten in auf einem Sheet. In einem zweiten Sheet verweise ich darauf und führe Berechnungen durch. Ich möchte aus Datenreihen mit jeweils 40 Werten Mittelwerte berechnen. Dabei soll einmal der Mittelwert der gesamten Reihe und die Mittelwerte von jeweils 10 Werten berechnet werden, um darüber einen Qualitätsindex zu bestimmen. Dabei kommt es häufig vor, dass die Randbereiche keine Werte bzw. 0 enthalten. Diese sollen bei der Mittelwertbildung allerdings ausgenommen werden. Zudem werden die Rohdaten unterschiedlich lang umfangreich sein und mit einer Wenn-Funktion habe ich es so gemacht, das sobald keine Werte mehr bei den Rohdaten sind, diese als leer angezeigt werden. Das habe ich dann etwas nach unten gezogen. Kann man es irgendwie automatisieren, dass die Berechnungen soweit runtergehen, wie die Anzahl der Rohdaten sind?

2) Zudem möchte ich ein Oberflächendiagramm erstellen, um die Werte dreidimensional darzustellen. Das klappt an sich auch gut. Ich habe später jedoch mehrere hundert Werte, welche ich gerne in Bereiche von Wertereihen dargestellt haben möchte z.B. 20 gleichzeitig. Beim Anwählen des Diagramm kann ich ja den Bereich der verwendeten Daten verschieben. Dies würde ich gerne über ein Steuerelement wie der Scrollleiste machen. Hierzu habe ich schon ein Steuerelement eingefügt, damit ich den Wertebereich des Diagramms verschieben kann. Jedoch weiß ich nicht, wie oder was ich dafür einbinden muss. Den Qualitätsindex habe ich in das Diagramm eingebunden. Kann man das mitlaufen lassen für den Bereich, in dem man gerade ist?

Hat jemand eine Idee, wie ich das machen kann?

Ich befürchte schon, dass es nur mit VBA gehen wird.

Ich hoffe, es war halbwegs verständlich. In der Exceldatei kann man das Problem vielleicht besser erkennen.

https://www.herber.de/bbs/user/140551.xlsx

Viele Grüße

Torsten

Betrifft: AW: Dynamische Rechnung und Diagrammbereiche
von: Beverly
Geschrieben am: 30.09.2020 17:35:12

Hi Thorsten,

zu Frage 1: du hast das schon richtig gemacht, indem du die Formeln wesentlich weiter nach unten gezogen hast als Daten in Tabelle1 vorhanden sind - eine andere Möglichkeit gibt es m.E. nicht da du eben Formeln verwendest. Du könntest die Formeln natürlich auch durch ein Makro eintragen lassen und damit nur die tatsächlich vorhandene Zeilenanzahl ausfüllen lassen. Aber das ist m.E. nicht notwendig, denn es ist schließlich kein Problem, wenn übreflüssig belegte Zeilen vorhanden sind - es sei denn, die Mappe wird dadurch sehr schlecht handhabbar und zu langsam, weil sehr viele Formeln auch viel Speicherplatz belegen (können).
Wie du Mittelwerte ohne 0-Werte berechnen kannst findest du hier: https://www.herber.de/excelformeln/pages/Minimum_Mittelwert_ohne_Nullen_aber_ohne_Arrayformel.html

zu Frage 2: wie genau stellst du dir das denn vor - soll immer nur eine feste Anzahl an Werten (z.B. immer 12 wie in deiner Beispielmappe) dargestellt werden und nur der Datenbereich soll "wandern"? Oder soll auch die Anzahl an Werten variieren können, also nicht immer 12 sondern auch mal 15 oder 10 usw.?

Du müsstest also schon mal genauere Angaben machen was du erreichen willst.


GrußformelBeverly's Excel - Inn

Betrifft: AW: Dynamische Rechnung und Diagrammbereiche
von: Torsten
Geschrieben am: 05.10.2020 08:52:33

Hallo Beverly,

1) die Lösung mit den 0 Werten klappt sehr gut. Danke dafür!!!

2) Bei der Darstellung wäre es gut, wenn ich die Anzahl der Werte variieren könnte, da man teilweise erst bei längeren Zeitintervallen Unterschiede erkennen kann.
Wenn man dann einen festen Intervall hat, soll dieses Intervall dann fest bleiben und der Datenbereich wandern.

Viele Grüße
Torsten

PS: Sorry für die späte Rückmeldung

Betrifft: AW: Dynamische Rechnung und Diagrammbereiche
von: Beverly
Geschrieben am: 05.10.2020 16:10:47

Hi Torsten,

um das zu lösen müssen Namen definiert werden, welche sich auf die ausgewählten Daten (Startzeile und Anzahl) sowie auf die jeweilige Spalte der Datenreihe beziehen. Man benötigt außerdem Zellen, die mit den Steuerelementen verknüpft sind, um die Auswahl dann weiterverarbeiten zu können (im Bereich der Spalten AZ:BB).
Schau dir einfach mal den Anhang an - dort habe ich das für die beiden Steuerlemente im Diagramm sowie für 6 Datenreihen realisiert.
Für die restlichen Datenreihen musst du das selbst machen. Die Namen für die Datenreihen habe ich zu S_1, S_2, S_3 usw. fetgelegt. S1, S2, S3 usw. geht nicht, da diese Zusammensetzung als Zelladresse betrachtet wird und deshalb nicht als Name verwendet werden darf.

Die Namensdefinition für die Datenreihen ist folgende (Beispiel für Datenreihen S1, S2 und S3):

Name: S_1
Bezieht sich auf: =INDEX(Tabelle2!$J:$AW;Tabelle2!$AZ$2+4;1):INDEX(Tabelle2!$J:$AW;Tabelle2!$BB$1+Tabelle2!$AZ$2+3;1)

Name: S_2
Bezieht sich auf: =INDEX(Tabelle2!$J:$AW;Tabelle2!$AZ$2+4;2):INDEX(Tabelle2!$J:$AW;Tabelle2!$BB$1+Tabelle2!$AZ$2+3;2)

Name: S_3
Bezieht sich auf: =INDEX(Tabelle2!$J:$AW;Tabelle2!$AZ$2+4;3):INDEX(Tabelle2!$J:$AW;Tabelle2!$BB$1+Tabelle2!$AZ$2+3;3)

Ich nehme an, du hast das Prinzip erkannt: die Grundformel ist bei allen identisch bis auf den Spaltenbezug in der INDEX-Formel. Das hängt damit zusammen, dass ich als Bereich die Spalten J:AW genommen habe und sich deshalb nur die Spaltennummer ändert. Das ist einfacher als wenn man die Spaltenbuchstaben für jede Spalte anpassen müsste.

Im Diagramm sind dann diese definierten Namen den Datenreihen zugewiesen. Außerdem muss für die Beschriftung der Rubrikenachse ein eigener Name definiert werden (Spalte A), der im Diagramm der Achsenbeschriftung zugewiesen ist.

Deutliche Veränderungen im Diagramm siehst du, wenn du die Startposition mittels Bildlaufleiste verringerst.

https://www.herber.de/bbs/user/140665.xlsx


GrußformelBeverly's Excel - Inn

Betrifft: AW: Dynamische Rechnung und Diagrammbereiche
von: Torsten
Geschrieben am: 08.10.2020 11:37:21

Hallo Beverly,

vielen Dank, dass klappt schon einmal gut. Bei der Namenszuordnung bzw. definieren der Namen habe ich kurz gebraucht, da ich die Funktion noch nicht kannte. Also wieder was neues gelernt:-)

Zwei Fragen sind jetzt noch aufgekommen.
1) Zum einen habe ich die Namen mittels folgendem VBA Code vergeben, was auch gut funktioniert hat.
Sub Makro1()
'
' Makro1 Makro
'

'
Dim i As Long

For i = 14 To 40


    ActiveWorkbook.Names.Add Name:="S_" & i, RefersToR1C1:= _
        "=INDEX(Berechnungen!C33:C72,Berechnungen!R2C77+2," & i & "):INDEX(Berechnungen!C33:C72, _
Berechnungen!R1C79+Berechnungen!R2C77+1," & i & ")"
Next

End Sub
Jetzt würde ich gerne die Zuweisung mittels VBA zu dem Diagramm machen, da ich wahrscheinlich noch ähnliche Diagramme erstellen möchte. Dabei möchte ich das nicht alles per Hand machen, wenn es mittels VBA einfacher und schneller geht. Mit dem Recorder habe ich folgenden Code aufgenommen und dann versucht zu einer Schleife umzuwandeln.

Sub Makro3()
'
' Makro3 Makro
'

'
    ActiveSheet.Shapes.Range(Array("Group 4")).Select
    ActiveSheet.ChartObjects("Diagramm 3").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.FullSeriesCollection(1).Values = _
        "=='Test_Verteilung-Rechnungen.xlsm'!S_1"
End Sub
Zu Schleife umgewandelter Code
Sub Makro4()
'
' Makro4 Makro
'

'
Dim i As Long

For i = 3 To 40

    ActiveSheet.Shapes.Range(Array("Group 4")).Select
    ActiveSheet.ChartObjects("Diagramm 3").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.FullSeriesCollection(i).Values = _
        "=='Test_Verteilung-Rechnungen.xlsm'!S_" & i & ""
        
Next
        
End Sub

Es kommt zum Laufzeitfehler '1004': Anwendungs- oder objektdefinierter Fehler.
Ich habe es auch nochmal ohne Schleife probiert und einfach statt (1) und S_1 --> (3) und S_3 eingetragen, was ebenfalls zu einem Fehler führt.
Ist irgendetwas falsch geschrieben oder sind das die falschen Befehle?


2) Zum anderen möchte ich für das Steuerelement zum verschieben der Position/Zeit den Maximalwert dynamisch Anpassen, da die Anzahl der Messwerte variiert. Hierfür habe ich mir eine Zelle mit der MAX() Funktion angelegt, welche die Positionswerte durchsucht. Leider kann ich den beim Steuerelement nicht verlinken. Geht das allgemein nicht?


Zur Info:
Ich habe jetzt schon einmal eine umfangreichere Datei mit anderen Sheetnamen genutzt, aber in den Formeln auch entsprechend angepasst. Ich hoffe, dass ist zur Beantwortung kein Hindernis, sonst mache ich das in der Beispieldatei nochmal.

Viele Grüße
Torsten

Betrifft: AW: Dynamische Rechnung und Diagrammbereiche
von: Beverly
Geschrieben am: 08.10.2020 15:20:31

Hi Torsten,

1. Zuweisung der definierten Namen:
Weshalb die Schleife von 3 bis 40? Die Zählung der Datenreihen beginnt doch mit 1
Die Schleife für die Zuweisung der Namensbereiche nach diesem Prinzip (Dateiname anpassen!!!):
Sub DatenreihenNamenZuweisen()
    Dim lngReihe As Long
    With ActiveSheet.ChartObjects("Diagramm 3").Chart
        .FullSeriesCollection(1).XValues = "=RE_dyn_dia_variabel.xlsm!XAchse"
        For lngReihe = 1 To .FullSeriesCollection.Count
            .FullSeriesCollection(lngReihe).Values = "=RE_dyn_dia_variabel.xlsm!S_" & lngReihe
        Next lngReihe
    End With
End Sub


2. Maximum für Bildlaufleiste:
Die Bildlaufleiste gibt nicht die Zeit sondern die Position an. Somit ist nicht das Maximum sondern die Anzahl gefragt
Sub MaxZuweisen()
    ActiveSheet.Shapes("Bildlaufleiste 3").DrawingObject.Max = Application.Count(Columns(1))
End Sub

GrußformelBeverly's Excel - Inn

Betrifft: AW: Dynamische Rechnung und Diagrammbereiche
von: Torsten
Geschrieben am: 13.10.2020 14:18:26

Hallo Beverly,

1) die beiden Codes funktionieren super.
Bei Code 1 musste ich allerdings noch Apostroph vor und hinter Dateinamen setzen.

2) Ich hatte erstmal mit 3 gestartet, da 1 und 2 bereits gemacht waren.

Vielen Dank nochmal!!!

Viele Grüße
Torsten