Vanuit de 'bouw hoek' kreeg ik een vraag of het mogelijk was om aan de hand van een startdatum, een aantal werkbare dagen en een lijstje met feestdagen bij ingave van een datum in cel A de berekende einddatum van het project in cel B te krijgen.

Hiervoor bestaat in Excel de handige functie =WERKDAG. 

Download hier het voorbeeld Excel bestand

In cel B1 zetten we de startdatum.

In cel B2 het aantal werkbare dagen.

In G2:G20 een lijstje met feestdagen.

Ik kreeg recentelijk de volgende vraag: 'Ik wil eigenlijk automatisch een msgbox laten verschijnen als de waarde in cel B1 ingevuld is en cel A1 leeg is. Is dit mogelijk?'

In dit geval ging het er om dat in zijn Excel bestand een werknemersnummer werd ingevuld, zonder dat er een naam van een werknemer ingevuld was!

Ik heb toen 2 mogelijkheden uitgewerkt:

VBA

DOWNLOAD HIER VOORBEELD BESTAND 1

M.b.v. een klein stukje VBA code kunnen we afdwingen dat er gekeken wordt na een 'change' van het werkblad of A2 leeg is en B2 niet, in dat geval wordt er een melding getoond:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Range("B2").Value <> Empty And Range("A2").Value = Empty Then

  MsgBox "vul een naam in!", vbCritical, "waarschuwing"

End If

End Sub

Bovenstaande code controleert slechts 1 cel combinatie (A2 / B2). Als we dit uit willen breiden naar bijvoorbeeld 10 regels, vervang dan bovenstaande door:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim MyRange As RangeDim currentCellDim nextCellDim i As Integer

Set currentCell = Range("A2")

Do While i < 10   

Set nextCell = currentCell.Offset(0, 1)   

If currentCell.Value = Empty And nextCell.Value <> Empty Then    

  MsgBox "vul een naam in!", vbCritical, "waarschuwing"   

End If        

Set currentCell = currentCell.Offset(1, 0)    

i = i + 1

Loop

End Sub  

Gegevensvalidatie

DOWNLOAD HIER VOORBEELD BESTAND 2

Een alternatieve manier zonder VBA is het gebruik van Excel gegevensvaldiatie. Ga op de cel staan waar je de validatie op uit wilt laten voeren (in ons voorbeeld B3) en kies op het lint, het tabblad 'GEGEVENS' --> 'Hulpmiddelen voor gegevens' --> 'Gegevens validatie.

Bij 'INSTELLINGEN' kiezen we bij 'TOESTAAN' voor 'AANGEPAST' en bij de formule geven we op:

=(EN(EN(ISLEEG(A3)=ONWAAR;ISLEEG(B3)=ONWAAR);LENGTE(B3)>=6))

'Lege cellen negeren' laten we 'uit'.

Met Copy / Paste trekken we dit door naar alle cellen die we willen valideren. Zoals je misschien al hebt gezien controleert deze validatie ook nog of de lengte van de code die we opgeven minimaal 6 posities is!

Als we nu in het voorbeeld bestand een code opgeven zonder naam of een code die < 6 tekens is dan 'triggert' dat de validatie:

In veel ERP systemen is een telefoonnummer veld vaak een 'simpel' tekst veld, zonder enige vorm van validatie. We zien telefoonnummers in het formaat 0182345678, 0182-345678, +31182345678, 0182-34.56.78, '0182345678, nummers met spaties, etc. Ik kreeg recentelijk een verzoek of daar in Excel iets mee te doen is, iemand had een download uit zijn systeem en wilde hier de telefooncentrale mee 'voeden'. Bijkomende requirement was dat de kolom landcode NL, BE of leeg kon zijn, indien leeg moest er uitgegaan worden van een NL landcode.

Met een simpele 'vervangen' functie in Excel kom je  er niet, ik moest dus kijken of ik een VBA functie kon maken ;)

Ik heb een kolom met telefoonnummers, een kolom met de landcode en een (hulp) tabel waarin ik achter de landcodes de internationale toegangsnummers / prefix zet.

De functie die ik gemaakt heb heet StripEnInternationaliseer (What's in a name..):

De functie heeft 3 parameters:

MyRange bevat de cel met het nummer wat we om willen zetten.
MyLandcode bevat de cel met de landcode
MyLandCodeRange bevat de cellen range waarin alle landcodes met hun toegangsnummers staan

Public Function StripEnInternationaliseer(MyRange As Range, MyLandcode As Range, MyLandcodeRange As Range)Dim nTmpVal As StringDim nLandCode As StringDim nPrefix As String

nTmpVal = Replace(MyRange.Value, ".", "")nTmpVal = Replace(nTmpVal, "-", "")

'FF omzetten naar integer omdat we een nummer willen.. If IsNumeric(nTmpVal) Then       nTmpVal = CStr(Int(nTmpVal))End If

'LandcodenLandCode = MyLandcode.ValueIf MyLandcode = "" Then       nLandCode = "NL"End If

'Prefix opzoeken o.b.v. landcode nPrefix = Application.WorksheetFunction.VLookup(nLandCode, MyLandcodeRange, 2, False)

'Samenvoegen resultaten nTmpVal = nPrefix + nTmpVal

'En teruggeven aan VBA functie StripEnInternationaliseer = nTmpVal

End FunctionDOWNLOAD het bestand HIER

Veel mensen laten de Excel sajblonen links liggen, dit terwijl ze erg handig en zeer informatief zijn.
Waarom het wiel opnieuw uitvinden ofwel waarom met een leeg werkblad beginnen als er standaard een Excel sjabloon beschikbaar is waarin hetgeen je in je hoofd hebt al zit?

Kies ‘Bestand – Nieuw’ en er worden al een groot aantal sjablonen getoond, variërend van onkostendeclaraties tot  kant en klare kalenders.

Dit scheelt je echt uren werk!

Verder kun je hier online zoeken via de textbox: ‘Sjablonen zoeken in Office.com’. Ben je als leraar op zoek naar een aanwezigheid overzicht van je leerlingen in Excel, dan hoef je dat niet helemaal opnieuw te bouwen maar is dit gewoon via ‘zoeken’ online te vinden!

Het ‘neuzen’ in deze sjablonen leert je ook heel veel, je ziet hoe veel formules in de praktijk gebruikt worden en op basis van het doel van de template komen de formules ook veel duidelijker over.

Zelf een sjabloon maken

Als je een Excel werkblad hebt gemaakt die je veelvuldig gaat gebruiken kun je hier heel eenvoudig een sjabloon van maken.
Kies ‘Bestand – Opslaan Als’, kies in het vak ‘opslaan als’: Excel sjabloon of Excel sjabloon met macro’s .

Als je nu ‘Bestand – Nieuw – Mijn sjablonen’  kiest dan zie je je zojuist opgeslagen sjabloon er tussen staan!

Sparklines in Excel zijn eigenlijk een soort van 'mini grafiekjes' die je gemakkelijk naast een tabel kan plaatsen om bijvoorbeeld een ontwikkeling / tendens in omzet te benadrukken:

Opdracht:

We hebben een tabel waarin de omzet over 2012 getoond wordt met damesfietsen, herenfietsen en kinderfietsen.

Na de laatste kolom willen we een sparkline tonen die de ontwikkeling in omzet accentueert.

Stappen:
Tabblad Invoegen - Sectie Sparklines - Kies bijvoobeeld Lijn:

 Geef bij Gegevensbereik B4:M4 in (de hele regel voor damesfietsen) en bij Locatiebereik / waar zet ik de sparklinegroep neer N4

Herhaal dit voor de herenfietsen en de kinderfietsen, LET OP: Veel gemakkelijker is nu om met CTRL-C / CTRL-V (Kopiëren / Plakken) de herenfietsen en kinderfietsen te doen!

De sparklines worden nu achter de tabel getoond:

Als we op de sparklines gaan staan kunnen we nu kiezen voor het tabblad Ontwerpen.
In onderstaande afbeelding hebben we bij de sparkline voor de damesfietsen gekozen voor Kolom en hebben we Hoge punt aangevinkt waardoor we zien dat de hoogste maand augustus een andere kleur krijgt:

Met sparklines geef je je Excel werkblad meer het idee van een echt dashboard!

Download het voorbeeldbestand HIER

Verticaal zoeken is 1 van de meest gebruikte formules in Excel!
Waarom? Omdat je met deze formule heel eenvoudig waarden in gegevenslijsten terug kan vinden. We laten nu aan de hand van een simpel voorbeeld zien hoe verticaal zoeken in Excel nu precies werkt zodat u het ook onder de knie krijgt.

Opdracht zoek in onderstaande tabel het percentage stemmen dat de PVDA gehaald heeft in de groep 75 jaar en ouder

In de cel onder ‘Partij’ staat de volgende formule:
=VERT.ZOEKEN(A14;A5:H11;8;ONWAAR)

Stap voor stap is dat:

• Zoek A14 = PVDA
• Zoek in het gebied A5:H11 dat is de hele tabel inclusief kolomlabels (groenlinks, D66, etc)
• Laat als de zoekwaarde gevonden is de 8E kolom terug van de regel waarin de zoekwaarde gevonden is zien. Had hier 1 gestaan dat kregen we als uitkomst de kolomlabels: groenlinks, D66, etc. Nu staat er 8 en krijgen we dus de 8e kolom terug als uitkomst.
• Zet benaderen op ‘ONWAAR’ zodat alleen iets wordt getoond als de zoekwaarde (pvda) exact is gevonden.

Om de laatste parameter ‘benaderen’ te verduidelijken wordt in het voorbeeld hieronder dezelfde formule gebruikt, echter met het veld benaderen op ‘WAAR’
=VERT.ZOEKEN(A29;A21:B25;2;WAAR)

Hierdoor wordt eigenlijk tegen de formule gezegd: ‘toon mij de uitkomst die het dichts tegen de zoekwaarde aanzit'. Er wordt dus gezocht op de waarde 64, die wordt niet gevonden maar de dichtstbijzijnde waarde wordt getoond = C.

Download het voorbeeldbestand HIER

Wanneer we een bestand openen waarin een 'mix' van getallen en tekst staat kunnen we natuurlijk op het gele bordje met het uitroepteken klikken en kiezen voor 'converteren naar getal'.
Als je een lange lijst moet converteren wil het nog wel eens voor komen dat je het gele bordje met uitroepteken niet 1-2-3 meer kan vinden.

Een alternatieve manier:

• Vul 'ergens' in een vrije cel een 1 in
• Selecteer deze cel en kies Kopiëren
• Selecteer de 'range' met velden waarvan u de tekst om wilt zetten naar getallen (bijvoorbeeld door de SHIFT toets ingedrukt te houden)
• Klik met de rechter muisknop op de range en kies 'Plakken Speciaal'
• Selecteer 'Vermenigvuldigen'
• Klik OK

 Kijk ook naar

In dit filmpje ziet u een gemakkelijke / alternatieve manier om tekst om te zetten naar getallen

Door variabelen in een formule te gebruiken hebben we de mogelijkheid om hier (tijdelijk) iets in te stoppen wat we later in een berekening weer gebruiken.
Het gebruik van variabelen maakt een formule leesbaarder maar bovenal flexibeler.

Hieronder een eenvoudig voorbeeld waarbij we een formule vakantiegeld maken die de som van 2 potjes met geld (de variabelen potje1 en potje2) optelt.

Stappen

• Toets ALT-F11
• Kies Invoegen Module
• Zet het volgende in de formule:

Public Function Vakantiegeld()
Dim potje1 As Integer
Dim potje2 As Integer

'We stoppen 1000 euro in potje 1
potje1 = 1000

'En 750 euro in potje 2
potje2 = 750

'Ons vakantiegeld is de som van de 2 potjes:
Vakantiegeld = potje1 + potje2
End Function

Als we vervolgens in een werkblad de formule =Vakantiegeld() zetten dan zien we de uitkomst 1750

As Integer

Je geeft bij het declareren (d.m.v. het woord Dim) altijd een gegevenstype mee wat aangeeft wat er in de variabele mag komen.
In bovenstaand verhaal is dit 'as integer' dit houdt in dat we alleen 'hele getallen' in deze variabelen kunnen parkeren.
Overigens staat Excel het volgende wel toe:

potje1 = 1000.6
De uitkomst van onze formule vakantiegeld() zou dan 1751 euro zijn, Excel rondt het bedrag af!

Excel formules maken kan iedereen!

Binnen Excel zijn er diverse kleine handige functies om uit kolommen met tekst precies datgene te krijgen wat je wilt hebben.

Functies Links() / Rechts()

Neem bijvoorbeeld de functies Links() en Rechts(), wil je uit een kolom met de postcodes van een klant alleen de hoofd code hebben, dus van 3415 PP in cel A1 alleen de 3415 dan wordt het: =LINKS(A1;4) de 4 wil zeggen 4 posities lang had hier 2 gestaan dan was de uitkomst 34!

Vind.Spec()

Wil je ‘een tekst’ splitsen op een spatie om bijvoorbeeld de voornamen uit een kolom met namen te halen dan is de meest handige / overzichtelijk manier het opdelen van de acties die nodig zijn om het gewenste resultaat te halen.
Dit klinkt moeilijker dan dat het is.
Stel we hebben 2 rijen in Excel met de namen Jan de Vries en Marijke van der Wal, we kunnen de functie =LINKS(A1;3) niet gebruiken omdat waar hier 3 staat voor de lengte dit bij JAN wel goed uit zou pakken maar bij Marijke zou de uitkomst MAR zijn..
Omdat de 3 hier dus ook net zo goed 5 of 10 kan zijn wordt stap 1 het bepalen van de lengte en dat kunnen we doen als we weten wat de positie is van de eerste spatie (tussen ‘Jan’ en ‘de’)

Waar zit de spatie?    Geef me dan de voornaam tot de spatie

Jan de Vries                        4    Jan
Piet Paulusma                     5     Piet
Henriette van de Broek     10    Henriette

In Excel gebruiken we hiervoor de functie vind.spec, in bovenstaand voorbeeld wordt dit =VIND.SPEC(" ";A2) omdat we de postie van een spatie in cel A2 willen vinden.

Als we dat weten dan is het heel eenvoudig om er naast in de cel een formule te zetten:
=LINKS(A2;C2)  wat in het geval van Jan de Vries hetzelfde is als =LINKS (“Jan de Vries”; 4)

Download het Voorbeeldbestand

Werk je in Microsoft Excel aan een uitgebreid document met veel formules, dan zal het ook wel eens gebeuren dat je niet meer precies weet hoe de formule voor een bepaalde cel in elkaar zit of waar een formule zich bevindt!
Wil je dat toch weten, selecteer dan de desbetreffende cel en druk dan op CONTROL+T. Microsoft Excel zal je dan de formule tonen in plaats van de uitkomst van die formule.

Om weer terug te keren naar de standaard weergave druk je gewoon nog een keer op CONTROL+T...