Slim­me Ex­cel-trucs

Ge­ge­vens ef­fi­ci­ënt in­voe­ren in spread­sheets

C’t Magazine - - Inhoud - Rai­ner Sch­wabe

Het in­voe­ren van ge­ge­vens in Ex­cels­heets komt vaak neer op dom­weg in­klop­pen: soms moet je in meer­de­re werk­bla­den steeds de­zelf­de cij­fers, for­mu­les of op­maak in­voe­ren. Dit soort saaie – en an­de­re – rou­ti­ne­klus­jes kun je veel snel­ler uit­voe­ren.

Ex­cel heeft veel func­ties waar­mee je ver­ve­len­de taak­jes mak­ke­lij­ker kunt uit­voe­ren. Zo kun je da­ta di­rect in meer­de­re werk­bla­den in­voe­ren. Ook an­de­re za­ken zijn al bij het in­voe­ren van ge­ge­vens te kla­ren. Zo kun je bin­nen tekst re­ke­nen en af­han­ke­lijk van het re­sul­taat het en­kel­voud of meer­voud la­ten weer­ge­ven – bij­voor­beeld bij de om­schrij­ving van het aan­tal goe­de­ren. Bo­ven­dien kun je mak­ke­lijk voor­ko­men dat be­lang­rij­ke ge­ge­vens tij­dens de in­voer wor­den over­schre­ven.

In­voer voor al­len

Vaak is het echt niet no­dig om tel­kens de­zelf­de ge­ge­vens, for­mu­les en func­ties apart in meer­de­re werk­bla­den in te voe­ren. Met de 3D-se­lec­tie zijn meer­de­re werk­bla­den tot een groep sa­men te voe­gen. Je se­lec­teert af­zon­der­lij­ke werk­bla­den door de Ctrl-toets en de lin­ker­muis­knop in te druk­ken. Druk je daar­en­te­gen de Shift-toets in, dan kun je werk­bla­den tot een be­reik 'van...tot' se­lec­te­ren. Klik je op een werk­blad dat niet tot de se­lec­tie hoort (of met in­ge­druk­te Shift op het eer­ste blad), dan wordt de groep weer op­ge­he­ven.

Met de 3D-func­tie wor­den be­re­ke­nin­gen mak­ke­lij­ker. Als de waar­den in cel­len op de af­zon­der­lij­ke werk­bla­den iden­tiek zijn, kun­nen ze in een to­taal over­zicht mak­ke­lijk wor­den be­re­kend. Voor­beeld: je houdt in een rit­ten­boek op 12 werk­bla­den de rij­kos­ten voor ja­nu­a­ri t/m de­cem­ber bij. De bla­den heb­ben de naam van de be­tref­fen­de maand. Op het eer­ste werk­blad staat het to­ta­le over­zicht van het jaar voor de af­zon­der­lij­ke maan­den. Dan komt de 3D-func­tie aan zet. Se­lec­teer de cel waar­in de for­mu­le voor de 3D moet ko­men en voer de vol­gen­de for­mu­le in: =SOM('Ja­nu­a­ri:De­cem­ber'!B4). Van elk werk­blad wordt dan tel­kens het be­drag uit cel B4 op­ge­teld.

Met de toets­com­bi­na­tie Shift+F11 maak je een nieuw werk­blad aan. Om dit au­to­ma­tisch in de 3D-be­re­ke­ning op te ne­men, hoef je het maar voor het laat­ste werk­blad in te voe­gen. Ex­cel telt de af­zon­der­lij­ke waar­den au­to­ma­tisch op bij het to­taal.

Een of meer?

Cel­len kun­nen ver­schil­len­de op­maak heb­ben. Druk je bij­voor­beeld Ctrl+% (Ctrl+Shift+5) in, dan stel je di­rect de pro­cent­op­maak in. Met Ctrl+$ krijg je de va­lu­ta­opmaak en met Ctrl+! het schei­dings­te­ken voor dui­zend­tal­len met twee cij­fers ach­ter de kom­ma.

