Inhoud
Inleiding
Als je liever een video bekijkt,
hier
is de opname van mijn "Modern Excel webinar" sessie van januari 2020
Veel gegevens die u in Excel gebruikt zijn ingedeeld als een tabel. Excel
heeft daar sinds Excel 2007 handige functionaliteit voor genaamd Opmaken
Als Tabel. In tegenstelling tot wat je van deze naam zou verwachten gaat
het eigenlijk helemaal niet om de opmaak, die is slechts een leuke bijkomstigheid.
Tabellen zijn heel nuttig in Excel en we leggen hier uit waarom.
Een tabel maken
Excel zal een willekeurige reeks cellen niet automatisch als een "tabel"
herkennen, u moet Excel zelf vertellen welk gegevensbereik het voortaan
als tabel moet gaan behandelen. Allereerst hebt u natuurlijk een reeks gegevens
nodig, ergens op een werkblad. Selecteer een bereik:
Figuur 1: Selecteer het tabel bereik
Vervolgens klikt u op de knop "Opmaken als Tabel". Deze vindt u in de
groep "Stijlen" op de tab "Start". (Zie figuur 2):
Figuur 2: "Opmaken als tabel" knop in de Stijlen groep op de Start tab.
Nadat u op deze knop heeft gedrukt toont Excel een nieuw element in de
gebruikersinterface –galerij genaamd- met een aantal voorgebakken formatteringsmogelijkheden
voor uw tabel. Zie figuur 3:
Figuur 3: Tabel opmaak galerij.
Selecteer één van de aanwezige opties. Excel zal vervolgens vragen welk
celbereik u tot tabel wilt omzetten (zie figuur 4). Mocht uw tabel een kopregel
hebben, zorg er dan voor dat het aankruisvakje is aangevinkt. Klik op OK
om het bereik om te zetten naar een tabel.
Figuur 4: Dialoogvenster waarin u het celbereik voor de tabel opgeeft en
of de tabel een kopregel heeft.
Na afronding van deze stappen krijgt u een tabel die er uitziet zoals
figuur 5 (met mogelijk andere kleuren).
Figuur 5: Celbereik na omzetten naar een tabel.
Dankzij Excel MVP Frederic Le Guen heb ik een kleine
kleine invoegtoepassing gemaakt die het werken met tabellen iets simpeler
maakt.
De tool voegt een tabje toe aan het lint van Excel genaamd Table Tools:
En een rechts-klik menu dat zich aanpast wanneer u in of buiten een tabel
klikt:
- In een tabel toont het rechts-klik menu een lijst met de kolommen
van de tabel zodat u snel kunt selecteren
- Buiten de tabel toont de tool een lijst met alle tabellen in de
werkmap
- Als je het tabelnaam vak in het TableTools lint gebruikt, dan zal
de tool alle queries in je werkmap nalopen om dezelfde naam wijziging
ook in de PowerQuery queries door te voeren
- Als je een kolomnaam wijzigt kijkt de tool ook alle PowerQuery queries
na en hernoemd die kolom daarin ook.
- Merk op dat de tool een verbeterde interface heeft om een bereik
om te zetten naar een tabel
- Het comment vak geeft de mogelijkheid om commentaar toe te voegen
aan de tabel die verschijnt als je een formule intikt:
Speciale functionaleit van
Tabellen
Nadat een bereik omgezet is naar een Tabel, krijgt dit extra functionaliteiten
toegewezen die uw dagelijks werk met deze gegevens makkelijk maken:
Geïntegreerde autofilter en sorteer mogelijkheden
Wanneer uw tabel een kopregel heeft, dan zal deze automatisch de autofilter
en sorteer knoppen krijgen, zie figuur 6:
Figuur 6: sorteer en filter dropdowns
De knopjes laten bovendien voortaan zien of er bijvoorbeeld een filter
is toegepast op de kolom waar ze bij horen. In vorige versies van Excel
was dit nauwelijks zichtbaar (donkerblauw pijltje in plaats van zwart).
Eenvoudig selecteren
Het selecteren van een hele kolom of rij in de tabel is eenvoudig: beweeg
uw muisaanwijzer naar de top van de tabel (of naar de meest linkse kolom
als u een rij wilt selecteren) totdat de aanwijzer verandert in een omlaag
wijzend pijltje (figuur 7) en klik. Het gegevensbereik van die kolom (rij)
zal worden geselecteerd. Klik nogmaals om de kopregel ook mee te selecteren.
Figuur 7: selecteren van een hele kolom data in een tabel
Door in de buurt van de linkerbovenhoek van de tabel te klikken selecteert
u alle gegevens in de tabel (de muisaanwijzer verandert naar een pijltje
dat naar rechtsonder wijst, zie figuur 8).
Figuur 8: Het selecteren van alle data in een tabel gaat met slechts 1 klik
van de muis (of 2 als u ook de kopregel mee wilt selecteren).
Tijdens scrollen blijft de kopregel altijd automatisch in beeld
Als uw tabel niet helemaal op het scherm past en u omlaag scrolt,
dan toont Excel 2007 een prettige nieuwe functie: de kolom letters worden
tijdelijk vervangen door de tekst in de kopregel van uw tabel (maar alleen
zolang de cursor binnen de tabel blijft!). Zie figuur 9. Zo weet u altijd
in welke kolom van uw tabel u bezig bent
Figuur 9: Tabel kop blijft zichtbaar tijdens scrollen
Automatische uitbreiding
Zodra u iets naast of onder een tabel typt neemt Excel automatisch aan,
dat u de tabel wilt uitbreiden met een nieuwe kolom of rij en past daarom
de afmetingen van uw tabel aan. Gelukkig kunt u deze handeling ook weer
ongedaan maken en tevens kunt u dit gedrag uitschakelen.
Automatisch aanpassen van de opmaak
Zodra u een rij of een kolom invoegt dan wel verwijdert, zal Excel de
tabel opmaak aanpassen. Alternerende arceringen en/of kleuren worden vanzelf
weer in orde gemaakt.
Automatische aanpassing van doelbereiken van grafieken, draaitabellen
en andere objecten
Zodra u regels toevoegt aan een tabel (en het automatisch aanpassen van
de tabel aan heeft laten staan), past Excel voor u automatisch alle doelbereiken
aan van objecten die gebruik maken van uw tabel, zoals grafieken en draaitabellen.
Deze optie is heel erg nuttig, omdat met het aanpassen van de grootte ook
bijvoorbeeld grafieken automatisch worden bijgewerkt. Als u bijvoorbeeld
wekelijks uw verkoopcijfers invoert, dan hoeft u niet langer alle daarop
gebaseerde grafieken bij te werken, of te werken met ingewikkelde dynamische
bereiknamen.
Tabel Opties op het lint
Zodra u een cel selecteert binnen een tabel zult u een nieuwe tab zien
verschijnen op het lint, genaamd "Hulpmiddelen voor tabellen", "Ontwerpen".
Figuur 10 toont u hoe het lint eruit zal zien als u op deze tab klikt.
Figuur 10: Het lint nadat u op de tab "Ontwerpen" heeft geklikt.
Elke groep op deze tab wordt in onderstaande paragraafjes besproken.
Eigenschappen groep
Middels de eigenschappen groep (zie figuur 11 hieronder) kunt u de volgende
dingen doen:
Figuur 11: Eigenschappen groep op de tab "Ontwerpen" van de "Hulpmiddelen
voor tabellen"
De naam van uw tabel wijzigen
De naam van een tabel wordt gebruikt zodra u naar cellen in een tabel
wilt verwijzen in werkblad formules. Als u dus een duidelijk herkenbare
naam geeft, dan zijn uw formules ook eenvoudiger te begrijpen.
Tabelgrootte wijzigen
Hier klikt u op wanneer u de afmetingen van uw tabel aan wilt passen.
De groep Extra
Deze groep (zie figuur 12) kent drie besturingselementen:
Figuur 12: Extra groep op de tab "Hulpmiddelen voor tabellen"
Samenvatten met draaitabel
Het is evident wat dit besturingselement voor u doet. Zodra uw draaitabel
klaar is, hoeft u zich geen zorgen meer te maken over het bronbereik van
uw draaitabel. Dit wordt automatisch gelijk gehouden aan de afmetingen van
uw tabel. Dus zodra u gegevens aan uw tabel toevoegt zal Excel uw tabel
afmetingen aanpassen en daarmee ook het bronbereik van uw draaitabel. U
moet nog wel de draaitabel vernieuwen om het resultaat van uw toevoegingen
te kunnen zien. Excel 2007 kent een duidelijk betere gebruikersinterface
voor het maken van draaitabellen, een van de krachtigste en veel te weinig
gebruikte functionaliteiten van Excel.
Duplicaten verwijderen
Een nieuwe mogelijkheid in Excel 2007. Nadat u op deze knop heeft gedrukt,
presenteert Excel een dialoogvenster waarin u de kolommen kunt selecteren
die gebruikt zullen worden om te bepalen of de gegevens in uw tabel uniek
zijn of niet. (zie figuur 13)
Figuur 13: Venster Dubbele waarden verwijderen
Deze functie is uitermate handig bij bijvoorbeeld het controleren en
bijwerken van adresgegevens van klanten en bij het controleren op dubbele
boekingen in uw boekhouding.
Converteren naar bereik
Deze knop zal de tabel weer degraderen tot een normale reeks cellen.
Wees erop verdacht, dat als u dit doet, eventuele draaitabellen die op de
tabel gebaseerd zijn hun bronbereik kwijt raken. U kunt dit repareren door
in de draaitabel wizard het bronbereik opnieuw aan te geven. Doet u dit
niet, dan kan de draaitabel niet meer vernieuwd worden.
De groep Externe tabelgegevens
Deze groep (zie figuur 14) heeft alles te maken met de brondata van uw
tabel en is alleen van toepassing als de gegevens in uw tabel bijvoorbeeld
het resultaat van een database- of webquery zijn, of afkomstig zijn van
een Sharepoint lijst.
Figuur 14:
De groep Externe tabelgegevens op de tab "Hulpmiddelen voor tabellen"
van het lint Deze groep kent de volgende 5 knoppen:
Exporteren
Dit is in feite een zogeheten combobutton. U krijgt twee opties wanneer
u erop drukt: "Tabel naar SharePoint lijst exporteren" and "Tabel naar Visio
draaidiagram exporteren". Op de exacte betekenis van deze twee opties wordt
hier niet verder ingegaan
Vernieuwen
Gebruik deze combobutton om de externe gegevens in uw tabel te vernieuwen.
Als u op de pijl onder deze knop klikt, krijgt u meerdere keuzemogelijkheden,
waaronder "Alles vernieuwen", waarmee u de gegevens van alle tabellen en
draaitabellen in uw bestand vernieuwt.
Gegevensbereikeigenschappen
Als uw tabel externe gegevens bevat, dan kunt u met deze knop de instellingen
daarvan wijzigen. Een mogelijk handige instelling kan hier zijn om de gegevens
te laten vernieuwen bij het openen van uw bestand. Let er wel op, dat Excel
2010 (en 2007) een nieuwe beveiligingsoptie kent. Deze zorgt ervoor, dat
u na openen van uw bestand eerst toestemming moet geven om externe gegevensbronnen
te vertrouwen. Dit werkt eigenlijk net zo als met macro’s, deze moet u ook
eerst toestaan (welke beveiligingswaarschuwingen u krijgt hangt af van uw
beveiligingsinstellingen).
Weergeven op Server
Als uw tabel afkomstig is van een Sharepoint lijst, dan kunt u middels
deze knop de bron van de lijst (in Sharepoint dus) laten tonen in een internet
Explorer venster.
Koppeling verbreken
Mocht uw tabel gebaseerd zijn op een Sharepoint lijst, dan kunt u met
deze knop de tabel loskoppelen van die lijst. Doe dit alleen als u de lijst
niet langer wilt laten bijwerken met de gegevens uit Sharepoint.
Groep Opties voor tabelstijlen.
Hier kunt u beïnvloeden op welke manier de beschikbare tabel stijlen
toegepast worden op uw tabel (zie figuur 15).
Figuur 15: Opties voor tabel stijlen groep op het lint.
Veldnamenrij
Wanneer u het vinkje uit dit vakje verwijdert, dan verwijdert Excel de
veldnamenrij van uw tabel. De cellen van de koprij worden geleegd, maar
de oude veldnamen worden wel onthouden door Excel, dus als u het vakje weer
aanvinkt, dan keren uw kopregels weer terug. Mocht u intussen iets anders
hebben ingevoerd in die kopregel, dan zal Excel die informatie niet overschrijven,
maar een nieuwe regel invoegen om de kopregel te kunnen tonen. Cellen onder
de tabel worden dan wel omlaag geduwd.
Totaalrij
Als u dit vakje aanvinkt, dan voegt Excel een totaal rij toe onder uw
tabel.
Gestreepte rijen
Vink dit vakje aan als u een alternerende arcering van uw tabel regels
wilt. Deze arcering wordt automatisch door Excel bijgewerkt als u rijen
invoegt of verwijderd. Alternerende arcering maakt uw tabel makkelijker
leesbaar.
Eerste kolom
Als u een afwijkende opmaak wilt hebben voor de eerste kolom van uw tabel,
plaatst u hier een vinkje.
Laatste kolom
Hiermee verandert u de opmaak van de laatste kolom van uw tabel.
Gestreepte kolommen
Net als bij gestreepte rijen kunt u hiermee de kolommen alternerende
kleuren geven.
Stijlen voor tabellen groep
De laatste groep op de Hulpmiddelen voor tabellen tab die hier beschreven
wordt is de groep "Stijlen voor tabellen" (zie figuur 16).
Figuur 16: Stijlen voor tabellen groep
Klik op de dropdown pijl om een galerij met mogelijke tabelopmaken te
krijgen. Beweeg uw muis over een opmaak om in uw spreadsheet te kunnen zien
hoe uw tabelopmaak eruit zal komen te zien en klik pas als u de gewenste
opmaak heeft gevonden.
Nieuwe tabelstijl
Hiermee kunt u een eigen tabel stijl ontwerpen. Een nieuwe stijl wordt
automatisch opgeslagen met het bestand dat u open heeft. Wilt u een tabel
stijl kopiëren naar een ander bestand, dan moet u de tabel met de gewenste
stijl naar dat bestand kopiëren. U kunt de tabel vervolgens direct weer
verwijderen. Een snelle manier om een tabel stijl te maken is door er eerst
één te kiezen die dicht bij uw gewenste stijl ligt en vervolgens deze te
dupliceren. Hiervoor beweegt u uw muis naar een gewenste tabelstijl, klikt
u met uw rechter muisknop en kiest u "Dupliceren" in het menuutje.
Wissen
Deze optie gebruikt u om de tabel stijl kompleet te verwijderen van de
tabel. Het bereik blijft wel een tabel, maar heeft geen bijzondere formattering
meer. Eventueel ingestelde getalsformattering blijft behouden.
Verwijzen naar cellen in een tabel (gestructureerde verwijzingen)
Excel 2007 introduceerde een nieuwe syntax waarmee naar cellen binnen
een tabel verwezen kan worden.
Om te zien hoe dit in zijn werk gaat klikt u in een cel direct rechts
van uw tabel en drukt u het is-gelijk teken in. Typ vervolgens SOM( en klik
dan op een cel binnen uw tabel. U krijgt een formule die er als volgt uit
kan zien:
Excel 2007: =SOM(Tabel1[[#Deze rij];[Korting]])
Excel 2010 en nieuwer: =SOM(Tabel1[@Korting])
Merk op, dat vervolgens vanaf Excel 2010 een nieuwe naamgevingconventie
is ingevoerd om cellen in een tabel aan te duiden:
Tabel1: De naam van uw tabel
Excel 2007 [# Deze Rij], Excel 2010 en nieuwer @: Geeft aan dat de gegevens
in dezelfde rij staan als de rij met uw formule
[Korting] : De kolom binnen uw tabel
Nog wat voorbeelden:
Beschrijving
Excel 2007
Excel 2010 en nieuwer
Gehele tabel
=Tabel1
=Tabel1
Gehele tabel (2)
=Tabel1[#Alles]
=Tabel1[#Alles]
Dezelfde rij in de tabel
=Tabel1[[#Deze rij][Discount]]
=Tabel1[@Discount]
Tabel kop
=Tabel1[#Kopteksten]
=Tabel1[#Kopteksten]
Tabel totaal rij
=Tabel1[#Totalen]
=Tabel1[#Totalen]
Vanwege deze naamgevingconventie is het niet mogelijk om twee keer dezelfde
tekst in een cel in de kopregel te hebben. Zodra u probeert een kop in te
typen die al bestaat, zal Excel deze "corrigeren" door er een getal achter
te plaatsen zodat de koptitel uniek blijft.
Een prettige nieuwe functionaliteit merkt u na het invoeren van de formule
naast de tabel: De tabel wordt uitgebreid met de kolom waarin u de formule
tikte én de formule wordt automatisch gekopieerd zodat de hele kolom ermee
wordt gevuld. Tevens heeft Excel een standaard kopje gemaakt. Zeer handig
als u een berekening op enkele gegevens uit elke rij in uw tabel wilt maken.
Beide acties kunt u overigens eenvoudig ongedaan maken middels control-z.
Verwijzen naar
een tabel vanuit een andere werkmap
Alhoewel een tabel aangeduid wordt met een bereiknaam is er toch een
probleem als u de tabel wilt gebruiken vanuit een andere werkmap. De bereiknaam
wijst namelijk alleen naar het gegevensgebied van de tabel, de koprij maakt
daar geen deel van uit. Dit betekent dat om de tabel als bronbereik van
een draaitabel te kunnen gebruiken u een andere syntaxis moet gebruiken.
Normaal gesproken zou u deze syntaxis gebruiken: [WerkmapNaam.xls]!TabelNaam
Om echter naar de gehele tabel te verwijzen gebruikt u nu dit:
Werkmapnaam!Tabelnaam
Hiermee begrijpt Excel dat u naar een tabel wilt verwijzen en neemt Excel
de koprij mee.
Conclusie
Zoals u hebt gezien, zijn tabellen een geweldig handige toevoeging aan
Excel 2007 en hoger. Hoewel een behoorlijk aantal van de mogelijkheden al
deel uitmaakten van de lijst functionaliteit van Excel 2003, zijn de nieuwe
opties die Excel 2007 toevoegt zeer de moeite waard. De meest in het oog
springende voordelen zijn:
- Geïntegreerde autofilter en sorteer mogelijkheden
- Eenvoudig selecteren van uw gegevens
- Koprij blijft altijd in beeld
- Automatisch uitbreiden van de tabel
- Automatisch herformatteren van de tabel
- Automatisch aanpassen bronbereiken van grafieken en draaitabellen
Links
Heeft u interesse in VBA, lees dan
hier verder over Excel Tabellen en VBA.
Ron de Bruin heeft een handige invoegtoepassing geschreven
om makkelijker te werken met tabellen.
