Excel - optimaal rooster opstellen

[Getest]

Best lezers,
Ik heb een uitdaging waarvan ik overtuigd ben dat het te automatiseren is, ik weet alleen niet hoe. Hopelijk zijn er op dit forum mensen die hierbij kunnen helpen. Alvast hartelijk dank!

Mijn vraag
Mijn vrouw moet voor haar werk met enige regelmaat een rooster maken. Meerdere docenten moeten samen met elkaar een gesprek voeren over een leerling. Het streven is om -waar mogelijk- 2 leerling besprekingen parallel aan elkaar te plannen. Zodoende ontstaat een optimalisatie uitdaging waar jullie hopelijk bij kunnen helpen. De bedoeling is dus om zoveel mogelijk paren van 2 leerlingen te vinden, waarbij geen overlap in docenten ontstaat (een docent kan niet bij 2 gesprekken tegelijk zijn).

Ik zal een geanonimiseerde versie van het Excel bestand delen:
https://1drv.ms/x/s!AqXu7bZoG1uOjSSNXI4ezJt9wT-F?e=yIabPc

Relevante software en hardware die ik gebruik
Excel 2013 maar beschik ook over nieuwere versies (waaronder office365).

Wat ik al gevonden of geprobeerd heb
Op dit moment lossen we het vrijwel geheel handmatig op. Met behulp van dubbele waarden zoeken kijken we per (arbitrair) paar leerlingen of er sprake is van overlap van docenten. Als het maar een paar docenten en een paar leerlingen zouden zijn is dit nog wel te overzien, maar het gaat nu concreet om 21 leerlingen met 9 tot 14 docenten per leerling. Dit maakt het een erg intensief klusje, terwijl dat het (hopelijk) niet hoeft te zijn.

[F_J_K]

Ik begrijp de situatie niet helemaal. Moet over leerling1 gesproken worden door docent1+docent2, door docent2+3, door docent1+3, en en etc etc? Dan dus 91 gesprekken alleen al over leerling1? Als ik zo 1-2-3 geen denkfout maak.

Of alleen dat iedere leerling door eenmaal 2 willekeurige van zijn/haar docenten wordt besproken waar het dus niet uitmaakt of leerling1 door docent1+2 of docent7+14 wordt besproken, zolang die leerling maar in hun klassen voorkomt? Maakt het uit als docent1 21 gesprekken heeft en docent84 juist 0? En wat is parallel: zijn de kolommen bedoeld als tijdstippen, zo ja zijn er dus max 16 tijdslots?

[Tazzios]

Ik was aan het knutselen geslagen maar kom ook niet uit de vraag.
Kun je een voorbeeld maken van het resultaat?

[Getest]

Dank voor de reacties. Voor elke leerling moet 1 gesprek plaatsvinden. Achter elke leerling staan de namen van de docenten die de bespreking moeten doen. Dus 21 leerlingen = 21 gesprekken.

[g0tanks]

Getest schreef op zondag 4 oktober 2020 @ 15:01:
Dank voor de reacties. Voor elke leerling moet 1 gesprek plaatsvinden. Achter elke leerling staan de namen van de docenten die de bespreking moeten doen. Dus 21 leerlingen = 21 gesprekken.

Hoe praat dat met jouw voorbeeldbestand? Hoe moet ik die 14 verschillende docenten achter leerling 1 interpreteren?

[F_J_K]

Je gaat maar in op een van de vele vragen. Het lijkt me zinvol om de rest ook te adresseren, incl een concreet uitgewerkt (anoniem!) voorbeeld.

Er zijn 20 docenten die maar 1 leerling hebben. Daar heb je al geen dubbele boeking-probleem.

[gwystyl]

Volgens mij is dit een soort rooster voor bijvoorbeeld een rapportbespreking op de middelbare school. Veel leerlingen hebben dan een ander vakkenpakket, en dus ook niet allemaal dezelfde leerlingen.

Bij de school waar ik les heb gegeven was het pech voor de docent. Alle docenten die lesgaven aan een deel van die leerlingen, moesten bij het bespreken van al die leerlingen aanwezig zijn. Als er een leerling werd besproken waar je geen les aan gaf (en dat waren er nogal wat aangezien ik Scheikunde gaf) moest je maar even je oren dichtdoen oid.

Blijkbaar hebben ze dat bij de school van de vrouw van TS anders op proberen te lossen, maar moet dus de volgorde van het bespreken van de leerlingen zo worden gemaakt dat alle docenten zo min mogelijk tijd op school/bij de meeting hoeven te zijn. Dat is het handigst als zoveel mogelijk dan de leerlingen van elke docent achter elkaar besproken worden.

