Tagarchief: Sparkline

Snelle analyse



Op de site van G-Info hebben we het al heel vaak gehad over allerlei methoden om met Excel gegevens te analyseren.

Uit alle vragen die we krijgen blijkt dat de meeste mensen daarbij worstelen met Voorwaardelijke opmaak, grafieken en draaitabellen. Daar hebben we dan ook al diverse keren aandacht aan besteed.

Waar we het nog niet over hebben gehad, is dat je Excel vaak automatisch het (voor)werk kunt laten doen!
Microsoft heeft (vanaf versie 2013) een optie ingebouwd, die ze Snelle Analyse genoemd hebben. Naar mijn idee zijn ze daarbij eigenlijk te bescheiden: ik zou dat eerder Zeer snelle analyse met heel erg veel mogelijkheden hebben genoemd, maar dat is natuurlijk wat lang in een menu ūüėČ

De diverse resultaten van Snelle analyse kunnen nog handmatig naar wens worden aangepast; ook kan het een goede start zijn om de diverse onderdelen van Excel die daarbij gebruikt worden, beter te begrijpen.

Basis-gegevens

In het Voorbeeldbestand staat op het tabblad Data een blokje gegevens dat we in dit artikel zullen gebruiken om te laten zien hoe snel (en goed!) Snelle Analyse werkt.

De getallen worden door de functie ASELECTTUSSEN bij iedere wijziging in de werkmap (of na het drukken op F9) opnieuw gegenereerd. Het effect daarvan zie je dan direct in de verschillende analyses terug.

Snelle analyse starten

Selecteer je in Excel meer dan één cel tegelijk dan zie je rechtsonder bij die selectie de button Snelle analyse tevoorschijn komen. Zoals je kunt zien kun je ook de toetscombinatie Ctrl-Q gebruiken.

NB de button komt alleen tevoorschijn als je aaneengesloten cellen selecteert.

Klik je op de button dan verschijnt het Snelle analyse-menu:

Je kunt dus op 5 manieren je gegevens analyseren: met Opmaak (beter gezegd Voorwaardelijke opmaak), door middel van Grafieken, door automatisch Totalen (en andere statistieken) toe te laten voegen, door het gebruik van Tabellen (inclusief draaitabellen) of met Sparklines.

De mogelijkheden binnen deze opties kunnen afhankelijk zijn van de geselecteerde cellen.

Opmaak

De (voorwaardelijke) opmaak willen we alleen toepassen op de getallen in het overzicht. Dus selecteer eerst de cellen met die getallen en klik dan op de Analyse-button (of druk op Ctrl-Q). De Opmaak-optie is al geselecteerd. Wanneer je nu met de muis over de 6 verschillende menu-keuzes gaat zie je direct het resultaat daarvan in de brongegevens.

Wanneer je de opmaak daadwerkelijk aan je gegevens wilt toevoegen dan moet je op de betreffende keuze klikken (zie het tabblad Opmaak van het Voorbeeldbestand).
Ook kun je een combinatie van voorwaardelijke opmaak toevoegen door meerdere keuzes achter elkaar te maken.
Wil je de opmaak verwijderen kies dan de laatste optie in het Opmaak-menu.

Gegevensbalk

Via de eerste keuzemogelijkheid worden Gegevensbalken aan de geselecteerde cellen toegevoegd. Hierdoor krijg je snel inzicht in hoe de getallen zich ten opzichte van elkaar verhouden.

NB Excel zal om de lengte van de gegevensbalken te maken alle getallen uit de geselecteerde cellen vergelijken. Wil je dat Excel per kolom (of rij) de waardes vergelijkt dan moet je de opmaak voor iedere kolom (of rij) apart instellen.

Kleurschalen en Pictogrammen

Ook via de opties Kleurschalen en Pictogrammen kun je de onderlinge verhoudingen van de geselecteerde getallen zichtbaar maken.

NB de voorwaardelijke opmaak kan nog naar wens worden aangepast:

  1. selecteer één van de cellen met opmaak
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. kies Regels beheren….
  4. kies Regel bewerken
  5. pas de diverse opties naar wens aan

Groter dan

Door middel van de vierde opmaak-optie kun je getallen die groter zijn dan een bepaalde waarde een opmaak meegeven.

Kies je deze mogelijkheid dan moet je een grenswaarde opgeven; in het voorbeeld verwijzen we daarbij naar cel D32. Door deze cel te wijzigen zal de opmaak zich automatisch aanpassen.

Bovenste 10%

