Office: (Office 365) Rangfolge stimmt nicht, doppelte Zeilen

Hallo,

ich habe eine Liste A1 (Spaltenüberschriften: Produkt und Differenzwert) mit Werten ab A2 bis B62:

Produkt Differenzwert
1 23
2 0
3 81
4 29
5 0
6 1
7 13
8 0
9 6
10 -31
11 -1
12 -1
13 1
14 1
15 0
16 3
17 42
18 1
19 0
20 -5
21 1
22 1
23 -1
24 -6
25 23
26 -1
27 -8
28 -1
29 16
30 7
31 -9
32 -14
33 11
34 26
35 7
36 -7
37 2
38 0
39 1
40 3
41 0
42 2
43 2
44 1
45 29
46 4
47 1
48 0
49 59
50 17
51 0
52 -4
53 -4
54 -1
55 1
56 -2
57 -2
58 0
59 -2
60 -1
61 1

Gebe ich jetzt in meine Auswertetabelle (D1 bis F22) bei Produkt in Feld E3 (und darunter) folgende Formel ein:
=INDEX($A$2:$A$72;VERGLEICH(KGRÖSSTE($F$2:$F$72;H3);$F$2:$F$72;0))
und bei Differenzwert in Feld F3 (und darunter) folgende Formel ein:
=SVERWEIS(E3;$A$2:$B$72;2;0)

dann kommt fälschlicherweise das hier raus:

Größte Zunahmen
Rang Produkt Differenzwert
1 3 81
2 49 59
3 17 42
4 4 29
5 4 29
6 34 26
7 1 23
8 1 23
9 50 17
10 29 16
11 7 13
12 33 11
13 30 7
14 30 7
15 9 6
16 46 4
17 16 3
18 16 3
19 37 2
20 37 2

Mehrere Produkte wie z.B. die 4, die 1, die 30, die 16 und die 37 sind doppelt. Ich will aber nur, dass die echten Top20 absteigend aufgelistet werden.

Das selbe Problem, dass Produkte doppelt vorkommen, habe ich auch mit den größten Abnahmen, sprich mit der Formel:
=INDEX($A$2:$A$72;VERGLEICH(KKLEINSTE($B$2:$B$72;D26);$B$2:$B$72;0))

dann sieht es so aus:

Größte Abnahmen
Rang Produkt Differenzwert
1 10 -31
2 32 -14
3 31 -9
4 27 -8
5 36 -7
6 24 -6
7 20 -5
8 52 -4
9 52 -4
10 56 -2
11 56 -2
12 56 -2
13 11 -1
14 11 -1
15 11 -1
16 11 -1
17 11 -1
18 11 -1
19 11 -1
20 2 0

Wie löse ich das Problem?
Dankeschön.
Viele Grüße

 

Hi,

1. indem Du das Ganze als vernünftige Excel-Datei vorstellst und
2. uns mal Deine Excel-Version nennst.

in 365:

Code:

=ZEILENWAHL(SORTIEREN(A:.B;2;-1);ZEILE(1:20))

 

Guten Morgen,

hier die Excel-Datei, bitteschön
Excel 365.

Danke!

 

Hallo,

Rang bei doppelten Werten (s. Mappe:


Gruß Sigi

 

Hallo Sigi,

vielen Dank. Ich habe es getestet und es klappt ganz wunderbar!
Ohne die Hilfsspalten wird die Formel aber vermutlich zu komplex?
Bin aber auch so sehr zufrieden. Das hilft mir weiter!

Gruß, Matthias

 

Hallo,

ja, ich denke schon. Aber Hilfsspalten kannst du ja ausblenden!

Gruß Sigi

 

Hallo,

in der beiliegenden Datei habe ich dir in deinen 2 gelben Zellbereichen je 1 Formel eingefügt:
in Zelle O16 (mit Resultatbereich O16:P35) bzw. in Zelle O39 (mit Resultatbereich O39:P58):

Beide Formeln kommen ohne Hilfsspalten aus. Der Unterschied liegt in SORTIEREN(...;-1) bzw. SORTIEREN(...;1)

Weiters verwenden beide Formeln die neuesten (dynamischen) Bereichsangaben (mit dem Punkt nach dem Doppelpunkt), sodass in den beiden Bereichsvariablen "prod" und "diff" nur der Zellbereich aufgenommen wird, der von unten her nach oben keine Leerzellen mehr enthält, sodass im Falle deiner Datei statt B15:B999 der Bereich B15:B75 (bzw. statt G15:G999 der Bereich G15:G75) verwendet wird. Das hat den Vorteil, dass du ab Zeile 76 weitere Eingaben anfügen kannst, die dynamisch korrekt in den Formeln ausgewertet werden.

 

Hi,

alternativ:

Code:

=LET(xa;B15:.G5900;xb;SPALTENWAHL(xa;6);xc;SORTIERENNACH(xa;xb;-1);xd;SPALTENWAHL(xc;1;6);ZEILENWAHL(xd;ZEILE(1:20)))
=LET(xa;B15:.G5900;xb;SPALTENWAHL(xa;6);xc;SORTIERENNACH(xa;xb;1);xd;SPALTENWAHL(xc;1;6);ZEILENWAHL(xd;ZEILE(1:20)))

 

Danke, aber die Formel scheint nicht ganz korrekt zu arbeiten. Es fehlen Werte.

Das scheint zu klappen.

Danke an alle!

 

Hallo,

welche Werte sollen denn fehlen? Du wolltest ja nur jeweils die 20 größten bzw. kleinsten Differenzen.

a) In der beiliegenden Datei habe ich alle Differenzen absteigend bzw. aufsteigend (orange Zellen) aufgeführt,
b) ferner deine 20 Differenzen (größte/kleinste) mit sämtlichen Linien (schwarz umrandet), in denen sie erscheinen.

 

Hallo Anton,

es fehlen die mehrfach vorhandenen 11er und 7er, wohl bedingt durch EINDEUTIG. Ist den unklaren Beschreibungen desTE geschuldet. Wenn mehrere Produkte mit gleicher Differenz vorkommen, sollen diese alle gelistet werden, wie es mit meiner Formel passiert.

 

Hallo Edgar,

danke für deinen Hinweis. Das habe ich inzwischen gemerkt, als ich deine Formeln herunterkopiert habe. Ich hatte jetzt Formeln erstellt, die eine vollständige Auflistung nach den 20 größten/kleinsten Differenzen durchführten - diese sind natürlich damit auch hinfällig (siehe Posting #10).

 

Moin,

ich werfe mal diese Formel in Runde:

=ÜBERNEHMEN(SORTIEREN(SPALTENWAHL(B15:G75;1;6);2;-1);20)

Für die größten Abnahmen einfach nur aus der -1 eine 1 machen.