zondag, april 20, 2014

Excel, datumformaat vs taalinstelling (yyyy, jjjj)

In Excel is het mogelijk om bijvoorbeeld cellen die datums bevatten, met behulp van de formule =TEKST(<datum>;<datumformaat>)in het gewenste formaat op het scherm te tonen. Bij het opgeven van het datumformaat kun je tegen problemen aanlopen als deze afwijken van de taalinstellingen (internationale instellingen van Windows). Dit probleem is echter goed te omzeilen met de VBA-functie Application.International()
Als voorbeeld vullen we cel A1 met de datum 31-12-2014. In cel A2 vullen de formule =TEKST($A$1;"yyyy-mm-dd"). Het formaat staat tussen dubbele quotes "" en zal op een computer met Nederlandse taalinstellingen niet werken. Op een computer met Nederlandse taalinstellingen zal de formule in A2 het resultaat yyyy-12-31 teruggeven. Met behulp van User Defined Functions (UDF), kan uitkomst worden geboden. Definieer in VBA (Module) de volgende functies:

Function Jaarformaat() As String
' Deze procedure is in het leven geroepen om de jaaraanduiding in het datumformaat
' afhankelijk te laten zijn van de internationale instellingen
' Hierbij wordt gebruik gemaakt van Application.International kenmerken
  Jaarformaat = String(4, CStr(Application.International(19)))
End Function
Function Uurformaat() As String
' Deze procedure is in het leven geroepen om de uuraanduiding in het datumformaat
' afhankelijk te laten zijn van de internationale instellingen
' Hierbij wordt gebruik gemaakt van Application.International kenmerken
  Uurformaat = String(2, CStr(Application.International(22)))
End Function

 Met behulp van bovenstaande functies is het mogelijk om de verschillen in formaataanduiding in uren ("hh" - "uu") en jaren ("yyyy" - "jjjj" ) op te vangen. De betreffende functionaliteit is natuurlijk ook voor andere taalinstellingen te gebruiken. De gedefinieerde UDF Jaarformaat (en Uurformaat) kan in de =TEKST formule worden gebruikt. (=TEKST(A1;Jaarformaat()&"-mm-dd")). 
Op deze manier is het bovenstaande probleem met betrekking tot de verschillende afkortingen in datumformaat per taal op te lossen.


Heb je nog vragen, neem dan gerust contact met ons op.

woensdag, februari 26, 2014

Excel, tonen celbereik waarvoor "Namen" zijn gedefinieerd.

Het is mogelijk om voor een bereik een "Naam" op te geven.

 Zie "Formules" -> "Namen beheren".

De gedefinieerde naam kun je vervolgens in formules gebruiken.
Stel er staan omzetgegevens in bereik $A$1:$A$10 en je definieert vervolgens een naam voor dit bereik en noemt dit bereik "Omzet", dan kun je in een formule de totale omzet berekenen door de formule:
=SOM(Omzet) 

Vaak weet je niet meer voor welke bereiken je namen hebt gedefinieerd.
Nu, hier kun je met een truc snel achterkomen. Als je in je werkblad inzoomed tot 39%, zie je in plaats van rasterlijnen in één keer de bereiken waarvoor je in het werkblad namen hebt gedefinieerd zichtbaar worden.

Probeer het maar eens uit en bezoek eens de pagina's van TemplateBuilding.

maandag, februari 24, 2014

Excel, het tonen van kleine getallen

Of ... Wetenschappelijke notatie.

