Guten Abend!
Ich muss für eine Hausarbeit Umsätze von verschiedenen Artikeln untersuchen.
Ich will jetzt die Artikel mit dem Obersten und Untersten 10% (gemessen am Umsatz) rausfiltern/separat darstellen.
Kann man das mit Hilfe einer Formel und/oder Pivot-Tabelle darstellen ?
Hier ein kleines Beispiel: http://www.file-upload.net/download-8477257/Beispiel…
Bitte keine Makros!
Vielen Dank für Eure Zeit und Mühe
Mit freundlichen Grüßen
Daniel
Hallo Daniel,
Wie kann ich aus einer unsortierten Liste den N’t-größten Wert bestimmen ?
RANG(Liste;Zahl;{1-Liste}) bestimmt den umgekehrten Wert, nämlich die Position in der Sortierten Liste ( ohne Entfernung der mehrfachen Elementen ).
Ergänze Dein Beispiel bitte durch eine weitere Spalte mit dem Inhalt
=RANG(B;$B$2:blush:B$31) , also =RANG(B2;$B$2:blush:B$31) für die Zeile 2 etc.
Dann siehst Du hier die „Position“ des betreffenden Umsatzes und kannst entsprechend auswählen.
Mit Gruß,
Frank-Thomas
Grüezi Daniel
Ich will jetzt die Artikel mit dem Obersten und Untersten 10%
(gemessen am Umsatz) rausfiltern/separat darstellen.
Was genau heisst für dich 10% gemessen am Umsatz?
Ich habe mal angenommen, dass Du bei den 30 Werten die obersten/untersten 3 haben möchtest.
Wenn das nicht deine Absicht ist, dann schildere bitte präzise® wie Du dir das vorstellst - am Besten indem Du die betreffenden Werte in die beiden Spalten schreibst und so das gewünschte Ergebnis definierst.
Kann man das mit Hilfe einer Formel und/oder Pivot-Tabelle
darstellen ?
Eine Pivot-Tabelle hilft dir nicht, wenn Du keine Auswertungen mit den Werten anstellen willst/musst.
Hier ein Beispiel aufgrund meiner oben geschilderten Annahmen (Formeln bitte einfach nach unten kopieren).
Tabellenblatt: E:\DownLoads\[Beispiel2.xlsx]!Tabelle1
│ A │ B │ C │ D │ E │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
1 │ Artikel │ Umsatz │ Obersten 10% │ Untersten 10% │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
2 │ 1 │ 4'783'359 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
3 │ 2 │ 14'079'190 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
4 │ 3 │ 37'562'075 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
5 │ 4 │ 177'537'296 € │ 177537296 │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
6 │ 5 │ 9'348'480 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
7 │ 6 │ 9'000'000 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
8 │ 7 │ 5'500'000 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
9 │ 8 │ 7'500'000 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
10 │ 9 │ 6'000'000 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
11 │ 10 │ 4'000'000 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
12 │ 11 │ 3'500'000 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
13 │ 12 │ 6'900'000 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
14 │ 13 │ 5'000'000 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
15 │ 14 │ 18'550'982 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
16 │ 15 │ 37'234'780 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
17 │ 16 │ 46'035'168 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
18 │ 17 │ 45'151 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
19 │ 18 │ 11'212 € │ │ 11212 │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
20 │ 19 │ 32'323 € │ │ 32323 │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
21 │ 20 │ 3'266 € │ │ 3266 │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
22 │ 21 │ 111'654'601 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
23 │ 22 │ 33'354'511 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
24 │ 23 │ 225'433 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
25 │ 24 │ 33'211'500 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
26 │ 25 │ 25'146'311 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
27 │ 26 │ 211'552'212 € │ 211552212 │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
28 │ 27 │ 222'865 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
29 │ 28 │ 365'481'174 € │ 365481174 │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
30 │ 29 │ 5'586'611 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
31 │ 30 │ 5'566'314 € │ │ │ │
───┼─────────┼───────────────┼──────────────┼───────────────┼───┤
32 │ │ │ │ │ │
───┴─────────┴───────────────┴──────────────┴───────────────┴───┘
Benutzte Formeln:
C2 : =WENN($B2\>=KGRÖSSTE($B$2:blush:B$31;ANZAHL($B$2:blush:B$31)/10);$B2;"")
D2 : =WENN($B2Tabellendarstellung erreicht mit dem Code in [FAQ:2363](/t/faq/9292363)
Wenn es um sehr viele Daten geht, könnten die Formeln auch noch optimiert werden indem die Grenzen oben/unten in einer Hilfszelle berechnet und dann darauf Bezug genommen wird.
Mit freundlichen Grüssen
Thomas Ramel
- MVP für MS-Excel -
Hallo Thomas,
vielen Dank für deine schnelle Antwort. Ja das ist genau wonach ich gesucht habe. Vielen Dank.
Noch eine Frage: Was ist muss ich tun, wenn ich jetzt mit Hilfe der bedingten Formatierung die Obersten und Untersten 10% farblich markieren will (in Spalte mit Umsatz sollen die Spalten markiert werden, basierend auf den Zahlen aus C bzw D Spalte)?
Grüzi Daniel
Noch eine Frage: Was ist muss ich tun, wenn ich jetzt mit
Hilfe der bedingten Formatierung die Obersten und Untersten
10% farblich markieren will (in Spalte mit Umsatz sollen die
Spalten markiert werden, basierend auf den Zahlen aus C bzw D
Spalte)?
Eine Variante ist die folgende Formel, angewandt auf B2:B31:
=ANZAHL($C2:blush:D2)
Mit freundlichen Grüssen
Thomas Ramel
Klasse hat alles super geklappt. vielen Dank !!!