Maandelijks archief: april 2014

Standaardinstellingen Excel

22 april 2014ExcelInstellingen, Printen, TemplateGijs Verbruggen

Hoe gek het misschien ook mag klinken: ook bij ervaren Excel-gebruikers kunnen een paar aanpassingen aan de standaard-instellingen veel dagelijks plezier met zich meebrengen (of minstens ergernis voorkomen) .

Aantal werkbladen

Misschien heb je je wel eens afgevraagd, waarom Excel standaard bij het starten van een nieuwe werkmap aankomt met 3 werkbladen. Nou, ik ook. En ik heb geen goede reden kunnen bedenken.
Ik weet wel dat ik er meer last van heb gehad dan plezier: hoe vaak heb ik niet tevergeefs in een Excel-sheet van iemand anders zitten zoeken wat er op Blad2 en Blad3 zou staan (om eerlijk te zijn: ik heb ook vaak in mijn ‘eigen werk’ zitten zoeken!).

Weg ermee, dus:

Kies in Excel bovenaan in het menu: Bestand
Dan in de linkerkolom Opties
en binnen deze Opties de keuze Algemeen
bijna onderaan staat: Aantal op te nemen bladen:
verander hier de standaardinstelling 3 in 1

Zoals je ziet, ga ik er van uit, dat we met Office 2010 of hoger werken. In vorige versies vind je deze keuze terug in de menucombinatie Extra/Opties.

Gebruikersnaam

Zeker in een zakelijke omgeving komt het nogal eens voor, dat je een Excel-bestand opent en dan de waarschuwing krijgt, dat LC10025 (willekeurig voorbeeld) dit bestand al geopend heeft. Tja, wie moet je nu vragen om het bestand ‘los te laten’?

Daar gaan we weer:

Kies in Excel bovenaan in het menu: Bestand
Dan in de linkerkolom Opties
en binnen deze Opties de keuze Algemeen
helemaal onderaan staat: Gebruikersnaam:
Vul hier je naam in.
(Zoals je ziet wordt deze naam binnen alle Office-producten gebruikt).

Templates

Waar de vorige 2 instellingen nog wel door diverse mensen gebruikt worden, zie ik het gebruik van Templates heel erg weinig terugkomen.

Misschien wel bekend van Word (en zeker van Powerpoint) worden ze binnen Excel heel weinig gebruikt.

Maar herken je het volgende: je hebt opnieuw een nuttig (natuurlijk!) Excel-overzicht gemaakt en wilt het printen en merkt (meestal te laat) dat deze net niet op 1 A4 past.
Ook wil je natuurlijk ergens je naam (als trotse maker van dit schoons) hebben staan; oja, ook nog even de naam van het bestand ergens, want dat kunnen we later ook niet meer zo goed herinneren.
Ofwel bij iedere nieuwe sheet moeten weer (dezelfde) kop- en voetteksten gemaakt worden, de printerinstellingen verandert etc.
Hier komen templates goed van pas!

Een template is niets anders dan een basis-sheet, waarmee je verder gaat werken. Dit kan een heel simpele zijn met maar 1 printerinstelling aangepast, of een bestand met 10 werkbladen met allerlei standaardteksten en basisinformatie al vastgelegd.
De templates worden met de extensie xltx (in ‘oudere’ versies xlt) weggeschreven. Het enig verschil met een xlsx- (resp xls-) bestand is, dat bij het opslaan van een (aangepaste) template als gewoon bestand het origineel niet wordt overschreven.

Zie voor een uitgebreid voorbeeld: G-Info basis-bestand.

Standaard-templates

Maar nog veel handiger: er zijn 2 templates, die door Excel standaard gebruikt worden.
Map.xltx wordt opgeroepen als er een nieuwe werkmap wordt gemaakt (en dus ook bij het opstarten) en Blad.xltx bij het aanmaken van een nieuw werkblad.

Dus als je daar al je gewenste standaard-instellingen vastlegt (en dat mag zo ingewikkeld en zo uitgebreid zijn als je zelf wilt), dan scheelt dat bij iedere nieuwe Excel-werkmap en -werkblad veel tijd!

Wel een klein probleem: Excel verwacht wel dat deze 2 bestanden in een speciale directory staan en het is van diverse technische zaken afhankelijk waar dat precies is.
Ik heb dan ook een kleine VBA-routine gemaakt, die dit netjes voor je uitzoekt.

Wat moet je doen:

zoek het pad van de opstart-directory op door in het volgende bestand op de button te klikken: Plaats Opstartbestanden
Aangezien sommige tussen-directories verborgen zijn, is de opstart-directory niet makkelijk te bereiken: schrijf het totale pad op of kopieer deze mbv Ctrl-c.
download het bestand Map en bewaar deze ALS TEMPLATE in de hiervoor gevonden directory met de naam Map.xltx
download het bestand Blad en bewaar deze ALS TEMPLATE in de hiervoor gevonden directory met de naam Blad.xltx
sluit Excel en start opnieuw op; tik wat in in cel A1 en ook in E20. Kies printen en bekijk het resultaat.
Pas de 2 xltx-bestanden naar behoefte aan en herhaal de stappen 3 en 4.

