Formatierung kopieren

Bild

Betrifft: Formatierung kopieren
von: DanielMa
Geschrieben am: 09.11.2015 11:34:34

Hallo liebes Forum,
mein VBA ist leider etwas eingerostet und ich stehe momentan etwas auf dem Schlauch.
Folgendes Problem:
Ich habe zwei Tabellenblätter. Das eine dient zur Dateneintragung. Hier habe ich eine Matrix, in welcher Jahre als Spalten und Nummern als Zeilen stehen.
In einem zweiten Arbeitsblatt werden dann die eingegeben Daten verrechnet und angezeigt. Hierfür wird wieder die selbe Matrix dargestellt und die Zellen haben eine einfache Formel:
=WENN(UND(Daten!B5=""; Daten!C5="");""; MITTELWERT(Daten!B5:C5))
(Natürlich ändern sich pro Zeile/Spalte die Zellverweise)
Ihr seht, dass in der ersten Matrix etwas mehr an Daten eingegeben werden können, die dann in der zweiten Matrix verrechnet als einfacher Wert dargestellt werden.
Hieraus wird später ein Diagramm erstellt.
Das funktioniert soweit super. Jetzt kann es aber sein, dass ich im ersten Arbeitsblatt die Nummern der Matrix ändern muss oder neue hinzukommen bzw alte wegfallen. Natürlich soll die zweite Matrix dementsprechend angepasst werden. Auch das würde ich wohl noch hinbekommen.
Jetzt zum Problem: ändert sich die Anzahl an eingetragenen Nummern, so müssen auch die dahinterstehenden Zellen mit der Formel/Formatierung übernommen werden, damit die Verrechnung stattfindet und das Diagramm erstellt wird.
Könnt ihr mir hierzu eine Lösung liefern?
Falls das Problem nicht ausführlich genug beschrieben ist, kann ich auch eine Beispielmappe erstellen.
Vielen Dank im Voraus,
Daniel

Bild

Betrifft: AW: ist so nicht eindeutig ...
von: ... neopa C
Geschrieben am: 09.11.2015 12:18:29
Hallo Daniel,
... eine Beispielmappe könnte da evtl. helfen.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Beispielmappe
von: DanielMa
Geschrieben am: 09.11.2015 13:02:52
Hallo Werner,
...ist nun erstellt und beigefügt. Auch nochmal mit einer kurzen Erklärung im Dokument :)
https://www.herber.de/bbs/user/101335.xlsx
Danke und Gruß,
Daniel

Bild

Betrifft: AW: nachgefragt ...
von: ... neopa C
Geschrieben am: 09.11.2015 13:38:37
Hallo Daniel,
... ich erkenne momentan nicht Deine Problem. Oder, meinst Du mit Daten hinzufügen in Tabelle 1, dass eine V3 etc je Jahr dazu gefügt werden könnte? Oder?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: nachgefragt ... und erklärt
von: DanielMa
Geschrieben am: 09.11.2015 13:49:33
Hallo Werner,
ich versuche den Vorgang mal Schritt für Schritt anhand der Beispielmappe zu erklären.
Fall 1: Eine "Nr." hat sich geändert:
Ich ändere die "Nr. 89" in Tabelle 1 ab, da es diese interne Nummer nicht mehr gibt.
Kein Problem, da ich die neue Nummer auch einfach in die Matrix in Tabelle 2 kopieren kann
Fall 2: Eine neue Nummer muss ergänzt werden:
Hierzu schreibe ich jetzt in Tabelle 1 die (z.B.) 823 ans Ende der Matrix. Anschließend können die Felder rechts davon wieder mit Werten befüllt werden. Die 823 kann ich auch in Tabelle 2 kopieren lassen (ginge ja einfach durch ein Change Event). Doch das Problem ist, dass zwar die 823 jetzt mit in der Matrix in Tabelle 2 stehen würde, hintendran jedoch noch nicht die Formel zur Übernahme bzw. Mittelwertbildung der Werte aus Tabelle 1.
Meine Frage richtet sich also daran, mit welchem Code ich die Formel in die neuen Zeilen hinzufügen kann.
Die Formel soll also dynamisch immer an den Umfang der Matrix angepasst werden.
Sind es 10 eingetragene Nummern, so müssen sich die Formeln über 10 Zeilen erstrecken. Sind es 25, dann über 25 usw.
Ich hoffe, dass das mein Problem genauer beschreibt :)
Lieber Gruß,
Daniel

