Hallo zusammen,
wir haben bei uns in der Firma jedes Jahr an die 20 Lehrlinge die wir nach Abschluss des 3. Lehrjahres in einen Ausbildungsplatz für 4. Lehrjahr zuweisen.
Für diese Zuweisung gibt der Lehrling ein Ranking der Abteilungen ab und die Abteilungen ebenfalls ein Ranking der Lehrlinge.
Diese beiden Werte werden dann noch mit dem Faktor Arbeitsweg addiert und in eine Liste Eingetragen.
Mein Problem:
Ich kann in der Liste nicht einfach von jedem Lehrling den geringsten Wert nehmen und diese Abteilung zuweisen da dann die Verteilung nicht stimmt. (in der einen Abteilung zu viele und in der anderen zu wenige)
Mich würde nun interessieren ob es eine Möglichkeit gibt, die kleinst mögliche Kombination aus dieser Zahlentabelle, anhand der benötigten Anzahl an Lehrlingen pro Abteilung, an zu zeigen.
Ich hoffe ich habe mich annähernd verständlich ausgedrückt und freue mich auf eure Rückmeldung.
Christoph
Hallo,
theoretisch könntest Du dir über RANG.MITTELW(C4;$C$4:$M$23;1) die Tabelle Neu aufziehen und die Reihenfolge anzeigen lassen. Danach könntest du über bedingte Formatierung nach und nach die Zellen anzeigen lassen und dir darüber (recht umständlich) nach und nach die ganze Liste mit den jeweils niedrigsten rängen auffüllen.
Daraus würde Resultieren:
1: Lehrlinge 4,7,16,18
2: 6,10,11,12
3: 1,9,20
4: 15,19
5:
6: 17
7: 2
8: 5,8
9: 3,13
10:
11: 14
Vorbehaltlich etwaiger Ablesungsfehler
EDIT: mit KKleinste könntest du dir unter deiner so entstandenen Rangtabelle auch die entsprechenden kleinsten werte auswerfen lassen und diese dann vermutl. über einen Zeilenabgleich o.Ä. gegenprüfen auf bereits vorhanden sein.
Hallo Christoph,
mit nur einer dafür etwas komplexeren Formel, kann mE eine einigermaßen für alle vertretbare Zuordnung vorgenommen werden.
Wobei natürlich durch die Reihenfolge des Listings der Lehrlinge sowie der Abteilungen bei gleichen Werten die jeweils oberen gelistetn zunächst "bevorzugt" zugeordnet werden. Dafür erhalten die Abteilungen, die mehr als der Durchschnitt an Lehrlingen aufnehmen auch Lehrlinge zugeordnet, die wenig gute Rankingwerte vergeben haben.
Nachfolgende
Formel R4 nach unten und weit genug nach rechts ziehend kopieren.
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V |
| 2 | | | Abteilungen | | benötigt pro Abt. | | | | | | |
| 3 | | | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | | Abteilung | Anzahl | | | | | | |
| 4 | | Lehrling 1 | 3,3 | 3,6 | 2,4 | 7,1 | 6,8 | 4,5 | 6,9 | 9,4 | 9,4 | 9,4 | 9,4 | | 1 | 4 | | Lehrling 4 | Lehrling 16 | Lehrling 17 | Lehrling 10 | |
| 5 | | Lehrling 2 | 5,7 | 6,1 | 2,8 | 4,1 | 3,7 | 3,9 | 2,3 | 8,3 | 8,3 | 8,3 | 8,3 | | 2 | 4 | | Lehrling 20 | Lehrling 11 | Lehrling 12 | Lehrling 5 | |
| 6 | | Lehrling 3 | 5,2 | 3,8 | 5,2 | 7,8 | 8,0 | 8,3 | 8,2 | 5,6 | 3,1 | 5,6 | 5,6 | | 3 | 3 | | Lehrling 1 | Lehrling 6 | Lehrling 19 | | |
| 7 | | Lehrling 4 | 2,3 | 3,1 | 3,4 | 9,1 | 8,8 | 9,0 | 8,9 | 11,4 | 11,4 | 11,4 | 11,4 | | 4 | 2 | | Lehrling 2 | Lehrling 15 | | | |
| 8 | | Lehrling 5 | 9,4 | 9,9 | 6,3 | 10,4 | 10,0 | 10,2 | 9,4 | 6,5 | 7,5 | 7,5 | 7,5 | | 5 | 0 | | | | | | |
| 9 | | Lehrling 6 | 3,9 | 5,3 | 5,1 | 11,8 | 11,4 | 6,7 | 11,6 | 7,0 | 7,0 | 7,0 | 7,0 | | 6 | 1 | | Lehrling 7 | | | | |
| 10 | | Lehrling 7 | 2,7 | 3,4 | 3,3 | 6,9 | 6,7 | 3,8 | 6,8 | 9,2 | 9,2 | 9,2 | 9,2 | | 7 | 1 | | Lehrling 9 | | | | |
| 11 | | Lehrling 8 | 5,3 | 5,3 | 5,1 | 11,3 | 11,3 | 11,4 | 11,1 | 2,3 | 2,8 | 3,3 | 3,3 | | 8 | 2 | | Lehrling 18 | Lehrling 14 | | | |
| 12 | | Lehrling 9 | 5,2 | 5,4 | 3,8 | 8,4 | 8,3 | 8,5 | 2,7 | 7,3 | 7,3 | 7,3 | 7,3 | | 9 | 2 | | Lehrling 13 | Lehrling 3 | | | |
| 13 | | Lehrling 10 | 5,6 | 7,1 | 7,4 | 11,6 | 11,2 | 6,2 | 11,9 | 8,5 | 8,5 | 8,5 | 8,5 | | 10 | 0 | | | | | | |
| 14 | | Lehrling 11 | 5,5 | 2,7 | 5,6 | 11,2 | 11,4 | 11,6 | 11,5 | 5,0 | 4,0 | 5,0 | 5,0 | | 11 | 1 | | Lehrling 8 | | | | |
| 15 | | Lehrling 12 | 8,9 | 6,2 | 9,7 | 7,7 | 7,8 | 8,1 | 8,6 | 6,9 | 5,9 | 6,9 | 6,9 | | | | | | | | | |
| 16 | | Lehrling 13 | 6,9 | 7,0 | 4,1 | 9,5 | 9,4 | 9,5 | 9,1 | 3,4 | 2,4 | 3,4 | 3,4 | | | | | | | | | |
| 17 | | Lehrling 14 | 2,9 | 5,8 | 5,6 | 8,3 | 7,9 | 8,2 | 8,1 | 4,0 | 5,0 | 5,0 | 5,0 | | | | | | | | | |
| 18 | | Lehrling 15 | 8,8 | 8,8 | 8,6 | 4,3 | 4,8 | 4,9 | 3,6 | 15,3 | 15,3 | 15,3 | 15,3 | | | | | | | | | |
| 19 | | Lehrling 16 | 2,5 | 5,2 | 5,4 | 7,7 | 7,6 | 7,6 | 7,8 | 6,5 | 5,5 | 6,5 | 6,5 | | | | | | | | | |
| 20 | | Lehrling 17 | 3,8 | 5,1 | 5,4 | 9,6 | 9,3 | 4,0 | 9,9 | 8,7 | 8,7 | 8,7 | 8,7 | | | | | | | | | |
| 21 | | Lehrling 18 | 2,7 | 5,4 | 5,3 | 9,4 | 9,2 | 9,3 | 9,3 | 2,2 | 3,2 | 3,2 | 3,2 | | | | | | | | | |
| 22 | | Lehrling 19 | 6,7 | 7,4 | 5,8 | 7,4 | 7,3 | 7,5 | 6,7 | 15,8 | 15,8 | 15,8 | 15,8 | | | | | | | | | |
| 23 | | Lehrling 20 | 3,5 | 2,7 | 2,7 | 7,2 | 7,1 | 7,3 | 6,7 | 9,6 | 9,6 | 9,6 | 9,6 | | | | | | | | | |
| 24 | | | | | | | | | | | | | | | | | | | | | | |
| 25 | | | | | | | | | | | | | | | | | | | | | | |
| Zelle | Formel |
| R4 | =WENNFEHLER(INDEX($B:$B;AGGREGAT(15;6;ZEILE(B$4:B$23)/($P4>=SPALTE(A4))/($C$3:$M$3=$O4)/($C$4:$M$23=AGGREGAT(15;6;$C$4:$M$23/($C$3:$M$3=$O4)/(ZÄHLENWENN($Q$4:Q$14;$B$4:$B$23)=0)/(ZÄHLENWENN(R$3:R3;$B$4:$B$23)=0);1));1));"") |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hallo,
vielen Dank für eure Lösungen. Dabei ist schon mal eine deutlich besserer Schnitt entstanden als bei mir. :19:
Dabei Funktioniert die von "Stoffo" etwas besser hier kommt ein Schnitt von 77,7 über alle Lehrlinge zustande bei der von "Neopa" ist leider einer von 79 raus gekommen. Allerdings ist die Zweitere natürlich deutlich eleganter :21:
@Stoffo: Habe gerade die Tabelle wie du geschrieben hast neu erstellt und mit Bedingter Formatierung Lehrling für Lehrling vergeben. Es war wie du beschrieben hast ein bisschen ein Aufwand aber hat für mich prima geklappt. Bin sogar mit dem Durchschnitt
noch weiter nach unten gekommen. :19:
1:4;7;14;16
2:6;10;11;12
3:1;9;20
4:15;19
5
6:17
7:2
8:8;18
9:3;13
10
11:5
Danke nochmals für die Lösung und auch nochmals danke an Werner
