een heatmap in Excel is een visuele representatie die je snel een vergelijkende weergave van een dataset laat zien.
bijvoorbeeld, in de dataset hieronder, kan ik gemakkelijk zien welke maanden de verkoop laag waren (gemarkeerd in rood) in vergelijking met andere maanden.
in de bovenstaande dataset worden de kleuren toegewezen op basis van de waarde in de cel. De kleurschaal is groen naar geel naar rood met hoge waarden die de groene kleur krijgen en lage waarden die de rode kleur krijgen.
Deze Tutorial omvat:
een heatmap maken in Excel
terwijl u een heatmap in Excel kunt maken door de cellen handmatig te kleuren. U zult het echter opnieuw moeten doen wanneer de waarden veranderen.
in plaats van handmatig werk kunt u voorwaardelijke opmaak gebruiken om cellen te markeren op basis van de waarde. Op deze manier, in het geval u de waarden in de cellen wijzigt, wordt de kleur/indeling van de cel automatisch bijgewerkt op basis van de vooraf gespecificeerde regels in voorwaardelijke opmaak.
In deze tutorial leert u hoe u:
- snel een heatmap in Excel maakt met behulp van voorwaardelijke opmaak.
- Maak een dynamische heatmap in Excel.
- Maak een heatmap in Excel-Draaitabellen.
laten we beginnen!
een heatmap maken in Excel met Conditionele opmaak
Als u een dataset in Excel hebt, kunt u handmatig gegevenspunten markeren en een heatmap maken.
dat zou echter een statische warmteafbeelding zijn omdat de kleur niet verandert wanneer u de waarde in een cel wijzigt.
daarom is conditionele opmaak de juiste manier, omdat het de kleur in een cel verandert wanneer u de waarde ervan wijzigt.
stel dat u een dataset hebt zoals hieronder getoond:
Hier zijn de stappen om een heatmap te maken met behulp van deze gegevens:
- Selecteer de dataset. In dit voorbeeld zou het B2:D13 zijn.
- ga naar Home –> Voorwaardelijke opmaak –> kleurschalen. Het toont verschillende kleurencombinaties die kunnen worden gebruikt om de gegevens te markeren. De meest voorkomende kleurschaal is de eerste waar cellen met hoge waarden in groen en laag in rood worden gemarkeerd. Houd er rekening mee dat als u met de muis over deze kleurschalen gaat, u de live preview kunt zien in de dataset.
Dit geeft u een heatmap zoals hieronder getoond:
standaard kent Excel rode kleur toe aan de laagste waarde en de groene kleur aan de hoogste waarde, en alle resterende waarden krijgen een kleur gebaseerd op de waarde. Dus er is een verloop met verschillende tinten van de drie kleuren op basis van de waarde.
nu, wat als ik geen verloop wil en alleen rood, geel en groen wil tonen. U wilt bijvoorbeeld alle waarden minder dan 700 in rood markeren, ongeacht de waarde. Dus 500 en 650 krijgen beide dezelfde rode kleur omdat het minder is dan 700.
om dit te doen:
- ga naar Home – > Voorwaardelijke opmaak –> kleurschalen –> meer opties.
- in het dialoogvenster Nieuwe opmaakregel selecteert u ‘3-Color scale’ in de opmaakstijl drop-down.
- nu kunt u het minimum, middelpunt en de maximale waarde opgeven en de kleur eraan toewijzen. Omdat we alle cellen met een waarde onder 700 in rood willen markeren, wijzigt u het type In nummer en waarde in 700.
- klik op OK.
nu krijgt u het resultaat zoals hieronder getoond. Merk op dat alle waarden onder 700 dezelfde tint rode kleur krijgen.
Een Woord van waarschuwing: hoewel voorwaardelijke opmaak een prachtig hulpmiddel is, is het helaas Vluchtig. Dit betekent dat wanneer er een verandering in het werkblad is, voorwaardelijke opmaak opnieuw wordt berekend. Hoewel de impact verwaarloosbaar kan zijn op kleine datasets, kan het leiden tot een langzame Excel-werkmap bij het werken met grote datasets.
een dynamische Warmteafbeelding maken in Excel
omdat voorwaardelijke opmaak afhankelijk is van de waarde in een cel, wordt voorwaardelijke opmaak opnieuw berekend en gewijzigd zodra u de waarde wijzigt.
Dit maakt het mogelijk om een dynamische warmtekaart te maken.
laten we eens kijken naar twee voorbeelden van het maken van heat maps met behulp van interactieve besturingselementen in Excel.
Voorbeeld 1: Heatmap met schuifbalk
Hier is een voorbeeld waarin de heatmap verandert zodra u de schuifbalk gebruikt om het jaar te veranderen.
Dit type dynamische heat maps kan worden gebruikt in dashboards waar u ruimtebeperkingen hebt, maar toch wilt dat de gebruiker toegang heeft tot de volledige dataset.
Klik hier om de Heat Map template te downloaden
Hoe maak ik deze dynamische heatmap?
Hier is de volledige dataset die wordt gebruikt om deze dynamische warmtekaart te maken.
Hier zijn de stappen:
- voer in een nieuw blad (of in hetzelfde blad) de namen van de maand in (Kopieer het gewoon uit de oorspronkelijke gegevens).
- ga naar Ontwikkelaar – > besturingselementen –> Insert –> schuifbalk. Klik nu ergens in het werkblad en het voegt een schuifbalk in. (Klik hier als u het tabblad Ontwikkelaar niet kunt vinden).
- Klik met de rechtermuisknop op de schuifbalk en klik op Format Control.
- in het dialoogvenster Format Control, breng de volgende wijzigingen aan:
- minimale waarde: 1
- maximale waarde 5
- Cell Link: Sheet1!$J$1 (U kunt op het pictogram rechts klikken en vervolgens handmatig de cel selecteren die u wilt koppelen aan de schuifbalk).
- klik op OK.
- voer in cel B1 de formule in: = INDEX (Sheet1!$B $ 1:$H$13, ROW (), Sheet1!$J $ 1 + kolommen (Sheet2!$ B $ 1:B1) -1)
- Formaat wijzigen en plaats de schuifbalk onderaan de gegevensverzameling.
wanneer u nu de schuifbalk wijzigt, de waarde in Sheet1!$J$1 zou veranderen en omdat de formules aan deze cel zijn gekoppeld, zou deze worden bijgewerkt om de juiste waarden weer te geven.
ook, omdat voorwaardelijke opmaak Vluchtig is, wordt deze ook bijgewerkt zodra de waarde verandert.
Bekijk video-dynamische Warmteafbeelding in Excel
Voorbeeld 2: Een dynamische Heatmap maken in Excel met behulp van keuzerondjes
Hier is een ander voorbeeld waar u de heatmap 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 radio/optieknop.
Klik hier om de heatmap-sjabloon
te downloaden het maken van een heatmap in Excel-draaitabel
Voorwaardelijke opmaak in draaitabellen werkt op dezelfde manier als met alle 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 hebt zoals hieronder getoond:
om een heatmap te maken in deze Excel-draaitabel:
- Selecteer de cellen (B5:D14).
- ga naar Home – > Voorwaardelijke opmaak –> kleurschalen en selecteer de kleurschaal die u wilt toepassen.
Dit zou direct de heatmap in de draaitabel maken.
het probleem met deze methode is dat als u nieuwe gegevens toevoegt aan de backend en deze draaitabel vernieuwt, de voorwaardelijke opmaak niet op de nieuwe gegevens wordt toegepast.
bijvoorbeeld, omdat ik nieuwe gegevens aan de achterkant heb toegevoegd, de brongegevens heb aangepast en de draaitabel heb vernieuwd, kunt u zien dat voorwaardelijke opmaak er niet op wordt toegepast.
Dit gebeurt als we de voorwaardelijke opmaak alleen op cellen B5:D14 hebben toegepast.
Als u wilt dat deze heatmap dynamisch is, zodat het wordt bijgewerkt wanneer nieuwe gegevens worden toegevoegd, zijn hier de stappen:
- Selecteer de cellen (B5:D14).
- ga naar Home – > Voorwaardelijke opmaak –> kleurschalen en selecteer de kleurschaal die u wilt toepassen.
- ga opnieuw naar Home – > Voorwaardelijke opmaak –> Regels beheren.
- in de Conditional Formatting Rules Manager, klik op de knop Bewerken.
- in het dialoogvenster opmaakregel bewerken, selecteert u de derde optie: alle cellen met ‘Sales’ – waarden voor ‘Date’ en ‘Customer’.
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 datumveld verwijdert en het opnieuw toepast, gaat voorwaardelijke opmaak verloren.
u kunt ook de volgende Excel-Tutorials leuk vinden:
- Hoe om de andere rij in Excel te markeren.
- Maak een 100% Stack-kolomdiagram met voorwaardelijke opmaak.
- Hoe gekleurde cellen in Excel te tellen.
- Hoe lege cellen in Excel te markeren.
- Excel Sparklines-A Complete Guide