Bild

Betrifft: AW: so wie Du jetzt schreibst ...
von: ... neopa C
Geschrieben am: 09.11.2015 14:49:31
Hallo Daniel,
... kommen also je Jahr immer nur max. zwei Werte zur Auswertung und nicht 3 oder mehre.
Dann in Tabelle2!A4 folgende Formel:
=WENN(Tabelle1!A4;Tabelle1!A4;"") und Formel bis z.B. A29 herunter kopiert.
Und in Tabelle2!B4 folgende erweiterte Formel:

=WENN((B$1="")+(ANZAHL(INDEX(Tabelle1!4:4;
ANZAHL($A$1:B$1)+SPALTE(A1)):INDEX(Tabelle1!4:4;ANZAHL($A$1:B$1)+SPALTE(A1)*2))=0);"";
MITTELWERT(INDEX(Tabelle1!4:4;ANZAHL($A$1:B$1)+SPALTE(A1)):INDEX(Tabelle1!4:4;
ANZAHL($A$1:B$1)+SPALTE(A1)*2)))
Dafür kannst Du diese Formel nach rechts und unten (auch bis z.B. G29) ziehend kopieren.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: vielen Dank
von: DanielMa
Geschrieben am: 09.11.2015 15:32:59
Hallo Werner,
...für die Formel. Sie löst das Problem wie gewünscht in der Beispielmappe. Ich versuche mal, die Formel auf mein Dokument umzuschreiben und melde mich, falls ich damit Probleme habe.
Ansonsten, vielen lieben Dank :)
Daniel

Bild

Betrifft: AW: Umformulierung
von: DanielMa
Geschrieben am: 09.11.2015 16:02:06
Hallo Werner,
ich kann deine Formel leider nicht auf mein Dokument anwenden. Ich habe noch nie mit Index gearbeitet und Google verschafft mir momentan auch noch keine Klarheit.
Ich habe dir die Beispielmappe nochmal hochgeladen, diesmal aber mit genau der Positionierung, wie sie bei mir im Dokument vorliegt (Achtung, Tabellenblätter nun vertauscht).
Könntest du mir deine Formel nochmal anpassen?
Ich wäre dir sehr dankbar! Auch über eine kurze Erklärung der Formel.
https://www.herber.de/bbs/user/101351.xlsx
Vielen lieben Dank,
Daniel

Bild

Betrifft: AW: diese muss konsequent durchgeführt werden ...
von: ... neopa C
Geschrieben am: 09.11.2015 17:29:23
Hallo Daniel,
... nun in Tabelle1!B63: =WENN(Tabelle2!A5;Tabelle2!A5;"") und nach unten kopieren.
Und in Tabelle1!C63:
=WENN((C$62="")+(ANZAHL(INDEX(Tabelle2!5:5;ANZAHL($A$62:C$62)+SPALTE(A1)):
INDEX(Tabelle2!5:5;ANZAHL($A$62:C$62)+SPALTE(A1)*2))=0);"";
MITTELWERT(INDEX(Tabelle2!5:5;ANZAHL($A$62:C$62)+SPALTE(A1)):INDEX(Tabelle2!5:5;
ANZAHL($A$62:C$62)+SPALTE(A1)*2)))
und diese nach rechts und unten kopieren.
Gruß Werner
.. , - ...

Bild

Betrifft: Formel passt, aber...
von: DanielMa
Geschrieben am: 10.11.2015 09:38:02
Hallo Werner,
...löst leider das Problem doch nicht ganz.
Zunächst aber vielen Dank für deine Hilfe!
Ich konnte die Formel von dir anwenden und sie funktioniert auch. Jedoch ergibt sich ein Problem:
Füge ich in der Tabelle 2 eine neue Zeile ein, um eine neue Nummer hinzuzufügen, die chronologisch dort stehen muss, dann wird diese neue Zeile nicht mit in Tabelle 1 übernommen.
Gibt es hierfür eine Lösung?
Falls nein, würde es sich sonst mit VBA lösen lassen?
Vielen Dank und Gruß,
Daniel
(..und fertig ist das Mondgesicht?)

Bild

