Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1860to1864
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1860to1864
1860to1864
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel für dynamisches Diagramm

Formel für dynamisches Diagramm
10.12.2021 09:59:23
Frank
Hallo,
ich habe ein Problem mit der Definition der Formel für die Namensgebung.
Es sollen die Daten dynamisch im Diagramm ankommen.
Userbild
Es sollen nur die roten Felder im Diagramm ankommen, für das Datum habe ich es mit folgender Formel versucht:
=TabStromEG!$A$2:INDEX(TabStromEG!$A:$A;VERWEIS(2;1/(TabStromEG!$A:$A"");ZEILE(TabStromEG!$A:$A)))
positiv-Spalte:
=TabStromEG!$F$2:INDEX(TabStromEG!$F:$F;VERWEIS(2;1/(TabStromEG!$F:$F"");ZEILE(TabStromEG!$E:$E)))
negativ-Spalte:
=TabStromEG!$G$2:INDEX(TabStromEG!$G:$G;VERWEIS(2;1/(TabStromEG!$G:$G"");ZEILE(TabStromEG!$E:$E)))
Leider bleibt hierbei das gewünschte Ergebnis aus, für eine Hilfestellung wäre ich sehr dankbar.
Gruß Frank.

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
10.12.2021 10:03:04
neopa
Gruß Werner
.. , - ...
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
10.12.2021 12:19:02
UweD
Hallo
- Rechtsclick auf den Reiter des Diagramms
- Code anzeigen
- Code rechts reinkopieren

Option Explicit
Dim LC As Long, TB As Worksheet
Private Sub Chart_Activate()
Dim LR As Long
Set TB = Sheets("TabStromEG")
With TB
LR = .Cells(TB.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte A
LC = .Cells(1, TB.Columns.Count).End(xlToLeft).Column 'letzte Spalte der Zeile 1
If .AutoFilterMode Then TB.AutoFilterMode = False ' Autofilter ausschalten
.Cells(2, LC + 2).Resize(LR - 1, 1).FormulaR1C1 = _
"=IF(AND(RC6="""",RC7=""""),""X"","""")"
' I2:   =WENN(UND($F2="";$G2="");"X";"")
.Cells(1, LC + 2) = "TMP"
.Columns(LC + 2).AutoFilter Field:=1, Criteria1:="=" 'Nur Leere anzeigen
End With
Me.PlotVisibleOnly = True ' Ausgeblendete Zeilen weglassen
End Sub
Private Sub Chart_Deactivate()
If TB.AutoFilterMode Then TB.AutoFilterMode = False         ' Autofilter ausschalten
TB.Columns(LC + 2).Delete
Me.PlotVisibleOnly = False 'Standard
End Sub
- Bei Aktivierung des Diagramms wird eine Hilfsspalte eingefügt
- Die in F und G gemeinsam leeren Zeilen werden ausgeblendet
- Dadurch werden sie im Diagramm unsichtbar
- Bei Verlassen des Diagramms wird alles zurückgestellt
LG UweD
Anzeige
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
10.12.2021 13:11:48
Frank
Vielen dank, das sieht sehr vielversprechend aus!
Es gibt einen Fehler in folgender Zeile:

If TB.AutoFilterMode Then TB.AutoFilterMode = False         ' Autofilter ausschalten

AW: (D)eine Beispieldatei wäre hilfreich(er) owT
10.12.2021 13:16:28
UweD
Hallo nochmal
ok.
Das zweite Makro läuft das erste mal vor dem Ersten und da kennt es den Namen noch nicht
Mach es so

Option Explicit
Dim LC As Long
Const TB As String = "TabStromEG"
Private Sub Chart_Activate()
Dim LR As Long
With Sheets(TB)
LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte A
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column 'letzte Spalte der Zeile 1
If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten
.Cells(2, LC + 2).Resize(LR - 1, 1).FormulaR1C1 = _
"=IF(AND(RC6="""",RC7=""""),""X"","""")"
' I2:   =WENN(UND($F2="";$G2="");"X";"")
.Cells(1, LC + 2) = "TMP"
.Columns(LC + 2).AutoFilter Field:=1, Criteria1:="=" 'Nur Leere anzeigen
End With
Me.PlotVisibleOnly = True ' Ausgeblendete Zeilen weglassen
End Sub
Private Sub Chart_Deactivate()
With Sheets(TB)
If .AutoFilterMode Then .AutoFilterMode = False                    ' Autofilter ausschalten
If LC  0 Then .Columns(LC + 2).Delete
End With
Me.PlotVisibleOnly = False 'Standard
End Sub
LG UweD
Anzeige
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
10.12.2021 15:08:37
Frank
Hallo Uwe, vielen lieben Dank.
Mir ist jetzt nur noch aufgefallen das die TMP Spalte öfters angelegt wird, d.h. am Ende meiner Spalten wird öfters nach jeder zweiten Spalte eine neue TMP-Spalte angelegt...
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
10.12.2021 15:21:40
UweD
dann hast du das erste Makro mehrmals von Hand ausgeführt.
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
10.12.2021 15:39:46
UweD
Hier mal eine andere Routine zum löschen der Temp Spalten.

Private Sub Chart_Activate()
Dim LR As Long
With Sheets(TB)
LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte A
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column 'letzte Spalte der Zeile 1
If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten
.Cells(2, LC + 2).Resize(LR - 1, 1).FormulaR1C1 = _
"=IF(AND(RC6="""",RC7=""""),""X"","""")"
' I2:   =WENN(UND($F2="";$G2="");"X";"")
.Cells(1, LC + 2) = "#TMP#"
.Columns(LC + 2).AutoFilter Field:=1, Criteria1:="=" 'Nur Leere anzeigen
End With
Me.PlotVisibleOnly = True ' Ausgeblendete Zeilen weglassen
End Sub
Private Sub Chart_Deactivate()
Dim i As Integer, Spalte As Integer
With Sheets(TB)
For i = 1 To WorksheetFunction.CountIf(.Rows(1), "#Tmp#")
Spalte = WorksheetFunction.Match("#Tmp#", .Rows(1), 0)
.Columns(Spalte).Delete
Next
End With
Me.PlotVisibleOnly = False 'Standard
End Sub
LG UweD
Anzeige
AW: (D)eine Beispieldatei wäre hilfreich(er) owT
10.12.2021 16:14:26
Frank
Das löschen der Hilfsspalte funktioniert sehr gut.
So kann die Hilfsspalte nicht mit gespeichert werden wenn das Diagramm geöffnet ist und beim beenden des Programms wegen zuvor getätigte Änderungen der Datensätze gespeichert wird.
Jetzt muss ich nur noch auf die richtige Formeln für den Namensmanager kommen damit sich der Diagrammbereich verändert wenn neue Datensätze hinzukommen oder wegfallen.
Gruß Frank.
Bereich erweitern
10.12.2021 16:24:23
UweD
Hi
&GT&GT Jetzt muss ich nur noch...

das kann man doch noch mit einbauen

Dim LC As Long
Const TB As String = "TabStromEG"
Private Sub Chart_Activate()
Dim LR As Long
With Sheets(TB)
LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte A
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column 'letzte Spalte der Zeile 1
If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten
.Cells(2, LC + 2).Resize(LR - 1, 1).FormulaR1C1 = _
"=IF(AND(RC6="""",RC7=""""),""X"","""")"
' I2:   =WENN(UND($F2="";$G2="");"X";"")
.Cells(1, LC + 2) = "#TMP#"
.Columns(LC + 2).AutoFilter Field:=1, Criteria1:="=" 'Nur Leere anzeigen
Me.PlotVisibleOnly = True ' Ausgeblendete Zeilen weglassen
Me.FullSeriesCollection(1).Values = "=" & TB & "!$F$2:$F$" & LR
Me.FullSeriesCollection(2).Values = "=" & TB & "!$G$2:$G$" & LR
End With
End Sub
Private Sub Chart_Deactivate()
Dim i As Integer, Spalte As Integer
With Sheets(TB)
For i = 1 To WorksheetFunction.CountIf(.Rows(1), "#Tmp#")
Spalte = WorksheetFunction.Match("#Tmp#", .Rows(1), 0)
.Columns(Spalte).Delete
Next
End With
Me.PlotVisibleOnly = False 'Standard
End Sub
LG UweD
Anzeige
AW: Bereich erweitern
10.12.2021 16:55:48
Frank
Ok, das wusste ich nicht das es so etwas auch gibt.
Funktioniert so weit auch ganz gut, nur das Datum des neuen Datensatz wird noch nicht mit gegeben.
Zudem habe ich die Achsbeschriftung für das Datum beim Diagramm auf JJJJ umgestellt, da ich hier nur das Jahr angezeigt haben möchte.
Aber dieses funktioniert auch noch nicht, hier wird immer das komplette Datum von der Tabelle angezeigt.
AW: Bereich erweitern
11.12.2021 12:02:33
Frank
Hallo Uwe, vielen Dank für deine Unterstützung!!
Mir ist noch aufgefallen das das Datum (Spalte A) im Code nicht herangezogen wird, wie muss die Spalte A definiert werden?
Und noch eine Frage: wäre es möglich im Code noch eine max. Anzahl an Datensätze für das Diagramm vorzugeben?
Viele Grüße, Frank.
Anzeige
AW: Bereich erweitern
16.12.2021 12:24:53
Frank
Hallo,
leider habe ich es immer noch nicht hinbekommen das das Datum in der X-Achse korrekt ankommt.
Hier steht unter jedem Datensatz für Datum "01.1900" (Formatiert auf Monat.Jahr).
Wie muss in diesem Code das Datum (Spalte A) angesprochen werden?
Viele Grüße, Frank.
AW: hierzu nachgefragt ...
10.12.2021 13:45:27
neopa
Hallo Frank,
... Deine "negativ"-Werte sind ausnahmslos positiv aber ein Wert in "positiv" ist "negativ". Soll das wirklich so sein?
Für mich ist auch nicht eindeutig, was genau Du unter "Daten dynamisch im Diagramm" verstehst?
Gruß Werner
.. , - ...
AW: hierzu nachgefragt ...
10.12.2021 15:05:39
Frank
Hallo Werner,
die Werte stimmen nicht ganz da ich diese nur mal schnell von Hand zu Verdeutlichung eingegeben habe.
Unter dynamisch verstehe ich das wenn ein Datensatz hinzukommt dieser dann auch im Diagramm dargestellt wird, ebenso wenn ein Datensatz gelöscht wird dieser im Diagramm nicht mehr vorhanden ist.
Ich dachte eigentlich das könne man einfach auch ohne Code mit dem Namensmanager lösen...
Deshalb mein erster Versuch mir der Formel, aber vermutlich lässt sich dieses nicht mit den Formeln lösen ?
Gruß Frank.
Anzeige
AW: nun dann ...
10.12.2021 15:37:42
neopa
Hallo Frank,
... erklärt sich mir zumindest einiges eher. Und wenn ich Dich richtig verstanden habe, ist das was Du anstrebst, mE auch einfach zu erzielen.
Du müsstest dazu nur Deine Datenliste in eine "intelligente" Tabelle uimwandenl (mittels Funktion: Als Tabelle formatieren" und Deine Diagrammdarstellung darauf beziehen. Wenn Du dann zusätzlich noch den Autofilter einsetzt, kannst Du auch z.B. ungewollte Datensätze im Diagramm aus- und einblenden.
Gruß Werner
.. , - ...
AW: nun dann ...
10.12.2021 15:45:24
Frank
Ich denke das bei mir alles soweit passen müsste, allerdings blicke ich beim erstellen der passenden Formeln für Datum, positiv und negativ nicht so richtig durch.
Anfangs dachte ich, ich könne die Spalte B als Hilfsspalte benutzen.
Beispiel: wenn in B ein Text mit "Nein" dann verwende diese Zeile für A (Datum), F (positiv) und G (negativ)
Wie gesagt, an den Formeln haberts bei mir...
Gruß Frank.
Anzeige
AW: dies ist aber unklar ...
10.12.2021 16:08:27
neopa
Hallo Frank,
... so wie Du jetzt schreibst: 

wenn in B ein Text mit "Nein" dann verwende diese Zeile für A (Datum), F (positiv) und G (negativ)
erkenne ich nicht, welcher Wert nun wo zugeordnet sein soll. Abgesehen davon erkenne ich auch aus Deinen Datenbeispiel nicht, nach welchen Kriterien die Zuordnung Deiner Werte in Spalte F und G erfolgte und somit kann ich Dir dafür auch keine Formeln aufzeigen.
Gruß Werner
.. , - ...
AW: dies ist aber unklar ...
10.12.2021 16:20:23
Frank
Die Werte für positiv und negativ erhalte ich aus der Spalte E (Verbrauch).
Danach wird ermittelt ob der Wert höher oder niedriger zum vorherigen Wert ausfällt.
Ist der neue Wert kleiner als der vorherige = Verbrauch ist positiv
Ist der neue Wert höher als der vorherige = Verbrauch ist negativ
Die Werte werden dann im Diagramm anders farbig Dargestellt.
Werte, welche aus der Spalte F (positiv) stammen werden grün dargestellt.
Werte, welche aus der Spalte G (negativ) stammen werden rot dargestellt.
Ich hoffe es nun verständlicher beschrieben zu haben, Gruß Frank.
Anzeige
AW: dies ist aber unklar ...
10.12.2021 18:20:24
Frank
Hallo Werner,
der Text habe ich auch falsch beschrieben, richtig müsste es heißen...
wenn in B ein Text mit "Nein" dann verwende diese Zeile für A (Datum), F (positiv) ODER G (negativ)
Je nachdem ob ein Wert in F oder G steht.
Kommst du mit dieser Darstellung klar?
Gruß Frank.
AW: dies ist aber unklar ...
10.12.2021 20:47:51
Frank
Ich habe es nochmals mit den Formeln versucht:
Datum

=INDEX(TabStromEG!$A:$A;ANZAHL(TabStromEG!$A:$A)+3):INDEX(TabStromEG!$A:$A;ANZAHL(TabStromEG!$A:$A) +2)
negativ

=INDEX(TabStromEG!$F:$F;ANZAHL(TabStromEG!$E:$E)+3):INDEX(TabStromEG!$F:$F;ANZAHL(TabStromEG!$E:$E) +2)
positiv

=INDEX(TabStromEG!$G:$G;ANZAHL(TabStromEG!$E:$E)+3):INDEX(TabStromEG!$G:$G;ANZAHL(TabStromEG!$E:$E) +2)
leider funktioniert es so immer noch nicht - wo liegt der Fehler in meiner Denkweise?
Gruß Frank
Anzeige
AW: die von Dir aufgestellten Formeln ...
11.12.2021 09:52:23
Dir
Hallo Frank,
... ermitteln lediglich jeweils 2 Datenwerte und zwar die letzten beiden Datumswerte und jeweils 2 Null-Werte aus Spalte F bzw. G. Damit kannst Du das von Dir angestrebte nie erreichen. Ich hatte Dir in meinem gestrigen Beitrag um 15:37 einen allgemein beschrieben Lösungsweg aufgezeigt.
Bevor wir uns beide weiter gegenseitig nicht richtig verstehen, solltest Du Dich aber entscheiden, ob Du eine Lösung mit oder ohne VBA anstrebst. Ich kann Dir nur eine relativ einfache ohne VBA aufzeigen.
Gruß Werner
.. , - ...
AW: die von Dir aufgestellten Formeln ...
11.12.2021 10:10:34
Dir
Hallo Werner,
ich bin noch auf dem Pfad des Lernens und würde es nur mal gern verstehen wollen wie die Index-Formel ausschauen müsste, mit Bereich-Verschieben funktioniert es auch mit VBA.
Gruß Frank
AW: wie bereits geschrieben ...
11.12.2021 10:28:08
neopa
Hallo Frank,
... so wie ich Deine Zielstellung interpretiere, braucht es überhaupt keine Formeln, um auch bei Datenerweiterung die Daten im Diagramm darzustellen und zwar sowohl die "negativ" als auch die "positiv" gemeinsam in einem Diagramm oder auch getrennt und oder mit und ohne die Daten ohne Wert.
Voraussetzung dazu ist lediglich Deine Quelldaten in eine "intelligente" Tabelle zu wandel, wozu es nur dreier Mausklicks bedarf und nach der Diagrammerstellung dafür noch mit dem Autofilter der Tabelle zu arbeiten.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige