Tagarchief: voorwaardelijke opmaak

Heatmap en voorwaardelijke opmaak

Excel, ,
Voorbeeldbestand: VoorwOpmaak2.xlsxDownload

Ik was deze week op Strava een zogenaamde heatmap aan het bekijken; in dit geval een overzicht van de meest (en minst) gefietste trajecten van West-Europa.
Wat direct opvalt is dat er vooral in de buurt van de grote steden (en in Zuid-Limburg) veel gefietst wordt, maar zeker ook dat Nederland en België er flink uitspringen.
Hoe zou dat komen? Wordt er in Noord-Frankrijk en in Duitsland echt minder gefietst? Of leggen de fietsers daar hun tochten niet vast in Strava? Of …

Misschien is dat ook de reden waarom je heatmaps in de praktijk niet zo veel tegenkomt: ze roepen soms meer vragen op dan ze antwoorden geven.

Maar bij mij kwam wel direct de vraag op: hoe zou je een heatmap in Excel maken? Och ja, natuurlijk: met voorwaardelijke opmaak. Daarom in dit artikel maar eens de schijnwerper op diverse mogelijkheden van de, in Excel ingebouwde, opties.

Voorwaardelijke opmaak

Deze optie vinden we in Excel binnen de menutab Start in het blok Stijlen. Wanneer u deze optie kiest opent zich een vervolgscherm met diverse mogelijkheden. Een groot gedeelte daarvan zal hieronder de revue passeren.

Markeringsregels

Kiest u voor Markeringsregels dan ziet u diverse opties: de opmaak regelen van cellen die een waarde bevatten groter dan een bepaalde target, of juist kleiner of die tussen 2 targets in liggen etc.

In het tabblad Markering van het Voorbeeldbestand staat een opbrengst-overzicht per dag. In kolom C wordt de dagopbrengst bepaald met de formule =ASELECTTUSSEN(0;10)
oftewel een willekeurig geheel getal tussen 0 en 10 (allebei de uitersten doen mee).

Om alle cellen, die een waarde groter dan 7 bevatten, te accentueren doorlopen we de volgende stappen:

  1. selecteer alle cellen, die een opmaak moeten krijgen; in het voorbeeld de cellen C3:C33
  2. kies Voorwaardelijke opmaak/Markeringsregels/Groter dan
  3. vul in het vervolgscherm de targetwaarde in (hier 7) en kies als opmaak Groene opvulling met donkergroene tekst
  4. klik dan op OK

Om de cellen, waarvan de waarde kleiner is dan 3, te benadrukken doen we iets vergelijkbaars, alleen kiezen we in stap 2 de optie Kleiner dan.

NB door op F9 te drukken, wordt de Excel-map opnieuw berekend. Alle waarden in kolom C worden dan ververst en de opmaak zal zich daaraan aanpassen.

Voorwaardelijke opmaak aanpassen

Soms voldoet de op deze manier standaard ingestelde opmaak niet (helemaal). Volg dan de volgende stappen:

  1. selecteer één van de cellen, waarvan de opmaak gewijzigd moet worden, in dit geval bijvoorbeeld C3
  2. kies Voorwaardelijke opmaak en dan de optie Regels beheren…
  3. bovenstaand scherm komt dan tevoorschijn. Standaard worden alleen de regels van de huidige selectie getoond. Dit kunt u makkelijk wijzigen in het eerste keuzevak.
  4. klik op de regel die u wilt wijzigen (of bekijken) en kies Regel bewerken
  5. afhankelijk van het soort regel kunnen nu diverse parameters ingesteld worden, inclusief de gewenste opmaak

Bij iedere regel is ook te zien voor welke cellen de regel geldt; in dit geval allebei de keren voor C3:C33.

NB Dit bereik kan hier aangepast worden. Uitermate handig als u in de eerste stap bij het instellen van opmaak vergeten bent een reeks cellen te selecteren.

Ook als er nieuwe waarden aan kolom B en C worden toegevoegd zal het bereik voor de opmaak-regels moeten worden aangepast.

Opmaak in een Excel-tabel

In de kolommen E en F van het tabblad Markering in het Voorbeeldbestand staat eenzelfde soort overzicht, maar dan als Excel-tabel (zie onder andere het artikel Kunst en Excel, ofwel de 10 voordelen van het gebruik van tabellen).
Dit heeft de volgende voordelen:

  1. selecteert u één van de cellen van de tabel en u kiest dan Regels beheren, dan zal Excel direct alle opmaakregels van die tabel tonen
  2. voegt u nieuwe gegevens onder aan de tabel toe, dan zal Excel automatisch ook de voorwaardelijke opmaak aan die cellen toevoegen

In kolom E wordt via Voorwaardelijke opmaak/Markering/Datum de datum van vandaag met een gele achtergrondkleur weergegeven.

Kolom F kent dezelfde opmaak als in het vorige overzicht.

Opmaak ten opzichte van het gemiddelde

In het tabblad Markering in het Voorbeeldbestand bevatten de kolommen H en I ook een tabel (met de naam tblOpbr2).
In cel K3 (met de naam GemOpbr) wordt de gemiddelde opbrengst bepaald.

Kolom I heeft op de volgende manier een voorwaardelijke opmaak gekregen:

  1. selecteer de cellen I3:I33
  2. kies Voorwaardelijke opmaak/Markering/Groter dan
  3. als target geven we nu niet een harde grens op, maar selecteren met de muis cel K3 of drukken op de functietoets F3 en kiezen dan de naam GemOpbr (de laatste optie is het mooiste, omdat dan duidelijker te zien is wat deze opmaakregel doet)
  4. kies de gewenste opmaak
  5. klik OK
  6. herhaal het vorige maar nu met de optie Kleiner dan

Opmaak voor unieke of dubbele waarden

Voorwaardelijke opmaak kan ook eenvoudig gebruikt worden om unieke of dubbele waarden te markeren (zie het tabblad UniekDubbel in het Voorbeeldbestand):

  1. selecteer de cellen, die een opmaak moeten krijgen (in dit geval B3:B9)
  2. kies Voorwaardelijke opmaak/Markering/Dubbele waarden
  3. kies de optie Dubbele of Unieke
  4. geef de gewenste opmaak op en klik op OK

