Exceltip | Op weg naar meer interactie

Het gebruikte voorbeeld

In een Excel-bestand zijn o.a. aanwezig een werkblad met een facturenlijst, zoals in onderstaande afbeelding. Deze gegevens staan in een Excel-tabel met de naam tb_Facturen.

Op een ander werkblad Klanten staan de klantgegevens in de tabel met de naam tb_Klanten.

Er moet een soort klantenkaart komen, waarin je snel een aantal gegevens kunt zien van een bepaalde klant. De opzet is zo.

Korte uitleg van dit werkblad:

• In cel B3 kan via een keuzelijst een klantnaam worden gekozen.
  Dit is gedaan met een gegevensvalidatie in combinatie met de dynamische functie UNIEK.
  
  
• In B5 staat voor de gekozen klant het aantal facturen dat in de tb_Facturen staat.
  Dit is berekend met de functie AANTALLEN.ALS.
  
  
• In B6 staat voor de gekozen klant het totaalbedrag van alle facturen.
  Dit is berekend met de functie SOMMEN.ALS.
  
  
• In B7 staat voor de gekozen klant hoeveel facturen nog open staan.
  Dit is berekend met de functie AANTALLEN.ALS.
  
  
• Vanaf A10 zie een overzicht van de facturen voor de gekozen klant.
  Dit is gemaakt met de dynamische functie FILTER.

Gegevensvalidatie

In B3 is gegevensvalidatie gebruikt om een keuzelijst te maken.
Je selecteert hiervoor B3 en kiest Gegevens > Gegevensvalidatie.
Bij Toestaan kies je voor Lijst, zoals hieronder.

Vervolgens moet je bij Bron opgeven wat er in die lijst moet komen: alle namen dus van de klanten die in de tb_Klanten voorkomen. Maar niet voor alle klanten is er een factuur aanwezig. Die klantenlijst die wel een factuur hebben kun je handmatig ergens neerzetten, maar dan moet je eraan denken dat je die steeds aanvult als er een klant bij komt! Dat gaat gegarandeerd een keer mis!

Dynamische klantenlijst met UNIEK

Daarom ga je ergens (bijvoorbeeld op het werkblad met de klantenkaart) een dynamische lijst maken van de klanten die staan in de tb_Facturen met de functie UNIEK. Het resultaat daarvan kun je vergelijken met Dubbele waarden verwijderen die je ook vindt op de tab Gegevens.

De opbouw is eenvoudig: =UNIEK(cellenbereik met de klantnamen)
Hier dus =UNIEK(tb_Facturen[Klantnaam])

Omdat die facturen in een Excel-tabel staan, breidt die zich automatisch uit als er nieuwe rijen bijkomen! Dat is een van de grote voordelen van een Excel-tabel.
Die keuzelijst is dynamisch: als er in de tb_Facturen een nieuwe klant komt, zal die lijst automatisch worden uitgebreid.
Je herkent dat omdat het resultaat van de functie er zo uitziet. De functie staat in de cel waar de pijl naar wijst en loopt over in de cellen met de blauwe rand eromheen. Dat wordt het dynamische bereik genoemd. Dat groeit dus als er klanten bijkomen in de tb_Facturen.

Wat moet je dan invullen bij de gegevensvalidatie als Bron?
Je moet alleen de begincel van dat dynamische bereik opgeven met daarachter een #. Dat # is voor Excel het teken dat het gaat om een dynamisch bereik!
Als dit dus staat vanaf P3 ziet dat er in de validatie zo uit:

EXTRA: als je de klantenlijst altijd oplopend gesorteerd wilt hebben, dan gebruik je ook de dynamische functie SORTEREN erbij. Het wordt dan in P3:

=SORTEREN(UNIEK(tb_Facturen[Klantnaam]))

Functies in de cellen B5:B7

In onderstaande afbeelding zie je welke functies ik hier heb gebruikt: die staan in de cel erachter.

TIP: in feite had ik voor B5 en B6 ook AANTAL.ALS en SOM.ALS kunnen gebruiken, maar mijn voorkeur gaat altijd uit naar de meervoud-variant.
Waarom? Omdat je bij de enkelvoud-variant maar één voorwaarde kunt opgeven. Kom je er dan later achter dat je het wilt uitbreiden naar meer voorwaarden, dan moet je de hele formule herschrijven, omdat het een andere functienaam is.
In B7 zijn er twee voorwaarden (klantnaam moet hetzelfde zijn als in B3 staat en in de kolom Betaald van de tb_Facturen moet WAAR staan. Daar moet je dus wel AANTALLEN.ALS gebruiken.

Overzicht van de facturen van de gekozen klant

Vanaf cel A10 staan de facturen van de gekozen klant. Dit is gemaakt met de FILTER, ook een dynamische functie.
De opbouw van die functie:

=FILTER(kolommen die je wilt zien; kolomnaam die voorwaarde bevat = voorwaarde; optioneel: foutboodschap)

In dit voorbeeld: =FILTER(tb_Facturen[[Factuurnummer]:[Betaald]];tb_Facturen[Klantnaam]=B3;”geen facturen”)

Uitleg:

• kolommen die je wilt zien zijn de kolommen Facturen tot en met Betaald uit de tb_Facturen
  tb_Facturen[[Factuurnummer]:[Betaald]]
  
  
• kolom die de voorwaarde bevat is de kolom Klantnaam uit de tb_Facturen die gelijk moet zijn (=) aan de klantnaam die staat in B3
  tb_Facturen[Klantnaam]=B3
  
  
• Als een klant niet voorkomt in de lijst, dan wil je niet de standaardfoutmelding #BEREKENEN!, maar een aangepaste melding, hier “geen facturen”.
  In feite is dat hier niet nodig, omdat een klantnaam altijd voorkomt, want die komt uit de tb_Facturen!

Waar je verder nog aan moet denken

Bij het gebruik van een dynamische functie zijn er een paar dingen die je moet bedenken.

• De cellen waarin de berekening overloopt, hebben natuurlijk hun eigen celeigenschappen. In dit voorbeeld zal een factuurdatum standaard dus ook als een getal worden weergegeven.
  
  Je moet die overloopcellen zelf voorzien van de gewenste opmaak. Houd er rekening mee, dat je die opmaak wat verder ‘doortrekt’.
  In de afbeelding van hierboven gaat het om 4 facturen, maar een andere klant heeft er wellicht 10 of meer. Neem dus (ruim) extra cellen mee die je alvast die datumnotatie geeft. Datzelfde geldt voor de andere opmaak, zoals de uitlijning of de euronotatie.
• Als je een dynamische functie gebruikt, moeten de cellen waarin het resultaat komt te staan allemaal leeg zijn. Is dat niet het geval dan krijg je een foutmelding #OVERLOPEN!
  Je wordt geholpen met de blauwe stippellijn: daar moet de berekening in kunnen overlopen. Maak die cel weer leeg en het probleem is opgelost!
  
• Je kunt de dynamische functies niet opnemen in een Excel-tabel!

Saskia Jacobsen is directeur-eigenaar van Toels-PC en helpt bedrijven en vooral hun gebruikers om slimmer met de apps op hun pc te werken..