Hallo zusammen,
ich habe in Excel eine Matrix, in der Schnittpunkte mehrfach vorkommen können (siehe untenstehendes Beispiel). Jetzt habe ich das Problem, dass ich den Schnittpunkt mit der größten Zahl finden muss. Habt ihr eine Idee, wie ich das lösen kann? Power Query und Tabelle sortieren ist leider nicht möglich….
Mit folgender Formel finde ich den Schnittpunkt …aber hier wird mir halt nur der erste Schnittpunkt angezeigt. Weitere werden nicht geprüft.
=INDEX(Grunddaten!$A$1:$BN$32049;VERGLEICH(Zusammenfassung!I2;Grunddaten!$A$1:$A$32049;0);VERGLEICH(Zusammenfassung!F2;Grunddaten!$A$1:$BN$1;0))
Vielen Dank für eure Hilfe!
Kati
Beispiel:
A B A
Hund 20 30 10
Katze 10 10 40
Katze 20 5 50
-> Der größte Schnittpunkt für A und Katze wäre: 50
=MAX(INDEX(Grunddaten!A1:BN32049;
ZEILE(Grunddaten!A1:A32049)*(Zusammenfassung!I2=Grunddaten!A1:A32049);
SPALTE(Grunddaten!A1:BN1)*(Zusammenfassung!F2=Grunddaten!A1:BN1)))
für xl365
Edit: Alles Unfug, was ich geschrieben habe. Siehe #9
Den Satz "... bei Ungleichheit... " habe ich nicht verstanden; natürlich geht es auf den deformierten gesamten Bereich. Bei mir (365) funktioniert es ohne {} mit eingetragenen Suchbegriffen (Minibeispiel). Für ältere Versionen kann ich nicht testen. Und "versionsübergreifend" war es von mir auch nicht erlaubt. Ich habe in #3 die Anmerkung jetzt von {} auf 365 geändert.
EDIT zu #6: Wie gesagt. Bei mir klappt's. Ich bekomme das Max über die gefragte (positive!) Zahl. Bei negativen klappt es nicht, da hast Du recht. Dann sollte man die Lösung ändern.
Hier ohne INDEX am Minibeispiel schnell und schmutzig:
=LET(x;MTRANS(VSTAPELN(A1:D1;FILTER(A1:D4;A1:A4="Katze")));MAX(WEGLASSEN(FILTER(x;SPALTENWAHL(x;1)="a");;1))) bzw.
=LET(x;MTRANS(VSTAPELN(A1:D1;FILTER(A1:D4;A1:A4="Katze")));MAX(FILTER(x;SPALTENWAHL(x;1)="a"))) da die Vorspalte Labels beinhaltet
(der FILTER wird wie beim Zauberwürfel gedreht)
Die ungefragten Einträge sind nun weg und nicht Null. Damit ginge auch eine Ermittlung von negativen Zahlen als Maxima.
Mit "Ungleichheit" meine ich
Der Term
(Q1=Grunddaten!A1:A100) liefert 0, wenn Q1 <> GrunddatenA1:A100 (analog bei Q2=Grunddaten!A1:D1)
=INDEX(Array;1;0) liefert die komplette 1. Zeile des Arrays
Trag mal im Minibeispiel für B und Hund den Wert 90 ein. Der kommt bei Variante 1 als Ergebnis, obwohl nicht zu den Suchkriterien passend.
Zitat:Für ältere Versionen kann ich nicht testen.
kein Problem, dafür bin ich ja da... ;)
Ach ihr seit lieb - danke für eure Hilfe erstmal. Ich probiere es nachher mal aus - aber ich muss dazu sagen, dass ich leider nur Excel2013 habe - daher wird einiges nicht gehen. Sorry... hatte nicht daran gedacht es dazu zu schreiben.
Arghs, ich muss mich korrigieren: Unter 365 liefert deine ursprüngliche Formel ohne CSE das korrekte Ergebnis, mit CSE das falsche Maximum.
Testdaten:
Code:
A B A
Hund 20 120 10
Katze 70 90 40
Katze 20 5 50
Korrektes Ergebnis ohne CSE: 70
Mit CSE: 120
Oldschool:
Nur positive Werte: (Excel <365 mit CSE-Pflicht!)
Code:
{=MAX((B2:D4)*(A2:A4=Q1)*(B1:D1=Q2))}
Läuft auch bei negativem Maximalwert
Code:
=AGGREGAT(14;6;(B2:D4)/(A2:A4=Q1)/(B1:D1=Q2);1)
Läuft auch bei negativem Maximalwert und leeren Zellen
Code:
=AGGREGAT(14;6;(B2:D4)/(A2:A4=Q1)/(B1:D1=Q2)/(B2:D4<>"");1)
Sehr schön! Bin ja kein AGGREGAT-Fan, aber die auf diese Weise erweiterte MAX-Funktionsweise ist ein Argument dafür. AGGREGAT ist auch sonst anscheinend optimiert/schneller (ge)worden. Das einzige: Spill-Schwächen oder -Unmöglichkeiten.
Sue: Immerhin steht ja 2010 bei Dir im Profil. Und zwischen 2010 und 2013 gibt es außer PQ und XMLFILTERN nicht so viele Unterschiede. Wir laden Dich aber herzlich ein, die Version trotzdem im Profil zu korrigieren.