Wereldkampioenschap voetbal in Excel: filteren met slicers
- 05/07/2014
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Voor alle voetbalfans/-liefhebbers… het Wereldkampioenschap voetbal is ondertussen begonnen! Maar dat wisten jullie al. En ook ik heb een template gemaakt om de wedstrijden op het WK te volgen. Daarin zal ik niet de enige zijn. Ik wilde al langer een artikel schrijven rond het gebruik van slicers in Excel, en daarom dacht ik: waarom zou ik de 2 onderwerpen niet combineren? Verderop in dit artikel vinden jullie een simpel template en uitleg rond het gebruik van slicers. Microsoft introduceerde de slicer objecten in Excel 2010 voor draaitabellen. Sinds Excel 2013 kunnen we slicers ook gebruiken bij (opgemaakte) gegevenstabellen. Vandaar dat je over Excel 2013 beschikken om het bestand uit de Downloads sectie te kunnen gebruiken. Kijk hier bij Excel MVP Jan Karel Pieterse als je wil lezen over slicers in Excel.
Onderstaand zie je een schermafdruk van het template. Het thema is roze/rood, maar ik heb er niets op tegen als je dit wijzigt in het thema dat je zelf verkiest. Niet dat ik roze en rood zo graag heb, maar ik vond het wel goed uitkomen. Links zie je de gefilterde gegevenstabel met 7 kolommen/velden. Twee velden werden met opzet verborgen, gebruikers hoeven niet alle velden te zien om de toepassing correct te gebruiken.
-
De kolommen zijn als volgt:
- Tijdstip: waarop de wedstrijd start
- Datum (verborgen): de datum zonder het uur, gebaseerd op kolom "Tijdstip". Een slicer werd gecreëerd voor deze kolom.
- Groep (verborgen): een mix van letters voor de groepsfase (A tot en met H), en afkortingen voor de knock-out fase.
- Match: uiteraard.
- Score: nog meer uiteraard.
- Locatie: waar wordt de match gespeeld?
- Fase: een verbeterde versie van de kolom "Groep". Cfr. supra voor details.
Verschillende tijdszones
In de Engelse versie van dit artikel kunnen gebruikers een tijdszone instellen. Wellicht is dit voor mijn Nederlandstalige website bezoekers minder van belang (eventueel Zuid-Afrikaanse lezers van dit artikel :-) ) Indien geïnteresseerd, bekijk zeker hoe ik de verschuiving van de tijdszones gedaan heb.
Slicer objecten
-
On the screenshot above, you can see the 3 slicer objects:
- Stage
- Location
- Date
Zoals je je wel kan inbeelden laten slicer objecten je toe om records in de gegevenstabel te filteren. Het is zo simpel als dat ik het nu zo neerschrijf, maar het biedt de gebruiker wel een groot voordeel ten opzichte van andere bestaande Excel functionaliteiten zoals daar zijn Autofilter en Uitgebreide filter. Doorgaans zijn mensen zeer visueel ingesteld. Als je boxen/rechthoeken/objecten kan aanbieden aan de gebruiker waar men enkel hoeft op te klikken, en men ziet onmiddellijk het resultaat van de filteractie, dan wordt men al snel enthousiast! Onmiddellijk na de filteractie of een wijziging in de filters of zelfs het uitzetten van een filter, zal Excel show de correcte rijen in de gegevenstabel tonen, gebaseerd op (in dit geval) alle 3 slicers. Filteren op (enkel) groep H toont ons dat 4 teams in 6 verschillende locaties spelen, op 4 verschillende datums. Zie naar de bovenstaande slicers of in het Excel voorbeeldbestand. De filteractie op group H maakt een heel aantal andere filterkeuzes onmogelijk: 7 locaties in Brazilië worden niet aangedaan door teams in Groep H, en groot aantal datums zijn ook niet mogelijk meer / worden niet geassocieerd met Groep H wedstrijden. Als we bijjkomend ook filteren op zeg maar 17 juni, betekent het dat maar 2 wedstrijden in Groep H overblijven:
- België - Algerije
- Rusland - Zuid-Korea
Enkel Belo Horizonte en Cuiaba blijven over als Locaties. En zo voort, en zo voort, je begrijpt het plaatje:-) Merk op dat je meerdere slicer items kan selecteren door de muis te gebruiken en te slepen, en met de Ctrl toets ingedrukt kan je meerdere niet-aangrenzende selecties maken in de slicer items! Een filter ongedaan maken kan middels de "trechter met het rode kruisje" in de rechterbovenhoek van de slicer. Slicer items zonder data worden grijs weergegeven,en kan je verbergen. Bijvoorbeeld, ik verberg de slicer items zonder gegevens voor de slicer "Datum".
Een (nieuwe) slicer invoegen doe je door een cel in de gegevenstabel te selecteren en dan bij Invoegen > Slicer te kiezen. Als je vindt dat de kolom "Score" een goede kandidaat zou zijn voor een slicer:
Eigenschappen van een slicer object
Wanneer de slicer getoond wordt in het werkblad, gebruik dan de "Slicer Tools" om de slicer en/of zijn eigenschappen, aan te passen. Een rechtermuisklik op de slicer brengt interessanteproperties eigenschappen aan het licht. Slicer instellingen (middels rechtermuisklik op de slicer) brengt dit dialoogvenster:
- De hoofding tonen blijkt optioneel te zijn.
- Je kan de slicer items op een aantal manieren sorteren, zie verderop.
- Je kan items zonder data verbergen. Dat is wat ik gedaan heb voor de Datum slicer maar niet voor Fase en Locatie.
- Als je slicer items zonder data niet verbergt, zijn er nog een aantal eigenschappen die je kan bepalen als je wil. Specifiek, slicer items zonder data grijs tonen, en slicer items zonder data naar onder schuiven en als laatste tonen.
De tweede plaats waar je een aantal opties voor slicers kan instellen, is deze (opnieuw, door een rechtermuisklik op het slicer object, of via het Excel lint bovenaan):
Het spreekt allemaal voor zich eigenlijk. De waarde 2 naast de cursor is het aantal kolommen: dit is vaak groter dan 1 om plaats te besparen en het filteren (nog) sneller te laten verlopen.
Slicer items sorteren
Zoals te zien is hierboven, kunnen slicer items gesorteerd worden in alfabetische volgorde (oplopend en aflopend). Dit is wenselijk voor Locaties en Datums in ons geval. Voor de Fase slicer items echter, is dit niet echt wenselijk. Alfabetisch sorteren heeft hier niet het beste effect. Daarom kan je ook sorteren met een Aangepaste lijst. Voor (Engelstalige) informatie over Aangepaste lijsten, zie hier bij nog een andere Excel MVP, Debra Dalgleish. Daar lees je dat je de unieke items voor jouw slicer in een leeg werkblad moet zetten (Gr. A, …, Gr. H, Achtste finale, Kwartfinals, …, Finale), gesorteerd in de gewenste volgorde en dan kan je bij de Excel Opties de Aangepaste lijst importeren. Daarna zet je bij de slicer eigenschappen dat je wil sorteren met een Aangepaste lijst en je bent klaar! Eerst krijg je de groepsfase (alfabetisch gesorteerd A tot en met H), en dan de knock-out fases in volgorde (neit noodzakelijk alfabetisch)! Heel handig als je het mij vraagt! Ter info: velden in draaitabellen kan je overigens ook sorteren per Aangepaste lijst.
Slicers verbergen
Als je een slicer wenst te verbergen, ga je eerst naar de "HOME" tab in het lint, groep "Editing" en klik je op het ook voor het desbetreffende object:
Een zoekfunctie voor fases
Volledigheidshalve, kijk eens naar de formule van de kolom "Fase" in de gegevenstabel. Het is een interessant voorbeeld van de ZOEKEN functie met arrays in Excel:
=ZOEKEN(
SPATIES.WISSEN(LINKS([@Groep];4));
{"1/8F";"3P";"A";"B";"C";"D";"E";"F";"FIN";"G";"H";"HF";"KF"};
{"Achtste finale";"Derde plaats";"Gr. A";"Gr. B";"Gr. C";"Gr. D";"Gr. E";"Gr. F";"Finale";"Gr. G";"Gr. H";"Halve finale";"Kwartfinale"})
)
Alternatief, met 1 array en daarin "koppeltjes van elementen":
=ZOEKEN(
SPATIES.WISSEN(LINKS([@Groep];4));
{"1/8F"\"Achtste finale";"3P"\"Derde plaats";"A"\"Gr. A";"B"\"Gr. B";"C"\"Gr. C";"D"\"Gr. D";"E"\"Gr. E";"F"\"Gr. F";"FIN"\"Finale";
"G"\"Gr. G";"H"\"Gr. H";"HF"\"halve finale";"KF"\"Kwartinale"})
)
Deze ZOEKEN functies maken veel (geneste) ALS formules overbodig. Zie hier. Zorg ervoor dat je de zoekwaarden alfabetisch oplopend sorteert: ofwel sorteer je ze in de eerste array als je met 2 arrays werkt, ofwel sorteer je telkens het eerste item in elk "koppeltje" in de oplossing met 1 array.
Een bijkomend voordeel van slicers, zeker tijdens het opzetten van de spreadsheet, is dat ze kunnen helpen bij het spotten van (type)fouten. Bijvoorbeeld, als je een fout maakt in het typen van een locatie van een Braziliaanse locatie, zal je ze beide zien opduiken in de slicer:
Als je vervolgens Fortalezza met dubbele "z" in de Locatie slicer selecteert, krijg je onmiddellijk 1 of meer record(s) waar je de typefout gemaakt hebt, en kan je dus heel snel en gemakkelijk corrigeren.
Oefening voor de lezer
-
Als je je Excel kennis en kunde wil vergroten bij het werken met slicer objecten, dan is er hier een oefening.
- Voeg een kolom toe aan de gegevenstabel om te bepalen of een wedstrijd al afgelopen / nog niet gestart / bezig is. Bijvoorbeeld gebaseerd op de vergelijking van NU() en [@[Tijdstip]] + 1.75 / 24 (dit laatste is de start van de wedstrijd plus 1 uur en 45 minuten)
- Creaeuml;er een slicer om filter op wedstrijden die bezig, nog niet gestart, afgelopen zijn.
Klaar!
Geniet van het tornooi en tot later!
