Mike Dole

Mike Dole

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!

 

 

 

 

maandag, 11 februari 2013 20:58

Zelf een formule maken 1 - De basis

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'

VBA Module invoegen

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:

Voorbeeld VBA functie

Excel formules maken kan iedereen!

maandag, 11 februari 2013 06:10

Wizard Voorwaardelijke Som

[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

Invoegtoepassing Voorwaardelijke Som

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 ExtraOpzoeken (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 Voorwaardelijke som 1

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 Voorwaardelijke som 2

 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!)
Wizard Voorwaardelijke Som 3

Wizard Voorwaardelijke Som 4



zondag, 10 februari 2013 21:34

Wizard Opzoeken

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

Invoegtoepassing installeren

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 ExtraOpzoeken (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 Opzoeken

Wizard Scherm 2: Geeft de kolom aan waarvan je de waarde op wilt zoeken
Wizard Opzoeken
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 Opzoeken 

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).
Wizard Opzoeken

 



[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

zondag, 10 februari 2013 21:16

Wat was de formule ook alweer?


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.

Toon formules in Excel

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

zondag, 10 februari 2013 20:43

Deling door nul (Deel/0) voorkomen

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!

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

Soms kan een Excel-bestand beschadigd raken. Wanneer je het bestand niet meer kunt openen houdt het op: er is dan meestal geen herstel meer mogelijk.Soms staan er nog versies van je Excel bestand in de temp map waar je nog iets mee kunt.
Kies dan Start en type in de uitvoeren / tekst balk %temp% en toest ENTER

Tijdelijke bestanden tonen

Je ziet nu de tijdelijk aangemaakt bestanden, sorteer op 'gewijzigd' om eventueel je beschadigde bestand te vinden.
Heb je het bestand gevonden waarvan je het idee hebt dat dit 1 van je Excel bestanden kan zijn, pas dan de extensie aan van .tmp naar .xlsx / .xls

Tijdelijke bestanden hernoemen

Ik heb wel eens mensen 'een dansje zien doen' door op deze manier een 'verloren' bestand terug te toveren!

Als je het bestand wel kan openen maar er vervolgens weinig mee kan doen probeer dan het volgende:

  • Open het 'beschadigde' bestand.
  • Kies Bestand - Opslaan als.
  • Klik Opslaan als en kies SYLK (Symbolic Link) (*.slk).
  • Klik op Opslaan, bestand afsluiten.

Opslaan als Symbolic Link

Het bestand wordt opgeslagen onder dezelfde naam, maar met de extensie .SLK. Let er wel op dat alleen het actieve werkblad wordt opgeslagen. Heb je meerdere werkbladen dan moeten deze 1 voor 1 opgeslagen worden.
Nadat we alles hebben opgeslagen kiezen we:

  • Bestand - Openen.
    Verander onderin bij Bestandstype de keuze naar Alle bestanden of SYLK-bestanden.
  • Selecteer het eerder opgeslagen .SLK-bestand en open dit.
  • Sla het zojuist geopende bestand nu op als 'gewoon' Excel bestand.

Op deze manier heb je het bestand als het ware geconverteerd van SYLK naar Excel en zo eventuele corrupte data verwijderd. Het is wel zaak om nu te kijken wat er over is gebleven van je originele bestand. Vooraal macro's, opmaak en afbeeldingen willen nog wel eens verloren gaan.

zondag, 10 februari 2013 20:19

Werken met werkruimten

Een handige en veel te weinig gebruikte optie in Excel is werkruimte. In een werkruimte kun je de status van alle geopende werkbladen bewaren. Je kunt de volgende dag dan direct weer verder waar je gebleven was. Werkruimten zijn vooral nuttig als je meerdere werkbladen tegelijk gebruikt.

Handel als volgt:

  • Open alle  Excel bestanden waar je in wilt werken.

Excel 2003

  • Kies Bestand >> Werkruimte opslaan.

Excel 2007 - Excel 2010

  • Kies in het lint 'BEELD' - Werkruimte opslaan.

 

  • De standaard werkruimtenaam is Hervat.xls. Wijzig deze naam indien nodig en klik op Opslaan.
  • Sluit Excel af.

Kies de volgende keer dat je Excel weer gaat gebruiken uit de lijst van laatst geopende bestanden de optie hervat.xlw.

Werkruimte opslaan

Alle werkbladen en hun positie op het scherm worden weer tevoorschijn getoverd. Je kunt verder waar je de vorige keer gebleven bent.
De Werkruimte bevat informatie over de werkbladen, maar niet de werkbladen zelf. Deze blijven op de normale plaats opgeslagen. De bestandsextensie van een werkbladbestand is .XLW (exceL Workspace).

zondag, 10 februari 2013 19:33

Venster Controle

Om een aantal ver uit elkaar liggende cellen in een groot werkblad blijvend in de gaten te houden, is de functie Controle gemaakt. Je gebruikt deze functie als volgt:

Excel 2003


1) Rechtsklik op een cel die je in de gaten wenst te houden en kies Controle toevoegen.
Venster controle toevoegen

Het dialoogvenster Venster Controle verschijnt. Hierin zie je de naam van de map en het werkblad, de celaanduiding, de waarde en - indien aanwezig - de formule.
2)  Het venster zelf blijft geopend en op zijn plaats staan, zelfs als je door het werkblad schuift of naar andere werkbladen gaat. Je kunt de waarden van de te controleren cellen dus steeds in de gaten houden.
Venster controle toevoegen
3) Om de controle op een cel op te heffen, selecteer je deze in het venster en kies je Controle verwijderen. Je kunt ook rechts klikken op de desbetreffende cel en kiezen voor Controle verwijderen.

Excel 2007 / 2010

1) Tabblad Formules

2) Venster controle

3) Controle toevoegen

4)Selecteer de cellen die je wilt controleren

 Venster controle toevoegen

 

 

Pagina 5 van 7

Excel Software Shop

Web Analytics