Microsoft Excel

Herbers Excel/VBA-Archiv

Spalten auswerten/analysieren

Betrifft: Spalten auswerten/analysieren von: erichm
Geschrieben am: 11.02.2020 13:03:14

Hallo,

ich habe eine Tabelle mit folgenden "Grunddaten":
Spalte A: verschiedene Nummerierungen
Spalte B: Zellen leer oder mit "aktiv" belegt
Spalten H bis BD: jeweils mit verschiedenen Zahlen oder Werten befüllt (in der Musterdatei nur mit JA oder NEIN); die Spalten sind derzeit von H bis AS so befüllt; von AT bis BD steht immer "999"; die Spalten AT bis BD werden nach und nach pro Spalte ebenfalls mit JA oder NEIN befüllt

Diese Grunddaten sollen wie folgt pro Zeile ausgewertet werden:
Spalte BE: welches ist der letzte Wert vor der Spalte mit "999"; dies habe ich bereits gelöst mit der Namensformel für BE2:
letzter: =VERWEIS(9;1/(!$A2:$BD2<>999);!2:2)

Spalte BF: wie oft ist der Wert von BE2 zuletzt direkt hintereinander vorgekommen; Lösung mit der Formel in BF2:
=WENN(B2<>"aktiv";"";AGGREGAT(14;6;SPALTE($A2:$BD2)/($A2:$BD2=letzter);1)-AGGREGAT(14;6; SPALTE($A2:INDEX($A2:$BD2;AGGREGAT(14;6;SPALTE($A2:$BD2)/($A2:$BD2=letzter);1)))/($A2:$BD2<>letzter); 1))

Spalte BG: wie oft ist der Wert von BE2 in den Spalten ab H insgesamt vorgekommen; Lösung mit der Formel in BG 2:
=WENN(B2<>"aktiv";"";ZÄHLENWENN(H2:BD2;letzter))

Spalte BH: wie oft hat sich der Wert von BE2 in den Spalten ab H direkt hintereinander wiederholt; Lösung mit der Formel in BH 2:
=WENN(B2<>"aktiv";"";ZÄHLENWENNS($G2:$BC2;letzter;$H2:$BD2;letzter))

Diese Analysen in BE bis BH sollen jetzt mehrmals wiederholt werden, wobei immer um eine Spalte mehr zurückgeschaut wird. Also vorletzter Wert, drittletzter Wert, viertletzter Wert usw...

In der Musterdatei befindet sich vorbereitet dies in den Spalten
CQ-CT, vorletzter Wert
DG-DJ, drittletzter Wert
DW-DZ, viertletzter Wert
EL-EO, fünftletzter Wert
FB-FE, sechstletzter Wert

Die Lösung für den vorletzten Wert habe ich gefunden und ist in der Musterdatei bereits enthalten.

Bei den weiteren Prüfungen konnte ich immer nur den "jeweils letzten Wert" ermitteln, durch eine Änderung der Namensformel.
Für die weiter betroffenen Spalten
EINFACH
ANZ
WDH
sind alle meine Versuche leider gescheitert.....
Hier bitte ich um Unterstützung - VIELEN DANK!

hier die Musterdatei (mit Office 365):
https://www.herber.de/bbs/user/135126.xlsx

mfg

Betrifft: AW: nachgefragt ...
von: neopa C
Geschrieben am: 11.02.2020 13:42:13

Hallo Erich,

... ohne mich jetzt mit Deiner Aufgabenstellung tiefer vertraut gemacht zu haben.
So wie ich Deine Daten im Beispiel sehe, stehen die letzten Werte immer in Spalte AS. Wenn das grundsätzlich so ist, dann lassen sich alle Auswertungen vereinheitlichen wie vereinfachen.

Gruß Werner
.. , - ...

Betrifft: AW: nachgefragt ...
von: erichm
Geschrieben am: 11.02.2020 13:57:23

Hallo Werner,

die Spaltenbefüllung ändert sich nach und nach, dies sollte mein Hinweis im 1. Beitrag zum Ausdruck bringen:
"....die Spalten AT bis BD werden nach und nach pro Spalte ebenfalls mit JA oder NEIN befüllt."

Also ändert sich mit jeder neuen Spaltenbefüllung jeweils die "Bezugsspalte".

mfg

Betrifft: AW: soweit klar, jedoch ...
von: neopa C
Geschrieben am: 11.02.2020 14:10:02

Hallo Erich,

... meine Frage sollte mehr darauf abzielen, ob jeweils alle Datensätze, das gleiche "Datenende" haben. So liest sich jetzt auch Deine Antwort: "...die Spalten AT bis BD werden nach und nach pro Spalte ebenfalls mit JA oder NEIN befüllt." Danach ist immer eine Spalte für alle Datensätze das Ende der Befüllung, oder?

Gruß Werner
.. , - ...

Betrifft: AW: soweit klar, jedoch ...
von: erichm
Geschrieben am: 11.02.2020 14:54:58

JA genau; es gibt keine Mischung in einer Spalte von 999 und anderen Werten.

Wenn eine Spalte in der Zeile 2 mit 999 beginnt, dann ist die gesamte Spalte mit 999 befüllt.

Wenn eine Spalte mit einem befüllten Wert (z. B. JA oder NEIN)beginnt, dann gibt es in dieser Spalte keine 999.

mfg

Betrifft: AW: dann reicht es doch auch aus, ...
von: neopa C
Geschrieben am: 11.02.2020 15:41:51

Hallo Erich,

... das man in einer (Hilfs-)Zelle den Spaltenwerte der letzte Datenzelle bestimmt und diese demAuswertungsbereich z.B. mit INDEX() zu Grunde legt, den man dann immer lediglich um 1 reduzieren muss. Das vereinheitlicht und vereinfacht doch dann alle Formeln.

Gruß Werner
.. , - ...

Betrifft: AW: dann reicht es doch auch aus, ...
von: erichm
Geschrieben am: 11.02.2020 19:17:38

DANKE für den Hinweis; werde ich morgen mal testen / üben.

mfg

Betrifft: erledigt - mit Alternative
von: erichm
Geschrieben am: 13.02.2020 18:07:59

Hallo Werner,

nach Deiner Idee mit der Fixierung des Spaltenwerts habe ich herausgefunden, dass ich weitere Auswertungen im Anschluss nochmals vereinfachen kann. Ich habe mir jetzt eine Hilfstabelle erstellt, bei der ich gesuchten Werte EINFACH, ANZ, WDH jeweils für alle Zeilen "untereinander" mit den bestehenden Formeln ermittelt habe.
Jetzt bin ich nochmals flexibler, tue mich leichter wenn die Spalten nach rechts neu befüllt werden und ich kann sogar problemlos die Anzahl der auszuwertenden Spalten schnell ändern.
Hat jetzt zwar etwas gedauert, bringt aber doch eine deutliche Zeitersparnis und mehr Transparenz für die Zukunft.

Besten Dank für die Unterstützung.

mfg

Betrifft: AW: bitte, freut mich auch owT
von: neopa C
Geschrieben am: 13.02.2020 19:46:02

Gruß Werner
.. , - ...

Beiträge aus dem Excel-Forum zum Thema "Spalten auswerten/analysieren"