Om onze excelblog op een nog professionelere manier te kunnen uitbouwen, gaan we vanaf nu verder met de excel tips en tricks op onze nieuwe website:

www.exceltips.be

De eerste dagen zullen wij volop bezig zijn om de huidige posts perfect over te brengen naar onze nieuwe webstek, na een week zullen we zeker en vast terug nieuwe posts uitwerken!

Floris en Steven

Oplossing: Ook hier: conditional formatting op basis van een formule.

Zoals je ziet in de afbeelding hierboven, zullen we op basis van de waarde in cel J2 ervoor zorgen dat je de gegevens in kolommen G en H wel of juist niet te zien krijgt.

Hiervoor selecteer je alle cellen die je wil onzichtbaar maken dmv deze truuk: in dit geval van G1 tot H14 (G1:H14). Daarna ga je naar “Conditional Formatting” en klik je op “New rule…”.

Selecteer dan “Use a formula to determine…”

Vul de volgende formule in:

=$J$2=0

Als formaat zorg je ervoor dat je de tekst in het wit zet (of de tekst dezeflde kleur als de achtergrond).

Tweemaal “Ok” klikken en klaar is kees! Als je nu cel J2 van waarde verandert (1 of 0) zal je zien dat de waarden respectievelijk zichtbaar en onzichtbaar zijn.

Oplossing: Conditional formatting op basis van een formule!

Om dit te bereiken gaan we gebruik maken van de COUNTIF() functie. De COUNTIF() functie zal in een range het aantal cellen tellen dat gelijk is aan een bepaalde waarde. Ze heeft dus 2 argumenten: een range (waarin geteld wordt) en een voorwaarde.

We willen dus een waarde in de tabel inkleuren als ze voorkomt in de lijst met referentiewaarden (List).

We selecteren daarom alle waarden van de tabel. In de selectie zal 1 actieve cel te zien zijn (deze is binnen de selectie lichter gekleurd). Als je de tabel selecteert vertrekkende van cel A2, zal cel A2 de actieve cel zijn. Dit is belangrijk voor ’t ingeven van de formule, zometeen.

Selecteer “New Rule…” en klik op “Use a formula to…”

Geef de volgende formule in:

=COUNTIF($B$13:$B$16;A2)

Noteer dat we hier als voorwaarde de actieve cel hebben ingegeven. Deze formule geeft dus het aantal keren dat waarde A2 in het lijstje ‘List’ voorkomt. U begrijpt dat dit enkel 1 of 0 kan zijn (aangezien we ervan uitgaan dat ’t lijstje unieke waarden bevat).

In excel staat 1 voor TRUE en 0 voor FALSE. Als de formule dus 1 als uitkomst heeft (= de waarde komt dus voor in ’t lijstje) zal het voorwaardelijke formaat worden toegepast.

Klik op “Format…” en selecteer een formaat voor deze cellen (bijvoorbeeld een lege achtergrond). Klik 2 maal op “Ok” en klaar is kees!

Oplossing: Via conditional formatting je rijen afwisselend blanco en een kleurtje geven.

More after the jump!

Om dit te kunnen doen is het belangrijk om even een inleiding te geven over de MOD() functie en de ROW() functie.

1. De MOD() functie geeft de restwaarde bij een deling. Herinner u hoe er vroeger in de lagere school gerekend werd: 5 delen door 2 geeft als uitkomst 2 met restwaarde 1. Analoog geeft MOD(5;2) waarde 1 als uitkomst:

Hier zit ‘m nu net de truuk. Als we het rijnummer delen door 2 zullen we door middel van de MOD() functie steeds 1 of 0 uitkomen. 1 of 0 staat voor TRUE of FALSE.

Als we nu conditional formatting gaan toepassen obv de MOD() functie zal het formaat (bv. een grijze achtergrond) worden toegepast als de functie 1 geeft als uitkomst (TRUE) en niet worden toegepast in het geval van een 0.

2. De ROW() functie geeft als resultaat het rijnummer. Typ in eender welke cel =ROW() in en je krijgt als resultaat het rijnummer van de bewuste cel.

Dus: we selecteren heel het gebied waar we de formattering op willen toepassen

We maken een nieuwe ‘rule’ aan onder ‘conditional formatting’ in de Home tab.

We klikken op “Use a formula…”

En vullen volgende formule in: =MOD(row();2)

Daarna klikken we op de knop “Format…” en zorgen ervoor dat we de formattering kiezen die we willen (in dit geval de grijze achtergrond).

We klikken 2 keer op “Ok” en klaar is kees!

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.

Laten we teruggaan naar het voorbeeld van de vorige blog: hierin koppelen we de tabel klanten met de tabel producten (en meer bepaald de prijs):

