Home Blog Pagina 3

Flash Fill in Excel – Scheiden of samenvoegen van data

0

Met de Flash Fill optie kun je gestructureerde data scheiden of samenvoegen. Je kunt bijvoorbeeld met gemak in een postcodelijst de cijfers en letters scheiden of op basis van van voor- en achternaam juist samenvoegen tot een email adres. Hieronder zal genoemde voorbeelden laten zien. Flash Fill is een makkelijk alternatief voor “Tekst naar kolommen”.

Flash Fill – Data scheiden

1. Zet in kolom A de gestructureerde data. Met gestructureerd wordt hier bedoeld dat de data dezelfde opbouw heeft.

2. Voordat we de actie gaan uitvoeren moeten we Excel wel eerst laten zien wat wij verwachten. Zet daarom in cel B1 de postcode “1011”.

Flash Fill - Data scheiden

3. Ga nu in het Lint (menubalk) naar tabblad gegevens. In de groep “Hulpmiddelen voor gegevens” zie je Flash fill staan. Zet je cursor in kolom B naast een met data gevulde cel in kolom A en klik op Flash fill.

Flash Fill - Data scheiden

Resultaat:

Flash Fill - Data scheiden

Let op: Excel voert geen formules in. Als je na de actie nog data aanpast in kolom A zal deze niet automatisch mee veranderen in kolom B.

Flash FIll: Data samenvoegen

Het tweede voorbeeld dat ik noemde was het samenvoegen van voor en achternaam om een email adres te maken.

1. Zet in kolom A een aantal achternamen en in kolom B een aantal voornamen.

2. Voordat we de actie gaan uitvoeren moeten we Excel wel eerst laten zien wat wij verwachten. Zet daarom in cel A1 “Smit” en in cel B1 “Piet”. Vul het lijstje verder aan.

Flash FIll: Data samenvoegen

3. Ga nu in het Lint (menubalk) naar tabblad gegevens. In de groep “Hulpmiddelen voor gegevens” zie je Flash fill staan. Zet je cursor in kolom C naast een met data gevulde cel in kolom A en B en klik op Flash fill.

Flash FIll: Data samenvoegen

Resultaat:

Flash FIll: Data samenvoegen

Let op: Excel voert geen formules in. Als je na de actie nog data aanpast in kolom A of B zal deze niet automatisch mee veranderen in kolom C.

8 voorbeelden van automatisch aanvullen in Excel

2

Automatisch aanvullen is een handigheidje in Excel waarmee je automatisch logische lijstjes mee kunt maken. Hieronder verschillende voorbeelden waarop je dit kunt toepassen.

1. Zet in cel A1 de waarde “10” en in cel A2 de waarde “20”.

8 voorbeelden van automatisch aanvullen in Excel

2. Selecteer cel A1 en cel A2 en sleep dan het groene selectie kader naar beneden aan het vierkantje rechts onder.

8 voorbeelden van automatisch aanvullen in Excel

Opmerking: Automatisch aanvullen vult de lijst aan met een logisch patroon gebaseerd op de twee geselecteerde cellen (stap van 10 van cel A1 naar A2).

3. Zet in cel A1 de waarde “Jan” (januari).

8 voorbeelden van automatisch aanvullen in Excel

4. Selecteer cel A1 en sleep dan het groene selectie kader naar beneden aan het vierkantje rechts onder. Automatisch aanvullen vult de lijst aan met alle maanden.

8 voorbeelden van automatisch aanvullen in Excel

5. Zet in cel A1 de waarde “Artikel 1”.

8 voorbeelden van automatisch aanvullen in Excel

6. Selecteer cel A1 en sleep dan het groene selectie kader naar beneden aan het vierkantje rechts onder. Automatisch aanvullen vult de lijst aan met de productnamen met oplopende nummering.

8 voorbeelden van automatisch aanvullen in Excel

7. Zet in cel A1 de waarde “Maandag”.

8 voorbeelden van automatisch aanvullen in Excel

8. Selecteer cel A1 en sleep dan het groene selectie kader naar beneden aan het vierkantje rechts onder. Automatisch aanvullen vult de lijst aan met de opvolgende weekdagen.

8 voorbeelden van automatisch aanvullen in Excel

Datum en tijd automatisch aanvullen

9. Zet in cel A1 de datum “11-01-2019”.

8 voorbeelden van automatisch aanvullen in Excel

10. Selecteer cel A1 en sleep dan het groene selectie kader naar beneden aan het vierkantje rechts onder. Automatisch aanvullen vult de lijst aan met de opvolgende datums.

8 voorbeelden van automatisch aanvullen in Excel

11. In plaats van de dagen kun je ook kiezen voor alleen werkdagen, maanden of jaren op te laten lopen. Klik na het slepen op het optie menu, zie hieronder:

8 voorbeelden van automatisch aanvullen in Excel

Opmerking: zie ook de opties om alleen de opmaak door te voeren en om een ​​reeks door te voeren zonder opmaak.

12. Voer in cel A1 de waarde “13-02-2020” in en in cel A2 de waarde “20-02-2020”.

8 voorbeelden van automatisch aanvullen in Excel

13. Selecteer cel A1 en A2 en sleep dan het groene selectie kader naar beneden aan het vierkantje rechts onder. Automatisch aanvullen vult de lijst aan met de opvolgende datums volgens het patroon, elke cel +7 dagen.

8 voorbeelden van automatisch aanvullen in Excel

14. Naast verticaal werkt deze methode ook horizontaal. Zet in cel A1 de waarde “6:00”.

8 voorbeelden van automatisch aanvullen in Excel

15. Selecteer cel A1 en sleep nu de het groene selectie kader naar rechts aan het vierkantje rechts onder. Automatisch aanvullen vult nu in elke cel een tijdstip in.

