vrijdag 17 juni 2016

Excel: lastige berekening gewogen percentage opgelost met matrix functies

Gewogen percentage

Een ogenschijnlijk simpele vraag leidt tot het nodige denkwerk.


Een collega kwam met het volgende simpele tabelletje:

Categorie # Items # Incidenten
a 1 1
b 1 1
c 5 1
d 1 1
e 1 1
f 1 3
g 1 1
h 1 1
i 3 1
j 1 1

De vraag was hoe het gewogen percentage te berekenen per categorie. Het heeft enige hoofdbrekens gekost maar uiteindelijk hebben we de oplossing.

In dit geval weegt het aantal incidenten zwaarder naarmate er minder items zijn. Zijn er minder items zijn, dan geldt het omgekeerde. Dit betekent dat we het aantal incidenten moeten delen op het aantal items.

Dit leidt in kolom D tot een simpele formule:

=C2/B2

Deze formule kunnen we dan doortrekken naar beneden. Om nu tot een gewogen percentage te komen moeten we de uitkomst delen door de som van alle delingen:

=SOM($C$2:$C$11/$B$2:$B$11)

Voor de juiste berekening moeten we hier een matrix formule van maken. Dat wil zeggen dat we de functie moeten bevestigen door op CTRL SHIFT + ENTER te drukken (in plaats van gewoon op ENTER)

De uiteindelijke formule wordt dan:

{=C2/B2/(SOM($C$2:$C$11/$B$2:$B$11))}

Deze kunnen we weer doortrekken naar beneden. Het uiteindelijk resultaat wordt dan:

Categorie # Items # Incidenten gewogen per categorie in %
a 1 1 9,49%
b 1 1 9,49%
c 5 1 1,90%
d 1 1 9,49%
e 1 1 9,49%
f 1 3 28,48%
g 1 1 9,49%
h 1 1 9,49%
i 3 1 3,16%
j 1 1 9,49%

Matrix berekening

Het verschil tussen een matrix berekening en een gewone berekening is duidelijk te zien in het volgende voorbeeld:

Categorie # Items # Incidenten gewogen per categorie in % Matrix Normaal
a 1 1 9,49% 10,53333 1
b 1 1 9,49% 10,53333 1
c 5 1 1,90% 10,53333 0,2
d 1 1 9,49% 10,53333 1
e 1 1 9,49% 10,53333 1
f 1 3 28,48% 10,53333 3
g 1 1 9,49% 10,53333 1
h 1 1 9,49% 10,53333 1
i 3 1 3,16% 10,53333 0,333333
j 1 1 9,49% 10,53333 1

In de voorlaatste kolom staat de formule:

{=(SOM($C$2:$C$11/$B$2:$B$11))}

Daar wordt het totaal berekend van alle delingen per rij.

In de laatste kolom staat de formule:

=(SOM($C$2:$C$11/$B$2:$B$11))

Daar wordt het totaal van de deling per rij. De functie SOM is daar in feite overbodig. We hadden er ook kunnen neerzetten:

=($C$2:$C$11/$B$2:$B$11)

of gewoon:

=C2/B2

en dan die formule doortrekken.

Bijbehorende bestand ExcelGewogenPercentage.xlsx is te downloaden via:

donderdag 16 juni 2016

VBA: script om internet explorer pagina's geautomatiseerd om te zetten in PDF's

De aanleiding voor dit script is de behoefte om geautomatiseerd een aantal rapporten van Reporting Services om te zetten naar PDF formaat.

Het onderstaande VBA script is algemeen te gebruiken om een willekeurige internet pagina om te zetten naar PDF. Het kan aangeroepen worden vanuit elk Office pakket. Zelf heb ik het gemaakt in Access.

Voor het omzetten van de internet pagina naar PDF heb ik gebruik gemaakt van de PDFCreator. Dit kan gratis worden gedownload: http://www.pdfforge.org/

De PDFCreator kan als printer worden aangeroepen met VBA. Binnen dit programma kunnen we een aantal zaken instellen zoals de map waarin we de PDF's willen opslaan.


