Een hittekaart in Excel is een visuele weergave die u snel een vergelijkend overzicht van een dataset laat zien.
In de onderstaande dataset kan ik bijvoorbeeld gemakkelijk zien in welke maanden de verkoop laag was (rood gemarkeerd) in vergelijking met andere maanden.
In de bovenstaande dataset worden de kleuren toegewezen op basis van de waarde in de cel. De kleurenschaal is groen naar geel naar rood, waarbij hoge waarden de groene kleur krijgen en lage waarden de rode kleur.
Een hittekaart maken in Excel
Terwijl u een warmtekaart in Excel kunt maken door de cellen handmatig te kleuren. U moet het echter opnieuw doen wanneer de waarden veranderen.
In plaats van het handmatige werk, kunt u voorwaardelijke opmaak gebruiken om cellen te markeren op basis van de waarde. Op deze manier, als u de waarden in de cellen wijzigt, zou de kleur/opmaak van de cel automatisch de warmtekaart bijwerken op basis van de vooraf gespecificeerde regels in voorwaardelijke opmaak.
In deze zelfstudie leert u hoe u:
- Maak snel een heatmap in Excel met behulp van voorwaardelijke opmaak.
- Maak een dynamische heatmap in Excel.
- Maak een heatmap in Excel Pivot Tables.
Laten we beginnen!
Een hittekaart maken in Excel met voorwaardelijke opmaak
Als je een dataset in Excel hebt, kun je handmatig datapunten markeren en een heatmap maken.
Dat zou echter een statische warmtekaart zijn, omdat de kleur niet zou veranderen wanneer u de waarde in een cel wijzigt.
Daarom is voorwaardelijke opmaak de juiste manier om te gaan, omdat de kleur in een cel verandert wanneer u de waarde erin wijzigt.
Stel dat u een dataset heeft zoals hieronder weergegeven:
Dit zijn de stappen om een warmtekaart te maken met behulp van deze gegevens:
- Selecteer de gegevensset. In dit voorbeeld zou dit B2:D13 zijn.
- Ga naar Home -> Conditionele opmaak -> Kleurschalen. Het toont verschillende kleurencombinaties die kunnen worden gebruikt om de gegevens te markeren. De meest voorkomende kleurenschaal is de eerste waarbij cellen met hoge waarden groen en laag rood worden gemarkeerd. Merk op dat wanneer u de muisaanwijzer over deze kleurenschalen beweegt, u de live preview in de dataset kunt zien.
Dit geeft u een warmtekaart zoals hieronder weergegeven:
Standaard wijst Excel de rode kleur toe aan de laagste waarde en de groene kleur aan de hoogste waarde, en alle overige waarden krijgen een kleur op basis van de waarde. Er is dus een verloop met verschillende tinten van de drie kleuren op basis van de waarde.
Wat als u geen verloop wilt en alleen rood, geel en groen wilt weergeven. U wilt bijvoorbeeld alle waarden kleiner dan bijvoorbeeld 700 in rood markeren, ongeacht de waarde. Dus 500 en 650 krijgen beide dezelfde rode kleur omdat het minder dan 700 is.
Om dit te doen:
- Ga naar Home -> Conditionele opmaak -> Kleurschalen -> Meer opties.
- Selecteer in het dialoogvenster Nieuwe opmaakregel '3-kleurenschaal' in de vervolgkeuzelijst Opmaakstijl.
- Nu kunt u de minimum-, middelpunt- en maximumwaarde specificeren en de kleur eraan toewijzen. Omdat we alle cellen met een waarde onder 700 in rood willen markeren, wijzigt u het type in Getal en waarde in 700.
- Klik OK.
Nu krijgt u het resultaat zoals hieronder weergegeven. Merk op dat alle waarden onder 700 dezelfde rode kleur krijgen.
Een woord van waarschuwing: Hoewel voorwaardelijke opmaak een geweldig hulpmiddel is, is het helaas vluchtig. Dit betekent dat wanneer er een wijziging in het werkblad is, de voorwaardelijke opmaak opnieuw wordt berekend. Hoewel de impact op kleine datasets verwaarloosbaar kan zijn, kan het leiden tot een trage Excel-werkmap bij het werken met grote datasets.
Een dynamische hittekaart maken in Excel
Aangezien voorwaardelijke opmaak afhankelijk is van de waarde in een cel, wordt de voorwaardelijke opmaak herberekend en gewijzigd zodra u de waarde wijzigt.
Dit maakt het mogelijk om een dynamische heatmap te maken.
Laten we eens kijken naar twee voorbeelden van het maken van warmtekaarten met interactieve bedieningselementen in Excel.
Voorbeeld 1: Heatmap met schuifbalk
Hier is een voorbeeld waarbij de warmtekaart verandert zodra u de schuifbalk gebruikt om het jaar te wijzigen.
Dit type dynamische warmtekaarten kan worden gebruikt in dashboards waar u beperkte ruimte heeft, maar toch wilt dat de gebruiker toegang heeft tot de volledige gegevensset.
Klik hier om de Heatmap-sjabloon te downloaden
Hoe maak je deze dynamische heatmap?
Hier is de volledige dataset die is gebruikt om deze dynamische warmtekaart te maken.
Dit zijn de stappen:
- Voer in een nieuw blad (of in hetzelfde blad) de maandnamen in (kopieer het eenvoudig uit de originele gegevens).
- Ga naar Ontwikkelaar -> Besturing -> Invoegen -> Schuifbalk. Klik nu ergens in het werkblad en er wordt een schuifbalk ingevoegd. (klik hier als u het tabblad Ontwikkelaar niet kunt vinden).
- Klik met de rechtermuisknop op de schuifbalk en klik op Opmaakbesturing.
- Breng in het dialoogvenster Opmaakbeheer de volgende wijzigingen aan:
- Minimale waarde: 1
- Maximale waarde 5
- Celkoppeling: Blad1!$J$1 (U kunt op het pictogram rechts klikken en vervolgens handmatig de cel selecteren die u aan de schuifbalk wilt koppelen).
- Klik OK.
- Voer in cel B1 de formule in: =INDEX(Sheet1!$B$1:$H$13,ROW(),Sheet1!$J$1+COLUMNS(Sheet2!$B$1:B1)-1)
- Pas het formaat aan en plaats de schuifbalk onderaan de dataset.
Wanneer u nu de schuifbalk wijzigt, zou de waarde in Blad1!$J$1 veranderen, en aangezien de formules aan deze cel zijn gekoppeld, zou deze worden bijgewerkt om de juiste waarden weer te geven.
Omdat voorwaardelijke opmaak vluchtig is, wordt deze ook bijgewerkt zodra de waarde verandert.
Bekijk video - Dynamische hittekaart in Excel
Voorbeeld 2: Een dynamische hittekaart maken in Excel met keuzerondjes
Hier is nog een voorbeeld waarbij u de warmtekaart kunt wijzigen door een keuzerondje te selecteren:
In dit voorbeeld kunt u de bovenste/onderste 10 waarden markeren op basis van de selectie van de keuzerondjes/optie.
Klik hier om de Heatmap-sjabloon te downloaden
Een hittekaart maken in Excel-draaitabel
Voorwaardelijke opmaak in draaitabellen werkt op dezelfde manier als bij normale gegevens.
Maar er is iets belangrijks dat u moet weten.
Laat me een voorbeeld nemen en je laten zien.
Stel dat u een draaitabel heeft zoals hieronder weergegeven:
Een warmtekaart maken in deze Excel-draaitabel:
- Selecteer de cellen (B5:D14).
- Ga naar Home -> Voorwaardelijke opmaak -> Kleurschalen en selecteer de kleurenschaal die je wilt toepassen.
Dit zou onmiddellijk de warmtekaart in de draaitabel maken.
Het probleem met deze methode is dat als u nieuwe gegevens toevoegt in de backend en deze draaitabel vernieuwt, de voorwaardelijke opmaak niet wordt toegepast op de nieuwe gegevens.
Toen ik bijvoorbeeld nieuwe gegevens aan de achterkant heb toegevoegd, de brongegevens heb aangepast en de draaitabel heb vernieuwd, kun je zien dat er geen voorwaardelijke opmaak op is toegepast.
Dit gebeurt omdat we de voorwaardelijke opmaak alleen op cellen B5:D14 hebben toegepast.
Als u wilt dat deze warmtekaart dynamisch is zodat deze wordt bijgewerkt wanneer nieuwe gegevens worden toegevoegd, volgen hier de stappen:
- Selecteer de cellen (B5:D14).
- Ga naar Home -> Voorwaardelijke opmaak -> Kleurschalen en selecteer de kleurenschaal die je wilt toepassen.
- Ga opnieuw naar Home -> Conditionele opmaak -> Regels beheren.
- Klik in Regelbeheer voor voorwaardelijke opmaak op de knop Bewerken.
- Selecteer in het dialoogvenster Opmaakregel bewerken de derde optie: Alle cellen met 'Verkoop'-waarden voor 'Datum' en 'Klant'.
Nu wordt de voorwaardelijke opmaak bijgewerkt wanneer u de backend-gegevens wijzigt.
Opmerking: Voorwaardelijke opmaak verdwijnt als u de rij-/kolomvelden wijzigt. Als u bijvoorbeeld het veld Datum verwijdert en opnieuw toepast, gaat de voorwaardelijke opmaak verloren.
