Warmtekaart maken in Excel

In Excel lijkt een warmtekaart op een tabel, wat een visuele weergave is die een vergelijkend overzicht van een dataset toont. Als er een grote dataset in uw werkblad staat, is het echt moeilijk om in één oogopslag de laagste of hoogste waarden te identificeren. Maar in een warmtekaart wordt de celwaarde weergegeven in een verschillend kleurpatroon, zodat we grotere of kleinere data snel en gemakkelijk kunnen zien, zoals in onderstaande schermafbeelding te zien is.

• Een eenvoudige warmtekaart maken met Voorwaardelijke Opmaak
• Een dynamische warmtekaart maken in Excel
• Voorbeeld 1: Maak een dynamische warmtekaart met behulp van een Schuifbalk
• Voorbeeld 2: Maak een dynamische warmtekaart met behulp van Radioknoppen
• Voorbeeld 3: Maak een dynamische warmtekaart met behulp van een Selectievakje
• Download voorbeeldbestand Warmtekaart
• Video: Warmtekaart maken in Excel

Een eenvoudige warmtekaart maken met Voorwaardelijke Opmaak

Er is geen directe warmtekaartfunctie in Excel, maar met de krachtige functie Voorwaardelijke Opmaak kunt u snel een warmtekaart maken. Volg hiervoor de volgende stappen:

1. Selecteer het gegevensbereik waarop u de Voorwaardelijke Opmaak wilt toepassen.

2. Klik vervolgens op Start > Voorwaardelijke Opmaak > Kleurschalen, en kies vervolgens een stijl die u nodig hebt uit de rechteruitgebreide vervolgkeuzelijst (in dit geval selecteer ik Groen - Geel - Rode Kleurschaal), zie onderstaande schermafbeelding:

3. Nu is de warmtekaart gemaakt die de cellen markeert op basis van hun waarden; groene kleur vertegenwoordigt de hoogste waarden en rode kleur vertegenwoordigt de laagste waarden, terwijl de overige waarden een kleurovergang tussen groen en rood tonen. Zie onderstaande schermafbeelding:

4. Als u de getallen wilt verbergen en alleen de kleuren wilt laten staan, selecteert u het gegevensbereik en drukt u op Ctrl + 1 om het dialoogvenster Celopmaak te openen.

5. In het dialoogvenster Celopmaak, onder het tabblad Getal, klikt u op Aangepast in de linkerlijst Categorie, en voert u vervolgens ;;; in het tekstvak Type in, zie onderstaande schermafbeelding:

6. Klik vervolgens op de knop OK, en alle getallen zijn verborgen zoals in onderstaande schermafbeelding te zien is:

Opmerking: Om de cellen met andere kleuren te markeren die u leuk vindt, selecteert u het gegevensbereik en klikt u op Start > Voorwaardelijke Opmaak > Regels Beheren om naar het dialoogvenster Manager Voorwaardelijke Opmaakregels te gaan.

Dubbelklik vervolgens op de bestaande regel om het dialoogvenster Regel voor Opmaak Bewerken te openen, en reset de regel naar uw behoefte, zie onderstaande schermafbeelding:

Een dynamische warmtekaart maken in Excel

Voorbeeld 1: Maak een dynamische warmtekaart met behulp van een Schuifbalk

Als er meerdere kolomgegevens in uw werkblad staan, maar u ze in een beperkte ruimte wilt weergeven, kunt u in dit geval een schuifbalk invoegen in het werkblad om de warmtekaart dynamisch te maken, zoals in onderstaande demo te zien is.

Volg de volgende stappen om dit type dynamische warmtekaart te maken:

1. Voeg een nieuw werkblad in en kopieer vervolgens de eerste kolom maanden van het originele blad naar dit nieuwe blad.

2. Klik vervolgens op Ontwikkelaar > Invoegen > Schuifbalk, zie onderstaande schermafbeelding:

3. Sleep vervolgens de muis om een schuifbalk onder de gekopieerde gegevens te tekenen, klik met de rechtermuisknop op de schuifbalk en selecteer Formaat Besturingselement, zie onderstaande schermafbeelding:

4. Stel in het dialoogvenster Formaat Object, onder het tabblad Besturingselement, de minimale waarde, maximale waarde, incrementele wijziging, paginawijziging en een gelinkte cel in op basis van uw gegevensbereik, zoals in onderstaande schermafbeelding te zien is:

5. Klik vervolgens op OK om dit dialoogvenster te sluiten.

6. Typ nu in cel B1 van dit nieuwe blad de volgende formule en druk op Enter om het eerste resultaat te krijgen:

Opmerking: In de bovenstaande formule is data1!$B$1:$I$13 het originele blad met het gegevensbereik, exclusief de rijkop (maanden), $I$1 is de cel waaraan de schuifbalk is gekoppeld, $B$1:B1 is de cel waar u de formule uitvoert.

7. Sleep deze formulecel vervolgens naar de rest van de cellen. Als u slechts 3 jaar in het werkblad wilt weergeven, sleep dan de formule van B1 naar D13, zie onderstaande schermafbeelding:

8. Pas vervolgens de Kleurschaal van de functie Voorwaardelijke Opmaak toe op het nieuwe gegevensbereik om de warmtekaart te maken. Nu, wanneer u de schuifbalk sleept, zal de warmtekaart dynamisch bewegen, zie onderstaande schermafbeelding:

Voorbeeld 2: Maak een dynamische warmtekaart met behulp van Radioknoppen

U kunt ook een dynamische warmtekaart maken met behulp van radioknoppen. Het selecteren van één radioknop markeert de grootste n-waarden, en het selecteren van een andere radioknop markeert de kleinste n-waarden, zoals in onderstaande demo te zien is:

Volg deze stappen om dit type dynamische warmtekaart te voltooien:

1. Klik op Ontwikkelaar > Invoegen > Optieknop (Formulierbesturingselement), sleep vervolgens de muis om twee radioknoppen te tekenen en bewerk de tekst naar uw behoefte, zie onderstaande schermafbeelding:

2. Nadat u de radioknoppen hebt ingevoegd, klikt u met de rechtermuisknop op de eerste en selecteert u Formaat Besturingselement. In het dialoogvenster Formaat Besturingselement, onder het Besturingselement tabblad, selecteert u een cel die aan de radioknop is gekoppeld, zie onderstaande schermafbeelding:

3. Klik op OK om het dialoogvenster te sluiten en herhaal vervolgens de bovenstaande stap (stap 2) om de tweede radioknop aan dezelfde cel (cel M1) te koppelen.

4. Pas vervolgens de voorwaardelijke opmaak toe op het gegevensbereik. Selecteer het gegevensbereik en klik op Start > Voorwaardelijke Opmaak > Nieuwe Regel, zie onderstaande schermafbeelding:

5. Selecteer in het dialoogvenster Nieuwe Opmaakregel de optie Gebruik een formule om te bepalen welke cellen te formatteren uit de lijst Regeltype selecteren, en voer vervolgens deze formule in: =IF($M$1=1,IF(B2>=GROOTSTE($B$2:$I$13,15),WAAR,ONWAAR)) in het tekstvak Waarden formatteren waar deze formule waar is en klik op de knop Formatteren om een kleur te selecteren. Zie onderstaande schermafbeelding:

6. Klik op OK, dit zal de grootste 15 waarden markeren met de kleur rood wanneer u de eerste radioknop selecteert.

7. Om de kleinste 15 waarden te markeren, houdt u de gegevens geselecteerd en gaat u naar het dialoogvenster Nieuwe Opmaakregel, en voert u vervolgens deze formule in: =IF($M$1=2,IF(B2<=KLEINSTE($B$2:$I$13,15),WAAR,ONWAAR)) in het tekstvak Waarden formatteren waar deze formule waar is, en klikt u op de knop Formatteren om een andere kleur te kiezen die u nodig heeft. Zie onderstaande schermafbeelding:

