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

We gebruiken in deze functie een range parameter, een verwijzing naar een cel 'waar we iets mee gaan doen'.

Opdracht: Excel heeft een formule om getallen op te vullen, de formule tekst(), =TEKST(A1;"000") geeft dan als resultaat als er 2 in cel A1 staat 002.
Willen we 200 of A000 dan moeten we op een andere manier te werk gaan, waarschijnlijk is er binnen Excel (een combinatie van) functies om dit te bewerkstelligen maar op het moment dat ik een dergelijke functie nodig had ben ik hem gaan bouwen:

Opdracht: Maak een formule met de volgende parameter: een cel als input,  het karakter op waar je mee wilt op- / uitvullen, de gewenste lengte en de kant waar je hem op wilt vullen (links / rechts).
   

• Toets ALT-F11   
• Kies Invoegen Module   
• Plak de volgende code: 

Als we nu in cel A1 een A zetten dan krijgen we met de formule =Opvullen(A1;0;10;1)

A000000000

Deze functie kun je heel gemakkelijk verbouwen om zelf een bewerking op een cel waarde uit te voeren!

We gaan weer een stapje verder en laten nu het gebruik van 'Select Case' in een zelfgemaakte formule zien.

Opdracht: Excel heeft alleen een formule om het nummer van een (week)dag aan te geven dus voor maandag 1, dinsdag 2, etc   
ik wil graag een formule hebben die mij van een datum toont welke weekdag dit geweest is.   
Maak een formule om op basis van een datum de naam van deze dag te tonen.   
   

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

    
Function DagNaam(WelkeDatum As Date)    
    Dim DagNummer As Integer    
    DagNummer = Weekday(WelkeDatum, vbSunday)    
    Select Case DagNummer    
        Case 1    
            DagNaam = "Zondag"    
        Case 2    
            DagNaam = "Maandag"    
        Case 3    
            DagNaam = "Dinsdag"    
        Case 4    
            DagNaam = "Woensdag"    
        Case 5    
            DagNaam = "Donderdag"    
        Case 6    
            DagNaam = "Vrijdag"    
        Case 7    
            DagNaam = "Zaterdag"    
    End Select    
End Function    
    

In een select case kijken we steeds of de waarde die we 'afvragen' overeenkomt met een opgegeven waarde. In ons voorbeeld bepalen we dus wat de dagnaam is van 10 februari 2013

* Overigens is er gewoon een bestaande functie binnen Excel om hetzelfde te bereiken: =TEKST(B50;"DDDD")   

Doe mee met formules in Excel!

Door parameters aan onze formule toe te voegen kunnen we vanuit het Excel werkblad waarden naar de formule sturen waarmee deze moet gaan rekenen.

Klinkt complex? is het niet!

Opdracht: maak een formule om op basis van de kilometerstanden bij het tanken en het getankte aantal liters te berekenen hoeveel   

kilometer er gemiddeld per liter is gereden:   

Stappen   

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

Public Function KilometersPerLiter(StartKM As Integer, EindKM As Integer, Liter As Single)    
    KPL = (EindKM - StartKM) / Liter    
End Function   

Wat we doen is, na de formule naam (in onderstaand voorbeeld KilometersPerLiter) zetten we de parameters door een komma gescheiden tussen de haakjes.   
De bovenstaande formule bevat het volgende =KilometersPerLiter(100;500;45)   
100 is de kilometerstand bij de vorige tankbeurt, 500 bij de huidige tankbeurt, Liter is het aantal liters getankt bij de vorige tankbeurt.   

Let er hier op dat je bij de formule in excel ; gebruikt waar je bij het maken van de functie , gebruikt tussen de parameters

De uitkomst van de formule is: 8,888889313   

As Integer/ As Single?

