Namen trekken voor Kerstmis
- 11/11/2017
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Over een goede maand is het alweer zover, de feestdagen komen eraan! Traditioneel worden er dan vaak cadeau's gegeven aan dierbaren, waarbij lottrekking bepaalt wie aan wie geeft. In dit artikel maken we dit na in Excel: we laten Excel bepalen hoe we de personen met mekaar matchen. Ben je eerder op zoek naar een bestand waarmee je gemeenschappelijke aankopen voor Kerstmis kan terugbetalen, kijk dan zeker op deze pagina.
De doelstelling van het artikel is ondermeer:
- tabelfunctionaliteit te laten zien
- functies te laten zien die misschien nieuw zijn voor de lezers
Voorbeeld
We hebben hier 3 koppels en een vrijgezel: Tom & Marie, Luc & Christine, Peter & Magda, en last but not least Jan. Zij moeten gematcht worden met mekaar, op voorwaarde dat:
- je niet met jezelf gematcht wordt (geen cadeau voor jezelf voorzien)
- je niet met je partner gematcht wordt (geen cadeau voor je partner voorzien)
De namen zetten we uit in een tabel, eerste kolom. Vervolgens nemen we de functie om een willekeurig getal tussen 0 en 1 te zetten: ASELECT in het Nederlands, RAND in het Engels. Maar we zijn niet zozeer geïnteresseerd in de waarde van het willekeurige getal, maar eerder willen we de getallen van 1 tot en met 7 (we zijn met 7 personen) op een willekeurige manier sorteren. Als we nu eens de RANG functie gebruiken ? Dan geeft Excel ons direct op basis van de willekeurige getallen de getallen 1 tot en met 7 :-) Als we dan de namen ophalen op positie 1, 2, …; 7, dan hebben we meteen willekeurig gematchte namen.
Nu resten er ons nog 2 problemen. Niets belet ons dat de personen met zichzelf gematcht worden. Of met hun partner, indien ze die hebben. Deze 2 problemen pakken we aan in de kolommen E en F. De functie in kolom E is eenvoudig:
=--( [@Gever] = [@Ontvanger] )
Als wat staat in de kolom "Gever" gelijk is aan wat staat in de kolom "Ontvanger", voor elke rij, dan krijg je WAAR. Anders ONWAAR. De -- operator gaat WAAR vertalen naar 1, en ONWAAR vertalen naar 0. Dat telt dan simpelweg op! Is de som in de totaalrij 9 strikt positief, dan hebben we een geval van iemand die gematcht is met zichzelf.
Heb je gemerkt hoe leesbaar formules worden als je tabelfunctionaliteit werkt ? In plaats van A2 en D2, A7 en D7, enz. krijg je de namen van de kolommen in de formules: Gever, Ontvanger, Random, enz. Dit leest en werkt een stuk prettiger en daarnaast zal de tabel er ook zorg voor dragen dat jouw formules doorgevoerd worden tot beneden.
De functie in kolom F is niet zo eenvoudig:
=--ISGETAL( VERGELIJKEN( [@Gever] & "_" & [@Ontvanger]; Tbl_Partner[Koppel]; 0 ))
We gaan voor elke rij Gever en Ontvanger aan mekaar vastplakken met een underscore (_) in het midden. Bijvoorbeeld: "Christine_Peter". Dit zoeken we op in de tabel rechts, in de laatste kolom. In de tabel rechts houden we bij wie partners zijn. Komt de samengevoegde tekst dus voor in kolom 3 van de tabel rechts, dan hebben we het geval dat een persoon gematch werd met zijn partner. Dat geval willen we uitsluiten, en dat doen we in kolom F. Tel je de getallen in kolom F op en krijg je een strikt positief getal, dan is het resultaat van de trekking niet wat we wensen. Geen nood: druk F9 om te herberekenen en je krijgt een nieuwe verdeling !
Middels voorwaardelijke opmaak gaan we in het rood aanduiden wanneer er een probleem is met de uitkomst van de trekking:
Indien gewenst kan je ook de kolommen van plaats wisselen en Gever en Ontvanger naast mekaar zetten, dat maakt niet veel uit. Of je verbergt de kolommen van de willekeurige getallen en de sortering. Download zeker het bestand bovenaan deze pagina en laat mij gerust weten wat je ervan vindt !