Opmaak voor bovenste/onderste

Soms wilt u zien welke cellen de grootste of kleinste waarden bevatten. Dat gaat als volgt (zie het tabblad BovenOnder in het Voorbeeldbestand):

  1. selecteer de cellen, die een opmaak moeten krijgen
  2. kies Voorwaardelijke opmaak, dan de optie Bovenste/Onderste en vervolgens de optie Bovenste 10 items
  3. dan geeft u aan of u echt 10 items wilt opmaken; u kunt bijvoorbeeld ook voor de top-3 kiezen
  4. kies de gewenste opmaak en klik op OK

NB1 u ziet ook de optie Bovenste 10%; de 10 is weer apart in te stellen. Excel zal dan zelf, aan de hand van het geselecteerde bereik, bepalen hoeveel cellen er ‘gekleurd’ moeten worden.

NB2 uiteraard kunt u ook de cellen met de laagste waarden accentueren (Onderste in plaats van Bovenste).

Opmaak ten opzichte van het gemiddelde 2

Hiervoor hebben we via Markering een methode laten zien om cellen boven of onder het gemiddelde op te maken. Excel kent standaard een alternatief (zie tabblad BovenOnder van het Voorbeeldbestand, kolommen E en F):

  1. selecteer de cellen, die een opmaak moeten krijgen
  2. kies Voorwaardelijke opmaak, dan de optie Bovenste/Onderste en vervolgens de optie Boven gemiddelde
  3. geef een opmaak op en klik op OK

NB nadeel van deze methode is, dat u nergens zelf het gemiddelde ziet.

Bovenste/onderste interactief

Soms hebben we inzicht nodig in de hoogste 5, een andere keer willen we de laagste 5 (of een ander aantal) zien (zie tabblad BovenOnder van het Voorbeeldbestand, kolommen H en I). Hier voorziet Excel niet standaard in.

  1. leg ergens vast hoeveel cellen er ‘gekleurd’ moeten worden; in het voorbeeld gebruiken we cel L2 daarvoor met de naam BovOnderAantal
  2. plaats in een cel de tekst Boven of Onder; in het voorbeeld cel L3 met de naam BovOnder. Via gegevensvalidatie kan uit die 2 woorden worden gekozen en is de invoer beperkt. 
  3. selecteer de cellen, die een opmaak moeten krijgen (beginnend bij I3)
  4. kies Voorwaardelijke opmaak, dan de optie Nieuwe regel en daarna de optie Een formule gebruiken
  5. plaats dan de volgende formule =EN(BovOnder=”Boven”; I3>=GROOTSTE($I$3:$I$33;BovOnderAantal))
  6. geef een gewenste opmaak en klik OK

De formule levert alleen als waarde WAAR (en dan zal de opmaak worden doorgevoerd) als aan allebei de voorwaarden wordt voldaan: cel L2 moet de waarde Boven bevatten EN de waarde in cel I3 moet groter of gelijk zijn aan de grootste waarde in het bereik van geselecteerde cellen (als BovOnderAantal gelijk is aan 1, aan de één na grootste als BovOnderAantal gelijk is aan 2 etcetera).

LET OP bij het maken van een formule voor voorwaardelijke opmaak met relatieve verwijzingen.
In dit geval bevat de formule I3, omdat dat het startpunt van onze selectie was.
Selecteer cel I4 en bekijk nu de regel die bij de voorwaardelijke opmaak hoort; Excel heeft de relatieve verwijzing in de formule automatisch aangepast.

NB de andere gebruikte formule (in het geval dat we de laagste waarden zoeken):
=EN(BovOnder=”Onder”;I3<=KLEINSTE($I$3:$I$33;BovOnderAantal))

Gegevensbalken

Sommige mensen willen getallen zien in overzichten, anderen zijn meer grafisch ingesteld.

Gegevensbalken als voorwaardelijke opmaak zijn in dit geval een uitkomst. Hierboven (zie het tabblad GegBalk in het Voorbeeldbestand) zijn per kolom de waarden ‘vertaald’ naar kleine grafiekjes:

  1. selecteer de cellen van de Uit-kolom. Wijs daartoe met de muis de bovenrand van de Uit-cel aan; de cursor wordt dan een pijl-naar-beneden. Klik dan met de muis.
  2. kies Voorwaardelijke opmaak en dan de optie Gegevensbalken. In dit geval kiezen we de derde kleurovergang (Rood)
  3. om de kleuren-balk en de getallen te scheiden: kies opnieuw Voorwaardelijke opmaak, maar dan de optie Regels beheren en Regel bewerken. Klik op de knop Negatieve waarden en as en zorg dat bij Asinstellingen de optie Middelpunt is geselecteerd.
  4. voor de In-kolom geldt een vergelijkbare procedure
  5. de Result– en ResCum-kolom zijn iets ingewikkelder: in eerste instantie kiezen we voor een Groene gegevensbalk. Daarna moet er nog een aanpassing doorgevoerd worden: kies bij Regel bewerken/Negatieve waarden en as een Rode opvulkleur (ook voor de rand van de balk). Zet meteen ook Middelpunt aan.

Gegevensbalken 2

Bijna hetzelfde overzicht als hiervoor (wel heb ik de kolom met cumulatieve resultaten weggelaten; zie het tabblad GegBalk in het Voorbeeldbestand, bereik B17:E29).
Het verschil zit hem er in dat de grootte van de gegevensbalken nu relatief ten opzichte van de totale tabel bepaald zijn en niet per kolom.

LET OP zoals uit de diverse voorbeelden mag blijken, moeten de overzichten wel altijd van een goede legenda worden voorzien anders kan de lezer snel de verkeerde conclusies trekken.

Gegevensbalken 3