Ik ben niet handig genoeg met Excel om zoiets te automatiseren, maar ik hoop dat de vraagstelling zo wat duidelijker is.

@Getest is bovenstaande een beetje de situatie die je probeert te berekenen?

[Tazzios]

Probeersel.

https://famrodenburg.net/cloud/s/9KiGk8CzqRGfM4W

voor de zoek formule heb ik in de data een voorloop 0 toegevoegd anders is docent 11 ook een hit bij zoeken naar docent 1.

Het andere tabblad is de data genormaliseerd maar die had ik niet nodig.

[ Voor 65% gewijzigd door Tazzios op 04-10-2020 15:39 ]

[Getest]

Ik zal m'n best doen om de vragen te beantwoorden.

g0tanks schreef op zondag 4 oktober 2020 @ 15:24:
[...]


Hoe praat dat met jouw voorbeeldbestand? Hoe moet ik die 14 verschillende docenten achter leerling 1 interpreteren?

Leerling 1 heeft 14 docenten die hem of haar les geven. Al die docenten moeten in principe in de gelegenheid zijn om die leerling te bespreken. Dus 1 gesprek over 1 leerling, waar 14 docenten aanwezig zijn.

F_J_K schreef op zondag 4 oktober 2020 @ 15:26:
Je gaat maar in op een van de vele vragen. Het lijkt me zinvol om de rest ook te adresseren, incl een concreet uitgewerkt (anoniem!) voorbeeld.

Er zijn 20 docenten die maar 1 leerling hebben. Daar heb je al geen dubbele boeking-probleem.

De leerlingen staan centraal, zij moeten besproken worden.

