Index/Vergleich nach Rang neu ordnen

Bild

Betrifft: Index/Vergleich nach Rang neu ordnen
von: Joerschi
Geschrieben am: 27.09.2015 16:59:04

Hallo liebes Excel-Forum,
folgendes Problem soll gelöst werden - Skizze zuerst, Erklärung darunter:
Userbild
https://www.herber.de/bbs/user/100427.xlsx
Es gibt eine Ausgangstabelle (Nr. 1) B3:P3, wo Namen und darunter zugehörige Werte drinstehen.
Jeder Name kommt nur einmal vor.
Desweiteren gibt es eine Rankingtabelle (Nr. 2) B6:P8 sortiert nach aufsteigendem Rang, wo ebenfalls wieder Namen und darunter Werte stehen.
Besonderheit ist, dass Namen mehrfach vorkommen können.
Hinweis, falls zur Lösung dienlich: Wenn Namen mehrfach vorkommen, so ist der zugehörige Wert darunter höher, je weiter rechts der Name steht (also je höher der Rang ist).
Was soll gelöst werden?
In Zelle E11 wird manuell ein Rang festgelegt.
In der Lösungstabelle (Nr. 3) sollen nun wieder die Werte der Ausgangstabelle stehen mit der Ausnahme: es sollen alle Werte derjenigen Namen ersetzt werden, welche bis zum manuell festgelegten Rang (E11) in der Rankingtabelle anders sind.
Kommen Namen in der Rankingtabelle mehrfach vor, dann soll der Wert mit dem höchsten Rang eingesetzt in der Lösungstabelle werden.
Als Beispiel - siehe Skizze: Die Zahl 9 ist in E11 als Rankingmaximum vorgegeben, was bedeutet, dass alle Namen/Werte von B6:J8 geprüft werden und dann in B14:P15 zugeordnet (K6:P8 demnach ignoriert...).
Dabei hat der Name "X1" zum Beispiel drei verschiedene Werte - der höchste davon (aus Zelle I7) wird nun beim zugehörigen Name in Zelle B15 eingesetzt.
Die richtige Lösung für das Skizzenbeispiel ist in Zeile 16 kursiv enthalten.
Hat jemand einen Ansatz für eine Formellösung für Zelle B15 ff.?
(Mein Ansatz wäre irgendeine Kombi mit Rang/Index/Vergleich...?)
Danke im Voraus und liebe Grüße
Joerschi

Bild

Betrifft: AW: ... und AGGREGAT() und WENNFEHLER() ...
von: ... neopa C
Geschrieben am: 27.09.2015 17:17:35
Hallo Joerschi,
... Formel B15 nach rechts kopieren:

 BCDEFGHIJKLMNOP
14X1X2X3X4X5X6X7X8X9X10X11X12X13X14X15
157,000,751,752,005,506,303,500,002,000,751,758,004,751,7515,75
167,000,751,752,005,506,303,500,001,750,752,508,004,751,7515,75

Formeln der Tabelle
ZelleFormel
B15=WENNFEHLER(INDEX(8:8;AGGREGAT(14;6;SPALTE($B7:$P7)/(SPALTE($B7:$P7)<=VERGLEICH($E11;6:6;)-1)/($B7:$P7=B14); 1)); B3)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Bild

