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”.
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.
Resultaat:
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.
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.
Resultaat:
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.
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”.
2. Selecteer cel A1 en cel A2 en sleep dan het groene selectie kader naar beneden aan het vierkantje rechts onder.
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).
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.
5. Zet in cel A1 de waarde “Artikel 1”.
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.
7. Zet in cel A1 de waarde “Maandag”.
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.
Datum en tijd automatisch aanvullen
9. Zet in cel A1 de datum “11-01-2019”.
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.
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:
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”.
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.
14. Naast verticaal werkt deze methode ook horizontaal. Zet in cel A1 de waarde “6:00”.
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.
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.
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.
2. Rechts klik in de geselecteerde kolom en klik op Verbergen.
3. Resulaat:
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.
2. Rechtsklik in de geselecteerde kolommen en klik op Zichtbaar maken.
Resultaat:
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.
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.
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.
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.
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.
5. Om de breedte van alle kolommen aan te passen kun je deze makkelijk te selecteren door op de Selecteer alles knop te klikken.
6. Nu kun je alle kolommen aanpassen door de de rechter lijn van een kolom te slepen.
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.
2. Klik in het lint (menu balk) in de groep cellen op Opmaak.
3. Klik op Rijhoogte Autoaanpassen.
4. Resultaat:
Opmerking: Deze methode kun je ook gebruiken voor de kolommen, zie Kolombreedte Autoaanpassen in de screenshot van stap 3.
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.
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.
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:
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)
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.
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.
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.
Na het uitvoeren van de macro had ik de volgende mappen (afbeelding 2):
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
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.
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().
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:
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.
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 ; )
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.
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.
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.
Selecteer nu het tabblad Opvulling en klik op de kleur rood. Klik vervolgens op OK.
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.
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.
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
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“.
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.
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 gegevensvalidatieen klik op OK
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.
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.
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.
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.
Er opent een scherm met Macro toewijzen aan object. Selecteer de macro Blad1.reset en klik OK.
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
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
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.
2. Selecteer in de volgende pop-up om de hoeveel rijen moet er een lege rij worden toegevoegd en klik op OK.
3. Selecteer in de pop-up hoeveel rijen er moeten worden toegevoegd na elke interval en klik op OK.
De macro wordt nu uitgevoerd. Na elke rij worden er twee lege rijen ingevoegd. Zie afbeelding 4 voor het eindresultaat.
De technische opslag of toegang is strikt noodzakelijk voor het legitieme doel het gebruik mogelijk te maken van een specifieke dienst waarom de abonnee of gebruiker uitdrukkelijk heeft gevraagd, of met als enig doel de uitvoering van de transmissie van een communicatie over een elektronisch communicatienetwerk.
Voorkeuren
De technische opslag of toegang is noodzakelijk voor het legitieme doel voorkeuren op te slaan die niet door de abonnee of gebruiker zijn aangevraagd.
Statistieken
De technische opslag of toegang die uitsluitend voor statistische doeleinden wordt gebruikt.De technische opslag of toegang die uitsluitend wordt gebruikt voor anonieme statistische doeleinden. Zonder dagvaarding, vrijwillige naleving door uw Internet Service Provider, of aanvullende gegevens van een derde partij, kan informatie die alleen voor dit doel wordt opgeslagen of opgehaald gewoonlijk niet worden gebruikt om je te identificeren.
Marketing
De technische opslag of toegang is nodig om gebruikersprofielen op te stellen voor het verzenden van reclame, of om de gebruiker op een website of over verschillende websites te volgen voor soortgelijke marketingdoeleinden.
