Office: (Office 2019) 2. und 3. min-Wert

Hallo, alle zusammen,
meine Frage: Wenn in C3-C46 die Namen der Verkäufer(innen) stehen und in D3-D46 deren jeweilige Umsätze, kann ich (z.B. in E1) mit dieser Formel

=INDEX(C3:C46;VERGLEICH(MIN(D3:D46);D3:D46;0))

die größte Lusche im Verkaufsteam anzeigen. Klappt wunderbar. Ich hätte aber auch gerne den/die Zweitschlechteste(n) und den/die Drittschlechteste(n) angezeigt. Wie lauten die entsprechenden Formeln für E2 und E3?

Lieben Dank im Voraus

Ziska

 

=KGRÖSSTE()

 

Moin

Es gibt Lösungen mit KKLEINSTE(), RANG.GLEICH() und Pivot.

 

Hi,

je nach Excel-Version:

Code:

=INDEX(C3:C46;VERGLEICH(AGGREGAT(15;6;D3:D46;ZEILE(1:3));D3:D46;0))
=INDEX($C$3:$C$46;VERGLEICH(AGGREGAT(15;6;$D$3:$D$46;ZEILE(A1));$D$3:$D$46;0))
=FILTER($C$3:$C$46;$D$3:$D$46<=KKLEINSTE($D$3:$D$46;3))
=FILTER($C$3:$C$46;$D$3:$D$46=KKLEINSTE($D$3:$D$46;Zeile(a1)))

 

Ganz herzlichen Dank für die schnellen Antworten!

Könnte mir vielleicht noch jemand verraten, wie ich das mit meiner Formel in Verbindung bringe?
Weder
=KKLEINSTE(INDEX(C3:C46;VERGLEICH(MIN(D3:D46);D3:D46;0)))
noch
=KGROESSTE(INDEX(C3:C46;VERGLEICH(MIN(D3:D46);D3:D46;0)))
bringen etwas anderes als Fehlermeldungen ...

Ziska

 

warum in Deiner Formel. Benutze das Vorgeschlagene.

Gruß Hajo

 

Offensichtlich habe ich meine Frage nicht präzise genug gestellt.
Ich will nicht den niedrigsten Umsatz, sondern den NAMEN, der zum niedrigsten Umsatz gehört, angezeigt bekommen.
So wie ich mit meiner Formel nicht „3.276 €“ sehe, sondern „Fritze Lusch“.
Ziska

 

wenn es unbedingt deine Formel sein soll.
Meine Tabelle sieht bestimmt anders aus als Deine, dann muss Du die Formel anpassen.

Tabelle1

	E
10	Person6
11	Person4
12	Person3
13	Person3
14	Person2

verwendete Formeln
Zelle	Formel	Bereich	N/A
E10:E14	=INDEX($C$3:$C$46;VERGLEICH(KGRÖSSTE(D$3:$D$46;ZEILE()-9);$D$3:$D$46;0))

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

Add-In-Version 25.24 einschl. 64 Bit

XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle

Gruß Hajo

 

nach Deiner letzten Beschreibung.

Tabelle2

	F
10	Person1
11	Person3
12	Person5
13	Person4
14	Person2

verwendete Formeln
Zelle	Formel	Bereich	N/A
F10:F14	=INDEX($C$3:$C$46;VERGLEICH(KKLEINSTE($D$3: D$46;ZEILE()-9);$D$3:$D$46;0))

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip

http://Hajo-Excel.de/tools.htm

Add-In-Version 25.24 einschl. 64 Bit

XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle

Gruß Hajo

 

HI
KKLEINSTE(xxx;1) ist das gleiche wie MIN(xxx)
du kannst dir hoffentlich selber ausdenken, warum da die 1 steht und was passiert, wenn man da ne andere Zahl reinschreibt.
Gruß Daniel

 

Hallo,

BoskoBiati hat dir ja 4 Formelvorschläge gemacht, je nachdem welche Excel-Version du hast.

Du hast angegeben als Version Excel2019. Das ist die letzte Version, die keine dynamischen Arrays kennt.
Also kommen nur die beiden ersten Formeln infrage - Formel 3 und 4 enthalten die FILTER-Funktion, die dein Excel noch nicht kennt.

