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.

 

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

Ik kreeg laatst de vraag: hoe kan ik bij onderwerp maken dat je in het invoervak van de keuzelijst de beginletters van het te zoeken woord kan intikken en verder kan scrollen in keuzelijst?

Standaard moet je bij gegevensvalidatie met een keuzelijst met de muis scrollen en de gewenste waarde zoeken:

Een auto complete / autofill functionaliteit die op basis van een letter de keuze mogelijkheden geeft zit eigenlijk niet in de lijst validatie van Excel, ik ben eens gaan zoeken en kwam op de volgende oplossing (zie voorbeeldbestand)

Stap 1:

Zet op blad2 een range met bijvoorbeeld een aantal plaatsnamen:

Stap 2

Kies tabblad Formules - Namen beheren - Nieuw - Geef naam MyList en bij 'verwijst naar':

=VERSCHUIVING(Blad2!$A$1;VERGELIJKEN(Blad1!$A$1&"*";Blad2!$A$1:$A$300;0)-1;0;AANTALARG(Blad2!$A:$A))

Ga naar cel A1 op blad1 en kies tabblad Gegevens - Gegevensvalidatie - kies Toestaan lijst, Bron=MyList

 

De gebruiker moet nu een letter opgeven en nu zal, als hij op het pijltje klikt, een selectie getoond worden vanaf de gekozen letter (let op de bron range moet alfabetisch gesorteerd zijn):

Veel mensen laten de Excel sajblonen links liggen, dit terwijl ze erg handig en zeer informatief zijn.
Waarom het wiel opnieuw uitvinden ofwel waarom met een leeg werkblad beginnen als er standaard een Excel sjabloon beschikbaar is waarin hetgeen je in je hoofd hebt al zit?

Kies ‘Bestand – Nieuw’ en er worden al een groot aantal sjablonen getoond, variërend van onkostendeclaraties tot  kant en klare kalenders.

Dit scheelt je echt uren werk!

Verder kun je hier online zoeken via de textbox: ‘Sjablonen zoeken in Office.com’. Ben je als leraar op zoek naar een aanwezigheid overzicht van je leerlingen in Excel, dan hoef je dat niet helemaal opnieuw te bouwen maar is dit gewoon via ‘zoeken’ online te vinden!

Het ‘neuzen’ in deze sjablonen leert je ook heel veel, je ziet hoe veel formules in de praktijk gebruikt worden en op basis van het doel van de template komen de formules ook veel duidelijker over.

Zelf een sjabloon maken

Als je een Excel werkblad hebt gemaakt die je veelvuldig gaat gebruiken kun je hier heel eenvoudig een sjabloon van maken.
Kies ‘Bestand – Opslaan Als’, kies in het vak ‘opslaan als’: Excel sjabloon of Excel sjabloon met macro’s .

Als je nu ‘Bestand – Nieuw – Mijn sjablonen’  kiest dan zie je je zojuist opgeslagen sjabloon er tussen staan!

De koppelingen in PowerPoint bevatten het complete / absolute pad naar het Excel bestand. Kopiëren en plakken geeft problemen omdat er naar 'oude' bestanden wordt verwezen...
In deze video toon ik hoe je m.b.v. een macro / vba functie het pad kan verwijderen zodat alleen de bestandsnaam in de koppeling overblijft!

Vooraf: Als we via 'Bestand - Koppelingen naar bestanden bewerken' kijken naar de koppeling dan zien we het volledige pad in de koppeling staan...

We gaan een VBA functie / Macro maken om het pad uit alle koppelingen in alle slides te verwijderen!

Stap 1: Toets ALT-F11

Stap 2: Kies boven in het menu voor 'Invoegen - Module'

Stap 3: Voeg de volgende code toe:

Global fso As New FileSystemObject

Public Sub MaakKoppelingenRelatief()
    Dim i As Integer
    Dim sld As Slide, shp As Shape
    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            If shp.Type = 10 Then
                Dim path As String, fname As String
                path = shp.LinkFormat.SourceFullName
                fname = GetFilenameFromPath(path)
                shp.LinkFormat.SourceFullName = fname
                 i = i + 1
            End If
        Next
    Next
    If i > 0 Then
        MsgBox "Bijgewerkt: " & CStr(i) & " koppelingen", vbOK
    Else
        MsgBox "Geen koppelingen gevonden.", vbOK
    End If
End SubFunction GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Stap 4: Wat we nog wel even moeten doen is in het menu bij 'Extra - Verwijzingen' de Microsoft Scripting Runtime aanvinken. Dit omdat we FileSystemObject gebruiken!
Blader naar beneden door de lijst en selecteer Microsoft Scripting Runtime

Stap 5: Terug in de presentatie kiezen we nu voor 'ALT-F8 - Maak koppelingen relatief - Uitvoeren'

Kijken we nu bij 'Bestand - koppeling naar bestanden bewerken' dan zien we dat het pad is verdwenen!!

 

De koppelingen in PowerPoint bevatten het complete / absolute pad naar het Excel bestand. Kopiëren en plakken geeft problemen omdat er naar 'oude' bestanden wordt verwezen...

In deze video toon ik een oplossing door het pad met bijvoorbeeld kladblok weg te halen zodat alleen de bestandsnaam overblijft.

Stap 1: Hernoem het .pptx bestand naar een .zip bestand

Stap 2: Open dit bestand en blader naar: PPT - Slides - _rels

