Top N prijzen van producten tonen

Geplaatst op

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))))

Tijd aftrekken met SUM, IF, ISNUMBER, MOD”

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.

Optie 1: Eerst de dagelijkse uren berekenen (Aanbevolen)

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.

Optie 2: Eén enkele formule voor het totaalaantal uren

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.)

Waarom dit werkt voor jouw gegevens

  • Ondersteunt nachtdiensten: Voor rij 3 (23:00 tot 07:00) ziet MOD dat de tijd over middernacht gaat. Het berekent correct 8 uur in plaats van een negatief getal.
  • Vermenigvuldigt met 24: Excel rekent tijd als een fractie van een dag. Door te vermenigvuldigen met 24 wordt bijvoorbeeld 0,333 dagen omgezet naar 8,0 decimale uren.
  • Negeert tekst of lege cellen: Als je “OFF” typt of een rij leeg laat, detecteert ISNUMBER dit. De IF‑functie maakt die rij dan 0 zodat de formule niet vastloopt.

De REST functie (tijd aftrekken)

Geplaatst op

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

Vensters Visual Basic Editor door elkaar

Geplaatst op

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.

© Deze oplossing komt van Erlandsen.

Data in een cel splitsen over meerdere cellen

Geplaatst op

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 gebruiken

Geplaatst op

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.

Het Range (bereik) object

Geplaatst op

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.

Elke N-th cel optellen

Geplaatst op

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.

Speciale tekens

Geplaatst op

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 



Tijd aftrekken en totaal berekenen

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)