Daar­naast kun je een aan­ge­pas­te ge­ta­lop­maak de­fi­ni­ë­ren via het dia­loog­ven­ster 'Cel­len op­ma­ken' (Ctrl+1). Kies de ca­te­go­rie Aan­ge­past en typ tekst die je aan ge­tal­len wilt toe­voe­gen tus­sen aan­ha­lings­te­kens, bij­voor­beeld #.### "stuk".

Ge­bruik een 0 in plaats van een # voor voor­loop- of ex­tra nul­len. In het voor­beeld­vak zie je het ef­fect van de no­ta­tie op de hui­di­ge cel.

Als je qua ge­tal­len on­der­scheid moet ma­ken tus­sen en­kel­voud of meer­voud, bij­voor­beeld 1 dag maar 2 da­gen, kun je in de aan­ge­pas­te op­maak een voor­waar­de op­ne­men. Bij een nul­waar­de kun je een le­ge cel zon­der tekst la­ten weer­ge­ven. Se­lec­teer hier­voor de ca­te­go­rie Aan­ge­past en typ in het veld Ty­pe voor de op­maak [=1] # "dag"; [=0] ""; # "da­gen". Ex­cel voegt dan aan de waar­de 1 de tekst “dag” toe, toont voor 0 een le­ge cel (al­leen “”) en voegt aan al­le an­de­re waar­den de tekst “da­gen” toe.

Re­ke­nen met tekst

For­mu­les en tekst zijn met één han­de­ling op al­ler­lei mo­ge­lij­ke ma­nie­ren te com­bi­ne­ren. Zo kan Ex­cel ook bin­nen tekst re­ke­nen en kun je bij be­re­ke­nin­gen bij­voor­beeld een zin toe­voe­gen als: "Dit be­drag is in­clu­sief xx eu­ro BTW."

Zet de ge­he­le for­mu­le­ring van tekst en for­mu­le tus­sen aan­ha­lings­te­kens: ="Het be­drag van "&(B2)&" eu­ro is in­clu­sief "&(B2/121*21)&" eu­ro BTW.". Al­les wat tel­kens tus­sen twee &-te­kens in­staat, wordt door Ex­cel als een for­mu­le be­schouwd die wordt be­re­kend; de rest ziet het pro­gram­ma aan voor tekst.

Je kunt de om­schrij­vin­gen nog uit­brei­den door ge­ge­vens mee te ne­men die re­gel­ma­tig wij­zi­gen. Zo kun je de naam van een ver­ko­per of ser­veer­ster in­voe­gen en ook de da­tum. Als in cel B4 bij­voor­beeld de naam van de ser­veer­ster staat en in B5 de da­tum (die met de func­tie =VAN­DAAG() da­ge­lijks wordt bij­ge­werkt), zou de for­mu­le als volgt kun­nen lui­den ="U werd op "&TEKST(B5;"DD-MM-JJJJ")&" be­diend door "&B4&"". Met de func­tie TEKST() neemt Ex­cel de da­tum niet als on­der­lig­gen­de ge­tal­waar­de over, maar wordt het di­rect in de aan­ge­ge­ven da­tum­op­maak weer­ge­ge­ven. Op de­zelf­de ma­nier is bin­nen de tekst­func­tie ook voor een an­de­re op­maak te kie­zen, bij­voor­beeld voor die van va­lu­ta: "&TEKST(B2*0,98;" € #.## 0,00")

Meer dan al­leen plak­ken

Je kunt ook recht­streeks be­re­ke­nin­gen toe­pas­sen op een cel­len­be­reik met de waar­den uit een an­de­re cel­len­be­reik. Al­le soor­ten ba­sis­be­re­ke­nin­gen zijn daar­voor be­schik­baar, zo­als op­tel­len, af­trek­ken, de­len en ver­me­nig­vul­di­gen. Ac­ti­veer hier­voor eerst de cel(len) waar­van de nu­me­rie­ke waar­de(n) moe­ten wor­den ge­bruikt bij de be­re­ke­ning. Ko­pi­eer de­ze met Ctrl+C naar het klem­bord of knip ze met Ctrl-X. Se­lec­teer ver­vol­gens het cel­len­be­reik waar­op je een be­re­ke­ning wilt toe­pas­sen.

