N = een waarde. Bijvoorbeeld N = 5.
Je wilt de top 6 productprijzen laten zien.
Formules:
[B2] =GROOTSTE(A6:A22;B1)
[B3] =AANTAL.ALS(A6:A22;”>=”&B2)
[D6] =ALS(RIJEN(D$6:D6)>$B$3;””;RIJEN(D$6:D6))
[E6] =ALS(D6=””;””;GROOTSTE($A$6:$A$22;D6))
[F6] =ALS(D6=””;””;INDEX($B$6:$B$22;AGGREGAAT(15;6;(RIJ($A$6:$A$22)-RIJ($A$6)+1)/($A$6:$A$22=E6);AANTAL.ALS($E$6:E6;E6))))
Hier is de volledige vertaling naar helder, natuurlijk Nederlands:
In deze opzet bevat kolom B je begintijden, kolom C je eindtijden en elke rij staat voor één dag. Hier lees je hoe je SUM, IF, ISNUMBER en MOD gebruikt in jouw voorkeursindeling.
De eenvoudigste aanpak is om de uren per dag in kolom D te berekenen en deze onderaan op te tellen.
Formule voor cel D2 (rij 2): =IF(ISNUMBER(C2), MOD(C2-B2, 1) * 24, 0)
Formule voor cel D3 (rij 3 – nachtdienst, 23:00 – 07:00):=IF(ISNUMBER(C3), MOD(C3-B3, 1) * 24, 0)
Sleep deze formule omlaag in kolom D. Gebruik onderaan kolom D:=SUM(D2:D31)
om het totaal te berekenen.
Als je geen kolom met dagelijkse uren wilt en liever één formule onderaan je sheet gebruikt, neem dan deze matrixformule:
=SUM(IF(ISNUMBER(C2:C31), MOD(C2:C31-B2:B31, 1) * 24, 0))
(Opmerking: gebruik je Excel 2019 of ouder, druk dan op Ctrl + Shift + Enter na het invoeren.)
Zie ook “Tijd aftrekken met SUM, IF, ISNUMBER, MOD”
Bepaalt het restgetal dat overblijft nadat getal is gedeeld door deler. Het restgetal en deler zijn beide positief of negatief.
Syntaxis
REST(getal;deler)
getal is het getal waarvoor je het restgetal wilt bepalen.
deler is het getal waardoor je getal wilt delen.
De REST functie is handig als je tijden moet aftrekken (om het aantal gewerkte uren te berekenen) en de begintijd GROTER is dan de eindtijd. Je krijgt dan namelijk een negatief getal.
Stel in C2 eindtijd en in B2 begintijd. De formule is dan:
=REST(C2-B2;1)*24
Let op: Je moet de uitkomst met 24 vermenigvuldigen omdat het hier om tijden gaat.
Tussen de functie REST en de functie INTEGER bestaat de volgende relatie:
REST(n, d) = n – d * INTEGER(n / d)
Hoe werkt dat: n – d * INTEGER(n / d) ?
Stel n = -0,83333 en d = 1.
Berekeningsstappen:
-0,83333/1 = -0,83333
INTEGER (-0,83333) = -1
1 * -1 = -1
-0,83333 – -1 = 0,16667 (2x min wordt plus).
Dit is dus hetzelfde als:
=MOD(-0,83333;1) = 0,16667
En dat is wat de REST functie doet. Als de tijd negatief is wordt er 1 bij het eindresultaat opgeteld.
[D2]=REST(C2-B2;1)*24
[D7]=SOM(D2:D5)
Verkorte versie:
[C7]=SOMPRODUCT(REST(C2:C5-B2:B5;1)*24)
De celopmaak moet je bij de celeigenschappen op standaard of Getal zetten voor de juiste weergave. In Excel is 1 dag gelijk aan het getal 1 en het uur is 1/24 deel. Vandaar dat er met 24 vermenigvuldigd wordt
Soms staan alle venster van de VBE (Visual Basic Editor) kriskras door elkaar. Met geen mogelijkheid is er enige orde in aan te brengen. Deze truc van Erlandsen werkt perfect om dat te herstellen naar de oorspronkelijke waarden.
Hiervoor moet je wel effe in de Registry duiken en daarbij hoort een waarschuwing.
WAARSCHUWING: Indien je de Registry editor verkeerd gebruikt, kun je serieus in de problemen geraken omdat je besturingssysteem in de soep draait.
Na dit gezegd te hebben kunnen we verder.
-Sluit Excel en de VBE.
-Uitvoeren > > > RegEdit. In Windows 8.1 met de muis helemaal naar rechtsboven zodat de Charms-Balk tevoorschijn komt en bij het vergrootglas Regedit invoeren.
-Navigeer naar HKEY_CURRENT_USER/Software/Microsoft/VBA/7.0/Common
-Verwijder de waarde Dock.
-Excel maakt de volgende keer een nieuwe waarde aan.
–Regedit sluiten
-Open Excel en de VBE, gelukkig, de venster zijn weer op orde.
Probleem:
Gegevens in [ A2 ] in het volgende formaat:
Achternaam Voorna(a)m(en) Nummer
Bijvoorbeeld:
Smith John 12345678
Eindresultaat moet zijn:
[ B2 ] = Nummer
[ C2 ] = Initialen van Voornamen en vervolgens Achternaam.
Het eerste woord in [ A2 ] is altijd de Achternaam. Het laatste gegeven is altijd het Nummer. Alle woorden tussen Achternaam en Nummer zijn Voornamen en vormen de initialen in [ C2 ].
Bijvoorbeeld:
[ A2 ] = Smith John James 12346578
Eindresultaat:
[ B2 ]: 12345678
[ C2 ]: J J Smith
Formule: [ B2 ]:
=ZOEKEN(10^9;--RECHTS(A2;{1;2;3;4;5;6;7;8;9}))Formule: [ C2 ]:
=SPATIES.WISSEN(ALS.FOUT(DEEL(A2;VIND.ALLES("#";SUBSTITUEREN(SUBSTITUEREN(A2;" "&B2;"");"";
"#";1));2);"")&ALS.FOUT(DEEL(A2;VIND.ALLES("#";SUBSTITUEREN(SUBSTITUEREN(A2;" "&B2;"");"";
"#";2));2);"")&ALS.FOUT(DEEL(A2;VIND.ALLES("#";SUBSTITUEREN(SUBSTITUEREN(A2;" "&B2;"");"";
"#";3));2);"")&" "&LINKS(A2;VIND.ALLES(" ";A2)))Resultaat:
De methode Cells(Rij, Kolom) gebruiken
Een gemakkelijke manier om een cel op een werkblad aan te duiden is de Cells methode.
De syntax voor deze methode is:
Cells(Rijnummer, Kolomnummer)
of
Cells(Rijnummer, “KolomLetter”) Let op de aanhalingstekens.
Je kunt geen negatieve nummers of 0 gebruiken.
Cells verwijst naar alle cellen op een werkblad.
Bijvoorbeeld:
Cells(1, 1) Verwijst naar cel A1
Cells(2, 1) Verwijst naar cel A2
Cells(5, “F”) Verwijst naar cel F5
Cells(X, Y) Verwijst naar de cel in rij X en kolom Y, X en Y zijn variabelen die eerder in de code zijn ingesteld.
ActiveCell.Cells(1, 2) Verwijst naar een cel relatief ten opzichte van de actieve cel.
In dit geval, in dezelfde rij en één kolom naar rechts.
ActiveCell.Offset(2, 2) Verwijst naar een cel 1 rij naar beneden en 1 kolom naar rechts.
Dit kan verwarrend zijn want bij 2 denk je aan 2 rijen naar beneden. ActiveCell.Offset(1, 1) is dus gewoon de actieve cel.
Je kunt in de syntax ook combinaties maken zoals:
Cells(1, 1).Value = 9
Hier wordt de methode Cells gebruikt om de waarde van cel A1 op 9 in te stellen.
Cells(x, y) is een voorbeeld waarbij x en y variabelen voorstellen.
Het voorbeeld ActiveCell.Cells(1, 2) is een voorbeeld waarbij de ligging relatief is ten opzichte van een andere cel namelijk de actieve cel.
Relatieve verwijzingen kunnen bijzonder nuttig zijn.
Je kunt de methode Cells koppelen aan de naam van de werkmap of het werkblad zonder eerst die werkmap of het werkblad te selecteren
Workbooks(“Testdata.Xls”).Sheets(“New Data”).Cells(4, 1).Value = 0
stelt de waarde van cel (A4) in op 0 zonder eerst het werkblad te selecteren.
De reden waarom Cells een methode is en niet een object, is omdat het een verwijzing is naar een object.
Range(cel verwijzing, cel verwijzing) gebruiken om naar een gebied te verwijzen
De syntax Range(cel verwijzing, cel verwijzing), stelt je in staat om naar een bepaald gebied te verwijzen. Je kunt voor je verwijzing de zogenaamde A1 syntax gebruiken maar ook de syntax Cells(RijNr, KolomNr). Ook kun je bereiknamen, die naar een cel verwijzen, gebruiken. Tenslotte kun je variabelen gebruiken
De volgende voorbeelden verwijzen naar een gebied d.m.v. de methode Range. Merk op dat de komma NIET binnen de aanhalingstekens staat.
Range(“B4”, “D9”)
Verwijst naar het bereik B4:D9
Range(Cells(4, 2), Cells(9, 4))
Verwijst eveneens naar het bereik B4:D9. Deze manier werkt alleen op het actieve blad. Het volgende voorbeeld is meer flexibel
With Sheets(“Blad4”)
.Range(.Cells(4, 2), .Cells(9, 4))
End With
Het woord With zorgt er voor dat je code korter wordt. Je hoeft namelijk Sheets(“Blad4”) niet telkens te herhalen daar waar de punt staat bij .Range en .Cells
Range(ActiveCell, ActiveCell.Offset(4, 5)).Select
Gaat uit van de actieve cel en vervolgens 4 rijen naar beneden en 5 kolommen naar rechts
Range(ActiveCell, Cells(2, 5)).Select
Gaat uit van de actieve cel en vervolgens 2 rijen naar beneden en 5 kolommen naar rechts
Range(Cells(4, 5), “H5”).Select
Links boven is cel E4 en rechts onder is cel H5. Dus het gebied E4:H5 wordt geselecteerd
Range(“A1”, “LaatsteCel”).Select
Selecteert het gebied van A1 tot en met de cel met de naam “LaatsteCel”
Range(“E2”, Cells(X, Y)).Select
Selecteert het gebied van E2 tot en met de cel met de variabelen X en Y waarbij X en Y getallen voorstellen.
Range(MijnCel, Cells(4, 4)).Select
Selecteert het gebied van MijnCel tot en met cel D4. Hierbij is MijnCel een object variabele. Dit is een verwijzing naar een cel die als volgt tot stand komt.
Dim MijnCel As Range
Set MijnCel = Range(“A1”)
Dus in dit voorbeeld wordt het gebied A1:D4 geselecteerd. Let op ! ! ! het blad MOET actief zijn.
Range(MijnCel, JouwCel).Select
Selecteert het gebied van MijnCel tot en met cel JouwCel. Hierbij zijn MijnCel en JouwCel object variabelen.
Dim MijnCel As Range, JouwCel As Range
Set MijnCel = Range(“A1”)
Set JouwCel = Range(“E10”)
Dus in dit voorbeeld wordt het gebied A1:E10 geselecteerd. Let op ! ! ! het blad MOET actief zijn.
Belangrijk: Verwijzingen moeten bijna altijd worden voorzien van een verwijzing naar het juiste blad. Hier weer een voorbeeld:
Sub test()
Dim BladVerw As Worksheet
Set BladVerw = Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”)
With BladVerw
.Range(.Cells(1, 1), .Cells(5, 5)).Copy
End With
End Sub
Nog een voorbeeld. Het zal langzamerhand voor zichzelf spreken.
Sub test()
Dim topCell, bottomcell, a, b, x, y
a = 1
b = 1
x = 5
y = 5
Set topCell = Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”).Cells(a, b)
Set bottomcell = Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”).Cells(x, y)
Range(topCell, bottomcell).Copy
end sub
Je hoeft niet altijd het juiste werkblad te activeren. Eigenlijk verdient dit de voorkeur want activeren en vervolgens selecteren zijn extra stappen en maken de uitvoering van de code langzaam.
Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”).Range(“1:2”).Copy
Deze code kopieert rij 1 en 2 naar het klembord zonder de map en het werkblad te activeren en het bereik te selecteren.
Nog een voorbeeld. Als je deze code uitvoert terwijl een ander blad actief is dan het blad “salarissen”, krijg je een foutmelding
Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”).Range(Cells(1, 1), Cells(3, 3)).Value = 4
Dit werkt wel:
With Workbooks(“medewerkers.xlsm”).Sheets(“salarissen”)
.Range(.Cells(1, 1), .Cells(3, 3)).Value = 4
End With
De Range methode kan voorzien worden met een celverwijzing middels ActiveCell. Indien voorzien met deze celverwijzing, is de Range verwijzing relatief ten opzichte van de ActiveCell. Als E4 de ActiveCell is, gebeurt bij de uitvoering van onderstaande code het volgende:
ActiveCell.Range(“A1:C1”).Clear
Het bereik A1:C1 wordt NIET gewist, maar WEL het bereik vanaf de ActiveCell twee cellen naar rechts. Dus bereik E4:G4
Range(ActiveCell.Offset(0, 6), ActiveCell.Offset(0, 10)).Select
Het bereik 6 cellen naar rechts tot en met 10 cellen naar rechts ten opzichte van de ActiveCell wordt geselecteerd. Indien de ActiveCell A1 is, wordt dus het bereik G1:K1 geselecteerd.
Het is niet ongebruikelijk dat je elke N-th cel wil optellen. Bijvoorbeeld, de derde, zesde, negende etc. Excel heeft daarvoor geen speciale functie die dat kan. Maar met de functie REST heb je wel een hulpmiddel. Kijk maar naar het voorbeeld.
In [E4] komt de volgende matrixformule:
=SOM(ALS(REST($A$1:$A$21;$F$2)=0;$B$1:$B$21;0))
Let op: Invoeren met toetscombinatie Ctrl+Shift+Enter
Verdere opzet van het voorbeeld
A1:A21, getallen van 1 tot en met 21
B1:B21, willekeurige getallen
F2, een keuzelijst met de getallen 1 tot en met 9.
Het maken van de keuzelijst:
Zet ergens op je werkblad de getallen 1 tot en met 9. Ga naar Data | Gegevens validatie en kies voor lijst en zet bij Bron het bereik van je getallen 1 tot en met 9 neer.
Om speciale tekens weer te geven of in cellen te plaatsen met VBA kun je gebruik maken van de ChrW functie. Kies in Excel Insert Symbol en kies bij Lettertype: Arial. Laten we het Euro-teken kiezen. In het venster rechtsonder genaamd Tekencode staat de Hex-code namelijk 20AC
Deze code kun je gebruiken om in Cel A1 het €-teken te krijgen:
Range(“A1”) = ChrW(&H20AC)
Let op ! ! ! De Hex-code wordt vooraf gegaan door &H
Meer experimenteren? Gebruik het Venster Direct, te bereiken via de toetscombinatie:
Alt+F11
Ctrl+G
Voer het volgende in en druk op Enter:
debug.print ChrW(&H20AC)
Resultaat: €
Je kunt ook dit doen. Voer in en dan Enter:
?&H20AC
Resultaat: €
Het vraagteken ? doet hetzelfde als debug.print.
Je kunt die Hex-code veranderen in een getal. Voer in en dan Enter:
S = “&H20AC” : ?Val(S)
Resultaat: 8364
Of van getal naar Hex-code. Voer in en dan Enter:
?Hex(8364)
Resultaat: 20AC
Tekens die vaak gevraagd worden zijn die driehoekjes die aangeven of iets is gestegen of gedaald.
Voorbeeld:
ActiveCell.Value = ChrW(&H25BC) & " Gedaald"
ActiveCell.Font.Color = vbRed
ActiveCell.Offset(1, 0).Value = ChrW(&H25B2) & " Gestegen"
ActiveCell.Offset(1, 0).Font.Color = vbBlue 
We hebben al eerder gezien hoe je begintijden van eindtijden kunt aftrekken d.m.v. één formule. Dat kan handig zijn als je het aantal gewerkte uren moet berekenen. Als er echter tekst tussen de gegevens staat, werkt dat niet en krijg je een foutmelding. Hier een oplossing.
Formule komt in D9.
De korte manier:
[D9]=SUM(IF(ISNUMBER(C2:C8-B2:B8);MOD(C2:C8-B2:B8;1)*24;0))
Invoeren met Ctrl+Shift+Enter
De langere werkwijze is door in D2:D8 een formule te zetten en de uitkomsten op te tellen.
[D2:D8] =IF(ISNUMBER(B2:C2);MOD(C2-B2;1)*24;””)
Invoeren met Ctrl+Shift+Enter
en dan in [D9] =SUM(D2:D8)