Betrifft: AW: nun dann die Formeln noch "dynamisiert" ...
von: ... neopa C
Geschrieben am: 10.11.2015 10:45:44
Hallo Daniel,
... und zwar mit der Funktion INDEX().
In Tabelle1!B63: =INDEX(Tabelle2!A:A;ZEILE(Tabelle2!$A$4)+ZEILE(A1))
Und in Tabelle1!C63:
=WENN((C$62="")+(ANZAHL(INDEX(Tabelle2!$A:$G;ZEILE(Tabelle2!$A$4)+ZEILE(A1);
ANZAHL($A$62:C$62)+SPALTE(A1)):INDEX(Tabelle2!$A:$G;ZEILE(Tabelle2!$A$4)+ZEILE(A1);
ANZAHL($A$62:C$62)+SPALTE(A1)*2))=0);"";MITTELWERT(INDEX(Tabelle2!$A:$G;ZEILE(Tabelle2!$A$4)+ZEILE(A1);
ANZAHL($A$62:C$62)+SPALTE(A1)):INDEX(Tabelle2!$A:$G;ZEILE(Tabelle2!$A$4)+ZEILE(A1);
ANZAHL($A$62:C$62)+SPALTE(A1)*2)))

und diese nach rechts und unten kopieren.
Gruß Werner
.. , - ...

Bild

Betrifft: "dynamisiert" klappt perfekt
von: DanielMa
Geschrieben am: 10.11.2015 10:52:42
Hallo Werner,
vielen vielen Dank, dass du das so schnell umgesetzt hast.
Jetzt ist das Problem perfekt gelöst!
Hab noch einen schönen Tag,
Gruß, Daniel

Bild

Betrifft: Thema leider nochmal offen
von: DanielMa
Geschrieben am: 10.11.2015 11:47:40
Hallo Werner,
zwar funktioniert deine tolle Formel in der Beispielmappe, jedoch habe ich Probleme bei meiner eigenen Darstellung.
Das liegt daran, dass ich in der Beispielmappe die Tabelle nicht exakt so dargestellt hatte, wie sie bei mir vorliegt. Denn ich habe noch vier weitere Spalten je Jahr in der "Tabelle2". Diese Daten werden jedoch nicht für das Diagramm gebraucht und müssen daher nicht kopiert werden, daher hatte ich diese Spalten weggelassen. Ein Fehler, wie mir jetzt klar wird.
Bitte entschuldige mein unüberlegtes Vorgehen.
Lässt sich das Problem durch eine Parameterangleichung in der Formel lösen?
https://www.herber.de/bbs/user/101378.xlsx
Die angepasste Beispielmappe, die exakt meinen Tabellenaufbau darstellt.
Viele Grüße,
Daniel

Bild

Betrifft: AW: dann anstelle ANZAHL() mit VERGLEICH() ...
von: ... neopa C
Geschrieben am: 10.11.2015 12:17:01
Hallo Daniel,
... nun fehlt evtl. noch immer eine Aussage und zwar ob der Mittelwert immer nur über die ersten zwei Spalten vorgenommen werden soll (dann siehe nachfolgende Formel) oder über alle 6 des Jahres. Dann sollte die Formelanpassung relativ einfach sein.
Nun in Tabelle1!C63 für Auswertung jeweils max. zwei Werte:
=WENN((C$62="")+(ANZAHL(INDEX(Tabelle2!$A:$S;ZEILE(Tabelle2!$A$4)+ZEILE(A1);VERGLEICH(C$62;Tabelle2!$2:$2;)):INDEX(Tabelle2!$A:$S;ZEILE(Tabelle2!$A$4)+ZEILE(A1);VERGLEICH(C$62;Tabelle2!$2:$2)+1))=0);"";MITTELWERT(INDEX(Tabelle2!$A:$S;ZEILE(Tabelle2!$A$4)+ZEILE(A1);VERGLEICH(C$62;Tabelle2!$2:$2;)):INDEX(Tabelle2!$A:$S;ZEILE(Tabelle2!$A$4)+ZEILE(A1);VERGLEICH(C$62;Tabelle2!$2:$2;)+1)))
Gruß Werner
.. , - ...

Bild

Betrifft: Lösung passt 100%
von: DanielMa
Geschrieben am: 10.11.2015 13:02:28
Hallo Werner,
ich bin wirklich beeindruckt, wie schnell und nett du mir geholfen hast. Vielen Dank!
Nun konnte ich die Formel (noch mit ein paar kleinen Anpassungen) in mein Dokument einfügen und sie funktioniert wunderbar! Auch wenn ich den ganzen Aufbau der Formel leider nicht nachvollziehen kann.
Ich bedanke mich und wünsche dir noch einen schönen Tag.
Viele Grüße,
Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formatierung kopieren"