vlookup pro: hoe 2 tabellen errorproof linken in excel met vlookup en match functie

Het moet zeker al gebeurd zijn: je hebt je tabellen gelinkt met de vlookup functie zoals we in één van onze vorige blogs (zie Hoe 2 tabellen linken met Excel) hebben uitgelegd. En hoera hoera, het werkt! Na een tijdje merk je dat plots het resultaat is veranderd en de vlookup functie naar de verkeerde kolom verwijst. Hoe is dit in godsnaam mogelijk?

Een typische oorzaak van dit probleem is het invoegen kolommen, zodat de verwijzing in de vlookup niet meer klopt (typisch door de nietsvermoedende collega die daardoor alles in de war stuurt, grrr…).

Er is relatief eenvoudige oplossing die dit probleem voor eens en voor altijd uit de wereld helpt: combineer de ‘vlookup’ functie met een ‘match’ functie.

5 basis principes bij invoeren van KPI Dashboard in bestaand excel – VOORBEELD

Om de principes van de vorige post duidelijk te maken (en aan te tonen dat we met concrete oplossingen komen ) hieronder voorbeeld van hoe je een bestaand excel model kan omtoveren in een gebruiksvriendelijk model met een waanzinnig dashboard output. Het volgt dezelfde structuur en geeft een stap per stap handleiding van hoe we te werk zijn gegaan.

We gebruiken een bestaand model (download hier van www.exinfm.com). Het model evalueert wat het meest voordelig is: een auto aankopen dan wel te leasen. Het is een redelijk eenvoudig model en dient enkel om een aantal principes te illustreren. De methode die wordt voorgesteld kan echter zonder problemen toegepast worden om de meest complexe modellen.

Hier gaan we dan…  (de resulterende excel staat in de ‘nuttige excels’ lijst voor download)

5 basis principes bij invoeren van KPI Dashboard in bestaand excel model

De wereld wordt steeds sneller en complexer – niettemin wordt er verlangd dat beslissingen kordaat en onderbouwd genomen worden. Zeker in de bedrijfswereld gaan deze belangrijke beslissingen gepaard met steeds meer verfijnde analyses door ingewikkelde financiële en operationele (excel) modellen. Niet zelden overdonderen deze modellen ons met een zondvloed van gegevens en data, waardoor we de bomen niet meer door het bos zien. Als gevolg is efficiente rapportering dikwijls een ramp.

De oplossing van dit probleem is het invoeren van een doordacht dashboard systeem, waarbij data wordt gestructuurd in verschillende niveaus en representaties. Dashboards kunnen opbouwd worden op zeer verschillende manieren (grafisch, Key Perfomance Indicators, verklikker lichtjes, enz). In andere posts geven we specifiek advies hoe je efficiente dashboards kan ontwerpen. Hieronder geven we aan hoe eenvoudig het is om een dashboard in te voeren in een bestaand excel model. Het is een zeer eenvoudige manier om je manager te verrassen met een krachtig beslissingstool!

In deze post geven we 5 basis principes die cruciaal zijn om een dashboard te maken voor een bestaand excel workbook zodat efficient kan gerapporteerd worden.

1. Weet wat je wil: waarom een dashboard?  wie zal het gebruiken? wie kan data interpreteren?

2. Denk output: vergeet niet dat je bestaande model een tool is om te helpen bij een bepaald probleem/beslissing. Wat wil je eigenlijk echt weten?

3. Gebruik black box methode: splits je excel op in duidelijke model inputs, model outputs (dashboard) en het model dat de berekeningen uitvoert.

4. Test test test test: we kunnen het niet vaak genoeg herhalen: test de resultaten die uit het dashboard komen tot je voldoende comfort hebt.

5. Verhoog gebruiksvriendelijkheid:  zorg ervoor dat het dashboard kan gebruikt worden door de iets minder tech-savvy collega.

Excel interface: Contextuele menu – afhankelijke dropdown menu’s

Uit verschillende reacties en commentaren blijkt dat er interesse is om ‘contextual’ menus te creeëren in excel.

Wel het kan en het is niet eens zo moeilijk!!!

“Contextual” menus zijn menus die veranderen afhankelijk van andere data input in de sheet. Een voorbeeld maakt het onmiddellijk duidelijk: stel je hebt een sheet waarbij je een een stuk taart wil beschrijven aan de hand van de kleur, de vorm en de smaak. Voor elk van deze eigenschappen heb je een menu van mogelijkheden. Je wil nu echter ervoor zorgen dat als de gebruiker de optie ‘kleur’ zou kiezen, het smaakmenu verschijnt en voor de optie ‘vorm’, het vormmenu, enz.

Hieronder wordt dit voorbeeldje uitgewerkt. Je kan het ook vinden in de lijst om te downloaden.

Hoe 2 tabellen linken met Excel? Vlookup!

Probleem: Je bent bezig met het opbouwen van een spreadsheet in excel, maar je stelt vast dat je gegevens nodig hebt die reeds in een andere tabel beschikbaar zijn. Domweg werken met een copy/paste lijkt een tijdrovend werkje. Daarenboven kan je opnieuw beginnen als de gegevens van de andere tabel veranderen. Er is een veel elegantere manier: link beide tabellen aan elkaar en klaar.

We heben het voorbeeld dat hieronder wordt uitgewerkt in de lijst gezet, zodat je het kan downloaden.

Excel printen: Automatische kolom selectie voor print layout

Probleem: Je hebt een excel sheet opgebouwd met tientallen verschillende kolommen, alleen te veel om proper te kunnen printen. Voor presentaties wil je snel een voorgeformateerde template kunnen printen. Kolommen verbergen vind je maar een gedoe en daarbij komt nog dat het niet altijd dezelfde kolommen zijn die geprint moeten worden.

Oplossing: Definieer je print layout en maak het mogelijk om je kolommen met een dropdown menu snel te kiezen… en het neemt maar enkele minuten om op te zetten.