Deze optie spreekt voor zich. De cellen die waarden bevatten die bij de hoogste 10% horen worden gemarkeerd.

Door de opmaak-regel aan te passen kun je ook een heel ander gedeelte van de getallen een opmaak geven:

Opmaak wissen

Met de 6e keuzemogelijkheid kun je bestaande opmaak wissen.

NB op het tabblad Opmaak van het Voorbeeldbestand staan ‘gewone’ Excel-overzichten. Wanneer je deze uitbreidt met nieuwe gegevens moet je de opmaak zelf nog aan die nieuwe gegevens toevoegen.
In het tabblad Opmaak2 staan dezelfde overzichten, maar dan in de vorm van een Excel-tabel. Als je daar gegevens toevoegt, krijgen de nieuwe gegevens automatisch de bijbehorende opmaak.

Grafieken

Grafieken worden veel gebruikt als rapportagetool, maar kunnen ook een goed analyse-hulpmiddel zijn.

Met Snelle analyse is een grafiek maken een fluitje van een cent:

  1. voor een grafiek hebben we niet alleen de getallen nodig maar ook de omschrijvingen daar omheen.
  2. selecteer alle benodigde cellen en klik op de button Snelle analyse
    of
    selecteer één van de cellen met getallen en druk op Ctrl-Q
  1. klik in het submenu op de optie Grafieken en ga met de muis over de voorgestelde grafieken. Welke dit zijn is afhankelijk van de brongegevens.
  2. Klik op een van de grafieken, et voilà! Zie het tabblad Grafieken van het Voorbeeldbestand.

Komen er nieuwe gegevens bij, dan zul je óf de de brongegevens van de grafiek moeten aanpassen (rechtsklikken op de grafiek en Gegevens selecteren kiezen) óf de grafiek op bovenstaande manier opnieuw moeten maken.

Dat kun je ondervangen door de brongegevens als Excel-tabel vast te leggen (zie het tabblad Grafieken2 van het Voorbeeldbestand).
Helaas, dan werkt het selecteren van de gegevens met Ctrl-Q niet goed. Doe dan het volgende:

  1. selecteer één van de cellen met getallen en druk op Ctrl-A
  2. maar we hebben ook de kopregel nodig; druk nogmaals op Ctrl-A
  3. druk dan Ctrl-Q of kies de Snelle analyse-button.

Totalen

  1. we willen natuurlijk alleen van de getallen in het binnenblok de totalen berekenen (en bijvoorbeeld niet van de datums ook al zijn dat voor Excel ook getallen); dus selecteer de cellen C4:F15 (zie het tabblad Totalen van het Voorbeeldbestand)
  2. druk op Ctrl-Q of klik op de analyse-button
  3. kies de optie Totalen en klik op de gewenste functie (Som, Gemiddelde, Aantal, Totaal % of Voorlopig …)
  4. Excel plaatst formules onder het geselecteerde blok getallen en voert daarmee de gewenste berekening uit.

NB1 zijn de cellen onder de selectie niet leeg, dan krijg je een waarschuwing of je deze wilt overschrijven. Wil je deze gegevens bewaren maak dan eerst ruimte door een lege regel in te voegen voordat je bovenstaande handelingen uitvoert.

NB2 bij de eerste 5 opties is een regel blauw gekleurd; dit betekent dat Excel de formules onderaan in een regel plaatst (en totalen per kolom bepaalt). De 6e optie (en verder, klik op het pijltje aan de rechterkant) laten een gekleurde kolom zien. Als je die optie gebruikt zal Excel formules aan de rechterkant plaatsen en dus totaalberekeningen over de rij uitvoeren.

NB3 Excel plaatst formules in het tabblad. Deze zijn zodanig opgezet dat bij het toevoegen van nieuwe gegevens de berekeningen met een paar kleine aanpassingen weer kloppen.

Lopend totaal

In rij 19 staat een formule die het lopend totaal bepaalt (in Excel wordt deze analyse-optie met Voorlopig … aangeduid). In cel C19 staat het totaal van de regio Noord, in D19 het totaal van Noord √©n Oost etc.

In kolom J staat een lopend totaal over alle regio’s. Wil je per regio een lopend totaal dan moet je eerst tussen C en D een nieuwe kolom invoegen, de gegevens van kolom C selecteren en dan het lopend totaal invoegen. Doe dat ook voor de andere kolommen; zie het tabblad Totalen:

NB plaats je de gegevens in een Excel-tabel dan zien de ingevoegde formules er heel anders uit (zie het tabblad Totalen2 van het Voorbeeldbestand). Bij het toevoegen van nieuwe gegevens hoeft dan niets (of veel minder) aan de formules gewijzigd te worden.

