Excellerend
Kwartaaltip 2013-4
Excellerend Heemraadweg 21 2741 NC Waddinxveen 06 – 5115 97 46
[email protected] BTW: NL0021459225 ABN/AMRO: NL72ABNA0536825491 KVK: 24389967
Grafiek en dynamisch bereik Iedereen die vaak met tabellen werkt, weet dat een grafiek in één oogopslag vaak meer informatie geeft dan al die cijfers samen. Een grafiek heeft een gegevensbereik, maar wanneer je periodiek cijfers toevoegt zodat het bereik groter wordt, dan wil je niet dat je iedere dag, week of maand het bereik van de grafiek handmatig moet aanpassen. Dat hoeft ook niet want het is mogelijk een dynamisch bereik te maken voor een grafiek. Grafiek Stel dat je in een tabel de verkopen gaat bijhouden van fietsen, steps en spacescooters, inclusief de kosten en omzet ervan:
In een aparte tabel staan de kostprijs per product en de verkoopprijs vermeld. Met de functie VERT.ZOEKEN zoek je de bijbehorende prijs bij het product in kolom B, en vermenigvuldig je het geheel met het verkochte aantal producten in kolom C. Om dit grafisch weer te geven kun je cellen A1:E4 selecteren en via de tab Invoegen > Grafieken een lijndiagram invoegen:
Pagina 1 van 6
www.excellerend.nl
Excellerend
Kwartaaltip 2013-4
Klik met de rechter muisknop op de grafiek en kies uit het menu ‘Gegevens selecteren’:
De gegevensbron is het gegevensbereik A1:E4 op tabblad Blad1. Het toevoegen van een nieuwe maand met de verkopen resulteert niet in een veranderende grafiek:
Gegevensbereik als Tabel definiëren Wanneer ik vooraf het bereik A1:E4 als tabel gedefinieerd had, was het anders verlopen. Selecteer een cel in de tabel met de verkopen, klik in het lint op de tab Invoegen, en in de categorie Tabellen klik je op de knop Tabel:
Pagina 2 van 6
www.excellerend.nl
Excellerend
Kwartaaltip 2013-4
Excel zet een kringellijn om het aaneengesloten bereik van rijen en kolommen. Klik op OK in het dialoogvenster ‘Tabel maken’. De tabel krijgt automatisch een andere opmaak mee, en er verschijnt een hulptab.
De hulptab ‘Hulpmiddelen voor tabellen’:
Op deze hulptab zitten diverse functionaliteiten om met de tabel te werken. Linksboven staat in de categorie ‘Eigenschappen’ de naam van de tabel ‘Tabel1’. Deze kun je hernoemen naar een herkenbare naam zodat je ook in formules de tabel kunt gebruiken. Wanneer ik nu gegevens over april ga toevoegen onderaan de tabel, herkent Excel dit als een uitbreiding van de tabel, trekt gelijk de functies in kolommen D en E door èn past automatisch de grafiek aan:
Pagina 3 van 6
www.excellerend.nl
Excellerend
Kwartaaltip 2013-4
De formules geven nu nog een foutmelding1 omdat ik geen product ingevoerd heb, en pas als ik ook een aantal ingevoerd heb, verschijnen de kosten en opbrengsten in de tabel en in de grafiek:
Ik vind het nu nog wel vervelend dat de lijn met de aantallen –die veel lager liggen dan de kosten en de omzet- op dezelfde as wordt uitgelijnd. Selecteer daarom de grafiek zodat de hulptab ’Hulpmiddelen voor grafieken’ zichtbaar wordt
Activeer de hulptab, klik op de tweede tab hierin ‘Indeling’ en klik in de categorie ‘Huidige selectie’ op de en kies de reeks ‘Aantal’:
1
Dit is op te lossen door de functie uit te breiden: =ALS(EN(B2<>"";C2<>"");VERT.ZOEKEN(B2;$G$1:$I$4;2;0)*C2;0)
Pagina 4 van 6
www.excellerend.nl
Excellerend
Kwartaaltip 2013-4
Vervolgens klik je in dezelfde categorie ‘Huidige selectie’ op ‘Selectie opmaken’. Vink de ‘Secundaire as’ aan en klik op ‘Sluiten’.
De aantallen zijn nu ook beter zichtbaar met rechts een tweede verticale as:
Pagina 5 van 6
www.excellerend.nl
Excellerend
Kwartaaltip 2013-4
Excel 2003 Mocht je werken met Excel 2003 en je wilt je grafiek ook automatisch laten bijwerken met nieuwe data, geef dan een seintje via
[email protected] en ik leg het je uit.
Einde kwartaaltip 2013-04
De kwartaaltip(s) zijn als PDF te downloaden via: www.excellerend.nl\kwartaaltips.html Wilt u een op maat gemaakte cursus Excel voor uw organisatie regelen? Zoekt u ondersteuning in het bouwen van rekenmodellen, controles, of denkt u dat iets mogelijk is in Excel maar u weet niet hoe? Neem dan contact op met Richard Meijles: 06 – 5115 9746 of via e-mail:
[email protected]. Wilt u geen kwartaaltips meer ontvangen? Klik dan op:
[email protected] Hebt u een verzoek voor een kwartaaltip of loop je ergens tegenaan in het gebruik van Excel? Klik dan op:
[email protected] en vermeld tevens uw Excelversie. Richard Meijles <><
Pagina 6 van 6
www.excellerend.nl