Wanneer de exacte details van de in- en uitgaande stromen niet van belang zijn kunt u er ook voor kiezen om alleen de gegevensbalken te laten zien (zie het tabblad GegBalk in het Voorbeeldbestand, bereik H2:K14):

  1. selecteer één van de cellen waarvan de opmaak moet worden gewijzigd
  2. kies Voorwaardelijke opmaak/Regels beheren
  3. selecteer de betreffende regel en klik op Regel bewerken
  4. plaats een vinkje voor de optie Alleen balk weergeven

Gegevensbalken 4

Gegevensbalken zijn ook toepasbaar in draaitabellen (zie het tabblad GegBalkDraai in het Voorbeeldbestand).

LET OP wanneer er aan de brongegevens regels worden toegevoegd dan dient de draaitabel te worden vernieuwd. Komt er in de draaitabel dan ook een nieuwe regel bij, dan zal de opmaak niet automatisch worden doorgevoerd. Dit kunt u als volgt oplossen:

  1. selecteer in de draaitabel één van de cellen waarvan de opmaak moet worden gewijzigd
  2. kies Voorwaardelijke opmaak/Regels beheren
  3. selecteer de betreffende regel en klik op Regel bewerken
  4. kies één van de 2 opties, die beginnen met Alle cellen

Heatmap

Dan nu nog even over Heatmaps (de aanleiding voor dit artikel).

Hiernaast worden de grootste getallen groen gekleurd en de kleinste rood (zie het tabblad Kleuren in het Voorbeeldbestand).
De getallen niet in de buurt van de extremen krijgen een tussenschakering.

We gaan als volgt te werk:

  1. selecteer alle cellen, die bij de heatmap betrokken moeten worden
  2. kies Voorwaardelijke opmaak en dan de optie Kleurenschalen. In dit geval kiezen we de eerste kleurovergang (groen-geel-rood)

Maar we kunnen de standaardinstellingen van Excel nog aanpassen.
Wanneer we alles onder de 30 te weinig vinden en alles boven 90 prima, dan passen we de regel als volgt aan (zie het tabblad Kleuren in het Voorbeeldbestand, bereik I3:L14):

Kies Voorwaardelijke opmaak/Regels beheren, selecteer de betreffende regel en klik op Regel bewerken. Stel de opties in zoals hieronder weergegeven.

Nog meer opmaak

Voor diegene die de smaak te pakken hebben gekregen: het Voorbeeldbestand bevat nog diverse andere voorbeelden van opmaak. Met het bovenstaande in het achterhoofd moeten de daarbij gemaakte keuzes duidelijk zijn.

Olympische spelen

Excel, ,
Voorbeeldbestand: Olympics-2018-vs2.xlsxDownload

De Giro voorbij, de Tour moet nog komen, Nederland niet op het WK: dan maar nog even dromen over de afgelopen winterspelen.

Een poosje geleden kwam ik een database tegen met daarin alle medaillewinnaars van de (moderne) Olympische spelen.

Een mooie aanleiding om eens te kijken of we wat leuke overzichten kunnen maken (met behulp van draaitabellen natuurlijk).

Basis-materiaal

De gegevens over alle Olympische Spelen zijn verzameld door Shane Devenshire. Die heeft in zijn sheet ook enkele voorbeelden opgenomen.

“The latest version of my free Excel Olympic Database is complete and may be downloaded by using the following link:
http://bit.ly/2BWvAte
Please share the link not the file because I want to know how often this file is downloaded so I can decide if I should continue updating it in the future. The 2016 version had very few downloads so I am thinking of discontinuing the project in the future.”

Ik heb een nieuw, aangepast Voorbeeldbestand gemaakt, maar vergeet niet óók op bovenstaande link te klikken.

Shane gebruikt op zijn welkom-pagina  een onderdeel van Excel, dat ik nog niet vaak ben tegengekomen: SmartArt-afbeeldingen.

Volgens Microsoft: “Een SmartArt-graphic is een visuele weergave van uw gegevens en ideeën. U maakt er een door een indeling te kiezen die past bij uw bericht. In sommige indelingen (zoals organigrammen en Venn-diagrammen) worden bepaalde soorten gegevens weergegeven, terwijl andere indelingen alleen een uitbreiding zijn van de weergave van een lijst met opsommingstekens.
Als u SmartArt-graphics combineert met andere functies, zoals thema’s, kunt u hiermee professionele illustraties maken met slechts een paar muisklikken.”

SmartArt is te vinden via de menutab Invoegen in het blok Illustraties.

Wanneer u in het tabblad Welkom van het Voorbeeldbestand op één van de ringen klikt, opent vanzelf het hulp-menu van SmartArt.

De gegevens zijn allemaal verzameld in het tabblad OlympData van het Voorbeeldbestand.

De betekenis van de meeste gegevens wijst zichzelf.
Alleen de kolom MedAantal (het aantal medailles) behoeft toelichting. Bij een individuele wedstrijd is dit aantal uiteraard gelijk aan 1. Bij een team-prestatie krijgt iedere deelnemer een “evenredig deel van de medaille”.
Dit betekent dat als je het aantal voorkomens in deze kolom telt, dat je dan het aantal uitgedeelde medailles krijgt. Neem je de som dan wordt een team-prestatie slechts als 1 medaille geteld (wat de bedoeling is als je bijvoorbeeld een landenklassement wilt maken).

De kolom Medaille heeft een Voorwaardelijke opmaak gekregen.

In het tabblad Locatie staat een overzicht van alle landen en plaatsen waar Olympische Spelen zijn gehouden.

Overzichten

