Calc: Für jede Zeile die Spalte mit dem größtem Wert herausfinden

[rummster]

Ich habe einen Datensatz mit Kraftwerke. Die Kapazitätsangaben befinden sich je nach Typ in verschiedenen Spalten, z.B. “Flywheels”, “Geothermal” oder “Landfill Gas”.

Ich suche

1. die Anzahl von Spalten mit Werten > 0 – das kann ich mit COUNTIFS
2. den Spaltennamen des größten Wertes und den des zweitgrößten – hier habe ich keinen Ansatz

Wenn ein Kraftwerk z.B. den höchsten Wert bei “Hydroelectric” hat, dann hätte ich gerne den Spaltennamen als Ergebnis.

Meine Testdatei (zip) gibt es hier: Link auf github

Vielen Dank im voraus!

[stromo]

ungezippt wäre besser

[Lupp]

Gute Idee, englische Funktionsnamen zu verwenden!
Am besten nimmt man auch gleich eine englischsprachige Benutzeroberfläche, stellt aber als Parametertrenner das Semikolon ein. (Auch das Komma als Trenner in konstanten Arrays passt nicht wirklich.)

[RobertG]

Ich habe zumindest für den größten Wert eine Lösung:

=VERWEIS(MAX(I2:P2);I2:P2;I$1:P$1)

eingetragen in G2.

In H2 habe ich die folgende Formel versucht, komme damit aber selbst bei Zeilen mit einem eindeutig zweitgrößten Wert nicht auf die richtige Bezeichnung in der ersten Zeile:`

=VERWEIS( MAXWENNS(I2:P2;I2:P2;"<"&MAX(I2:P2));I2:P2;I$1:P$1)

Diese Formel ergibt mit MAXWENNS sehr wohl z.B. in Zeile 7 den Wert 50 als 2. Wert, aber sucht nicht dazu passend den Verweis raus, sondern landet grundsätzlich eine Spalte vor dem Wert, den die vorherige Formel auswirft. Das liegt wohl daran, dass für die Funktion VERWEIS die Daten sortiert vorliegen müssen. Deswegen jetzt dies Funktion - Calc-ExpertInnen können das sicher besser:

WENN(MAXWENNS(I2:P2;I2:P2;"<"&MAX(I2:P2)) > 0;VERSCHIEBUNG(H2;1-ZEILE();VERGLEICH(MAXWENNS(I2:P2;I2:P2;"<"&MAX(I2:P2)) ;I2:P2;0)) ;"")

Das müsste dann in H2 stehen und könnte runter gezogen werden. Ist natürlich deutlich länger, weil teilweise kein zweiter Wert auftaucht.

[Lupp]

Ob das den Aufwand lohnt?
Eine Funktion, die ich in der Demo verwendet habe, arbeitet erst ab Version LibO V5.4 verlässlich. (In AOO gibt es sie nicht.)

Editing:
Die Formeln in meiner Lösung arbeiteten nicht unter allen Bedingungen korrekt. Ich habe mir die Sache nochmals durch den Kopf gehen lassen, und bin jetzt überzeugt, dass eine Lösung mit mindestens act Helferspalten, aber mit viel einfacheren (und pflegeleichten) Formeln, wesentlich empfehlenswertetr ist.

Meine Frage nach der zu erwartenden Höchstzahl von Datenreihen wurde nicht beantwortet. Falls es aber “sehr viele” (etliche tausend z.B.) sind, wird die formelbasierte Löung weniger effectiv sein, und eine große Datei efordern. Jede Zelle, die eine Formel enthält, erzeugz halt auch Speicherbedarf.

Eine Lösung mit Hilfe eines Benutzerprogramms (“Makro”) braucht nur Platz für die eingegebenen Daten. Den Platzbedarf des Programms kann man getrost ignorieren.

Ich habe in diese neue Demo deshalb auch eine (recht roh geschnitzte) Lösung mit Benutzerprogramm eingebaut.
Dabei bin ich auf ein sehr seltsames Problem gestoßen, das mich gezwungen hat, die Bsic-Funktion IIf() zu verwenden.

[rummster]

Hallo lupp, vielen Dank dafür. Weißt du, dass die komplizierte Formel für das zweitgrößte Kraftwerke völlig unnötig ist und auch die einfache Funktion für das größte Kraftwerk + IFERROR vorne vor funktioniert?

[Lupp]

@rummster: "Weißt du, dass die komplizierte Formel für das zweitgrößte Kraftwerke völlig unnötig ist und auch die einfache Funktion für das größte Kraftwerk + IFERROR… "
Das stimmt natürlich nicht.
Es gibt zwei Fehlerquellen.
-1- Der Wert für den 2. Rang ist der gleiche wie für den ersten.
-2- Mehere “Kategorien” im 2. Rang sind gleichwertig.

[Lupp]

Ich habe dfas mit dem “Phantasy Plant” in Reihe 20 demonstriert.
Meine Lösing ist natürlich noch insoweit mangelhaft, dass im Falle mehrerer gleichrangiger “Kategorien” mit der höchsten Kennzahl nur die am weitesten links stehende auf den ertsen Platz gesetzt wird, und die anderen unter Platz 2 erscheinen. Ich habe das “stillscheigend inkauf genommen”.
Rechenblätter können solche Dinge einfach nicht gut.
Ein kleines Spezialprogramm, das die Daten seriell bearbeitet, hätte es leichter.