Maximalwert im Index-Bereich ermitteln

[Ralf Schönberger]

Hallo zusammen,

ich brüte über folgendes Problem: Über Index und Vergleich möchte ich
den Maximalwert aus dem Index-Bereich ermitteln. In Spalte A habe ich
ein Datum, in Spalte B Zahlen. Wenn ich zu einem Datum nur eine Zahl
habe schreibe ich in C=INDEX($B:$B;VERGLEICH(A1;$A:$A;0))

Jetzt kann aber ein Datum in A doppelt oder mehrfach auftauchen mit
jeweils einer Zahl in Spalte B, von denen ich aber nur den grössten
Wert aus B in C haben möchte.

Ich habe schon mit INDEX(MAX... experimentiert, komme aber zu keinem
Ergebnis. Arrays kann ich wegen Performance leider nicht anwenden.

Hat jemand eine Idee?

Danke und Grüsse
Ralf

[Klaus "Perry" Pago]

Post by Ralf Schönberger
Hallo zusammen,
ich brüte über folgendes Problem: Über Index und Vergleich möchte ich
den Maximalwert aus dem Index-Bereich ermitteln. In Spalte A habe ich
ein Datum, in Spalte B Zahlen. Wenn ich zu einem Datum nur eine Zahl
habe schreibe ich in C=INDEX($B:$B;VERGLEICH(A1;$A:$A;0))
Jetzt kann aber ein Datum in A doppelt oder mehrfach auftauchen mit
jeweils einer Zahl in Spalte B, von denen ich aber nur den grössten
Wert aus B in C haben möchte.
Ich habe schon mit INDEX(MAX... experimentiert, komme aber zu keinem
Ergebnis. Arrays kann ich wegen Performance leider nicht anwenden.
Hat jemand eine Idee?

Hallo Ralf,

ohne Matrixformel bleibt dir IMO dann nur eine Hilfsspaltenlösung (oder
VBA).

C1 =WENN($A$1=A1,B1;"") und herunterkopieren. Mit MAX() bekommst du dann
einen Maximalwert und mit VERGLEICH() den Index für INDEX()

Gruß
Klaus

[Klaus "Perry" Pago]

war aus der Hand - das Komma muss natürlich ein Semikolon sein.

Klaus

[{Boris}]

Post by Ralf Schönberger
Hallo zusammen,
ich brüte über folgendes Problem: Über Index und Vergleich möchte ich
den Maximalwert aus dem Index-Bereich ermitteln. In Spalte A habe ich
ein Datum, in Spalte B Zahlen. Wenn ich zu einem Datum nur eine Zahl
habe schreibe ich in C=INDEX($B:$B;VERGLEICH(A1;$A:$A;0))
Jetzt kann aber ein Datum in A doppelt oder mehrfach auftauchen mit
jeweils einer Zahl in Spalte B, von denen ich aber nur den grössten
Wert aus B in C haben möchte.
Ich habe schon mit INDEX(MAX... experimentiert, komme aber zu keinem
Ergebnis. Arrays kann ich wegen Performance leider nicht anwenden.
Hat jemand eine Idee?
Danke und Grüsse
Ralf

Hi Ralf,

sortiere die Spalten A und B:
Spalte A auf- oder absteigend (ist egal) und gleichzeitig nach Spalte
B "ABsteigend".
Dann bekommst Du den Maximalwert zu jedem Datum mit einem einfachen
SVERWEIS (oder auch mit der Kombination aus INDEX und VERGLEICH).

--
Grüße Boris
www.excelformeln.de

[Ralf Schönberger]

Post by {Boris}

Post by Ralf Schönberger
Hallo zusammen,
ich brüte über folgendes Problem: Über Index und Vergleich möchte ich
den Maximalwert aus dem Index-Bereich ermitteln. In Spalte A habe ich
ein Datum, in Spalte B Zahlen. Wenn ich zu einem Datum nur eine Zahl
habe schreibe ich in C=INDEX($B:$B;VERGLEICH(A1;$A:$A;0))
Jetzt kann aber ein Datum in A doppelt oder mehrfach auftauchen mit
jeweils einer Zahl in Spalte B, von denen ich aber nur den grössten
Wert aus B in C haben möchte.
Ich habe schon mit INDEX(MAX... experimentiert, komme aber zu keinem
Ergebnis. Arrays kann ich wegen Performance leider nicht anwenden.
Hat jemand eine Idee?
Danke und Grüsse
Ralf

Hi Ralf,
Spalte A auf- oder absteigend (ist egal) und gleichzeitig nach Spalte
B "ABsteigend".
Dann bekommst Du den Maximalwert zu jedem Datum mit einem einfachen
SVERWEIS (oder auch mit der Kombination aus INDEX und VERGLEICH).
--
Grüße Boriswww.excelformeln.de- Zitierten Text ausblenden -
- Zitierten Text anzeigen -

Hallo zusammen,

ich glaube nicht, dass ich mit SVERWES oder INDEX VERGLEICH nur den
MAX-Wert zu jedem Datum finden kann...

Vielleicht kennt jemand eine SUMMENPRODUKT Lösung oder ist die genauso
Performanceintensiv wie ein Array...?

Danke und Grüsse
Ralf

[Klaus "Perry" Pago]

Post by Ralf Schönberger

Post by {Boris}
Hi Ralf,
Spalte A auf- oder absteigend (ist egal) und gleichzeitig nach Spalte
B "ABsteigend".
Dann bekommst Du den Maximalwert zu jedem Datum mit einem einfachen
SVERWEIS (oder auch mit der Kombination aus INDEX und VERGLEICH).
--
Grüße Boriswww.excelformeln.de- Zitierten Text ausblenden -
- Zitierten Text anzeigen -

Hallo zusammen,
ich glaube nicht, dass ich mit SVERWES oder INDEX VERGLEICH nur den
MAX-Wert zu jedem Datum finden kann...
Vielleicht kennt jemand eine SUMMENPRODUKT Lösung oder ist die genauso
Performanceintensiv wie ein Array...?

Hallo Ralf,

wenn du es so machst, wie Boris es empfiehlt, erst sortieren und dann
SVERWEIS oder INDEX und VERGLEICH, dann klappt das, hat nichts mit glauben
zu tun ==> einfach mal ausprobieren.

SUMMENPRODUKT ist eine Matrixformel mit ähnlich zweitaufwendiger Abarbeitung
wie andere Matrixformeln auch.

Gruß
Klaus

[Thomas Ramel]

Grüezi Ralf

Ralf Schönberger schrieb am 22.07.2007

Post by Ralf Schönberger

Post by {Boris}

Post by Ralf Schönberger
ich brüte über folgendes Problem: Über Index und Vergleich möchte ich
den Maximalwert aus dem Index-Bereich ermitteln. In Spalte A habe ich
ein Datum, in Spalte B Zahlen. Wenn ich zu einem Datum nur eine Zahl
habe schreibe ich in C=INDEX($B:$B;VERGLEICH(A1;$A:$A;0))
Jetzt kann aber ein Datum in A doppelt oder mehrfach auftauchen mit
jeweils einer Zahl in Spalte B, von denen ich aber nur den grössten
Wert aus B in C haben möchte.
Arrays kann ich wegen Performance leider nicht anwenden.
Hat jemand eine Idee?

Spalte A auf- oder absteigend (ist egal) und gleichzeitig nach Spalte
B "ABsteigend".
Dann bekommst Du den Maximalwert zu jedem Datum mit einem einfachen
SVERWEIS (oder auch mit der Kombination aus INDEX und VERGLEICH).

ich glaube nicht, dass ich mit SVERWES oder INDEX VERGLEICH nur den
MAX-Wert zu jedem Datum finden kann...

Doch das geht mit dem Vorschlag von Boris ohne weiteres - den ç. Parameter
musst Du dann allerdings auf '0' setzen.

Post by Ralf Schönberger
Vielleicht kennt jemand eine SUMMENPRODUKT Lösung oder ist die genauso
Performanceintensiv wie ein Array...?

Summenprodukt wird sich in etwa gleich verhalten, da es sich dabei
ebenfalls um eine Matixformel handelt.


Wenn dir die Performance am Herzen liegt und Du die Daten nicht so sortiern
kanns/willst, wie Boris vorschlägt, dann verwende doch eine Pivot-Tabelle.

Ziehe das Datum in den Zeilenbereich und deinen Wert in den Datenbereich -
wähle dann noch 'Maximum' anstelle von 'Summe' als Aggregierungsfunktion
und Du hast die ganze Auswertung vollzogen. Es ist nicht notwendig die
Daten zu sortieren und du musst auch nicht die einzelnen Datumswerte zuvor
extrahieren - all das macht die Pivot-Tabelle selbst.


Mit freundlichen Grüssen
Thomas Ramel

[Ralf Schönberger]

Post by Thomas Ramel
Grüezi Ralf
Ralf Schönberger schrieb am 22.07.2007

Post by Ralf Schönberger

Post by {Boris}

Post by Ralf Schönberger
ich brüte über folgendes Problem: Über Index und Vergleich möchte ich
den Maximalwert aus dem Index-Bereich ermitteln. In Spalte A habe ich
ein Datum, in Spalte B Zahlen. Wenn ich zu einem Datum nur eine Zahl
habe schreibe ich in C=INDEX($B:$B;VERGLEICH(A1;$A:$A;0))
Jetzt kann aber ein Datum in A doppelt oder mehrfach auftauchen mit
jeweils einer Zahl in Spalte B, von denen ich aber nur den grössten
Wert aus B in C haben möchte.
Arrays kann ich wegen Performance leider nicht anwenden.
Hat jemand eine Idee?

Spalte A auf- oder absteigend (ist egal) und gleichzeitig nach Spalte
B "ABsteigend".
Dann bekommst Du den Maximalwert zu jedem Datum mit einem einfachen
SVERWEIS (oder auch mit der Kombination aus INDEX und VERGLEICH).

ich glaube nicht, dass ich mit SVERWES oder INDEX VERGLEICH nur den
MAX-Wert zu jedem Datum finden kann...

Doch das geht mit dem Vorschlag von Boris ohne weiteres - den ç. Parameter
musst Du dann allerdings auf '0' setzen.

Post by Ralf Schönberger
Vielleicht kennt jemand eine SUMMENPRODUKT Lösung oder ist die genauso
Performanceintensiv wie ein Array...?

Summenprodukt wird sich in etwa gleich verhalten, da es sich dabei
ebenfalls um eine Matixformel handelt.
Wenn dir die Performance am Herzen liegt und Du die Daten nicht so sortiern
kanns/willst, wie Boris vorschlägt, dann verwende doch eine Pivot-Tabelle.
Ziehe das Datum in den Zeilenbereich und deinen Wert in den Datenbereich -
wähle dann noch 'Maximum' anstelle von 'Summe' als Aggregierungsfunktion
und Du hast die ganze Auswertung vollzogen. Es ist nicht notwendig die
Daten zu sortieren und du musst auch nicht die einzelnen Datumswerte zuvor
extrahieren - all das macht die Pivot-Tabelle selbst.
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-2]
Microsoft Excel - Die ExpertenTipps- Zitierten Text ausblenden -
- Zitierten Text anzeigen -

Hallo zusammen,

das Problem ist, dass die Tabelle von einer Software mit Daten gefüllt
wird ohne dass ich da anschliessend noch eingreifen kann. Nach dem
Datenimport sollen die Auswertungen schon explizit da sein. Ich kann
auch nichts sortieren und kann somit über Verweis oder Index-Vergleich
nicht weiterkommen, wie ich oben schrieb.

Die Pivot Tabelle müsste auch jedesmal neu definiert werden, weil der
Datenbereich sich beim Datenimport ändert.

Ich habe über einen verschachtelten Index-Verweis nachgedacht, um
einen zweiten Vergleich zu starten. Das Ergebnis der ersten Suche wird
als Startindex für eine zweite Suche definiert, vielleicht etwa so:

INDEX(INDEX($B:$B;VERGLEICH(A1;$A:$A;0)):$B;VERGLEICH(A1;$A:$A;0))

Das ist aber nur eine erste Idee...
In der Regel erwarte ich in B nur 1 oder 2 Zahlen, wobei die grössere
ausgegeben werden muss...

Danke und Grüsse
Ralf

[Claus Busch]

Hallo Ralf,

Post by Ralf Schönberger
das Problem ist, dass die Tabelle von einer Software mit Daten gefüllt
wird ohne dass ich da anschliessend noch eingreifen kann. Nach dem
Datenimport sollen die Auswertungen schon explizit da sein. Ich kann
auch nichts sortieren und kann somit über Verweis oder Index-Vergleich
nicht weiterkommen, wie ich oben schrieb.
Die Pivot Tabelle müsste auch jedesmal neu definiert werden, weil der
Datenbereich sich beim Datenimport ändert.

du benötigst für deine Auswertung nur Spalte A und Spalte B. Dann vergebe
doch einen dynamischen Bereichsnamen mit Einfügen => Name => definieren.
Name: Pivot_Daten
bezieht sich auf:
=BEREICH.VERSCHIEBEN(Tabelle1!$A$1;;;ANZAHL2(Tabelle1!$A:$A);2)
Dann erstellst du deine Pivot-Tabelle, indem du als Quelle =Pivot_Daten
angibst. Damit ist der Bereich dynamisch und die Pivot-Tabelle passt sich
immer deiner Datenmenge an. Weitere Vorgehensweise hat Thomas schon
beschrieben.

[Thomas Ramel]

Grüezi Ralf

Ralf Schönberger schrieb am 22.07.2007

Post by Ralf Schönberger

Post by Thomas Ramel
Wenn dir die Performance am Herzen liegt und Du die Daten nicht so sortiern
kanns/willst, wie Boris vorschlägt, dann verwende doch eine Pivot-Tabelle.

Die Pivot Tabelle müsste auch jedesmal neu definiert werden, weil der
Datenbereich sich beim Datenimport ändert.

Mit dem dynamischen Bereichsnamen (danke Claus) kannst Du das elegant
umgehen.

Das Sahnehäubchen quasi könntest Du noch mit ein paar wenigen VBA-Zeilen
draufsetzen die bewirken, dass die Pivot-Tabelle automatisch aktualisiert
wird, wenn sich die Daten im Quellbereich verändern.

Die folgende Zeile aktualisiert die erste Pivot-Tabelle des aktiven
Tabellenblattes:

ActiveSheet.PivotTables(1).RefreshTable




Mit freundlichen Grüssen
Thomas Ramel

[Ralf Schönberger]

Post by Thomas Ramel
Grüezi Ralf
Ralf Schönberger schrieb am 22.07.2007

Post by Ralf Schönberger

Post by Thomas Ramel
Wenn dir die Performance am Herzen liegt und Du die Daten nicht so sortiern
kanns/willst, wie Boris vorschlägt, dann verwende doch eine Pivot-Tabelle.

Die Pivot Tabelle müsste auch jedesmal neu definiert werden, weil der
Datenbereich sich beim Datenimport ändert.

Mit dem dynamischen Bereichsnamen (danke Claus) kannst Du das elegant
umgehen.
Das Sahnehäubchen quasi könntest Du noch mit ein paar wenigen VBA-Zeilen
draufsetzen die bewirken, dass die Pivot-Tabelle automatisch aktualisiert
wird, wenn sich die Daten im Quellbereich verändern.
Die folgende Zeile aktualisiert die erste Pivot-Tabelle des aktiven
ActiveSheet.PivotTables(1).RefreshTable
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-2]
Microsoft Excel - Die ExpertenTipps

Hallo zusammen,

ich hatte das Problem stark vereinfacht um es schnell verständlich zu
machen. Genaugenommen habe ich in Spalte B fortlaufendes Datum für
eine Woche, dass in der Regel auch mehrfach untereinander gleich ist.
Der Datenbereich liegt in CL bis DI, und ist mit Zeiten beschriftet:
00 bis 23. Ausgabebereich soll in AE bis BB sein, ebenfalls mit 00 bis
23 beschriftet. Auf diesen Ausgabebereich greifen weitere Berechnungen
zu. Eine Formel hätte ich einfach nach rechts gezogen. Zu jedem neuen
Datum wird in CL bis DI jeweils eine Zahl ausgegeben. Nun kann es
vorkommen, das zum gleichen Datum einige Zeilen darunter eine neue
Zahlenkette kommt.

In AE bis BB will ich für jeden Tag zu jedem neuen ersten Datum nur
eine Zahlenkette mit den jeweiligen MAX Werten.

Die Mappe hat 9 Sheets mit Tabellen, Diagrammen sowei 2 Hilfsblätter
und hat ohne Daten einen Umfang von 9,8 MB. Für den Datenimport und
die Berechnungen braucht der P4 mit 2 GHz unter Vollast und Excel 2002
gut 4,5 Minuten....

Ich will versuchen wie ich das mit einer Pivot Tabelle hinbekomme,
obwohl ich damit bisher wenig gearbeitet habe.

Danke und Grüsse
Ralf

[Klaus "Perry" Pago]

Post by Ralf Schönberger
Hallo zusammen,
ich hatte das Problem stark vereinfacht um es schnell verständlich zu
machen. Genaugenommen habe ich in Spalte B fortlaufendes Datum für
eine Woche, dass in der Regel auch mehrfach untereinander gleich ist.
00 bis 23. Ausgabebereich soll in AE bis BB sein, ebenfalls mit 00 bis
23 beschriftet. Auf diesen Ausgabebereich greifen weitere Berechnungen
zu. Eine Formel hätte ich einfach nach rechts gezogen. Zu jedem neuen
Datum wird in CL bis DI jeweils eine Zahl ausgegeben. Nun kann es
vorkommen, das zum gleichen Datum einige Zeilen darunter eine neue
Zahlenkette kommt.
In AE bis BB will ich für jeden Tag zu jedem neuen ersten Datum nur
eine Zahlenkette mit den jeweiligen MAX Werten.
Die Mappe hat 9 Sheets mit Tabellen, Diagrammen sowei 2 Hilfsblätter
und hat ohne Daten einen Umfang von 9,8 MB. Für den Datenimport und
die Berechnungen braucht der P4 mit 2 GHz unter Vollast und Excel 2002
gut 4,5 Minuten....

Hallo Ralf,

mit einer Matrixformel wäre es so schön einfach - aber bei der bisherigen
Rechnerauslastung...

AE2: =MAX((B2=$B$2:$B$20)*CL$2:CL$20)

Sieht nach einer VBA-Lösung aus - vielleicht findet ja jemand von der
VBA-Fraktion eine schnelle Routine.

Gruß
Klaus

[Bernd P]

Hallo Ralf,

die Größe Deiner Datei ruft wahrscheinlich nach einer grundlegenden
Neustrukturierung (Datenlagerung in einer Datenbank, Import der
notwendigen Daten, Berechnung, Export der Ergebnisse in
Datenbank, ...).

Eine mögliche Lösungsskizze für eine VBA Lösung:
1. In Hilfsspalte alle Zeilen von 1 bis n durchnummerieren (um
Ursprungssortierung wieder herstellen zu können)
2. Tabelle sortieren wie von Boris vorgeschlagen
3. Die Berechnungen durchführen (INDEX und VERGLEICH in Zellen
eintragen, Range.Calculate durchführen - also lediglich
Teilberechnung, dann Formeln durch Ergebnisse ersetzen)
4. Nach Hilfsspalte sortieren
5. Speichern

Viele Grüße,
Bernd

[Ralf Schönberger]

Post by Klaus "Perry" Pago
Hallo Ralf,
die Größe Deiner Datei ruft wahrscheinlich nach einer grundlegenden
Neustrukturierung (Datenlagerung in einer Datenbank, Import der
notwendigen Daten, Berechnung, Export der Ergebnisse in
Datenbank, ...).
1. In Hilfsspalte alle Zeilen von 1 bis n durchnummerieren (um
Ursprungssortierung wieder herstellen zu können)
2. Tabelle sortieren wie von Boris vorgeschlagen
3. Die Berechnungen durchführen (INDEX und VERGLEICH in Zellen
eintragen, Range.Calculate durchführen - also lediglich
Teilberechnung, dann Formeln durch Ergebnisse ersetzen)
4. Nach Hilfsspalte sortieren
5. Speichern
Viele Grüße,
Bernd

Hallo Bernd,

es ist weniger die Vielzahl der Daten, da kommen vielleicht 1 MB dazu.
Es ist die Vielzahl der komplexen Berechnungen mit Grafiken,
allerdings, wie ich zugebe, mit nicht unerheblicher Redundanz. Aber
dafür brauche ich einfach mehr Zeit...

Danke erst mal bis hierhin...
Grüsse
Ralf