Een analyse van de formule in verschillende stappen:

1. We zoeken $B4 ofwel ‘product code’ 12345-26

2. De tabel waarin we zoeken is A1 tot D13 in de prijslijst sheet:

3. We wensen de prijs terug te vinden, dus kiezen we voor kolom D en dit is de 4de kolom

Het probleem is dat als we in de tabel ‘prijslijst en kolom zouden toevoegen, dan zal je zien dat het resultaat niet meer klopt:

Kolom 4 uit de vlookup functie verwijst nu nog steeds naar kolom D, nu echter de voorraad in plaats van de prijs.

In de plaats van de referentie van de kolom hardcoded in te geven, kunnen we ze ook eenvoudig laten berekenen door volgende functie:

match(“prijs (EUR)”;prijslijst!$A$1:$E$1;0)

Opgelet: de (unieke) naam van de header rij wordt hierdoor wel zeer belangrijk

Als we vervolgens deze functie in de vlookup innesten, zijn al onze problemen opgelost.

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)

0. Voorbereiding

Het model ziet er zo uit:

1. Weet wat je wil

We willen een eenvoudige tool die ons snel en gemakkelijk de verschillen tussen de aankoop van een auto en het leasen van de wagen visueel duidelijk maakt.

Het is trouwens absoluut NIET de bedoeling om een debat op te starten of het model correct zou zijn opgesteld en hoe een leasing contract juist berekend wordt!

2. Denk output

We willen aan de hand van enkele parameters inzicht krijgen in:

• Vergelijking van de geactualiseerde huidige waarde (NPV) van de 2 alternatieven: Dit is de belangrijkste output voor een investeringsbeslissing. We opteren om de numerieke gegevens te gebruiken
• Belangrijk om de resulterende cash-flows te analyseren om de impact op de liquiditeit te bepalen. We kiezen de bar-chart per maand.
• Hoe verandert de huidige waarde als functie van de interestvoet. Sensitiveitsanalyse, we kiezen voor een lijngrafiek met op de x-as de interestvoet en op de y-as de huidige waarde van de huidige kost. De interestvoet varieert van 5% tot 15% (per procent).

3. Gebruik de black-box methode

Schematisch weergave van de black-box methode:

Definities:

• Model parameter:
  • Verkoopsbelasting op auto’s

• Model input aankoop:
  • Officiële prijs
  • Onderhandelde prijs
  • Verkeersbelasting
• Model input Lease:
  • Lease periode (maanden)
  • Verkoopswaarde aan het eind van de lease
  • Interest voet (APR)
  • Maandelijkse betaling voor lease

• Model output:
  • NPV van totale kost van aankoop wagen
  • NPV van totale kost van de lease van de auto
  • Tabel met betaling per maand in aankoop scenario
  • Tabel met betaling per maand in lease scenario
  • Tabel met NPV van de totale kost van de lease van de auto voor de verschillende interestvoeten

Hoe?

1. Creeër een nieuwe sheet ‘IN-PARAM’

2. Link de inputs door naar het model

3. Creeër een nieuwe scheet ‘OUT’

4. Link de verschillende output door naar sheet (opmerking: om de sensitiviteitsanalyse gemakkelijk te kunnen opstellen, maken we gebruik van de functie ‘table’, excel laat echter niet toe dat input en output op verschillende sheet staan, hierdoor zijn we verplicht om de tussenberekening op de sheet ‘IN-PARAM’ uit te voeren)

5. Creeër een nieuwe sheet ‘Dashboard’

6. Ontwerp het dashboard en maak de verschillende grafische ouput voor het dashboard

4. Test test test test

Uiteraard belangrijk, vermits het hier over een eenvoudig model gaat, is het relatief gemakkelijk om te controleren of het resultaat overeenkomt met de resultaten van het oorspronkelijk model. Het valt redelijk veel voor dat er hierdoor bugs in het oorspronkelijke model bovenwater komen.

5. Verhoog gebruiksvriendelijkheid – user interface

Vermits de sheets ‘OUT’ en ‘Blackbox’ eigenlijk enkel tussen berekening bevatten, kunnen we die gemakkelijk verbergen voor de gebruiker.

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.

1. Weet wat je wil

Zonder hier te lang te blijven stilstaan, toch een aantal bemerkingen die belangrijk zijn als je met dashboards begint.

Dashboards zijn een krachtige manier om data te consolideren en voor te stellen. Het expliciete doel ervan is om trends duidelijk te maken en beslissingen te vergemakkelijken. Het interpreteren van de dashboard is daarbij een must.

