Suche von Schnittpunkt mit größter Zahl (Index/ Vergleich)

[CurlySue]

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

[steve1da]

Hola,
zB mit Max/Wenn als Matrixformel:
https://www.automateexcel.com/de/formeln...bedingung/
Gruß,
steve1da

[LCohen]

=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

[26865]

Edit: Alles Unfug, was ich geschrieben habe. Siehe #9

[LCohen]

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.

[26865]

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.

Für ältere Versionen kann ich nicht testen.

kein Problem, dafür bin ich ja da... ;)

[LCohen]

in #5 weiteres ergänzt

[CurlySue]

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.

[26865]

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)

[LCohen]

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.