Deze zelfstudie laat zien hoe u Excel-diagrammen maakt met voorwaardelijke opmaak in alle versies van Excel: 2007, 2010, 2013, 2016 en 2022.
Voorwaardelijke opmaak is de praktijk van het toewijzen van aangepaste opmaak aan Excel-cellen (kleur, lettertype, enz.) op basis van de opgegeven criteria (voorwaarden). De functie helpt bij het analyseren van gegevens, het vinden van statistisch significante waarden en het identificeren van patronen binnen een bepaalde dataset.
Maar helaas biedt Excel geen ingebouwde tools voor het toepassen van voorwaardelijke opmaak op Excel-diagrammen.
Met een beetje spreadsheetmagie is echter geen enkel probleem onoverkomelijk. In deze zelfstudie leert u hoe u een dynamische grafiek maakt met voorwaardelijke opmaak waarbij de geplotte gegevenspunten automatisch worden opgemaakt op basis van hun respectieve werkelijke waarden.
Kijk bijvoorbeeld eens naar dit kolomdiagram, waarin de prestaties van een verkoopmanager over een periode van acht maanden worden uitgesplitst. Merk op hoe de kleur van de kolom automatisch wordt aangepast, afhankelijk van de onderliggende waarde:
Beginnen
Overweeg deze voorbeeldgegevensset met verkoopprestatiegegevens:
Verplaats voordat we beginnen je dataset zodat je drie lege rijen boven de tabel hebt met je onbewerkte gegevens. De rijen worden gebruikt voor het instellen van voorwaardelijke opmaakregels.
Stap 1: Bereid grafiekgegevens voor.
Om dingen te laten werken, moet u wat extra kaartgegevens aan de vergelijking toevoegen. Bekijk voor uw gemak eens hoe uw grafiekgegevens er aan het einde van de voorbereidingsfase uit moeten zien:
Onze eerste stap is het definiëren van de grenswaarden (voorwaarden) die de werkelijke waarden in meerdere categorieën moeten opsplitsen, zodat ze als afzonderlijke gegevensreeksen in kaart kunnen worden gebracht.
Uit de bovenstaande schermafbeelding kunt u bijvoorbeeld zien dat de verkoopcijfers voor een bepaalde maand binnen het bereik van $ 90.000 tot $ 150.000 moeten vallen om als bevredigend te worden beschouwd.
Gebruik de drie lege rijen boven de dataset om de regels te maken:
- Rij 1 (label): Gebruik deze cellen voor het labelen van de opmaakregels.
- Rij 2 (min. waarde): De waarden in deze rij definiëren de ondergrens voor elke voorwaarde.
- Rij 3 (Max. Waarde): Evenzo bepalen deze waarden de bovengrenzen.
Met deze methode kunt u zoveel voorwaardelijke opmaakregels maken als u wilt. Zorg er wel voor dat de waardebereiken elkaar niet overlappen.
Terwijl u steeds verder gaat met het opzetten van uw grafiek, ontwerpt u aangepaste legenda-items voor de grafiek die context voor de grafiek zullen bieden. Voer deze formule in in C4 en kopieer het naar D4 door de vulgreep te slepen:
| 1 | =C1&": van "&TEXT(C2, "$#,##")&" tot "&TEXT(C3, "$#,##") |
De formule stelt een netjes uitziend dynamisch label samen op basis van de eerder ingestelde voorwaardelijke opmaakregels. De TEKST-functie maakt de waarden op als valuta. Maar als uw gegevenstype verschilt, past u in plaats daarvan deze formule toe:
| 1 | =C1&": van "&TEKST(C2, "#,##")&" tot "&TEKST(C3, "#,##") |
Of deze als je met percentages werkt:
| 1 | =C1&": van "&TEKST(C2, "#%")&" naar "&TEKST(C3, "#%") |
Zoek ten slotte de grafiekwaarden. Typ de volgende formule in C5, kopieer het naar C12 en naar de overeenkomende cellen in kolom D:
| 1 | =ALS(EN(C$2<$B5,$B5<=C$3),$B5,NA()) |
De formule vergelijkt een gegeven werkelijke waarde in kolom B met behulp van de ALS- en AND-functies tegen de opgegeven grenswaarden. Als de waarde binnen het bereik valt, wordt deze naar deze kolom gekopieerd. Anders retourneert de formule de fout #N/B om te voorkomen dat dezelfde waarde meerdere keren wordt geplot.
Stap 2: Stel een kolomdiagram in.
Nadat u alle grafiekgegevens hebt verzameld, stelt u als alternatief een eenvoudig kolomdiagram of een staafdiagram in:
- Markeer alle grafiekgegevens behalve de kolommen met de werkelijke waarden en de regels door de ingedrukt te houden Ctrl toets (A4:A12 en C4:D12).
- Ga naar de Invoegen tabblad.
- Selecteer "Kolom- of staafdiagram invoegen.”
- Kiezen "Geclusterde kolom/geclusterde staaf.”
Als gevolg hiervan krijgt u een diagram met gegevenspunten die zijn uitgezet op basis van de voorwaardelijke opmaakregels, precies zoals beloofd:
Stap 3: wijzig de waarden voor overlap en spleetbreedte.
Voordat u deze zelfstudie afsluit, moet u het probleem oplossen van de verkeerd geplaatste kolommen die worden veroorzaakt door de onzichtbare lege waarden (de #N/A-fouten) die in de grafiek zijn uitgezet.
Klik met de rechtermuisknop op een van de kolommen en kies "Gegevensreeks opmaken” uit het contextuele menu dat verschijnt.
Wijzig in het taakvenster de positie en breedte van de kolommen:
- Schakel over naar de Serie-opties tabblad.
- Wijziging "Serie-overlap" tot "100%.”
- Stel de Breedte spleet tot "60%.”
Stap 4: Pas het kleurenschema aan.
Voeg als laatste de laatste hand toe. Zonder de . te sluiten Gegevensreeks opmaken taakvenster, wijzig het kleurenschema van de grafiek:
- Ga naar de Vulling & lijn tabblad.
- Onder "Vullen," Kiezen "Vaste vulling.”
- Klik op de "Opvulkleur” icoon en kies groente uit het kleurenpalet.
Eenmaal daar, kleurt u de andere gegevensreeksen opnieuw, wijzigt u de grafiektitel en is uw volledig dynamische grafiek met voorwaardelijke opmaak klaar voor gebruik!