Tabellen

Met de eerste keuze binnen de analyse-optie Tabellen wordt een gewoon bereik van cellen omgezet naar een Excel-tabel. Maar ik gebruik die (bijna) nooit; die optie kennen we al via Invoegen of door Ctrl-L te tikken.

In het tabblad Tabellen van het Voorbeeldbestand is het gebruikte bronbestand omgezet naar een database-vorm; dit om de mogelijkheden van een draaitabel makkelijker te benutten.

Per combinatie van kenmerken (Regio, Maand en Product) wordt het Aantal vastgelegd.

  1. selecteer één van de cellen in de Excel-tabel en druk op Ctrl-Q
  2. kies de analyse-optie Tabellen
  3. op basis van deze brongegevens stelt Excel 2 draaitabellen voor: Som van Aantal per regio en per product
  4. klik op één van de 2 mogelijkheden (of de 3e om zelf een draaitabel te maken)

NB de 2 draaitabellen zijn gebaseerd op dezelfde brongegevens maar laten ieder een ander totaal zien!
Dit is een inconsistentie binnen Excel. Maak je zelf verschillende draaitabellen op basis van dezelfde brongegevens dan worden alle draaitabellen tegelijkertijd vernieuwd. Zijn de draaitabellen via Snelle analyse aangemaakt dan moet iedere draaitabel afzonderlijk vernieuwd worden. Maar dan worden de brongegevens tussentijds aangepast door de Aselect-formule.

Op het tabblad Tabellen staat ook een draai-grafiek; deze is gemaakt op basis van de gegevens in de eerste kolommen door in de analyse-optie een grafiek te kiezen met het -teken.

Sparklines

Niets nieuws meer onder zon: selecteer alle cellen met getallen (dus niet de maanden en de kopregels), Ctrl-Q, kies de optie Sparklines en klik op één van de drie mogelijkheden (zie het tabblad Sparklines in het Voorbeeldbestand).

Het eerste type sparkline laat het globale verloop als lijngrafiek zien, de tweede globaal het verloop als kolomgrafiek en de derde (Winst/verlies) laat alleen maar zien of het resultaat positief of negatief is.

NB1 het uiterlijk van de sparklines kan makkelijk aangepast worden: klik op een cel die een sparkline bevat en kies bij Hulpmiddelen voor sparklines de optie Ontwerpen.

NB2 of je nu met een bereik van cellen werkt of met een Excel-tabel (zie het tabblad Sparklines2 van het Voorbeeldbestand), bij het uitbreiden van gegevens zul je de sparklines ook moeten aanpassen (of opnieuw maken). Het aanpassen gaat simpel door de vulgreep rechtsonder in de onderste cel met een sparkline naar beneden te trekken.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Sparklines

Volgens Microsoft:

Een sparkline is een kleine grafiek in een cel van een werkblad met een visuele weergave van de gegevens. Gebruik sparklines om trends in een reeks waarden aan te geven, zoals seizoensmatige stijgingen of dalingen of economische cycli, of om maximum- en minimumwaarden te markeren.

Anders gezegd: de sparkline-grafiek is niet bedoeld om details te onderscheiden, maar vooral om snel bepaalde trends of uitschieters te signaleren.

Hieronder gaan we eerst kijken hoe je een sparkline in je werkmap opneemt en daarna worden enkele extra mogelijkheden belicht en ook een aandachtspunt bij het gebruik van draaitabellen.

Hoe maak je een sparkline?

Laten we een simpel voorbeeld nemen: de inkomsten en uitgaven per maand en het resultaat daarvan. Zie het tabblad InUit in het Voorbeeldbestand.

LET OP de getallen zullen afwijken van de figuur hierboven. Met de functie ASELECTTUSSEN worden iedere keer nieuwe cijfers gegenereerd (druk op F9); dit om de consequenties voor de grafieken makkelijk te kunnen laten zien.

  1. selecteer het blokje getallen, dat in grafieken moet worden weergegeven (in het voorbeeld de cellen C4:N6; dus zonder de verklarende teksten! Een sparkline geeft alleen getallen weer.)
  2. kies in de menutab Invoegen in het blok Sparklines de optie Lijn
  3. in het nieuwe venster, dat automatisch opent, is het gegevensbereik al ingevuld. De plaats van de sparklines moet nog ingevuld worden. In dit geval moeten die achter de gegevens (dus in kolom O) komen; we vullen bij Locatiebereik O4:O6 in.
  4. klik op OK
  5. maak de kolom met de sparklines (in dit geval O) zo breed als gewenst om de resultaten het beste tot hun recht te laten komen.