Betrifft: AW: ... und AGGREGAT() und WENNFEHLER() ...
von: Joerschi
Geschrieben am: 27.09.2015 17:26:56
Hi Werner,
Danke Dir erstmal.
Irgendwo ist aber noch ein Fehler :-(
Wenn man z. B. E11 auf "1" ändert, dürfte sich ja in B15 der Wert nur von 3,5 zu 3,75 ändern (passiert aber nicht).
Oder wenn man die Vorgabe auf "10" stellt, müsste sich gegenüber der Beispiellösung von Dir der Wert von dem Name "X14" in Zelle O15 von 1,75 auf 2,9 ändern.
Liebe Grüße
Joerschi

Bild

Betrifft: AW: ... Du hast Recht, ich korrigiere ...
von: ... neopa C
Geschrieben am: 27.09.2015 17:34:14
Hallo Joerschi,
... so ist es auch noch "einfacher":

 ABCDEFGHIJKLMNOP
1 Ausgangstabelle (Nr. 1) - vorgegeben
2Name:X1X2X3X4X5X6X7X8X9X10X11X12X13X14X15
3Wert:3,500,751,750,755,254,253,500,001,750,751,754,754,751,7515,75
4                
5 Rankingtabelle (Nr. 2) - vorgegeben
6Rang:123456789101112131415
7Name:X1X4X1X9X12X6X5X1X11X14X4X4X7X6X1
8Wert:3,752,004,802,008,006,305,507,002,502,903,005,004,807,4014,00
9                
10                
11Vorgabe - bis welcher Rang?:11           
12                
13 Lösungstabelle (Nr. 3)
14Name:X1X2X3X4X5X6X7X8X9X10X11X12X13X14X15
15Wert:7,000,751,753,005,506,303,500,002,000,752,508,004,752,9015,75
16(richtige Lösung)7,000,751,752,005,506,303,500,001,750,752,508,004,751,7515,75

Formeln der Tabelle
ZelleFormel
B15=WENNFEHLER(AGGREGAT(14;6;$B8:$P8/(SPALTE($B7:$P7)<=VERGLEICH($E11;6:6;))/($B7:$P7=B14); 1); B3)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Bild

Betrifft: AW: ... Du hast Recht, ich korrigiere ...
von: Joerschi
Geschrieben am: 27.09.2015 17:37:56
Yup - jetzt passt es :-)
Herzlichen Dank Dir - und natürlich auch an Sepp!
@ neopa: Wenn es Deine Zeit zulässt, kannst Du da nochmal über das (vor)gestrige Thema schauen?
Wenn es ohne die Datentabelle Nr. 2 und Performanceverlust nicht geht, dann sollen die gesuchten Lösungen direkt von Nr. 2 abgeleitet werden (Datentabelle Nr. 3 kann dann komplett ignoriert werden)
https://www.herber.de/forum/archiv/1448to1452/t1449254.htm
Liebe Grüße
Joerschi

Bild

Betrifft: AW: verschiebe ich ...
von: ... neopa C
Geschrieben am: 27.09.2015 17:50:04
Hallo Joerschi,
... ich geh jetzt erst einmal offline.
Gruß Werner
.. , - ...

Bild

Betrifft: AW: dazu siehe ...
von: ... neopa C
Geschrieben am: 28.09.2015 09:27:21
Hallo Joerschi,
... meinen aktuellen Beitrag in https://www.herber.de/forum/archiv/1448to1452/t1449254.htm. Deine Aussage hier im thread: "Datentabelle Nr. 3 kann dann komplett ignoriert werden" verwirrt nun allerdings mich momentan. Die Ergebnisse in Tabelle 2 sollten doch aus der Tabelle 3 ermittelt werden. oder?
Gruß Werner
.. , - ...

Bild

Betrifft: AW: Index/Vergleich nach Rang neu ordnen
von: Sepp
Geschrieben am: 27.09.2015 17:25:55
Hallo Joerschi,
bei X9 ist dir, meiner meinung nach, ein Fehler unterlaufen.
So?
Tabelle1

 ABCDEFGHIJKLMNOPQ
