Grafieken zonder waarden in cellen
- 07/04/2012
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Zij die al langer deze Excel pagina′s aandachtig in het oog houden, hebben vroeger reeds een grafiek van wereldrecords en de gemiddelde snelheid gezien: dubbele grafiekas. Nu zou ik echter een interessant concept willen toelichten: we kunnen in Excel grafieken maken zonder dat de waarden in cellen staan.
Bekijk eens de bovenstaande schermafbeelding. Op zich niets mis mee, een zeer simpele grafiek met een aangepaste getalweergave voor labels en assen. We zien er een vergelijking van de snelheid voor 5 wereldrecords atletiek bij de heren. De gemiddelde snelheid wordt uitgezet t.o.v. de afstand die gelopen moet worden. De invoer die ik deed, is ENKEL de afstand (cellen A1:A6) en het wereldrecord (cellen B1:B6). De gemiddelde snelheid zet ik nergens in cellen.
Waar zijn de getallen voor de grafiek?
Hoe kunnen we dan de gemiddelde snelheid in de grafiek zetten? Ik maakte een benoemd bereik met een formule. Benoemde bereiken zijn zeer handige instrumenten voor elke Excel gebruiker en (zeker) ontwikkelaar. Of het nu gaat om vaste celbereiken, formules, constanten, … benoemde bereiken zijn onmisbaar. Druk in het te downloaden bestand op Ctrl + F3 en je ziet (of ga via het menu):
(de formule MID is dezelfde als de functie DEEL in een Nederlandse Excel versie)
Matrixformule
De formule verwijst naar het bereik data!B2:B6 (3 keer) en ook naar het bereik data!A2:A6. We gaan daar de
gelopen tijd omrekenen naar de snelheid. Gezien deze argumenten mag het niet verbazen dat deze formule
een matrix-formule is. We geven ze echter niet in via
Ctrl + Shift + Enter, maar gewoon
Enter. Kort gezegd is de formule:
Snelheid (km/uur) = 360/(6000 * #minuten + 100 * #seconden + #honderdsten van seconden)
* afstand (meter)
Hoe weten we of bovenstaande formule juist is? Dan zetten we in werkbladcellen gewoon enkele beschrijvende statistieken, zoals:
Overigens is dit snel gedaan als je een truukje gebruikt. Typ:
=COUNT( of =AANTAL(
en druk op F3 waar je de naam record_mannen uit het lijstje
kiest.
Benoemde bereiken in een grafiek
Natuurlijk moeten we in de grafiek nog aangeven dat we de waarden uit het benoemd bereik / formule in de grafiek willen zetten op de verticale as:
In de grafiek gaan we voor de waarden van de gegevensreeks aangeven:
=grafiekenviabenoemdbereik.xls!record_mannen
Dit is jammer genoeg niet helemaal zichtbaar want ik kon het scherm niet vergroten.
Let goed op de syntax hierboven, want daartegen zondigen laat niet toe om het scherm te verlaten.
Vergeet niet om ‘ ’ te zetten mocht de bestandsnaam een spatie bevatten.
Wordt het bestand hernoemd, dan zal Excel automatisch de gegevensreeksen in de grafiek aanpassen met de nieuwe bestandsnaam.
De naam van het werkblad kan in bepaalde gevallen ook gebruikt worden in bovenstaand scherm. Bemerk dat in bijgevoegde schermafdruk de functie OFFSET (VERSCHUIVING) werd gebruikt voor het bereik B2:B6. De scope van dit bereik is locaal: enkel het blad data. Dit kunnen we dan ook zo aanduiden voor het bereik van de gegevensreeks:
Een nieuwe wereld aan mogelijkheden opent zich…
Zo, nu we dit weten opent zich een hele waaier aan mogelijkheden: bvb. een spreidingsgrafiek (XY scatter) met 10000 punten, waarbij de punten in een benoemd bereik / formule zitten en NIET als 10000 X en 10000 Y coöordinaten in een Excel werkblad. Dit werkt echt… test het gerust uit. Als ik dit maak in een .xlsx bestand dan is dat bestand 101 KB groot. Hetzelfde bestand en grafiek maar dezelfde formule in de cellen A1:B10000 en het bestand is 278 KB groot. De formules staan prominent in beeld terwijl dit niet echt nodig is.
