Excel Top 10 Vertriebsmitarbeiter

Question

Ich habe eine Tabelle mit den Umsätzen der einzelnen Vertriebsmitarbeiter, sodass

   A      B             C        D
1  ID    Name        Product  Sales($)   
2  1    John Smith   Toaster   250
3  2    Dina Caan    Kettle    450
4  1    John Smith   Kettle    450
5  1    John Smith   Kettle    250
6  2    Dina Caan    Toaster   250
7  3    Peter Hues   TV        5400
8  3    Peter Hues   Radio     480
9  2    Dina Caan    Radio     100
10 4    Ralph Do     TV        890
11 4    Ralph Do     Radio     100
12 5    Ian Poe      Watch     750
13 6    Tina Hood    TV        450 
14 7    Peter Maan   Watch      99

Ich hätte gerne eine formelbasierte Tabelle in F, die die Top-n (sagen wir 3) Verkäufer nach Gesamtumsatz mit den Überschriften ID Name TotalSales($) zeigt.

Ich kann dies problemlos mit einer Pivot-Tabelle tun und dann die obersten N filtern, aber in diesem Fall möchte ich es mit einer Formel tun.

Bearbeiten: Produkt aus der Ergebnistabelle entfernt.

Answer 1

Mit den neuen dynamischen Arrayformeln von Excel können Sie das Ergebnis erhalten, ohne eine Pivot-Tabelle zu erstellen. Dazu sind jedoch einige Hilfszellen erforderlich. Betrachten Sie den folgenden Screenshot:

Die Formel in F2 lautet =UNIQUE(B2:B14)und wird NICHT nach unten kopiert.

Die Formel in G2 wird =SUMIFS($D$2:$D$14,$B$2:$B$14,UNIQUE(B2:B14))NICHT nach unten kopiert.

Die drei besten Verkäufer in der Rangfolge ihres Gesamtumsatzes können dann mit der Formel in I2 ermittelt und =INDEX(SORTBY(F2#,G2#,-1),{1;2;3})ebenfalls NICHT nach unten kopiert werden.

Dynamische Arrays „überlaufen“ so weit, wie es die Formel erfordert, und wenn auf die Formelzelle eines dynamischen Arrays verwiesen wird, wird dieser Überlauf übernommen. Das bedeutet, dass Sie die Liste der eindeutigen Namen in Spalte F nicht aktualisieren müssen, wenn es beim nächsten Mal neue Vertriebsmitarbeiter gibt. Die Formel erledigt das automatisch.

Answer 2

Um die höchsten Umsätze in der richtigen Reihenfolge zu erhalten, verwenden Sie diese Matrixformel:

=LARGE(MODE.MULT(IF(MATCH($B$2:$B$14,B:B,0)=ROW($B$2:$B$14),SUMIFS(D:D,B:B,$B$2:$B$14)*{1,1})),ROW(1:1))

Da es sich um eine Array-Formel handelt, muss sie beim Verlassen des Bearbeitungsmodus mit Strg-Umschalt-Eingabe statt mit der Eingabetaste bestätigt werden.

Um die ID und den Namen zu erhalten, können wir diese dann mit einer anderen Array-Formel nutzen:

=INDEX(A:A,MATCH($I2,SUMIFS($D:$D,$B:$B,$B$2:$B$14),0)+1)

Außerdem ist Strg-Umschalt-Eingabe erforderlich, dann rüber und nach unten kopieren.