Microsoft Excel

Herbers Excel/VBA-Archiv

Index mit zusätzlicher Bedingung

Betrifft: Index mit zusätzlicher Bedingung von: Peter Müller
Geschrieben am: 06.09.2020 10:14:41

Hallo zusammen,


ich habe folgendes Problem: Gesucht wird der gößte Wert (Spalte C) wenn die Anzahl (Spalte D) größer gleich 5 ist. Die dazugehörige Stadt soll ebenfalls ausgegeben werden. Mit Index funktioniert das nicht, da hier die Anzahl nicht berücksichtigt wird.


Wie kann ich die Formel anpassen, dass die Anzahl berücksichtigt wird?


https://www.herber.de/bbs/user/140061.xlsx



Besten Dank vorab!


Gruß

Peter

Betrifft: AW: Index mit zusätzlicher Bedingung
von: SF
Geschrieben am: 06.09.2020 10:31:04

Hola,

den Wert holst du dir z.B. mit einer Matrix Formel. Max(wenn(.....)).
Die Stadt dann mit:
https://www.herber.de/excelformeln/pages/SVERWEIS_bei_mehrspaltigen_Suchkriterien_Mehrfachtreffer.html

Gruß,
steve1da

Betrifft: AW: Index mit zusätzlicher Bedingung
von: Peter Müller
Geschrieben am: 06.09.2020 11:30:54

Hallo steve1da,

besten Dank für den Tipp. Funktioniert!

Schönen Sonntag noch.

Gruß
Peter

Betrifft: AW: Index mit zusätzlicher Bedingung
von: Herbert_Grom
Geschrieben am: 06.09.2020 12:35:25

Hallo Peter,

kannst du bitte die "Max(wenn(.....))"-Formel posten, die du verwendet hast? Danke!

Servus

Betrifft: AW: Index mit zusätzlicher Bedingung
von: Peter Müller
Geschrieben am: 06.09.2020 12:39:43

Hallo Herbert,

habe mich für diese Formel entschieden:
=INDEX($B$5:$B$24;VERGLEICH(1;MMULT(($C$5:$C$24=G5)*($D$5:$D$24>4);1);0))
Gruß
Peter

Betrifft: AW: Index mit zusätzlicher Bedingung
von: Herbert_Grom
Geschrieben am: 06.09.2020 12:43:17

Hallo Peter,

ich hatte die andere Formel, die die "66" ermittelt, gemeint!

Servus

Betrifft: AW: Index mit zusätzlicher Bedingung
von: Peter Müller
Geschrieben am: 06.09.2020 12:46:00

Hallo Herbert,
die habe ich nicht geändert (s. Datei um Ursprungsbeitrag, Zelle G5):
=WENNFEHLER(AGGREGAT(14;6;$C$5:$C$24/($D$5:$D$24>4);1);"")
Gruß
Peter

Betrifft: AW: Danke! owt
von: Herbert_Grom
Geschrieben am: 06.09.2020 12:53:50

,,,

Betrifft: AW: MAX(WENN() bedarf es nicht ...
von: neopa C
Geschrieben am: 06.09.2020 12:56:12

Hallo Herbert,

... das Ergebnis kann auch ohne dies ermittelt werden.

So: =INDEX(B:B;AGGREGAT(15;6;ZEILE(C5:C99)/(C5:C99=G5)/(D5:D99>4);1))

Und wenn G5 nicht separat ermittelt werden soll, dann so:

=INDEX(B:B;AGGREGAT(15;6;ZEILE(C5:C99)/(C5:C99=MAX(INDEX((D5:D99>4)*C5:C99;)))/(D5:D99>4);1))

Gruß Werner
.. , - ...

Betrifft: AW: MAX(WENN() bedarf es nicht ...
von: Herbert_Grom
Geschrieben am: 06.09.2020 13:11:46

Hallo Werner,

perfekt wie immer!!! :o)=)

Servus

Betrifft: AW: gerne owT
von: neopa C
Geschrieben am: 06.09.2020 13:12:56

Gruß Werner
.. , - ...

Betrifft: AW: Index mit zusätzlicher Bedingung
von: Günther
Geschrieben am: 06.09.2020 15:46:53

Na gut, dann provoziere ich einfach einmal: Ich behaupte, dass das korrekte Ergebnis Kalkutta ist … ;-)
Denn: K < S !
 
Gruß
Günther  |  mein Excel-Blog

Betrifft: AW: wenn schon denn schon ...
von: neopa C
Geschrieben am: 06.09.2020 16:00:38

Hallo Günther,

... dann wäre wohl das korrekte Ergebnis mit Formeln wie folgt zu ermitteln:

=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(C$5:C$99)/(C$5:C$99=G$5)/(D$5:D$99>4);ZEILE(B1)));"")

und Formel weit genug nach unten kopieren.

Analog natürlich, wenn ohne die Hilfszelle G5 ermittelt werden soll.

Wobei mir natürlich klar ist, dass Du darauf hinaus willst, das mit PowerQuery so oder so das Ergebnis ganz ohne Zellformeln ermittelt werden kann ;-)

Gruß Werner
.. , - ...

Betrifft: AW: wenn schon denn schon ...
von: Günther
Geschrieben am: 06.09.2020 16:11:59

Hallo Werner,
stimmt schon, was du zum Ausdruck bringst. ;-)
Aber in erster Linie "moniere" ich hier exemplarisch/stellvertretend für eine ganze Anzahl von Fragestellern die fehlende Akkuratesse, Vorausschau, … oder auch bessere Beschreibung, dass (auf dieses Beispiel bezogen) nur der erste gefundene Wert ausgegeben wird/werden soll. Ich finde dass Fragesteller sich ein Optimum an Mühe geben sollten, um eine optimale Antwort zu bekommen. Oder ist das in der heutigen Zeit zu viel verlangt?
 
Gruß
Günther  |  mein Excel-Blog

Betrifft: AW: teils, teils ...
von: neopa C
Geschrieben am: 06.09.2020 16:29:15

Hallo Günther,

... im vorliegenden Fall hatte Peter das erwünschte Ergebnis explizit vorgegeben und daran haben sich alle gehalten. Aber Du hast Recht, wir hätten ihn den Hinweis geben können/sollen, den Du gemacht hast.

Möglicherweise ist für Peter als Ergebnis wirklich nur São Paulo richtig, weil bei gleichem Wert die Anzahl größer ist als bei Kalkutta. Dann allerdings müsste die Auswertungsformel noch um diese Bedingung erweitert werden, was allerdings keine große Hürde wäre.

Gruß Werner
.. , - ...


Beiträge aus dem Excel-Forum zum Thema "Index mit zusätzlicher Bedingung"