Opmerking: In de bovenstaande formules is $M$1 de cel die aan de radioknoppen is gekoppeld, $B$2:$I$13 is het gegevensbereik waarop u de voorwaardelijke opmaak wilt toepassen, B2 is de eerste cel van het gegevensbereik, en het getal 15 is het specifieke aantal dat u wilt markeren.

8. Klik op OK om het dialoogvenster te sluiten. Nu, bij het selecteren van de eerste radioknop, worden de grootste 15 waarden gemarkeerd, en bij het selecteren van de tweede radioknop worden de kleinste 15 waarden gemarkeerd, zoals in onderstaande demo te zien is:

Voorbeeld 3: Maak een dynamische warmtekaart met behulp van een Selectievakje

In deze sectie introduceer ik een dynamische warmtekaart met behulp van een selectievakje die u kan helpen om de warmtekaart te tonen of te verbergen op basis van uw behoefte. Als u het selectievakje aanvinkt, wordt de warmtekaart weergegeven, als u het selectievakje uitvinkt, wordt deze onmiddellijk verborgen, zie onderstaande demo:

1. Converteer eerst uw gegevensbereik naar een tabelindeling, wat u helpt om automatisch voorwaardelijke opmaak toe te passen wanneer u een nieuwe gegevensrij invoegt. Selecteer het gegevensbereik en druk vervolgens tegelijkertijd op Ctrl + T om het dialoogvenster Tabel Maken te openen, zie onderstaande schermafbeelding:

2. Klik op OK om het dialoogvenster te sluiten, en klik vervolgens op Ontwikkelaar > Invoegen > Selectievakje (Formulierbesturingselement), sleep vervolgens de muis om een selectievakje te tekenen en bewerk de tekst naar uw behoefte, zoals in onderstaande schermafbeeldingen te zien is:

3. Klik vervolgens met de rechtermuisknop op het selectievakje en selecteer Formaat Besturingselement. In het dialoogvenster Formaat Object, onder het tabblad Besturingselement, selecteert u een cel die aan het selectievakje is gekoppeld, zie onderstaande schermafbeelding:

4. Klik op OK om het dialoogvenster te sluiten, selecteer vervolgens het gegevensbereik waarvoor u de warmtekaart wilt maken, en klik op Start > Voorwaardelijke Opmaak > Nieuwe Regel om naar het dialoogvenster Nieuwe Opmaakregel te gaan.

5. Voer in het dialoogvenster Nieuwe Opmaakregel de volgende handelingen uit:

• Selecteer de optie Alle cellen formatteren op basis van hun waarden uit de lijst Regeltype selecteren;
• Kies 3-Kleurschaal uit de vervolgkeuzelijst Opmaakstijl;
• Selecteer Formule in de Type-vakken onder de vervolgkeuzelijsten Minimum, Middenpunt en Maximum afzonderlijk;
• Voer vervolgens de volgende formules in de drie Waarde-tekstvakken in:
• Minimum: =IF($M$1=TRUE,MIN($B$2:$I$13),FALSE)
• Middenpunt: =IF($M$1=TRUE,GEMIDDELDE($B$2:$I$13),FALSE)
• Maximum: =IF($M$1=TRUE,MAX($B$2:$I$13),FALSE)
• Specificeer vervolgens de markeringkleuren in de sectie Kleur naar uw behoefte.

Opmerking: In de bovenstaande formules is $M$1 de cel die aan het selectievakje is gekoppeld, $B$2:$I$13 is het gegevensbereik waarop u de voorwaardelijke opmaak wilt toepassen.

6. Nadat u de instellingen hebt voltooid, klikt u op de knop OK om het dialoogvenster te sluiten. Nu, wanneer u het selectievakje aanvinkt, wordt de warmtekaart weergegeven, anders wordt deze verborgen. Zie onderstaande demo:

Download voorbeeldbestand Warmtekaart

Video: Warmtekaart maken in Excel