Volgende keer zal ik de gebruikte VBA-routine toelichten.

Data (datums)

19 april 2014ExcelDatum, OpmaakGijs Verbruggen

Ik wilde het in dit artikel gaan hebben over een speciaal gebruik van data in Excel. Maar al snel kreeg ik het gevoel, dat het misschien niet duidelijk zou zijn wat ik met dat woord bedoel.

Gevormd (of misvormd) door een klassieke opleiding wil ik daarmee het meervoud van datum aanduiden, maar tegenwoordig wordt het woord data (al dan niet in combinatie met big) meestal in een andere context gebruikt, namelijk om aan te geven dat we het over gegevens hebben.
Even terzijde: hoe vaak wordt het woord data, dat toch echt een meervoudsvorm is, niet in het enkelvoud gebruikt? (“De data wordt door de volgende programma’s gebruikt.”; brrr).

Dus in dit artikel hanteer ik toch maar het meervoud datums.

Datums

Misschien zul je nu denken, dat hier de formule-freak bezig is geweest (trouwens wel een leuke uitdaging om een formule voor C2 te bedenken!).
Nee, in principe staat in alle cellen precies hetzelfde, namelijk het getal 41748:

Tip: wanneer je wilt weten welke formules er gebruikt worden in een spreadsheet, schakel dan over naar het formule-scherm. Dit kan via Formules/Formules weergeven, maar veel gemakkelijker is natuurlijk de toetscombinatie Ctrl-T.

Blijkbaar is een datum voor Excel niets anders dan een getal (om precies te zijn geeft het getal aan hoeveel dagen we ondertussen verwijderd zijn van 1 jan 1900). Daarom is het ook zo makkelijk om te achterhalen hoeveel dagen er tussen twee data, sorry datums, zitten: trek de 2 datums/getallen gewoon van elkaar af! Wil je weten hoeveel dagen er sinds je geboorte zijn verstreken:

Tip: wil je ergens in Excel de datum van vandaag hebben, gebruik dan de functie =VANDAAG(); denk aan de haakjes! Iedere dag zal hier dus iets anders komen; wil je de datum “hard” invoeren, gebruik dan ctrl-; (dus de Ctrl-toets inhouden en op de punt-komma drukken).

Maar weer terug naar het voorbeeld: als in alle cellen dezelfde waarde staat, waarom zien we dan iets heel anders?
Precies, we zien iets anders: het is een kwestie van opmaak!

Datum-opmaak

Om hier wat duidelijkheid te geven over de mogelijkheden gaan we het voorbeeld stap voor stap opbouwen:

in cel B2 tikken we in: 19-4
Wat is Excel toch “slim”, we zien dat er 19-apr komt te staan: hij snapt dat we een datum bedoelen! Als je de inhoud van de cel bekijkt (19-04-2014) zie je dat er ook nog een jaar aan is toegevoegd (namelijk het huidige).
Het is nog vreemder: als je op B2 klikt, zie je dus 19-04-2014 staan, maar dat is niet de echte Excel-waarde, dat is 41748 (zie hiervoor).
we gaan de opmaak van cel B2 veranderen:
Klik op cel B2 en kies de Celeigenschappen (rechtsklikken of Ctrl-1),
Kies als Categorie: Datum.
Kies dan als Type: 14-03-01
Klik OK en je zult in B2 onze notatie zien.
Nog even terug naar de Celeigenschappen (Ctrl-1 dus):
Kies als Categorie: Aangepast.
Bij Type zie je nu: d-mm-jj
Hier zit de crux van dit artikel: met de combinatie van de letters d, m en j kun je heel veel opmaakkwesties oplossen!
In cel C2 komt dezelfde waarde door in te tikken: =B2
Celeigenschappen (Ctrl-1), Categorie: Aangepast
Wis alles bij Type (wat staat er nu bij Voorbeeld?) en tik 1 d in, kijk naar Voorbeeld.
Nog een d (niks veranderd?), nog een d (hé, wat is dat nu?) en nog een d.
We krijgen de smaak te pakken (?): in cel D2 komt =C2
Ctrl-1, Categorie: Aangepast, alles bij Type wissen.
1 m, nog een m, nog een m, nog een m, nog een m, nog een m, nog …..
cel E2 moet nu lukken.

Tip: op deze manier zijn allerlei combinaties in te voeren
(bijvoorbeeld dddd, “de” d”e” mmmm “van het jaar” jjjj).

Voorbeeldbestand

