Je bent net begonnen als adviseur ruimtelijke ordening. De eerste opdracht is het in kaart brengen van de leegstand bij het lokale winkelcentrum. Na weken wachten heb je dan eindelijk een databestand in Excel. Net op tijd, want over twee dagen mag je een eerste update geven aan de stakeholders. Het liefst wil je iets interactiefs waarbij de betrokken partijen zelf gemakkelijk doorsnedes kunnen maken.
In deze blog laat ik je zien hoe je met behulp van een draaitabel en slicers, snel en simpel een interactief overzicht en een grafiek maakt.
Dit is het databestand wat je hebt gekregen.
En dit is het interactief overzicht waar we naar toewerken, met aan de linkerkant de slicers waarmee makkelijk gefilterd kan worden.
Databewerking
In het databestand staat in kolom G het bouwjaar. Het is niet handig om alle bouwjaren als filteroptie te geven in de slicer. De lijst met mogelijkheden zou superlang worden. Daarom voegen we een kolom toe en maak je er categorieën van met behulp van de volgende formule:
=IF(ISBLANK(G2);”Onbekend”;IF(G2<1945;”<1945″;IF(G2<1970;”1945-1970″;IF(G2<1990;”1970-1990″;IF(G2<2010;”1990-2010″;”>2010″)))))
Verder ziet het bestand er goed uit.
Liever uitbesteden aan Expert Excel?
Met het team Excel specialisten sta ik voor je klaar!
Visualisatie
Met behulp van een draaitabel gaan we het interactief maken. De slicers kan je namelijk alleen gebruiken in combinatie met een draaitabel. Allereerst selecteren we de data in het databestand, kolom A t/m L. Dan via de menuknop invoegen, kies je voor draaitabel.
Klik ok op het volgende venster om de draaitabel op een nieuw werkblad te zetten.
Je krijgt daarna het volgende te zien op je werkblad.
De velden uit het datawerkblad kan je nu in kolommen en rijen weergeven. Ook kan je, indien nodig, waarden totaliseren door velden naar het venster rechtsonder te slepen (values). We slepen Onderneming_branche in de rij en tellen Pand_straatnaam in de values.
Als Excel standaard de waarden totaliseert (sum), dubbelklik dan op het veld in de values, en kies tellen (count).
Nu zien we in één oogopslag hoeveel leegstand we hebben. We missen alleen nog een mooie visualisatie om dit in verhouding te zien. Door met de cursor in de draaitabel te staan, kies je voor invoegen draaigrafiek.
De volgende grafiek verschijnt dan.
Dit geeft al mooi weer hoe de verhouding zijn, maar het kan nog beter. Met rechtermuisknop in de grafiek, kies ik voor een circeldiagram. Bovenaan kies ik eerst voor style 3 en daarna voor style 6.
Door deze volgorde te kiezen heb ik gelijk ook percentages in de donut staan.
Ik wil ook graag de waarden uit de draaitabel er in terugzien. Door te dubbelklikken op een van de percentages in de grafiek verschijnt er rechts een venster met opties. Ik kies ook voor value en de scheid de waarden door een nieuwe regel.
Verder pas ik de titel aan door er op te dubbelklikken.
Detailinformatie
In de volgende stap maken we een overzicht met meer details. Je kopieert en plakt de draaitabel naar cel N4 voor meer ruimte in het Excel werkblad. De grafiek sleep je daaronder.
De ene draaitabel begint nu op D4 en de andere in N4. In de draaitabel in D4, selecteer je de volgende velden voor in de rijen:
Eigenaar_Soort, Onderneming_Branch, Pand_Straatnaam, Pand_Huisnummer, Pand_Huisnummer_toevoeging, Pand_Bouwjaar, Locatus_Branch en Contactgegevens pandeigenaar.
Ook kunnen de headers wat klantvriendelijker gemaakt worden in regel drie.
Interactieve filters
Als laatste stap gaan we de interactieve filters toevoegen: de slicers. Filteren op straatnaam, bouwjaarcategorie, type eigenaar en op Onderneming branche, geven het meeste inzicht.
De slicers zet je netjes onder elkaar in de eerste drie kolommen. Het aanpassen van de naam kan via Slicer Settings (rechtermuisknop). Vink dan gelijk ‘items verbergen zonder data’ aan. Dit zorgt ervoor dat je geen ‘lege’ knoppen krijgt.
Als je in de draaitabel staat en vervolgens klik op Filter Connections, dan zie je welke slicers verbonden zijn met de draaitabel. Als het goed is met alle vier.
Om de slicers ook te verbinden met de draaitabel in N4 met zijn bijbehorende grafiek, gaan we in de de draaitabel (N4) staan en klikken via Filter Connections ook bovenstaande vier slicers aan.
Het resultaat
Als je nu op ‘leegstand’ klikt in de bovenste slicer, dan wordt zowel de detailinformatie gefilterd als de grafiek. Ook zie je dat de keuzes in de andere slicers meebewegen met je eerst keuze. Zo kan je bijvoorbeeld in de tweede slicer alleen maar verder kiezen uit de Institutionele en Particuliere belegger. Simpelweg omdat dit de twee type eigenaren zijn met leegstand.
In dit voorbeeld heb ik voor een prettige weergave de grafiek over de draaitabel in N4 heen gesleept.
In het detailoverzicht heb je verder direct de belangrijkste informatie beschikbaar en kan je gelijk in actie komen door de betreffende pandeigenaar te benaderen voor meer informatie.
De visualisatie voegt in deze filtering niet zoveel toe, maar wel als je bijvoorbeeld alleen filtert op de bouwjaren 1990-2010 in de Ferdinand Bolstraat. Dan zie je gelijk dat de selectie slechts twee branche categorieën heeft.
Je kan in dezelfde slicer ook meerdere waarden selecteren. Dat doe je door eerst de multi-select aan te zetten met de groene vinkjes. Filtering weer ongedaan maken, doe je met het kruisje bij de filter helemaal rechts van de slicer.
Nu ben je in staat om zelf je volgende draaitabel met interactieve grafiek te maken.
Succes!
Alles bij elkaar optellen is een mogelijkheid, maar getotaliseerd per categorie biedt natuurlijk het meeste inzicht. Door middel van een SUMIFS formule, kunnen we verschillende criteria opgeven voor het optellen.
Om de blog overzichtelijk te houden, zal ik in de volgende blog het Macro gedeelte uitleggen. Voor nu kan je handmatig de data uit de invoer tab kopiëren om hetzelfde resultaat te bereiken.
Met behulp van het bovenstaande voorbeeld dashboard laat ik je zien hoe je met twee dashboard tips het verschil kan maken. Door het gebruik van een formule om de effectiviteit te bepalen en het toepassen van voorwaardelijke opmaak.
In het volgende venster kan je de regel specificeren. Groter dan nul moet een groene tekst krijgen. Die kleur is niet direct kiesbaar, maar wel via Customised Format.
Bijvoorbeeld op dagniveau. Allereerst maak ik weer een formule aan om een numerieke waarde te genereren voor cel B26.