Met de toet­sen­com­bi­na­tie Ctrl+Alt+V ac­ti­veer je dan de wi­zard 'Plak­ken spe­ci­aal'. Geef on­der Be­wer­king de ge­wens­te be­re­ke­ning aan. Zo­dra je op OK klikt, wordt die be­re­ke­ning met de ge­ko­pi­eer­de waar­den toe­ge­past op het cel­len­be­reik. De in­houd van de cel­len wordt daar­bij over­schre­ven.

Als je niets op­geeft, wordt de be­re­ke­ning ook toe­ge­past met le­ge cel­len uit de bron, wat bij­voor­beeld bij de­len tot een fout­mel­ding leidt (“#DEEL/0!”). Je kunt dat voor­ko­men door in de wi­zard 'Plak­ken spe­ci­aal' een vin­kje te zet­ten bij 'Le­ge cel­len over­slaan'.

Me­nig Ex­cel-ge­brui­ker zal zich sto­ren aan het knop­je met pla­k­op­ties dat on­der meer ver­schijnt als je ge­ge­vens hebt ge­ko­pi­eerd. Ge­luk­kig kun je dat ook uit­scha­ke­len. Ga hier­voor naar de op­ties voor Ex­cel en se­lec­teer links Ge­a­van­ceerd. Zet ver­vol­gens on­der het ge­deel­te 'Knip­pen, ko­pi­ë­ren en plak­ken' de op­tie 'Knop voor pla­k­op­ties weer­ge­ven wan­neer in­houd wordt ge­plakt' uit.

Le­ge cel­len voor­ko­men

Bij lan­ge lijs­ten kan het be­lang­rijk zijn dat je ge­ge­vens door­lo­pend in­voert – of­te­wel zon­der le­ge rij­en er­tus­sen. Om dat di­rect bij het in­voe­ren te la­ten ge­beu­ren, staat op het tab­blad Ge­ge­vens de func­tie Ge­ge­vens­va­li­da­tie. Se­lec­teer de ko­lom­men waar­van je de ge­ge­vens wilt chec­ken en ac­ti­veer de func­tie. Se­lec­teer op het tab­blad In­stel­lin­gen het cri­te­ri­um Aan­ge­past en typ als for­mu­le bij­voor­beeld =AAN­TAL. LE­GE.CEL­LEN(A$1:A1)=0 – daar­mee wordt ko­lom A com­pleet ge­con­tro­leerd. Om daar­bij een mel­ding te la­ten weer­ge­ven, moet je naar het tab­blad Fout­mel­ding.

Als er al le­ge cel­len zijn, kun je ze met 'Ga naar' (Ctrl+G) se­lec­te­ren. Klik in de wi­zard op de knop Spe­ci­aal en se­lec­teer daar­na 'Le­ge waar­den'. Sluit je de wi­zard ver­vol­gens met OK, dan zijn al­le le­ge cel­len ge­se­lec­teerd. Na Ctrl+– ver­schijnt een ven­ster en met een klik op OK zijn al­le le­ge cel­len weg.

Naast le­ge rij­en kun je met Ge­ge­vens­va­li­da­tie ook voor­ko­men dat er le­ge te­kens wor­den in­ge­voerd. Voer hier­voor on­der Aan­ge­past in het veld For­mu­le=ISFOUT2(Zoe­ken(" ";A1)) in (A1 dient hier als voor­beeld). Voor het in­voe­ren van le­ge te­kens wordt niet al­leen ge­waar­schuwd, het is ook niet toe­ge­staan. Wil je daar­en­te­gen dat er al­leen cij­fers mo­gen wor­den in­ge­voerd, dan moet je =NIET(ISTEKST(A1)) ge­brui­ken. Met bei­de ge­com­bi­neerd wordt het dan:

=EN(NIET(ISTEKST(A1);ISFOUT2—

(Zoe­ken(" ";A1)))

Daar­mee kun je de in­voer van tekst uit­slui­ten en al­leen die van cij­fers toe­staan. Om te voor­ko­men dat en­tries dub­bel wor­den in­ge­voerd, moet je in het veld For­mu­le SOM.ALS ($A$1:$A$30;A1)<=1 zet­ten.

Week­ends uit­slui­ten

Als al­leen de werk­da­gen voor je be­re­ke­nin­gen in aan­mer­king ko­men en je dus niet wilt dat de da­ta voor week­ends wor­den mee­ge­no­men, biedt Ge­ge­vens­va­li­da­tie de vol­gen­de func­tie: =EN(WEEKDAG(A1;2 )7;WEEKDAG(A1;2)6). Om­dat de werk­week op maan­dag be­gint, moet bij de func­tie WEEKDAG als pa­ra­me­ter de waar­de 2 wor­den aan­ge­ge­ven.

Je kunt zelfs re­ke­ning hou­den met feest­da­gen die geen vas­te da­tum heb­ben. Om dat soort feest­da­gen te be­pa­len is Paas­zon­dag bij elk jaar het uit­gangs­punt. Die dag wordt on­der meer be­paald met de for­mu­le =AFRONDEN.BE­NE­DEN(DA­TUM(A1;5 ;DAG(MI­NUUT(A1/38)/2+56));7)-34.

Van­af Paas­zon­dag wor­den de an­de­re feest­da­gen be­re­kend die elk jaar op een an­de­re da­tum val­len. Zo moet je voor Paas­maan­dag 1 er­bij op­tel­len (-33 aan het eind) en voor Goe­de Vrij­dag 2 da­gen af­trek­ken (-36). Op de­zelf­de ma­nier kun je ook Car­na­val (50 af­trek­ken), He­mel­vaart (39 da­gen op­tel­len) en bei­de Pink­ster­da­gen (49 resp. 50 da­gen op­tel­len) uit­re­ke­nen.

Meer on­ge­daan ma­ken

Ie­der­een maakt fou­ten. Als je tij­dens het werk aan een spread­sheet dus een be-

paal­de stap ach­ter­af lie­ver niet had wil­len doen, kun je die on­ge­daan ma­ken. In ou­de­re ver­sies van Ex­cel kon je hoog­uit 16 stap­pen te­rug, maar van­af Ex­cel 2007 is dat stan­daard 100 keer. Mocht je daar nog niet ge­noeg aan heb­ben, dan kun je ook dat aan­tal ver­ho­gen. Je past de waar­de aan via de Re­gis­ter-edi­tor (maak van te vo­ren dus eerst een back-up van het Re­gis­ter). Open daar­na on­der Win­dows het ven­ster Uit­voe­ren (met Win­dows+R) en start met het com­man­do re­gedit de Re­gis­ter-edi­tor.

Ga nu naar 'HKEY_CURRENT_ USER\ Soft­wa­re\Mi­cro­soft\Of­fi­ce\ x.0\Ex­cel\ Op­ti­ons'. Daar­bij staat x.0 voor de be­tref­fen­de Ex­cel-ver­sie. Zo staat daar bij Ex­cel 2007 12.0, bij Ex­cel 2010 '14.0, bij Ex­cel 2013 15.0 en bij Ex­cel 2016 16.0. Voer daar­na via het me­nu 'Be­wer­ken / Nieuw' een 'DWORD-waar­de' in en typ als waar­de­naam Un­doHis­to­ry in. Open het in­voer­veld met een dub­bel­klik en stel bij Grond­tal de op­tie De­ci­maal in.

Typ dan on­der het veld Waar­de­ge­ge­vens het aan­tal stap­pen in dat je wilt kun­nen te­rug­gaan. Met 0 als waar­de wordt un­do in Ex­cel niet meer mo­ge­lijk. Ver­laat je de Re­gis­ter-edi­tor, dan wordt de nieu­we in­stel­ling van kracht als je Ex­cel op­nieuw hebt op­ge­start.

Sfeer­ma­ker

Ex­cel kan de in­voer van ge­ge­vens ook min­der saai ma­ken. Je kunt tij­dens het in­klop­pen van ge­ge­vens ook smi­leys met een blij, ver­drie­tig of neu­traal ge­zicht­je toe­voe­gen. Daar­mee zie je in een oog­op­slag of cij­fers bo­ven of on­der ver­wach­ting lig­gen of juist naar ver­wach­ting. Ge­bruik hier­voor een for­mu­le zo­als =ALS(A1>A2;TE KEN(74);ALS(A1=A2;TE­KEN(75);Te­ken(76))) en ko­pi­eer die naar de ge­wens­te cel­len.

Maak die cel­len ten­slot­te op met het let­ter­ty­pe Wingdings. Al naar­ge­lang de uit­komst van de for­mu­le zie je dan in de cel met de for­mu­le een smi­ley ver­schij­nen. Met de knop 'Voor­waar­de­lij­ke op­maak' (tab­blad Start) kun je het met kleu­ro­ver­gan­gen en -scha­len nog bon­ter ma­ken.

Wil je we­ten wat je in de toe­komst te wach­ten staat, dan put je mis­schien ver­trou­wen uit de ho­ro­scoop. Ex­cel kan die niet voor je ma­ken, maar het Of­fi­ce­pro­gram­ma kan wel een ge­boor­te­da­tum snel aan een ster­ren­beeld toe­wij­zen. Aan elk ster­ren­beeld is in Ex­cel een ge­tal toe­ge­we­zen. De­ze ge­tal­len heb­ben als ba­sis het jaar 1900 (van­af dat jaar telt Ex­cel de da­gen). De­fi­ni­eer voor het cel­len­be­reik links in de af­beel­ding on­der­aan de naam Ster­ren­te­ken en ge­bruik in G4 als for­mu­le =IN­DEX(Ster­ren­te­ken; VER­GE­LIJ­KEN(DA­TUM(;MAAND(F4); DAG(F4)) ;IN­DEX(Ster­ren­te­ken;;2));1).

Voor el­ke ge­boor­te­da­tum of (met weg­la­ten van het jaar en de juis­te da­tum­op­maak) ver­jaar­dags­da­tum die je dan in F4 in­vult, ver­schijnt ver­vol­gens als tekst het bij­be­ho­ren­de ster­ren­beeld. Ko­pi­eer de for­mu­le naar de an­de­re cel­len. In het let­ter­ty­pe Wingdings vind je de bij­be­ho­ren­de pic­to­gram­men voor het ster­ren­beeld – in het ge­val je dat leuk mocht vin­den. (mvdm)

Met een aan­ge­pas­te op­maak kun­nen waar­den met een en­kel- of meer­vou­dig woord wor­den aan­ge­vuld, han­dig bij een of meer­de­re da­gen.

Voor be­re­ke­nin­gen in tekst moet je de zin com­pleet tus­sen aan­ha­lings­te­kens zet­ten. Voor ver­mel­ding van de ver­ko­per of da­tum kun je de in­for­ma­tie uit los­se cel­len ha­len.

Met de Ge­ge­vens­va­li­da­tie kun je ook le­ge rij­en ver­wij­de­ren; voer hier­voor on­der 'Aan­ge­past' de bij­be­ho­ren­de for­mu­le in.

Om be­re­ke­nin­gen di­rect bij het in­voe­gen uit te voe­ren, ko­pi­eer je eerst de waar­den naar het klem­bord, se­lec­teer je ver­vol­gens het doel­be­reik en be­paal je de be­wer­king na Ctrl+Alt+V te heb­ben in­ge­drukt.

Elk ster­ren­beeld is in Ex­cel aan een ge­tal ge­kop­peld. Om for­mu­les mak­ke­lij­ker in te voe­ren, heeft het ge­he­le cel­be­reik in dit voor­beeld de naam Ster­ren­te­ken.

In de Re­gis­ter-edi­tor kun je het aan­tal stap­pen voor on­ge­daan ma­ken aan­pas­sen. Als je op OK klikt, wordt de nieu­we waar­de na een her­start van Ex­cel van kracht.

Newspapers in Dutch

Newspapers from Netherlands

© PressReader. All rights reserved.