5 handige Excel tips voor kwaliteitscontrole en analyse van data

Ondanks de ruime keuze in softwarepakketten om data te verwerken is Microsoft Excel is nog steeds een van de meest gebruikte tools om analyses en rapportages te maken. Dit komt omdat je met Excel op een flexibele manier juist die informatie die voor jouw van belang is uit een berg data inzichtelijk kunt maken. Die flexibiliteit heeft echter ook een keerzijde: werken met Excel kan ook heel foutgevoelig zijn. Vooral als je omvangrijke bestanden gebruikt om data van het ene systeem naar het andere systeem over te zetten, dan ben je al snel het overzicht kwijt. En foute data levert bovendien foute informatie, iets wat je absoluut wilt voorkomen.

Gelukkig heeft Excel een aantal krachtige functies die je ondersteunen bij het maken, ordenen, filteren, combineren, controleren en inzichtelijk maken van data. Hiermee kun je de datakwaliteit nog beter borgen en de data optimaal analyseren. Wij hebben vijf nuttige tips op een rij gezet.

Excel tip 1: Maak een heatmap

Een heatmap maken in Excel is een makkelijke en eenvoudige manier om uitbijters, trends of andere belangrijke kenmerken in de data zichtbaar te maken. Een heatmap toont de hoogste waarden in een rapport in een bepaalde kleur en de laagste waarden in een andere kleur. Alle waarden er tussenin worden weergegeven in een combinatie van beide kleuren. Het geeft je een krachtig visueel overzicht waarmee je bijvoorbeeld fouten uit de data kunt halen. Een heatmap is ook geschikt om visuele rapportages mee te maken. Hieronder staan in het kort de stappen die je moet doorlopen om een heatmap te creëren.

Stap 1: Selecteer de informatie die je in de heatmap wilt, ga naar ‘Start’ en klik in de groep ‘Stijlen’ op ‘Voorwaardelijke opmaak’. Klik vervolgens op ‘Kleurenschalen’.

Stap 2: Kies vervolgens voor de kleurencombinatie die je wilt toepassen. Beweeg over de pictogrammen om een voorbeeld weer te geven. Je kunt ook zelf een kleurencombinatie kiezen door op ‘Meer regels’ te klikken.

Een heatmap kun je voor allerlei verschillende soorten data opstellen. Het helpt je om sneller overzicht te krijgen, sneller te analyseren en eenvoudiger rapportages te maken.

Excel tip 2: Stel filters in

Wanneer je uitgebreide en complexe datasets moet analyseren hoef je niet naar elke rij op hetzelfde moment te kijken. Soms wil je bijvoorbeeld alleen data zien die aan bepaalde criteria voldoet en dan zijn filters handig.

In Excel kan aan elke kolom een filter worden toegevoegd en vanuit daar kun je aangeven welke cellen je tegelijk wilt bekijken.

Stap 1: Voeg een filter toe door de gegevens te selecteren waar je een filter op wilt toepassen. Het gemakkelijkste is om de bovenste rij (de rij waarin de ‘titels’ staan weergegeven) te selecteren en vervolgens bij ‘Gegevens’ op ‘Filter’ te klikken. Alle kolommen krijgen dan een filter optie.

Stap 2: Vervolgens kun je op het pijltje achter de titel van iedere kolom selecteren welke gegevens je wilt zien. Bijvoorbeeld alleen de voedselveiligheidsincidenten die zijn vastgesteld in Frankrijk, Spanje en Rusland. Als je een filter hebt ingesteld voor een bepaalde kolom, dan verandert het driehoekje (pijltje) in een trechter. Door op de trechter te klikken kun je de filter wijzigen of uitschakelen door ‘Selecteer alles’ te kiezen.

Als je een filter hebt ingesteld zie je links onderin het scherm hoeveel records er gevonden zijn. Bij grote datasets kan het handig zijn om op die manier een paar controles uit te voeren op een aantal gegevens binnen een bepaalde selectie. Ook is het handig om bij elke filter te kijken op welke data je kunt filteren. Staat er bijvoorbeeld Lege cellen (Blanks) bij de filteropties, dan zijn er mogelijk ontbrekende gegevens. Je kunt ook controleren of er meerdere spelwijze van een naam zijn, zoals kiwien kiwie in onderstaand voorbeeld.

Excel tip 3: Duplicaten weergeven, beoordelen en verwijderen

Uitgebreide datasets hebben soms ongewenst dubbele waarden en in sommige gevallen zijn dubbele waarden terecht. Maar hoe krijg je deze waarden gemakkelijk in beeld? Je wilt namelijk soms dubbele gevallen individueel kunnen beoordelen.

Om betrouwbare rapportages en analyses te maken of gegevens van het ene systeem naar het ander over te zetten moet je een kwaliteitscontrole op dubbele waarden doen. Hieronder is beschreven hoe je dubbele waarden eenvoudig en duidelijk zichtbaar kunt maken, kunt beoordelen en vervolgens de ongewenste data kunt verwijderen.

Om de dubbele waarden weer te geven sorteer je de data op de kolom waar je de dubbele waarden wilt controleren (zoals productnaam in het voorbeeld hieronder). Vervolgens voeg je een extra kolom toe direct achter de gesorteerde kolom. Ga achter het eerste veld staan wat je wilt controleren (in onderstaand voorbeeld achter ‘Appel’) en kies bovenin bij ‘Formules’ voor ‘Functie invoegen’ en zoek de formule ‘EXACT’ (GELIJK) en druk op enter. Kies bij Tekst 1 het veld A2 en bij Tekst 2 het veld A3. Er staat dan =GELIJK(A2;A3) in cel B2. De formule vergelijkt dan de waarde in A2 (“Appel”) met die in A3 (“Banaan”) en toont als resultaat ONWAAR. Kopieer de formule vervolgens naar alle velden in de kolom (B in dit voorbeeld).