Bij de parameters geven we aan wat voor soort waarde we verwachten, voor StartKM willen we een heel getal (Integer), hetzelfde geldt voor EindKM maar voor liters willen we een getal achter de komma aangeven (Single)
Excel zal een foutwaarde geven als je toch probeert om een tekst met quotes (") als paramter mee te sturen:

=KPL("honderd";500;45) --> #WAARDE

Meer hierover vindt u op deze pagina

Excel formules maken kan iedereen!

Om een zelfgemaakte formule te maken doe je het volgende:

• Toets je ALT-F11 vanuit je Excel werkblad.
• Kies nu bovenaan in het menu voor 'Invoegen - Module'

Een voorbeeld van een simpele formule die een cel vult met een vaste tekst is:

Public Function simsalabim()
   simsalabim = "pilatuspas"
End Function 

Merk op dat na de opening “Public Function” de naam van de functie komt (simsalabim)
Ergens in de functie moet dan altijd een waarde toegekend worden aan de naam van de functie: simsalabim = "pilatuspas"

Dit lijkt misschien wat moeilijk maar het komt er eigenlijk gewoon op neer dat de laatste zin in je functie een waarde moet geven aan (de naam van) je functie.

We doen er nog een, we maken een formule 1 + 1

Public Function EenPlusEen()
    EenPlusEen = 1 + 1
End Function

Eigenlijk doen we alles in 1 regel, we wijzen de uitkomst van 1 + 1 toe aan de formulenaam 'EenPlusEen'.

Gebruiken we deze zelfgemaakte formule in Excel dan ziet dit er als volgt uit:

Excel formules maken kan iedereen!

[De wizard is niet langer inbegrepen in Excel 2010. Daar is hijvervangen door de functies Som.Als en Sommen.Als]

Een geweldige wizard die nog voor velen onbekend is gebleven, net als de Wizard Opzoeken!
Hiermee kun je op een eenvoudige manier formules maken om gegevens te zoeken in een bereik met behulp van een andere bekende waarde in het bereik.

De invoegtoepassing moet geïnstalleerd zijn. Is deze nog niet geïnstalleerd, volg dan de volgende stappen:

Excel 2003

Kies Extra - Invoegtoepassingen - Vink Wizard voorwaardelijke som aan

Excel 2007

1.Klik op de Microsoft Office-knop en klik achtereenvolgens op Opties voor Excel en op de categorie Invoegtoepassingen.
2.Klik in het vak Beheren op Excel-invoegtoepassingen en klik op Zoeken.
3.Schakel in het dialoogvenster Beschikbare invoegtoepassingen het selectievakje naast Wizard voorwaardelijke som in en klik op OK.
3.Volg de aanwijzingen in de wizard.

Gebruik

Kies Extra – Opzoeken (Excel 2003) of (Excel 2007) het tabblad Formules, groep Oplossingen en klik op Opzoeken.

Wizard Scherm 1: Selecteer het bereik waarop je de ‘wizard voorwaardelijke som’ op los wil laten, dit is altijd het bereik inclusief de beschrijvende kolomcel / label.
 

Wizard Scherm 2: Geef in het vakje ‘Op te tellen kolom:’ aan van welke kolom de waarden opgeteld moeten worden en geef daaronder aan aan welke voorwaarde een bepaalde kolom moet voldoen:

 Wizard Scherm 3 en 4: Geef aan of je alleen de formule wil zien of dat je ook de voorwaardelijke waarde af wil beelden (verander je dan ‘paul’ in ‘peter’ dan veranderd de uitkomst gelijk mee!)

Een geweldige wizard die nog voor velen onbekend is gebleven!
Hiermee kun je op een eenvoudige manier formules maken om gegevens te zoeken in een bereik met behulp van een andere bekende waarde in het bereik.

De invoegtoepassing moet geïnstalleerd zijn. Is deze nog niet geïnstalleerd, volg dan de volgende stappen:

Excel 2003

Kies Extra - Invoegtoepassingen - Vink Wizard opzoeken aan

Excel 2007

1.Klik op de Microsoft Office-knop en klik achtereenvolgens op Opties voor Excel en op de categorie Invoegtoepassingen.
2.Klik in het vak Beheren op Excel-invoegtoepassingen en klik op Zoeken.
3.Schakel in het dialoogvenster Beschikbare invoegtoepassingen het selectievakje naast Wizard Opzoeken in en klik op OK.
3.Volg de aanwijzingen in de wizard.

Gebruik

Kies Extra – Opzoeken (Excel 2003) of (Excel 2007) het tabblad Formules, groep Oplossingen en klik op Opzoeken.

Wizard Scherm 1: Geeft het totale bereik op waarop de wizard moet worden uitgevoerd (Mb.v. Control * selecteer je de hele tabel) d.w.z. geef hier het totale gebied op waar je in wilt zoeken:
 

Wizard Scherm 2: Geeft de kolom aan waarvan je de waarde op wilt zoeken

En geef de rij(waarde) aan waarop je wilt zoeken.

Wizard Scherm 3: Geef aan of je alleen de uitkomst weer wilt geven in een cel of dat je de zoekparameters ook wil laten zien.
Kies je voor het laatste, dan kun je d.m.v. het veranderen van deze parameters eenvoudig je zoekopdracht aanpassen in het werkblad!
 

Wizard Scherm 4: Geef als laatste aan in welke cel je de uitkomst wil laten zien (heb je ‘formule en zoekparameters aangegeven bij stap 3 dan is dit stap 4 van 6, de volgende schermen zien er hetzelfde uit).


 

[De wizard is niet langer inbegrepen in Excel 2010. Daar is hijvervangen door de wizard Functie / functies Opzoeken en Verwijzing]

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...

Een veel voorkomende foutmelding is #DEEL/0! we willen hier 'door 0 delen'. Dit komt voor wanneer je een getal wilt delen door een cel met de waarde 0 of door een lege cel.
Een werkblad vol met deze foutmeldingen staat slordig, zeker als je dit bestand wilt delen of al wilt drukken.
In onderstaand voorbeeld delen we steeds de Afzet van een kwartaal in 2012 door de (nieuwe) afzet in 2013, omdat we voor Q4 nog geen afzet hebben toont Excel de foutmelding #DEEL/0!

Je kunt deze formule voorkomen door gebruik te maken van de formule ALS()
Als we bijvoorbeeld in een aantal cellen 1000 delen door de waarde van een andere cel en deze cel bevat niet altijd een waarde dan gaan we als volgt te werk:

 =ALS(ISFOUT(I3/E3);0;I3/E3)

De ALS-functie kijkt nu eerst of de uitkomst van de deling een fout oplevert (ISFOUT). Als dat het geval is wordt gewoon een 0 ingevuld. Wanneer de deling geen fout oplevert wordt deling echt uitgevoerd.
Copy / Paste deze formule door naar alle relevante cellen van je werkblad.