Werken met tabellen (deel 2)

Dit is het tweede deel in ons tweeluik over het werken met Excel-tabellen. Deze week: de ins- en outs in het werken met Excel-tabellen. In het eerste artikel in deze serie zie je hoe je van een tabel een Excel-tabel maakt. Essentieel om gebruik te maken van nieuwe, geavanceerde mogelijkheden. Klik hier voor het eerste deel, mocht je dat gemist hebben en kom daarna terug voor dit tweede deel.

Je hebt de Excel-tabel gemaakt! Er is nu een scala aan extra mogelijkheden beschikbaar geworden.

Excel geeft de Excel-tabel weer in een standaard streepjespatroon: rij blauw-rij wit-rij blauw, etc. Je kan verschillende tabellen ieder een eigen kleur geven om je data goed uit elkaar te houden. Ga met de cursus op een van de cellen in de tabel staan en er verschijnt rechts van de bestaande tabbladen op het lint een tabblad ‘Hulpmiddelen voor tabellen – ONTWERPEN’.

Rechts zie je het item ‘Stijlen voor Tabellen’ en als je met de muis over een van de zichtbare stijlen gaat zie je hoe die er met jou data uit gaat zien. Ook kan je met behulp van het kleine pijltje rechts van die voorbeeldkleuren nog meer mogelijkheden naar voren halen. Je ziet drie varianten (categorieën): licht-normaal-donker.

Tot slot is het mogelijk om een eigen (bedrijfs)stijl te ontwikkelen en die hier vervolgens te kiezen.

Voeg je nieuwe kolommen of regels toe, dan zal de gekozen stijl daar direct op toegepast worden: je tabel blijft daarmee een eenheid uitstralen. Daarnaast worden formules en bepaalde andere zaken 'meegenomen' in die nieuwe regels.

Als je in Excel werkt met verschillende soorten data in verschillende tabellen, is het zaak die tabellen een herkenbare naam te geven. Hierdoor hou je overzicht over je data, kan een ander het bestand ook makkelijker lezen en begrijpen en je kan de namen gebruiken in formules!

Voordeel daarvan is dat je geen absolute of relatieve verwijzing (klik hier voor item over absolute en relatieve verwijzingen) naar de data hoeft te maken en dat uitbreiding of inkrimping van de data automatisch meegenomen wordt in de formule.

Een handigheidje: geef tabellen de beginletters TB mee, dan vind je ze gegroepeerd later weer terug bijvoorbeeld bij het invoeren van een formule.

Er zijn meerdere manieren om namen te geven in Excel. Een tweetal voorbeelden in dit item.

Voorbeeld 1:

Selecteer de data in de tabel zonder de kopregel. Je ziet nu in het naamvak (links boven de aanduiding van de kolommen en rijen van het Excelsheet) de huidige naam (Tabel1) staan.

Typ hiervoor in de plaats de door jou bedachte naam (bijvoorbeeld TB_namen). Let erop dat er geen spaties in de naam mogen worden opgenomen en ook bepaalde leestekens niet (zoals het verbindingsstreepje, (vandaar het onder de regel liggende verbindingsstreepje in dit voorbeeld)). Nadeel van deze (makkelijke) variant is dat ook de oorspronkelijke naam in het systeem blijft staan, wat niet altijd even duidelijk is.

Voorbeeld 2:

Ga naar het beheer van de namen (Lint-Tabblad ‘Formules’-item ‘Gedefinieerde namen’-button ‘Namen beheren’). Er verschijnt een pop-upvenster ‘Namen beheren’. Zoek tussen de namen de Tabel (Tabel1, je ziet ook een icoontje van een Tabel ervoor staan). Ga op die naam staan en kies ‘Bewerken’. Er verschijnt een pop-upvenster ‘Naam bewerken’ met als bovenste invulvak ‘Naam’. Vul daar de door jouw gewenste naam in en klik op ‘OK’ om te bevestigen en dit venster te sluiten. Klik vervolgens in het ‘Namen beheren’ pop-upvenster voor ‘sluiten’ om terug te keren naar je Excelsheet. Nadeel is dat dit een wat omslachtiger variant is, maar je hebt maar met 1 naam voor de tabel te maken en dat is in overzicht weer een voordeel.

Een eerste duiding waarom het werken met Formules in zo’n Excel-tabel zoveel krachtiger en prettiger is dan in een gewone tabel.

In bijgaand voorbeeld hebben we een product, aantal verkochte exemplaren en een prijs in de verschillende kolommen staan. We hebben alvast een Excel-tabel gemaakt (TB_verkoop) van de beschikbare data. De totale Excel-tabel inclusief koppen bevindt zich in de cellen A3:C10.

We willen de BTW in een aparte kolom zien en vervolgens het totaalbedrag (aantal maal prijs inclusief de BTW).

De werkwijze is als volgt: 1) maak de nieuwe kolommen, 2) geef op de eerste dataregel de formule die nodig is en…………. 3) Excel doet de rest ‘vanzelf’ voor je!

Nieuwe kolommen maken

Het eenvoudigst is dit te doen door de kolomkoppen te maken. Ga rechts naast de laatst bestaande kolomkop staan (in cel D3 dus) en typ de nieuwe naam (btw). Na het geven van Enter om dit te bevestigen ‘trekt’ Excel deze kolom bij de Excel-tabel. Je ziet dat doordat de kolomkop dezelfde lay-out heeft gekregen als de andere koppen en dat de rijen meegekleurd zijn in het patroon van de Excel-tabel. Doe hetzelfde naast deze kolom ‘btw’ met de kolom ‘totaal’ door in cel E3 die kopnaam te typen.

Formule voor de btw maken

Ga in cel D4 staan, de eerste datacel in de kolom waar de formule moet komen. We willen dat in deze kolom de berekening '=6% van de prijs' wordt gemaakt (lage BTW tarief). Er zijn (natuurlijk) meer manieren om de formule te maken. De voorkeur in dit geval is als volgt:

Typ het ‘is gelijk teken’ (=) om een formule of berekening mee te starten. Klik vervolgens cel C4 aan. Je ziet nu in de formulebalk iets vreemds, in plaats van ‘C4’ wordt de naam van de kolomkop (‘prijs’) weergegeven, tussen vierkante haken en met een @ voor de naam van de kolom. Niets van aantrekken en de formule aftypen (‘*6%’). Na het ingeven van Enter wordt de formule automatisch doorgekopieerd naar de onderliggende cellen!

In de formule is de naam van de kolom opgenomen, iets dat je ook in andere formules goed kan gebruiken, maar daarover meer in een ander item.

Formule voor de totaalberekening maken

Dit werkt hetzelfde als de formule voor de BTW-berekening maken. De formule die in cel E4 moet komen te staan is ‘aantal maal (prijs+btw)’.

Ga in cel E4 staan en typ weer het ‘is gelijk teken’. Klik vervolgens op cel B4 om te verwijzen naar het aantal (en ook nu verschijnt het wat vreemd ogende ‘[@aantal]’ in plaats van het verwachte ‘B4’). Je maakt de formule af door het ‘maal teken’ (‘*’) te toetsen, gewone haakjes openen (‘(‘), op cel C4 klikken (daar is ‘ie weer: [@prijs]) het plus teken (‘+’) en D4 klikken ([@btw]). Haakje sluiten ( ‘)‘) en na ingeven van Enter wordt de formule door de hele tabel heen getrokken.

Het ‘opmaken’ van de cellen werkt net als in gewone cellen. De betreffende cellen selecteren en de Celeigenschappen aanpassen.

Als je dit met rechts muisklikken doet, staat er echter nu niet ‘Celeigenschappen’ zoals vertrouwd, maar ‘Cellen opmaken’. Je komt vervolgens gewoon in het pop-upvenster ‘Celeigenschappen’ terecht (zie hiervoor bijvoorbeeld het item Sneltoets Ctrl+1). Geef het gewenste getalformaat aan (twee decimalen en eventueel de punt als scheidingsteken voor duizendtallen) en bevestig.

Nu het fundament van de tabel er is, kunnen we gebruik maken van enkele mooie eigenschappen om meer data in de tabel te zetten.

Rechtsonder in de tabel staat een klein driehoekje, ten teken dat de tabel daar ‘ophoudt’ en uitgebreid kan worden.
Een manier is om nieuwe regels in te gaan voeren. Als je meteen onderaan de bestaande data gaat typen, neemt Excel de nieuwe regel automatisch in de tabel op!

Voorbeeld:

Ga in cel A11 staan en typ een nieuw product in (‘dropveters’). Met ‘TAB’ of ‘Pijltje naar rechts’ kom je in B11. In D11 en E11 zie je al streepjes verschijnen en ook zie je dat de regel onderdeel uitmaakt van de tabel (het kleine driehoekje is een regel naar beneden gezakt en staat nu onderaan E11).

Er kan een waarschuwingsknopje verschijnen (zie afbeelding). Je kan hier instellen dat je het automatisch aanvullen van de tabel wil uitzetten (iets dat je normaliter juist niet wil).

In cel B11 kan je het aantal ingeven (‘101’) en in cel C11 de prijs (‘0,45’). Als je vervolgens Enter geeft of Tab of anderszins de invoer in de cel bevestigt, zullen in de cellen D11 en E11 de berekeningen verschijnen, net als in de daar bovenstaande regels!

Een laatste voorbeeld van een voordeel van het werken met zo’n Excel-tabel is het werken met totalen. Er zijn ook hier verschillende manieren om aan te geven dat je dit wil gebruiken en er zijn ook diverse instellingen voor beschikbaar.

Eerste manier: ga ‘in de tabel staan’. Klik met rechtermuisknop en kies uit het popup-menu de optie ‘Tabel’ en dan de eerste mogelijkheid ‘Totalenrij’.

Er verschijnt een totalenrij direct onder de bestaande data, met tellingen onder die kolommen waarvan Excel ‘denkt’ dat dit getotaliseerd zou kunnen worden. Semi-intelligent derhalve en in ons voorbeeld ook helemaal juist: alleen in de laatste kolom een totaaltelling.

Tweede manier:
Ga ook hiervoor ‘in de tabel staan’. Klik op het lint op het speciaal verschenen tabje ‘Hulpmiddelen voor tabellen’ – ‘Ontwerpen’. Wat rechts van het midden is er een item ‘Opties voor tabelstijlen’. Vink hier de optie ‘Totaalrij’ aan en de telling verschijnt in je werkblad.

Het voert hier te ver om alle mogelijkheden van de Excel-tabellen weer te geven. Binnenkort verschijnt de eerste Exceltraining waar dit uitgebreid behandeld zal worden. Drie dingen nu als 'extra's'.

Allereerst: het totaal dat je hebt gemaakt is niet zomaar een totaal, maar een van de speciale subtotalen die Excel kent. Het is daardoor mogelijk om de data in de tabel te filteren en daarmee automatisch het juiste totaal van de filtering in beeld te brengen! Als voorbeeld: stel we willen zien wat het totaal is van alle items die meer dan € 1 kosten. Dan geven we het filter op de prijs aan (‘Getalfilter’-‘Groter dan’ en 1 invoeren). Direct komt het overzicht in beeld met het nieuwe totaal.

Ten tweede: het kan wel eens zo zijn dat je juist niet het totaal maar bijvoorbeeld het gemiddelde wil weten van een kolom. Ga op het totaalbedrag staan (cel E12 in ons voorbeeld) en zie dat er een pijltje naast de kolom
verschijnt. Klik op dit pijltje en de diverse mogelijkheden voor deze telling worden weergegeven.

Ten derde: als je een totaalrij hebt toegevoegd en je wil daarna dataregels in de tabel toevoegen, dan kan ook dat heel eenvoudig. Ga ergens in de tabel staan. Zie dat het kleine driehoekje rechtsonderaan staat, in de cel met het subtotaal. Klik met links op dat kleine driehoekje en sleep het bijvoorb
eeld vier rijen naar beneden en laat vervolgens los. De tabel is nu uitgebreid met vier rijen, de totaalrij is naar beneden verplaatst en na invoer van nieuwe data rekent ‘ie deze direct door!