donderdag, december 07, 2017

Nieuw sjabloon, WiskundeABC

Een hulpmiddel voor leerlingen van de middelbare school. Je hebt bijvoorbeeld een wiskundige formule in de vorm van:
ax^2+ bx + c. Zoals je ziet gaat het hier om een parabool, waarvoor je de snijpunten met x-as kan bepalen door de vergelijking op 0 te stellen. Voor het oplossen van een dergelijke vergelijking heb je een aantal alternatieven. Product/Som, ABC-formule, buiten haakjes op basis van de grootste gemene deler.

Hoe los ik in Excel de genoemde vergelijking op en hoe geef ik het geheel vorm. Hoe maak ik het geheel een hulpmiddel voor studenten om uitgewerkte antwoorden te controleren. Kijk op de site van TemplateBuilding.

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)

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.

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 ?

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

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.

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.


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 de site van TemplateBuilding pagina weergegeven.

dinsdag, november 02, 2010

Nieuw w-tje, Splitsen Cellen

Het kan voorkomen dat je de inhoud van bepaalde cellen (tekst), die je in eerste instantie als één tekstveld in een cel hebt geplaatst zou willen splitsen, spreiden over meerdere cellen. Dit zou je in meerdere stappen met Excel-(tekst)formules kunnen bewerkstelligen, maar het is ook mogelijk hiervoor de ingebouwde Excel-wizard te gebruiken. Deze werkwijze is hier uitgewerkt.

Lees er meer over in de w-tjes.

vrijdag, oktober 08, 2010

Nieuw w-tje, DATUMVERSCHIL

Bent u op zoek naar een functie waarmee u op basis van een geboortedatum de leeftijd van een persoon (of dier) wil bepalen, of het verschil in maanden of dagen tussen 2 verschillende datumwaarden ? Op het moment dat u door de lijst met beschikbare functies binnen Excel bladert komt u deze niet tegen.

Toch is de betreffende formule er wel, maar moet handmatig worden ingevoerd!

Lees er meer over in de w-tjes.

vrijdag, september 17, 2010

Nieuw w-tje, Autofilter en lege regels

Heb je de totale lijst onderverdeeld en gebruik je lege rijen, kan dit bij het gebruik van Autofilter ongewenste resultaten opleveren en loop je kans dat niet de gehele lijst wordt gefilterd. Hoe je dit kunt voorkomen staat beschreven op de site van TemplateBuilding.

Lees er meer over in de w-tjes

maandag, september 06, 2010

Nieuw w-tje, Validatie unieke invoerwaarde

Met Gegevensvalidatie (onder het lint Gegevens in Excel 2007) is het mogelijk om de gebruiker een waarde in een cel te laten invoeren met een vervolgkeuzelijst. Met dezelfde techniek is het ook mogelijk om te controleren of een gebruiker een unieke waarde in een bereik invoert. Hoe dit in zijn werk gaat staat op de site van TemplateBuilding beschreven.

Lees er meer over in de w-tjes.

vrijdag, juli 30, 2010

Nieuw w-tje, Aanpassen Autofilter

"Autofilter" is een krachtig hulpmiddel binnen Excel om in een lijst met gegevens te zoeken. Wel wordt er binnen Excel bij de filtering uitgegaan van een aaneengesloten lijst. Wil je echter ten behoeve van de layout tussen de te filteren kolommen een extra dummykolom opnemen dan loop je tegen een probleem op. Ook de dummykolommen krijgen een dropdownpijl. Dit is iets wat je niet zou willen. Voor de oplossing is een macro vereist en enige kennis van VBA gewenst.

Lees er meer over in de w-tjes.