1 Ausgangstabelle (Nr. 1) - vorgegeben               
2Name:X1X2X3X4X5X6X7X8X9X10X11X12X13X14X15 
3Wert:3,500,751,750,755,254,253,500,001,750,751,754,754,751,7515,75 
4                 
5 Rankingtabelle (Nr. 2) - vorgegeben               
6Rang:123456789101112131415 
7Name:X1X4X1X9X12X6X5X1X11X14X4X4X7X6X1 
8Wert:3,752,004,802,008,006,305,507,002,502,903,005,004,807,4014,00 
9                 
10                 
11Vorgabe - bis welcher Rang?:   9            
12                 
13 Lösungstabelle (Nr. 3)               
14Name:X1X2X3X4X5X6X7X8X9X10X11X12X13X14X15 
15Wert:7,000,751,752,005,506,303,500,002,000,752,508,004,751,7515,75 
16(richtige Lösung)7,000,751,752,005,506,303,500,001,750,752,508,004,751,7515,75 
17                 

Formeln der Tabelle
ZelleFormel
B15=WENN((ISTZAHL($E$11)*($E$11>0)*($E$11<=15)); WENNFEHLER(AGGREGAT(14;6;$B$8:$P$8/($B$7:INDEX($B$7:$P$7;;$E$11)=B14); 1); INDEX($B$3:$P$3;VERGLEICH(B14;$B$2:$P$2;0))); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp


Bild

Betrifft: AW: Index/Vergleich nach Rang neu ordnen
von: Joerschi
Geschrieben am: 27.09.2015 17:30:57
Hallo Sepp,
Danke auch Dir.
Scheint zu funktionieren, außer: Bei Vorgabe Zelle E11 = "1", wird noch falsch gerechnet. In B15 kommt dann eine "14", die scheinbar aus der Zelle P8 übernommen wird (welche bei vorgenannter Vorgabe aber gar nicht mit beachtet wird.)
Liebe Grüße
Joerschi

Bild

Betrifft: AW: Index/Vergleich nach Rang neu ordnen
von: Sepp
Geschrieben am: 27.09.2015 17:34:57
Hallo Joerschi,
hast Recht!
Tabelle1

 ABCDEFGHIJKLMNOPQ
1 Ausgangstabelle (Nr. 1) - vorgegeben               
2Name:X1X2X3X4X5X6X7X8X9X10X11X12X13X14X15 
3Wert:3,500,751,750,755,254,253,500,001,750,751,754,754,751,7515,75 
4                 
5 Rankingtabelle (Nr. 2) - vorgegeben               
6Rang:123456789101112131415 
7Name:X1X4X1X9X12X6X5X1X11X14X4X4X7X6X1 
8Wert:3,752,004,802,008,006,305,507,002,502,903,005,004,807,4014,00 
9                 
10                 
11Vorgabe - bis welcher Rang?:   1            
12                 
13 Lösungstabelle (Nr. 3)               
14Name:X1X2X3X4X5X6X7X8X9X10X11X12X13X14X15 
15Wert:3,750,751,750,755,254,253,500,001,750,751,754,754,751,7515,75 
16(richtige Lösung)7,000,751,752,005,506,303,500,001,750,752,508,004,751,7515,75 

Formeln der Tabelle
ZelleFormel
B15=WENN((ISTZAHL($E$11)*($E$11>0)*($E$11<=15)); WENNFEHLER(AGGREGAT(14;6;$B$8:INDEX($B$8:$P$8;;$E$11)/($B$7:INDEX($B$7:$P$7;;$E$11)=B14); 1); INDEX($B$3:$P$3;VERGLEICH(B14;$B$2:$P$2;0))); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp


Bild

Betrifft: PS: Mit dem Fehler hattest Du Recht...
von: Joerschi
Geschrieben am: 27.09.2015 17:33:39
das war manuell falsch eingetragen.
wie man sieht: zum Glück gibts Formeln :-)

Bild

Betrifft: AW: die Ergebnisse sind jetzt identisch ...
von: ... neopa C
Geschrieben am: 27.09.2015 17:48:58
Hallo Sepp, hallo joerschi,
... wie eine eben erfolgte Vergleichsrechnung zwischen der Formel von Sepp und der von mir ergeben hat.
Gruß Werner
.. , - ...

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Index/Vergleich nach Rang neu ordnen"