Wat betekent een # in Excel?

Het kan steeds vaker voorkomen dat je in Excel dit ziet staan: het #-teken achter een celadres.
Wat houdt dat in? Waarom verschijnt dat?

Het heeft te maken met ‘dynamische bereiken’ (Engels: dynamic arrays) die gebruikt worden. Daarom eerst een uitleg over dat onderwerp.

Video bij Blog

Bij dit blog is ook een videotip gemaakt. Dit is de link https://youtu.be/NoBwsSEXZOY

Wat is een dynamisch bereik?

Je verwacht waarschijnlijk dat in cel D2 staat: =B2*C2> En dat die formule is gekopieerd naar de andere cellen eronder. Ze werkt Excel al vanaf het begin!

Maar je kunt dit tegenwoordig ook anders maken. Je wilt immers dat in de D-kolom alle prijzen van B vermenigvuldigd worden met alle aantallen in C op dezelfde rij! Dan hoef je maar één berekening te maken waarin je dat aangeeft: =B2:B6 * C2:C6

Als je dit invoert ziet het er zo uit.

De berekening staat in één keer in D2:D6. En er staat een blauwe rand om die cellen. Als je heel goed kijkt, zie je dat de berekening in de formulebalk in D2 zwart is, en in de andere cellen grijs.

Dat betekent dat de formule alleen in D2 staat en ‘overloopt’ in de andere cellen van het blauw omrande gebied: het overloopgebied of het dynamische bereik.
Zou er iets staan in een van die cellen, dan krijg je een foutmelding: #OVERLOPEN! (Engels: #SPILL!) Maak je de cel weer leeg dan is het probleem opgelost!

Een dynamisch bereik is dus een gebied waarin één formule staat (in de cel linksboven), die overloopt in de andere cellen van het blauw omrande gebied. Je ziet die blauwe rand als je een cel in dat gebied selecteert.

Berekening maken met een dynamisch bereik

Ga je nu een totaaltelling maken in D8 ①, dan zie je dat zodra je het overloopgebied exact selecteert ②, dat niet meer het gebruikelijke D2:D6 verschijnt, maar D2#.
Dat betekent “het overloopgebied dat begint in D2”. Dat moet in dit geval (SOM) worden opgeteld.

Nieuwe functies met dynamische bereiken

In bovenstaande situatie kies je er zelf voor om een formule te maken met een dynamisch bereik. Maar er zijn intussen ook al veel functies die als uitkomst altijd een dynamisch bereik hebben.

Een voorbeeld is de fdunctie FILTER(). Die geeft als resultaat een gefilterde lijst in een dynamisch gebied.
In de afbeelding wordt links hierbij een normaal cellenbereik gebruikt ① en rechts een tabel ②.

=FILTER() heeft hier 2 argumenten.

Het eerste is het tabelbereik (bij ① A3:C17 en bij ② de naam van de tabel Tabel1).
Het tweede argument is de kolom waarop gefilterd moet worden (bij ① B3:B17 en bij ② de naam van de kolom Cat. van Tabel1: Tabel1[Cat.]). Die kolom moet gelijk zijn aan een B (=”B”).

Je ziet dat in beide gevallen het resultaat een dynamisch bereik is.

Een ander voorbeeld van een dynamische functie is UNIEK (Engels UNIQUE). Die geeft als resultaat een lijst met de unieke items uit een gebied.

De functie heeft hier 1 argument.

Het bereik waar de unieke items uit moeten komen.
Bij ① B3:B17 en bij ② de kolom Land van herkomst van de tabel Tabel2 (Tabel2[Land van herkomst]).

Weer de #

