Excel kennis verspreiden - deel 07: Namen in Excel
- 04/02/2014
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
The (Excel) story continues… part 07… Vandaag bekijken we Namen in Excel. Dat is op zich een relatief uitgebreid onderwerp. Het omvat benoemde bereiken, constanten die je gebruikt in formules, formules op zich, validatielijsten, en meer. Ook de meer geroutineerde Excellers onder ons gaan nog interessante dingen in dit artikel lezen :-)
Om het thema wat te schetsen begin ik met een vraag. "Wat kan je allemaal via Namen doen?" Ik denk dat een opsomming hiervan een goede manier is om dit onderwerp te bespreken. Met concrete voorbeelden zal je het nut van Namen snel inzien.
Verwijzing naar cellen
Een Naam kan een verwijzing bevatten naar een bereik, gaande van 1 cel tot hele kolommen of rijen. De gemakkelijkste manier om de naam aan te maken, is de cellen te selecteren, en dan linksboven in het Naamvak (links van de formulebalk) de naam in te voeren. Bevestig met Enter.
De andere vaak gebruikte methode om een Naam aan te maken, is om ook eerst de selectie te maken, en dan naar de Name Manager te gaan. Je komt terecht in dit scherm via het lint: Formulas > Name Manager. Handige shortcut hiervoor is Ctrl-F3, die gebruik ik steeds en is te verkiezen boven de weg via het lint. Onthoud zeker die F3, die gaat nog terugkomen verderop.
Het dialoogvenster ziet er zo uit (kijk voor nu enkel naar de geselecteerde Naam "mijn_cellen"):
Hierboven beeld ik het scherm van de namen af zoals het er in Excel 2013 uitziet. Al jaren is het scherm ongeveer gelijkaardig van uitzicht, 2013 bracht een paar kleine veranderingen met zich mee.
Voor het toevoegen van een nieuwe Naam: dat wijst zichzelf uit. Klik op "New…" en geef het geselecteerde bereik een goed gekozen naam (zorg dat je meer inspiratie hebt dan mij met mijn "mijn_cellen"). Let erop dat je bepaalde karakters niet mag gebruiken, een liggend streepje is daar bijvoorbeeld uit den boze. Houd er ook rekening mee dat korte namen ook wel eens als kolomletter(s) kunnen voorkomen. Als voorbeeld, de naam "inp" (om inputcellen aan te duiden) mag dan wel geldig zijn, het is ook de 6.464ste kolom op een Excel werkblad sinds Excel 2007 met zijn uitgebreider aantal kolommen (Het zijn er 16384 om precies te zijn, 2^14). Hoewel Excel dit toelaat, neem je om verwarring te vermijden best namen die niet elders kunnen voorkomen.
Het voordeel is dat het bereik mee opschuift als je rijen invoegt/verwijdert boven het benoemde bereik, en/of kolommen links van het benoemde bereik. Zo moet je uiteraard niet zelf de veranderende celreferenties gaan bijhouden. Wil je tóch vaste bereiken hebben, kies dan (o.a.) voor de INDIRECT functie: =INDIRECT("C5:E8")
zal steeds naar het bereik C5:E8 verwijzen. C, D, 5 en 8 zijn hier vastgezet en zullen niet wijzigen door bvb. invoegen of verwijderen van bovenliggende kolommen/rijen. Een bereik kan dus ook bepaald worden door functies zoals dit. Verderop meer hierover, om niet te snel alle geheimen prijs te geven :-)
Wat is nu het nut van zo’n bereik? Stel dat je ergens ten velde staat in een werkboek. Dan kan je naar een ander bereik springen, zo ook "mijn_cellen". Hoe doe je dat? Er zijn 2 veel gebruikte manieren. De eerste is om in het Naamvak de naam te kiezen via het pijltje. Let op: dit zal NIET werken als de naam gevuld wordt door een formule. Is dit laatste het geval, dan kan je met F5 of Ctrl-G de naam van het bereik ingeven of aanklikken, en zo er naartoe gaan. Dit werkt ook als de naam verwijst naar cellen op een ander tabblad en is eigenlijk de 2de manier die ik wilde uitleggen. Zie schermafdruk hieronder die je verkrijgt bij een druk op functietoets F5:
Kijkende naar de kolom "Scope" in de Name Manager, dan merk je dat er namen zijn op het niveau van het bestand (workbook level) en namen op het niveau van een werkblad (worksheet level). Je kan m.a.w. eenzelfde naam gebruiken voor meerdere werkbladen. Denk maar aan de naam "tussenresultaten" die je op elk tabblad zou kunnen gebruiken voor hulpberekeningen. Ook kan eenzelfde naam gebruikt worden op beide niveau’s - ik raad het echter niet aan maar het werkt wel. Maak je een naam aan via het Naamvak, dan wordt de naam standaard een workbook level Naam.
Validatie met keuzelijstjes heb je wellicht al gemaakt in Excel. Dat kan je doen met behulp van Data validatie. De cellen waaruit je kiest, kan je best in een benoemd bereik zetten. Hieronder neem ik de 5 ploegen samen in een benoemd bereik, genaamd "ploegen". De validatielijst ziet er dan zo uit:
Hier kan je ver in gaan, zoals trapsgewijze validatie met de INDIRECT functie: een keuze in een lijstje bepaalt de beschikbare opties in een volgend lijstje, enz. Zie dit artikel bij Excel MVP Debra Dalgleish. Een video is beschikbaar!
Constante waarden
Ik heb al verteld dat namen formules kunnen bevatten. Dan ga je mij ook geloven als ik zeg dat een naam ook een constante waarde (alfanumeriek, textueel of numeriek) kan bevatten: dat is gewoon het speciaal geval van een formule die niet verandert :-) BTW%_hoog en BTW%_laag zijn 2 voorbeelden nu volgend. Beide constanten bevatten een numerieke waarde, 21% en 6% hier.
Je kan dan in heel het bestand in formules de namen BTW%_hoog en BTW%_laag gebruiken, en niet de (hardgecodeerde) 21% en 6%. Uiteraard kan je het hardcoderen (zoals hierboven) ook vermijden door de parameters centraal ergens weg te zetten in een overzichtsblad of in een verborgen tabblad. Maar het voordeel van namen is dat de formules beter leesbaar worden. Verderop zien we dit nog in de praktijk met een voorbeeldje.
Een budgetgetteringsmodel in Excel waarin bepaalde parameters zitten (zoals huidig jaar, aantal werknemers, inflatie, enz.) past zich eenvoudig aan bij veranderende parameters: pas de constante aan in de Name Manager en je bent klaar. Gebruik in alle formules dan ook die namen/parameters.
Een voorbeeld van een bestand met veel parameters / constanten en formules in Namen, is dit eerdere artikel van mij. Ik maak daar gebruik van prefixen en suffixen om de parameters onder te brengen in "categorieën" om het ons gemakkelijker te maken in de formules zelf. Er zijn hier namen voor ondermeer constanten (CONST_), namen voor inputcellen (INPUT_) en nog veel meer. Die zie je dan terugkomen in de berekeningen:
Merk de constante waarden en ook de inputcellen. Tevens, kijk evens in de formulebalk en stel vast dat "Totaal terugbetaald" in cel C31 gelijk is aan 12 maandelijkse aflossingen gedurende de looptijd (INPUT_looptijd), van een bedrag dat berekend werd als CALC_aflossing. Ik bereken m.a.w. de maandelijkse aflossing, geef daar een naam aan, en kan dan verder werken met die naam. Het levert duidelijkere formules op (naar mijn mening toch). Want stel je eens voor dat je honderden van zulke formules moet auditen en allemaal hebben ze celverwijzingen als E13, P76, AA4 en wat weet ik nog allemaal.
Maar goed. Ik haalde het al aan: een naam kan ook een formule bevatten. Vaak gebruiken we dit voor dynamische bereiken met een VERSCHUIVING functie (OFFSET in het Engels). Dit geeft een bereik als resultaat, uitgaande van een startbereik en daar dan enkele verschuivingen op toegepast. Kijk hieronder naar de naam "mijn_cellen_met_formule", dat is opnieuw B2:C5 maar dan met een formule zodat het bereik dynamisch wordt:
-
Het bereik zal mee inkrimpen en uitbreiden indien nodig. De VERSCHUIVING functie heeft 5 argumenten (je hoeft ze niet alle 5 altijd te gebruiken):
- startcel/bereik
- aantal rijen naar onder (+) of naar boven (-) verschuiven ten opzichte van het startbereik
- aantal kolommen naar rechts (+) of naar links (-) verschuiven ten opzichte van het startbereik
- aantal rijen dat het nieuwe bereik zal tellen
- aantal kolommen dat het nieuwe bereik zal tellen
Elk van deze argumenten kan op zich opnieuw een functie zijn. Vooral argumenten 4 en 5 worden regelmatig als een functie bepaald: argument 4 wordt dan het aantal gevulde cellen in een kolom, en argument 5 wordt dan het aantal gevulde cellen in een rij. Zo komen we tot een dynamisch bereik, wat kan dienen als de bron voor ondermeer een draaitabel. De draaitabel zal steeds de juiste rijen en kolommen opnemen aangezien zijn brondata dynamisch bepaald wordt.
Overigens, de aandachtige lezer van mijn Excel lessen heeft al opgemerkt dat een Excel Tabel handiger is voor dynamische bereiken. Die houdt sowieso steeds het juiste aantal rijen en kolommen bij.
Namen gebruik je in werkbladcellen, in andere namen, in brondata voor grafieken of draaitabellen, in VBA-code, zelfs als tekst op afbeeldingen, kortom, zo een beetje overal in Excel :-) Maar hoe voeg je een naam in in een cel? Als de naam BTW_perc_hoog bestaat, hoe doe je dan cel A1 maal BTW_perc_hoog? Simpel: =A1 * BTW_perc_hoog. Je kan die Naam op meerdere manieren in een formule zetten:
- typ de naam gewoon :-) Excel zal helpen door mee te lopen terwijl je typt (en stoppen met helpen bij een typfout)
- terwijl je in de formule bezig bent, druk F3 en kies uit het lijstje de juiste naam. (Dit lukt echter niet voor alle types namen.) Naast functietoets F4 is bij het invoeren van formules ook F3 een onmisbare hulp.
- kies op het lint (ribbon) voor Formulas > Defined Names > Use in Formula. Klik de naam aan en hij wordt in de formule in de huidige cel ingevoegd.
Je kan namen hergebruiken. Als je de cellen C2:D5 in een bereik wil zetten uitgaande van bereik mijn_cellen (dus 1 kolom naar rechts opschuiven), dan kan dat zo met een formule in een (nieuwe) Naam:
Deze formule behoeft weinig uitleg, de OFFSET / VERSCHUIVING functie kennen we nu ondertussen ;-) Of toch: het startbereik is "mijn_cellen", een ander bereik. Vervolgens geef ik geen waarde mee voor de verschuiving langs de rijen (ik laat het leeg of zet 0) en tot slot is er nog een verschuiving van 1 kolom. De argumenten voor aantal rijen en aantal kolommen laat ik achterwege, dan blijft het zoals het was. Resultaat zoals te zien op de afbeelding: C2:D5. Concreet voorbeeld: je kan 12 kolommen Actuals hebben in een werkblad, die je in een benoemd bereik zet. Vervolgens met een OFFSET formule 12 kolommen naar rechts heb je misschien Budget gegevens staan.
Bereiken waarvan de broncellen (deels) verwijderd worden, geven aanleiding tot verwijzingsfouten: #VERW! (in het Engels: #REF!) Corrigeer die fouten dan.
Formules met relatieve celverwijzingen
Formules kunnen ook relatief ingezet worden. Stel dat je de stelling van Pythagoras wil toepassen. Je hebt de lengte van 2 zijden van een rechthoekige driehoek, en wil de lengte van de andere zijde berekenen. Je zet dan de lengtes uit in 2 cellen, naast mekaar, en in de derde cel ernaast wil je het resultaat zien. De formule voor de berekening is uiteraard "A²+B² = C²" (dat weet iedereen nog). Hieruit kan je C afleiden en als formule in een Naam zetten.
-
Volg deze stappen EXACT:
- Zet de cursor in de cel rechts van de 2de lengte (belangrijk voor later)
- Druk Ctrl-F3
- Voeg een nieuwe naam toe, bvb. lengte_Pythagoras
- Als formule neem je: =SQRT(B14^2+C14^2) als de cursor in cel D14 staat. Neem de cel 2 kolommen naar links, zelfde rij, en daarna de cel 1 kolom naar links, ook zelfde rij.
- Laat de dollartekens in de formule weg ! Merk op: er is nog niets gebeurd in het werkblad zelf.
- Zet nu de naam in de "derde cel" (meest rechtse): typ "isgelijkaan", druk F3, kies lengte_Pythagoras uit het lijstje en druk Enter.
- Het resultaat zou nu in die derde cel moeten staan. Met lengtes 3 en 4 krijg je inderdaad 3*3 + 4*4 = 25 en 25 = 5*5
Het "straffe" nu is dat je, door de relatieve celverwijzingen in de formule, EENDER waar in het werkboek die 3 cellen kan zetten. Telkens zet je een getal, daarnaast nog een getal, en in de derde cel zet je =lengte_Pythagoras Niets anders. De formule berekent zich steeds op basis van de 2 cellen links van de actieve cel, waar dat ook moge wezen.
Casus: omrekening van valuta
Oké, Pythagoras zat misschien ver weg en zegt ons niet zoveel meer, we zullen een voorbeeld nemen van het omrekenen tegen wisselkoersen. Stel dat je een getal in USD hebt en het moet omgezet worden naar EUR. Dan heb je natuurlijk de wisselkoers nodig. Zet die in een cel en benoem die cel via het Naamvak, of maak de naam aan in de Name Manager en bewaar daar de waarde van de laatst bekende wisselkoers. Onthoud de naam die je aan de wisselkoers gegeven hebt.
Stap 2 is het USD bedrag in een cel te typen, en dan de cursor in de cel rechts ernaast te positioneren.
3de stap is naar de Name Manager gaan (Ctrl-F3 alweer) en daar een naam maken met een formule voor het resultaat in EUR: relatieve celverwijzingen gaan we gebruiken. Voor de formule, druk isgelijkaan, dan typ je het adres van de cel LINKS van de actieve cel in het werkblad, typ * van vermenigvuldiging, druk F3 om de wisselkoers op te halen uit de naam en bevestig met Enter. Onthoud weer de naam die je gebruikt hebt voor de formule. De formule is workbook level.
Laatste stap is het toepassen van de gemaakte formule. Eender waar in het bestand kan je nu gewoon een getal typen, en in de cel RECHTS ernaast zet je: =naamvandeformule (ik weet niet wat jij genomen hebt als namm…). Druk Enter en de omrekening in EUR is gebeurd!
Zoals eerder, je kan nu overal in het werkboek de formule =Amount_in_EUR zetten! Als het resultaat 0 is, kijk dan na of de cel links juist ingevuld is. En of de wisselkoers correct is. Kom je er dan nog niet uit, gebruik dan de Formula Auditor die we een tijdje terug bekeken hebben.
Waar ik zei om LINKS of RECHTS te werken, dat kan je natuurlijk aanpassen aan de noden. Zolang het relatief gezien maar klopt, kan alles. Bvb. 5 cellen naar rechts en 10 rijen naar onder, zou relatief gezien ook kunnen. Pas wel op dat het blijft kloppen :-)
Bij het ingeven van formules met relatieve celverwijzingen moet je de $ tekens verwijderen, anders heb je absolute celverwijzingen of een mix van relatief en absoluut. Je zal daarbij misschien vaststellen dat de pijltjestoetsen niet werken als je de formule wil aanpassen. Dit is by design in Excel! TIP: als je F2 drukt kan je de pijltjestoetsen weer wel gebruiken :-)
Miscellaneous
We komen zo stilaan bij het einde van dit artikel uit, het is al een lang artikel geworden! Je kan een hyperlink leggen naar een benoemd bereik. Kijk bij Insert > Hyperlink (Ctrl-K) naar "Place in This Document". Handig voor templates, dashboards enz. De naam kan verwijzen naar een vaste locatie, of berekend worden met een formule. Wees maar creatief ! :-)
Een zeer handige functionaliteit voor het en masse aanmaken van namen op basis van 1 rij en 1 kolom, is de optie Formulas > "Create form Selection":
HET RESULTAAT IS:
Je geeft met de 2 vinkjes aan dat je een naam wil maken voor elke rij en voor elke kolom in de tabel. Het resultaat is aan de rechterkant te zien. Hier nog een weetje in Excel…: een spatie is de operator voor een intersectie. Als je dus het getal op het kruispunt van "Lierse" en "Seizoen 4" wil uitvragen, neem dan een formule zoals hieronder te zien: benoemd bereik Lierse (een rij) spatie benoemd bereik Seizoen 4 (een kolom) levert 1 cel op, de 2 die we zoeken :-) Allemaal dankzij benoemde bereiken. Eronder staan 3 alternatieve formules waar niemand echt vrolijk van wordt (maar wel werken uiteraard):
Met VBA-code kan je uiteraard ook Namen toevoegen, aanpassen, verwijderen, updaten, uitvragen, en dies meer. Namen worden er wel vaker gebruikt voor het (tijdelijk) bijhouden van informatie, zoals een bestandsnaam of -locatie. Additioneel ten opzichte van de gebruikersinterface, kan VBA ook een Naam verbergen. Enkel VBA kan die naam dan ook weer zichtbaar maken.
We hebben vandaag de ingebouwde Name Manager besproken tot nu toe, maar die kent zo zijn beperkingen. Een échte Name Manager, en gratis, kan je bij Excel MVP Jan Karel Pieterse downloaden: . Dit is een aanrader voor wie vaak met Namen werkt in Excel (en waarschijnlijk vertel ik dan ook niets nieuws met deze info…)
1 belangrijk punt moet nog vermeld worden waar je voor moet oppassen: als je Voorwaardelijke opmaak gebruikt en dit toepast op een benoemd bereik, dan werkt dit niet als dusdanig. Ik verklaar me nader, de Voorwaardelijke opmaak wordt toegepast, maar op bereiken met harde celverwijzingen. De naam en het dynamische karakter ervan zijn verdwenen als je het dialoogvenster van de Voorwaardelijke opmaak sluit en opnieuw opent. Wees je daar dus van bewust, Voorwaardelijke opmaak met namen zal niet werken.
Het laatste dat ik nog zou willen vermelden, zonder er dieper op in te gaan, is dat je grafieken kan maken zonder dat de data of formules in cellen staan! Wie hier het fijne van wil weten, vraagt het mij eens bij gelegenheid, of bekijkt deze pagina.
Wat een artikel is het geworden! Maar ik ben er zeker van dat er bij de meesten van jullie een aantal ogen gaan open gegaan zijn :-) (En bij anderen een aantal ogen dicht en in slaap gevallen :-) )