LET OP het aantal cellen in locatiebereik moet gelijk zijn aan het aantal regels in het Gegevensbereik anders krijg je een foutmelding.

NB1 De sparklines mogen overal in het werkblad staan en ze mogen onder of naast elkaar komen. Maar zoals op de Microsoft-website staat:¬†¬†“Plaats een sparkline dichtbij de gegevens voor het optimale effect.

NB2 Je kunt bij punt 1 ook beginnen met de selectie van de locatiecellen (waar moeten de sparklines komen). In stap 3 moet dan het gegevensbereik worden opgegeven.

Sparklines opmaken

Hoewel in bovenstaand voorbeeld het verloop in de tijd al goed zichtbaar is, kunnen we bepaalde kenmerken nog accentueren:

  1. selecteer één van de sparklines; klik bijvoorbeeld met de muis in cel O4
  2. kies in de nieuwe menutab Hulpmiddelen voor sparklines de optie Ontwerpen
  3. vink in het blok Weergeven de opties Hoge punt en Negatieve punten aan
  4. kies in het blok Stijl eventueel een andere voor-geprogrammeerde opmaak

Deze opmaak wordt automatisch tegelijkertijd toegepast op alle drie de sparklines. Dit komt omdat Excel deze drie grafieken in één groep heeft geplaatst (klik in cel O4, kies in de menutab Hulpmiddelen voor sparklines de optie Ontwerpen en kies de optie Gegevens bewerken; nu zie je dat alle drie de sparklines bij elkaar horen).

Bij de inkomsten is de hoogste waarde (Hoge punt) goed, maar bij de uitgaven is dit natuurlijk niet het geval. De toppen moeten een verschillende kleur krijgen, daarom mogen de grafieken niet meer als één groep geselecteerd worden :

  1. selecteer de cellen met sparklines (O4:O6) en kies in de menutab Hulpmiddelen voor sparklines binnen de optie Ontwerpen de optie Groep opheffen
  2. selecteer alleen cel O4; kies binnen de optie Markeringskleur bij Hoge punt een groene kleur
  3. selecteer alleen cel O5; kies binnen de optie Markeringskleur bij Hoge punt een rode kleur
  4. selecteer alleen cel O6; kies binnen het blok Groeperen de optie As/As weergeven

NB Klik in cel O4, kies in de menutab Hulpmiddelen voor sparklines de optie Ontwerpen en kies de optie Gegevens bewerken; nu zie je dat deze sparkline alleen bij gegevens uit regel 4 hoort.

In het tabblad InUit2 van het Voorbeeldbestand is nog een andere optie gebruikt; bij de eerste 2 grafieken is als Type de optie Kolom gekozen:

NB de sparklines worden in Excel als achtergrond van een cel opgenomen. Dat betekent dat we nog steeds andere informatie in de cel kunnen opnemen, bijvoorbeeld toelichtende tekst.

Sparklines en draaitabellen

Wanneer je sparklines achter een draaitabel plaatst kan het voorkomen, dat wanneer deze draaitabel ‘groeit’ door nieuwe informatie die aan de bron wordt toegevoegd, de draaitabel over de sparklines valt.

Een oplossing is om van te voren te zorgen dat de draaitabel al alle mogelijke items bevat, zodat deze nooit meer groter kan worden (tabblad Ovz in het Voorbeeldbestand):

Aan de bron zijn hiertoe lege regels voor Nov en Dec toegevoegd; zie tabblad Data in het Voorbeeldbestand.

NB Het Resultaat staat niet in de bron, maar wordt in de draaitabel berekend:

  1. selecteer in de draaitabel één van de soorten(In of Uit); klik bijvoorbeeld met de muis in cel A5
  2. kies in de menutab Hulpmiddelen voor draaitabellen binnen Opties in het blok Berek. de optie Velden, Items en sets

    Kies dan de optie Nieuw item.
    NB we gaan binnen het veld Soort een nieuw item maken gebaseerd op andere items binnen Soort. De optie Nieuw veld wordt gebruikt om een nieuw veld (naast Soort, Maand en Bedrag) te maken.
  3. in het vervolgvenster geven we het nieuwe item een Naam, bijvoorbeeld Res (Resultaat is al toegevoegd) en als Formule tikken we in: = In-Uit en klikken op Toevoegen en OK

 


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: