Een paneeldiagram maken in Excel

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

Een paneeldiagram (ook wel een trellis-diagram of een klein veelvoud genoemd) is een reeks vergelijkbare kleinere diagrammen die naast elkaar worden vergeleken en gescheiden door scheidingstekens. Aangezien deze minigrafieken dezelfde assen delen en op dezelfde schaal worden gemeten, consolideert een paneelgrafiek ze in feite allemaal op één plaats.

Met de grafiek kunt u niet alleen netjes meer informatie weergeven, het helpt u ook om snel de relatie tussen meerdere datasets tegelijk te vergelijken of te analyseren, terwijl u veel dashboardruimte bespaart.

Bekijk als voorbeeld het onderstaande paneldiagram, dat vier afzonderlijke lijndiagrammen combineert die de jaaromzet van Orange Ltd en Banana Ltd franchisenemers in vier staten in de afgelopen zes jaar vergelijken.

Zoals u kunt zien, illustreert het paneeldiagram een ​​bodemloze bron van nuttige gegevens, in tegenstelling tot de wirwar van lijnen die u zou krijgen als u gewoon voor de ingebouwde tegenhanger zou kiezen.

Helaas wordt dit grafiektype niet ondersteund in Excel, wat betekent dat u het zelf handmatig zult moeten bouwen. Maar voordat we beginnen, bekijk de Chart Creator Add-in, een veelzijdige tool voor het maken van geavanceerde Excel-diagrammen en grafieken in slechts een paar klikken.

In deze zelfstudie leert u hoe u vanaf het begin een aanpasbaar paneeldiagram in Excel kunt plotten.

Beginnen

Om de te volgen stappen te illustreren, moeten we beginnen met enkele gegevens. Zoals je misschien al geraden hebt, gaan we de historische staat van dienst vergelijken van de franchisenemers van twee bedrijven, Orange Ltd en Banana Ltd, in vier verschillende staten: Iowa, Minnesota, Texas en Utah.

Dat gezegd hebbende, overweeg dan de volgende tabel:

Laten we elke kolom meer in detail bespreken.

• Staat - Deze kolom vertegenwoordigt de categorieën waarmee de grafiek wordt opgesplitst in kleinere grafieken (panelen). In ons geval illustreert elk minilijndiagram de prestatiedynamiek voor elk van de staten.
• Jaar - Deze kolom bepaalt de schaal van de horizontale as. De waarden en opmaak moeten identiek zijn in alle panelen.
• Orange Ltd en Banana Ltd - Dit zijn uw werkelijke waarden. We gaan slechts twee sets gegevens gebruiken voor illustratiedoeleinden, maar met de methode die in deze zelfstudie wordt getoond, is de lucht de limiet.

Uiteindelijk willen we dat onze gegevens er als volgt uitzien:

Technisch gezien kunt u de gegevens handmatig manipuleren (als dat zo is, ga dan naar Stap #5). Deze aanpak kan de voorkeur hebben als uw gegevens eenvoudig zijn. Anders laten we u zien hoe u een draaitabel gebruikt om de gegevens in het benodigde formaat te manipuleren.

Dus laten we er meteen in duiken.

Stap 1: voeg de scheidingstekens toe.

Voordat u een paneeldiagram kunt maken, moet u uw gegevens op de juiste manier ordenen.

Ten eerste, rechts van uw werkelijke gegevens (kolom E), stel een hulpkolom in met de naam "scheidingsteken.Het doel van deze kolom is om de gegevens op te splitsen in twee afwisselende categorieën, uitgedrukt met de waarden 1 en 2, om de basis te leggen voor de toekomstige draaitabel.

Typ "1” in elke corresponderende cel van kolom scheidingsteken die tot de eerste categorie behoren, Iowa (E2:E7). Typ vervolgens "2” in alle respectievelijke cellen die in de tweede categorie vallen, Minnesota (E8:E13).

Vul op dezelfde manier de resterende lege cellen in de kolom in, waarbij u tussen de twee scheidingstekens wisselt terwijl u van staat naar staat gaat.

Stap 2: Voeg een draaitabel toe.

Zodra je klaar bent Stap 1, maak een draaitabel.

1. Markeer een willekeurige cel binnen het gegevenssetbereik (A1:E25).
2. Ga naar de Invoegen tabblad.
3. Kiezen "Draaitabel.”

Wanneer de Draaitabel maken dialoogvenster verschijnt, selecteer "Bestaand werkblad,” markeer een lege cel in de buurt van uw werkelijke gegevens (G1) en klik op "OKE.”

Stap 3: Ontwerp de lay-out van de draaitabel.

Onmiddellijk nadat uw draaitabel is gemaakt, wordt de Draaitabelvelden taakvenster verschijnt. Verschuif in dit taakvenster de items in de lijst met velden in de volgende volgorde - de volgorde is belangrijk - om de lay-out van uw nieuwe draaitabel te wijzigen:

1. Beweging "Staat" en "Jaar" tot "rijen.”
2. Beweging "scheidingsteken" tot "Kolommen.”
3. Beweging "Orange Ltd" en "Banana Ltd" tot "Waarden.”

Op dit punt zou uw draaitabel er ongeveer zo uit moeten zien:

Wijzig nu het lay-outtype en verwijder de overbodige tabelelementen.

1. Selecteer een cel in de draaitabel (G4:L26).
2. Navigeer naar de Ontwerp tabblad.
3. Klik "Rapportindeling.”
4. Kiezen "Toon in tabelvorm.”

Nog steeds in de Ontwerp tabblad, klik op de "Eindtotalen” icoon en kies “Uit voor rijen en kolommen” in het vervolgkeuzemenu.

Klik vervolgens op "Subtotalen” en kies “Subtotalen niet weergeven.”

Nadat u alle stappen hebt gevolgd, moet uw draaitabel op deze manier worden opgemaakt:

Stap 4: extraheer de gegevens uit de draaitabel.

Aangezien u geen draaigrafiek wilt maken (nee, dat doet u niet), is het tijd om de gegevens van de draaitabel te scheiden. Kopieer alle waarden uit uw draaitabel (G4:L27) in de lege cellen ernaast. (Een lege kolom tussen de gegevenssets helpt verwarring te voorkomen.)

De kopregel (P3:S3) van uw nieuw gemaakte tabel is verantwoordelijk voor het weergeven van diagramlegenda-items, dus laten we ze ook snel toevoegen.

Aangezien de draaitabel de gegevens effectief in twee quasi-lijndiagrammen splitst, zou uw koprij als volgt moeten gaan:

Stap #5: Maak een lijndiagram.

Teken ten slotte een regelmatig lijndiagram en kijk wat er gebeurt.

1. Markeer de tabel met de gegevens die uit de draaitabel zijn geëxtraheerd (N3:S27).
2. Ga naar de Invoegen tabblad.
3. Klik op de "Lijn- of vlakdiagram invoegen" knop.
4. Kiezen "Lijn.”

Stap #6: Maak de lijnkleuren consistent.

Zoals u kunt zien, heeft Excel een lijndiagram gemaakt met vier verschillende gegevensreeksen. Verenig die gegevens door voor elk bedrijf dezelfde kleuren te gebruiken.

Klik met de rechtermuisknop op een gegevensreeks en kies "Gegevensreeks opmaken.”

In de Gegevensreeks opmaken taakvenster, doet u het volgende:

1. Schakel over naar de Vulling & lijn tabblad.
2. Onder "Lijn," selecteer "Ononderbroken lijn.”
3. Klik op de "Omtrekkleur” icoon en kies je kleur uit het palet. (Herhaal voor elke regel.)
4. Controleer nogmaals de legenda van de grafiek om er zeker van te zijn dat u de kleuren op de juiste manier hebt afgestemd.

Stap #7: Maak de scheidslijnen.

De taak die voor u ligt lijkt misschien niets bijzonders, maar het is niet zo eenvoudig als het klinkt. Ja, je zou gewoon de lijnen kunnen tekenen met behulp van ingebouwde vormen, maar ze zouden uiteindelijk misplaatst raken elke keer dat je het formaat van de grafiek aanpast.

We gaan dus in plaats daarvan verticale foutbalken gebruiken, die tussen elk paneel worden geplaatst, precies waar de Y-as de categorie-as kruist.

Om het probleem te omzeilen, moet u echter door een paar hoepels springen. Laten we eerst de hulpkaartgegevens maken:

1. Zet een aparte dummytafel op precies dezelfde manier zoals je het op de onderstaande schermafbeelding ziet. cel verlaten U4 leeg om dingen te laten werken en typ "Verdelers" naar binnen V4.
2. Typ "0" naar binnen V5 en kopieer het naar beneden. Het aantal rijen met waarden in de tabel geeft aan hoeveel foutbalken er uiteindelijk zullen worden gemaakt. In dit geval hebben we drie foutbalken nodig, dus er zullen drie cellen zijn met "0" in hen.
3. Typ "=AANTAL (O4:O9)+0.5" naar binnen U5.

De formule berekent het aantal x-asschaalwaarden dat voor één paneel wordt gebruikt en berekent het middelpunt om de lijnen op de X-as precies daar te plaatsen waar de twee assen elkaar ontmoeten.

Typ nu "=U5+COUNTA(O4:O9)” naar binnen U6 en kopieer het naar U7.

Nadat u de hulptabel hebt ingesteld, voegt u die gegevens in uw paneeldiagram in.

1. Markeer alle waarden in de helpertabel, behalve de koprij (U4:V7).
2. Selecteer het grafiekgebied.
3. Ga naar de Huis tabblad.
4. Klik "Plakken.”
5. Kiezen "Plakken speciaal.”

Volg een paar eenvoudige stappen in het dialoogvenster dat verschijnt:

1. Onder "Voeg cellen toe als," selecteer "Nieuwe series.”
2. Onder "Waarden (Y) in," Kiezen "Kolommen.”
3. Controleer de "Serienamen op de eerste rij" en "Categorieën (X-labels) in eerste kolom” dozen.
4. Klik "OKE.”

Stap #8: Wijzig het grafiektype van de dummy-serie.

Klik met de rechtermuisknop op de nieuw toegevoegde gegevensreeks (Serie "Verdelers") en kies “Grafiektype serie wijzigen.”

Voor Serie "Verdelers", verandering "Grafiektype" tot "Verspreid met rechte lijnen.”

Stap #9: Wijzig de secundaire assen.

Verwijder eerst de secundaire horizontale as. Klik met de rechtermuisknop op de cijfers bovenaan de grafiek en kies "Verwijderen.”

Klik vervolgens met de rechtermuisknop op de secundaire verticale as aan de rechterkant van de grafiek en selecteer "Formaat as.”

In de Formaat as taakvenster, in steen gebeiteld, de asschaalbereiken:

1. Schakel over naar de Asopties tabblad.
2. Verander de Minimale grenzen waarde aan “0.”
3. Stel de Maximale grenzen waarde aan “1.”

Scrol in hetzelfde tabblad omlaag naar de Etiketten sectie en verander “Labelpositie" tot "Geen" om de asschaal te verbergen - als u deze eenvoudig verwijdert, zal dat de grafiek verpesten.

Stap #10: voeg foutbalken toe.

De basis is nu gelegd, wat betekent dat je eindelijk de foutbalken kunt toevoegen.

1. Selecteer Serie "Verdelers".
2. Klik op de Grafiekelementen icoon.
3. Klik op de pijl naast "Foutbalken."
4. Kiezen "Meer opties.”

In de Foutbalken opmaken taakvenster, wijzigt u de scheidingstekens.

1. Schakel over naar de Opties voor foutbalk tabblad.
2. Onder "Richting," Kiezen "Plus.”
3. Onder "Eindstijl," selecteer "Geen pet.”
4. Onder "Foutbedrag," verandering "Vaste waarde" tot "1.”

Stap # 11: Verberg de hulpkaartelementen.

Als laatste stap verwijdert u de onderliggende secundaire spreidingsplot, horizontale foutbalken en dubbele legenda-items.

Laten we eerst de dummy-serie behandelen. Selecteer Serie "Verdelers", navigeer naar de Gegevensreeks opmaken taakvenster, en in de Serie-opties tabblad, kies "Geen lijn.”

Verwijder nu de horizontale foutbalken en elk legenda-item, bewaar voor "Orange Ltd" en "Banana Ltd” door elk item te selecteren, met de rechtermuisknop te klikken en te kiezen voor “Verwijderen.” Aan het einde van deze stap zou uw paneeldiagram er als volgt uit moeten zien:

Wijzig de titel van de grafiek en u hebt uw verbluffende paneelgrafiek klaar voor gebruik!