In het Voorbeeldbestand zijn diverse overzichten opgenomen; de tabbladen Ovz:

  1. het eerste is een landenoverzicht. In cel C2 kan het gewenste jaar worden geselecteerd.
    In het Waarden-gebied van de draaitabel is de Som van het veld MedAantal weergegeven.
    De landen zijn gesorteerd naar aflopend totaal aantal medailles; klik rechts op een land, kies de optie Sorteren en dan Meer sorteeropties.
    NB1 de Medaille-velden heb ik dezelfde voorwaardelijke opmaak gegeven als de kolom in de bron-gegevens. Het woord Medaille is overschreven door MedSrt.
    NB2 gediskwalificeerde winnaars hebben wel een MedSrt, maar geen waarde voor MedAantal. In de draaitabel staat dus ook geen waarde in die kolom.
    Wilt u weten welke deelnemers gediskwalificeerd zijn: dubbel-klik dan op het eindtotaal van de betreffende kolom.
    In hetzelfde tabblad Ovz1 staat ook een landenoverzicht, maar daar zijn de diskwalificaties weggelaten door een filtering op MedSrt (klik op het vinkje achter MedSrt; hebt u een filtering aangebracht dan krijgt dit de vorm van een trechter).
  2. in Ovz2 is het landenoverzicht verder uitgesplitst naar Sport en Event.
  3. Ovz3 geeft de verdeling van het aantal medailles naar geslacht; uitgesplitst per sport.
    Duidelijk is te zien dat bobsleeën nog een mannensport is.
  4. ook zo benieuwd bij welke sport de meeste diskwalificaties plaats vinden en bij welk land? Kijk dan in Ovz4.
    LET OP als nu in het waarden-gebied het aantal van het veld MedAantal wordt bepaald, dan zal Excel niets laten zien omdat dat veld in deze situatie niet is gevuld. Kies dus voor het bepalen van het aantal voorkomens een ander (wel gevuld) veld.
  5. geïnteresseerd in de resultaten van Nederland op de winterspelen in de loop van de tijd? Kijk in Ovz5.
    NB wilt u weten welke atleten de medailles hebben binnengesleept: dubbel-klik op het betreffende aantal.
  6. in Ovz6 ziet u een verdeling naar leeftijd van de winnaars.
    De leeftijden zijn in categorieën ingedeeld.
    Hoe gaat dat in zijn werk?
    * voeg de leeftijd als Rijlabel toe aan de draaitabel
    * klik rechts op een willekeurige leeftijd en kies de optie Groeperen
    * u krijgt dan de mogelijkheid om aan te geven hoe groot het interval moet zijn
    * helaas: aangezien in de kolom Lft ook de tekst NB voorkomt, kan Excel deze groepering niet uitvoeren dus komt er wat ‘handwerk’ aan te pas: selecteer de cellen met de leeftijden 10 t/m 15 en klik rechts op één van die waardes, kies dan de optie Groeperen, wijzig de naam Groep1 in een meer relevante omschrijving (hier 10-15).
    NB1 het Waarden-gebied van de draaitabel heeft een voorwaardelijke opmaak gekregen, waarbij gebruik is gemaakt van de optie Gegevensbalken.
    NB2 er zijn meerdere jaren geselecteerd (2000 t/m 2018); Excel laat dan alleen de tekst “(Meerdere items)” zien. Het verdient aanbeveling om zelf in de cel daarnaast aan te geven welke selectie op dat moment zichtbaar is (in dit geval: resultaten vanaf 2000).

 

Unieke waarden

Excel, , , , , , , ,
Voorbeeldbestand: Uniek.xlsxDownload

Er bestaan nogal wat situaties, waarbij je wilt weten of items allemaal verschillend zijn of niet.
En, als ze niet allemaal verschillend zijn, welke unieke exemplaren komen er dan voor?

In Excel kennen we een dergelijk probleem ook: welke unieke waarden komen er in een rij of kolom voor?
 

Deze keer zullen we enkele methoden de revue laten passeren; van simpele tot complexe, van opmaak tot selectie.

Voorwaardelijke opmaak

In het Voorbeeldbestand heb ik op het tabblad Data1 een tabel opgenomen met in de kolommen oa de afdelingen en het soort bedrag.

Alle cellen in die kolommen hebben een voorwaardelijke opmaak gekregen:

  1. selecteer cel C3
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. klik op de optie Nieuwe regel
  4. in het vervolgscherm kiezen we als Type: Een formule gebruiken
  5. en de formule wordt:
    =AANTAL.ALS(C$3:C3;C3)=1
    ofwel: als in het bereik C$3:C3 de waarde uit cel C3 1 keer voor komt mag de cel een opmaak krijgen.
    Dat is natuurlijk altijd waar!
    Maar wat gebeurt er als we deze voorwaardelijke opmaak ook ‘loslaten’ op een cel lager? Excel interpreteert de formule dan als =AANTAL.ALS(C$3:C4;C4)=1 en kijkt of de waarde uit C4 slechts 1 keer voor komt in het bereik C$3:C4.
    Ook wanneer deze formule in kolom D wordt toegepast is de controle precies wat we zoeken.
    NB om de formule ook in andere kolommen te kunnen gebruiken is alleen de 3 absoluut gemaakt als begin van het bereik (vandaar het $-teken vóór de 3) en is niet het meer voor de hand liggende $C$3 gebruikt.
  6. voeg nog de gewenste opmaak toe (hier is voor een licht-groene opvulling van de cel gekozen)
  7. klik op OK
  8. zorg dat de opmaak op alle relevante cellen van kolom C en D wordt toegepast:
    * kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
    *     klik op de optie Regels beheren
    * vul bij Van Toepassing op het gewenste bereik in (in dit geval $C$3:$D$32)

NB1 het overzicht is een Excel-tabel. Daarom zal Excel, wanneer er een regel aan wordt toegevoegd, ook de Voorwaardelijke opmaak direct meenemen; het bereik wordt automatisch aangepast.

NB2 welke unieke waarden er in een tabel-kolom zitten ziet u ook direct door op het ‘vinkje’ achter een kolomkop te klikken.

NB3 het vorige ‘recept’ kan met 1 stap ingekort worden: selecteer in de eerste stap niet één cel, maar het totale bereik waar de opmaak voor moet gelden; stap 8 kan dan vervallen.

Via draaitabel

De vorige oplossing was een optische methode om unieke waarden te vinden; bij een grote tabel niet echt handig.

