Wanneer u een Excel-tabel maakt, wijst Excel een naam toe aan de tabel en aan elke kolomkop in de tabel. Wanneer u formules toevoegt aan een Excel-tabel, worden deze namen automatisch weergegeven terwijl u de formule invoert en de celverwijzingen in de tabel selecteert. U hoeft dat dus niet handmatig te doen. Hier volgt een voorbeeld van wat er in Excel gebeurt:
| In plaats van expliciete celverwijzingen te gebruiken | Gebruikt Excel tabel- en kolomnamen |
|---|---|
| =Som(C2:C7) | = SOM(VerkPerAfd[Verkoopbedrag]) |
Die combinatie van tabel- en kolomnamen wordt een gestructureerde verwijzing genoemd. De namen in gestructureerde verwijzingen worden aangepast wanneer u gegevens toevoegt aan de tabel of eruit verwijdert.
Gestructureerde verwijzingen worden ook gebruikt wanneer u een formule maakt buiten een Excel-tabel waarin naar tabelgegevens wordt verwezen. De verwijzingen kunnen helpen bij het vinden van tabellen in een grote werkmap.
Als u gestructureerde verwijzingen wilt gebruiken in uw formule, selecteert u de tabelcellen waarnaar u wilt verwijzen in plaats van dat u de verwijzing in de formule typt. In dit voorbeeld gaat u een formule invoeren waarin automatisch gestructureerde verwijzingen worden gebruikt om het bedrag aan verkoopprovisie te berekenen.
| Verkoper | Regio | Sales Amount | Percentage provisie | Provisiebedrag |
|---|---|---|---|---|
| Arnoud | Noord | 260 | 10% | |
| Robert | Zuid | 660 | 15% | |
| Johanna | Oost | 940 | 15% | |
| Erik | West | 410 | 12% | |
| Diana | Noord | 800 | 15% | |
| Rob | Zuid | 900 | 15% |
- Kopieer de voorbeeldgegevens in de bovenstaande tabel, inclusief de kolomkoppen, en plak deze in cel A1 van een nieuw Excel-werkblad.
- Maak een willekeurige cel in het gegevensbereik en druk op Ctrl+T om de tabel te maken.
- Zorg ervoor dat het vakje Mijn tabel bevat kopteksten is ingeschakeld en selecteer OK.
- Typ een gelijkteken (=) in cel E2 en selecteer cel C2.
Op de formulebalk wordt de gestructureerde verwijzing [@[Verkoopbedrag]] weergegeven na het gelijkteken. - Typ een sterretje (*) direct achter de vierkante haak sluiten en selecteer cel D2.
Op de formulebalk wordt de gestructureerde verwijzing [@[Percentage provisie]] weergegeven na het sterretje. - Druk op Enter.
Er wordt automatisch een berekende kolom gemaakt en Excel kopieert de formule omlaag in de kolom, waarbij de waarde voor elke rij wordt aangepast.
Wat gebeurt er wanneer ik expliciete celverwijzingen gebruik?
Als u in een berekende kolom expliciete celverwijzingen invoert, kan het lastiger zijn om te zien wat er door de formule wordt berekend.
- Selecteer cel E2 in het voorbeeldwerkblad
- Typ op de formulebalk =C2*D2 en druk op Enter.
De formule wel omlaag wordt gekopieerd in de kolom, maar er worden geen gestructureerde verwijzingen gebruikt. Als u nu bijvoorbeeld een kolom invoegt tussen de bestaande kolommen C en D, moet u de formule aanpassen.
Hoe wijzig ik de naam van een tabel?
Wanneer u een Excel-tabel maakt, wordt er automatisch een standaardnaam toegewezen (Tabel1, Tabel2 enzovoort). U kunt de tabelnaam echter wijzigen in een naam met een duidelijkere betekenis.
- Selecteer een cel in de tabel om het tabblad Tabelontwerp op het lint weer te geven.
- Typ de gewenste naam in het vak Tabelnaam en druk op Enter.
In onze voorbeeldgegevens hebben we de naam DeptSales gebruikt.
Hanteer de volgende regels voor tabelnamen:
- Gebruik geldige tekens Begin een naam altijd met een letter, een onderstrepingsteken (_) of een backslash (\). Gebruik letters, cijfers, punten of onderstrepingstekens voor de rest van de naam. De tekens "C", "c", "R" en "r" kunt u niet gebruiken als naam omdat deze tekens zijn ingesteld als snelkoppelingen voor het selecteren van de kolom of rij voor de actieve cel wanneer u het teken typt in het vak Naam of Ga naar .
- Gebruik geen celverwijzingen Namen mogen niet hetzelfde zijn als celverwijzingen, zoals Z$100 of R1C1.
- Gebruik geen spatie om woorden van elkaar te scheiden Spaties mogen niet worden gebruikt in de naam. U kunt het onderstrepingsteken (_) en de punt (.) gebruiken om woorden te scheiden. Bijvoorbeeld VerkPerAfd, Sales_Tax of Eerste.kwartaal.
- Gebruik niet meer dan 255 tekens Een tabelnaam mag niet meer dan 255 tekens bevatten.
- Gebruik unieke tabelnamen Dubbele namen zijn niet toegestaan. In Excel wordt geen onderscheid gemaakt tussen hoofdletters en kleine letters in namen, dus als u 'Verkoop' typt, maar er al een tabel met de naam 'VERKOOP' aanwezig is in dezelfde werkmap, wordt u gevraagd een unieke naam te kiezen.
- Een object-id gebruiken Als u van plan bent om tabellen, draaitabellen en grafieken te combineren, is het een goed idee om het objecttype voor uw namen te gebruiken. Bijvoorbeeld: tbl_Sales voor een verkooptabel, pt_Sales voor een verkoopdraaitabel en chrt_Sales voor een verkoopgrafiek of ptchrt_Sales voor een verkoopdraaigrafiek. Hierdoor blijven al uw namen in een geordende lijst staan in Namen beheren.
Syntaxisregels voor gestructureerde verwijzingen
U kunt gestructureerde verwijzingen ook handmatig invoeren of wijzigen in de formule, maar dan is het wel handig als u weet hoe de syntaxis van een gestructureerde verwijzing in elkaar zit. Laten we dit eens bekijken aan de hand van de volgende voorbeeldformule:
=SOM(VerkPerAfd[[#Totalen],[Verkoopbedrag]],VerkPerAfd[[#Gegevens],[Provisiebedrag]])
Deze formule bevat de volgende onderdelen van een gestructureerde verwijzing:
- **Tabelnaam:**DeptSales is een aangepaste tabelnaam. De naam verwijst naar de tabelgegevens, zonder rijen voor koptekst of totaal. U kunt een standaardtabelnaam gebruiken, bijvoorbeeld Tabel1, of deze wijzigen in een aangepaste naam.
- Kolomaanduiding:[Verkoopbedrag] en [Provisiebedrag] zijn kolomaanduidingen met de namen van de betreffende kolommen. Ze verwijzen naar de kolomgegevens, zonder eventuele kolomkoppen of totaalrijen. Plaats aanduidingen altijd tussen vierkante haken, zoals in de afbeelding.
- Aanduiding van items:[#Totals] en [#Data] zijn aanduidingen van speciale items die verwijzen naar bepaalde gedeelten van de tabel, zoals de rij met totalen.
- Tabelaanduiding:[[#Totalen];[Verkoopbedrag]] en [[#Gegevens]; [Provisiebedrag]] zijn tabelaanduidingen die de buitenste gedeelten van de gestructureerde verwijzing voorstellen. OUTER-verwijzingen volgen op de tabelnaam en u plaatst deze onderdelen tussen vierkante haken.
- Gestructureerde verwijzing:(VerkPerAfd[[#Totals],[Verkoopbedrag]] en VerkPerAfd[[#Data],[Provisiebedrag]] zijn gestructureerde verwijzingen, voorgesteld door een tekenreeks die begint met de tabelnaam en eindigt met de kolomaanduiding.
Hanteer deze syntaxisregels als u met de hand gestructureerde verwijzingen wilt maken of bewerken:
- Gebruik vierkante haken rond aanduidingen Alle tabelaanduidingen, kolomaanduidingen en aanduidingen van speciale items moeten tussen vierkante haken staan ([ ]). Het is nodig dat de vierkante haken van de andere aanduidingen binnen de vierkante haken van een aanduiding staan. Bijvoorbeeld: =VerkPerAfd[[Verkoper]:[Regio]]
- Alle kolomkoppen zijn tekenreeksen U hoeft ze echter niet tussen aanhalingstekens te plaatsen wanneer ze worden gebruikt in een gestructureerde verwijzing. Getallen of datums, zoals 2014 of 1/1/2014, worden ook als tekenreeksen gezien. U kunt geen expressies gebruiken met kolomkoppen. De expressie VerkPerAfdBJOverzicht [[2014]:[2012]] werkt bijvoorbeeld niet.
Gebruik vierkante haken rond kolomkoppen met speciale tekens Als er speciale tekens voorkomen, moet de hele kolomkop tussen vierkante haken worden geplaatst. Dit betekent dat een kolomaanduiding tussen dubbele vierkante haken moet staan. Bijvoorbeeld: =VerkPerAfdBJOverzicht[[Totaal bedrag $]]
Hier volgt de lijst met speciale tekens die extra vierkante haken in de formule nodig hebben:
- Tab
- Nieuwe regel
- Enter-teken
- Komma (,)
- Dubbele punt (:)
- Punt (.)
- Vierkante haak openen ([)
- Vierkante haak sluiten (])
- Hekje (#)
- Enkel aanhalingsteken (')
- Dubbel aanhalingsteken (")
- Accolade openen ({)
- Accolade sluiten (})
- Dollarteken ($)
- Accent circonflexe (^)
- En-teken (&)
- Sterretje (*)
- Plusteken (+)
- Gelijkteken (=)
- Minteken (-)
- Groter dan (>)
- Kleiner dan (<)
- Deelteken (/)
- Bij teken (@)
- Backslash (\)
- Uitroepteken (!)
- Haakje openen ()
- Haakje sluiten ())
- Procentteken (%)
- Vraagteken (?)
- Backtick (')
- Puntkomma (;)
- Tilde (~)
- Onderstrepingsteken (_)
- Gebruik een escape-teken voor bepaalde speciale tekens in kolomkoppen Sommige tekens hebben een speciale betekenis en moeten daarom samen met een escape-teken in de vorm van een enkel aanhalingsteken (') worden gebruikt. Bijvoorbeeld: =VerkPerAfdBJOverzicht['#Artikelen]
Hier volgt de lijst met speciale tekens die een escape-teken (') nodig hebben in de formule:
- Vierkante haak openen ([)
- Vierkante haak sluiten (])
- Hekje (#)
- Enkel aanhalingsteken (')
- Bij teken (@)
Gebruik een spatie om de leesbaarheid van een gestructureerde verwijzing te verbeteren Met behulp van spaties kunt u de leesbaarheid van een gestructureerde verwijzing verbeteren. Bijvoorbeeld: =VerkPerAfd[ [Verkoper]:[Regio] ] of =VerkPerAfd[[#Koppen]; [#Gegevens]; [Percentage provisie]]
Het is raadzaam in de volgende gevallen een spatie te gebruiken:
- Na de eerste vierkante haak openen ([)
- Vóór de laatste vierkant haak sluiten (])
- Na een komma
Verwijzingsoperatoren
Als u een bereik van cellen opgeeft, kunt u voor meer flexibiliteit de volgende verwijzingsoperatoren gebruiken om kolomaanduidingen te combineren.
| Deze gestructureerde verwijzing: | Verwijst naar: | Met behulp van: | Wat overeenkomt met celbereik: |
|---|---|---|---|
| = VerkPerAfd[[Verkoper]:[Regio]] | Alle cellen in twee of meer aangrenzende kolommen | : (dubbelepunt) de bereikoperator | A2:B7 |
| =VerkPerAfd[Verkoopbedrag];VerkPerAfd[Provisiebedrag] | Een combinatie van twee of meer kolommen | ; (puntkomma) de verenigingsoperator | C2:C7, E2:E7 |
| =VerkPerAfd[[Verkoper]:[Verkoopbedrag]]VerkPerAfd[[Regio]:[Percentage provisie]] | De intersectie van twee of meer kolommen | (spatie) de intersectieoperator | B2:C7 |
Aanduidingen van speciale items
Als u wilt verwijzen naar bepaalde gedeelten van een tabel, zoals de rij met totalen, kunt u een van de volgende aanduidingen van speciale items gebruiken in uw gestructureerde verwijzingen.
| Deze aanduiding van een speciaal item: | Verwijst naar: |
|---|---|
| #Alles | De volledige tabel, inclusief kolomkoppen, gegevens en totalen (indien van toepassing). |
| #Gegevens | Uitsluitend de gegevensrijen. |
| #Koppen | Uitsluitend de veldnamenrij. |
| #Totalen | Uitsluitend de rij met totalen. Als er geen rij met totalen is, is het resultaat leeg. |
| #Deze rij of @ of @[Kolomnaam] |
Alleen de cellen in dezelfde rij als de formule. U kunt deze aanduidingen niet combineren met andere aanduidingen van speciale items. Gebruik ze om impliciet snijpuntgedrag voor de verwijzing af te dwingen of om impliciet snijpuntgedrag te vervangen en naar afzonderlijke waarden in een kolom te verwijzen. In Excel wordt in tabellen met meerdere rijen met gegevens de aanduiding #Deze rij automatisch gewijzigd in de kortere @-aanduiding Als de tabel echter maar één rij bevat, wordt in Excel de aanduiding #Deze rij niet vervangen. Als de tabel echter maar één rij bevat, wordt de aanduiding #This rij niet vervangen. Dit kan leiden tot onverwachte resultaten als u meer rijen toevoegt. Om berekeningsproblemen te voorkomen, moet u meerdere rijen in de tabel invoeren voordat u de formules met gestructureerde verwijzingen invoert. |
Gestructureerde verwijzingen in berekende kolommen kwalificeren
Als u een berekende kolom maakt, zult u vaak een gestructureerde verwijzing gebruiken in de formule. Het betreft hier een niet-gekwalificeerde of volledig gekwalificeerde gestructureerde verwijzing. Als u bijvoorbeeld de berekende kolom Provisiebedrag maakt waarmee het provisiebedrag in euro wordt berekend, kunt u de volgende formules gebruiken:
| Type gestructureerde verwijzing | Voorbeeld | Opmerking |
|---|---|---|
| Niet-gekwalificeerd | =[Verkoopbedrag]*[Percentage provisie] | Hiermee worden de betreffende waarden uit de huidige rij vermenigvuldigd. |
| Volledig gekwalificeerd | =VerkPerAfd[Verkoopbedrag]*VerkPerAfd[Percentage provisie] | Hiermee worden de betreffende waarden per rij voor beide kolommen vermenigvuldigd. |
De algemene regel is als volgt: als u gestructureerde verwijzingen in een tabel gebruikt, bijvoorbeeld een berekende kolom, kunt u een niet-gekwalificeerde gestructureerde verwijzing gebruiken, maar als u de gestructureerde verwijzing buiten de tabel gebruikt, moet u een volledig gekwalificeerde gestructureerde verwijzing gebruiken.
Voorbeelden van het gebruik van gestructureerde verwijzingen
Hier volgen enkele manieren om gestructureerde verwijzingen te gebruiken.
| Deze gestructureerde verwijzing: | Verwijst naar: | Watovereenkomtmet celbereik: |
|---|---|---|
| =VerkPerAfd[[#Alles];[Verkoopbedrag]] | Alle cellen in de kolom Verkoopbedrag. | C1:C8 |
| =VerkPerAfd[[#Koppen];[Percentage provisie]] | De kop van de kolom Percentage provisie. | D1 |
| =VerkPerAfd[[#Totalen];[Regio]] | Het totaal van de kolom Regio. Als er geen rij Totalen is, is het resultaat leeg. | B8 |
| =VerkPerAfd[[#Alles];[Verkoopbedrag]:[Percentage provisie]] | Alle cellen in Verkoopbedrag en Percentage provisie. | C1:D8 |
| =VerkPerAfd[[#Gegevens];[Percentage provisie]:[Provisiebedrag]] | Uitsluitend de gegevens van de kolommen Percentage commissie en Commissiebedrag. | D2:E7 |
| =VerkPerAfd[[#Koppen];[Regio]:[Provisiebedrag]] | Uitsluitend de koppen van de kolommen tussen Regio en Provisiebedrag. | B1:E1 |
| =VerkPerAfd[[#Totalen];[Verkoopbedrag]:[Provisiebedrag]] | De totalen van de kolommen Verkoopbedrag tot en met Provisiebedrag. Als er geen rij Totalen is, is het resultaat leeg. | C8:E8 |
| =VerkPerAfd[[#Koppen];[#Gegevens];[Percentage provisie]] | Uitsluitend de kop en de gegevens uit Percentage provisie. | D1:D7 |
| =VerkPerAfd[[#Deze rij];[Commissiebedrag]] of =VerkPerAfd[@Provisiebedrag] |
De cel op het snijpunt van de huidige rij en de kolom Provisiebedrag. Bij gebruik in dezelfde rij als een kop- of totaalrij, wordt de fout #VALUE! geretourneerd. Als u de langere vorm van deze gestructureerde verwijzing typt (#Deze rij) in een tabel met meerdere rijen met gegevens, wordt deze automatisch vervangen door de kortere vorm (@). Beide werken op dezelfde manier. |
E5 (als de huidige rij 5 is) |
Strategieën voor het werken met gestructureerde verwijzingen
Als u met gestructureerde verwijzingen werkt, moet u rekening houden met het volgende.
Formule automatisch aanvullen gebruiken Formule automatisch aanvullen kan bijzonder nuttig zijn bij het invoeren van gestructureerde verwijzingen en het gebruik van een juiste syntaxis. Zie De functie Formule automatisch aanvullen gebruiken voor meer informatie.
Bepalen of gestructureerde verwijzingen moeten worden gegenereerd voor tabellen in semi-selecties Als u een formule maakt, wordt een celbereik in een tabel semi-geselecteerd en wordt in de formule automatisch een gestructureerde verwijzing ingevoerd in plaats van het celbereik. Door dit semi-selectiegedrag is het veel gemakkelijker om een gestructureerde verwijzing in te voeren. U kunt dit in- of uitschakelen door het selectievakje Tabelnamen in formules gebruiken in het dialoogvenster Bestandopties>>Formules>Werken met formules in of uit te schakelen.
Werkmappen gebruiken met externe koppelingen naar Excel-tabellen in andere werkmappen Als een werkmap een externe koppeling naar een Excel-tabel in een andere werkmap bevat, moet die gekoppelde bronwerkmap zijn geopend in Excel om #REF!- fouten te voorkomen in de doelwerkmap die de koppelingen bevat. Als u eerst de doelwerkmap opent en er #REF!- fouten worden weergegeven, worden deze opgelost als u vervolgens de bronwerkmap opent. Als u eerst de bronwerkmap opent, ziet u als het goed is geen foutcodes.
Een bereik naar een tabel en een tabel naar een bereik converteren Als u een tabel naar een bereik converteert, worden alle celverwijzingen in de bijbehorende absolute A1-verwijzingen gewijzigd. Als u een bereik naar een tabel converteert, worden eventuele celverwijzingen naar dit bereik door Excel niet automatisch gewijzigd in de bijbehorende gestructureerde verwijzingen.
Kolomkoppen uitschakelen U kunt de koptekst van tabelkolommen in- en uitschakelen via Veldnamenrij op het tabblad >Tabelontwerp. Als u kolomkoppen in een tabel uitschakelt, heeft dit geen invloed op gestructureerde verwijzingen waarin kolomnamen worden gebruikt en kunt u deze nog steeds in formules gebruiken. Gestructureerde verwijzingen die rechtstreeks naar de tabelkoppen verwijzen (bijvoorbeeld =VerkPerAfd[[#Headers],[%Commissie]]) resulteren in #REF.
Kolommen en rijen toevoegen aan of verwijderen uit de tabel Omdat gegevensbereiken in tabellen vaak worden gewijzigd, worden celverwijzingen voor gestructureerde verwijzingen automatisch aangepast. Als u bijvoorbeeld in een formule een tabelnaam gebruikt om alle gegevenscellen in een tabel te tellen en u vervolgens een rij met gegevens toevoegt, wordt de celverwijzing automatisch aangepast.
De naam van een tabel of kolom wijzigen Als u de naam van een kolom of tabel wijzigt, wordt deze naam door Excel automatisch gewijzigd in alle gestructureerde verwijzingen die in de werkmap worden gebruikt.
Gestructureerde verwijzingen verplaatsen, kopiëren en omlaag doorvoeren Als u een formule kopieert of verplaatst waarin een gestructureerde verwijzing wordt gebruikt, wordt deze gestructureerde verwijzing ongewijzigd gehandhaafd.
Opmerking
Kopiëren van een gestructureerde verwijzing is niet hetzelfde als omlaag doorvoeren van een gestructureerde verwijzing. Wanneer u kopieert, blijven alle gestructureerde verwijzingen ongewijzigd, terwijl wanneer u een formule doorvoert, de kolomaanduidingen door volledig gekwalificeerde gestructureerde verwijzingen als een reeks worden aangepast, zoals samengevat in de volgende tabel.
| U voert een formule in deze richting door: | Tijdens het doorvoeren drukt u op: | Vervolgens: |
|---|---|---|
| Omhoog of omlaag | Niets | Er wordt geen kolomaanduiding aangepast. |
| Omhoog of omlaag | Ctrl | De kolomaanduidingen worden als reeks aangepast. |
| Rechts of links | Geen | De kolomaanduidingen worden als reeks aangepast. |
| Omhoog, omlaag, rechts of links | Shift | Waarden in huidige cellen worden niet overschreven, maar huidige celwaarden worden verplaatst en er worden kolomaanduidingen ingevoegd. |
Meer hulp nodig?
U kunt altijd uw vraag stellen aan een expert in de Excel Tech Community of ondersteuning vragen in Community's.
Verwante onderwerpen
Tabellen maken en opmaken
Totalen berekenen van gegevens in een Excel-tabel
De opmaak van een Excel-tabel wijzigen
Het formaat van een tabel wijzigen door rijen en kolommen toe te voegen of te verwijderen
Gegevens filteren in een bereik of tabel
Een tabel naar een bereik converteren
Compatibiliteitsproblemen met Excel-tabellen
Een Excel-tabel exporteren naar SharePoint
Overzicht van formules in Excel