Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1448to1452
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Index/Vergleich nach Rang neu ordnen

Index/Vergleich nach Rang neu ordnen
27.09.2015 16:59:04
Joerschi
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

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: ... und AGGREGAT() und WENNFEHLER() ...
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
.. , - ...

Anzeige
AW: ... und AGGREGAT() und WENNFEHLER() ...
27.09.2015 17:26:56
Joerschi
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

AW: ... Du hast Recht, ich korrigiere ...
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
.. , - ...

Anzeige
AW: ... Du hast Recht, ich korrigiere ...
27.09.2015 17:37:56
Joerschi
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

AW: verschiebe ich ...
27.09.2015 17:50:04
...
Hallo Joerschi,
... ich geh jetzt erst einmal offline.
Gruß Werner
.. , - ...

Anzeige
AW: dazu siehe ...
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
.. , - ...

AW: Index/Vergleich nach Rang neu ordnen
27.09.2015 17:25:55
Sepp
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

Anzeige
AW: Index/Vergleich nach Rang neu ordnen
27.09.2015 17:30:57
Joerschi
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

AW: Index/Vergleich nach Rang neu ordnen
27.09.2015 17:34:57
Sepp
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

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

AW: die Ergebnisse sind jetzt identisch ...
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
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige