Het werken met Excel-tabellen biedt vele voordelen, die bij de meeste gebruikers niet bekend zijn.  Excel-tabellen vormen de basis voor het maken van dynamische en transparante rapportages. In dit artikel ga ik in op een aantal belangrijke aspecten daarvan.

Wat zijn de voordelen van Excel-tabellen?

1. Als u draaitabellen maakt die gebaseerd zijn op Excel-tabellen, dan zal de brondata waarop de tabellen gebaseerd zijn automatisch meegroeien. U hoeft het bronbereik niet meer handmatig aan te passen voor de draaitabel indien er nieuwe records worden toegevoegd. Om de draaitabel aan te passen hoeft u slechts de gegevens te verversen via Gegevens > Alles Vernieuwen.
2. De kopteksten worden automatisch vastgezet als u via de tabel naar beneden scrolt. U hoeft de titels derhalve niet meer vast te zetten.
3. Uw grafieken worden automatische bijgewerkt als nieuwe data onderaan wordt toegevoegd.
4. Formules in tabellen zijn transparanter, omdat niet meer met cel-referenties wordt gewerkt maar met zogenaamde gestructureerde referenties.
5. U kunt dynamische validatie- en keuzelijsten
6. Excel-tabellen zijn een logische stap voor het werken met Power Query en Power BI.
7. U kunt slicers gebruiken om uw datarecords te filteren.

Hoe maakt u een Excel-tabel?

Er zijn twee methoden. Uit het lint kunt kiezen:

• Invoegen > Tabellen > Tabel
• Of: Start > Stijlen > Opmaken als tabel

Daarna verschijnt er een nieuw tab genaamd Tabelontwerp. Het verdient aanbeveling om de tabel een naam te geven. Via Tabelontwerp > Eigenschappen > Tabelnaam kunt u een naam invoeren. U mag geen spaties gebruiken in de naam.

Tevens kunt u via Tabelontwerp > Tabelstijlen de tabel voorzien van een gewenste kleur.

Het is ook mogelijk om een eigen tabelstijl aan te maken via de optie Nieuwe tabelstijl.

Hoe dien ik formules in Excel-tabellen te interpreteren?

In Excel-tabellen komen geen celverwijzingen meer voor, maar verwijzingen naar kolomnamen die tussen [ ] staan en naar [@ ] als het verwijzingen betreft naar een cel uit een kolom.

Hoe maak ik een cumulatieve optelling vanaf het begin tot en met de actieve cel in een Excel-tabel?

Stel de kolomnaam is Credit.  De formule wordt dan:

=SOM(INDEX([Credit];1;0):[@Credit])

Met de eerste INDEX-functie verwijst u naar de eerste rij uit de kolom Credit. De : geeft aan tot en met. De term [@Credit] verwijst naar de actuele rij vanuit de kolom Credit.

Hoe maak ik een cumulatieve berekening vanaf de huidige rij tot en met de laatste rij in een Excel-tabel?

In het voorbeeld willen we de Netto Huidige Waarde te berekenen voor elke rij in kolom I (Nodig).

De formule luidt als volgt:

=([@Totaal]>0)*NHW(Rendement;([@Totaal]:INDEX([Totaal];AANTALARG([Jaren]);1)))

Als de cel in kolom Totaal groter dan 0 is, wordt de Netto Huidige waarde berekend tegen een rendement voor het variabel bereik dat begint in de actieve rij van de kolom Totaal. Het einde van het bereik wordt berekend met de functie: INDEX([Totaal];AANTALARG([Jaren]);1))

Uit de kolom Totaal wordt de laatste actieve cel gevonden door middel van de functie AANTALARG.

Het beginpunt van het bereik wordt gescheiden van het eindpunt door middel van een dubbele punt :

Welke valkuilen zijn er bij het werken met Excel-tabellen?

U dient te letten op de volgende zaken:

• Formules in kopteksten worden bij de conversie naar een Excel-tabel omgezet naar waarden. Voor dynamische modellen kan dat rampzalig zijn. Tip: plaats de formules twee regels boven de koptekst van de feitelijke tabel.
• Datums in kopteksten worden bij de omzetting naar een Excel-tabel veranderd in een tekst. Zo nodig dient u formules die hiernaar verwijzen met behulp van de TEKST-functie aan te passen.
• Formules in Excel-tabellen kunnen niet worden beveiligd. U kunt ze door middel van gegevensvalidatie afschermen met behulp van de volgende instelling:

• Als u formules heeft met gestructureerde referenties dient u eraan te denken dat u deze formules niet gewoon naar rechts kunt kopiëren. Her verdient aanbeveling om de cursor te plaatsen op de cel waar de formule staat en van daaruit de aanbelandende cellen te markeren; dan klikt u op de formulebalk en drukt gelijktijdig op Control-Enter om de formule in de overige cellen te kopiëren.

Auteur: Tony De Jonker

Dit artikel is verschenen in cm: 2021, afl. 1.

Hier leest u meer Excel tips en trucs door Tony De Jonker