Als bijvoorbeeld de verkoopscijfers van een bepaald product plots spectaculair zouden dalen, zal dit zeer duidelijk en objectief weergegeven worden in het verkoopsdashboard. Wat de gepaste actie is daarentegen hangt af van vele andere factoren (zijn andere producten gestegen? is er een probleem in de markt? is er een probleem met de verkopers?  kwaliteitsproblemen?  heeft de concurrentie een nieuw product gelanceerd?  enz…).

Hieronder een checklist van een aantal aspecten die mogelijks belangrijk kunnen zijn:

• Model gebruikers: wordt het dashboard geprint en gebruikt als schriftelijk rapportering of gaan gebruikers het excel model zelf gebruiken? wie bedient de knoppen?
• Dashboard gebruikers: wie krijgt het dashboard te zien? Is het nuttige informatie voor hun?
• Updates: Is het nodig om parameters aan te passen (waarschijnlijk wel), wie doet dit? Hoe frequent?
• Veiligheid: Ze maken als dusdanig bedrijfsdata zeer transparant. Het is uiteraard niet wenselijk dat deze informatie in te verkeerde handen valt.

Uiteindelijk is het belangrijk dat je weet wat je wil. Daarbij moet wel opgemerkt worden dat excel modellen dikwijls overgelaten worden aan de experten – management heeft meestal geen interesse of tijd om er zich in te verdiepen. Dit is NIET het geval met Dashboard, je zult merken dat ze dit zeer ernstig nemen. Het is dus wijzelijk om ze er vanin het begin bij te betrekken.

2. Denk output

Weten wat je wilt is zeer belangrijk bij het opzetten van dashboards. Daarbij moet wel opgemerkt worden dat het opbouwen van excel modellen dikwijls overgelaten worden aan de experten – management heeft meestal geen interesse of tijd om er zich in te verdiepen.

Dit is NIET het geval met Dashboard, je zult merken dat management en directie dit zeer ernstig nemen. Het is dus wijzelijk om ze er vanin het begin bij te betrekken.

Hierbij is het belangrijk om te redenen in output en resultaten. Vergeet even de complexiteit van het excel model waarop je zo gezwoegd hebt. Organizeer een brainstorm met de verantwoordelijke manager en probeer die ene vraag te beantwoorden: wat willen we eigenlijk weten (meten)?

Maak een lijst met mogelijke outputs en beslis hoe je die het best kan weergeven. Sommige resultaten kan je best numeriek weergeven, andere misschien best in een grafiekje, andere dan weer als een verklikker lichtje…

Als je die lijst hebt (en je hebt die afgecheck met de verantwoordelijke manager(s)), kan je je dashboard beginnen ontwerpen!!

Uiteraard moeten al die outputs nog correct uit het model gehaald worden.

3. Gebruik de black-box methode

Schematisch weergave van de black-box methode:

Definities:

• Model parameter: numerieke waarden die in het model gebruik worden, maar slecht zeer zelden veranderen. (voorbeelden zijn bv. conversieparamters (bv 0.304 8m/ft), interne transfer prijzen, … Het is een goede gewoonte om numerieke gegevens strikt te scheiden van formules, zeker indien ze kunnen veranderen.
• Model input: numerieke waarden die in het model gebruikt worden en op regelmatige tijdstippen geactualiseerd moeten worden om tot een correct resultaat te komen.
• Model output: numerieke waarden die het resultaat zijn van berekeningen van het model.

Het gebruik van de black-box methode heeft als groot voordeel dat het compatibel is met externe dashboard toepassingen die commercieel op de markt te vinden zijn (ze zijn gebaseerd op het inlezen (mapping) van gegevens).

In de praktijk is de black-box methode extreem eenvoudig, hoewel het een zekere dosis discipline vereist.

• Organizeer inputs in verschillende (logisch opgebouwde) input sheet en/of forms. Indien nodig (maar vermijd indien mogelijk) koppel ze aan externe data bronnen (database, andere excel sheets…).
• Organizeer model parameters in aparte sheet(s) – het verschil tussen inputs en model parameters kan soms zeer klein zijn, belangrijk is dat GEEN ENKELE INPUT RECHTSTREEKS INGEVOERD WORDT IN HET MODEL!
• Alle berekeningen van het model gebeuren in aparte sheet(s) – afhankelijk van de complexiteit van het model kan dit over meerdere sheets verspreid zijn.
• Verzamel (link door of bereken locaal) alle outputs in aparte (sheets)
• Creeer Dashboard sheet(s) waarbij de outputs gebruik worden voor de rapportering (met grafische voorstelling)

De sheets een duidelijke naam geven helpt om het overzicht te bewaren (bv. voor input sheets: IN – prijslijst) en waarom de tabs geen kleur geven afhankelijk van het type sheet.

4. Test test test test

Het kan niet genoeg benadrukt worden: test je dashboards tot je voldoende vertrouwen hebt dat alles goed doorgelinkt en berekend is.

Hoewel er boeken zijn volgeschreven over model tests en data integriteit, zeker voor zeer complexe modellen, zijn er een aantal eenvoudige en beproefde methoden beschikbaar:

• Vergelijken met andere berekeningsmethoden (bv. handmatig, andere modellen, orde grootte berekeningen…)
• Veranderen van inputs en voorspellen output en dan vergelijken van resultaten met de voorspelling
• Testen van extreme inputs (bv. zet alle inputs ’s op 0, wat gebeurt er?)
• Opstellen van een testprogramma

5. Verhoog gebruiksvriendelijkheid – user interface

Er zijn verschillende redenen om de gebruiksvriendelijkheid van het model te verhogen:

• Eenvoudige en prettiger om ermee te werken
• Vermijd fouten door een ondoorzichtig kluwen van sheet, formules, tabellen enzo
• Makkelijker voor de niet-ervaren gebruiker
• Stuur de gebruikersmogelijkheden – maak het idiot proof

Hoewel we er in verschillende andere posts dieper op ingaan, hier alvast enkele nuttige tips:

• Gebruik kleurcodes  (vb. input cellen in blauw; formules in het zwart, formules die verwijzen naar andere sheet in italic…)
• Hide alle sheets waarin berekeningen gemaakt worden  –  toon enkel input en output sheets
• Gebruik user interface mogelijkheden van excel zoals forms met button, scroll bars,…

Oplossing: gebruik conditional formatting op basis van een formule!

Hoe doen we dit nu?

1. Selecteer de cellen waar je de formattering op wil toepassen (D2:D20).

2. Klik op “Conditional Formatting” en dan op “New Rule”.

3. Klik op “Use a formula…”

4. Tik de volgende formule in: “=iserror(d2)”. D2 gebruik je als cel, omdat dit nu de actieve cel is in de selectie.

5. Kies de formattering die je wil: in dit geval dezelfde kleur voor de tekst als de kleur van de achtergrond.

6. Klik 2 maal “OK” en klaar!

Was dit niet duidelijk? Heb je andere vragen? Laat een bericht na in de comments!

Technorati : conditional formatting excel interface
Del.icio.us : conditional formatting excel interface
Zooomr : conditional formatting excel interface
Flickr : conditional formatting excel interface

Oplossing: Zorg ervoor dat je grafieken in een cel maakt! Zo ziet ’t eruit:

Ik geef hier de score voor “Free Market” weer met een grafiek. Deze staafdiagrammen zijn gemaakt met de REPT() functie. Met de REPT() functie kan je een bepaald karakter een aantal keer achter elkaar weergeven. Bijvoorbeeld REPT(“X”,10) wordt dan “XXXXXXXXXX”. De truuk voor de staafdiagrammen is om te werken met het “|” karakter. Als je de cel met deze diagrammen dan formatteert met een voldoende klein lettertype, ziet de grafiek eruit zoals hierboven. Hier is de formule die eracher zit:

Nu, wat is ’t nut van zo’n staafdiagram in een cel? Het is bijvoorbeeld gemakkelijker om data voor lange lijsten op deze manier weer te geven. Een gewone staaf-grafiek zal al gauw onoverzichtelijk worden en de waarden op de assen worden dan niet altijd goed weergegeven. Ook voor een licht dashboard is dit erg handig!

Samen met conditional formatting (vb. een andere kleur geven aan het minimum en het maximum van de waarden) is dit een erg krachtige tool om data te representeren!

Oplossing: een combinatie van de IF() functie en de COUNTIF() functie.

Je hebt een lijst van records uit een database: in kolom 1 staan bijvoorbeeld namen, hier ga je de dubbels eruit halen.

Gebruik hiervoor de COUNTIF() functie: we gaan door de lijst lopen van het eerste naar het laatste record (van boven naar beneden) en tellen hoeveel keer een waarde tot dan voorkomt. Is dit 1 keer, wil dit zeggen dat de waarde tot dan uniek is, is het meer dan 1 dan wil dit zeggen dat de waarde ervoor al voorkwam en dus gedelete mag worden.

Het enige dat we nu nog moeten doen is een filter opzetten, en filteren op “Delete”.

Selecteer nu gewoon deze rijen, rechtsklikken en op ‘Delete Row’ klikken.

Als je nu de filter afzet, zal je zien dat je tabel ontdubbeld is!

Is dit niet duidelijk? Heb je andere vragen? Laat gerust iets weten in de comments!

Technorati : countif duplicates excel tutorial
Del.icio.us : countif duplicates excel tutorial
Zooomr : countif duplicates excel tutorial
Flickr : countif duplicates excel tutorial