Hierdoor zie je met ‘Waar’ en ‘Onwaar’ welke waardes dubbel zijn. Door bijvoorbeeld een filter in te stellen op ‘Waar’ heb je direct alle dubbele waardes in beeld. In dit geval zul je vaak één of meer van de rijen in zijn geheel verwijderen. Om de dubbele waardes te verwijderen selecteer je de gewenste rij en klik je op ‘Delete’.

Excel tip 4: Kies voor transponeren

Soms heb je data in een Excel bestand staan die niet is weergegeven in kolommen en rijen zoals je dat zou willen. Met transponeren kun je gegevens in kolommen zo draaien dat ze opnieuw worden gerangschikt in rijen. Handig als je verschillende databestanden bij elkaar moet voegen die niet op dezelfde manier zijn ingedeeld.

Bijvoorbeeld als de gegevens er als volgt uitzien, met de landen aan de bovenkant en de kwartalen links.

Je kunt de kolommen en rijen zo draaien dat de kwartalen bovenaan en de landen links komen te staan.

Dit doe je als volgt:

Stap 1: Selecteer de gegevens die je opnieuw wilt rangschikken, inclusief rij- of kolomlabels en druk op Ctrl + C.

Stap 2: Klik met de rechtermuisknop op de cel waar u de gegevens wilt plakken, ga naar ‘Speciaal plakken’ en kies ‘Transponeren’

Excel tip 5: Voeg data uit twee werkbladen samen

Het is niet ondenkbaar dat je op enig moment data uit twee verschillende werkbladen wilt samenvoegen. Dat kan handmatig, maar Excel heeft functies waardoor het sneller, slimmer en efficiënter kan: VLOOKUP (of VERT.ZOEKEN) en HLOOKUP (of HORIZ.ZOEKEN). De enige voorwaarde om deze functie te gebruiken is dat beide werkbladen over een unieke identifier of key beschikken waarmee de data aan elkaar gekoppeld kan worden.

Bijvoorbeeld wanneer je het totaal aan voedselveiligheidsincidenten van alle landen in Europa (werkblad 2) wilt toevoegen aan werkblad 1.

Voorbeeld werkblad 1:

Voorbeeld werkblad 2:

Stap 1: Ga in de cel staan waarin je de data van werkblad 2 zichtbaar wilt krijgen.

Stap 2: Ga naar de ‘Formules’ en kies voor de VLOOKUP (of VERT.ZOEKEN)

Stap 3: Vul de juiste argumenten in. De VLOOKUP functie bestaan uit vier argumenten: ‘zoekwaarden’ (of ‘lookup_value’), ‘tabelmatrix’ (of ‘table_array’), ‘kolommenindex_getal’ (of ‘col_index_num’) en ‘benaderen’ (of ‘range_lookup’).

De ‘zoekwaarden’ is de unieke identifier om de data uit het tweede werkblad aan het eerste werkblad te koppelen. In het voorbeeld is dit A2. Vervolgens vul je de ‘tabelmatrix’ in. Dit is het celbereik van het tweede werkblad waarin die functie naar data moet gaan zoeken. In het voorbeeld is dit Blad2!$A$2:$B$5.

De derde stap is het invullen van de ‘kolommenindex_getal’. Dit is het nummer (geteld vanaf links) van de kolom in het tweede werkblad waarin de data staat die je in het eerste tabblad zichtbaar wilt maken. In dit voorbeeld is dat de tweede (2) kolom.

Als laatste vul je het argument ‘benaderen’ in. Dit argument kan de waarde ‘waar’ of ‘onwaar’ hebben. Bij de waarde onwaar geeft Excel de eerst gevonden waarde die exact overeenkomt terug. In dit voorbeeld gebruiken we dus ‘onwaar’. Vervolgens kopieer je de formules voor de overige cellen door in de rechteronderhoek de formule naar beneden te ‘slepen’.

Het resultaat:

In dit voorbeeld zoek je dus in werkblad 2 naar de juiste waarde die bij het juiste kwartaal uit werkblad 1 hoort en je plaatst die waarde op de juiste plek in werkblad 1. Dus ook als de kwartalen in werkblad 2 niet netjes op volgorde zouden staan zou deze functie nog steeds werken. Dit maakt VLOOKUP een krachtige functie waarmee je een Excel sheet kunt organiseren en data op de juiste plek kunt zetten.

De HLOOKUP functie werkt hetzelfde, maar geldt voor horizontale combinaties.

Je kunt je voorstellen dat deze functie je een hoop tijd bespaart wanneer je dit in grote datasets van honderden, zo niet duizenden, regels toepast.

Kortom, Excel biedt genoeg mogelijkheden om het controleren van data kwaliteit en het opstellen van rapportages en data analyses nog eenvoudiger te maken.

Goede rapportages en data analyses zijn belangrijk om stakeholders en de achterban goed te informeren. Om dit te realiseren moet je de juiste informatie uit de beschikbare data halen. Door de toenemende complexiteit van voedselketens en de grote hoeveelheden beschikbare data is dat echter een steeds grotere uitdaging.