Dubbel gestapelde staafgrafiek

8 oktober 2014 | onderwerpen: excel, grafiek

Een combinatie van twee staafgrafieken in Excel levert de mogelijkheid om twee variabele waarden in één staafgrafiek te vangen.

Op LinkedIn verscheen deze vraag:

“Ik wil graag data inzichtelijk maken d.m.v. een bandbreedte grafiek. Binnen dit bereik wil ik 2 waarden inzichtelijk te maken. Hoe krijg ik dit binnen Excel voor elkaar?”

excel grafiek bandbreedte

Grafiek 1 – Voorbeeld hoe de grafiek er ongeveer uit zou moeten komen te zien

De grafiek die ik naar aanleiding van deze vraag heb gemaakt is een staafgrafiek of barchart met twee gegevensreeksen. De eerste grafiek laat het verschil tussen de minimum en de maximum waarde zien. In dit geval 50 (minimum) en 150 (maximum). De tweede grafiek (de smalle strookjes) tonen de twee in te vullen waarden (gele cellen): de grenswaarde en het gemiddelde.

excel gestapelde staafgrafiek

Grafiek 2 – Gestapelde dynamische staafgrafiek

Stappenplan

Stap 1: Staafgrafiek met minimum & maximum

Zet deze gegevens in een gestapelde staafgrafiek (zie grafiek 2)

min verschil
bandbreedte 50 100

excel staafgrafiek gestapeld

Stap 1 – Gestapelde staafgrafiek met minimum en maximum

Stap 2: Voeg data voor variabele waarden toe

Kopieer en plak de tweede regel van onderstaande tabel toe aan de grafiek (zie grafiek 3). Kies voor ‘cellen toevoegen als nieuwe reeks‘.

 grens lijn  gem lijn
tbv grafiek: 84,5 1 35 1

De gegevens verwijzen in het excelbestand naar het in te voeren ‘gemiddelde’ en ‘grenswaarde’.

excel gestapelde staafgrafiek

Stap 2 – Toevoegen gegevens voor grenswaarde en gemiddelde

Stap 3: Secundaire as toevoegen en schaal aanpassen

Selecteer in de grafiek de zojuist toegevoegde waarden en kies in ‘Opties voor reeks’ voor de ‘secundaire as’. Herhaal dit vier keer, voor grens, lijn, gem en lijn. Zie figuur 3.

De als laatste toevoegde reeksen staan nu op de secundaire as en liggen over de eerste heen. Dit lossen we in de volgende stap op.

Verminder eerst nog de ‘breedte tussenruimte’ in ‘Opties voor reeks’ van de secundaire reeks. Dan zijn in de volgende stap de smalle lijnen beter zichtbaar.

excel gestapelde staafgrafiek

Stap 3 – Toevoegen secundaire horizontale as

Stap 4: Onzichtbaar maken

Verwijder de opvulling van eerste en derde reeks: de grenswaarde en gemiddelde. Deze delen worden dan onzichtbaar.

Pas de schaal van de beide horizontale assen (primair en secundair) aan. Minimum is 50 en maximum is 150.

Helaas heeft Excel niet de mogelijkheid in hier verwijzingen naar cellen in te voeren (bijvoorbeeld =$B$4), dus vullen we de waarden als harde waarden in.

excel gestapelde staafgrafiek

Stap 4 – Onzichtbaar maken hulpwaarden en aanpassen schaal horizontale assen

Stap 5: Finishing touch

Tot slot passen we nog het volgende aan in de grafiek:

  1. Verwijder de secundaire horizontale as
  2. Verwijder de primaire verticale as
  3. Zet de primaire eenheid van de primaire horizontale as op 100 (=verschil tussen minimum en maximum)
  4. Voeg bij de smalle lijnen voor gemiddelde en grenswaarde de labels toe

Omdat de grenswaarde kleiner maar ook groter kan zijn dan het gemiddelde moet in de celverwijzing naar de labels een formule staan die daar rekening mee houdt. Kijk daarvoor in het voorbeeldbestand.

excel gestapelde staafgrafiek

Stap 5 – Finishing touches


Download voorbeeldbestand Excel

Bandbreedte grafiek (1779 downloads)