De simpelste oplossing zou zijn om alle 78 docenten (uit m'n hoofd) een overleg te laten voeren waar alle 21 leerlingen worden besproken. Maar lang niet alle docenten hebben alle leerlingen, dus een groot gedeelte van de vergadering zou voor hen niet relevant zijn. Bovendien worden er zaken over de leerlingen besproken die alleen gedeeld moeten worden aan de docenten voor wie dat relevant voor het werk is.

[Getest]

gwystyl schreef op zondag 4 oktober 2020 @ 15:34:
Volgens mij is dit een soort rooster voor bijvoorbeeld een rapportbespreking op de middelbare school. Veel leerlingen hebben dan een ander vakkenpakket, en dus ook niet allemaal dezelfde leerlingen.

Bij de school waar ik les heb gegeven was het pech voor de docent. Alle docenten die lesgaven aan een deel van die leerlingen, moesten bij het bespreken van al die leerlingen aanwezig zijn. Als er een leerling werd besproken waar je geen les aan gaf (en dat waren er nogal wat aangezien ik Scheikunde gaf) moest je maar even je oren dichtdoen oid.

Blijkbaar hebben ze dat bij de school van de vrouw van TS anders op proberen te lossen, maar moet dus de volgorde van het bespreken van de leerlingen zo worden gemaakt dat alle docenten zo min mogelijk tijd op school/bij de meeting hoeven te zijn. Dat is het handigst als zoveel mogelijk dan de leerlingen van elke docent achter elkaar besproken worden.

(...)

@Getest is bovenstaande een beetje de situatie die je probeert te berekenen?

Inderdaad, dank!

[F_J_K]

Ah, ik had het geïnterpreteerd als dat er slechts 2 docenten bij het gesprek aanwezig zijn. Maar dat zijn er dus 'gewoon' ca. 14 per gesprek. Dat is logischer ja.

[jagermaster]

Er mist nog heel veel informatie. Aangezien je het over ongeveer 14 docenten hebt interpreteer ik het dat het over het voortgezet onderwijs gaat. Hebben we het over onderbouw of bovenbouw ? Een leerling zit in een klas hoe is dit vormgegeven?
Welke basisbeginselen zitten beleidsmatig achter de les verdeling, eventuele keuzevakken?

Als je vrouw met enige regelmaat roosters moet maken dan is ze waarschijnlijk rooster maker? Welke rooster software wordt er gebruikt en zit hier niet een module in voor deze roosters?

Je spreekt over 78 docenten, ik gok dat je dus te maken hebt met een een 900-1000 leerlingen?
Als dit allemaal individuele roosters zijn adviseer ik je te stoppen met Excel wat dat gaat niet lukken met Excel. Daarnaast een factor is dat je de besprekingen ook zeer waarschijnlijk parralel wilt roosteren. Welke eisen leg je hieraan? Hoeveel procent minimaal theoretisch aanwezig?

[ANdrode]

jagermaster schreef op zondag 4 oktober 2020 @ 16:07:
Als dit allemaal individuele roosters zijn adviseer ik je te stoppen met Excel wat dat gaat niet lukken met Excel. Daarnaast een factor is dat je de besprekingen ook zeer waarschijnlijk parralel wilt roosteren. Welke eisen leg je hieraan? Hoeveel procent minimaal theoretisch aanwezig?

Je wilt logischerwijs toch de totale bestede uren minimaliseren?

Mijn denkrichting was eerst om dit gewoon in een SMT-solver te modeleren. Maar de variant waar je eerst clustert en daarna de roosters maakt is computationeen veel efficiënter 🙄

[Getest]

jagermaster schreef op zondag 4 oktober 2020 @ 16:07:
Er mist nog heel veel informatie. Aangezien je het over ongeveer 14 docenten hebt interpreteer ik het dat het over het voortgezet onderwijs gaat. Hebben we het over onderbouw of bovenbouw ? Een leerling zit in een klas hoe is dit vormgegeven?
Welke basisbeginselen zitten beleidsmatig achter de les verdeling, eventuele keuzevakken?

Als je vrouw met enige regelmaat roosters moet maken dan is ze waarschijnlijk rooster maker? Welke rooster software wordt er gebruikt en zit hier niet een module in voor deze roosters?

Je spreekt over 78 docenten, ik gok dat je dus te maken hebt met een een 900-1000 leerlingen?
Als dit allemaal individuele roosters zijn adviseer ik je te stoppen met Excel wat dat gaat niet lukken met Excel. Daarnaast een factor is dat je de besprekingen ook zeer waarschijnlijk parralel wilt roosteren. Welke eisen leg je hieraan? Hoeveel procent minimaal theoretisch aanwezig?

Het gaat inderdaad om een middelbare school. De casus is het bespreken van leerlingen die om verschillende redenen extra zorg nodig hebben.

Mijn vrouw is geen roostermaker, maar heeft een rol in de zorg. Zij coördineert dat alle docenten van leerlingen die zorg behoeven, op de hoogte zijn van wat er met die leerlingen is en handelingsperspectieven krijgen hoe ze daar mee om moeten gaan. Bijvoorbeeld leerlingen met specifieke vormen van autisme.

Dus je probeert te optimaliseren dat alle 21 leerlingen op één avond besproken kunnen worden, waarbij alle docenten die de leerling les geven aanwezig kunnen zijn. Dit wil je zo efficiënt mogelijk indelen, vanuit het perspectief van de docenten.

[ Voor 7% gewijzigd door Getest op 04-10-2020 16:31 ]

[Tazzios]

@Getest mijn excel al bekeken?

[jagermaster]

ANdrode schreef op zondag 4 oktober 2020 @ 16:19:
[...]


Je wilt logischerwijs toch de totale bestede uren minimaliseren?

Mijn denkrichting was eerst om dit gewoon in een SMT-solver te modeleren. Maar de variant waar je eerst clustert en daarna de roosters maakt is computationeen veel efficiënter 🙄

eerst clusteren is echt noodzakelijk maar wel afhankelijk hoe de keuzes zijn opgebouwd dus of dit zin heeft om zo te clusteren. Als bijvoorbeeld in de school de voorwaarde is dat iedereen tenminste 90% van zijn lessen in zijn eigen team geeft kan je ook zeggen alle leerlingen uit het team doe je achter elkaar en de de teams ten opzichte van elkaar parallel
. Zit je nog wel met een kanttekening hoe groot een team is en of iedereen besproken moet worden. Heb je 360 leerlingen in een team en besteed je 5 minuten per leerling ben je 30 uur verse.

[Getest]

Tazzios schreef op zondag 4 oktober 2020 @ 16:24:
@Getest mijn excel al bekeken?

Ja, dank daarvoor! Even een check: bij leerling 3 lijkt het alsof die tegelijkertijd met elke andere leerling zou kunnen, maar bijvoorbeeld docent 6 komt ook voor bij leerling 1. Kan het zijn dat dit een foutje in de formule is, of interpreteer ik het verkeerd?

Verder denk ik dat dit een hele goede eerste stap is om tot een oplossing te komen. Ik kan me voorstellen dat een volgende stap is om de volgorde te bepalen.

[jagermaster]

Getest schreef op zondag 4 oktober 2020 @ 16:23:
[...]

Het gaat inderdaad om een middelbare school. De casus is het bespreken van leerlingen die om verschillende redenen extra zorg nodig hebben.

Mijn vrouw is geen roostermaker, maar heeft een rol in de zorg. Zij coördineert dat alle docenten van leerlingen die zorg behoeven, op de hoogte zijn van wat er met die leerlingen is en handelingsperspectieven krijgen hoe ze daar mee om moeten gaan. Bijvoorbeeld leerlingen met specifieke vormen van autisme.

Dus je probeert te optimaliseren dat alle 21 leerlingen op één avond besproken kunnen worden, waarbij alle docenten die de leerling les geven aanwezig kunnen zijn. Dit wil je zo efficiënt mogelijk indelen, vanuit het perspectief van de docenten.

dit op een avond is vanuit docentperspectief nooit gunstig. Qua AVG prima te verantwoorden dan de voorlichting per team wordt gegeven.

21 leerlingen met zorg vind ik niet zoveel. Zitten deze door de hele school, dus 90% van de leerlingen hebben te maken met 1 of meerdere zorgleerlingen dan kom je niet op een vanuit docentoogpunt op een goed rooster. Belangrijk is hoeveel tijd is er nodig per leerling, dus met wat voor soort ernst van problematiek heb je te maken en hoeveel overlap er tussen zit qua problematiek. Op het moment dat docenten in en uit moeten lopen verlies je veel tijd en met roosteren haal je de flexibiliteit eruit dat het iets uit kan lopen of je toch minder tijd nodig hebt.

[Tazzios]

Getest schreef op zondag 4 oktober 2020 @ 16:30:
[...]

Ja, dank daarvoor! Even een check: bij leerling 3 lijkt het alsof die tegelijkertijd met elke andere leerling zou kunnen, maar bijvoorbeeld docent 6 komt ook voor bij leerling 1. Kan het zijn dat dit een foutje in de formule is, of interpreteer ik het verkeerd?

Verder denk ik dat dit een hele goede eerste stap is om tot een oplossing te komen. Ik kan me voorstellen dat een volgende stap is om de volgorde te bepalen.

Je interpretatie klopt
Er zit nog wel wat structureel fout in de controle, zo is ook de rij met kruisjes vreemd dat het niet bij de laatste rijen nodig is.

Maar zou je aan zoiets wat hebben?

[Getest]

Tazzios schreef op zondag 4 oktober 2020 @ 16:55:
[...]

Je interpretatie klopt
Er zit nog wel wat structureel fout in de controle, zo is ook de rij met kruisjes vreemd dat het niet bij de laatste rijen nodig is.

Maar zou je aan zoiets wat hebben?

Ja zeker, dat helpt om de overlap te bepalen en dat is een belangrijk element in de puzzel.

Uiteindelijk wil je de volgorde kunnen bepalen waarin de leerlingen besproken worden, waarbij idealiter zoveel mogelijk 2 leerlingen parallel aan elkaar besproken worden. Hoe je de volgorde kunt bepalen die "zo ideaal mogelijk" is voor de docenten lijkt mij ook nog een uitdaging.

[Tazzios]

Excel bijgewerkt, werkt volgensmij nu wel goed
Van de formules wordt denk alleen niemand vrolijk,Zie het als een POC

dezelfde link: https://famrodenburg.net/cloud/s/9KiGk8CzqRGfM4W

[ Voor 21% gewijzigd door Tazzios op 04-10-2020 17:14 ]

[Getest]

Tazzios schreef op zondag 4 oktober 2020 @ 17:14:
Excel bijgewerkt, werkt volgensmij nu wel goed
Van de formules wordt denk alleen niemand vrolijk,Zie het als een POC

dezelfde link: https://famrodenburg.net/cloud/s/9KiGk8CzqRGfM4W

Haha nou het concept is bewezen, dat zeker Dank voor het meedenken, dit scheelt al een hoop!

P.S. ik heb een tijd als product owner gewerkt met een data science team, ik ben er van overtuigd dat dit nog veel verder opgelost kan worden maar iets zegt mij dat je dan tegen de grenzen van Excel aanloopt. Waarschijnlijk is het totaal niet rendabel qua tijdsinvestering - tijdsbesparing en dit zeg ik ook absoluut niet om afbreuk te doen aan de hulp die je hebt gegeven of om Excel af te kraken, maar als je dit soort puzzels leuk vind dan is het misschien leuk om ook eens andere tooling (zoals R Studio) te verkennen.

Nogmaals dank!
Hartelijke groet