Formel1:
=INDEX(C3:C46;VERGLEICH(AGGREGAT(15;6;D3:D46;ZEILE(1:3));D3:D46;0))
Diese Formel enthält die AGGREGAT-Funktion, die keine Array-Formeleingabe benötigen würde. Da aber zusätzlich ZEILE(1:3) enthalten ist (das ist ein Array), musst du die Formel dennoch als Array-Formel eingeben:
1) Dazu markierst du den Zellbereich E3:E5
2) In die Formeleditierzeile von Excel gibst du (bzw. kopierst du) obige Formel1
3) Die Formeleingabe in Punkt 2) schließt du aber nicht bloß mit der EINGABE-Taste ab, sondern mit der Tastenkombination UMSCHALT+Strg+EINGABE.
4) Danach erscheint obige Formel plötzlich in geschweiften Klammern eingeschlossen:
{=INDEX(C3:C46;VERGLEICH(AGGREGAT(15;6;D3:D46;ZEILE(1:3));D3:D46;0))}

Formel2:
=INDEX($C$3:$C$46;VERGLEICH(AGGREGAT(15;6;$D$3:$D$46;ZEILE(A1));$D$3:$D$46;0))
1) Diese Formel gibst du zB. in Zelle F3 ein (oder kopierst sie dorthin).
2) Dann kopierst du diese Formel von F3 in die Zellen F4 und F5, sodass du schließlich 3 Formeln im Zellbereich F3:F5 hast.
Der Unterschied zwischen den 3 Formeln besteht darin, dass die Formel von F3 den Formelteil ZEILE(A1) aufweist, während in F4 dort ZEILE(A2) steht bzw. in F5 ist ZEILE(A3) enthalten.
Dabei ist ZEILE(A1)=1 bzw. ZEILE(A2)=2 bzw. ZEILE(A3)=3

In der beiliegenden Datei habe ich dir obige 4 Formeln in E3:E5 bzw. in F3, F4, F5 eingefügt.
Angezeigt werden nicht die Umsätze sondern die dazu gehörigen Namen - so wie gewünscht.

 

HEUREKA! Also, nicht ich, sondern ihr habt's gefunden. Besten Dank an alle Helferlein, besonders an Edgar und Anton!
Dummerweise 8•) würde ich eure Lösungsvorschläge auch gerne verstehen, deshalb noch 1, 2 Fragen.

Anton, in deiner Datei Ziska24_Mappe1.xlsx steht in E1, E2 und E3 dreimal die selbe Formel, zeigt aber drei unterschiedliche Ergebnisse; wie kann das sein?
Und Edgar, worin unterscheiden sich die Argumente ZEILE(A1) und ZEILE(1:3) in deinen beiden ersten Formeln?
(Das mit „ZEILE()“ hab' ich eh nicht verstanden; muss ich mir mal mein „Excel für Dummies“-Buch vornehmen ...)

Und noch eine ganz andere Frage: Ich wollte eben die Formel
=KKLEINSTE($C$3:$C$46;1)
ein paar Felder nach unten kopieren, in der Hoffnung, Excel würde die Daten wie sonst auch hochzählen (natürlich nicht den Bereich, den wollte ich nicht ändern!). Nix da, die „1“ bleibt wie und wo sie ist: Warum? Und gibt's eventunnel einen Weg, das zu ändern?

Keine der Antworten eilt (falls jemand Lust hat zu antworten); ich bin einfach nur neugierig – und mein Problem ist ja gelöst!

Ziska,
allen ein geruhsames Wochenende wünschend

 

Hola,
hab grad kein Excel zur Hand und kann die Datei nicht anschauen, aber wenn du beim kopieren der Formel die Zahl dynamisch haben willst musst du aus der 1 einfach Zeile(A1) machen.
Gruß,
steve1da

 

Hallo Ziska,

Nein! da steht nur 1Formel in E3, die sich selbstständig nach E4 und E5 erweitert (spillt), weil da ZEILE(1:3) steht, was Excel als Sequenz interpretiert.

Wenn man eine Formel mit einem solchen Term nach unten kopiert oder zieht, dann wird daraus A2, A3 usw., und Zeile(A1) ergibt 1!
Formelauswertung hilft!
Hier mal ein bißchen Lesestoff, damitDu Excel besser verstehst:
Online - Excel

 

Wie einfach wäre es doch, wenn Du eine Version nach 2019 nutzen würdest.
Dazu gehört auch Excel Online, kostenfrei:
=ÜBERNEHMEN(SORTIERENNACH(C3:C46;D3:D46);3)
In Google Sheets geht es sogar noch einfacher:
=SORTN(C3:C46;3;;D3:D46;1)

Gruß Ralf