E xce l - l e n t e M a n a g e m e n t To o l s
SPREKENDE GRAFIEKEN In deze nieuwe rubriek treft u interessante Excel-tips en -technieken aan die u in de praktijk
Cijfers gaan pas leven als u ze in grafieken zet. In dit artikel gaan we nog een stapje verder. U leert hoe u ‘sprekende’
kunt toepassen. Het betreft handi-
grafieken maakt die meer tot de verbeelding spreken. U
ge werkwijzen hoe u Excel op een
maakt dit soort grafieken gewoon in Excel.
andere, slimme wijze kunt inzetten. De voorbeelden zijn veelal uit de praktijk gehaald en aangepast voor dit artikel. Mocht u bijzonde-
De grafiek die hieronder is afgebeeld is een voorbeeld van zo'n 'sprekende grafiek'. U kunt deze grafiek gebruiken als u over een bepaalde periode een bepaalde grootheid wilt afbeelden waarbij u te maken heeft met een onderen bovengrens.
▲
re toepassingen behandeld willen zien, mail dan uw vragen en verzoeken naar
[email protected].
Deze grafiek toont de omzet per maand met een minimum- en maximumlijn. Zodra de omzet boven de maximumwaarde gaat, wordt deze voorzien van een lachend mannetje. Bevindt de omzet zich tussen de minimum- en maximumwaarde, dan ziet u een neutraal mannetje. Daalt de omzet onder de minimumwaarde, dan ziet u een huilend mannetje. De hier getoonde grafiek is op zich geen standaardgrafiek zoals u die in Excel aantreft, maar met een paar handige technieken laat ik zien hoe u dat wel voor elkaar kunt krijgen. Opzet van het werkblad Als u grafieken in Excel wilt maken, is het van belang dat u de cijfers en omschrijvingen goed in het werkblad positioneert, dat wil zeggen bij elkaar plaatst. Veelal worden de maanden verticaal geplaatst en in de kolom ernaast de waarden, in ons geval de omzetten per maand. Natuurlijk kunt u meerdere waarden in de grafiek tonen, maar in ons voorbeeld laten we slechts de omzet zien. Wilt u meer waarden laten zien (bijvoorbeeld kosten) dan gebruikt u de kolommen daarnaast. In de volgende afbeelding ziet u hoe u in ons voorbeeld het werkblad dient in te richten.
▲
32
WWW.KLUWERMANAGEMENT.NL
In kolom A schrijven we de maanden, in kolom B de omzetwaarden en in kolommen C en D de maximumen minimumwaarden. U hoeft de maximum- en minimumwaarde slechts een keer in te geven (namelijk in cellen C2 en D2), want u kunt deze in één keer naar beneden kopiëren. Markeer daartoe cellen C2 en D2 en dubbelklik op de vulgreep.
U dient de formule als volgt te lezen: als de omzet hoger is dan de maximumwaarde en hoger dan de minimumwaarde, dan dient een lachend mannetje te worden afgebeeld. In het geval dat de omzet lager is dan beide genoemde waarden, wordt een huilend mannetje afgebeeld. Bevindt de omzet zich tussen beide grenzen, dan wordt een neutraal mannetje afgebeeld. Let erop dat de cellen E2 t/m E13 als Wingdings zijn opgemaakt.
De vulgreep is het kleine vierkantje rechts onderaan. Als u met de muis hiernaar toegaat, verandert de vorm in een kruisje. Zodra u dit ziet, kunt u hierop dubbelklikken. De maanden kunt u snel invoeren door alleen de maand januari in te geven en met de muis via de vulgreep naar beneden te kopiëren. Deze handige functie heet ‘Autovullen’. De maximum- en minimumwaarden zijn veelal gerelateerd aan de waarden zoals die voorkomen in uw begroting of bedrijfsplan.
De grafiek maken
▲
Voor het maken van de grafiek gebruikt u de Wizard Grafieken. Daartoe dient u eerst het gebied A1 t/m D13 te markeren. Daarna klikt u op de Wizard Grafieken in de standaardwerkbalk. Vervolgens zult u de volgende dialoogvensters doorlopen.
▲
Het toevoegen van smiley’s U kunt smiley’s toevoegen door een ander lettertype te kiezen, namelijk Wingdings.
▲
De hoofdletters J, K en L veranderen respectievelijk in een lachend mannetje, neutraal en daarna huilend mannetje zodra u Wingdings als lettertype heeft gekozen. Met deze wetenschap kunnen we de volgende ALS-formule maken die we in cel E2 plaatsen:
Kies deze lijngrafiek als grafiektype en klik op Volgende.
▲
= ALS(EN(B2>C2;B2>D2);"J";ALS(EN(B2
▲
Accepteer de weergegeven instellingen en klik op Volgende.
33
E xce l - l e n t e M a n a g e m e n t To o l s
MANAGEMENT TOOLS NUMMER 1 / 2006
▲
ziet verschijnen met de tekst Waardeas. Op dat moment moet u op de rechter muisknop klikken, waarna een verkort menu verschijnt. Uit dit menu kiest u de optie As opmaken. Kies daarna uit het dialoogvenster het tabblad Schaal en geef de volgende instellingen in. Minimum: Maximum: Primaire eenheid:
10000 36000 2000
▲
Voorzie de grafiek eventueel van titels en klik daarna op het tabblad Gegevenslabels.
▲
Klik daarna op het tabblad Getal om het lettertype te verkleinen. Kies bijvoorbeeld 9 punten en klik op OK.
▲
Klik dan op de knop Volgende.
▲
Klik op Voltooien om de grafiek als object in het werkblad af te beelden. De grafiek wordt in het werkblad afgebeeld, maar ziet er nog niet goed uit.
De grafiek aanpassen
34
U kunt de grafiek in eerste instantie groter maken door één keer op de grafiek te klikken. Na het verschijnen van de aangrijppunten kunt u met behulp van de muis de grafiek zowel verticaal als horizontaal vergroten. Vervolgens gaat u de y-asindeling aanpassen. Beweeg daartoe zodanig met de muis naar de linkerkant van de grafiek dat u een geel informatievakje
De volgende stap is het vervangen van de waarden in de grafiek door de smiley’s. Dit kost even wat tijd want u moet het voor twaalf waarden doen. Gelukkig hoeft u dit maar eenmalig te verrichten. Het vervangen van de waarden gaat als volgt. Kies eerst waarde in de grafiek uit door met de muis naar de eerste waarde te bewegen. Zodra u het gele vakje ‘omzet’ Gegevenslabels ziet verschijnen, dient u een keer met de muis hierop te klikken. Als het goed is, ziet u nu dat alle waarden in de grafiek omringd zijn met aangrijppunten. Beweeg dan de muis naar het eerste punt, totdat u een geel vakje ziet
WWW.KLUWERMANAGEMENT.NL
verschijnen met de tekst ‘omzet’ Punt ‘januari’ Gegevenslabel. Klik dan een keer met de muis hierop en u zult zien dat de eerste waarde omkaderd wordt met een vierkant.
▲
op de maximumlijn (bijvoorbeeld juni) totdat u een geel vakje ziet verschijnen met de tekst ‘maximum’ Punt ‘juni’ Gegevenslabels. Klik dan met de muis hierop. De lijn is dan gemarkeerd met aangrijppunten. Daarna klikt u met de rechter muisknop en uit het dan verschijnende verkorte menu kiest u de optie Gegevensreeks opmaken. Uit het dialoogvenster kiest u het tabblad Patronen en bij Markering selecteert u de optie Geen. Daarna klikt u op OK om door te gaan. U zult zien dat de markeringen verdwenen zijn in de grafiek. Op analoge wijze dient u dat ook te doen voor de minimumlijn.
▲
Klik dan met de muis in de formulebalk en plaats een = teken en beweeg de muis naar de eerste smiley in cel E2. U ziet dat er in de formulebalk een formule wordt gebouwd. Klik dan op Enter om de formule te bekrachtigen.
▲
Zo doet u dat op analoge wijze voor de overige 11 punten. Wel moet u eraan denken te verwijzen naar de juiste smiley in kolom E. Als u klaar bent, heeft u nu een grafiek met overal de letters J, K en L staan. Nu dient u het lettertype nog te veranderen naar Wingdings. Dit gaat als volgt. Ga naar een van de punten in de grafiek totdat u een geel vakje ziet verschijnen met de tekst ‘omzet’ Gegevenslabels. Klik dan met de muis hierop. U zult zien dat de omzetlijn gemarkeerd is met aangrijppunten. Klik daarna met de rechter muisknop, waarna er een verkort menu verschijnt. Hieruit kiest u de optie Gegevenslabels opmaken. Kies dan Wingdings als lettertype.
Als resultaat krijgt u de volgende grafiek. In deze grafiek heb ik bovendien een andere achtergrondkleur gebruikt.
▲
▲
Klik daarna op OK om door te gaan. Het resultaat is dat alle letters door smiley’s zijn vervangen. De laatste actie is het verwijderen van grenspunten in de lijnen die de maximum- en minimumwaarden weergeven. Dat gaat als volgt. Beweeg de muis naar een van de markeerpunten
De meeste mensen vinden het werken met grafieken moeilijk, omdat er vele opties zijn. Ook het klikken en selecteren lijkt nogal ingewikkeld. Echter, de truc bestaat daarin dat u met de muis pas mag gaan klikken, zodra u het bijbehorende gele informatievakje ziet verschijnen. Als u iets wilt doen met het grafiekgebied, moet u de muis zodanig bewegen totdat u het gele vakje ziet verschijnen met de tekst grafiekgebied. Door te klikken op de rechter muisknop verschijnt dan een verkort menu waaruit u een keuze kunt maken. Voor sommige onderdelen echter, moet u eerst de gehele lijn selecteren, voordat u de rechter muisknop kunt gebruiken. Het feitelijk selecteren van een onderdeel of meerdere onderdelen geschiedt steeds met de linker muisknop in combinatie met het verschijnen van het daartoe bijpassende commentaar in het gele infovakje.
35
E xce l - l e n t e M a n a g e m e n t To o l s
MANAGEMENT TOOLS NUMMER 1 / 2006
TONY DE JONKER
Tony de Jonker werkt als zelfstandig consultant bij De Jonker Consultancy en heeft specifieke kennis op het snijvlak van accounting/finance en het ontwikkelen van Officeapplicaties. Hij heeft meer dan 28 jaar ervaring en heeft gewerkt voor gerenommeerde nationale en internationale bedrijven. Daarnaast geeft hij Excel Mastertrainingen. In het cursusmateriaal heeft hij de laatste inzichten en best practices verwerkt van verschillende Excel-websites, vakliteratuur en internationale Excel-trainingen.
E-MAIL:
[email protected].
36
Toepassingen Zoals u ziet kunt u met Excel sprekende grafieken maken en deze goed gebruiken in uw presentaties zodat u overtuigender overkomt. U kunt bovenstaande toepassing natuurlijk ook aanpassen om kosten af te beelden. Alleen zult u de smileyformules overeenkomstig moeten aanpassen. Ook is het mogelijk om een complete applicatie te maken waaruit u kunt kiezen uit bijvoorbeeld meerdere filialen, meerdere soorten en verschillende tijdperioden, waarna de grafiek zich automatisch aanpast. Ook het bepalen van de maximum- en minimumwaarden kan volautomatisch geschieden. Dit gebeurt in combinatie met aanvullende Excel-functies en kleine VBA-programma’s.