8 voorbeelden van automatisch aanvullen in Excel

Een ander voorbeeld van een automatisch lijstje dat je kunt maken is de Rij van Fibonacci. Hierover lees je meer in het artikel Rij van Fibonacci in Excel.

Verbergen van kolommen en rijen

0

Soms kan het handig zijn om kolommen te verbergen in een werkblad.

Kolom verbergen

Kolommen verbergen die je op de volgende manier:

1. Selecteer de kolom die je wilt verbergen.

Excel Verbergen van kolommen en rijen

2. Rechts klik in de geselecteerde kolom en klik op Verbergen.

Excel Verbergen van kolommen en rijen

3. Resulaat:

Excel Verbergen van kolommen en rijen

Om een rij ter verbergen herhaal je dezelfde stappen. Selecteer een rij en rechtsklik op de geselecteerde rij en klik op verbergen.

Kolom zichtbaar maken na kolom verbergen

Een kolom zichtbaar maken doe je op de volgende manier:

1. Selecteer de kolommen links en rechts van de verborgen kolom.

Excel Verbergen van kolommen en rijen

2. Rechtsklik in de geselecteerde kolommen en klik op Zichtbaar maken.

Excel Verbergen van kolommen en rijen

Resultaat:

Excel Verbergen van kolommen en rijen

Om een verborgen rij zichtbaar te maken selecteer je de rij boven tot en met de rij onder de verborgen rij en rechtsklik je op de geselecteerde rijen. klik vervolgens op Zichtbaar maken.

Autoaanpassen kolombreedte en rijhoogte Excel

0

Waarschijnlijk ben je bekend met het aanpassen van de kolombreedte en/of rijhoogte in Excel. Je kunt de kolombreedte automatisch aanpassen aan de breedste inhoud van een cel in de kolom.

Kolombreedte autoaanpassen

Standaard is de kolombreedte ingesteld op 8.43 punten.

1. Je kunt de breedte van de kolom aanpassen door de lijn rechts van de kolom aan te klikken en te slepen.

Autoaanpassen kolombreedte en rijhoogte  Excel

2. Om de breedte automatisch aan te passen de breedste cel inhoud in de kolom dubbel klik je twee maal op lijn rechts van de betreffende kolom kop.

Autoaanpassen kolombreedte en rijhoogte  Excel

3. Om de breedte van meerdere kolommen automatisch aan te passen selecteer je de eerste kolom en sleep je door tot de laatste kolom die je wilt aanpassen.

Autoaanpassen kolombreedte en rijhoogte  Excel

Alternatief: Je kunt ook de CTRL ingedrukt houden en de kolom koppen aanklikken.

4. Vervolgens dubbelklik je op de rechter lijn van één van de geselecteerde kolommen.

Autoaanpassen kolombreedte en rijhoogte  Excel

5. Om de breedte van alle kolommen aan te passen kun je deze makkelijk te selecteren door op de Selecteer alles knop te klikken.

Autoaanpassen kolombreedte en rijhoogte  Excel

6. Nu kun je alle kolommen aanpassen door de de rechter lijn van een kolom te slepen.

Autoaanpassen kolombreedte en rijhoogte  Excel

Autoaanpassen rijhoogte

De beschrijving hierboven is exact hetzelfde toe te passen op de rijen. In plaats van op de rechter lijn te klikken van de kolom kop klik je nu op de onder lijn van de rij kop. Er is echter nog een methode om de breedte of hoogte aan te passen.

1. Selecteer meerdere rijen door te klikken en te slepen of gebruik de CTRL en klik op de gewenste rijen.

Autoaanpassen kolombreedte en rijhoogte  Excel

2. Klik in het lint (menu balk) in de groep cellen op Opmaak.

Autoaanpassen kolombreedte en rijhoogte  Excel

3. Klik op Rijhoogte Autoaanpassen.

Autoaanpassen kolombreedte en rijhoogte  Excel

4. Resultaat:

Autoaanpassen kolombreedte en rijhoogte  Excel

Opmerking: Deze methode kun je ook gebruiken voor de kolommen, zie
Kolombreedte Autoaanpassen in de screenshot van stap 3.

Meerdere resultaten weergeven zonder verticaal zoeken

1

Verticaal zoeken is een functie waarmee je data kunt ophalen uit een specifieke kolom in een tabel. Het nadeel is dat de functie is bedoeld om 1 resultaat weer te geven. Soms kan het zijn dat je meerdere overeenkomsten in de tabel hebt die je wilt tonen. In deze uitleg gaan we meerdere resultaten  in één lijst verwerken. 

Hoewel ik in de titel van dit bericht de term verticaal zoeken gebruik komt de hele functie niet voor in dit artikel. De functie verticaal zoeken is genoemd omdat het resultaat lijkt op verticaal zoeken. Hoe de functie verticaal zoeken exact werkt kun je lezen in het artikel Excel verticaal zoeken functie

Adresboek

We beginnen met een simpele adresboek tabel. We zien in de tabel enkele gelijkenissen zoals 3 Marieke’s en 2 keer de achternaam Peters. Als we nu VERT.ZOEKEN op Marieke krijgen we alleen de gegevens van rij 2. Er is immers een resultaat en dus is de functie voltooid. 

Meerdere resultaten weergeven zonder verticaal zoeken

Nu willen we juist een lijst krijgen van alle Marieke’s als ik zoek op de voornaam Marieke. In de volgende afbeelding van het tabblad “Zoeken” zien we het eindresultaat. We zoeken op voornaam Marieke en krijgen 3 records. Op de achternaam Peters vinden we 2 records.

Meerdere resultaten weergeven zonder verticaal zoeken

Werking van de zoek functie voor meerdere resultaten

Buiten de namen, telefoonnummers en email adressen zien we op het tabblad Zoeken nog een hulp kolom met getallen. De getallen corresponderen met de rij nummers van de persoon in het adresboek. Dit doen we middels de volgende formule:

=VERGELIJKEN($B$4;VERSCHUIVING(TELEFOONBOEK!$B$1;F3;0;1000;1);0)+F3

Zodra we het rijnummer weten kunnen we middels de volgende INDEX formules de overige gegevens uit deze ophalen:

=ALS(ISFOUT(INDEX(TELEFOONBOEK!C:C;$F4));"";INDEX(TELEFOONBOEK!C:C;$F4)) // Achternaam

=ALS(ISFOUT(INDEX(TELEFOONBOEK!E:E;$F4));"";INDEX(TELEFOONBOEK!E:E;$F4)) // Telefoonnnummer

=ALS(ISFOUT(INDEX(TELEFOONBOEK!D:D;$F4));"";INDEX(TELEFOONBOEK!D:D;$F4)) // Email adres

Op dezelfde wijze werkt ook de lijst van de achternamen. 

Download voorbeeld bestand

  Telefoonlijst-verticaal-zoeken-meerdere-waarden.xlsx – Bestandsgrootte 19,9 Kb  

Excel ALS formule: ALS cel gelijk is aan

Excel formule: ALS cel gelijk is aan

Algemene formule

= ALS (A1="rood";waar;onwaar)

De functie uitgelegd

De ALS functie kun je gebruiken als je iets specifieks wilt doen als een cel aan een bepaalde waarde voldoet. Je kunt de ALS functie toepassen als je op een specifieke waarde wilt testen. Voor de waarde WAAR kun je een waarde bepalen en optioneel voor de waarde ONWAAR.

Als de waarde “rood” is, geef de waarde X

In het voorbeeld hieronder willen we simpel weg de rijen markeren of vlaggen waarvan de kleur waarde rood is. Met andere woorden, we controleren cellen in een bepaalde kolom en geven een vastgestelde waarde terug als de kleur rood is.

In cel D6 gebruiken we de volgende formule:

ALS(B6="red";"x";"")

In deze formule is de logische test:

B6 = "rood"

Het resultaat is WAAR als de waarde rood is en ONWAAR als de waarde anders is dan rood.

Aangezien we de cellen met de waarde rood willen markeren of vlaggen hoeven we alleen een waarde als de logische test WAAR is. We voegen een “x” toe aan kolom D als de waarde “rood” is. Als de kleur niet “rood” is (of leeg etc), voegen we een lege waarde toe (“”).

Het resultaat is een “x” als de kleur “rood” is en niets bij elke andere waarde.

Opmerking: Als we geen lege waarde (“”) invullen bij onwaar, de zal de waarde “ONWAAR” weergegeven worden.

Prijs verhogen als de kleur rood is

We kunnen deze formule nog wat moeilijker en uitgebreider maken. We gaan de prijs van de rode artikelen verhogen met 15%.

In dat geval kun je de volgende formule plaatsen in kolom E om de nieuwe prijs te berekenen.

=ALS(B6="rood";C6*1,15;C6)
Excel formule: ALS cel gelijk is aan

De logische test is hetzelfde als de vorige (B6=”rood”). Als het resultaat WAAR is vermenigvuldigen we de originele prijs met 1.15 (verhoging van 15%). Als het resultaat ONWAAR is, dan tonen we gewoon de originele prijs.

Excel ALS formule: ALS cel is X of Y en Z

Excel formule ALS cel is X of Y en Z

ALS cel is X of Y en Z

=ALS(EN(OF(A1= x;A1=y);B1=Z);"Ja";"Nee")

De ALS functie uitgelegd

Je kunt de logische functie combineren met OF en EN functie in de ALS functie.

Als product is kast of cd en het aantal is groter dan 10

In het voorbeeld filteren we regels waarvan het product kast of cd is en het aantal groter is dan 10.

De formule gebruikt in D5 is:

=ALS(EN(OF(B5="Kast";B5="Cd");C5>10);"x";"")

Hoe deze ALS formule werkt

In deze ALS formule is de logische test:

EN(OF(B5="Kast";B5="Cd");C5>10)

Let op dat we de ALS functie gebruiken waarbij de OF functie de eerste logische test is en C5>10 de tweede logische test.

Dit gedeelte van de code geeft alleen WAAR als het product in B5 een kast of een cd is en het aantal in C5 groter is dan 10.

De ALS functie geeft “x” als het resultaat WAAR is en “” (niets)  als het resultaat onwaar is.

Opmerking: Als we de lege tekenreeks niet toevoegen als ONWAAR, geeft de formule ONWAAR als het product kast is.

Windows mappen maken met excel

8

Onlangs moest ik een map maken in windows voor een digitaal dossier. De windows mappen structuur was telkens hetzelfde. 1 hoofdmap met een aantal sub-mappen. De sub-mappen hadden telkens dezelfde naam, alleen de hoofdmap had een afwijkende naam.

Windows mappen maken met excel

Om alles handmatig te doen was onbegonnen werk met zo’n 20.000 records. De informatie kon ik in excel omzetten dus uiteindelijk startte ik de zoektocht naar een macro die daar mappen van zou kunnen maken. En die vond ik!

Data in excel bestand klaar zetten

In het excel bestand moet eerst de data juist gezetten worden. In afbeelding 1 staat hoe ik mijn structuur moest hebben.

Windows mappen maken met excel

Na het uitvoeren van de macro had ik de volgende mappen (afbeelding 2):

Windows mappen maken met excel

Zoals je ziet heb ik hier zelfs nog een sub sub map gemaakt. Hierin kun je doorgaan zolang je wilt. Met deze methode heb ik uiteindelijk een klanten dossier gemaakt en een digitale agenda voor opslaan van gegevens belangrijk voor die datum. Je kunt het bestand uiteraard ook gebruiken voor bijvoorbeeld muziek catalogus, foto’s en documenten.

Zodra je de macro start zal eerst worden gevraagd waar je de mappen wilt opslaan.

De macro’s voor windows mappen

De gehele macro bestaat uit 3 delen.

Gedeelte 1

Sub MappenMakenMetExcel()

 baseFolder = BrowseForFolder
 If (baseFolder = False) Then
 Exit Sub
 End If
 Set fs = CreateObject("Scripting.FileSystemObject")
 For iRow = 1 To 6500
 pathToCreate = baseFolder
 leafFound = False
 For iColumn = 1 To 6500
 currValue = Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, iColumn).Value
 If (currValue = "" And leafFound) Then
 Exit For
 ElseIf (currValue = "") Then
 parentFolder = FindParentFolder(iRow, iColumn)
 If (parentFolder = False) Then
 Exit For
 Else
 pathToCreate = pathToCreate & "\" & parentFolder
 If Not (fs.FolderExists(pathToCreate)) Then
 fs.CreateFolder (pathToCreate)
 End If
 End If
 Else
 leafFound = True
 pathToCreate = pathToCreate & "\" & currValue
 If Not (fs.FolderExists(pathToCreate)) Then
 fs.CreateFolder (pathToCreate)
 End If
 End If
 Next
 If (leafFound = False) Then
 Exit For
 End If
 Next
End Sub

Gedeelte 2

Function FindParentFolder(row, column)
 For iRow = row To 0 Step -1
 currValue = Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, column).Value
 If (currValue <> "") Then
 FindParentFolder = CStr(currValue)
 Exit Function
 ElseIf (column <> 1) Then
 leftValue = Worksheets(ActiveCell.Worksheet.Name).Cells(iRow, column - 1).Value
 If (leftValue <> "") Then
 FindParentFolder = False
 Exit Function
 End If
 End If
 Next
End Function

Gedeelte 3

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
 'Function purpose: To Browser for a user selected folder.
 'If the "OpenAt" path is provided, open the browser at that directory
 'NOTE: If invalid, it will open at the Desktop level

Dim ShellApp As Object

'Create a file browser window at the default folder
 Set ShellApp = CreateObject("Shell.Application"). _
 BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
 'Set the folder to that selected. (On error in case cancelled)
 On Error Resume Next
 BrowseForFolder = ShellApp.self.Path
 On Error GoTo 0

'Destroy the Shell Application
 Set ShellApp = Nothing

'Check for invalid or non-entries and send to the Invalid error
 'handler if found
 'Valid selections can begin L: (where L is a letter) or
 '\\ (as in \\servername\sharename. All others are invalid
 Select Case Mid(BrowseForFolder, 2, 1)
 Case Is = ":"
 If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
 Case Is = "\"
 If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
 Case Else
 GoTo Invalid
 End Select

Exit Function

Invalid:
 'If it was determined that the selection was invalid, set to False
 BrowseForFolder = False

End Function

Yahtzee scoreblok in Excel

0
Yahtzee scoreblok in Excel

Bijna iedereen kent wel het gezelschapsspel Yahtzee. In 3 worpen met 5 dobbelstenen moet je of zo hoog mogelijke aantal dezelfde ogen gooien van één soort of een combinatie zoals Three of a kind, Carre, Full House of kleine of grote straat. Uiteraard mag de Yahtzee daarbij niet ontbreken, dat is 5 maal dezelfde aantal ogen. Er zijn vele varianten van het spelregels.

Yahtzee scoreblok

In deze eerste tutorial in de Excel-lent serie gaan we een Yahtzee scoreblok maken. Dit Yahtzee scoreblok telt automatisch de score op, laat zien hoeveel speelbeurten je hebt gegooid en bij de bovenste helft toont het hoeveel punten je te kort komt. Uiteindelijk kun je het formulier resetten met behulp van knop gekoppeld aan een macro. Na het invullen van de laatste score wordt ook je totaal score van het hele scoreblok getoond.

Stap 1: Bereik speelbeurten vaststellen met functie AANTALARG()

Als u het bron bestand opent ziet u de basis van het scoreblok en een reset knop. In cel C2 gaan we vermelden hoeveel beurten we hebben gegooid. Dat doen we door middel van het aantallen cellen te tellen dat is ingevuld met een getal. We gebruiken hiervoor de formule =AANTALARG(waarde1;[waarde2];…). Met deze formule tellen binnen het bereik dat we opgeven het aantal cellen met een waarde. Als er in een cel binnen dit bereik een waarde is ingevuld telt hij deze als 1. Is er niets ingevuld dan is de waarde 0.

Waarde1 in de formule is verplicht. Je kunt meerdere waarden invullen gescheiden door een ; teken. Uiteindelijk sluit je af met een haakje sluiten ). Een bereik kun je ook definiëren door van cel A1 tot en met cel A20. Je hoeft niet alle cellen in te vullen, je gebruikt dan het : teken. In dit voorbeeld is dat =AANTALARG(A1:A20).

Wij gaan tellen in twee bereiken want we hebben een scorelijst boven en onder. Boven is vanaf cel D7 tot en met I12 en onder is van D16 tot en met I22. Deze twee bereiken moeten we scheiden door een ; teken. Onze formule zal worden: =AANTALARG(D7:I12;D16:I22).

Selecteer cel C2, kopieer deze formule in de formulebalk en druk enter.

Yahtzee scoreblok in Excel

Stap 1 is daarbij afgerond. Elke score die we invullen zal worden opgeteld en kunnen we direct zien hoeveel speelbeurten we hebben gehad. Bij de volgende stap gaan we de de scores bij elkaar optellen aan de van de functie SOM().


Stap 2: Optellen score met de functie SOM() en ALS().

Yahtzee scoreblok in Excel

In stap 2 gaan we alles scores bij elkaar op tellen. Hiervoor gebruiken we de functie =SOM(getal1;[getal2];…).
Om de formule direct te controleren is het handig om alvast een fictieve score in te vullen. Zet in de bovenste helft de maximale score die te behalen is achtereenvolgend 3, 6, 9, 12, 15 en 18. We hebben deze nodig om bonus te berekenen.

2.1 SOM() functie sub-totaal bovenste helft

De SOM() functie werkt hetzelfde als de AANTAL() functie. Tussen de haakjes zetten we het bereik van de getallen die opgeteld moeten worden gescheiden door een ; teken of een : teken voor een reeks. We gaan in cel D13 alle getallen optellen van cel D7 tot en met cel D12. De formule voor het sub-totaal is: =SOM(D7:D12).

Selecteer cel D13, kopieer =SOM(D7:D12) in de formulebalk en druk enter

2.2 ALS() functie om de bonus te bepalen

Om het totaal van de boven helft te bepalen moeten we eerst weten of we recht hebben op de bonus. Om de bonus te ontvangen moeten we 63 punten hebben of meer. Deze punten behaal je als je alle getallen minimaal 3 keer hebt gegooid. We moeten nu dus een vergelijking in cel D14 gaan maken die vergelijkt dat als we 63 punten of meer hebben de bonus ontvangen. Indien we niet aan die voorwaarde voldoen dan krijgen we 0 punten bonus.

Vergelijken doen we met de functie =ALS(). De formule die we gaan maken is:
Als de waarde in cel D13 63 of groter is dan moet er 35 weergegeven worden, anders 0.
De formule is: =ALS(D13>=63;35;0)

  • De cel waar het om gaat is D13.
  • De vergelijking is gelijk of groter dan >=
  • het getal 63
  • Achter de eerste ; teken de waarde als de vergelijking waar is
  • Achter de tweede ; de waarde als de vergelijking niet waar is

Selecteer cel D14, kopieer =ALS(D13>=63;35;0) in de formulebalk en druk enter

2.3 SOM() functie totaal bovenste helft

Nu we het sub-totaal en de bonus punten hebben hebben bepaald kunnen de totaal score voor de bovenhelft bepalen. Dit is vrij gemakkelijk door de functie SOM() te gebruiken en de cellen D13 en D14 bij elkaar op te tellen. De formule voor het totaal is =SOM(D13;D14).

Selecteer cel D15, kopieer =SOM(D13;D14) in de formulebalk en druk enter

2.4 SOM() functie onderste helft

Om het totaal van de onderste helft op te tellen kunnen we weer de SOM() functie gebruiken. We gaan in cel D23 alle getallen optellen van cel D16 tot en met cel D22. De formule voor het sub-totaal is: =SOM(D16:D22).

Selecteer cel D22, kopieer =SOM(D16;D22) in de formulebalk en druk enter

2.5 D24 is hetzelfde als cel D15

Bij stap 2.3 hebben we al de totaal score van de bovenste helft berekend. We gaan deze score nu 1 op 1 overnemen. Dat doen we door in cel D24 de volgende formule te zetten: =D15.

Selecteer cel D24, kopieer =D15 in de formulebalk en druk enter

2.6 SOM() functie totaal score spel 1

Nu we alle scores hebben kunnen we de totaal score optellen van heel spel 1. De totaal score komt in cel D24. We gebruiken hiervoor wederom de SOM() functie. We tellen hiervoor cel D23 en D24 bij elkaar op. De formule voor de totaal score is: =SOM(D23:D24).

Selecteer cel D24, kopieer =SOM(D23:D24) in de formulebalk en druk enter

We hebben het optellen van de scores voor spel 1 voltooid. Hiervoor hebben we met name de SOM() functie en ALS() gebruikt. De laatste praktijk oefening van stap 2 is om deze formule’s toe te passen op spel 2 tot en met 6.

TIP: U kunt formule’s kopiëren door de cel met de formule te kopiëren naar de naastliggende cel. Selecteer cel D13 en kopieer deze (CTRL + C). Ga naar cel E13 en doe plakken (CTRL + V).

Als u alle stappen heeft herhaald voor spel 2 tot en met 6 kunt u door naar stap 3.


Stap 3: Voorwaardelijke opmaak bij bonus

Het behalen van de hoogste score is mede afhankelijk van of je de bonus hebt behaald. Om de bonus te behalen moet je van de bovenste helft alles minimaal drie keer gooien. 3 maal de enen, 3 maal de tweeën, 3 maal de drieën enzovoorts. Om je niet te vergissen bij het spelen bij gebruik van het hele scoreblok schrijf je boven het betreffende spel de + of de – score.

Bijvoorbeeld, in plaats van 9 punten bij de drieën heb je maar 6 punten gescoord. Dat betekent een min score van 3 punten. Je schrijft dan boven het betreffende spel -3. Je moet bij de 4, 5 of 6 minimaal 4 maal gelijke dobbelstenen gooien om de score weer in de + te krijgen. Gooi je vervolgens vier maal de 4, scoor je 16 punten. Dat is 4 punten meer dan nodig voor de vieren. Als je die in hetzelfde spel noteert heb je eerst een min score 3, tel daar 4 extra bij op en je totaal score is +1.

3.1 ALS() en ISLEEG() formule

Met behulp van het samenvoegen van formules gaan we de + of – score berekenen. We controleren eerst of de betreffende cel leeg is. Daarvoor gebruiken de we de formule ISLEEG(). Die gaan we uiteindelijk combineren met de formule ALS(). We willen namelijk dat als de cel nog leeg is dat er niets gebeurd. Laten we beginnen bij de enen van spel 1.

De +- score zetten we in cel D6. We gaan controleren of er iets is ingevuld. Dat doen we door de formule ISLEEG(). Tussen de haakjes zetten we de cel die we willen controleren. In ons geval is dat cel D7. De formule zal worden =ISLEEG(D7).

De volgende stap is om aan te geven wat te doen als er wel of niet een waarde is. Dat doen we met de ALS() functie welke we in stap 2.2 al hebben uitgelegd. Als de cel D7 leeg is dan is de waarde 0. Als er een waarde is dan dan trekken we de minimale score van de enen van die waarde af namelijk 3 punten. Bij de enen moeten we namelijk minimaal 3 punten scoren om de bonus te behalen. Scoren we 2 punten zal de som 2-3 worden en is de uitkomst -1. Bij een score van 4 punten is de som 4-3 en is de uitkomst +1. De formule zal zijn: =ALS(ISLEEG(D7);0;D7-3).

  • Als cel D7 leeg is
  • Achter de eerste ; teken de waarde 0
  • Achter de tweede ; teken de som D7-3

We hebben nu een formule om te controleren hoeveel + of – score we hebben op de enen. Deze moeten we herhalen voor elk getal in de bovenste helft voor spel 1. Het enige wat we moeten aanpassen in de formule is de cel waar het om gaat en de te behalen score per getal. Hierbij de formule’s:

Enen: =ALS(ISLEEG(D7);0;D7-3)
Tweeën: =ALS(ISLEEG(D8);0;D8-6)
Drieën =ALS(ISLEEG(D9);0;D9-9)
Vieren =ALS(ISLEEG(D10);0;D10-12)
Vijfen =ALS(ISLEEG(D11);0;D11-15)
Zessen =ALS(ISLEEG(D12);0;D12-18)

Nu we alle formule’s van alle getallen hebben moeten we deze bij elkaar gaan optellen. De formule voor het optellen is simpel, we zetten ze allemaal achter elkaar zonder het = teken maar met een + teken.

ALS(ISLEEG(D7);0;D7-3)+ALS(ISLEEG(D8);0;D8-6)+ALS(ISLEEG(D9);0;D9-9)+ALS(ISLEEG(ALS(ISLEEG(D10);0;D10-12)+ALS(ISLEEG(D11);0;D11-15)+ALS(ISLEEG(D12);0;D12-18)

We gaan deze nu in de ALS() formule gieten. We willen namelijk dat als de som 0 is dat er niets wordt getoond en als er wel een waarde uit komt dat deze getoond wordt. Dus ALS( als som = 0 ; “” ; de som ; ) 

De uiteindelijke formule is:

=ALS(ALS(ISLEEG(D7);0;D7-3)+ALS(ISLEEG(D8);0;D8-6)+ALS(ISLEEG(D9);0;D9-9)+ALS(ISLEEG(D10);0;D10-12)+ALS(ISLEEG(D11);0;D11-15)+ALS(ISLEEG(D12);0;D12-18)=0;"";ALS(ISLEEG(D7);0;D7-3)+ALS(ISLEEG(D8);0;D8-6)+ALS(ISLEEG(D9);0;D9-9)+ALS(ISLEEG(D10);0;D10-12)+ALS(ISLEEG(D11);0;D11-15)+ALS(ISLEEG(D12);0;D12-18))
Selecteer cel D6, kopieer bovenstaande formule in de formulebalk en druk enter

Herhaal deze stappen voor spel 2 tot en met 6 in cel E6 tot en met I6. U kunt hierbij de formule weer kopiëren net als in stap 2.6.

3.2 Voorwaardelijk opmaak

Om het formulier iets meer glans te geven gaan we een voorwaardelijk opmaak toevoegen aan de D6 tot en met I6. Met een voorwaardelijke opmaak kun je de opmaak van een cel aanpassen als er aan een vooraf ingestelde voorwaarde is voldaan. Het doel is om de achtergrondkleur en de tekstkleur aan te passen als de formule van stap 3.1 negatief is.

Yahtzee scoreblok in Excel

Selecteer cel D6 en klik op de knop Voorwaardelijke opmaak boven in het lint in het menu start. Klik op nieuwe regel. Selecteer de 2e regel “Alleen cellen opmaken met”. Onderin het scherm kun je nu de voorwaarde opgeven. We gaan alleen cellen opmaken met een cel waarde kleiner dan 0. Selecteer kleiner dan in de dropdown box en vul als waarde 0 in.

Yahtzee scoreblok in Excel

Voordat we op OK klikken gaan we eerste de opmaak aanpassen. Klik op de knop opmaak. Ga naar het tabblad Lettertype en zet de tekstkleur op wit.

Yahtzee scoreblok in Excel

Selecteer nu het tabblad Opvulling en klik op de kleur rood. Klik vervolgens op OK.

Yahtzee scoreblok in Excel

We hebben nu voor de cel D6 de voorwaardelijke opmaak bepaald. Probeer maar eens door bijvoorbeeld een score van 2 bij de enen te noteren. Er komt nu -1 te staan boven spel 1 in witte tekst met rode achtergrond.

Nu willen we echter de regel toepassen op de hele rij D6 tot en met I6. Selecteer cel D6 en klik op Voorwaardelijke opmaak in het menu in het lint en selecteer nu Regels beheren.

Yahtzee scoreblok in Excel

Het scherm met de zojuist gemaakte regel is hier zichtbaar. In het blokje “Van toepassing op” staat voor welke cel deze regel van toepassing is. Dit bereik gaan we aanpassen van =$D$6naar =$D$6:$I$6. Door dit bereik aan te passen zeggen we nu dat deze regel van toepassing is op cel D6 tot en met I6.

Yahtzee scoreblok in Excel

Stap 3 is daarmee klaar. We hebben uitgerekend hoeveel +- score we hebben en als we in de – komen zal de teller aangeven hoeveel en een rode achtergrond krijgen met witte letters. We kunnen nu door naar stap 4.


Stap 4: Yahtzee-bonus via gegevensvalidatie

Yahtzee scoreblok in Excel

Als je geluk hebt scoor je een Yahtzee-bonus! Voorwaarde is dat je alle Yahtzee’s hebt ingevuld zonder kruisjes, streepjes of 0. De Yahtzee-bonus bedraagt dan 100 punten voor elke volgende Yahtzee. In stap 4 gaan we de bonus Yahtzee invullen met behulp van een dropdown-lijstje.

4.1 Gegevensvalidatie

Het aantal Yahtzee-bonussen gaan we noteren in cel J21. Selecteer deze cel en ga boven in het lint naar het tabblad “Gegevens“. Klik op de knop “Gegevensvalidatie“. Selecteer onder toestaan de optie “Lijst“.

Yahtzee scoreblok in Excel

Bij 1 vinken we beide vinkjes aan. Bij 2 voeren we het bereik in van de bron lijst. We kunnen een bereik selecteren van een lijstje wat we in het excel bestand hebben of we kunnen een eigen bereik creëren. We gaan het laatste doen. Elk teken dat we in de lijst willen hebben scheiden we door het ; teken.

Yahtzee scoreblok in Excel

Wij willen alleen maar getallen in het bereik hebben van 0 tot en met 9. Je moet wel heel gelukkig zijn wil je meer dan 9 Yahtzee’s-bonussen gooien! Ons bereik zal worden: 0;1;2;3;4;5;6;7;8;9

Kopieer bereik 0;1;2;3;4;5;6;7;8;9 in de bron van de gegevensvalidatie en klik op OK
Yahtzee scoreblok in Excel

We hebben nu een dropdown-lijstje met getallen 0 tot en met 9 waarmee we kunnen aangaven hoeveel Yahtzee-bonussen we hebben gegooid. Deze gaan we in de volgende stap gebruiken om de eindscore te berekenen van alle 6 de spellen.

In stap 5 gaan we de eindscore bepalen en wanneer deze zichtbaar wordt.


Stap 5: Yahtzee eindscore optellen na laatste worp

Om de spanning nog een beetje vast te houden tot het einde wordt pas na de laatste worp de eindscore zichtbaar. We gaan eerst controleren of alle score vakjes zijn ingevuld. Dan gaan we de scores bij elkaar optellen. Als laatste gaan we de Yahtzee-bonus aantal uit stap 6 vermenigvuldigen met 100 en er bij optellen.

5.1 AANTALARG() functie voor controle of we klaar zijn

Als je alle 6 de spellen speelt heb je in totaal 78 beurten (zonder Yahtzee-bonus). Het aantal scores gaan we tellen met behulp van de AANTALARG() functie. Deze is bijna gelijk aan die we in stap 1 ook hebben gebruikt om het aantal beurten te tellen. De formule was: =AANTALARG(D3:I8;D11:I17) en zal nu worden =ALS(AANTALARG(D3:I8;D11:I17). De reden dat we nu AANTALARG() gebruiken is omdat deze telt of er een waarde is ingevuld, ongeacht welke. Als we een score niet behalen zet je een kruisje, streepje of een 0. AANTALARG() telt die ook mee in het aantal beurten.

Nu gaan we deze functie combineren met de ALS() functie. We willen namelijk pas wat tonen als we alle beurten (78) hebben ingevuld. De formule hiervoor is =ALS(AANTALARG(D3:I8;D11:I17)=78;waarde1;waarde2)

Als we 78 beurten hebben gehad is zal waarde 1 worden getoond. Hebben we nog geen 78 beurten gehad zal waarde 2 worden getoond. We gaan bij waarde 1 de som van de totaal score plaatsen. De totaal scores per spel staan in de cellen D25 tot en met I25. De formule voor de som zal dan worden: SOM(D25:I25)

De totaal formule tot nu toe is dus: =ALS(AANTALARG(D3:I8;D11:I17)=78;SOM(D25:I25);waarde2)

Nu moeten we hierbij de het aantal Yahztee-bonussen bij optellen. Deze berekenen we door cel J21 te vermenigvuldigen met 100 en bij deze som op te tellen. De formule hiervoor is J21*100.

De totaal formule tot nu toe is dus: =ALS(AANTALARG(D3:I8;D11:I17)=78;SOM(D25:I25)+(J21*100);waarde2)

Nu moeten we alleen nog de waarde bepalen voor waarde 2. De waarde als we nog niet alle beurten hebben gegooid. Deze zetten we op 0.

De totaal formule is: =ALS(AANTALARG(D3:I8;D11:I17)=78;SOM(D25:I25)+(J21*100);0)

Selecteer cel H2, kopieer =ALS(AANTALARG(D3:I8;D11:I17)=78;SOM(D25:I25)+(J21*100);0) in de formulebalk en druk enter

5.2 Voorwaardelijke opmaak voor totaal score

De cel H2 met de formule is groen. In stap 3.2 hebben we al de voorwaardelijke opmaak gebruikt voor de +- score. Deze gaan we nu gebruiken voor de totaal score in cel H2. Selecteer cel H2 en in het lint onder “Start” op “voorwaardelijke opmaak“. Klik op nieuwe regel. Selecteer de 2e regel “Alleen cellen opmaken met“. Onderin het scherm kun je nu de voorwaarde opgeven. We gaan de cel alleen opmaken als de er een score is vermeld, dus als het getal groter is dan 0.

Yahtzee scoreblok in Excel

Klik op de knop “opmaak” om de opmaak van de cel te bepalen als aan de voorwaarde is voldaan. Ik heb gekozen voor een witte achtergrond en groen letters.

Yahtzee scoreblok in Excel

Het scoreblok is nu helemaal klaar voor gebruik. De score kan bijgehouden worden en de totaal score wordt zichtbaar aan het einde van het spel. In de laatste stap, stap 6 gaan we als laatste nog een knop toevoegen met een macro om het spel te resetten.


Stap 6: Het formulier resetten met een macro

Als we helemaal zijn uitgedobbeld kunnen we het formulier met behulp van een simpele druk op de knop resetten. Alle scores worden verwijderd en we kunnen opnieuw beginnen. Eerst moeten we de macro maken. Een macro is geschreven in VBA en dat is een programmeertaal die bepaalde handelingen automatiseert.

Wat wij willen gaan doen is een bereik bepalen en en binnen dat bereik moeten alle cellen worden gewist. De macro ziet er als volgt uit:

Sub reset()
   Range("D7:I12,D16:J22").Select
   Selection.ClearContents
   Range("B5").Select
End Sub

We hebben hiermee een Sub gemaakt. Met een Sub kunnen we acties uitvoeren. De naam van de Sub is reset.
Op de tweede regel geven we aan om welk bereik het het gaat en dat we die willen selecteren. Dat doen we door Range( het bereik ).Select. Zie dat het bereik in het voorbeeld het bereik is van de Yahtzee score kaart.
Op de derde regel geven we uiteindelijk aan wat er moet gebeurd, het geselecteerde moet gewist worden (ClearContents).
Op de vierde regel geven we aan dat de cel B5 willen selecteren. Dat is de cel waar de naam staat, wat betekent dat je gelijk een nieuwe naam kunt invullen na de reset.
Met End Sub wordt de Sub beëindigd en geeft die aan dat hij klaar is.

6.1 VBA code toevoegen in Microsoft Visual Basic for Applications

Om Microsoft VBA te openen druk je op de toetsencombinatie ALT + F11. Dubbelklik vervolgens links in de mappen op Blad 1 (Yahtzee). In het witte venster onder Algemeen kun je dan de code in kopiëren.

Yahtzee scoreblok in Excel

Kopieer de VBA code in het veld Algemeen en sluit het venster.

Nu moeten we alleen nog de knop koppelen aan de macro. Rechtsklik op de knop en klik op Macro toewijzen.

Yahtzee scoreblok in Excel

Er opent een scherm met Macro toewijzen aan object. Selecteer de macro Blad1.reset en klik OK.

Yahtzee scoreblok in Excel

De macro is nu gekoppeld aan de knop. Als je nu op de knop klikt zal de inhoud van het formulier worden gewist.


Yahtzee scoreblok in Excel

Yahtzee scoreblok in Excel

We hebben nu een compleet bestand werkend bestand. Wat hebben we in het kort gedaan:

  • Stap 1: Aantal beurten bereken
  • Stap 2: Scores berekenen en bonus bepalen
  • Stap 3: Berekenen +- score en voorwaardelijke opmaak
  • Stap 4: Dropdown-lijstje voor Yahtzee-bonus
  • Stap 5: Totaal score na laatste worp
  • Stap 6: VBA gebruikt om formulier te resetten

Lege rijen invoegen Excel met VBA code

3

Voor een excel bestand was ik op zoek naar een methode om lege rijen toe te voegen in een lange excel lijst. Handmatig zou dit mijn hele avond gekost hebben. Via de website ExtendOffice kwam ik de volgende VBA code tegen.

VBA code toevoegen in Microsoft Visual Basic for Applications

Open een nieuw bestand of een bestand waarin je de VBA wilt gebruiken. Druk op de toetsencombinatie ALT + F11 om de Microsoft Visual Basic for Applications te openen. Plak de volgende VBA code en sla het bestand op als Excel-werkmap met macro’s.

Sub InvoegenLegeRijenMetInterval()
Dim Rng As Range
Dim xInterval As Integer
Dim xRows As Integer
Dim xRowsCount As Integer
Dim xNum1 As Integer
Dim xNum2 As Integer
Dim WorkRng As Range
Dim xWs As Worksheet
xTitleId = "Excel Wiki - Excel.wiki"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRowsCount = WorkRng.Rows.Count
xInterval = Application.InputBox("Interval rijen ", xTitleId, 1, Type:=1)
xRows = Application.InputBox("Hoeveel rijen invoegen na de interval? ", xTitleId, 1, Type:=1)
xNum1 = WorkRng.Row + xInterval
xNum2 = xRows + xInterval
Set xWs = WorkRng.Parent
For i = 1 To Int(xRowsCount / xInterval)
 xWs.Range(xWs.Cells(xNum1, WorkRng.Column), xWs.Cells(xNum1 + xRows - 1, WorkRng.Column)).Select
 Application.Selection.EntireRow.Insert
 xNum1 = xNum1 + xNum2
Next
End Sub

VBA invoegen lege rijen uitvoeren

Om de VBA uit te voeren klik op de knop Macro’s weergeven of gebruik de toetsencombinatie ALT + F8. Selecteer de macro InvoegenLegeRijenMetInterval en klik op uitvoeren.

1. Selecteer het bereik van de rijen en klik op OK.

Lege rijen invoegen in Excel met VBA code

2. Selecteer in de volgende pop-up om de hoeveel rijen moet er een lege rij worden toegevoegd en klik op OK.

Lege rijen invoegen in Excel met VBA code

3. Selecteer in de pop-up hoeveel rijen er moeten worden toegevoegd na elke interval en klik op OK.

Lege rijen invoegen in Excel met VBA code

De macro wordt nu uitgevoerd. Na elke rij worden er twee lege rijen ingevoegd. Zie afbeelding 4 voor het eindresultaat.

Lege rijen invoegen in Excel met VBA code