Kombination aus INDEX, VERGLEICH und MAXWENNS

[hottehue]

Hallo zusammen,
Ich versuche eine Formel zu finden, die INDEX, VERGLEICH und MAXWENNS kombiniert. Gegeben sei folgende Tabelle:

Legende	Start	Ende
x	03.01.2013	27.05.2013
x	03.01.2013	27.05.2013
x	03.01.2013	27.05.2013
x	03.01.2013	27.05.2013
x	03.01.2013	27.05.2013
x	03.01.2013	27.05.2013
	03.01.2013​	14.01.2013​
	17.01.2013​	21.01.2013​
	24.01.2013​	25.01.2013​
x	25.01.2013​	25.01.2013​
	26.01.2013​	03.02.2013​
	04.02.2013​	17.02.2013​
	18.02.2013​	01.03.2013​
x	01.03.2013​	01.03.2013​
	02.03.2013​	16.03.2013​
	17.03.2013​	29.03.2013​
	04.04.2013​	13.04.2013​
	14.04.2013​	22.04.2013​
	02.05.2013​	10.05.2013​
	11.05.2013​	18.05.2013​
	18.05.2013​	27.05.2013​
x	27.05.2013	27.05.2013​

Mit der folgenden Formel:

=INDEX(A1:C23;VERGLEICH(MAXWENNS(C1:C23;A1:A23;"");C1:C23);2)

wollte ich zunächst den Maximum-Wert aus Spalte C ermitteln, falls die Legende aus Spalte A leer ist. Dann soll der Wert links neben dem Maximum-Wert, d.h. in Spalte B ermittelt werden.

Lösung sollte 18.05.2013 sein (Spalte B, zweit-unterste Zeile). Es kommt jedoch 27.05.2013, d.h. offensichtlich Spalte B, unterste Zeile heraus. Excel scheint zu ignorieren, dass die Legende leer sein soll. Was mache ich falsch?
Im Voraus vielen herzlichen Dank!

 

[Scientist]

Wenn unbedingt diese drei Funktionen verwendet werden muessen, dann wirst du denke nicht drumherum kommen, einen Zwischenwert mit einer Hilfsspalte zu berechnen.
Vielleicht koennte man auch eine Matrixformal basteln, kA.

Ich wuerde das ganze mit Summewenns() loesen.

Das Problem an deiner Formel:
Du berechnest zwar den Max-Wert mit der richtigen Bedingung, beim Vergleich wird diese aber wieder außenvorgelassen.
Vergleich() liefert dir anschließend den ersten Treffer und das ist Zeile 1.

 

[hottehue]

Vielen Dank für den Hinweis!
Wie könnte denn eine mögliche Formel aussehen - gerne auch mit Summewenns().

Im Voraus besten Dank!

 

[Scientist]

Loesung: Summewenns:
Summenbereich: Spalte C
Kriterien wie schon in der Formel.

So werden beide Kriterien gleichzeitig angewand.
Funktioniert natuerlich nur, wenn die Kombination einmalig ist, ansonsten werden alle Ergebniss summiert.

Loesung: mit Hilfsspalte
max-Wert bestimmt und dann in der Zeile ueber Wenn-Abfrage, ob Spalte C mit max-Wert uebereinstimmt und Spalte A leer ist. Wenn Bedingungen zutreffen, dann soll Spalte B ausgegeben werden.
Anschließend brauchst du nur noch in der Hilfsspalte nach den einzigen gueltigen Wert suchen.

 

[steve1da]

Hola,

warum das so nicht funktioniert, hat Scientist ja schon geschrieben. Wie du trotzdem den richtigen Wert bekommst:
=VERWEIS(2;1/(A2:A23&C2:C23=""&MAXWENNS(C2:C23;A2:A23;""));B2:B23)

Gruß,
steve1da

 

[hottehue]

Ganz herzlichen Dank Euch beiden!

 

[hottehue]

Hallo zusammen,
Ich habe versucht die geniale Formel zu interpretieren und für eine weitere Berechnung zu nutzen, bin aber schnell an meine Grenzen gestossen.

Gegeben sei die folgende, um Spalte D und den Einzelwert Faktor erweiterte, Tabelle:

Legende	Start​	Ende​	Neu-Start​	​	Faktor​
x	03.01.2013​	27.05.2013​	03.01.2013​	​	0,9​
x	03.01.2013​	27.05.2013​	03.01.2013​	​	​
x	03.01.2013​	27.05.2013​	03.01.2013​	​
x	03.01.2013​	27.05.2013​	03.01.2013​	​
x	03.01.2013​	27.05.2013​	03.01.2013​	​
x	03.01.2013​	27.05.2013​	03.01.2013​	​
	03.01.2013​	14.01.2013​	03.01.2013​	​
	17.01.2013​	21.01.2013​	18.01.2013​	​
	24.01.2013​	25.01.2013​	25.01.2013​	​
