Zellbezüge anpassen an vorhandene Daten

[schauan]

Hallöchen,

also hier mal im Einsatz Mit der kompletten Formel habe ich bei den WENN die BA und BB ausgewechselt. In dem INDIREKT-Teil habe ich nur mit dem "Stückchen" VERWEIS die Spaltenberechnung für BA und BB ersetzt. Ich habe dabei vorausgesetzt, dass die jeweils letzte Spalte durch den Jahreseintrag in Zeile 1 festgelegt wird. Sobald Du 2019 einträgst, würden die Bezüge demzufolge eine Spalte nach rechts rücken, auch wenn da noch nix eingetragen ist.

Arbeitsblatt mit dem Namen 'DPS'
	BJ
3	8,1%

Zelle

Formel

BJ3

=WENN(INDEX(A3:BF3;1;VERWEIS(2;1/(A$1:BF1);SPALTE(A$1:BF$1)))="NA";WENN(INDEX(A3:BF3;1;VERWEIS(2;1/(A$1:BF$1);SPALTE(A$1:BF$1))-1)="NA";WENN(BG3>0;((AZ3/(INDIREKT(ADRESSE(ZEILE(A3);SPALTE(AZ3)-BG3))))^(1/BG3))-1;"NA"); WENN(BG3>0;((BA3/(INDIREKT(ADRESSE(ZEILE(A3);VERWEIS(2;1/(A1:BF1);SPALTE(A1:BF1))-1-BG3))))^(1/BG3))-1;"NA")); WENN(BG3>0;((BB3/(INDIREKT(ADRESSE(ZEILE(A3);VERWEIS(2;1/(A1:BF1);SPALTE(A1:BF1))-BG3))))^(1/BG3))-1;"NA"))

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

[mhryvo]

Die ganzen abfragen, ob in den jeweiligen Zellen "NA" steht, kann man nicht umgehen? Das macht das ganze ja so unflexibel und unübersichtlich. Ich verstehe jetzt nicht ganz, wozu dann die Spalte ist, wo das aktuelle Jahr drinnen steht?
Die benötigten Kernformeln stehen in der Datei im ersten Post.

[mhryvo]

Ah, nach längerem befassen mit der Formel habe ich verstanden, was du verändert hast  :19:
Das funktioniert ja schonmal sehr gut! :)

Doch wie ändere ich damit komplexe Matrix-Formeln, wie in BG und BH? 

[schauan]

Hallöchen,

wenn nur BB oder BA oder SPALTE(BB...) steht, hatte ich ja in der anderen Formel schon angepasst.

Einen Formelteil über mehrere Spalten wie z.B. bei MAX könntest Du so angehen:

=MAX(D2:INDEX(A2:BF2;1;VERWEIS(2;1/(A1:BF1);SPALTE(A2:BF2))))

Der INDEX(...) würde hier nicht mit dem Inhalt der Zelle , sondern mit dem Bereich - also der Zelladresse BB2, weiterverarbeitet.

[mhryvo]

Mir ist gerade aufgefallen, dass es mit der Herangehensweise ein Problem gibt:
Es wird ja immer die "Überschriften-Zeile" ausgewertet, ob dort ein neues Jahr dazu kommt, aber oft ist es so, dass das Jahr 2017 da steht, aber nicht für alle Unternehmen Daten für dieses Jahr da sind. D.h. die Auswertung muss individuell geschehen, also für jede Zeile muss ausgewertet werden, ob Daten vorhanden sind oder nur "NA" steht, d.h. keine Daten für das entsprechende Jahr vorhanden sind.

[schauan]

Hallöchen,

kann es da passieren, dass mehr als ein Jahr fehlt? Kann es auch passieren, dass zwischendrin was fehlt, also z.B. 2009 und sonst in allen Jahren was steht?

[mhryvo]

Also in der Regel ist es so, dass Jahres-Spalten ab 1967 vorhanden sind, aber die Daten meistens erst ab 1980 vorhanden sind bzw. ab Börsengang des Unternehmens, d.h. es fängt immer erst mit einer Reihe von "NA"s an und dann kommt eine durchgehende Reihe an Daten bis zum aktuellen Jahr. Also aktuell sind meistens Daten bis 2016 verfügbar und zum Teil auch für 2017. Aber ich habe manuell Spalten für 2018, 2019, 2020 hinzugefügt und diese mit "NA" gefüllt, damit dann bei der Aktualisierung mit Daten ersetzt werden und die Formeln dann (hoffentlich) sich daran anpassen.
Aber es kann auch mal passieren, dass nur Daten bis 2015 vorhanden sind, anstatt bis 2016 oder 2017, daran sollte sich die Formel anpassen. Wenn zwischendurch Daten fehlen, dann ist es egal, da Unternehmen wohl ziemlich irrelevant.