Om visueel te zien wanneer de lijn op de grafiek boven of onder het gemiddelde van de reeks ligt, kunnen we eenvoudig een gemiddelde lijn in de grafiek invoegen. Het zal er ongeveer zo uitzien.
Is het aantrekkelijk? Of deze?
Ik kan je antwoord wel raden.
Dus hoe maken we deze grafiek die het gebied markeert wanneer de lijn boven of onder het gemiddelde ligt? Laten we het stap voor stap leren.
Markeer gebied wanneer lijn boven en onder het gemiddelde ligt
De beste manier om te leren is door het voorbeeld te leren. Dus laten we beginnen met een voorbeeld.
Hier heb ik enkele gegevens die de totale verkopen van individuele verkopers vertellen. Ik wil deze gegevens visualiseren in een grafiek.
Stap 1: Voeg drie hulpkolommen toe
Om zo'n grafiek te maken, hebben we drie hulpkolommen nodig. Eerste hulpkolommen hebben gemiddelde gegevens. De tweede helperkolom zal alle punten bevatten waar de verkoop groter is dan het gemiddelde. En in de derde kolom staan de punten waar de verkopen lager zijn dan het gemiddelde.
Dus de formule in de gemiddelde kolom wordt:
| = GEMIDDELDE ($ B $ 2: $ B $ 7) |
Schrijf deze formule in de eerste cel van de gemiddelde kolom en sleep deze naar beneden. Zorg ervoor dat alle cellen dezelfde waarden hebben. We hebben absolute verwijzingen gebruikt, zodat verwijzingen niet veranderen wanneer de formule naar beneden wordt gekopieerd.
In de volgende kolom hebben we alle UP-waarden nodig. Het zal een positief verschil zijn tussen gemiddelde en verkoopgegevens. Met andere woorden, we hoeven alleen de gemiddelde gegevens van de verkoop af te trekken als de verkoop groter is dan het gemiddelde. De formule in D2 wordt:
| =ALS(B2>C2,B2-C2,"") |
Sleep het naar beneden.
In de volgende kolom hebben we alle Down-waarden nodig. Het zal een negatief verschil zijn tussen de gemiddelde en verkoopgegevens. Met andere woorden, we hoeven alleen de gemiddelde gegevens van de verkoop af te trekken als de verkoop lager is dan het gemiddelde. De formule voor E2 wordt:
| =ALS(B2<> |
Sleep het naar beneden.
Stap 2: Selecteer Verkoopadviseur en Verkoop en voeg een lijndiagram in: In ons voorbeeld selecteren we het bereik A1:C7. Ga naar Invoegen--> Grafieken--> Lijn en gebied --> Lijn met markeringen. We hebben een grafiek die er als volgt uit zal zien.
Stap 3: Voeg nog een keer de gemiddelde reeks toe
Klik met de rechtermuisknop op de grafiek en klik op de optie Gegevens selecteren. Klik hier op de knop Toevoegen in het gedeelte Legenda en vermeldingen. Selecteer het gemiddelde bereik C2:C7 als waarden van de reeks. Het voegt een regel in op de oorspronkelijke gemiddelde regel. We hebben het nodig in de opmaak. Het is belangrijk.
Stap 4: Op- en neerwaartse reeksen toevoegen aan de gegevens
Terwijl je de gemiddelde reeks opnieuw hebt toegevoegd, voeg je op dezelfde manier de reeksen Omhoog en Omlaag één voor één toe aan de reeks. Ten slotte heb je een grafiek die er zo uitziet. dit lijkt helemaal niet op wat we willen dat het is, toch? Wacht, het zal evolueren.
Stap 5: Wijzig de tweede gemiddelde reeks, de opwaartse en neerwaartse reeks in een gestapeld vlakdiagram
Klik met de rechtermuisknop op een reeks in de grafiek en klik op de optie Grafiektype gegevensreeks wijzigen. Dit zal de grafiek veel veranderen en het zal de vorm aannemen die we willen dat dit Excel-lijndiagram is.
Stap 6: Selecteer Geen vulling voor gestapeld diagram met gemiddelde oppervlakte:
Klik met de rechtermuisknop op het gebied van het gemiddelde onderdeel en klik op de opmaakgegevensreeks. Ga in lijn met de vuloptie naar vullen en selecteer geen vuloptie.
Het diagram ziet er nu precies uit zoals we wilden dat het eruit zou zien. We hoeven alleen een klein beetje opmaak te doen om het geschikt te maken voor ons dashboard/presentatiethema.
Stap 7: Formatteer de grafiek zodat deze bij het thema past.
Verwijder alle minder vereiste dingen uit de grafiek. Ik heb de gemiddelde legenda uit de grafiek verwijderd omdat ik deze niet nodig heb. Ik heb de rasterlijnen verwijderd omdat ik wil dat mijn grafiek er schoner uitziet. Ik heb de kleur van het bovenste gedeelte sindsdien in groen veranderd en het onderste gedeelte in rood. Ik noemde de grafiektitel "Verkoop tegen gemiddelde".
Eindelijk heb ik deze grafiek.
Opmerking:Deze grafiek kan worden gebruikt als een doel- versus prestatiegrafiek. Verander gewoon de waarden van de gemiddelde reeks in doelen en boem, je hebt de creatieve doel versus prestatiegrafiek in Excel.
Download het onderstaande sjabloonbestand:
Dus ja jongens, dit is hoe je creatief een Excel-lijndiagram kunt gebruiken om gebieden te markeren wanneer een lijn onder het gemiddelde of boven het gemiddelde gaat. Ik hoop dat het nuttig voor je is. Als je twijfels hebt over deze grafiek over een ander Excel/VBA-gerelateerd onderwerp, vraag het dan in de opmerkingen hieronder.
Schaduw toevoegen aan krommelijn in Excel-diagram | In Google Analytics en andere online tools heb je vast wel eens lijndiagrammen gezien. In deze grafieken ziet u het gearceerde gebied onder de curven. Deze schaduw ziet er cool uit, niet? De vraag is, kunnen we dit in Excel doen? Hoe kunnen we het gebied onder de curvegrafiek verduisteren?
Hoe maximale en minimale gegevenspunten in Excel-diagram te markeren | Khet is in bijna alle soorten rapporten essentieel om de hoogste en laagste waarde in een dataset te bepalen. Maar in lijn- en kolom-Excel-diagrammen wordt het vaak moeilijk om te bepalen welke waarde het hoogst is en welke waarde het laagst.
Een dynamische verticale markeringslijn invoegen in Excel-lijndiagram |We kunnen handmatig een verticale lijn op de kaart tekenen, maar dat is gewoon niet slim. We willen graag dynamische verticale lijnen toevoegen om een bepaald gegevenspunt te markeren, bijvoorbeeld de maximale waarde.
Mijlpaalgrafiek maken in Excel |Een mijlpaalgrafiek toont de datum of tijd waarop een mijlpaal is bereikt op een grafische manier. Deze grafiek moet gemakkelijk te lezen, verklarend en visueel aantrekkelijk zijn.
Watervalkaart |Deze kaart wordt ook wel de vliegende stenenkaart of de brugkaart genoemd. Het wordt gebruikt om te begrijpen hoe een initiële waarde wordt beïnvloed door een reeks tussenliggende positieve of negatieve waarden.
Excel Sparklines: de kleine grafieken in cel | De Sparklines zijn de kleine diagrammen die zich in een enkele cel bevinden. Sparklines worden gebruikt om trends, verbeteringen en winst-verlies over de periode te tonen. De sparklines zijn grafieken, maar ze hebben beperkte functionaliteiten in vergelijking met gewone grafieken.
Creatieve kolomdiagram met totalen | Het is niet eenvoudig om het totaal van de geclusterde kolom in het diagram op te nemen en te vergelijken met een andere groep kolommen in het diagram. Hier heb ik uitgelegd hoe u op een slimme manier totalen kunt opnemen in het geclusterde kolomdiagram.
4 Creatief doel versus prestatiegrafieken in Excel |Target vs Achievement-diagrammen zijn een zeer basisvereiste van elk Excel-dashboard. In maandelijkse en jaarlijkse rapporten zijn Target vs Achievement-grafieken de eerste grafieken die het management ook verwijst en een goede target vs Achievement-grafiek zal zeker de aandacht van het management trekken
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de VERT.ZOEKEN-functie in Excel te gebruiken? | Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
AANTAL.ALS gebruiken in Excel 2016 | Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om een specifieke waarde te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.