Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Home > Nederlandse site > Artikelen > Excel Tabellen
This page in English

Werken Met tabellen in Excel 2013, 2010 en 2007

Inleiding

Dit artikel is reeds eerder gepubliceerd op de Microsoft website

Excel 2007 is de eerste Excel versie in lange tijd die vele vernieuwingen bevat. Eén daarvan is de verbetering in de manier waarop Excel om kan gaan met gegevens tabellen. De functionaliteit (Tabellen genaamd) is in wezen een uitbreiding op de lijsten die Excel 2003 al kent.

Dit artikel introduceert deze nieuwe functionaliteit en laat zien hoe het u kan helpen in uw dagelijks werk met Excel.

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.

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 introduceert 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, 2013: =SOM(Tabel1[@Korting])

Merk op, dat Excel 2010 en 2013 een nieuwe naamgevingconventie hebben om cellen in een tabel aan te duiden:

Tabel1: De naam van uw tabel

Excel 2007 [# Deze Rij], Excel 2010 en 2013 @ : 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, 2013
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: [WorkmapNaam.xls]!TabelNaam

Om echter naar de gehele tabel te verwijzen gebruikt u nu dit:

Workmapnaam!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 t/m 2013. 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:

Links

Heeft u interesse in VBA, lees dan hier verder over Excel 2007-2013 Tabellen en VBA.

Ron de Bruin heeft een handige invoegtoepassing geschreven om makkelijker te werken met tabellen.


Vragen, suggesties en opmerkingen

Laatste 8 commentaren van in totaal 39 (Toon ze allemaal):

 


Comment by: Jan Karel Pieterse (10/16/2013 8:37:02 PM)

Hoi Martine,

Ja, dat klopt helaas. Wat je misschien het beste kunt doen is de tabel "alleen" op een apart werkblad zetten, dan is beveiligen misschien niet nodig is.
Ook kan je wel beveiligen en eventuele daardoor niet mogelijke acties door een macrootje laten doen.

 


Comment by: cees riedijk (11/14/2013 4:12:32 PM)

dit artikel over opmaken als tabel spreekt me ook aan, maar je kan beter niet zelf selecteren maar eenvoudig één cel aan te wijzen en dan voor opmaken als tabel te kiezen
dit scheelt veel tijd maar dan moet je wel zeker zijn dat je geen lege rijen en kolommen hebt

 


Comment by: Swart (3/7/2014 9:09:43 PM)

Importeren van gegevens van de bron werkmap naar nieuwe werkmap. Bij de nieuwe werkmap waarbij er kolommen met cellen en gegevens worden toegevoegd blijven bij het sorteren niet in lijn met de rijen. Hoe komt dit en is dit op te lossen?

gr. Swart

 


Comment by: Jan Karel Pieterse (3/7/2014 9:56:44 PM)

Hoi Swart,

Wellicht bestaat de tabel (ddels) uit formules en veranderen de celverwijzingen in di formules (of juist niet)?"

 


Comment by: Swart (3/10/2014 9:54:06 PM)

Fijn Jan Karel Pieterse
Alle gevulde cellen in de rijen van de ingelezen tabel, waarin steeds nieuwe regels worden toegevoegd, worden nu keurig ingelezen. De veranderingen zijn keurig na het inlezen van de gegevens overgenomen. Echter bij sorteren op de eerste kolom (projectnummer) worden de toegevoegde kolommen, deze zijn niet opgenomen in de ingelezen tabel, niet op de juiste wijze mee gesorteerd.

Maak ik een fout in het toepassen van deze twee tabellen. Of gaat dit niet. nb. alles in een excel bestand was te zwaar. Hierdoor het bestand gesplitst in twee tabellen.
Kortom bronbestand waarin generatief het project met basis gegevens wordt aangemaakt en later wordt ingelezen in een hoger gelegen tabel waarin logistieke informatie wordt toegevoegd. Deze aanvullingen moeten dus wel bij het desbetreffende project blijven.
Graag een oplossing.

m.vr.gr. Swart

 


Comment by: Jan Karel Pieterse (3/11/2014 7:11:03 AM)

Hallo Swart,

Als je een tabel sorteert wordt alles buiten de tabel genegeerd. Daar kan je helaas weinig aan veranderen. Als het echter cellen met formules zijn, kan je die in de tabel opnemen, de formules blijven bestaan als je de bron vernieuwd.

 


Comment by: swart (3/11/2014 11:44:17 AM)

Jan Karel Pieterse

U schrijft dat als je een tabel sorteert wordt alles buiten de tabel genegeerd. Op beide levels gebruik ik vast gedefinieerde tabellen die niet veranderen qua kolommen.

Ter aanvulling het volgende.
De hoger gelegen tabel bestaat uit de ingelezen tabel van de onderliggende level. De hoger gelegen tabel is uitgebreid qua kolommen. Dit is een vaste structuur qua kolommen. Alleen de regels kunnen toenemen en afnemen op basis van wat er in de onderliggende tabel is opgenomen. De formules en andere informatie zijn in de bestaande kolommen van de hoger gelegen tabel reeds aanwezig. In enkele cellen van deze bestaande, tweede level-kolommen, wordt info gewijzigd. Sorteren wordt alleen gedaan op de eerste kolom. Het probleem is dat de regels(cellen) van de kolommen van de hoger gelegen vaste tabel bij het sorteren uit de pas lopen.
Mijn vraag is de aanpak juist, door een tabellen te gebruiken op het tweede level en deze in te lezen op het hoger gelegen eerste level?

b.v.d. Swart

 


Comment by: Jan Karel Pieterse (3/11/2014 12:51:27 PM)

Hallo Swart,

Ik geloof niet dat ik het kan volgen :-) Kan je misschien een voorbeeld bestandje mailen?

 


Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: www.eileenslounge.com.

Uw naam (verplicht veld):

Uw e-mail adres (Niet verplicht, dit adres wordt niet getoond)

Uw verzoek of commentaar:

Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].