Het maken van een draaitabel is echter een simpele en doeltreffende manier om snel de unieke waarden in een kolom te vinden:

  1. selecteer een cel in de tabel met gegevens, bijvoorbeeld B2
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm is alles al goed ingevuld; de bron voor de draaitabel is de totale Excel-tabel (met de naam tblData1)
  4. klik OK
  5. sleep het veld Afd naar de Rijlabels en daar zijn alle unieke afdelingsnamen!
  6. hetzelfde kunt ook doen voor de Soort, maar u kunt beter de vorige draaitabel uitbreiden: sleep Soort naar Kolomlabels.
    Sleep ook nog Bedrag naar het Waarde-gebied en u hebt meteen een overzicht van de verdeling van de bedragen naar afdeling en soort!

    Zie ook het tabblad Data1 in het Voorbeeldbestand.

Even een uitstapje: wilt u weten wat het totaal van Srt1 is dan kunt u ook de volgende formule gebruiken: =SOM.ALS(tblData1[Soort];”Srt1″;tblData1[Bedrag]) maar beter is het volgende:

  1. plaats in een bepaalde cel de waarde Srt1, bijvoorbeeld in G14 (zie het tabblad Data1 in het Voorbeeldbestand)
  2. in de cel daarnaast gaan we een formule opbouwen:
    tik in =som.als(
  3. wijs met de cursor de bovenkant van de kolom Afd aan (de cursor wordt dan een zwarte pijl naar beneden) en klik.
    De formule wordt dan aangevuld met tblData1[Soort] ofwel alle cellen in de kolom Soort van de Excel-tabel tblData1.
  4. tik in ; (de punt-komma) en klik op cel G14 en plaats weer een ;
  5. als laatste voegen we de kolom Bedrag toe, die onder voorwaarden gesommeerd moet worden; dat gaat op dezelfde manier als in stap 3.
  6. nog een haakje sluiten en Enter

Door in G14 een andere soort in te typen, krijgt u daarvan het totaal.

Maar wat als er aan 2 (of meer voorwaarden moet worden voldaan?

U moet dan de functie SOMMEN.ALS gebruiken.
Deze werkt net iets anders: eerst geeft u het bereik op, waarvan u de som wilt bepalen, dan het eerste criterium-bereik met daarachter het criterium zelf, daarna een nieuw criterium-bereik met criterium etc.

Gegevensvalidatie

Maar in G14 kunnen nu willekeurige teksten worden ingevoerd. Dat is natuurlijk niet de bedoeling; we willen alleen bestaande soorten kunnen opgeven.

Dit gaat eenvoudig met gegevens-validatie:

  1. plaats de cursor in de cel die u wilt valideren (bijvoorbeeld cel G19 zoals in het tabblad Data1)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. kies bij Toestaan: de optie Lijst
  4. en bij Bron: kiest u de kolom Soort uit de Excel-tabel op de manier zoals hierboven aangegeven.
    NB Excel vertaalt de kolom direct naar daadwerkelijke cellen, maar als de tabel groter (of kleiner wordt) verandert dit bereik mee.
  5. klik op OK.

Oeps, het resultaat is niet helemaal wat we hoopten. In de keuzelijst staan alle voorkomens uit de kolom Soort, we willen natuurlijk alleen unieke waarden hebben!

Unieke waarden via filtering

Om een lijstje te maken van unieke waarden (maar u wilt geen draaitabel gebruiken) dan kent Excel nog een andere aanpak:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. in het Uitgebreid filter kiezen we als Actie de optie Kopiëren naar andere locatie
  3. voor het Lijstbereik selecteert u alle gegevens uit de kolom Afd van de tabel, INCLUSIEF de kop
  4. bij Kopiëren naar vult u de cel in, waar de unieke waarden moeten komen (in het voorbeeld heeft deze cel de naam AfdKop gekregen)
  5. vergeet niet het vinkje te plaatsen bij Unieke records!

In het tabblad Data1 van het Voorbeeldbestand ziet u het resultaat van deze actie.

Ook voor de kolom Soort zijn de unieke waarden opgehaald.

NB1 Wat direct opvalt is dat er geen (automatische) sortering plaats vindt. De volgorde is gelijk aan de volgorde in de bron.

NB2 als er regels aan de bron-tabel worden toegevoegd dan moeten de ophaal-acties opnieuw worden uitgevoerd.
Excel ‘onthoudt’ de gegevens van de laatste actie. Dit is ook te zien in het overzicht Namen beheren in de menutab Formules.

LET OP1 de locatie, waar de unieke waarden moeten komen, moet op hetzelfde tabblad staan als waar de brongegevens zich bevinden.

LET OP2 vergeet niet om bij het Uitgebreid filter ook de kop van de kolom mee te nemen, anders krijgt u de eerste waarde dubbel. Dit is nergens in de Microsoft-documentatie terug te vinden!

Gegevensvalidatie

Op basis van deze unieke waarden kunnen nu makkelijk Gegevensvalidaties worden gemaakt.
Maar om het geheel dynamisch te houden (als er nieuwe unieke waarden bijkomen moet dit automatisch worden meegenomen bij de gegevensvalidatie) wordt als bron-lijst niet  een hard bereik genomen (bijvoorbeeld R3:R5).

Nee, we creëren een dynamisch bereik door een naam toe te voegen:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  2. vul een naam in (bijvoorbeeld AfdUniek)
  3. en plaats de formule
    =VERSCHUIVING(AfdKop;1;0;AANTALARG(Data1!$R:$R)-1)
    in het vak Verwijst naar.

Bij gegevensvalidatie (zie cel G20 in het tabblad Data1 van het Voorbeeldbestand) gebruikt u als Bron de naam AfdUniek (vergeet niet het =-teken daar voor te zetten!).

In de cellen G21 en H21 is op een vergelijkbare manier gegevensvalidatie toegepast, maar daarbij zijn als bron voor de verschuiving 2 simpele draaitabellen gebruikt.

LET OP worden er regels aan de oorspronkelijke tabel toegevoegd, vergeet dan niet de draaitabellen te vernieuwen anders bevatten de gegevensvalidaties niet de meest recente gegevens.

 

Sudoku in Excel

Excel, VBA (Visual Basic for Applications), , , ,
Voorbeeldbestand: Sudoku.xlsmDownload

Ik kreeg onlangs de volgende vraag: kun je geen artikel schrijven waarin je uitlegt hoe je met behulp van Excel Sudoku-puzzels op kunt lossen.
Mijn antwoord bestond uit 2 woorden ‘Ja’ en ‘maar’.

De Ja, omdat met Excel ‘alles’ mogelijk is en de maar omdat het oplossen van zo’n puzzel meer een programmeer-uitdaging is en niet zozeer iets om de mogelijkheden van Excel voor de dagelijkse praktijk te laten zien.

Maar toen ik er wat langer over nadacht, zag ik wel wat mogelijkheden.
Dus nu toch een Sudoku in Excel, maar niet een machine die de oplossing genereert (wat zou je daar ook mee moeten), wel een systeem dat het zoeken van een oplossing ondersteunt.

Met behulp van Voorwaardelijke opmaak laat Excel zien op welke plaatsen een cijfer geplaatst kan worden. Dubbele invoer in een rij, kolom of blok wordt ook door Voorwaardelijk opmaak gesignaleerd. Bij deze opmaak wordt veelvuldig gebruik gemaakt van de Namen-optie van Excel; niet alleen de standaard-toepassing daarvan maar ook enkele meer ingewikkelde en dus interessante mogelijkheden!

Basis van het G-Info/Sudoku-systeem

Allereerst hebben we natuurlijk een blok van 9 bij 9 nodig, die dan verder is onderverdeeld in kleinere blokken van 3 bij 3 en in rijen en kolommen. Daar gebruiken we een eigenschap van cellen voor; we kunnen de (dikte van de) randen aanpassen (druk op Ctrl-1). Het bereik van de 81 cellen heeft de naam Sudoku gekregen.

Om straks te weten over welke kolom of rij we het hebben, zijn in regel 3 en in kolom B de cijfers 1 t/m 9 ingevuld. De kruising van die twee, dus cel B3, heeft de naam Ref gekregen. Straks moeten we ook weten wat de inhoud van een geselecteerde cel is; die informatie komt in cel O3 met de naam Selectie.
Regel 3 en de kolommen B en O zijn ‘onzichtbaar’ gemaakt . Dit is te zien aan de +-jes; door daar op te klikken, open en sluit je een bepaalde groep cellen (zie het tabblad Sudoku in het Voorbeeldbestand).

Om het geheel wat mooier te maken is het standaard-raster weggehaald (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rasterlijnen weergeven uit).
Alle regels en kolommen, die we niet nodig hebben, zijn verborgen (selecteer ze eerst en dan rechts klikken).
En als laatste zijn de klom- en rijnamen weggelaten (menutab Bestand, dan Opties en daarbinnen Geavanceerd; vink dan Rij- en kolomkoppen weergeven uit).

Voor een soepele werking van het spel zijn nog een paar knoppen toegevoegd die bepaalde VBA-routines opstarten; hier komen we later op terug.

Controle 1

Het eerste wat we willen weten als we een Sudoku invullen, is waar overal (als voorbeeld) een 1 staat of waar een 2 etc.
Dat kunnen we met Voorwaardelijke opmaak goed zichtbaar maken; de enige voorwaarde is dat we weten welk cijfer we willen laten oplichten. Daarvoor hebben we een klein VBA-programmaatje nodig:

De subroutine Worksheet_SelectionChange is gekoppeld aan het werkblad Sudoku. Als de selectie in dit werkblad wordt gewijzigd, wordt deze routine automatisch door Excel aangeroepen. Op deze manier bevat de cel Selectie (O3) altijd de laatst geselecteerde waarde uit het bereik Sudoku.

NB bekijk de VBA-routine in het Voorbeeldbestand door bijvoorbeeld met de muis rechts te klikken op de tab Sudoku en dan te kiezen Programmacode weergeven. Eventueel nog dubbelklikken op het blad Sudoku in de Projectverkenner.

Nu kunnen we de Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en Fselecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we de 2e optie (Alleen cellen opmaken met) en geven dan aan dat de opmaak moet worden doorgevoerd als de celwaarde gelijk is aan de inhoud van de cel Selectie (denk aan het =-teken vóór Selectie)
  4. nog even de gewenste Opmaak instellen en OK

Controle 2

De volgende controle zorgt er voor dat we weten of er geen dubbele cijfers in de kolommen, rijen of kleine blokken voorkomen.

Voordat we dit makkelijk en flexibel kunnen doen moeten we wat voorwerk verrichten.
Excel moet automatisch beoordelen bij welke rij, kolom en blok een bepaalde cel hoort. Dan heb ik gedaan door enkele nieuwe Namen in te voeren:

  1. plaats de cursor in de eerste cel van de Sudoku
  2. een nieuwe naam creëren: kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  3. in het vervolgscherm kiezen we als Naam een handige omschrijving, in dit geval RijSel en bij Verwijst naar: =Sudoku!$B5
    Denk aan het dollar-teken voor de kolom-letter.
    Afhankelijk waar de cursor staat (nu in regel 5) zal deze naam de waarde uit kolom B van die regel ophalen.
  4. op gelijkwaardige manier bestaat er ook de naam KolomSel met als formule =Sudoku!D$3
    Deze naam geeft dus altijd de waarde uit regel 3 van de kolom waar de cursor staat.
  5. om te bepalen in welk blok de cursor staat is wat ingewikkelder:
    =GEHEEL((Sudoku!RijSel-1)/3)*3+GEHEEL((Sudoku!KolomSel-1)/3)+1
    Hier is de naam BlokSel aan gekoppeld.
  6. nu moeten we nog de inhoud van zo’n rij, kolom en blok kunnen ophalen om de inhoud te kunnen testen. Daartoe zijn nog 3 extra namen gedefinieerd:
    RijTest met de formule =VERSCHUIVING(Sudoku!Ref;Sudoku!RijSel+1;2;1;9)
    KolomTest met =VERSCHUIVING(Sudoku!Ref;2;Sudoku!KolomSel+1;9;1) en
    BlokTest met =VERSCHUIVING(Sudoku!Ref;GEHEEL((Sudoku!BlokSel-1)/3)*3+2;REST((Sudoku!BlokSel-1);3)*3+2;3;3)

Nu kunnen we een nieuwe Voorwaardelijke opmaak toevoegen:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =OF(AANTAL.ALS(BlokTest;D5)>1;
    OF(AANTAL.ALS(RijTest;D5)>1;
    AANTAL.ALS(KolomTest;D5)>1))
    Aantal.Als kijkt hoe vaak in een bereik (de eerste parameter) de waarde van de tweede parameter voorkomt.
  4. nog even een Opmaak instellen en OK

NB1 wil je de gebruikte opmaak bekijken kies dan Regels beheren binnen de optie Voorwaardelijke opmaak

NB2 achteraf gezien was het waarschijnlijker makkelijker geweest om voor het testen de functie INDEX te gebruiken in plaats van VERSCHUIVING. Misschien een andere keer.

Controle 3

Als laatste controle voegen we nog wat opmaak toe, die aangeeft op welke plaatsen een bepaald geselecteerd cijfer nog kan worden ingevuld:

  1. plaats de cursor in de eerste cel van de Sudoku (D5 dus) en selecteer dan de 81 Sudoku-cellen
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak en dan Nieuwe regel …
  3. in het vervolgscherm kiezen we nu de laatste optie (Een formule gebruiken). De formule wordt
    =EN(D5<=0;
    EN(Selectie>0;
    EN(AANTAL.ALS(BlokTest;Selectie)=0;
    EN(AANTAL.ALS(RijTest;Selectie)=0;
    AANTAL.ALS(KolomTest;Selectie)=0))))
    Dus als de betreffende cel, waarvan de opmaak moet worden ingesteld, een waarde kleiner of gelijk aan nul heeft (dus er is nog geen cijfer ingevuld) EN via Selectie weten we welk cijfer we onder de loep nemen EN in het betreffende blok komt het cijfer nog niet voor EN ook niet in de rin EN niet in de kolom dan moet de opmaak toegepast worden.

Buttons/knoppen

Onder de Sudoku zijn een paar knoppen opgenomen, die het opzetten van een spel kunnen vergemakkelijken (zie het Voorbeeldbestand):

  1. Wissen: de beveiliging van het tabblad wordt verwijderd, het speelveld leeg gemaakt, lettertypes en eventuele kleuren worden gereset. Ook de Selectie en de Starttijd worden leeg gemaakt.
  2. de volgende stap is dat een nieuw spel moet worden opgezet. Doe dit op het tabblad Makkelijk of Moeilijk door onder een bestaand spel ‘met de hand’ cijfers op de juiste plaats te zetten.
    Kies dan op het tabblad Sudoku de knop Kopieer. Er komt een pop-up waarmee kan worden aangegeven welk spel uit de voorraad moet worden opgezet. Selecteer daartoe het betreffende blok met de muis en klik dan op OK.
  3. Als je klaar bent om te gaan spelen, klik dan op de knop Start. Allereerst worden alle opgezette cijfers geblokkeerd en vet gemaakt, het tabblad beveiligd (zonder wachtwoord) en de Starttijd ingevuld.
  4. Wil je tussentijds wat zetten gaan proberen, bewaar dan de tussenstand via de knop Save. De stand van dat moment wordt gekopieerd naar het tabblad SaveGame.

Handleiding

In het tabblad Handleiding staat de werkwijze van het systeem ook uitgelegd en ik heb enkele tips toegevoegd.

Veel speel-plezier!

 

Gantt-grafiek

Excel, , , ,
Voorbeeldbestand: Gantt.xlsxDownload

Henry Laurence Gantt ontwikkelde de Gantt-grafiek rond 1910.
In zijn werk als werktuigkundig ingenieur, managementconsultant en bedrijfsadviseur werd de Gantt-grafiek gebruikt als een visueel hulpmiddel om de planning en voortgang van een project te laten zien. In die tijd werd dit gezien als een opzienbarende innovatie.

Voor (complexe) Gantt-grafieken worden veelal specialistische (project-)programma’s gebruikt, maar voor het gewone werk kunnen we met Excel ook al aardig vooruit.

Aan de hand van een simpele project-opzet zullen we 2 mogelijkheden voor een Gantt-grafiek bekijken.

Project-data

Hiernaast ziet u de gegevens van een fictief project (zie het tabblad ProjData in het Voorbeeldbestand).
Per stap en substap leggen we een volgnummer vast, samen met een omschrijving, een begindatum en het aantal geplande dagen dat de stap duurt (alleen op het laagste niveau).

Uiteraard beïnvloeden de stappen elkaar. In het voorbeeld zijn dat de volgende zaken:

  1. de startdatum van het project leggen we vast in cel D3
  2. in cel D4 komt de echte startdatum van de eerste hoofd-stap, de Brainstorm-sessies. We gebruiken de functie WEEKDAG om te controleren of D3 niet in een weekend valt. Als D3 een zondag is dan is het resultaat van WEEKDAG een 1, bij maandag een 2 etc.
  3. er zijn 2 brainstormsessies, die parallel plaats vinden. De startdatums daarvan (cellen D5 en D6) zijn gelijk aan D4.
  4. In E5 en E6 ligt vast hoeveel dagen deze sessies in beslag nemen.
  5. de einddatum van deze 2 substappen wordt bepaald met de functie WERKDAG. Zo staat in F5 de formule =WERKDAG(D5;E5-1)
    Deze functie bepaalt de einddatum door bij de startdatum (D5) een aantal dagen op te tellen (E5); de functie telt dagen van het weekend daarbij niet mee.
    Omdat op de startdatum ook al gewerkt wordt, verlagen we het aantal te werken dagen met 1.
  6. De afronding van de brainstorm vindt plaats op de eerste werkdag nadat de laatste sessie klaar is; in cel D7 staat daarom de formule =WERKDAG(MAX(F5:F6);1)
  7. De ontwerpfase begint als de brainstorm voorbij is; eerst het Functioneel Ontwerp, daarna het Technisch Ontwerp en vervolgens een Eindoverleg om alle details door te nemen.
    De opzet van de cellen D8:D11 mag dan ook duidelijk zijn.
  8. De Bouw start na de afronding van het Ontwerp (cellen D12 en D13).
  9. Deel2 van de bouw kan pas beginnen na afronding van Deel1 (cel D14: =WERKDAG(F13;1)).
  10. Wel kan de bouw van de Schil parallel aan de andere bouwactiviteiten plaats vinden (cel D15 is gelijk aan D12).
  11. De daadwerkelijke oplevering vindt plaats nadat de laatste bouwactiviteit is afgerond; in D16 staat de formule =WERKDAG(MAX(F13:F15);1)

NB de functie WERKDAG kent nog een derde parameter, Vakantiedagen. Leg ergens in uw werkmap een overzicht vast van die dagen, dat er voor het project niet gewerkt kan worden en voeg deze reeks als derde parameter toe aan alle WERKDAG-functies (zie een voorbeeld op het tabblad ProjData).

Gantt-grafiek 1

Op basis van de gegevens uit het tabblad ProjData van het Voorbeeldbestand gaan we nu een grafiek maken. Wel hebben we dan nog 2 hulpkolommen nodig:

  • in kolom G komt de startdatum van de betreffende stap minus 1 (waarom dat zullen we hieronder zien; datums zijn voor Excel gewoon getallen dus in G3 komt de formule =D3-1)
  • in kolom H komt het verschil in kalenderdagen tussen de start- en einddatum van iedere stap (in H3 staat dan de formule =F3-D3+1; met 1 gecorrigeerd omdat de startdatum ook meedoet)

Dan kunnen we de grafiek gaan opbouwen:

  1. selecteer de cellen G3:G16
  2. kies in de menutab Invoegen in het blok Grafieken de optie Staaf
  3. en dan bij 2D-staaf de 2e optie (Gestapelde staaf). Het eerste gedeelte van de grafiek is klaar.
  4. selecteer de cellen H3:H16 en Kopieer deze (Ctrl-C)
  5. klik ergens in de grafiek van punt 3 en Plak (Ctrl-V)
  6. de legenda kan weg: klik er op en druk op Delete
  7. de basis-as moet nog aangepast worden:
    * klik met de rechtermuisknop ergens in de grafiek
    * kies de optie Gegevens selecteren
    * klik op de button Bewerken onder Horizontale aslabels
    * selecteer de cellen B4:C16 en klik op OK
    * nog een keer OK om bij de grafiek terug te komen
  8. eigenlijk willen we alleen de tweede staaf zien; de eerste geeft het verloop tot aan de startdatum weer (vandaar de minus 1 in kolom G):
    * klik rechts op één van de staven, die ‘weg’ moeten
    * kies de optie Gegevensreeks opmaken
    * kies onder Opvulling de optie Geen opvulling en dan de button Sluiten
  9. Nog één aanpassing. De volgorde van de stappen is (voor ons) niet logisch:
    * klik rechts op de Categorie-as
    * kies de optie As opmaken
    * vink de optie Categorieën in omgekeerde volgorde aan en klik op de button Sluiten

In het tabblad Gantt1 ziet u het resultaat van bovenstaande exercitie (met nog enkele aanpassingen, die de grafiek overzichtelijker maken).

NB kijk wat er gebeurt, als je in cel D3 van het tabblad ProjData een andere startdatum voor het project invoert.

Gantt-grafiek 2

Deze Gantt-grafiek is op een heel andere manier tot stand gekomen (het is geen echte grafiek, maar een inkleuring van Excel-cellen; zie tabblad Gantt2 van het Voorbeeldbestand):

  1. het ‘blauwe’ blok zijn allemaal verwijzingen naar het tabblad ProjData; bijvoorbeeld in cel B6 staat de formule =ProjData!B4
  2. in cel H4 staat de formule =D5-5, zodat de ‘grafiek’ niet exact op de startdatum begint, maar 5 dagen eerder.
  3. in I4 staat de volgende dag =H4+1; deze formule is zover naar rechts gekopieerd, dat de einddatum van het project in de rij voorkomt.
  4. in H3 hebben we de Maand zichtbaar gemaakt met de formule =Tekst(H4,”mmm”) en deze formule is ook naar rechts gekopieerd
  5. in regel 2 bepalen we op een vergelijkbare manier het Jaar

Alle overige effecten (verticale lijnen, gekleurde cellen en het al dat niet zichtbaar maken van het jaar en de maand) zijn met behulp van Voorwaardelijke opmaak geïmplementeerd (LET OP deze optie is niet direct beschikbaar; om ongewilde wijzigingen te voorkomen is  het werkblad Gantt2 beveiligd. Verwijder de beveiliging via de menutab Controleren en dan de optie Beveiliging blad opheffen (geen wachtwoord vereist). Vergeet niet daarna de beveiliging weer aan te brengen!):

  1. als de tekst in regel 3 verandert (H$3<>G$3), dan komt er een rand aan de linkerkant van de cellen in de regels 2 t/m 18
  2. als de inhoud van een cel in de 2e en 3e regel gelijk is aan de cel links daarvan (H2=G2) dan krijgt de tekst in die cel een witte kleur en is dus niet meer zichtbaar
  3. de cellen in de regels 4 t/m 18 krijgen een rode linker- en rechterrand als de inhoud van de 4e regel gelijk is aan de datum van vandaag (H$4=VANDAAG()).
  4. ook krijgen de cellen in de regels 4 t/m 18 een groenige achtergrond als de datum in de 4e regel een weekend voorstelt (WEEKDAG(H$4;2)>5; de parameter 2 geeft aan dat de week op een maandag begint)
  5. de cellen in de regels 5 t/m 18 krijgen een bruinige achtergrond als de datum in de 4e regel groter of gelijk is aan de startdatum in kolom D EN de datum in de 4e regel kleiner of gelijk is aan de datum in de kolom E.

Welke methode heeft de voorkeur?

Mijn persoonlijke voorkeur gaat naar de 2e methode uit. Hierbij is ieder facet van de ‘grafiek’ in te stellen.

Dit is meteen ook het grootste nadeel van deze methode. Dus heb je snel een grafiek nodig, gebruik dan methode 1.

Laat in een reactie hieronder weten, welke methode u prefereert.