Kijk ook in het voorbeeldbestand en schroom niet om allerlei lettercombinaties uit te proberen.

Let op: we hebben alleen de opmaak van de cellen aangepast! Zoeken (Ctrl-F) naar bijvoorbeeld zaterdag zal in eerste instantie niets opleveren; pas als je in het zoekscherm aangeeft dat hij naar Waarden moet zoeken (niet Formules) zal Excel iets kunnen vinden. Ook sorteren of voorwaardelijke opmaak (om bijvoorbeeld het weekend een andere kleur te geven, zullen niet (direct) werken (in het downloadbestand is een voorbeeld van voorwaardelijke opmaak opgenomen).

Een alternatief voor verticaal zoeken

14 april 2014ExcelIndex, Vergelijken, ZoekenGijs Verbruggen

In de meeste administratieve toepassingen van Excel zie je wel ergens een opzoekfunctie opduiken. Dit meestal in de vorm van verticaal zoeken (ofwel VERT.ZOEKEN), maar ook in de horizontale vorm (HORIZ.ZOEKEN). Maar lang niet altijd is deze functie (zonder aanpassingen aan het spreadsheet) toepasbaar.

Voorbeeld

We hebben een overzicht van bedragen per maand en per regio.

In welke maand en in welke regio hebben we de maximale omzet gehaald?

Met verticaal zoeken lukt dat niet direct, want zoals iedereen wel eens tot zijn of haar frustratie heeft meegemaakt, kun je met deze functie niet “naar links” zoeken.

Dus zit er niets anders op dan het verplaatsen van kolommen of het aanmaken van hulpkolommen.

Alternatief voor VERT.ZOEKEN (en ook voor HORIZ.ZOEKEN)

Als VERT.ZOEKEN dus niet (direct) werkt moeten we het anders aanpakken.

We willen vanuit het vorige voorbeeld (waarin de gegevens doorlopen tot en met de Excel-rij 66) het volgende resultaat bereiken:

Dus vanuit het maximum-bedrag moet Excel ons de betreffende maand en regio laten zien.

Eerst het makkelijkste gedeelte: in cel G4 zoeken we het maximum bedrag op met de formule =MAX(D4:D66)

Maar hoe vinden we nu de betreffende maand en regio?

Hiervoor zijn 2 stappen nodig (die we uiteindelijk in één formule stoppen):

zoek de regel, waarin het maximum zit
zoek in die regel de maand (of regio)

Regel opzoeken met VERGELIJKEN

We willen dus weten op welke regel in het bereik D4:D66 het maximum (de waarde in cel G4) staat.
Dat gaat heel simpel met de formule =VERGELIJKEN(G4; D4:D66; 0).
Denk aan de 0 (nul) op het einde; deze geeft aan, dat de functie een exacte waarde moet zoeken. De reeks getallen hoeft dan ook niet gesorteerd te zijn.

Zet deze formule in H4 en we weten in ieder geval op welke regel in het bereik het maximumbedrag staat.

Betreffende maand (of regio) opzoeken met INDEX

In cel G5 zetten we nu de formule =INDEX(B4:B66;H4) en klaar is Kees (of Jolanda)!

Wil je de regio weten, dan moet het natuurlijk zijn =INDEX(C4:C66;H4).

Deze functie werkt dus net “andersom” als VERGELIJKEN: waar VERGELIJKEN de plaats zoekt waar een opgegeven waarde in een bereik staat, zal INDEX een waarde in een bereik opzoeken, als je de plaats in dat bereik meegeeft.

Resultaat

Als we voorgaande stappen samenvatten in één formule dan ziet dat er als volgt uit:

=INDEX(B4:B66;VERGELIJKEN(G4;D4:D66;0))

of =INDEX(C4:C66;VERGELIJKEN(G4;D4:D66;0))

De formules kunnen nog compacter door ook het berekenen van het maximum hierin op te nemen.

LET OP: de gebruikte bereiken in de functies VERGELIJKEN en INDEX moeten even groot zijn en met elkaar corresponderen: regels (of bij horizontaal zoeken de kolommen) moeten met elkaar overeenkomen. In ons voorbeeld beginnen de bereiken allemaal in rij 4 en lopen door t/m rij 66.

Wanneer je in je spreadsheets met namen werkt dan worden de formules een stuk leesbaarder:

=INDEX(Maanden;VERGELIJKEN(MaxBedrag;Bedragen;0)

of =INDEX(Regios;VERGELIJKEN(MaxBedrag;Bedragen;0)

Een volgende keer zullen we op het gebruik van bereiknamen terugkomen.

Minimum, gemiddelde, mediaan

Wat we net met het maximum van de bedragen hebben gedaan kan natuurlijk ook met andere samenvattingsformules; of toch niet? Wat denk je?

Voorbeeldbestand

Download het Voorbeeldbestand. Hier is ook een variant met bereiknamen terug te vinden.