Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1784to1788
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

Dynamische Rechnung und Diagrammbereiche

Dynamische Rechnung und Diagrammbereiche
30.09.2020 17:13:04
Torsten
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

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Dynamische Rechnung und Diagrammbereiche
30.09.2020 17:35:12
Beverly
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.


Anzeige
AW: Dynamische Rechnung und Diagrammbereiche
05.10.2020 08:52:33
Torsten
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
AW: Dynamische Rechnung und Diagrammbereiche
05.10.2020 16:10:47
Beverly
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


Anzeige
AW: Dynamische Rechnung und Diagrammbereiche
08.10.2020 11:37:21
Torsten
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
Anzeige
AW: Dynamische Rechnung und Diagrammbereiche
08.10.2020 15:20:31
Beverly
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


Anzeige
AW: Dynamische Rechnung und Diagrammbereiche
13.10.2020 14:18:26
Torsten
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

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige