Office: Diagramm mit variabler x Achse und variablen Werte

Hallo Leute,

ich komme einfach nicht weiter, ich bin nicht so schlecht was Excel 2010 angeht, aber dennoch reicht das nicht.

Folgende Situation:

Ich habe Messungen die immer Montags eingetragen werden und ich soll daraus Diagramme erstellen, welche sich automatisch aktualisiert, wenn die Messungen am Montag eingetragen werden. Ich habe es geschafft, anhand Summewenns/Anzahlwenns und Makros, ein dynamisches Diagramm zu erstellen, welches nur im Jahr auf die Monate angezeigt wird (Durchschnittswerte). Nun möchte ich ALLE Tage pro Jahr in 1 Diagramm angezeigt bekommen. Quasi so das ich für das Jahr 2014 ein Diagramm, 2013 ein Diagramm etc. habe und das soll alles im Hintergrund ablaufen. So das ich nur die Messung eintragen brauche ohne das ich jedes mal die Diagramme erweitern muss.

Also ich habe ein Reiter, Tabelle 1, wo in Zeile 1 die Datumswerte/Messungen, ab Spalte B bis unendlich stehen und in Zeile A nur die Überschriften.

-------A------------B-------------C----------...---------Z
1:-Datum:---11.04.2014---10.04.2014---...------31.12.2013
2:-Messung:------20°----------22°---------...--------14°

Diese Tabelle wird immer erweitert. Die Spalte B wird bei einen neuem Datensatz nach rechts verschoben und in die neue Spalte b kommt die neue Messung rein.

-------A------------B----------------C--------------D---------...---------Z
1:-Datum:----12.04.2014---11.04.2014 10.04.2014-----...------31.12.2013
2:-Messung:-----23°------------20°-------------22!--------...---------14°

So mein Problem ist nicht das Diagramm variable zu machen im Sinne von n von n XPunkte, (Dies sollte mit Namen definieren und Bereich verschieben gehen) sondern, ich bekomme von meinen Eingangsdaten keine gescheite Diagrammdatenliste zusammen. Diese habe ich in einem neuen Reiter versucht zusammenzufassen, nur das bekomme ich nicht als "Funktion" "Automatisch" hin.

Also wo ich pro Zeile nur die Daten aus 2014 stehen habe und nächste Zeile nur Daten aus 2013:

-------A------------B----------------C--------------D---------...---
1:-Jahr 2014:-12.04.2014--11.04.2014---10.04.2014----...
2:-Messung:------23°-------------20°------------22°-------...
3:
4:-Jahr 2013:--31.12.2013-------...
5:-Messung:------14°-------------...
3:
4:-Jahr 2012:--31.12.2012-------...
5:-Messung:------10°-------------...

Ich versuche damit meine Diagrammdatenbereiche etwas zu vereinfachen. Im Jahr 2014 können 50 Messungen sein, aber im Jahr 2013 nur 40.
Diese einzelnen Datensätze bekomm ich einfach nicht hin in eine Tabelle zu sortieren/anzugeben.

Wenn jetzt eine neue Messung eingetragen wird im ersten Reiter, soll sich die Tabelle im zweiten Reiter auch erweitern, ohne das ich etwas machen muss. Mit Makros oder Formeln egal, wie Hauptsache es klappt :D

Ich habe versucht eine Wenn/dann Abfrage zumachen, aber dies klappt nicht.

Im Grunde müsste ich doch von den schon eingetragenen Werte ein Array raus machen und diese in dem zweiten Reiter abfragen lassen ob der neue Datensatz schon vorhanden ist und wenn nicht, dann wird dieser eingefügt. So könnte man dies doch sicherlich mit Programmieren schaffen, geht dies auch einfacher?

Vielen Dank für Eure Hilfe, wenn ich zu ungenau erklärt habe, sagt dies bitte. Dann versuch ich es erneut.

Nette Grüße,
Timo

 

Hallo Timo,

in der beiliegenden Excel-Datei habe ich Dir im Codemodul von Arbeitsblatt "Tabelle1" 2 VBA-Makros eingebaut, die auf Ereignisse im Arbeitsblatt "Tabelle1" reagieren:

1) Wenn Du im Arbeitsblatt "Tabelle1" in Zelle B1 einen Doppelklick machst und dort bereits eine Messung (Datum + Wert in Zelle B2) eingetragen ist, so wird das Ganze ab B1:B2 eine Spalte nach rechts verschoben, sodass in B1:B2 zwei Leerzellen entstehen. In diese ist sodann eine neue Messung (Datum+Wert) einzutragen.

2) Wird daraufhin die Zellmarkierung in die Zelle B3 von Arbeitsblatt "Tabelle1" verschoben, wird das 2. Makro aktiv.
2a) Es überprüft zuerst, ob die beiden Jahresangaben in Zelle Tabelle1!B1 und Zelle Jahreslisten!B1 noch gleich sind: falls nicht, werden im Arbeitsblatt "Jahreslisten" am Anfang 3 neue Zeilen eingefügt und in Jahreslisten!A1:A2 neue Jahresbeschriftungen.
2b) Es wird sodann überprüft, ob die beiden Datumsangaben in Tabelle1!B1 und Jahreslisten!B1 noch gleich sind: falls nicht, werden die Daten ab Jahreslisten!B1:B2 um 1 Spalte nach rechts verschoben und nach Jahreslisten!B1:B2 die Werte und Zahlenformate aus Tabelle1!B1:B2 übertragen.

3) Falls Du das Arbeitsblatt "Jahreslisten" umbenennen möchtest, musst Du dies auch im 2. Makro tun, damit es zu keiner Fehlermeldung kommt. Ich habe Dir die betreffende Stelle speziell gekennzeichnet durch Pfeile und Hilfetext. Um ins Codemodul von Arbeitsblatt "Tabelle1" zu gelangen, musst Du einen Rechtsklick auf den Arbeitsblatt-Reiter "Tabelle1" machen, dann den Befehl "Code anzeigen..." anklicken: dann bist Du im Codemodul von Tabelle1 - dort dann an der gekennzeichneten Stelle die Namensänderung durchführen. Ins Arbeitsblatt kommst Du dann ebenfalls mit der Tastenkombination Alt+F11 zurück.

 

Hi Anton,

tut mir Leid das ich mich so spät melde, habe erst heute Zeit gefunden weiter an meinem Hobby zuarbeiten.

Vielen Dank für deine Mühe/Arbeit, ich wusste einfach nicht weiter.

Ich glaube du hast eine falsche Datei hochgeladen. Ich kann die Datei nicht herunterladen, weil ich immer nur eine "attachment.php" bekomme. ca. 20 KB ist die groß.
Kann glaub ich keine Excel Datei sein.

Vielen Dank und Nette Grüße,
Timo

 

Hallo Timo,

falls Du Windows8 hast, erscheint
1) nach dem Klick auf den Download-Link "TimoFin_Messreihen.xlsm" eine Buttonleiste mit der Frage "Möchten Sie 'attachment.php' (19,6 KB) von 'office-hilfe.com' öffnen oder speichern?". Die 3 Buttons haben die Aufschrift "Öffnen", "Speichern", "Abbrechen". Der "Speichern"-Button ist gleichzeitig als Combobox ausgeführt.
2) Du musst also auf den Combobox-Pfeil des "Speichern"-Buttons klicken und dann aus der Liste die Befehlsoption "Speichern unter" auswählen. Daraufhin erscheint das Dateidialog-Fenster "Speichern unter", welches in der untersten Combobox "Dateityp" die Auswahlmöglichkeit für den gewünschten Dateityp bietet.
3) Dort musst Du den Dateityp von "PHP-Datei (*.php)" umstellen auf "Alle Dateien (*.*)" und in der darüber sich befindlichen Combobox "Dateiname" einen entsprechenden Dateinamen mit *.xlsm"-Dateierweiterung eingeben (zB.: TimoFin_Messreihen.xlsm).
4) Nach Auswahl des gewünschten Zielverzeichnisses klickst Du dann auf den Button "Speichern" und Du erhältst damit eine Datei mit dem gewünschten "*.xlsm"-Format, dem gewünschten Dateinamen und im gewünschten Verzeichnis.

Ich muss auch jedes Mal dieses Prozedere bei jedem Download aus diesem Forum durchführen.

 

Hi Anton,

es hat geklappt. ohje wie umständlich gelöst...

So vielen Dank wieder mal =)

Sowas habe ich gesucht. Vielen Dank. Nun muss ich das auf meine Vorhandenen Daten einbauen.

 

Hi Anton,

Frage: wie erreicht man, dass man bei einem Doppelklick auf eine Zelle mehr als 1 Target hat?

Hinweis: beim Doppelklick-Ereignis sollte man vielleicht noch Cancel = True einbauen, weil man sonst nach Doppelklick in den Editiermodus gelangt, was sicher nicht unbedingt erwünscht ist.

Bis später,
Karin

 

Hallo Beverly,

das geht theoretisch ganz einfach: Man markiere zB. Zelle B10, halte die Umschalttaste gedrückt und mache einen Doppelklick in Zelle C11. Ich musste aber aufgrund Deiner Frage und meines durchgeführten Tests dann feststellen, dass in diesem Fall überhaupt kein DoubleClick-Ereignis ausgelöst wird. - Also ist diese Zusatzabfrage in meinem Makro überflüssig, sie führt aber zu keiner Fehlfunktion des Makros.

Bezüglich Deines Hinweises: Es ist vermutlich eine Geschmacksfrage, ob der User das will. Er muss in diesem Fall ohnehin eine Eingabe in dieser Zelle machen - ob er diese in der Formeleditierzeile oder gleich direkt in der Zelle macht, bleibt sich vermutlich ziemlich egal. Aber Danke für den Hinweis, dass man damit den Zelleditiermodus ausschalten kann.

 

Hi Anton,

Genau aus diesem Grund hatte ich die Frage - zugegebener Maßen ein wenig provokatorisch :) - gestellt.

Bis später,
Karin

 

Hi Anton,

ich habe deinen Code benutzt und ein wenig verändert. Nun ist mir aufgefallen, das ich kein Copy/Paste haben möchte, sondern einen Bezug zur aktuellen Messliste. Fals ich falsch gemessen habe und es korrigieren möchte.

Das habe ich bis jetzt:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Datum1 As Date
With Target
If .Cells.Count > 1 Then Exit Sub 'falls die Markierung mehr als 1 Zelle umfasst
If .Address <> "$B$2" Then Exit Sub 'falls die markierte Zelle nicht Zelle B2 ist
If IsEmpty(.Offset(-1).Value) Then Exit Sub 'falls Zelle B1 leer ist
Datum1 = .Offset(-1).Value
End With

Dim Ws2 As Worksheet
Set Ws2 = Worksheets("LGDaten") '<--- Arbeitsblattnamensänderungen auch hier vornehmen!!!

'Überprüfe, ob Jahr in Tabelle1!B1 gleich dem Jahr in LGDaten!B1 ist:
If Year(Datum1) <> Year(Ws2.Range("B6").Value) Then
'Die beiden Datumsangaben gehören zu verschiedenen Jahren:
'Im Datenbestand von LGDaten 3 Leerzeilen einfügen:
Ws2.Range("A5:A7").EntireRow.Insert Shift:=xlShiftDown, CopyOrigin:=xlFormatFromRightOrBelow
'Beschriftung in Jahreslisten!A1:A2 einfügen:
Ws2.Range("A6").Value = "Jahr " & Year(Datum1) & ":"
Ws2.Range("A7").Value = "Messung" & Year(Datum1) & ":"
End If

If Datum1 <> Ws2.Range("B6").Value Then
Sheets("LGDaten").Select
Range("B6").Select '<----da ist mein Fehler...
ActiveCell.FormulaR1C1 = "=Tabelle1!R[-5]C"
Range("B7").Select '<----da ist mein Fehler...
ActiveCell.FormulaR1C1 = "=Tabelle1!R[3]C"
End If



Kann ich aus dem Private Sub auf das Tabellenblatt LGDaten zugreifen und den Zelleninhalt von LGDaten!B6 "= Tabelle1!B1" setzen?

Vielen Dank & nette Grüße,
Timo

edit: ich seh grad ich hab das Schieben nach rechts vergessen.

+Ws2.Range("B6:B8").Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow

 

Ok ich glaubs ich habs.

Bisher hat der Code keine ungewünschten Sachen erzeugt:

If Datum1 <> Ws2.Range("B6").Value Then
'Die beiden Datumsangaben sind verschieden:
'Daher den bisherigen Datenbestand ab LGDaten!B6:B7 eine Spalte nach rechts verschieben
Ws2.Range("B6:B7").Insert Shift:=xlShiftToRight, CopyOrigin:=xlFormatFromRightOrBelow

Ws2.Range("B6").Value = "=Tabelle1!B1"
Ws2.Range("B7").Value = "=Tabelle1!B10"

End If

 

Hallo Timo,

besser wäre statt
Ws2.Range("B6").Value = "=Tabelle1!B1"
Ws2.Range("B7").Value = "=Tabelle1!B10"
folgende Version:
Ws2.Range("B6").Formula = "=Tabelle1!B1"
Ws2.Range("B7").Formula = "=Tabelle1!B10"
Ich wusste bis jetzt gar nicht, dass ein Formelbezug offenbar sogar mit der Value-Eigenschaft (statt mit den verschiedenen Formula-Varianten) des Range-Objektes möglich ist.

 

Hi Anton,

danke, danke, danke für deine Hilfe. Ich habe mir gestern nur Gedanken darüber gemacht, wie ich etwas in die Zelle hinein schreiben kann. Mein erster Versuch mit Select hat ja nicht funktioniert und durch verschiedene Beitrage im Internet habe ich gelesen, das zu 99% der Fälle ein Select im VBA Code überflüssig sei. Daraufhin habe ich unseren Code nochmal angeschaut und bemerkt das wir die Zellen A ja auch ansprechen und dort etwas hineinschreiben und das habe ich einfach übernommen. Scheint zuklappen :D

Immoment läuft alles ohne Probleme =)

Nette Grüße,
Timo

 

Hallo Ihrs,

ich habe gerade ein kleines VBA Problem. Kann mir jmd sagen, wie ich die Anführungszeichen setzen muss???

Ws2.Range("C4").Formula = "=""KW "" &WENN(KALENDERWOCHE(B7;1)<10;""0""&KALENDERWOCHE(B7;1);KALENDERWOCHE(B7;1))"

Das ganze soll ja ein String sein, welcher in Zelle C4 so kopiert werden soll und durch die doppelten Anführungszeichen (in Rot) müsste es doch ein Unterschied zu den ersten und letzten Anführungszeichen geben (in Blau).

Im Internet steht zwar genau ein ähnliches Problem, aber ich versteh meinen Fehler nicht.

Nette Grüße,
Timo

 

Hi Timo,

die Anführungszeichen sind schon korrekt, nur darfst du nicht .Formula sondern .FormulaLocal benutzen, weil du die Formel in Deutsch eintragen willst.

Bis später,
Karin

 

Hallo Timo,

die Anführungszeichen sind nicht das Problem, sondern entweder die eingesetzte Eigenschaft des Range-Objektes oder aber die verwendeten (deutschsprachigen) Funktionsbezeichner.

Entweder muss es heißen:
Ws2.Range("C4").FormulaLocal = "=""KW "" &WENN(KALENDERWOCHE(B7;1)<10;""0""&KALENDERWOCHE(B7;1);KALENDERWOCHE(B7;1))"
oder aber (englische Funktionsnamen und Kommas statt Semikolons):
Ws2.Range("C4").Formula ="=""KW "" &IF(WEEKNUM(B7,1)<10,""0""&WEEKNUM(B7,1),WEEKNUM(B7,1))"
wobei ich die 2. Variante bevorzugen würde, da sie überall und in jeder Excel-Version funktioniert - sofern nicht die eingesetzte KALENDERWOCHE/WEEKNUM-Funktion zu falschen Ergebnissen führt!!

Eine weit einfachere Version Deiner Formel wäre:
entweder (deutsch):
Ws2.Range("C4").FormulaLocal = "= TEXT(KALENDERWOCHE(B7;1);""KW 00"")"
oder (englisch = international):
Ws2.Range("C4").Formula= " = TEXT(WEEKNUM(B7,1),""KW 00"")"
- vorausgesetzt Deine KALENDERWOCHE/WEEKNUM-Funktionsversion liefert tatsächlich korrekte Ergebnisse
(vgl. dazu die europäische Norm ISO8601 bzw. http://de.wikipedia.org/wiki/Woche )

Korrekte Ergebnisse nach europäischer Norm ISO8601 liefern folgende Funktionen (Annahme: in A1 befindet sich die Datumszelle):
bis Excel2007 (nach Franz Pölt): =KÜRZEN((A1-DATUM(JAHR(A1+3-REST(A1-2;7));1;REST(A1-2;7)-9))/7)
ab Excel2010: =KALENDERWOCHE(A1;21)
ab Excel2013: =ISOKALENDERWOCHE(A1)
bzw. die englischen Versionen:
bis Excel2007: =TRUNC((A1-DATE(YEAR(A1+3-MOD(A1-2,7)),1,MOD(A1-2,7)-9))/7)
ab Excel2010: =WEEKNUM(A1,21)
ab Excel2013: =ISOWEEKNUM(A1)