Zou ik bij de functie FILTER een optelling maken van het dynamische bereik, dus =SOM(E3#), dan zullen alle data in de cellen E3:G8 opgeteld worden: 404,8. Daarbij zullen alleen getallen van G3:G8 opgeteld worden (SOM slaat niet numerieke gegevens over bij het optellen).

Zou ik de functie AANTALARG (Engels COUNTA) gebruik bij het dynamische bereik van de uniek-functie, dan staat er dus =AANTALARG(D3#) en is de uitkomst 3.

Voordelen van een tabel versus bereik

Een dynamische functie kan dus zowel overweg met een cellenbereik als met een Excel-tabel. Maar dat laatste is flexibeler!
Stel dat er bij het voorbeeld van de filterfunctie een artikel categorie B bij komt. Dan zal bij ① het cellenbereik handmatig aangepast moeten worden (je moet er dus zelf aan denken en actie ondernemen!). Bij een tabel wordt de tabel vanzelf groter en past het resultaat zich vanzelf aan ②! Minder kans op fouten dus!

Bij UNIEK hetzelfde: wordt het bereik groter, dan breidt de tabelformule zich automatisch uit en de bereikformule doet dat niet.

Samenvatting

De # kom je dus tegen in berekeningen waarbij verwezen wordt naar een dynamisch bereik.
Zo’n bereik is herkenbaar aan de blauwe rand eromheen, als je een cel in dat bereik selecteert.
De berekening staat alleen in de cel linksboven van dat bereik en ‘loopt over’ in de andere cellen (die cellen moeten leeg zijn).
Een dynamisch bereik komt het beste tot zijn rechts als je die gebruikt met een Excel-tabel. Bij het groter (en kleiner) worden van de tabel, past het bereik zich automatisch aan omdat er tabelformules gebruikt worden, zoals je ziet in de laatste afbeelding.

30 oktober 2024

PDF importeren in Word en Excel

Blog

Decoratieve afbeelding die symboliseert dat je een pdf kunt binnehalen in Word en Excel

Een PDF-bestand wordt vaak gebruikt omdat het er dan op alle apparaten hetzelfde uitziet. Dat geeft de afkorting al aan: Portable Document Format. Ook wordt het vaak gebruikt omdat het door de meeste mensen niet bewerkt kan worden. Zo’n bestand lees je met een PDF-reader, een gratis programma (de bekendste is waarschijnlijk Adobe Reader. Maar als je de PDF wilt kunnen bewerken, dan heb je aanvullende software nodig waar je vaak voor moet betalen!
Maar tijdens trainingen krijg ik vaak de vraag of je er met de Office-programma’s “wat mee kunt”. Het antwoord is vaak “ja”. Met Word kun je het openen en bewerken. In Excel kun je de gegevens eruit halen bijvoorbeeld voor verdere analyse.

Is de PDF tekst of een afbeelding?

Om antwoord te krijgen op deze vraag moet je het document in een reader openen. Kun je dan over de tekst slepen met de muis, dan is het een tekst ①. Lukt dat niet en wordt in feite de hele pagina geselecteerd, dan is het een afbeelding ②. Met een afbeelding kun je meestal minder doen dan met een tekst.

Overnemen met kopiëren en plakken

Als je over de tekst kunt slepen, kun je die selecteren, kopiëren (Ctrl+c) en dan elders plakken (zie ②) en dan verder bewerken. Toch kan dat er soms totaal anders uitzien dan in de PDF!

PDF document bewerken in Word

In Word kun je een pdf openen. Je krijgt dan wel een waarschuwing dat het even kan duren en dat het er niet hetzelfde kan uitzien als het origineel. Soms levert dit zelfs wat op als het een pdf-afbeelding is.

Loop het document goed langs, want er wil nog wel eens ‘gewone’ tekst in een tekstvak staan.
Maar je kunt het bewerken. Als je het opslaat wordt het standaard een Word-document.

PDF-documenten en Excel

In Excel kun je niet direct een PDF openen, maar je kunt de gegevens wel importeren.

Kies Gegevens > Gegevens ophalen > Uit bestand > Uit PDF.
Selecteer het PDF-bestand.
In het navigatievenster zie je hoe het bestand is opgebouwd.
- Er kunnen bijvoorbeeld tabellen in staan die herkend worden ①. Ook zie je altijd de pagina’s staan ②.
- Klik je op een element dan zie je rechts een voorbeeld ③: handig voor de herkenning.
- Kies wat je wilt gebruiken om te importeren. Kies bij voorkeur tabellen, want daar heb je het minste werk aan (bij een pagina staan bijvoorbeeld ook kop- en voetteksten er altijd bij).
  Als je meer elementen nodig hebt, moet je dat eerst aangeven ④.
- Kies daarna Gegevens transformeren om te bewerken ⑤.
Je bent nu overgeschakeld naar Power Query. In dat programma voer je de bewerkingen uit die nodig zijn. Het lijkt heel veel op Excel en je zult veel dingen op dezelfde manier kunnen doen als in Excel. Gebruik ook hier vaak de rechtermuisknop, want die is erg handig.
Verwijder bijvoorbeeld kolommen en rijen die je niet nodig hebt. Splits kolommen als gegevens verdeeld moeten worden.
- Alle bewerkingsstappen die je uitvoert worden vastgelegd als Toegepaste stappen ①.
- Ongedaan maken bestaat hier niet, maar je kunt wel een stap verwijderen met het kruisje ②.
- Je ziet steeds hoe het uitpakt in het voorbeeld ③.
Als alles naar wens is, zet je het in Excel met Sluiten en laden④.
- Standaard wordt het dan een tabel op een nieuw werkblad.
  Maar kies je het menu van die knop en dan Sluiten en laden naar… dan heb je ook andere mogelijheden om te importeren.
De gegevens staan dan in Excel.
- Heb je een foutje gemaakt, of moet er nog meer gedaan worden? Dubbelklik dan rechts op de query die gemaakt is (of rechtsklik erop > Bewerken).
  Je kunt dan aanvullingen doen of verbeteringen aanbrengen.
- Zet het weer terug met Sluiten en laden.

Houd er rekening mee, dat er op deze manier een koppeling is tussen de PDF en je Excel-bestand. Als je het Excel-bestand sluit en later weer opent krijg je dan ook een melding dat er Externe koppelingen zijn.
Wil je van die koppeling af? Klik dan met de rechtermuisknop op de query en kies Verwijderen. Je houdt dan alleen de tabel over.

Over Power Query is nog veel meer te melden. Wil je meer weten? Neem contact op voor een training of werkplekbegeleiding: https://toels-pc.nl.

Welke opties heb je met een afbeelding-PDF?

Zoals eerder aangegeven kun je hier soms wat mee als je het opent in Word.
In Excel kun je een poging wagen om de tekst uit een afbeelding te halen. Het eindresultaat is sterk afhankelijk van de kwaliteit van de afbeelding!
Excel kan tekst halen uit een afbeeldingbestand (bijv. JPG of PNG) of uit een gekopieerde afbeelding. Een PDF-is geen afbeeldingsbestand dus Afbeelding uit bestand kun je niet gebruiken niet werken.

Open het PDF-bestand in een reader.
Je moet de afbeelding op het klembord krijgen. Dan kan bijvoorbeeld zo.
- Klik op de afbeelding en kopieer dit met Ctrl+c.
  De afbeelding staan nu op het klembord.
- Maak een schermafdruk met Windows+Shift+s en sleep over de gegevens die in de afbeelding moeten komen.
Kies daarna in Excel Gegevens > Uit afbeelding > Afbeelding uit Klembord.

Rechts verschijnt een schermdeel waar de afbeelding wordt ‘gelezen’.
Gebruik Controleren ① om de plaatsen langs te gaan waar Excel vraagtekens bij heeft. Verbeter dit als dat nodig is > Accepteren ②.
Kies daarna Gegevens invoegen ③ om de gegevens in Excel te krijgen.

De gegevens staan nu in het werkblad en je kunt ermee aan de slag.
In tegenstelling tot ophalen uit een pdf-bestand, is er nu geen koppeling met de afbeelding meer.

3 oktober 2024

Zoeken in de Office-programma’s

Blog

Decoratieve afbeelding die zoeken in een tekst symboliseert

In de enorme hoeveelheid gegevens op je computer zul je niet altijd exact weten waar iets staat. Dus zul je ‘zoeken’ vast wel eens gebruikt hebben. Het klinkt simpel, maar tijdens trainingen merk ik toch soms wat problemen hiermee of krijg ik een “Oh, DAT is handig!” te horen.
Hoe het zoeken gebeurt is niet in alle Microsoft Office-programma’s hetzelfde. Eenduidigheid is hierbij ver te ‘zoeken’ 😉!
Daarom dit blog over Zoeken.

Wat bijna altijd geldt bij Zoeken

Wat wordt gezocht?
Als je in een programma ‘zoeken’ gebruikt wordt er bijna altijd naar een combinatie van tekens gezocht. Dat betekent dat als je zoekt naar ‘scherm’ dat je dan niet alleen het woord ‘scherm’ vindt, maar ook bijvoorbeeld ‘beschermen’, ‘schermtijd’.
Waar wordt gezocht?
Als je tevoren een selectie hebt gemaakt, zal in eerste instantie alleen binnen die geselecteerde tekst/cellen/dia’s/map worden gezocht. Heb je geen selectie gemaakt, dan wordt overal gezocht. Bekijk bijzonderheden per programma hieronder.
Sneltoets
In de meeste programma’s kun je het zoeken starten met een vaste sneltoets: Ctrl+f van het Engelse Find). Vreemd genoeg is de sneltoets in Outlook anders, namelijk Ctrl+e.
Na die sneltoets moet je de zoektekens opgeven. Hoe dat gebeurt is niet in alle Office-programma’s hetzelfde.
Waar vind je het?
In de Office-programma’s vind je dit ‘zoeken’ op de tab Start met een loep-afbeelding erbij: in Word en PowerPoint is Zoeken een aparte opdracht ①, in Excel is het een opdracht in het menu bij Zoeken en selecteren ②. In Outlook staat het niet op een tabblad maar in de titelbalk bovenaan ③.

Omdat de verschillende instellingen en keuzes die je hebt per Office-programma nog verschillen, beschrijf ik het hierna per programma.

Word

Als je een tekst in Word gaat doorzoeken, krijg je met Bestand > Zoeken (of Ctrl+f) een apart schermdeel Navigatie te zien. In dit scherm is dan Resultaten geselecteerd. Als je dat schermdeel al had, zal overgeschakeld worden naar het onderdeel Resultaten.
Je typt hier de zoektekens in ① en direct eronder zie je hoe vaak dit voorkomt ②. In het document zelf worden die zoektekens gemarkeerd ③.
In het navigatievenster kun je bladeren door de gevonden resultaten met de knoppen bij ④.

Wil je nauwkeuriger zoeken, dan kun je de Opties gebruiken via de menuknop ①.
Dan verschijnt een apart scherm ②. Daar kun je bijvoorbeeld opgeven dat je alleen de tekencombinatie wilt vinden als een Heel woord of dat er ook onderscheid gemaakt moet worden tussen hoofd- en kleine letters (Identieke hoofdletters/kleine letters).

Wat hier pas sinds kort aanwezig is, dat je kunt zoeken op de tekens aan het begin of einde, dat dat je leestekens (interpunctie) en spaties kunt negeren bij het zoeken ③.
Ook kun je zou zoekwensen tegenwoordig als een standaard laten instellen met de knop bij ④.

Word kent ook Geavanceerd zoeken. Dit vind je in het menu van de Zoeken-knop ①. Heb je het Navigatiescherm, dan vind je dat onder het menu ②.

Dit levert een dialoogvenster op met nog meer mogelijkheden. Met Volgende zoeken kun je langs de resultaten in het document gaan. Met de knop Minder/Meer ① kun je dit uitgebreider maken of niet.

Handig zijn hier:

Markeren voor lezen ② zal alle gevonden tekst geel markeren in het hele document
Zoeken in ③ om aan te geven of je in de tekst van het document wilt zoeken of in de kop-voettekst
Opmaak ④ om bepaalde opmaak te zoeken. Als je geen tekens invult zal bijvoorbeeld “vet en cursief’ gevonden worden. Als je er tekens bij opgeeft alleen de tekens in die opmaak.
Met de knop Geen opmaak ⑤ kun je die opmaak weer verwijderen.
Speciaal ⑥ om speciale tekens te zoeken zoals een Enter (=Alineamarkering), een zelf aangebracht pagina-einde of een vaste spatie. Die worden met een code weergegeven in het zoekvak (^p is bijvoorbeeld een Enter).

Excel

In Excel vind je Zoeken op de tab Bestand in het menu bij Zoeken en selecteren. Ook hier ga je met Volgende zoeken steeds naar het volgende zoekresultaat.

Met Opties kun je het dialoogvenster uitgebreid of minder uitgebreid maken.

Binnen ①: hier geef je aan of je alleen wilt zoeken in het huidige werkblad of in het hele bestand (werkmap).
Zoekrichting ②: geef aan of je rij voor rij wilt doorzoeken of kolom voor kolom.
Zoeken in ③: geef aan in welke soort gegevens je wilt zoeken (bijvoorbeeld in formules of notities).
Identieke hoofdletters/kleine letters en Identieke celinhoud ④ zijn hetzelfde als bij Word.
Opmaak ⑤ geeft je de optie om bepaalde opmaak te zoeken of de zoektekst in een bepaalde opmaak (net als bij Word)
Alles zoeken ⑥ breidt het dialoogvenster nog verder uit en toont alle plekken waar het zoekresultaat staat. Dit kun je gebruiken om erheen te gaan (erop klikken).
Vergeet niet dat je een dialoogvenster groter kunt maken als je rechtsonder deze puntjes ziet!

PowerPoint

In PowerPoint is veel hetzelfde als in Word. Het dialoogvenster is vrij eenvoudig. Wat hier bijzonder is, is dat er ook een knop Vervangen aanwezig is. Daarmee kun je de zoektekens laten vervangen door andere tekens.

Wat PowerPoint heeft en de andere programma’s niet, is het vervangen van een gebruikt lettertype in de hele presentatie.

Outlook

Outlook is wat zoeken betreft een buitenbeentje. Allereerst is van belang in welk onderdeel je zoekt: in de mail, agenda of contacten.
Als je in het zoeken-vak van de mail klikt en iets typt vult het programma dit aan voorgestelde teksten, namen van geadresseerden of afzenders, bestanden waar de tekst in kan voorkomen enzovoort.

Tegelijk zal er een speciaal tabblad Zoeken verschijnen waarin je het zoeken verder kunt opgeven. Belangrijk is bijvoorbeeld het Bereik waar gezocht wordt (Huidige postvak, huidige map, Alle postvakken, Submappen of Alle Outlookitems).

Dat speciale tabblad Zoeken past zich aan het onderdeel van Outlook aan waar je zoekt.
Zo ziet het eruit in de Agenda ① en zo in de Contacten ②.

24 september 2024

Afbeeldingsindelingen in Word

Blog

Altijd een lastig onderwerp in een training: wat gebeurt er nu met een afbeelding in Word en met de tekst die er wel/niet omheen gaat.
Wat er gebeurt, hoe je dat kunt beïnvloeden en welke instellingen er mogelijk zijn leer je in deze blog.

Dit blog sluit aan bij de minicursus op YouTube: https://youtu.be/XmAEyxIqT78

Afbeeldingen en objecten

Wat ik hier ga uitleggen geldt niet alleen voor afbeeldingen, maar ook voor andere objecten. Denk aan vormen, grafische vormen, SmartArts en grafieken.

Bij al die elementen zie je een knop ernaast, als die is geselecteerd: de knop Indelingsopties.

In het lint heb je ook bij afbeeldingen en objecten die geselecteerd zijn een speciaal tabblad, waar je die opdrachten ziet die speciaal bedoeld zijn voor dit object. Hieronder zie je die tabbladen voor een afbeelding en een vorm.

Afbeeldingen en objecten kun je altijd invoegen met kopiëren en plakken. In Word vind je de opties in het menu Invoegen in de groep Illustraties.

Welke indelingsopties zijn er en wat betekent dat?

Als de afbeelding is geselecteerd en je klikt op de knop Indelingsopties, dan zie je welke mogelijkheden er zijn. Symbolisch zie je ook al een beetje wat dit inhoudt.

In tekstregel: De afbeelding of het object wordt als een teken in de tekst behandeld, dus net als een getypte letter. Dit betekent dat de afbeelding op dezelfde manier wordt geplaatst als tekst: de omliggende tekst zal zich nooit om de afbeelding heen gaan.
Vierkant: De tekst loopt in een rechthoek rondom de afbeelding. Dit zorgt ervoor dat de tekst rondom de afbeelding loopt en na de afbeelding weer verder gaat.
Dicht opeen: De tekst loopt dicht om de contouren van de afbeelding, waardoor er minder witruimte rondom de afbeelding is dan bij de vierkantoptie. Of de vorm echt wordt gevolgd is afhankelijk van de afbeelding. Bij een afbeelding zal dit bijna nooit het geval zijn, maar bij een vorm of pictogram wel.
Transparant: Er is bijna geen verschil met Dicht opeen, maar het kan in sommige situaties net wat dichter erop zitten.
Boven en onder: De tekst loopt alleen boven en onder de afbeelding, en niet aan de zijkanten.
Achter tekst: De afbeelding wordt achter de tekst geplaatst, waardoor de tekst over de afbeelding heen loopt.
Voor tekst: De afbeelding wordt voor de tekst geplaatst, waardoor de tekst achter de afbeelding verdwijnt.

Vergeleken met de andere opties is In tekstregel duidelijk anders dus. De tekst zal er nooit langs kunnen lopen. Versleep je een afbeelding dan zie je heel duidelijk het verschil. In tekstregel kun je het alleen verplaatsen tussen andere tekens. Bij het wijzigen van de afmetingen wordt het als het ware een groter of kleiner teken.

Welke indelingsoptie wordt standaard gebruikt?

Dat kun je zelf bepalen bij de Word-instellingen. Bestand > Opties > Geavanceerd > bij Knippen, kopiëren en plakken.

Vaste positie op de pagina

Naast deze indelingsopties kun je ook kiezen voor een vaste plaats op de pagina. Dan komt een afbeelding bijvoorbeeld altijd precies rechtsboven op de pagina te staan. Dat regel je via de knop Positie in het lint. Zo weet je zeker dat een afbeelding bijvoorbeeld linksonder aan de pagina staat.

Die vaste posities zijn alleen mogelijk als het niet in de tekstregel staat. Als je voor en achter een afbeelding in een tekstregel een Enter plaatst (hiermee zet je de afbeelding in een eigen alinea), dan kun je wel de uitlijnknoppen gebruiken om de afbeelding links, midden of rechts in de marges te krijgen.

19 augustus 2024

Pas op met kopiëren van tabelformules

Blog

Als lijsten, filteren, sorteren een onderwerp is bij een Excel-training, vertel ik altijd dat het verstandig is om van die gegevens een Excel-tabel te maken. Dat heeft heel veel voordelen. Een hele tijd geleden heb ik hierover al eens een videotip gemaakt “20 voordelen van Excel-tabellen en 1 nadeel”. Ondanks dat die al 6 jaar oud is, is die nog steeds actueel! Dit is de link: https://youtu.be/YOGNJyaDIKU

Maar naast dat ene nadeel dat genoemd is in die videotip, is ook een waarschuwing op zijn plaats. Namelijk over het kopiëren van formules die in zo’n tabel voorkomen. Daarover gaat dit blog.
Bekijk eventueel ook deze snelle korte tip over tabelformules: https://youtu.be/JWj_x4alq_k

Als voorbeeld gebruik ik deze data.

Even: hoe maak je een Excel-tabel?

Je maakt hiervan een tabel door het bereik te selecteren:

Invoegen > Tabel ①.
Pas indien nodig het voorgestelde bereik aan en controleer of de eerste rij inderdaad kopteksten (=kolomomschrijvingen) zijn en kies OK ②.
Je gegevens zien er dan anders uit: het is duidelijk dat ze bij elkaar horen ③!
Er is ook een extra tabblad Tabelontwerp bijgekomen als een cel in die tabel is geselecteerd ④.
Een tabel krijgt altijd een naam (Tabel1, Tabel2, etc) die je via dat speciale tabblad kunt aanpassen.

Tabelformules

Wanneer je berekeningen maakt in de tabel of daarbuiten met verwijzingen naar cellen in de tabel, worden standaard geen ‘normale’ celverwijzingen gebruikt (zoals =A2 of A1:A10).
In plaats daarvan zie je tabelformules. Die verwijzen met speciale teksten naar de elementen in zo’n tabel.

Kopiëren van cellen (normale situatie)

Wanneer je een cel kopieert heb je hiervoor vele mogelijkheden. Natuurlijk “kopiëren & plakken” of de sneltoetsen hiervoor (Ctrl+c & Ctrl+v). Maar heel vaak wordt ‘doorvoeren’ gebruikt met de ‘vulgreep’. De vulgreep is dat blokje dat rechtsonder een cel staat en waar je muisaanwijzer een zwarte plus wordt. Door te slepen kun je kopiëren naar de aansluitende cellen (naar rechts, omlaag, links en omhoog).

Het maakt geen enkel verschil welke manier van kopiëren je gebruikt in Excel: het eindresultaat is altijd hetzelfde.

Kopiëren van tabelformules

Er is echter wel een verschil tussen kopiëren & plakken en doorvoeren met de vulgreep als het gaat om tabelformules!

Bij ① zie je de tabelformule =Tabel1[@KW1]+Tabel1[@KW2]: tel op de inhoud van de huidige rij van kolom KW1 (B2) en KW2 (C2).

Resultaat na “kopiëren & plakken”: =Tabel1[@KW1]+Tabel1[@KW2]
De formule blijft hetzelfde: de verwijzingen blijven naar dezelfde cellen verwijzen ②.
Resultaat na doorvoeren naar rechts: =Tabel1[@KW2]+Tabel1[@KW3]
De formule is aangepast: de verwijzingen zijn opgeschoven naar rechts en KW2 (C2)en KW3 (D2) worden opgeteld! ③

Je ziet het bijvoorbeeld ook bij deze formule: =SOM(Tabel1[KW1])
Hier worden de verkopen van kolom KW1 van de tabel allemaal opgeteld.

Kopieer & Plak je dit naar de cel ernaast, dan blijft het de optelling van KW1.
Maar gebruik je doorvoeren naar rechts dan krijg je de optelling van KW2!
Zou je hem nog verder doorvoeren, dan gebeurt er nog wat anders, zoals je in onderstaande afbeelding ziet. Drie cellen naar rechts is het de optelling geworden van de 1^e kolom van de tabel (de Datum-kolom).

Kun je ook ‘normale’ celverwijzingen gebruiken in tabellen?

Ja dat kan ook, maar dan moet je ze zelf typen en kun je niet op de cellen klikken (dan gebruikt Excel standaard de tabelformule).
Maar je moet je dus wel realiseren wat er gebeurt als je tabelformules kopieert!

Eventueel kun je bij de instellingen van Excel aangeven dat je die tabelformules niet wilt gebruiken.
Pas op: dit is een instelling voor jouw Excel-programma en is dus niet gekoppeld aan een bestand! Met andere woorden: collega’s die in hetzelfde bestand werken kunnen hiervoor een andere instelling hebben!

Bestand > Opties.
Kies de rubriek Formules >Schakel het selectievakje bij Tabelnamen gebruiken in formules uit.

Moet je dan geen tabel meer gebruiken?

Nee, dat zou mijn advies absoluut niet zijn. De voordelen blijven wat mij betreft opwegen tegen dit nadeel! Realiseer je dus het volgende als je berekeningen maakt met gegevens uit een Excel-tabel.

Kopiëren & Plakken
De tabelformule blijft hetzelfde.
Doorvoeren met de vulgreep
De tabelformule wordt aangepast.
In feite ‘gedraagt’ de tabelformule zich nu als normale celverwijzingen. Die zouden ook bij het kopiëren naar rechts aangepast worden van =B2+C2 naar =C2+D2 (op welke manier je die dan ook zou kopiëren).

En zorg dat als je Excel-bestanden gebruikt waar anderen ook in werken, dat zij dit ook weten!

21 juli 2024

Bespaar tijd met deze 10 Excel-tips

Blog

Poppetje met Excel-hoofd en klok in de hand

Een van de doelstellingen die mensen vaak aangeven bij een cursus, is dat ze tips willen hebben om sneller te kunnen werken. Ik heb daar al eerder een blog over gemaakt (https://toels-pc.nl/blog-sneller-werken). In dit blog 10 tips om tijd te besparen met Excel.
Tip 8 is een tip over een vrije nieuwe functionaliteit.

Kopieer de cel erboven
Kopieer de cel links van huidige cel
Snel naar de onderkant gaan
Snel tot de onderkant selecteren
Kopieer tot de onderkant
Combineer tips
Snel navigeren naar een ander werkblad
Gebruik de (nieuwe) navigatie
Draai de instelling om met Ctrl
Doorvoeren met de rechtermuisknop

Er zijn ook twee Snelle Korte Tips waarin deze tip worden gedemonstreerd. De eerste is te vinden via https://youtu.be/AHqQa7ZHMWE en de tweede volgt op 15 juli.

Tip 1 Kopieer de cel erboven

Met de sneltoets Ctrl+d kun je altijd de inhoud en opmaak van de cel erboven kopiëren.

Tip 2 Kopieer de cel links van huidige cel

Vrijwel vergelijkbaar: dat kan ook met de cel links van de actieve cel met de sneltoets Ctrl+r.

Tip 3 Snel naar de onderkant gaan

Je hebt een (lange) lijst en wilt snel naar de onderkant. Hiervoor is een sneltoets Ctrl+↓.
Voorwaarde is dat de kolom geen lege cellen bevat, want anders ga je met deze sneltoets naar de cel bóven de eerste lege cel. Je moet het dan enkele keren herhalen.

Tip 4: Snel tot de onderkant selecteren

Als je Shift ingedrukt houdt en dan de pijltoetsen gebruikt, selecteer je de cellen vanaf het startpunt.
Combineer je deze tip met de vorige: met Ctrl+Shift+↓ selecteer je vanaf de startcel tot de onderkant. Dit werkt ook als je bijvoorbeeld met =SOM( zo’n bereik wilt selecteren.

Tip 5: Kopieer tot de onderkant

In een lange lijst kun je een berekening heel snel naar de andere cellen van de lijst kopiëren als de kolom ervoor ook is gevuld: met een dubbelklik op de vulgreep!

Tip 6: Combineertip

De vorige tip is handig, maar soms moet je het naar beneden kopiëren en is de kolom ervoor niet (helemaal) gevuld. Dan werkt dat niet!

Daarom een workaround.

① Ga naar de onderkant van de lijst in een andere wél helemaal gevulde kolom (sneltoets: Ctrl+↓).
② Ga vervolgens in dezelfde rij naar de kolom waarvan je de inhoud wilt kopiëren.
③ Gebruik de sneltoets Ctrl+Shift+↑ om te selecteren tot en met de cel die je wilt kopiëren.
④ Gebruik de sneltoets Ctrl+d om de inhoud van de bovenste cel te kopiëren naar de andere geselecteerde cellen.

Tip 7: Snel navigeren naar een ander werkblad

Zeker als er veel werkbladen in een bestand zijn is dit een handige tip.

Klik met de rechtermuisknop op de navigatieknoppen bij de werkbladen.
Kies het werkblad waar je naar toe wilt gaan en OK (of dubbelklik erop).

Tip 8: Gebruik de (nieuwe) navigatie

Sinds een tijdje heeft Excel ook een handige navigatiemogelijkheid in het menu Beeld [View].

Kies Beeld > Navigatie ①.
Je hebt nu een overzicht van alle werkbladen ② en klikt op het blad waar je heen wilt.
Via de rechtermuisknop kun je een blad een andere naam geven, verbergen en verwijderen ③.
Via de driehoek kun je elementen zien op een blad, zoals bereiken, tabellen en namen en grafieken ④.

Tip 9: Draai de instelling om met Ctrl

Slepen met de linkermuisknop kan ook met Ctrl ingedrukt. Vaak wordt er dan net een andere actie uitgevoerd.
In tip 5 heb je gezien hoe je met de vulgreep gegevens kunt doorvoeren door te slepen. Wat er dan gebeurt is afhankelijk van de celinhoud. Tekst, getallen en berekeningen worden gekopieerd, van een datum wordt een reeks gemaakt en van speciale teksten ook.
Met Ctrl draai je dit om!

Zet de muis op het blokje: wacht tot je een zwarte plus ziet! ①
Dit is de standaardmanier zonder ctrl ②.
Sleep met Ctrl ingedrukt met de linkermuisknop ③.
De situatie wordt omgedraaid bij getallen, datums en ‘bekende’ teksten!

Tip 10: Doorvoeren met de rechtermuisknop

Aanvullend op de tip hierboven: je kunt ook slepen met de rechtermuisknop. Excel maakt dan geen keuze voor wat je gaat doen, maar je kiest zelf uit een menu.

21 juni 2024

Het nieuwe lettertype in Office-programma’s

Blog

Misschien dat je het al hebt gemerkt: Microsoft heeft in de Office-apps (Word, Excel, PowerPoint en Outlook) sinds een tijdje een ander lettertype als je begint met een nieuw leeg document. Tot een tijdje geleden was het standaardlettertype Calibri en nu is dat Aptos.
Los van het feit of je het een ‘mooi’ lettertype vindt: velen vinden het hinderlijk dat er weinig onderscheid is tussen vette tekst in Aptos en normale tekst.

Tijdens mij trainingen krijg ik daarom vaak de vraag of dit aangepast kan worden. En het antwoord is: ja, dat kan. Maar het gaat niet in alle Office-apps op dezelfde manier.
In deze blog laat ik je zien hoe je terug kunt keren naar Calibri of naar een lettertype dat jouw voorkeur heeft voor nieuwe bestanden. Ik heb bijvoorbeeld voorkeur voor Verdana.

LET OP: als je een sjabloon gebruikt, bijvoorbeeld voor document in de huisstijl van je bedrijf, zal het deze lettertypewijziging niet van belang zijn. En onderstaande stappen gelden voor nieuwe lege documenten die je gaat maken in de programma’s.

Wat is een nieuw leeg bestand eigenlijk?

Het is in ieder geval niet een bestand waarin niets is geregeld. Sterker nog: er is al heel veel geregeld. Denk bijvoorbeeld aan het papierformaat, de marges, het lettertype, de uitlijning, is het staand of liggend.
Wat ik bedoel is: het bestand dat je maakt als je kiest voor Bestand > Nieuw > Leeg … [File > New > Blanc ... Of met de sneltoets Ctrl+n.

Standaardlettertype wijzigen in Word

In Word is het centrale woord hierbij de sjabloon NORMAL.DOTM. Dat is de basis voor een nieuw leeg Word-document. Je kunt zonder standaardlettertype wijzigen, zonder dat je deze speciale sjabloon opent.

Maak in een document via Start > Lettertype [Home > Font] de gewenste instelling via de knop bij ①.
Je krijgt dan het dialoogvenster Lettertype waar je de gewenste instelling maakt ②.
Daarna kies je voor Als standaard instellen [Set as Default] ③.
In het dialoogvenster dat dan verschijnt kies je de onderste optie ④.
Misschien dat je bij het afsluiten van Word nog een vraag krijgt of je de wijzigingen in NORMAL.DOTM wilt opslaan. Kies dan Ja.

Standaardlettertype wijzigen in Excel

Hier moet je naar de standaardinstelling van Excel zelf om het lettertype voor nieuwe lege werkmappen te vervangen.

Kies Bestand > Opties [File > Options].
Kies links de rubriek Algemeen [General] ①.
Blader naar het onderdeel Wanneer nieuwe werkmappen worden gemaakt [When creating new workbooks].
Kies daar het gewenste lettertype en lettergrootte ②.

Je krijgt een melding dat het pas ingaat als je Excel afsluit en daarna weer start.

Standaardlettertype wijzigen in PowerPoint

In dit programma is het iets ingewikkelder.

Maak een nieuwe lege presentatie.
Kies Ontwerpen ① > menu bij Varianten > Lettertypen ② [Design > Variants > Fonts] >kies Office 2013-2022 ③ (of maak een andere keuze).
Je hebt nu het lettertype gewijzigd voor de huidige presentatie.
Nu moet dit nog doorgevoerd worden als je nieuwe standaard.
Houd de presentatie verder leeg en kies Bestand > Opslaan als [File > Save as].
- Kies Bladeren [Browse].
- Geef het bestand de naam Blank ①.
- Geef als type op PowerPoint-sjabloon [PowerPoint Template] ②.
  WAARSCHUWING: Omdat je kiest voor het bestandstype “sjabloon” zal het bestand automatisch op de juiste plaats worden opgeslagen. Kies dus niet een andere locatie!
Nu moet je opletten. Je begint een nieuwe lege presentatie vanaf nu met Bestand > Nieuw > Default Theme [File > New > Default Theme] en niet meer met Lege presentatie [Blank Presentation]!

Alternatief: je kunt in de laatste stap het bestand ook opslaan als een sjabloon met een andere naam, bijvoorbeeld Mijn Lege Presentatie. Die gebruik je dan steeds als basis voor nieuwe

Standaardlettertype wijzigen in Outlook

In Outlook wijzig je het weer op een andere manier.

Kies Bestand > Opties [File > Options].
Ga naar de rubriek E-mail [Mail] ①.
Kies Briefpapier en Lettertype [Stationary and Fonts] ②.
Kies in het vervolgscherm voor het tabblad Persoonlijk e-mailpapier [Personal Stationary] ③.
Kies Lettertype onder Nieuwe e-mailberichten [Font New mail messages] ④ en maak kies daarna het lettertype en evt. grootte ⑤.

23 mei 2024

Werken met vensters/schermdelen

Blog

Decoratief: verschillende citaten als "dit had ik eerder moeten weten".

Vaak heb je meer bestanden tegelijk geopend, soms zelfs meer per programma. Je hebt bijvoorbeeld 2 Word-documenten of Excel-bestanden tegelijk geopend. Hoe kun je daar nu handig mee werken? Of je nu één scherm hebt of met meer schermen werkt: deze tips kunnen altijd handig zijn. Vaak krijg ik een “had ik dit maar geweten” als ik het tijdens een training gebruik.

Windows-manier: twee venster onder/naast elkaar

Een standaardmanier van Windows om dit snel voor elkaar te krijgen is met een sneltoets:

Windows+→ (of met ←↓↑)
zet het huidige venster rechts op het scherm zetten en toont de andere links. Klik op het venster dat je links wilt zien.
In Windows 11 kun je bij een venster ook op het knopje wijzen en dan kiezen op welke manier je het scherm ingedeeld wilt hebben. Het huidige venster komt dan op die plaats en daarna kun je voor de overige vensters ook op een schermdeel klikken.

Binnen een programma overschakelen naar ander bestandsvenster

Wanneer je met Word, Excel of PowerPoint twee (of meer) bestanden geopend hebt, kun je in deze programma’s naar die andere geopende bestanden met het lint of met een sneltoets.

Ctrl+F6 schakelt over naar het volgende geopende bestand en dat kun je herhalen: je komt vanzelf weer terug bij het bestand waarmee je begon.
Shift is ook hierbij de ‘omdraaitoets’: je gaat met Ctrl+Shift+F6 in dezelfde volgorde weer terug.
Via het lint doe je dit met Beeld > Ander venster. Je ziet dan alle geopende bestanden en kiest welke je wilt zien.

Twee delen van een bestand gelijktijdig zien

Soms is het handig om 2 delen van een programma tegelijk te kunnen zien. Het meest wordt dit gebruikt in Excel: je wilt bijvoorbeeld de gegevens op rij 100 zien, maar ook van de bovenste rijen. In PowerPoint kan het soms handig zijn bij het maken van een dia als je een andere dia ook kunt zien. In Excel is het handig als je verschillende werkbladen tegelijkertijd wilt zien.

Hierover heb ik ook een videotip gemaakt. Klik hier om die te bekijken.

Met Beeld > Nieuw venster maak je voor het document waar je mee bezig bent een tweede venster. Je zit dat in de titelbalk: er komt een nummer bij. Dit kun je herhalen voor een 3^e, 4^e enz aantal venster.
Met Beeld > Alle vensters kun je deze vensters dan samen rangschikken op het scherm.

Met Word en PowerPoint worden niet alleen de verschillende vensters van hetzelfde bestand hiermee geranhschikt op het scherm, maar óók andere geopende bestanden.
Excel heeft het wat dat betreft beter geregeld: die vraagt bij Alle vensters of het gaat om alle venster die er in het programma geopend zijn ①, of alleen de vensters van de actieve werkmap ② (=huidige bestand).

Scherm splitsen

Word en Excel hebben de mogelijkheid het scherm te splitsen.

Beeld > Splitsen.

Je scherm wordt in tweeën gedeeld, maar blijft in hetzelfde venster. Elk deel heeft een schuifbalk ①, zodat je door te scrollen een ander deel van het bestand op het scherm kunt weergeven.
Door je muis op een splitsing te plaatsen kun je die verplaatsen ②.

De splitsing ongedaan maken doe je weer via het lint met Beeld > Splitsing verwijderen.

Ook hier is het in Excel weer net even anders. Die splitst het scherm in 4 delen. Waar exact, hangt af van de actieve cel. Alle rijen erboven worden de horizontale splitsing en alle kolommen links ervan de verticale. Heb je zoals in de afbeelding I28 geselecteerd ①, dan zullen de eerste 27 rijen het bovenste schermdeel vormen en de kolommen A:H het linkerschermdeel. Scrollen doe je met de schuifbalken ②.

Titels blokkeren op het scherm en op de afdruk

Excel heeft daarnaast de mogelijkheid om titels te blokkeren. Dat betekent dat de eerste rijen/kolommen op het scherm altijd blijven staan. Ook kun je titels blokkeren voor het afdrukken (of het maken van een PDF). Dat geef je apart aan.

Titels blokkeren op je scherm

Beeld > Blokkeren.
Maak een keuze.
- Titels blokkeren werkt op basis van de actieve cel (net als splitsen). Alle rijen erboven en alle kolommen links van de actieve cel worden vastgezet.
- Bovenste rij blokkeren zet alleen rij 1 vast.
- Eerste kolom blokkeren zet alleen kolom A vast.

Titels blokkeren bij het afdrukken of maken van een PDF

Kies Pagina-indeling > Afdruktitels.
Geef aan welke rij(en) en welke kolom(men) je op elke pagina herhaald wilt hebben.

LET OP:

Titels blokkeren en Afdruktitels zijn instellingen die opgeslagen worden. Dat geldt niet voor de andere schermindelingsopties die hier besproken zijn. Ook hier weer een uitzondering voor Excel: splitsen wordt ook in Excel opgeslagen!
Wil je een andere titelblokkering dan de huidige, dan moet je eerst kiezen voor Blokkeren > Titelblokkering opheffen.

Extra tips voor Excel-gebruikers

Soms wil je titels blokkeren gebruiken, maar gaat het eigenlijk alleen maar om één belangrijke rij. De rest staat er ‘toevallig’ boven. Bekijk onderstaande voorbeeld maar eens De eerste rijen bevatten informatie die niet per se op het scherm hoeft te blijven. Dat is pas van belang vanaf rij 10 (als je de datum ook wilt zien) of rij 11.

Hoe regel je dat dan?

Je zorgt dat de eerst getoonde regel rij 10 is ①.
Je selecteer daarna rij A12 en kies Beeld > Blokkeren > Titels blokkeren.
Je krijgt dan de situatie van ②.

Heb je een voorbeeld als dit, dan vormen de cellen van rij 11 in feite een tabel. Maak er dan een Excel-tabel van, want dan hoef je de eerste rij met de kolomomschrijvingen niet te blokkeren. Dat gebeurt dan automatisch!

6 mei 2024

Is het een getal of niet?

Blog

Afbeelding met een vraagteken: twee lijsten met getallen. Zoek de verschillen (die zijn er niet)

Is het nu een getal of niet?

Een geregeld terugkerend probleem in Excel is de vraag waarom een berekening niet werkt. Dan kan de formule die is gemaakt wel goed zijn, maar de cellen waarmee gerekend worden bevatten dan vaak niet de juiste ‘soort’ gegevens.
Kijk maar eens naar dit voorbeeld.

Zoek de verschillen!

Zie jij een verschil tussen de gegevens in kolom A en D

Toch is er een belangrijk verschil, kijk maar als je ermee wilt rekenen. In zowel kolom B als in E staat de berekening dat de cel ervoor vermenigvuldigd moet worden met 10.

Hetzelfde probleem zie je in onderstaand voorbeeld. Ook hier lijken er geen verschillen tussen de kolommen A en D, maar als je die cellen vermenigvuldigt met 10 zie je het verschil.

Een ander voorbeeld met een datum. Zoals je wellicht weet is een datum in Excel een getal. Daarom kun je ermee rekenen, zoals je ziet in kolom E: de functie MAAND berekent het maandnummer uit een datum. Maar in kolom B, waar dezelfde maandfunctie is gebruikt werkt dit niet.

Wat betekenen de foutmeldingen?

De foutmelding #WAARDE! betekent vaak dat er gerekend wordt met een tekst.

Kijk maar.

De foutmelding #GETAL! krijg je als een formule of functie een ongeldige numerieke waarde heeft.
Wanneer je het waarschuwingspictogram gebruikt bij de voorbeelden van eerder, dan is de melding veel cryptischer!

Wat is een gegevenstype?

Vooral bij berekeningen is het gegevenstype van een cel belangrijk. Het gegevenstype geeft aan wat voor een soort gegeven het is. Excel kent als gegevenstypes vooral getal en tekst. En zoals aangegeven kun je met een tekst niet rekenen!

Hoe herken je een getal als tekst?

Natuurlijk zie je het direct als je een berekening wilt uitvoeren, omdat je dan een foutmelding krijg. Een andere manier om het te ontdekken: wijzig de opmaak van de cel.
Kies je bijvoorbeeld voor minder decimalen bij de getallen in de A-kolom, dan zal er niets gebeuren. Terwijl dat wel effect heeft op ‘echte’ getallen zoals in kolom D.

Bij een datum is het eenvoudiger om het te ontdekken. Selecteer de cel en kijk in de formulebalk. Als het een echte datum is zie je daar altijd d-m-jjjj.

Tips voor het invoeren van getallen

Als je een getal of een datum wilt typen, typ dan altijd de simpelste manier. Maak dan het getal op zoals je het graag wilt zien.

Enkele voorbeelden:

Invoer: 1000
Gewenste opmaak: € 1.000,00
Kies dan uit menu:

Invoer: 5,5
Gewenste opmaak: 5,50
Kies dan uit menu:

Invoer: 1234,5
Gewenste opmaak: 1.234,50
Kies dan uit menu Meer getalnotaties en dan:

Hoe los je het probleem met ‘verkeerde getallen’ op?

Meestal komen deze “verkeerde” gegevens komen uit een ander systeem, bijvoorbeeld een boekhoudprogramma, een CRM-systeem of een download van een bank.
Hoe zet je dat dan om naar een echt getal waar je ook mee kunt rekenen?

Hiervoor zijn verschillende manieren.

Groene driehoekje

Als je een groen driehoekje bij een cel ziet staan, is dat een waarschuwing. Dat kan een waarschuwing zijn voor een mogelijk verkeerd gegevenstype, maar ook dat er een formule een foutmelding als resultaat heeft (zie afbeeldingen hierboven).
Plaats je de muisaanwijzer op het pictogram, dan kun je de waarschuwing lezen. Onderstaande melding gaat over een verkeerd gegevenstype in die cel. Dat is dan eenvoudig op te lossen.

Selecteer alle cellen met deze melding.
Klik op het waarschuwingspictogram (dat staat als je bijvoorbeeld een kolom hebt geselecteerd boven- of onderaan).
Kies Converteren naar getal.

Kopiëren en plakken speciaal

Een beetje een vreemde oplossing is dit, maar het werkt vaak wel.

Zet ergens in een lege cel een 1.
Kopieer die cel.
Selecteer de cellen met het verkeerde gegevenstype
Kies Plakken speciaal
Vul het dialoogvenster zo in en kies OK.

Verkeerde datum

Hierboven heb ik al aangegeven hoe je een echte datum kunt herkennen: selecteer de cel en kijk in de formulebalk. Staat daar inderdaad gewoon een groot getal, dan kun je daar niet zomaar een datum van maken door er een datumopmaak voor te kiezen. Je moet er echt een datum van maken. Dat gaat het handigste op de volgende manier.

Selecteer de cellen met de datums
Kies Gegevens > Tekst naar kolommen.
Kies in de eerste stap Gescheiden > Volgende
Zorg in de 2^e stap dat er geen scheidingsteken is geselecteerd (het is namelijk helemaal niet de bedoeling om de gegevens te scheiden!) > Volgende.
Kies in de derde stap Datum en selecteer de manier waarop in de selectie de datumnotatie staat (hier dus JMD: jaar, maand, dag) > Voltooien.

En nu zijn het allemaal echte datums geworden (ja ik gebruik expres ‘datums’ om het onderscheid te maken met data=gegevens).

18 april 2024

Filter-functie

Blog

Filteren, maar dan anders …

In mijn trainingen en tijdens één-op-één-spreekuurgesprekken zie ik dat een van de meest gebruikte onderdelen van Excel het werken met lijsten/tabellen is. Het is een hele mooie manier om gegevens weer te geven, je kunt goed sorteren en je kunt selecteren (wat Excel dan ‘filteren’ noemt). De meeste mensen schakelen hiervoor de filterknoppen in (tab Gegevens > Filter).

Maar bij het filteren werk je altijd IN de lijst zelf. Filter je bijvoorbeeld op de kolom waar de provincienaam staat op Gelderland, dan worden de rijen waarin dat woord niet voorkomt verborgen.
Hieronder zie je een voorbeeld van zo’n lijst ①. Je ziet dat de filterknop is ingeschakeld ②.
Aan de trechter bij de provinciekolom ③, zie je dat er in die kolom is gefilterd. Als je de muis erboven houdt, zie je ook waarop is gefilterd ④. De rijen waar Gelderland niet staat, zijn verborgen ⑤ (je mist rijnummers!).

Er zijn 2 problemen met deze manier van filteren.
Als naast je tabel/lijst ook gegevens staan en die staan toevallig in verborgen rijen, dan zie je die ook niet meer!
En bij gebruik van de filterknoppen is het niet mogelijk tegelijk een lijst op je scherm te krijgen van de gegevens van Gelderland en een andere lijst met Friesland-gegevens.
Met de functie Filter kun je dat wel. Je laat de originele lijst intact en zet het resultaat met het filter ergens anders neer. En er is een connectie met de basislijst, dus als daar iets wijzigt, zie je dat automatisch ook in de gefilterde lijst.

Je bent nog beter af als je voor het filteren van je cellenbereik een tabel maakt. Daarover heb ik eerder een blog geschreven (Maak altijd een tabel).
In de blog Filteren met slicers heb je gezien dat er nog een manier is om te filteren, maar die werkt ook met verborgen rijen!

De lijst uit de afbeelding bij ① gebruik ik nu om de functie Filter te beschrijven.

De functie Filter() in een cellenbereik

Het gebruik van deze functie is vrij eenvoudig. Je moet opgeven welke cellen de lijst vormen (A2:C19) en je moet opgeven op welke kolom je wilt filteren (B2:B19) en wat hiervoor het gewenste selectiecriterium is (=”Gelderland”).
Het resultaat is dat je alle gegevens uit die tabel van de provincie Gelderland ziet. Je moet er nog wel zelf de kolomomschrijvingen boven zetten.

Er is wel iets bijzonders: je ziet dat er een blauwe rand om de cellen met het resultaat. Die rand geeft het ‘overloopgebied’ aan.
Technisch gezien staat de berekening alleen in de linkerbovenhoek van het blauw omrande gebied. De uitkomst loopt over in de andere cellen die nodig zijn.
Dat overloopgebied is flexibel: als in de lijst iets wijzigt (naam 13 wordt verwijderd of Naam 5 wordt toch Gelderland), dan zal het overloopgebied automatisch wijzigen.

Foutmelding #OVERLOPEN!

De cellen in het overloopgebied moeten echt leeg zijn, anders krijg je een foutmelding #OVERLOPEN! (Engels: #SPILL!).
Je ziet met een gestreepte rand hoe groot het overloopgebied is. In dit voorbeeld zie je direct wat de niet lege cel is. Maar als het overloopgebied groot is, zie je het misschien niet zo snel. Door te klikken op het getoonde pictogram kun je de cel(len) met de problemen laten selecteren. Haal je die leeg, dan werkt het weer!
Excel noemt het overloopgebied een ‘dynamisch gebied’.

Meer flexibiliteit

Je maakt het geheel natuurlijk nog flexibeler door in de filterberekening “Gelderland” niet zelf te typen, maar door te verwijzen naar een cel waar je de provincienaam kunt invullen. Als je die provincienaam dan ook nog met gegevensvalidatie laat kiezen uit een lijst wordt het natuurlijk helemaal fraai (zie de blog over Gegevensvalidatie).

LET OP

Er zijn met deze functie wel enkele dingen waar je rekening mee moet houden.

Zorg dat je bij de verwijzingen naar de lijst/tabel de kolomomschrijving niet meeneemt (dus niet A1:C19): je geeft dus alleen de rijen met de gegevens op (A2:C19). Dat doe je natuurlijk ook bij de kolomgegevens waarop je wilt filteren.
Dat betekent dus ook dat je de kolomomschrijving zelf boven de kolommen moet zetten boven de filterresultaten.
De opmaak van de cellen van de basislijst wordt niet overgenomen in de gefilterde lijst.
De cellen in het overloopgebied hebben een eigen opmaak. Hieronder zie je daarvan een duidelijk voorbeeld. Je moet dus de opmaak van de cellen in het overloopgebied wellicht aanpassen.

De functie Filter() met een tabel

Wanneer je van je cellenbereik een tabel hebt gemaakt, werkt de filterfunctie op dezelfde manier. Je verwijst dan echter niet naar de cellen met de gegevens, maar je gebruikt de tabelverwijzingen. Die krijg je automatisch als je bij het maken over de cellen sleept met je muis.

Hieronder zie je een afbeelding hiervan. Van de celen A1:C19 is met Invoegen > Tabel een tabel gemaakt ①. Die heeft de naam Tabel1 gekregen. Die naam zie je terug als je de cellen selecteert in de functie Filter bij de lijst . Selecteer je de cellen met de provincienamen, dan wordt dit automatisch Tabel1[Provincie] (=de kolom Provincie van Tabel 1) ②.

Voordeel bij een tabel

Stel dat er een nieuwe naam bij komt in de basislijst ①. Als het een cellenbereik moet je in de filterfunctie het cellenbereik B2:B19 aanpassen naar B2:B20 om die nieuwe gegevens ook mee te nemen ②!
Bij een tabel is dat niet nodig ③! Dit is weer een voorbeeld van waarom je met een tabel beter af bent!

27 maart 2024