x	25.01.2013​	25.01.2013​	27.01.2013​	​
	26.01.2013​	03.02.2013​	08.02.2013​	​
	04.02.2013​	17.02.2013​	17.02.2013​	​
	18.02.2013​	01.03.2013​	02.03.2013​	​
x	01.03.2013​	01.03.2013​	10.03.2013​	​
	02.03.2013​	16.03.2013​	11.03.2013​	​
	17.03.2013​	29.03.2013​	21.03.2013​	​
	04.04.2013​	13.04.2013​	04.04.2013​	​
	14.04.2013​	22.04.2013​	14.04.2013​	​
	02.05.2013​	10.05.2013​	03.05.2013​	​
	11.05.2013​	18.05.2013​	10.05.2013​	​
	18.05.2013​	27.05.2013​	19.05.2013​	​
x	27.05.2013​	27.05.2013​	29.05.2013​	​

Mit der Formel:

=VERWEIS(2;1/(A2:A23&B2:F23=""&MAXWENNS(B2:F23;A2:A23;""));(((C2:C23-B1:B23)/F2)+D1:23))

wollte ich diesmal den Maximum-Wert der gesamten Tabelle aus einer Formel ((Ende-Start)/Faktor)+Neustart), d.h. (((C-B)/F)+D) ermitteln, falls die Legende aus Spalte A leer ist. Bei einem gegebenen Faktor 0,9 sollte ein Datum 29.05.2013 (zweit-unterste Zeile: (((27.05.2013-18.05.2013)/0.9)+19.05.2013) ermittelt werden – es kommt aber #NV heraus. Vielleicht kann ich nicht einfach einen Bereich B2:F23 über mehrere Spalten in VERWEIS oder MAXWENNS eingeben.

Nochmals vielen Dank im Voraus.

 

[zahlenmensch]

Wie du trotzdem den richtigen Wert bekommst:
=VERWEIS(2;1/(A2:A23&C2:C23=""&MAXWENNS(C2:C23;A2:A23;""));B2:B23)

Könnte mir jemand diese Formel erklären? Ich habe grundsätzlich das identische Problem, verstehe aber nicht wie anpassen.

Was hat die Zahl 2 nach Verweis für eine Bedeutung? Ich kann auch pi eingeben, es funktioniert mit jeder positiven reellen Zahl im Beispiel. WTF?

Weshalb die Division 1/()?

Wozu der Vergleich C2:C23=""?

Gebe ich nur den Term =1/(A2:A23&C2:C23=""&MAXWENNS(C2:C23;A2:A23;"")) in eine Formel, erscheint eine ausklappbare Formel mit #DIV/0! in den meisten Zellen und 1 wo der Treffer liegt. Das widerspricht dann meinem Verständis der Funktion Verweis.

Ich denke, so manchem, der im Internet diesen Thread findet, geht es so wie mir.

 

[steve1da]

Schau mal hier:
https://www.herber.de/forum/archiv/1224to1228/1226733_Deutung_VerweisFunktion.html
Und es ist auch nicht C2:C23="" sondern die Abfrage auf leer gilt für A2:A23.

 

[zahlenmensch]

Der Link erklärt die Spezialanwendung der Funktion VERWEIS, danke. Er erklärt aber nicht, weshalb die Formel funktioniert.

Dem Link nach zu urteilen ist A2:A23&C2:C23="" eine andere Schreibweise für UND(Ax="";Cx=""). Doch in der Formel hat es eine dreifach-Verknüpfung, wovon der letzte Term ein Skalar ist.

Weshalb braucht es die Angabe von C2:C23, damit die Abfrage auf A2:A23 funktioniert? Im Beispiel hier stehen ja überall in Spalte C Werte, da müsste eigentlich auch A2:A23=""&MAXWENNS(C2:C23;A2:A23;"") im Klammerterm unter dem Bruchstrich funktionieren. Tut es aber nicht, es gibt zwar einen Vektor zurück, aber nur mit FALSCH.

 

[steve1da]

Dem Link nach zu urteilen ist A2:A23&C2:C23="" eine andere Schreibweise für UND(Ax="";Cx="").

Nein, ist es nicht. Du musst schon das ganze betrachten.
A2:A23&C2:C23=""&MAXWENNS(C2:C23;A2:A23;"")
Hier gilt: A2:A23 ist gleich "" und C2:C23 ist das Maximum aus C2:C23, wenn A2:A23 leer ist. Vor dem "=" Zeichen sind es 2 Bereiche, nach dem "=" Zeichen sind es 2 Bedingungen.