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

Excel is uitstekend geschikt om bijvoorbeeld gewerkte uren bij elkaar op te tellen. Dat optellen van tijden vindt in principe op dezelfde manier plaats als getallen. Er ontstaat echter een probleem als de som van de tijden meer dan 24 uur bedraagt. Probeer maar 8:15, 8:15 en 8:15 uur bij elkaar op te tellen.
 
Het resultaat dat je te zien krijgt is 0:45 uren dat klopt natuurlijk niet.

Dat komt omdat Excel standaard een 24-uurs weergave gebruikt en deze optelling van 24:45 uren, ziet als 24+0:45 uren en dus 0:45 uren weergeeft. Om toch het juiste totaal aantal gewerkte uren te zien te krijgen, handel je als volgt:
 

• Rechtsklik op de cel met het resultaat (formule) van de optelling.
• Selecteer in het getoonde snelmenu de optie Celeigenschappen.
• Selecteer indien nodig in het dialoogvenster Celeigenschappen het tabblad Getal.
• Selecteer onder Categorie de optie Aangepast.
• Selecteer in de lijst onder Type de optie [u]:mm:ss.

Klik tot slot op OK. nu wordt het correcte aantal gewerkte uren (24:45) getoond.

Let er op dat je de juiste kiest, als eerste zal u:mm:ss getoond worden in de lijst met mogelijkheden, wij moeten [u]:mm:ss. hebben dus met de blokhaken [u]!

Downlad voorbeeldbestand HIER

Om tekst over twee of meer regels te laten lopen is er in Excel een bijzondere toetsaanslag: Alt+Enter. In andere Office-programma's zou je wellicht Shift+Enter gebruiken. Typ een stukje tekst in een cel. Wanneer je de celrand nadert druk je op Alt+Enter. Je kunt nu weer verder typen. De regels komen onder elkaar in één cel te staan en de rijhoogte wordt automatisch aangepast.

Als je al cellen hebt met tekst die te lang is voor de kolombreedte, dan kun je beter het volgende doen: selecteer de cel(len), klik met de rechtermuisknop en kies 'Celeigenschappen'. Ga naar het tabblad Uitlijning en zet de optie Terugloop aan. Druk dan op OK. De tekst wordt nu automatisch over meerdere regels verdeeld.