Bij kleine getallen bijv. 0,000000056 maakt Excel bij het tonen van het getal (als de kolombreedte niet toereikend is) van de wetenschappelijke notatie. Het getal dat je ziet is: 5,6E-08 (dwz 5,6 * 10^-8). Als je deze notatie wil voorkomen, maak dan het getal in aangepaste celopmaak op door het formaat "0,#########" (of met meer "#" voor een langer getal. In plaats van de "#" kun je ook een "0" gebruiken. In het laatste geval zullen de teveel aan "0" en niet worden afgekapt.

N.B. Voor het aanpassen van de celopmaak in een cel kies voor:

Start -> Opmaak -> Celopmaak -> Tabblad Getal -> Categorie "Aangepast"

N.B. Kijk ook eens op de site van TemplateBuilding

donderdag, februari 20, 2014

Tekst samenvoegen in Excel

Tekst samenvoegen in Excel:

Het is mogelijk op een aantal manieren inhoud van cellen met behulp van een formule samen te voegen. Hieronder staan een aantal manieren uitgelegd. (Stel A1 bevat een voornaam en B1 bevat een achternaam).
Methode 1: Maak gebruik van & " " &
Met deze methode voeg je in cel C1 de formule

=A1 & " " & B1 in.

Hierdoor worden de voornaam en achternaam samengevoegd met een spatie ertussen.

Methode 2: Maak gebruik van de formule TEKST.SAMENVOEGEN
Eigenlijk zou deze formule zo moeten zijn gemaakt dat het mogelijk zou zijn een bereik aan cellen samen te voegen. Helaas moet je met de formule TEKST.SAMENVOEGEN ook elke cel afzonderlijk opgeven.
De syntax voor deze formule is (invoeren C1):

=TEKST.SAMENVOEGEN(A1;" ";B1) 

 Zelf geef ik voorkeur voor methode 1. En jullie ?

vrijdag, december 17, 2010

Nieuw w-tje, VBA eigenaardige ROUND()

Binnen Excel waarbij voor UDF (User Defined Functions = functies / formules die door de gebruiker in VBA worden gemaakt) is het mogelijk om getallen af te ronden met de VBA functie ROUND. De functie heeft echter een afwijkende manier van afronden. In dit w-tje wordt beschreven op welke wijze de functie ROUND in VBA getallen afrond en waar je bij het gebruik van de betreffende functie rekening moet houden.

Lees er meer over in de w-tjes.

dinsdag, november 16, 2010

Nieuw w-tje, voorwaardelijke opmaak, dikkere randen

Dit w-tje heeft betrekking op Excel 2007 en Excel 2010. Het gebruik van voorwaardelijke opmaak is binnen Excel een krachtig hulpmiddel om werkbladen op flexibele manier te verfraaiien. Het is mogelijk om afhankelijk van een celwaarde of formule opmaak van een cel aan of uit te zetten. Toch heb je bij het instellen van de voorwaardelijke opmaak NIET de volledige set van opmaakopties die je normaal onder Excel tot beschikking hebt. Zo ook het maken van een dikkere kaderrand. Toch is het mogelijk, als je in de xml-structuur van Excel (2007 en 2010) duikt. De betreffende workaround is op deze pagina weergegeven.

Lees er meer over in de w-tjes.

donderdag, november 11, 2010

Nieuw w-tje, Vervolgkeuzelijst met meerdere kolommen

Excel heeft de mogelijkheid om bij invoer van gegevens in een cel door middel van vervolgkeuzelijsten ondersteuning te bieden. Hierbij kan worden gebruik gemaakt van:

(Excel 2003) -> Data -> Valideren . . . . met een ingevulde validatielijst als validatiecriterium.
(Excel 2007) -> Gegevens -> Gegevensvalidatie . . . . . met een ingevulde validatielijst als validatiecriterium.

Hoewel gegevensvalidatie binnen Excel een krachtig hulpmiddel is, heeft het de beperking dat de hieruit gegenereerde vervolgkeuzelijst slechts 1 kolom bevat. Het maken van een keuzelijst met meerdere kolommen is echter ook mogelijk, maar hiervoor dient gebruik te worden gemaakt van een op het werkblad te plaatsen besturingselement. Welke handelingen hiervoor moeten worden verricht is in de w-tjes uitgewerkt voor Excel 2003. (Maar ook bruikbaar voor Excel 2007 en Excel 2010)

Lees er meer over in de w-tjes.