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!

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

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.

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

Stel je hebt een werkblad waarin de verkoopresultaten van een bepaalde periode per vestiging of regio van een bedrijf worden getotaliseerd. Door gebruik te maken van de opmaakfunctie Voorwaardelijke opmaak kun je in één oogopslag zien hoe het met de cijfers van de diverse vestigingen is gesteld. Als het resultaat onder de maat is zijn de cijfers rood gekleurd, oranje als het resultaat redelijk is en groen als het resultaat goed is. Om dit realiseren, handel je als volgt:Selecteer de cellen met de getotaliseerde resultaten.

Kies Opmaak >> Voorwaardelijke opmaak.

Selecteer na Celwaarde is de optie kleiner dan en voer in de cel ernaast bijvoorbeeld de waarde 500 in (of elke andere waarde die je classificeert als 'te weinig omzet').

Klik op de knop Opmaak en selecteer in het dialoogvenster Celeigenschappen in het tabblad Lettertype bij Kleur de tekstkleur wit en in het tabblad Patronen de opvulkleur rood. Klik op OK.

Klik in het dialoogvenster Voorwaardelijke opmaak nu op de knop Toevoegen.

Selecteer na Celwaarde is de optie tussen en voer in de twee cellen ernaast bijvoorbeeld de waarden 1000 en 500 in.

Klik op de knop Opmaak en selecteer in het dialoogvenster Celeigenschappen in het tabblad Lettertype bij Kleur de tekstkleur wit en in het tabblad Patronen de opvulkleur oranje. Klik op OK.

Klik in het dialoogvenster Voorwaardelijke opmaak wederom op de knop Toevoegen.

Selecteer na Celwaarde is de optie groter dan en voer bijvoorbeeld de waarde 1000 in.

Klik op de knop Opmaak en selecteer in het dialoogvenster Celeigenschappen in het tabblad Lettertype bij Kleur de tekstkleur wit en in het tabblad Patronen de opvulkleur groen. Klik op OK.

Het scherm ziet er nu ongeveer als volgt uit:

Klik tot slot nogmaals op OK.

Het volgende eindresultaat wordt getoond:

Wanneer de waarde van één van de voorwaardelijk opgemaakte cellen kleiner is dan 500 wordt de cel rood weergegeven. De cijfers erin worden wit gekleurd voor een goede leesbaarheid. Wanneer de waarde (in dit voorbeeld) tussen 500 en 1000 zit is de achtergrondkleur oranje. De vestigingen die meer dan de bij groter dan ingevulde waarde hebben omgezet krijgen een groen gekleurde cel. Die zitten qua omzet 'veilig', dus daar hoef je niet direct op te letten!
 

Natuurlijk zit de optie voorwaardelijke opmaak ook in Excel 2007 / 2010 / 2013 (tabblad START)

Download voorbeeldbestand voorwaardelijke opmaak HIER