Stap 3: Sleep het .xml.rels bestand naar een plek 'buiten de zip'. Klik met de rechtermuisknop en kies bewerken.

Stap 4: We verwijderen het absolute pad maar laten file:/// en de bestandsnaam staan.
Eventueel gebruiken we ALT-H, dit werkt gemakkelijker als er meerdere koppelingen zijn! We vervangen dan het pad door 'niets'

Stap 5: We slepen de aangepaste .xml.rels weer terug naar de zip en kiezen daar OK

Stap 6: Hernoem het .zip bestand terug naar een .pptx bestand

Als we nu een kopie maken van de bestanden uit de map januari naar een nieuwe map februari...
En we wijzigen de gegevens in de Excel, dan zien we als we 'koppelingen bijwerken' kiezen dat de presentatie de nieuwe Excel data bevat. Ook de koppeling zal verwijzen naar de nieuwe locatie!

 

 

 

'Stel' We hebben een Excel bestand met gegevens over de maand januari En een powerpoint die verwijst naar deze 'gegevens.xlsx' Via 'Bestand - Koppelingen naar bestanden bewerken' zien we de 'harde' verwijzing naar het Excel bestand in de map januari...

We kopiëren nu de bestanden naar de map februari Passen de cijfers aan. Als we nu het net gekopieerde bestand openen dan zien we -hoewel we kiezen voor 'koppelingen bijwerken'- dat de cijfers niet bijgewerkt worden! Bij de eigenschappen van de koppeling zien we nog steeds een verwijzing naar het bestand in de map januari...

In dit filmpje wordt bovenstaand probleem getoond, oplossingen vindt u in deel 2 en deel 3 van deze serie

 

Als we een bestand openen met een kolom waarin getallen en tekst door elkaar staan kunnen we natuurlijk op het gele bordje klikken met het uitroepteken en kiezen voor 'converteren naar getal' maar als je een lange lijst moet converteren wil het nog wel eens voor komen dat je het gele bordje met uitroepteken niet 1-2-3 meer kan vinden. In dit Excel instructie filmpje ziet u een gemakkelijke / alternatieve manier om tekst om te zetten naar getallen.

Sparklines in Excel zijn eigenlijk een soort van 'mini grafiekjes' die je gemakkelijk naast een tabel kan plaatsen om bijvoorbeeld een ontwikkeling / tendens in omzet te benadrukken:

Opdracht:

We hebben een tabel waarin de omzet over 2012 getoond wordt met damesfietsen, herenfietsen en kinderfietsen.

Na de laatste kolom willen we een sparkline tonen die de ontwikkeling in omzet accentueert.

Stappen:
Tabblad Invoegen - Sectie Sparklines - Kies bijvoobeeld Lijn:

 Geef bij Gegevensbereik B4:M4 in (de hele regel voor damesfietsen) en bij Locatiebereik / waar zet ik de sparklinegroep neer N4

Herhaal dit voor de herenfietsen en de kinderfietsen, LET OP: Veel gemakkelijker is nu om met CTRL-C / CTRL-V (Kopiëren / Plakken) de herenfietsen en kinderfietsen te doen!

De sparklines worden nu achter de tabel getoond:

 

Als we op de sparklines gaan staan kunnen we nu kiezen voor het tabblad Ontwerpen.
In onderstaande afbeelding hebben we bij de sparkline voor de damesfietsen gekozen voor Kolom en hebben we Hoge punt aangevinkt waardoor we zien dat de hoogste maand augustus een andere kleur krijgt:

 

Met sparklines geef je je Excel werkblad meer het idee van een echt dashboard!

Download het voorbeeldbestand HIER

Verticaal zoeken is 1 van de meest gebruikte formules in Excel!
Waarom? Omdat je met deze formule heel eenvoudig waarden in gegevenslijsten terug kan vinden. We laten nu aan de hand van een simpel voorbeeld zien hoe verticaal zoeken in Excel nu precies werkt zodat u het ook onder de knie krijgt.

Opdracht zoek in onderstaande tabel het percentage stemmen dat de PVDA gehaald heeft in de groep 75 jaar en ouder

In de cel onder ‘Partij’ staat de volgende formule:
=VERT.ZOEKEN(A14;A5:H11;8;ONWAAR)

Stap voor stap is dat:

• Zoek A14 = PVDA
• Zoek in het gebied A5:H11 dat is de hele tabel inclusief kolomlabels (groenlinks, D66, etc)
• Laat als de zoekwaarde gevonden is de 8E kolom terug van de regel waarin de zoekwaarde gevonden is zien. Had hier 1 gestaan dat kregen we als uitkomst de kolomlabels: groenlinks, D66, etc. Nu staat er 8 en krijgen we dus de 8e kolom terug als uitkomst.
• Zet benaderen op ‘ONWAAR’ zodat alleen iets wordt getoond als de zoekwaarde (pvda) exact is gevonden.

Om de laatste parameter ‘benaderen’ te verduidelijken wordt in het voorbeeld hieronder dezelfde formule gebruikt, echter met het veld benaderen op ‘WAAR’
=VERT.ZOEKEN(A29;A21:B25;2;WAAR)

Hierdoor wordt eigenlijk tegen de formule gezegd: ‘toon mij de uitkomst die het dichts tegen de zoekwaarde aanzit'. Er wordt dus gezocht op de waarde 64, die wordt niet gevonden maar de dichtstbijzijnde waarde wordt getoond = C.

Download het voorbeeldbestand HIER