Het VBA script:

'functie nodig om de standaard printer aan te passen
Private Declare Function SetDefaultPrinter Lib "winspool.drv" Alias "SetDefaultPrinterA" (ByVal pszPrinter As String) As Long

Sub printURL()
    Dim objExplorer As Object
    Dim lngQuery As Long 'geef waarde QueryStatusWB
    Dim intTeller As Integer
    Dim sglTime As Single
    Dim prtDefault As Printer
    Dim strURL As String

    'URL instellen
    strURL = "http://www.google.nl"

    'Internet Explorer starten, zichtbaar maken en van een pagina voorzien
    Set objExplorer = CreateObject("InternetExplorer.Application")
    objExplorer.Visible = True
    objExplorer.Navigate strURL

    'startprinter vastleggen
    Set prtDefault = Application.Printer 'huidige printer vastleggen
    'MsgBox prtDefault.DeviceName

    'printer instellen door functie aan te roepen; download PDFcreator
    SetDefaultPrinter ("PDFCreator")

Herhaal:
        'Wacht 5 seconden zodat IE de pagina kan laden
        sglTime = Timer
        Do While sglTime > Timer - 5
            DoEvents
            'The DoEvents function surrenders execution of the macro so that the operating system can process other events.
            'The DoEvents function passes control from the application to the operating system.
        Loop
        lngQuery = objExplorer.QueryStatusWB(6)  'command status van de printer

        If lngQuery And 2 Then
            objExplorer.ExecWB 6, 2, "", ""   'Ok om af te drukken? Voer het Printcommando uit (6) zonder print venster te tonen (2)
            '1 toont print venster wel
            'wacht 5 seconden zodat IE kan afdrukken
            sglTime = Timer
            Do While sglTime > Timer - 5
                DoEvents
            Loop
        Else
            GoTo Herhaal
        End If

    'internet explorer sluiten en object verwijderen
    objExplorer.Quit
    Set objExplorer = Nothing

    'oorspronkelijke printer terugzetten
    SetDefaultPrinter (prtDefault.DeviceName)
End Sub

vrijdag 3 juni 2016

Excel: VBA script om wachtwoord te verwijderen

Af en toe krijg ik een vraag om een wachtwoord van een Excel blad te halen. Doodsimpel met VBA. Hier een script dat ik gebruik:

Sub WachtwoordCrack()
    Dim a As Integer, b As Integer, c As Integer, d As Integer, _
    e As Integer, f As Integer, g As Integer, h As Integer, _ 
I As Integer, j As Integer, k, m As Integer
    Dim begin As Date, eind As Date
    Dim duur As String
    Dim objSheet As Worksheet
    begin = TimeValue(Time)
    On Error Resume Next
    For Each objSheet In Application.Worksheets
        For a = 65 To 66: For b = 65 To 66: For c = 65 To 66
            For d = 65 To 66: For e = 65 To 66: For f = 65 To 66
                For g = 65 To 66: For h = 65 To 66: For I = 65 To 66
                    For j = 65 To 66: For k = 65 To 66: For m = 32 To 126
                        ActiveSheet.Unprotect Chr(a) & Chr(b) & _ 
 Chr(c) & Chr(d) & Chr(e) & Chr(f) & _ 
 Chr(g) & Chr(h) & Chr(I) & Chr(j) & Chr(k) & Chr(m)
                        If ActiveSheet.ProtectContents = False Then
                            eind = TimeValue(Time)
                            duur = Format(eind - begin, "hh:mm:ss")
                                MsgBox "Werkblad is wachtwoord-vrij." _
                                & Chr(10) & "in: " & Chr(10) & Chr(10) & duur, vbInformation, "Kraker"
                            Exit Sub
                        End If
                    Next: Next: Next
                Next: Next: Next
            Next: Next: Next
        Next: Next: Next
    Next
End Sub

Even kopiƫren naar een Excel VBA module en draaien maar.

Het script verwijdert geen wachtwoorden die op de VBA code zijn gezet.

Voor verder Excel tips klik hier.