Mike Dole

Mike Dole

donderdag, 03 januari 2019 17:51

Rekenen met werkdagen / vakantiedagen

Vanuit de 'bouw hoek' kreeg ik een vraag of het mogelijk was om aan de hand van een startdatum, een aantal werkbare dagen en een lijstje met feestdagen bij ingave van een datum in cel A de berekende einddatum van het project in cel B te krijgen.

Hiervoor bestaat in Excel de handige functie =WERKDAG. 

Download hier het voorbeeld Excel bestand

In cel B1 zetten we de startdatum.

In cel B2 het aantal werkbare dagen.

In G2:G20 een lijstje met feestdagen.

Rekenen met werkdagen project bouw

Reken einddatum project uit

 

Ik kreeg een interessante vraag hoe je gegevensvalidatie op een cel kan toepassen zodat er alleen alfanumerieke tekens A-Z / 0-9 zijn toegestaan. Waarschijnlijk zijn er meer wegen die naar Rome leiden maar dankzij de handige Excel functie vertaal site https://nl.excel-translator.de/translator/ kwam ik op de volgende Nederlands formule:

=ISGETAL(SOMPRODUCT(VIND.SPEC(DEEL(A1;RIJ(INDIRECT("1:"&LENGTE(A1)));1);"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

Hoe pas je dit toe?

Ga in het lint naar het tabblad 'Gegevens', kies 'Gegevensvalidatie' (A), 'Aangepast' (B) en copy & paste bovenstaande functie in het 'formule' vak (C).
In mijn voorbeeld wil ik dus de tekst in cel A1 valideren. Is het een andere cel, pas dit dan aan!

Gegevensvalidatie alfanumerieke tekst

Waarden zoals AB1234 worden nu geaccepteerd, A!BCD wordt geweigerd:

Gegevensvalidatie alfanumeriek

 

Download het voorbeeld bestand hier

Bijna iedere Excel gebruiker werkt met de functie verticaal zoeken. We zoeken 1 waarde op basis van 1 zoekwaarde. Een omzet op basis van een klantnummer of een saldo op basis van een grootboekrekening. Daar houdt het normaal gesproken op! Willen we de omzet van b.v klant 100 zoeken in 1000 regels met waarbij de er ook een jaartal bij de regel vermeld staat (2015, 2016) dan schiet verticaal zoeken normaal gesproken tekort. Natuurlijk kun je hulp kolommen gebruiken waarbij je in 1 cel  klant 100_2015 / 100_2016 zet en ga je dan ook zoeken op deze combinatie maar dat wordt zo’n ‘geknutsel’. Een andere beperking van verticaal zoeken is ook dat je allen maar naar rechts kan zoeken, je vindt een klant en kan alleen waarden aan de rechterkant van het klantnummer retourneren.. Vergeet verticaal zoeken!

Ik ga jullie een mooier, sneller en krachtiger alternatief tonen!

De functie INDEX() in combinatie met vergelijken(). Lijkt lastig maar als je hem snapt dan valt dat reuze mee en heb je er verschrikkelijk veel plezier van . Ik start met een Excel tabblad STAMDATA waarin ik voor de verschillende DELI bedrijven kan opgeven welke software paketten ze in gebruik hebben, voor welk boekjaar / welke maand en wat voor tarief er aan dat pakket hangt. Het origineel wat ik gemaakt heb ziet er iets anders uit maar ik hou de namen even algemeen (aantallen / tarief is ‘fictief’):

Functie Index Vergelijken

In mijn tabblad INVOER wil ik iedere maand een opgave kunnen doen van het aantal pakketten in gebruik bij de bedrijven waarbij ik wil dat het tarief wat berekend wordt voor het software pakket uit het tabblad STAMDATA wordt opgehaald op basis van bedrijf, boekjaar maand en menuitem_code uit het tabblad STAMDATA. Ga daar maar eens verticaal op zoeken :)

Index vergelijken

De hele formule om dit voor elkaar te krijgen is:

{ =INDEX(STAMDATA!$A$3:$E$5001;VERGELIJKEN(1; (B2=STAMDATA!$B$3:$B$5001) * (D2=STAMDATA!$A$3:$A$5001) *  (A2=STAMDATA!$D$3:$D$5001) *  (C2=STAMDATA!$C$3:$C$5001);0);5;1)}

In eerste instantie denk je (dacht ik) dan ‘laat maar, dat kwartje blijft bij mij niet hangen, ik ga het niet eens proberen..’
Dit valt mee, eigenlijk kun je ze uit elkaar trekken.

Je hebt een INDEX deel: {=INDEX(RANGE UIT STAMDATA WAAR IK WAT UIT WIL HALEN;  ‘HIER ZOEK IK DE REGEL IN STAMDATA DMV VERGELIJKEN’   ;5 (e kolom uit stamdata);1)}

En een VERGELIJKEN deel: {=VERGELIJKEN(1; (B2=STAMDATA!$B$3:$B$5001) * (D2=STAMDATA!$A$3:$A$5001) *  (A2=STAMDATA!$D$3:$D$5001) *  (C2=STAMDATA!$C$3:$C$5001);0)}


LET OP DE RODE {} om de functies geeft aan dat we te maken hebben met een zgn. MATRIX functie. Je kunt deze haakjes niet intypen maar je moet bij het afsluiten van je functie de toesten CTRL-SHIFT-ENTER gebruiken. De {} worden er dan automatisch omheen gezet.

Zonder {} is je functie nutteloos!


 

Zet ik alleen de functie vergelijken in een cel:

Dan krijg ik, mits alle zoekwaarden gevonden worden een 1:

Verander ik even cel B2 naar 2020, een boekjaar waarvan ik nog geen tarieven heb opgegeven in het tabblad stamdata dan krijg ik een fout:

Als je verticaal zoeken gebruikt dan begrijp je wel dat je de 5e kolom opvraagt uit STAMDATA!$A$3:$E$5001.

Hier raken we echter wel de beperking van verticaal zoeken, want waar we bij verticaal zoeken alleen naar rechts kunnen, kunnen we hier ook de 0e of 1e kolom terugvragen c.q. naar links ‘zoeken’.

In het voorbeeld hieronder is goed te zien wat de functie vergelijken doet, hij zoekt een ‘match’ tussen de waarde in B2 (2017) met een cel in STAMDATA!B3:B5001 en een match tussen D2 (OFFICE)  met een cel in STAMDATA!A3:A5001,

A2 (BHP) moet matchen met een cel in STAMDATA!D3:D5001 en als laatste moet C2 (MAAND) matchen met een cel in STAMDATA!C3:C5001.

Vergeet verticaal zoeken, gebruik index vergelijken!

Aan ALLE voorwaarden moet dus worden voldaan, anders krijgen we een foutmelding!

Voorbeeld Index vergelijken

Download de voorbeeld file HIER!

 

 

Ik kreeg recentelijk de volgende vraag: 'Ik wil eigenlijk automatisch een msgbox laten verschijnen als de waarde in cel B1 ingevuld is en cel A1 leeg is. Is dit mogelijk?'

In dit geval ging het er om dat in zijn Excel bestand een werknemersnummer werd ingevuld, zonder dat er een naam van een werknemer ingevuld was!

Ik heb toen 2 mogelijkheden uitgewerkt:

VBA

DOWNLOAD HIER VOORBEELD BESTAND 1

M.b.v. een klein stukje VBA code kunnen we afdwingen dat er gekeken wordt na een 'change' van het werkblad of A2 leeg is en B2 niet, in dat geval wordt er een melding getoond:

 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Range("B2").Value <> Empty And Range("A2").Value = Empty Then

  MsgBox "vul een naam in!", vbCritical, "waarschuwing"

End If

End Sub

 

Bovenstaande code controleert slechts 1 cel combinatie (A2 / B2). Als we dit uit willen breiden naar bijvoorbeeld 10 regels, vervang dan bovenstaande door:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim MyRange As RangeDim currentCellDim nextCellDim i As Integer

 

Set currentCell = Range("A2")

Do While i < 10   

Set nextCell = currentCell.Offset(0, 1)   

If currentCell.Value = Empty And nextCell.Value <> Empty Then    

  MsgBox "vul een naam in!", vbCritical, "waarschuwing"   

End If        

Set currentCell = currentCell.Offset(1, 0)    

i = i + 1

Loop

 

End Sub  

Gegevensvalidatie

DOWNLOAD HIER VOORBEELD BESTAND 2

Een alternatieve manier zonder VBA is het gebruik van Excel gegevensvaldiatie. Ga op de cel staan waar je de validatie op uit wilt laten voeren (in ons voorbeeld B3) en kies op het lint, het tabblad 'GEGEVENS' --> 'Hulpmiddelen voor gegevens' --> 'Gegevens validatie.

Lint gegevensvaldiatie Excel

Bij 'INSTELLINGEN' kiezen we bij 'TOESTAAN' voor 'AANGEPAST' en bij de formule geven we op:

=(EN(EN(ISLEEG(A3)=ONWAAR;ISLEEG(B3)=ONWAAR);LENGTE(B3)>=6))

 Excel gegevens validatie

'Lege cellen negeren' laten we 'uit'.

Met Copy / Paste trekken we dit door naar alle cellen die we willen valideren. Zoals je misschien al hebt gezien controleert deze validatie ook nog of de lengte van de code die we opgeven minimaal 6 posities is!

Als we nu in het voorbeeld bestand een code opgeven zonder naam of een code die < 6 tekens is dan 'triggert' dat de validatie:

Voorbeeld gegevensvalidatie Excel

 

In veel ERP systemen is een telefoonnummer veld vaak een 'simpel' tekst veld, zonder enige vorm van validatie. We zien telefoonnummers in het formaat 0182345678, 0182-345678, +31182345678, 0182-34.56.78, '0182345678, nummers met spaties, etc. Ik kreeg recentelijk een verzoek of daar in Excel iets mee te doen is, iemand had een download uit zijn systeem en wilde hier de telefooncentrale mee 'voeden'. Bijkomende requirement was dat de kolom landcode NL, BE of leeg kon zijn, indien leeg moest er uitgegaan worden van een NL landcode.

Met een simpele 'vervangen' functie in Excel kom je  er niet, ik moest dus kijken of ik een VBA functie kon maken ;)

Ik heb een kolom met telefoonnummers, een kolom met de landcode en een (hulp) tabel waarin ik achter de landcodes de internationale toegangsnummers / prefix zet.

De functie die ik gemaakt heb heet StripEnInternationaliseer (What's in a name..):

Excel omzetten telefoonnummer

 

De functie heeft 3 parameters:

MyRange bevat de cel met het nummer wat we om willen zetten.
MyLandcode bevat de cel met de landcode
MyLandCodeRange bevat de cellen range waarin alle landcodes met hun toegangsnummers staan

 

Public Function StripEnInternationaliseer(MyRange As Range, MyLandcode As Range, MyLandcodeRange As Range)Dim nTmpVal As StringDim nLandCode As StringDim nPrefix As String

nTmpVal = Replace(MyRange.Value, ".", "")nTmpVal = Replace(nTmpVal, "-", "")

'FF omzetten naar integer omdat we een nummer willen.. If IsNumeric(nTmpVal) Then       nTmpVal = CStr(Int(nTmpVal))End If

'LandcodenLandCode = MyLandcode.ValueIf MyLandcode = "" Then       nLandCode = "NL"End If

'Prefix opzoeken o.b.v. landcode nPrefix = Application.WorksheetFunction.VLookup(nLandCode, MyLandcodeRange, 2, False)

'Samenvoegen resultaten nTmpVal = nPrefix + nTmpVal

'En teruggeven aan VBA functie StripEnInternationaliseer = nTmpVal

End FunctionDOWNLOAD het bestand HIER

 

donderdag, 12 februari 2015 21:23

Gratis cursus excel 2013

Online Excel Cursus heeft momenteel een hele leuke actie via 'Learn it'. Een gratis online cursus Excel 2013 van 6 lessen!

Deze Excel 2013 cursus is een online cursus om kennis te maken met de vele mogelijkheden van Excel 2013. U leert in zes lessen hoe u een Excel werkblad kan opmaken, hoe u eenvoudig calculaties kunt maken, data kan sorteren of filteren en formules en grafieken kan maken, waarmee u vriend of collega kan verbazen!

Omdat de cursus is opgebouwd m.b.v. alledaagse praktijk voorbeelden is hij erg toegankelijk en wordt leren weer leuk!

KLIK HIER!

 

LearnIt heeft natuurlijk ook een uitgebreide range aan betaalde cursussen (o.a. E-learning) op ieder niveau, twijfelt u aan uw kennisniveau? Ze hebben ook een hele handige cursuswijzer!

 

 

donderdag, 27 november 2014 20:33

Externe gegevens ophalen via ODBC moeilijk?

Zelfs in deze tijd zie je als je rond kijkt bij bedrijven nog mensen die grote hoeveelheden data aan het overkloppen zijn.  Heel soms is er 1 super user die weet hoe hij / zij vanuit hun ERP systeem een export kan maken naar tekst file en hoe deze in Excel te openen.

In dit item laat ik zien hoe je in een paar stappen (mits je toegang hebt tot de database o.b.v je active directory account of de inloggegevens hebt van een SQL account) externe gegevens uit een SQL sever 2008 database ophaalt!

Stap 1

Ga naar het tabblad Gegevens - Van andere bronnen - Van SQL Server:

 

Stap 2

De wizard gegevensverbinding wordt gestart, vul bij servernaam de naam van de server op waar de SQL database op draait. Staat deze op de pc / server waar je op aan het werk bent gebruik dan de  servernaam localhost, bij aanmeldingsreferenties kies je voor windows verificatie als jouw account rechten heeft of je geeft de SQL naam en het wachtwoord op:

Stap 3

Als je rechten in orde zijn worden alle views en tabellen getoond die in de database staan, kies 1 van de tabellen:

Stap 4

Geef de locatie aan waar je de opgehaalde gegevens wilt plaatsen:

 

Stap 5

Bij de laatste stap zie je een samenvatting van de keuzes die je hebt gemaakt, bij 'type opdracht' zie je TABEL, we kunnen dit aanpassen naar SQL:

 

 

Het voordeel van 'Type opdracht' SQL is dat we een subset aan velden kunnen opgeven en eventueel kunnen joinen naar andere tabellen. Het is bijvoorbeeld mogelijk om met SQL management studio met click / drag & drop je tabellen bij elkaar te harken en de SQL die daar uit voort komt via copy / paste hier te plaatsen:

 

Nog 1 ding wat goed is om te weten is dat als je je query aanpast en bijvoorbeeld extra velden toevoegt deze niet altijd op de plek komen waar jij ze wilt hebben, ze verschijnen dan achteraan de reeds bestaande kolommen. Om dit aan te passen ga je ergens in de tabel op een cel staan en kies je voor 'Gegevens - Eigenschappen', bij de Externe gegevenseigenschappen zet je het vinkje bij 'Sortering / filterin / indeling van kolommen behouden' uit:

 

Eigenlijk is het redelijk eenvoudig om Excel rechtstreeks te laten lezen in de SQL of Oracle database, zonder dat je zoals sommige misschien denken het risico loopt om de data in de database te beschadigen.

Externe gegevens ophalen via Excel gebruikt alleen een Select statement, je database is dus veilig voor users die met Excel connecten. Wat ik wel eens zie is dat de (super) users die ODBC'n op de database zulke complexe queries met complexe joins bouwen dat ze de performance van de database (en dus van je ERP applicatie) nadeling beinvloeden. Op een terminal server zie je dan dat de Excel sessie van Jantje / Pietje 95% CPU in beslag neemt, pas hier voor op!

Meer over de combinatie Excel - SQL vind je hier:

https://www.simple-talk.com/sql/database-adminitration/getting-data-between-excel-and-sql-server-using-odbc--/

vrijdag, 21 november 2014 06:33

Wat kan ik met Office 365

Voorbij zijn de dagen van het kopen van dozen met Office-software. Microsoft zet in op het het abonnement model, waarbij we kleine bedragen per maand / jaar betalen voor langere tijd in ruil voor steeds verse functies en diensten geleverd via het internet.

Office 365 altijd en overal beschikbaar

Waarom zou ik mijn vertrouwde pc versie inruilen voor een Office 365?

  • Office 365 is beschikbaar waar en wanneer u het nodig hebt, thuis, vakantie, hotel, trein? Het maakt niet uit waar u bent!
    PC, tablet, telefoon? Het maakt niet uit met welk device u aanmeldt!
  • Office365serviceswerken gemakkelijk samen. Communicerenmet instantmessaging, toont de aanwezigheid van uw vrienden en collega'sreal-time, videoconferencing, en desktopsharing-bereikbaarvanaf alle vertrouwdeOffice-toepassingen.
  • Eenvoudig te installeren, minder storings gevoelig dan de pc versies en Office 365is ook ontworpen voor professionalsenkleine bedrijven ommeteen aan de slag te kunnen. Alsdeze bedrijvenniet over 'in-house'IT-personeel beschikken, geen probleem!Het opzetten van nieuwegebruikers en huntoestemmingniveausis in een handomdraai geregeld.
  • Office365betekent dat er geenverrassingen. Met een duidelijketechnologische enfunctionelroadmap en automatische updates biedt Office365 in de cloud u de nieuwste techniek en de hoogste betrouwbaarheid!

Wat kost het?

Office 365 kost op het moment van schrijven 10 € per maand betalen voor het recht om het te installeren op een maar liefst vijf computers. Voor dat geld geeft Microsoft tevens elke gebruiker op uw Office 365-account maar liefst terabyte aan Microsoft OneDrive cloud storage. Dus 5 terabytes online, plus al die Office-software.

In vergelijking met de online concurrentie heeft Office 365 meest krachtige functies, variërend van Excel macro's naar Word mail-merge trucs.

Web versies

Zodra u zich aanmeldt op onedrive met een Microsoft-account, kunt u overigens al gebruik maken van een gratis web versie en documenten, spreadsheets en presentaties bewerken, je krijgt zelfs 15 GB gratis online opslagruimte.

In de Web-versies ontbreken wel veel functies die in de normale Office 365 zitten. U kunt bijvoorbeeld geen tekstvak niet maken in Word, of 3-D grafieken in Excel, bijvoorbeeld. Maar ze voelen zich als de klassieke programma's en beter presteren als het gaat om het opslaan van bestanden online en samenwerken met collega's.

Ik gebruik sinds kort voor mijn Excel demo's Easy Sketch Pro, een geweldig programma! Kijk hier

Als je echt indruk wilt maken met je demo is dit het stukje (goedkope) software wat je nodig hebt! Op het werk gebruik ik het regelmatig om korte en inspirerende instructie filmpjes te maken. Easy Sketch Pro 2 komt met een groot aantal sketch plaatjes, netjes verdeeld in categorieën. Zet de plaatjes of achtergrond video en tekst neer per scene en kijk hoe alles real-time getekend wordt!

 

 

Categorieën

zondag, 02 november 2014 12:59

Gemakkelijk checkboxen aanmaken

De volgende macro heb ik geschreven n.a.v. een vraag van iemand of het mogelijk was om snel per rij in Excel een aantal checkboxes aan te maken die allemaal koppelen naar de volgende kolom. Dus bijvoorbeeld in kolom D vanaf rij 5 20 checkboxes die linken naar kolom rij 5 t/m 25.

 Sub SnelAanmakenCheckBoxen()
    Dim str1 As String
    Dim str2 As String
    Dim str3 As String
    Dim str4 As String
    Dim OLEObj As OLEObject
    Application.ScreenUpdating = False
    
    
    str1 = InputBox("Geef een unieke naam ter identificatie van de checkboxen (b.v. verdediger,middenvelder,aanvaller):", "Naam")
    str2 = InputBox("In welke kolom moeten de checkboxen komen (D,E,F,etc):", "Kolom info")
    str3 = InputBox("Start checkboxen op rij (1,2,3,etc):", "Startrij")
    str4 = InputBox("Stop checkboxen op rij (1,2,3,etc):", "Stoprij")
    
    Dim i
    Dim nleft As Integer
    Dim ntop As Integer
    Dim nname As String
    
    If Val(str3) = 0 Or Val(str4) = 0 Then
        MsgBox ("Geen geldige rij opgegeven voor checkboxen")
        Exit Sub
    End If
        '
    For i = Val(str3) To Val(str4) 'cells from 1st to 10th
    
    nleft = Cells(i, str2).Left
    ntop = Cells(i, str2).Top
    
    
    Set OLEObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=nleft, Top:=ntop, Width:=21.75, Height:=15 _
        )
        
     nname = str1 & i
        
     OLEObj.Name = nname
     OLEObj.Object.Caption = ""
     OLEObj.LinkedCell = Chr(Asc(str2) + 1) & i
     OLEObj.Object.Value = False
    
    Application.ScreenUpdating = True
    Next
End Sub

 Met deze macro kon hij zijn Excel gemakkelijk opbouwen zonder stuk voor stuk de checkboxes aan te hoeven maken.

Klik HIER voor de Excel

Pagina 1 van 7

Excel Software Shop

Web Analytics