Excel

De inhoud van dit document bevat hints en tips voor Excel.
Voor Macro’s en VBA zie Macro’s en VBA
Een aantal van de tips komen van Gratiscursus.be.
Ik wil de maker van deze site wil ik hierbij ook hartelijk bedanken voor al zijn werk.
Kijk eens op zijn site voor nog meer tips.

Alle cellen in een kolom tellen

Deze formule telt alle cellen in Kolom A:

=COUNTA(A2:A30)

Brieven samenvoegen vanuit Excel

Maak eerst de standaardbrief in Word dus zonder adresgegevens.
Sla deze op.
Schrijf op hoe het Excel bestand heet van waar je uit gaat werken.
Start Word
Open de standaardbrief
Extra
Afdruk samenvoegen
Hoofddocument:
Maken
Standaarbrieven
Actief venster (links)
Gegevensbestand:
Gegevens
Gegevensbestand koppelen (2e)
bestandstypen veranderen in MS-Excel-werkbladen)(links onder)
Zoek je Excel bestand op
Naam of Celbereik
Hele werkblad
Ok
Hoofddocument bewerken
Ga op de juiste plek in de brief staan
Linksboven in je beeld staat nu Samenvoegveld invoegen.
Naam, adres, etc...
Kies het juiste veld
Dit zie je nu tussen haakjes << >> staan.
Doe dit met alle velden die je nodig hebt.
Gegevens samenvoegen
Klik op Samenvoegen op de werkbalk
Samenvoegen naar nieuw document of printer (Afhankelijk wat je wilt doen)
Samenvoegen
Document opslaan als...
Geef het document een nieuwe naam.

Cellen Blokkeren

Selecteer cellen die geblokkeerd moeten worden
Rechtermuisknop
Celeigenschappen
Tab Bescherming
Zet het vinkje bij beveiligd AAN
OK
Selecteer cellen die niet geblokkeerd moeten worden
Rechtermuisknop
Celeigenschappen
Tab Bescherming
Zet het vinkje bij beveiligd UIT
OK
Selecteer het hele werkblad
Tab Controleren, kies voor Blad beveiligen.
Zorg dat het vinkje bij ontgrendelde cellen AAN staat, en de rest uit.
Beveilig blad (eventueel met wachtwoord, maar hoeft niet)
Beveiliging van blad halen
Tab Controleren, kies voor Blad beveiligen.
Beveiliging opheffen

Een zoekbare validatielijst maken

Deze tip komt van Gratiscursus.be

Hier leg ik uit hoe je een validatielijst maakt die, afhankelijk van ingetypte zoekletters, wijzigt.
Een dynamisch zoekbare validatielijst dus.
Zoals je kunt zien heb ik in cel B2 een validatielijst die is gebaseerd op de lijst in kolom G.
In deze lijst staan “slechts” 148 namen, dus dit valt nog mee om de juiste persoon te zoeken in de validatielijst, maar toch.
Stel je voor je hebt er duizenden, en je weet slechts een deel van de naam.
Ik kan je garanderen je vindt deze nooit.
_images/01.jpg
Wat ik hiervoor nodig heb is een validatielijst die, afhankelijk van de ingetypte letters in de cel, dynamisch geupdate wordt.
Dus typ ik bijvoorbeeld bart of Bart in het validatievak, dan wil ik enkel de personen terugvinden die aan deze voorwaarde voldoen, in de validatielijst.
_images/02.jpg
Typ ik de letters ceu, dan worden enkel de personen weergegeven waarin deze letters voorkomen.
Zoals bijvoorbeeld “Ceurvels”, “Ceulemens, “Ceustermans” en nogmaals “Ceulemans”.
_images/03.jpg
Of deze letters zich bevinden in de voornaam of familienaam, dit maakt niks uit.
Wanneer ze er in staan en in de juiste volgorde, dan worden deze opgenomen in de validatielijst.
_images/04.jpg
Laat ik beginnen met het begin.
Het eerste wat we gaan doen is zoeken of de ingetypte tekst in cel D2, hetzelfde is dan de gegevens in onze lijst in kolom G.

De formule hiervoor is:

=ALS(ISGETAL(VIND.SPEC($D$2;G2));MAX($F$1:F1)+1;0)
Ik probeer deze even uit te leggen.
(VIND.SPEC($D$2;G2)
De functie VIND.SPEC zal de ingetypte tekst in D2 zoeken in cel G2.
De cel D2 maken we absoluut want de formule wordt later gekopieerd naar onder.
De functie ISGETAL zal de waarde weergeven als WAAR of ONWAAR.
De functie ALS +1;0 zal deze waarden omzetten in getallen, en wanneer hij er een heeft gevonden er een bijtellen.
Heeft hij er geen gevonden dan geeft hij een nul weer.
Dus als de waarde in cel D2 gelijk is aan de waarde van cel G2, dan geef je me een een, is dit niet het geval dan geef je me een nul.
Het getal 1 voorzie je van een plusteken omdat hij er telens wanneer dit het geval is een moet bijtellen.
De functie MAX($F$1:F1) zal deze zoekopdracht uitvoeren in het bereik $F$1:F1. En ook hier maken we de eerste celverwijzing absoluut.
_images/05.jpg
Dus kopieren we deze cel naar onder, dan zal je zien dat telkens hij de ingetypte letter in cel D2 vindt in kolom G hij er 1 zal bijtellen.
_images/06.jpg
Nu, op basis van de gegevens in kolom F gaan we onze dynamische lijst maken in kolom I.

De formule hiervoor is:

=ALS.FOUT(VERT.ZOEKEN(RIJEN($I$2:I2);$F$2:$G$200;2;0);"")
Ik probeer deze even uit te leggen.
De functie VERT.ZOEKEN zal zoeken in het bereik ingegeven in de functie RIJEN
De functie ALS.FOUT zal voorkomen dat er een foutmelding wordt weergegeven wanneer er niks wordt gevonden. Wanneer er niks wordt gevonden moet hij een lege cel weergeven. Daarvoor staan de twee dubbele aanhalingsteken.
_images/07.jpg
Nu, op basis van deze dynamische lijst moeten we onze validatielijst maken.
Omdat de dynamische lijst niet altijd evenveel namen zal bevatten, moeten we hiervan eerst het aantal bepalen.
Dit doen we met de functies AANTAL.ALS in samenwerking met de funtie VERSCHUIVING::
=VERSCHUIVING(Blad1!$I$2;;;AANTAL.ALS(Blad1!$I$2:$I$300;”?*”))
De functie AANTAL.ALS bepaalt het aantal gevonden namen.
De functie VERSCHUIVING bepaalt de te starten cel in het bereik I2, bepaalt het aantal rijen, het aantal kolommen en de hoogte.
Het aantal rijen en kolommen is steeds 1, dus hiervoor hoeven we niks te typen ;;.
En de hoogte wordt bepaalt door de functie AANTAL.ALS
En de criteria hiervoor is ?*. Vraagteken en vermenigvuldigingsteken. Vraagteken staat voor een zelfde teken, vermenigvuldigingsteken voor opeenvolging van tekens.
Deze formule gebruiken we als bereik.
Klik de knop Beheer en klik de knop Nieuw.
Geef het bereik een naam, validatie_lijst lijkt me wel wat.
En kopieer de zojuist aangemaakte formule in het vak “Verwijst naar:”::
=VERSCHUIVING(Blad1!$I$2;;;AANTAL.ALS(Blad1!$I$2:$I$300;”?*”))
Klik de knop Ok om het eerste venster te sluiten.
Klik de knop “Sluiten om het tweede venster te sluiten.
_images/08.jpg
En ten laatste gaan we de nieuwe validatielijst toevoegen aan cel D2.
Dus selecteer cel D2.
Klik de knop “Validatielijst” in het lint.
En in het venster dat opent kies je in het vak “Toestaan” voor “Lijst”.
Je plaatst de cursor in het vak “Bron”, en klikt de F3 toets op je toetsenbord.
Selecteer de zojuist aangemaakte lijst in het venster dat opent en klik de knop OK.
_images/09.jpg
En als laatste verwijderen we nog het vinkje voor de tekst “Foutmelding...”
Klik de knop OK
_images/10.jpg
Onze zoekbare validatielijst is klaar
_images/11.jpg

Excel automatisch een of meerdere bestanden laten openen

Dit is een handige tip wanneer je dagelijks hetzelfde bestand of bestanden moet openen.

Waarschuwing

Deze tip wordt uitgevoerd elke keer als je Excel opstart.

In deze tip leg ik je uit, hoe je telkens wanneer je Excel opstart, een of meerdere bestanden worden geopend.
Als voorbeeld heb ik een map gemaakt op mijn bureaublad genaamd Nieuwe Map
In deze map zitten twee Excelbestanden die ik automatisch wens te openen wanneer ik Excel opstart.
(Waar deze map staat, en hoeveel bestanden hierin zitten maakt niks uit.)
_images/014.png
Open Excel
Klik op de Officeknop linksboven
_images/024.png
Klik op Opties voor Excel
Klik op Geavanceerd
_images/032.png
Ga helemaal naar beneden bij het kopje Algemeen.
In het vakje Alle bestanden bij opstarten openen moet de link naar de map komen.
_images/042.png
Hoe deze te vinden:
Open de map waar de bestanden in staan.
Klik met je rechtermuiskop in de adresbalk en klik op Adres kopieeren.
_images/052.png
Ga weer naar het vakje Alle bestanden bij opstarten openen.
Klik hierin met je rechtermuisknop
Klik op plakken
_images/061.png
Wanneer je nu Excel opstart, openen automatisch alle bestanden in deze map.

Getal verhogen met een percentage

Cel A1 bedrag
Cel B1: Formule =A1*(1+2%)
Sleep de formule naar beneden om alle getallen te verhogen met 2%
Selecteer alle cellen in kolom B
Kopieren
Plakken Speciaal
Waarden plakken
De formule is omgezet naar een getal
Voorbeeld
_images/015.png
Formule Beschrijving
=A2*(1-B2) Verlaagt 50 met 25% (37,50)
=A2*(1+B2) Verhoogt 50 met 25% (62,50)
=A2*(1+35%) Verhoogt 50 met 35% (67,50)

Keuzelijst

Deze tip komt van Gratiscursus.be

Een keuzelijst gebruiken uit de verschillende besturingselementen.
Ga naar het tabblad “Ontwikkelaars”.
Klik de knop “Invoegen” en kies “Keuzelijst”
_images/011.jpg
Klik en sleep in het document op de plaats waar je deze keuzelijst wenst.
De grootte en de positie doet er eigenlijk niet toe, je kunt dit steeds aanpassen.
_images/021.jpg
Rechtsklik de keuzelijst en kies “Besturingselement opmaken”.
_images/031.jpg
In het bovenste vak van het venster dat opent geef je het bereik in met de verschillende gemeenten.
In het onderste vak geef je een cel in waarmee je de koppeling wil maken. In dit geval kies ik I3, maar hiermee doe je wat je wenst.
_images/041.jpg
Typ volgende formule in deze cel: =$i$3
_images/051.jpg
Dit zal de inhoud van de eerste rij uit de tabel weergeven.
_images/061.jpg
Afgaande op deze celverwijzing willen we het aantal inwoners van de gemeente weten.
Je kunt dit doen met de VERT.ZOEKEN functie, of met de INDEX functie.
De INDEX functie laat ons toe gegevens uit een lijst weer te geven, dus geef ik het voorbeeld met deze functie.
Maar je zou, toch in dit geval, ook de VERT.ZOEKEN functie kunnen gebruiken.
Typ volgende formule: =INDEX(C:C;i3+1)
Wat staat hier nu:
Zoek in kolom C de waarde die staat in cel i3 +1
Waarom +1? Wel omdat de lijst pas begint op de tweede rij.
Dit geeft netjes het aantal bewoners van de geselecteerde gemeente.
_images/071.jpg
Selecteer je een andere gemeente in de keuzelijst, dan wordt het aantal inwoners aangepast.
_images/081.jpg

Maak een kopie zonder de formules

Je hebt een Excel map die je door wilt sturen, maar zonder de formules.
De ontvanger heeft namelijk alleen de data nodig, en niet de formules.

Open het bestand dat je door wilt sturen.
Maak een nieuw Excel bestand en geef dat een duidelijke naam.

Het plaatje hieronder geeft het Excel blad weer voordat we gaan beginnen.
_images/016.png
Voor de volgende stap gaan het werkblad kopieren.
Klik op het hoekje helemaal linksboven, en het hele blad wordt geselecteerd.
_images/025.png
Klik in de Start tab op kopieren.
_images/033.png
Ga naar het nieuwe bestand dat je gemaakt hebt.
Klik in cel A1
Klik in de Start tab op het vinkje onder Plakken, een menu opent.
Klik op Waarden plakken.
_images/043.png
De tekst staat nu op het nieuwe werkblad zonder de formules.
_images/053.png
Je kunt het bestand nu opslaan en versturen.

Naamkaartjes maken

Dit is een handleiding om in Excel een namenlijst te maken en deze daarna samen te voegen met een word document om Naamkaartjes te maken voor bijvoorbeeld een evenement.
Ik gebruik hier het voorbeeld voor een evenement waar mensen van diverse bedrijven en provincies bijeen komen.

Start Excel.
Open eventueel het bestand met alle gegevens die je hebt.
Verwijder de kolommen die je niet nodig hebt voor de naamkaartjes.

Geef de bovenste rij de namen van de diverse kolommen.
Nummer, Voornaam, Tussenvoegsel, Achternaam, Bedrijf, Provincie.

Zet de bovenste rij in een vet lettertype.
Selecteer de bovenste rij.
Klik op het nummer 1 voor de rij (de rij wordt geselecteerd).
Klik in het lint op de letter B.
De rij is nu in vet gezet.

Zet de bovenste rij vast.
Dit zorgt ervoor dat als naar beneden scrolt de bovenste rij blijft staan.
Selecteer de bovenste rij.
Klik op tabblad Beeld.
Klik op Deelvensters blokkeren.
Er klapt een submenu open.
Klik op Titels blokkeren.
_images/017.png
Sla het bestand op als Naamkaartjes.

Nu kun je het bestand vullen met al je gegevens.
Zie hieronder mijn voorbeeld.
_images/026.png
Als je lijst gevuld is, is het handig als de lijst op (achter)naam gesorteerd wordt.
Selecteer al de kolommen door met je muis ingedrukt over alle kolommen te gaan.
De kolommen veranderen van kleur.

Sorteren lijst op alfabetische volgorde.
Tabblad Start.
Klik op Sorteren en filteren.
Kies voor Aangepast sorteren.
_images/034.png
Er opent een nieuw schermpje.

Zorg dat het vinkje bij De gegevens bevatten kopteksten aangevinkt is.
_images/044.png
Kies voor de eerste regel voor:
Sorteren op: Achternaam.
Laat de overige velden leeg.
Klik op Niveau toevoegen.
Sorteren op: Voornaam.
Sorteer eventueel de andere kolommen ook.
Klik op OK.
_images/054.png
De lijst is nu alfabetisch gesorteerd.
_images/062.png
Sla het bestand op.

Als je van deze lijst naamkaartjes wilt maken waar al deze gegevens opstaan is het handig als de kolom met het nummer er als een tekst veld uitziet.

Alle kommen in tekstformaat maken
Selecteer al de kolommen door met je muis ingedrukt over alle kolommen te gaan.
De kolommen veranderen van kleur.
Klik met de rechtermuisknop ergens in de kolommen die je geselecteerd hebt.
Er opent een nieuw schermpje.
_images/071.png
Klik op Celeigenschappen.
Er opent een nieuw schermpje.
_images/08.png
Selecteer bij de Categorie voor Tekst.
OK.
Sla het bestand op.
Het bestand is nu klaar om Naamkaartjes te maken.
Hoe je dit doet kun je lezen bij:
Etiketten maken met Verschillende adressen
Ik gebruik de kaartjes van Avery met nummer C32017. De maten zijn 8,5 x 5,4 centimeter.

Rijen om en om kleuren zonder tabel

Deze tip komt van Gratiscursus.be

Rijen om en om in te kleuren, maar dit zonder het invoegen van een tabel.
Voor gefilterde rijen zie halverwege deze instructie
Selecteer eerst alle rijen die je om en om wenst in te kleuren.
Klik vervolgens de knop “Voorwaardelijke opmaak”, en kies “Nieuwe regel”.
_images/012.jpg
In het venster dat opent kies je het type “Een formule gebruiken...” en typ je volgende formule:
WAAR
In 2 rijen zal er een worden voorzien van opmaak.
Klik de knop “Opmaak”.
Wanneer jij bv. liever hebt dat in 5 rijen twee worden voorzien van opmaak, dan typ je de formule:
ONWAAR
_images/022.jpg
Kies het tabblad “Opvulling” en kies een kleur die je wenst te gebruiken als opvulling.
Klik de knop ok.
_images/032.jpg
En nogmaals de knop ok.
_images/042.jpg
Alle rijen zijn netjes om en om ingekleurd.
_images/052.jpg

Gefilterde rijen om en om kleuren zonder tabel

_images/062.jpg

Hiervoor gebruiken we volgende formule:

=REST(SUBTOTAAL(3;$A$1:$A2);2)
_images/072.jpg
Filter je nu op 2 items uit de lijst,
_images/082.jpg

of 3 items uit de lijst, dit maakt niet uit, de rijen worden om en om ingekleurd.

_images/091.jpg

Samenvoegen 2 cellen

Samenvoegen 2 cellen en van allebei de tekst bewaren.

  A B C
1 Voornaam Achternaam Voornaam Achternaam
2 Jan Jansen Jan Jansen
Zet in C1 de volgende formule: =A1&” “&B1
Of
zet in C1 =TEKST.SAMENVOEGEN(A1; ” ”;B1)
Dit voegt de velden samen.
Sleep formule naar beneden voor alle velden.
Nu moet je zorgen dat je de tekst nog zonder de formule krijgt.
Selecteer kolom C
Kopieeren
Selecteer E1
Bewerken
Plakken speciaal
Klik op Waarden plakken.
Namen worden geplakt.
Nu heb je een namenlijst zonder de formule.

Spatie aan het begin van de kolom verwijderen

Soms gebeurt het dat rijen aan het begin een spatie hebben.
Hier een methode om deze snel te verwijderen.
Ik ga er vanuit dat de kolom met spaties kolom A is.
Maak rechts van kolom A een twee extra kolommen. (Kolommen B en C).
Selecteer cel B1.
Zet in deze cel de volgende formule: =SPATIES.WISSEN(A1)
Sleep de formule naar beneden zodat alle cellen aangepast zijn
Selecteer kolom B
Kopieeren
Selecteer C1
Bewerken
Plakken speciaal
Klik op Waarden plakken.
Nu heb je een lijst zonder de formule.
Verwijder kolommen A en B.

Tekst naar kolommen

Soms heb je een lijst met namen en adressen die in Excel alleen in kolom A staat.
(Jan Jansen, Stationsstraat 1, 1000AA, Amsterdam)
Als je deze kolom wilt splitsen om er bijvoorbeeld adresetiketten te maken gebruik je Tekst naar kolommen.
Zorg dat de gegevens in kolom A staan.
Selecteer kolom A.
Tabblad Gegevens.
Kies voor Tekst naar kolommen.
Er opent een Wizard.
Kies voor Gescheiden.
Volgende.
Verwijder het vinkje bij Tab.
Zet een vinkje bij Komma.
Volgende.
Vervang eventueel de Bestemming, maar meestal laat ik dit staan.
Voltooien.
De tekst wordt nu over 4 kolommen verspreid.
Pas de kolombreedte aan.
Sla het bestand op onder een nieuwe naam.
Nu moet je waarschijnlijk voor een aantal kolommen de spatie aan het begin van de cel verwijderen. Zie daarvoor Spatie aan het begin van de kolom verwijderen.

Verberg Celwaarden

Soms is het handig om in een Excelblad de inhoud van diverse cellen onzichtbaar te ze zetten.
Dit is vooral handig als je documenten naar iemand anders wilt sturen, en het document bevat bijvoorbeeld persoonlijke gegevens die de ontvanger niet mag zien (BSN nummer, salaris)
Let op! De waardes zijn alleen maar onzichtbaar gezet, maar wel zichtbaar in de formulebalk.
Selecteer de cel of cellen waarvan je de waardes wilt verbergen.
Op de Start Tab klik bij Getal op het pijltje rechtsonder.
_images/0113.png

Klik in het eerste tabblad (Getal) op Aangepast.
Klik in Type:
;;; (3 keer een puntkomma)
Klik OK
De inhoud van de cel(len) is nu verborgen.

Om de inhoud weer zichtbaar te maken volg bovenstaande procedure maar kies bij Type voor Standaard.

Verwijderen tekens in regel

Deze tip is handig als je uit een kolom maar een gedeelte van de gegevens nodig hebt.

Als voorbeeld heb ik even een lijstje met postcodes gebruikt.

  A B C D E
1 1001aa 1001 aa 1aa 1a
2 2002bb 2002 bb 2bb 2b
3 3003cc 3003 cc 3cc 3c
Kolom B geeft als resultaat alleen de eerste 4 karakters. (de cijfers van de postcode)
Gebruik hiervoor de volgende formule: =LINKS(A1;4)
Kolom C geeft als resultaat alleen de laatste 2 karakters. (de letters van de postcode)
Gebruik hiervoor de volgende formule: =RECHTS(A1;2)
Je kunt ook de laatste de laatste 2 getallen uit een rij verwijderen, terwijl het niet uitmaakt hoe lang de rij is.
Gebruik hiervoor de volgende formule: =LINKS(A1;LENGTE(A1)-2)
Natuurlijk is het ook mogelijk om slechts een gedeelte uit de tekst te halen.
(Ik weet niet precies waar je dit voor nodig zou kunnen hebben, voor de volledigheid heb ik het erbij gezet)
Kolom D geeft als resultaat een deel van de tekst, in dit geval vanaf het 4e tot en met het 999 karakter (999 is meestal wel het einde van de regel)
Gebruik hiervoor de volgende formule: =DEEL(A1;4;999)
Kolom E geeft als resultaat een deel van de tekst, in dit geval vanaf het 4e karakter en dan 2 karakters lang.
Gebruik hiervoor de volgende formule: =DEEL(A1;4;2)