Excel-thermometergrafiek - gratis downloaden en maken

Deze zelfstudie laat zien hoe u een thermometergrafiek maakt in alle versies van Excel: 2007, 2010, 2013, 2016 en 2022.

Een thermometergrafiek is een speciale wijziging van een gestapelde kolomgrafiek. De kolommen zijn op elkaar geplaatst en lijken visueel op een thermometer, die zich vult naarmate je verder komt naar je doel.

Elke keer dat u de voortgang naar een doel moet volgen, of het nu gaat om financiering, verkoop, omzet of andere relevante statistieken, biedt deze grafiek een eenvoudige en effectieve manier om die gegevens te visualiseren.

Maar een eenvoudige thermometerkaart snijdt de mosterd gewoon niet meer (kom op, dit is 2022!).

In deze diepgaande, stapsgewijze zelfstudie leert u hoe u een opmerkelijk veelzijdige thermometer-doelgrafiek kunt plotten met een mooi label dat werkt, zelfs als uw resultaten een bepaald doel overschrijden.

Bekijk het maar eens:

Zoals je binnenkort zult zien, kost het veel moeite om een ​​indrukwekkende thermometergrafiek te maken. Daarom hebben we de Chart Creator Add-In ontworpen, een tool waarmee je met slechts een paar klikken geavanceerde Excel-diagrammen kunt maken.

Dus laten we tot de kern van de zaak komen.

Beginnen

Stel je ter illustratie voor dat je een e-commerce winkel hebt gelanceerd als bijzaak en een thermometergrafiek wilt maken om de prestaties van de winkel bij te houden ten opzichte van je aangegeven omzetdoel. In 2022 had je een behoorlijk goede run.

De winkel genereerde $ 21.734 aan inkomsten, iets meer dan je oorspronkelijke doel (je kunt later een fles champagne knallen).

Dat gezegd hebbende, bekijk de dataset voor de grafiek:

Voordat we verder gaan met het maken van de grafiek, moeten we elk gegevenspunt opsplitsen, zodat u de stappen gemakkelijk kunt volgen met behulp van uw gegevens.

De dataset heeft twee kolommen:

• Oorspronkelijke gegevens: Deze kolom vertegenwoordigt uw werkelijke gegevens, wat deze ook mogen zijn. Laat het gewoon zoals het is.
• Kaartingangen: Dit is waar alle magie gebeurt. De tabel bevat alle speciale waarden die u zult gebruiken voor het samenstellen van de grafiek. Ook een belangrijke opmerking: de categorieën moeten op exact dezelfde manier worden gerangschikt als op de bovenstaande schermafbeelding!

Je vraagt ​​je misschien af ​​waar alle waarden in de tweede tabel vandaan komen. Nou, die zul je eigenlijk zelf moeten uitrekenen met behulp van onderstaande speciale Excel-formules (surprise, surprise).

Helaas is er geen andere manier om het te omzeilen, maar de onderstaande instructies zouden het vrij eenvoudig moeten maken om te volgen.

Dus de reis begint!

Stap 1: Stel de helpertafel in.

Als de spil die alles bij elkaar houdt, hebben alle elementen in de tweede tabel hun doel. Laten we deze slechteriken meer in detail bespreken.

Doelinkomsten: Dit gegevenspunt moet overeenkomen met de doelwaarde (B7). Zo simpel is het.

Totale omzet formule: Deze ALS-functie voorkomt dat de gegevensmarkeringen verkeerd op elkaar worden gestapeld als het resultaat het doel overschrijdt:

1	=ALS(B6<=B7, B6, B7)

Hier is de gedecodeerde versie op basis van de originele gegevens:

1	=ALS({Totaal}<={Doel}, {Totaal}, {Doel})

Vertaling in menselijke basistaal: als het resultaat (B6) is kleiner dan of gelijk aan het doel (B7), retourneert de formule de resultaatwaarde (B6) - in ons geval alles dat onder de $ 20K valt. Omgekeerd, als het resultaat (B6) overschrijdt het doel (B7), retourneert de formule de doelwaarde (B7).

Snelle tip: klik op de "Boekhoudnummer formaat” knop (Tabblad Start > Nummergroep) om de functie-uitvoer als valuta weer te geven.

Overtroffen: Dit ALS-functie berekent of uw doel al dan niet wordt overtroffen. Telkens wanneer dat gebeurt, trekt de formule het doel af (B7) van het resultaat (B6) en geeft terug wat er over is. Anders toont de formule eenvoudig nul:

1	=ALS(B7<=B6, B6-B7, 0)

De gedecodeerde versie:

1	=ALS({Doel}<={Totaal}, {Totaal}-{Doel}, 0)

Voortgang: Deze is vrij duidelijk. We moeten de voortgang meten als een percentage van het doel voor ons ultra-fancy label. Deel eenvoudig het resultaat (B6) door het doel (B7) en formatteer de uitvoer als een percentage-klik op de "procent stijl” knop (Tabblad Start > Nummergroep).

1

=B6/B7

Gedecodeerde versie:

1	={Totaal}/{Doel}

Label: De kers op de taart. Deze combineert de numerieke en procentuele waarden die de voortgang naar uw doel vertegenwoordigen en maakt ze netjes op. Om het voor elkaar te krijgen, gaan we naar de TEKST-functie (Excel-nieuwelingen, zet je schrap voor impact):

1	=TEKST(B6, "$#0,00") & " (" & TEKST(E5, "##0%") & ")"

En de gedecodeerde versie:

1	=TEXT({Totaal}, "{Format the value as valuta}") & " (" & TEXT({Progress}, "{Format the value as percentages}") & ")"

Als dat ontmoedigend lijkt, bekijk dan het artikel dat hierboven is gelinkt over de functie.

Zo ziet het er in de praktijk uit:

Oef, het ergste ligt achter ons. Om het allemaal samen te vatten, kijk nog eens naar de formules:

Kaartinstelling	Formule
Formule voor totale omzet	=ALS(B6<=B7, B6, B7)
overtroffen	=ALS(B7<=B6, B6-B7, 0)
Voortgang	=B6/B7
Label	=TEKST(B6, “$#0.00″) & ” (“” & TEKST(E5, “##0%”) & “)”

Eenmaal daar, ga naar de volgende stap.

Stap 2: Maak een gestapeld kolomdiagram.

1. Markeer het celbereik E2:E4 in de Waarde
2. Selecteer de Invoegen
3. Klik op de "Kolom- of staafdiagram invoegen" icoon.
4. Kiezen "Gestapelde kolom.”

Stap 3: Stapel de datareeksen op elkaar.

Zodra de grafiek verschijnt, herschikt u de gegevensreeks:

1. Klik op de grafiek.
2. Schakel over naar de Ontwerp
3. Kiezen "Wissel van rij/kolom.”

Stap 4: Wijzig de kleuren van de gegevensmarkeringen.

Om de grafiek om te zetten in iets dat op een thermometer lijkt, begint u met het veranderen van de kleur van de gegevensmarkeringen. Maak eerst de gegevensmarkering voor Serie 1 "Doelopbrengst" (E2) transparant en zet er randlijnen omheen.

Klik met de rechtermuisknop op de gegevensmarkering die de "Doelopbrengst" vertegenwoordigt (E2-het onderste gedeelte van de grafiek) en selecteer “Gegevensreeks opmaken.”

Doe het volgende in het taakvenster dat verschijnt:

1. Navigeer naar de Vulling & lijn
2. Onder "Vullen”, selecteer “Geen vulling.”
3. Onder "Grens," Kiezen "Ononderbroken lijn.”
4. Onder "Grens," Klik op de Opvulkleur pictogram om het kleurenpalet te openen en selecteer rood.
5. Stel de Breedte waarde aan 2 punten.

Kleur de overige datamarkers als volgt in:

1. Voor Series 2 "Totale omzetformule" (E3):
   1. Selecteer de gegevensmarkering (E3) met de Gegevensreeks opmaken taakvenster nog steeds open.
   2. Onder "Vullen," Kiezen "Vaste vulling.”
   3. Klik op de Opvulkleur icoon en selecteer rood.
2. Voor Series 3 "Overpresteerd" (E 4):
   1. Selecteer de gegevensmarkering (E 4) met de Gegevensreeks opmaken taakvenster nog steeds open.
   2. Onder "Vullen," Kiezen "Vaste vulling.”
   3. Klik op de Opvulkleur icoon en selecteer licht groen.

Stap #5: Voeg het gegevenslabel toe aan de grafiek.

Nu is het tijd om het mooie gegevenslabel (E6) dat kostte ons zoveel bloed, zweet en tranen om samen te stellen.

Klik met de rechtermuisknop op Series 2 "Formule totale inkomsten" (die met een rode vulling) en kies “Gegevenslabels toevoegen.”

Stap #6: Wijzig de waarde van het gegevenslabel.

Koppel het label aan de waarde in de tweede tabel.

1. Dubbelklik op het etiket.
2. Type “=” in de Formule balk.
3. Selecteer de labelwaarde (E6).

Nu moeten we het een beetje opfleuren. Maak het label vetgedrukt, kleur het in wit en wijzig de lettergrootte om het element esthetisch te verbeteren (Tabblad Start > Lettertypegroep).

Stap #7: Verplaats het gegevenslabel.

Nadat u het label hebt ingesteld, duwt u het naar het bovenste uiteinde van de gerelateerde gegevensmarkering. Klik met de rechtermuisknop op het gegevenslabel en kies "Gegevenslabels opmaken.”

Dan, in de Labelopties tabblad, onder Labelpositie, Kiezen "Inside End.”

Stap #8: Verplaats serie 1 "Doelopbrengst" naar de secundaire as.

Om de grafiek in de vorm van een thermometer te vormen, moet je: Serie 1 “Doelopbrengst” (E2) in de juiste positie.

1. Klik met de rechtermuisknop op Serie 1 “Doelopbrengst” (de transparante helemaal onderaan).
2. Kies “Gegevensreeks opmaken.”

In het taakvenster dat verschijnt, onder Plot-serie aan, Kiezen "Secundaire as.”

Stap #9: Verwijder de secundaire as.

De secundaire as deed zijn werk, dus die hebben we niet meer nodig.

Klik met de rechtermuisknop op de secundaire as (de kolom met getallen aan de rechterkant) en selecteer "Verwijderen.”

Stap #10: Pas de schaalbereiken van de primaire as aan.

Zoals je misschien hebt gemerkt, ziet de schaal van de primaire as er een beetje uit. Laten we dat veranderen.

Klik met de rechtermuisknop op de schaal van de primaire as en kies "Formaat as.”

Stel de Minimale grenzen waarde aan -10000. Waarom? Als u dit doet, maakt u hieronder wat ruimte vrij voor de thermometerlamp.

Deze waarde verschilt op basis van uw grafiekgegevens. Een goede vuistregel is om: gebruik je doelnummer en deel het door twee.

Stel dat uw omzetdoel € 100.000 is. Dan is jouw Minimale grenzen waarde zou zijn -50000.

Stap #11: Wijzig de getalnotatie van de schaal van de primaire as en voeg maatstreepjes toe.

Die negatieve getallen helpen ons hier niet verder. Gelukkig kunnen we ze gemakkelijk verwijderen met behulp van de kracht van aangepaste getalnotaties.

Met de Formaat as taakvenster nog steeds open, onder Asopties, navigeer naar de Cijfers sectie en doe het volgende:

1. Voor "Categorie,” selecteer “”
2. Typ "$#,##0.00;;$0" in de Formaatcode veld en klik op Toevoegen.

Ook onder Vinkjes, Kiezen "Binnenkant" van de "hoofdtype" drop-down menu.

Schakel nu over naar de Vulling & lijn tabblad. Verander de kleur van de maatstreepjes in zwart en stel de Breedte waarde aan 1,5 pt.

Stap #12: Verwijder de grafiektitel, rasterlijnen en horizontale as.

Verwijder de grafiekelementen die geen praktische waarde hebben: de grafiektitel, rasterlijnen en horizontale as. Klik met de rechtermuisknop op elk element en selecteer "Verwijderen.”

Stap #13: Wijzig de spleetbreedte van de gegevensreeks op de secundaire as.

Terwijl u de laatste hand legt aan uw kaart, maakt u de thermometerbuis kleiner. Begin met de reeks die op de secundaire as is geplaatst (Series 1 "Doelinkomsten”).

Klik met de rechtermuisknop op Serie 1 "Doelopbrengst" (E2), open de Gegevensreeks opmaken taakvenster en stel de Breedte spleet waarde aan 250%. Verhoog het percentage als u het meer wilt afslanken.

Stap #14: Wijzig de spleetbreedte van de gegevensreeks op de primaire as.

Klik daarna nogmaals op de rand van de rode buis om Serie 2 te selecteren.Formule voor totale omzet” (E3), geplaatst op de primaire as. Stel hetzelfde in Breedte spleet waarde (250%).

Stap #15: Voeg de thermometerbol toe aan de grafiek.

Maak als laatste aanpassing de thermometerbol en voeg deze toe aan de grafiek.

1. Schakel over naar de Invoegen
2. Kies de "Vormen" knop.
3. Kies “Ovaal.”

Houd ingedrukt Verschuiving tijdens het tekenen om een ​​perfecte cirkel te creëren. Klik vervolgens met de rechtermuisknop op de cirkel en wijzig in het menu dat verschijnt de Vormoverzicht en Vormvulling kleuren naar rood.

Nog een laatste ding. Kopieer de cirkel door deze te selecteren en op te drukken Ctrl + C. Selecteer vervolgens de kaart en druk op Ctrl + V om de cirkel in de thermometerkaart te plakken. Pas ten slotte de grootte van de lamp aan en pas deze aan zodat deze in de afbeelding past.

Dus daar heb je het. U bent nu klaar om adembenemende thermometergrafieken in Excel te maken!