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:
- selecteer één van de cellen met opmaak
- kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
- kies Regels beheren….
- kies Regel bewerken
- 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:
- voor een grafiek hebben we niet alleen de getallen nodig maar ook de omschrijvingen daar omheen.
- selecteer alle benodigde cellen en klik op de button Snelle analyse
of
selecteer één van de cellen met getallen en druk op Ctrl-Q
- 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.
- 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:
- selecteer één van de cellen met getallen en druk op Ctrl-A
- maar we hebben ook de kopregel nodig; druk nogmaals op Ctrl-A
- druk dan Ctrl-Q of kies de Snelle analyse-button.
Totalen
- 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)
- druk op Ctrl-Q of klik op de analyse-button
- kies de optie Totalen en klik op de gewenste functie (Som, Gemiddelde, Aantal, Totaal % of Voorlopig …)
- 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.
- selecteer één van de cellen in de Excel-tabel en druk op Ctrl-Q
- kies de analyse-optie Tabellen
- op basis van deze brongegevens stelt Excel 2 draaitabellen voor: Som van Aantal per regio en per product
- 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.