Mike Dole

Mike Dole

zaterdag, 13 april 2013 18:25

Excel toernooi schema

Het organiseren van een toernooi is altijd een flinke klus, het indelen van de poules / teams, bepalen van de puntentelling, etc.
Excel kan hier een handig hulpmiddel bij zijn maar vaak is het last om 'ergens te beginnen', gelukkig is er op internet al veel te vinden wat met een beetje knutselen aan kan sluiten bij jouw toernooi.

In de tips en trucs categorie staan wat voorbeeld Excel bestanden* voor toernooien, gebruik het contactformulier als je er niet uit komt of een variant met meer / minder teams / poules nodig hebt.

Downloads

LET OP: in Firefox 'rechts klikken' en 'koppeling opslaan als' kiezen!

2 poules 4 ploegen voorronde en finale rondes

2 poules 5 ploegen voorronde en finale rondes

2 poules 6 ploegen voorronde en finale rondes

4 poules 4 ploegen Uit en Thuis

4 poules 5 ploegen - nummers 1 en 2 gaan naar de kwartfinale

4 poules 5 ploegen - nummers 1 en 2 gaan naar de finaleronde nummers 3 en 4 naar de troostfinale

4 poules 4 ploegen - nummers 1 en 2 gaan naar de kwartfinale


Updates:

Toernooi 5 poules 5 ploegen finale ronde beste nummers 1

Toernooi 5 poules 5 ploegen voorrondes en finales beste nummers x

4 poules 4 ploegen aangepaste kruis finales SAEN toernooi

Toernooi 4 poules 8 ploegen

Toernooi 4 poules 6 ploegen - kwartfinale - halve finale - finale

Toernooi poule van 2 X 5 en 1 x 1 ploegen, halve finales 3 nummers 1 en beste nummer 2

donderdag, 11 april 2013 19:48

Excel toernooi schema 2

Het organiseren van een toernooi is hele klus, het indelen van de poules / teams, bepalen van de puntentelling, etc.
Excel kan hier een handig hulpmiddel bij zijn maar vaak is het last om 'ergens te beginnen', gelukkig is er op internet al veel te vinden wat met een beetje knutselen aan kan sluiten bij jouw toernooi.

UPDATE: HIER vind u de populaire 4 poules 5 ploegen en 4 poules 4 ploegen toernooi bestanden!

In de tips en trucs categorie staan wat voorbeeld Excel bestanden* voor toernooien, gebruik het contactformulier als je er niet uit komt of hulp nodig hebt.

 Bekers algemeen

Gelijk bekere en vaantjes regelen? Keuze uit vele tientallen modellen,voor een blijvende herinnering aan uw toernooi!

Klik hier!

 


Als eerste bestand een voorbeeld van het toernooi schema voor de wereldkampioenschappen 2006, download HIER. Een erg handig Excel werkblad waarbij een paar zaken opvallen:

  • Alle functies zijn standaard Excel (geen VBA), er wordt geen gebruik gemaakt van macro's
  • Zoek matrixen staan allemaal (in het wit) in het hoofdwerkblad
  • Een goed voorbeeld van meertaligheid in Excel. Taalteksten zijn te vinden  in tabblad T
    Excel multilanguage
  • Veel gebruik van verticaal zoeken en index formules.
    Een index formule zoekt een waarde in een lijst / bereik op basis van een kolom en een rij. Dus eigenlijk op basis van een X en een Y locatie in een verzameling cellen.
    In het Excel bestand wordt bijvoorbeeld de tekst 'Eerste ronde' opgezocht met de formule:
    =INDEX(T;41;language)
    T staat voor het werkblad met alle taalteksten, waarbij iedere taal een eigen kolom heeft. 41 geeft de rij aan op dit werkblad met de tekst die we willen vinden (Engels = First Round) en als laatste is language een benoemd bereik wat verwijst naar cel BK6.
    In deze cel BK6 staat ook weer een formule (=VERT.ZOEKEN(BJ6;BJ7:BK50;2;ONWAAR)) die zoekt op basis van de gekozen taal (BJ6) het corresponderende nummertje.
    Lijkt lastig maar kijk gewoon even in het voorbeeldbestand.
  • Gebruik van benoemde bereiken (GMT, GMT_MIN, T, language)
    Benoemde Bereiken Excel
  • Comboboxen Tijd en Taal zorgen d.m.v. een gekoppelde cel voor wijzigingen in het werkblad (geen macro)
    ComboBox Met Gekoppelde Cel

Hoe worden de stand bepaald?
In het voorbeeldbestand heb ik de kolommen R en hoger die normaal verborgen zijn / witte letters hebben zichtbaar gemaakt zodat we kunne zien wat hier gebeurt.

Het rekenwerk gebeurt in de normaal gesproken verborgen kolommen, voor groep A is dit het bereik T7 t/m AA10. Vullen we scores in in de kolommen D en E voor de wedstrijd Duitsland - Costa Rica dan wordt  in cel AW7 gekeken of de uitslag in D7 hoger is dan in E7, hieruit wordt de tekst 'win / draw of lose' gemaakt en gecombineerd (tekst.samenvoegen) met F7 (Costa Rica).

=ALS(D7="";"";ALS(E7="";"";ALS(D7>E7;TEKST.SAMENVOEGEN(F7;"_lose");ALS(D7<E7;TEKST.SAMENVOEGEN(F7;"_win");TEKST.SAMENVOEGEN(F7;"_draw")))))

Kolommen V - W - X ('WIN', 'DRAW' en 'LOSE') worden dan berekend door bij _WIN te kijken hoeveel keer het land in U7 (dit kan verschillen) in het bereik AV7:AW54 staat met _WIN achter de landnaam!

=AANTAL.ALS(AV7:AW54;TEKST.SAMENVOEGEN(U7;"_win"))

De punten in kolom AA worden berekend door het aantal 'WINS" maal 3 punten te doen + het aantal gelijke spelen.

Kolom AB is interessant omdat hier bepaald wordt wie er bij een gelijk aantal punten doorgaat naar de volgende ronde (Ik moet nog bekijken hoe dit wordt opgebouwd, wie er achter is gekomen ff mailen ;)  )

=0,4+AK7+Y7*1000+(Y7-Z7)*100000+AA7*10000000

Bepalen Score Toernooi Excel

Het rekenwerk in de hulptabellen zoals  T7:AA10 wordt vervolgen in het groepoverszicht met verticaal zoeken opgezocht en getoond. Iedere groep heeft 4 posities / 4 landen. Positie 4 is de nummer 1, positie 3 de nummer 2, 2 de nummer 3 en positie 1 de nummer 4. De verticaal zoeken formules zijn opgebouwd zodat ze steeds de betreffende positie (rood) op gaan zoeken, in bereik T7:AA10 en de waarden uit de benodigde kolom (blauw) terug geven.

Bij een bereik T7:AA10:

 Verticaal zoeken bereik

Geven de formules:

=VERT.ZOEKEN(4;T7:AA10;2;ONWAAR)
Zoek 4 in bereik T7:AA10 en geef de 2e kolom terug (onwaar wil zeggen we moeten een exacte zoekmatch hebben) --> Ecuador

=VERT.ZOEKEN(3;T7:AA10;2;ONWAAR)
Zoek 3 in bereik T7:AA10 en geef de 2e kolom terug (onwaar wil zeggen we moeten een exacte zoekmatch hebben) --> Duitsland

=VERT.ZOEKEN(2;T7:AA10;2;ONWAAR)
Zoek 2 in bereik T7:AA10 en geef de 2e kolom terug (onwaar wil zeggen we moeten een exacte zoekmatch hebben) --> Polen

=VERT.ZOEKEN(1;T7:AA10;2;ONWAAR)
Zoek 1 in bereik T7:AA10 en geef de 2e kolom terug (onwaar wil zeggen we moeten een exacte zoekmatch hebben) --> Costa Rica

Verder zitten er nog een groot aantal formules in waar we later op terug komen..

* Excel schema's zijn niet zelfgemaakt maar gevonden op het internet en in detail aangepast. Auteurs zijn onbekend, neem contact op via het contactformulier als u meent dat het formulier onterecht op onlineexcelcursus.nl staat

Overigens kun je voor een gewoon schema waarbij je meerdere ploegen 1x tegen elkaar wilt laten spelen (zgn. Round Robin principe) ook een generator gebruiken zoals op:
http://oxfordcroquet.com/manage/roundrobin/index.asp

In formules ben je al snel gewend om het gebruik van dubbele quotes / aanhalingstekens / "" te gebruiken om lege cellen aan te geven zoals bijvoorbeeld =ALS(B1="";1;0).
Excel zet er dan zelf nog dubbele quotes omheen (A)
Bij voorwaardelijke opmaak ben al gauw geneigd om het op dezelfde manier aan te geven, de voorwaardelijke opmaakt werkt dan echter niet!

Voorwaardelijke opmaak Excel 2013

Voorwaardelijke opmaak in Excel bij lege cellen

Voorwaardelijke opmaak Excel 2010

Voorwaardelijke opmaak lege cellen Excel 2010

Selecteer bij 'Alleen cellen opmaken' - 'Lege waarden', het ligt erg voor de hand maar omdat je bijna overal voor lege cellen met quotes werkt kijk je er gemakkelijk overheen!

Met voorwaardelijke opmaak laat je 'je cijfers' veel meer spreken en toont het bijvoorbeeeld aan een gebruiker in 1 oogopslag hoe de cijfers er voor staan. Waar scoren we goed (groene cijfers), waar scoren we slecht (rode cijfers).  Een goed voorbeeld van het gebruik van voorwaardelijke opmaak in Excel vind je hier

Hou het gebruik van voorwaardelijke opmaak wel 'subtiel', veel kleuren, toeters en bellen geven al snel de indruk van een kermis kraam in plaats van een werkblad.

 

 

Als julliie de vorige tips en trucs over het zelf schrijven van een vba functie ook hebben gelezen en de smaak te pakken hebben dan gaan we nu wat functies bekijken die ik tegen ben gekomen waar we de werkbladnamen in gebruiken. Het voorbeeldbestand met de zelfgemaakte vba functies kun je hier downloaden!

Stappen:

Om de functies die we in ieder werkblad aan kunnen roepen toe te voegen kiezen we na het openen van een Excel / maken van een nieuwe Excel voor ALT-F11 en klikken in de menubalk bovenin op 'Invoegen - Module'. Daarna 'Kopiëren' en 'Plakken' we de functies hieronder die we willen gebruiken met uiteindelijk het volgende resultaat:

Voorbeeld functies met werkbladnaam

In het voorbeeldbestand hebben we 12 tabbladen voor de maanden in een jaar.

Huidige werkbladnaam

Geef de naam van het huidige werkblad:

Function HuidigeWerkbladNaam() As String
    Application.Volatile True
    HuidigeWerkbladNaam = Application.Caller.Parent.Name
End Function

Eerste werkbladnaam

Geef de naam van het eerste werkblad:

Function EersteWerkbladNaam() As String
    Application.Volatile True
    With Application.Caller.Parent.Parent.Worksheets
    EersteWerkbladNaam = .Item(1).Name
    End With
End Function

Deze functie kunnen we nu ook heel eenvoudig in een andere functie gebruiken. Als voorbeeld gebruiken we de functie indirect waarmee we door een combinatie te maken tussen de nieuwe functie EersteWerkbladNaam() en de celverwijzing A3 eigenlijk zeggen 'Geef me de waarde uit cel A3 van het eerste werkblad'!

=INDIRECT(EersteWerkbladNaam() & "!A3")

Huidige positie werkblad

Geef het nummer / de positie van het huidige werkblad

Function HuidigeWerkpladPositie() As Integer
    Application.Volatile True
    HuidigeWerkpladPositie = Application.Caller.Parent.Index
End Function

Toon aantal werkbladen

Deze functie geeft het totaal aantal werkbladen aan wat er in een Excel file zit

Function AantalWerkbladen() As Integer
    Application.Volatile True
    AantalWerkbladen = Application.Caller.Parent.Parent.Worksheets.Count
End Function

Laatste werkbladnaam

Toon de naam van het laatste werkblad

Function LaaststeWerkbladNaam() As String
    Application.Volatile True
    With Application.Caller.Parent.Parent.Worksheets
    LaaststeWerkbladNaam = .Item(.Count).Name
    End With
End Function

Toon de naam van het vorige werkblad t.o.v. het huidige werkblad

Dit is een handige functie waarin we de naam van het vorige werkblad ophalen, staan we op het eerste werkblad (januari in het testbestand), dan toont de formule de maand december!

Function VorigeWerkbladNaam(Optional ByVal WS As Worksheet = Nothing) As String
    Application.Volatile True
    Dim S As String
    Dim Q As String
    If IsObject(Application.Caller) = True Then
    Set WS = Application.Caller.Worksheet
    If WS.Index = 1 Then
    With Application.Caller.Worksheet.Parent.Worksheets
    Set WS = .Item(.Count)
    End With
    Else
    Set WS = WS.Previous
    End If
    If InStr(1, WS.Name, " ", vbBinaryCompare) > 0 Then
    Q = "'"
    Else
    Q = vbNullString
    End If
    Else
    If WS Is Nothing Then
    Set WS = ActiveSheet
    End If
    If WS.Index = 1 Then
    With WS.Parent.Worksheets
    Set WS = .Item(.Count)
    End With
    Else
    Set WS = WS.Previous
    End If
    Q = vbNullString
    End If
    VorigeWerkbladNaam = Q & WS.Name & Q
End Function

Ook deze functie kunnen we eenvoudig in een andere functie gebruiken. Als voorbeeld gebruiken we nogmaals de functie indirect waarmee we door een combinatie te maken tussen de nieuwe functie EersteWerkbladNaam() en de celverwijzing A3 eigenlijk zeggen 'Geef me de waarde uit cel A3 van het vorige werkblad'!

=INDIRECT(VorigeWerkbladNaam() & "!A3")

 Volgende werkblad naam

Function VolgendeWerkbladNaam(Optional WS As Worksheet = Nothing) As String
    Application.Volatile True
    Dim S As String
    Dim Q As String
    If IsObject(Application.Caller) = True Then
    Set WS = Application.Caller.Worksheet
    If WS.Index = WS.Parent.Sheets.Count Then
    With Application.Caller.Worksheet.Parent.Worksheets
    Set WS = .Item(1)
    End With
    Else
    Set WS = WS.Next
    End If
    If InStr(1, WS.Name, " ", vbBinaryCompare) > 0 Then
    Q = "'"
    Else
    Q = vbNullString
    End If
    Else
    If WS Is Nothing Then
    Set WS = ActiveSheet
    End If
    If WS.Index = WS.Parent.Worksheets.Count Then
    With WS.Parent.Worksheets
    Set WS = .Item(1)
    End With
    Else
    Set WS = WS.Next
    End If
    Q = vbNullString
    End If
    VolgendeWerkbladNaam = Q & WS.Name & Q
End Function

=INDIRECT(VolgendWerkbladNaam() & "!A3" geeft ons ook weer de waarde van A3 op het volgende werkblad.

Een andere manier om een celwaarde van een vorig werkblad te krijgen is m.b.v. de volgende functie:

Function PakCelOpVorigWerkblad(Addr As String) As Variant
    Application.Volatile True
    With Application.Caller.Parent
    If .Index = 1 Then
    RefOnPrevSheet = _
    .Parent.Worksheets(.Parent.Worksheets.Count).Range(Addr).Value
    Else
    PakCelOpVorigWerkblad = .Previous.Range(Addr).Value
    End If
    End With
End Function

We geven als parameter een tekststring mee, bijvoorbeeld: =PakCelOpVorigWerkblad("A6"). Let op de dubbele quotes om A6!
Ook hier geldt weer, staan we op het eerste werkblad, pak dan de waarde uit de cel waar naar verwezen wordt op het laatste werkblad.

Wat opvalt is dat iedere functie Application.Volatile True heeft staan, dit betekent dat de functie niet alleen herberekend moet worden als de input parameter van waarde verandert, maar dat Excel moet herberekenen bij iedere wijziging van het werkblad.

zondag, 24 maart 2013 16:01

Hoe werkt de formule aantal.als?

De formule aantal.als is handig als je wilt weten of een bepaalde waarde zich in een lijst met gegevens bevindt maar hoe werkt hij nu precies?Ik geef een praktijkvoorbeeld waarbij ik in een kolom een lijst met plaatsen heb: Londen, Hamburg, etc.

In cel B1 zet ik de plaats waarvan ik wil weten of deze in de lijst voorkomt: Londen.

Nu wil ik in cel A1 een 'Ja' als de waarde van cel B1 (Londen) in de lijst (D1 t/m D10) voor komt, mijn formule in A1 wordt dan:
=ALS(AANTAL.ALS(D1:D10;B1);"Ja";"Nee")

aantal.als

Hoewel som.als apart wordt behandeld bij tips en trucs zijn er misschien mensen die op deze pagina terecht komen terwijl ze eigenlijk op zoek waren naar het volgende:

Stel na de plaatsen staat een inwoneraantal (totaal niet gecheckt ;) ) en we willen op basis van wat erin Cel B1 staat het inwoneraantal hebben dan gebruiken we de formule som.als:

voorbeeld som.als formule

In dat geval wordt de formule als volgt:

=SOM.ALS(D1:D4;B1;E1:E4)

D1 t/m D4 is het criteriumbereik, de lijst met plaatsen. In de cel B1 staat de plaats die we willen opzoeken en het optelbereik is E1:E4.
Staat Londen 2x in de lijst, dan krijgen we de optelsom van de cellen (2 x 30000000 = 6000000):

Nog een voobeeld functie som.als

 

Op zoek naar een speciale functie of macro, gebruikt het contactformulier specificeer uw wens en ik ga kijken of ik de functie / macro voor u kan schrijven.

Download office 2013

Gratis Micrososft Office 2013

Microsoft heeft een pagina voor een gratis 60 dagen versie van Microsoft Office Professional Plus 2013

Hierin zitten o.a: Word, PowerPoint, Excel, Outlook, OneNote, Access, Publisher, and Lync.

De volgende systeemeisen gelden voor Excel 2013:

 

  • PowerPivot
    .NET 3.5 or .NET 4.0 en minimaal 2GB intern geheugen

  • PowerView add-in
    Silverlight 5.0 en minimaal 2GB intern geheugen

  • Inquire add-in
    .NET 4.0 of 4.5

Download hier een evaluatie versie van Micrososft Office 2013

vrijdag, 08 maart 2013 19:53

Gebruik een Excel sjabloon / template

Veel mensen laten de Excel sajblonen links liggen, dit terwijl ze erg handig en zeer informatief zijn.
Waarom het wiel opnieuw uitvinden ofwel waarom met een leeg werkblad beginnen als er standaard een Excel sjabloon beschikbaar is waarin hetgeen je in je hoofd hebt al zit?
Excel sjablonen

Kies ‘Bestand – Nieuw’ en er worden al een groot aantal sjablonen getoond, variërend van onkostendeclaraties tot  kant en klare kalenders.

onkosten declaratie
Dit scheelt je echt uren werk!

Verder kun je hier online zoeken via de textbox: ‘Sjablonen zoeken in Office.com’. Ben je als leraar op zoek naar een aanwezigheid overzicht van je leerlingen in Excel, dan hoef je dat niet helemaal opnieuw te bouwen maar is dit gewoon via ‘zoeken’ online te vinden!

aanwezigheid leerlingen

Het ‘neuzen’ in deze sjablonen leert je ook heel veel, je ziet hoe veel formules in de praktijk gebruikt worden en op basis van het doel van de template komen de formules ook veel duidelijker over.

Zelf een sjabloon maken

Als je een Excel werkblad hebt gemaakt die je veelvuldig gaat gebruiken kun je hier heel eenvoudig een sjabloon van maken.
Kies ‘Bestand – Opslaan Als’, kies in het vak ‘opslaan als’: Excel sjabloon of Excel sjabloon met macro’s .
Template opslaan

Als je nu ‘Bestand – Nieuw – Mijn sjablonen’  kiest dan zie je je zojuist opgeslagen sjabloon er tussen staan!

We gebruiken in deze functie een range parameter, een verwijzing naar een cel 'waar we iets mee gaan doen'.


Opdracht: Excel heeft een formule om getallen op te vullen, de formule tekst(), =TEKST(A1;"000") geeft dan als resultaat als er 2 in cel A1 staat 002.
Willen we 200 of A000 dan moeten we op een andere manier te werk gaan, waarschijnlijk is er binnen Excel (een combinatie van) functies om dit te bewerkstelligen maar op het moment dat ik een dergelijke functie nodig had ben ik hem gaan bouwen:

Opdracht: Maak een formule met de volgende parameter: een cel als input,  het karakter op waar je mee wilt op- / uitvullen, de gewenste lengte en de kant waar je hem op wilt vullen (links / rechts).
   

  • Toets ALT-F11   
  • Kies Invoegen Module   
  • Plak de volgende code: 
Public Function Opvullen(ByVal DoelCel As Range, ByVal OpvullenMet As String, ByVal OpvulLengte As Integer, ByVal OpvulRichting As Integer)
Dim TmpString As String
Dim TmpChar As String
Dim i As Integer
TmpString = DoelCel.Value 'Basiswaarden vullen
i = 1
If Len(TmpString) = 0 Then 'Geen celwaarde dan gelijk uit functie gaan
    Opvullen = ""
End If
If Len(TmpString) < OpvulLengte Then
    If OpvulRichting = 0 Then '0 = links
        For i = Len(TmpString) To OpvulLengte - 1 'i is 1 tot lengte - 1
            TmpChar = TmpChar & OpvullenMet
        Next i
        TmpString = TmpChar & TmpString
    ElseIf OpvulRichting = 1 Then '1 = rechts
        For i = Len(TmpString) To OpvulLengte - 1
            TmpChar = TmpChar & OpvullenMet
        Next i
        TmpString = TmpString & TmpChar
    End If
End If
Opvullen = TmpStringEnd Function

 

Als we nu in cel A1 een A zetten dan krijgen we met de formule =Opvullen(A1;0;10;1)

A000000000


Opvullen

Deze functie kun je heel gemakkelijk verbouwen om zelf een bewerking op een cel waarde uit te voeren!

De koppelingen in PowerPoint bevatten het complete / absolute pad naar het Excel bestand. Kopiëren en plakken geeft problemen omdat er naar 'oude' bestanden wordt verwezen...
In deze video toon ik hoe je m.b.v. een macro / vba functie het pad kan verwijderen zodat alleen de bestandsnaam in de koppeling overblijft!

Vooraf: Als we via 'Bestand - Koppelingen naar bestanden bewerken' kijken naar de koppeling dan zien we het volledige pad in de koppeling staan...

We gaan een VBA functie / Macro maken om het pad uit alle koppelingen in alle slides te verwijderen!

Stap 1: Toets ALT-F11

Stap 2: Kies boven in het menu voor 'Invoegen - Module'

Stap 3: Voeg de volgende code toe:

Global fso As New FileSystemObject

Public Sub MaakKoppelingenRelatief()
    Dim i As Integer
    Dim sld As Slide, shp As Shape
    For Each sld In ActivePresentation.Slides
        For Each shp In sld.Shapes
            If shp.Type = 10 Then
                Dim path As String, fname As String
                path = shp.LinkFormat.SourceFullName
                fname = GetFilenameFromPath(path)
                shp.LinkFormat.SourceFullName = fname
                 i = i + 1
            End If
        Next
    Next
    If i > 0 Then
        MsgBox "Bijgewerkt: " & CStr(i) & " koppelingen", vbOK
    Else
        MsgBox "Geen koppelingen gevonden.", vbOK
    End If
End Sub
Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Stap 4: Wat we nog wel even moeten doen is in het menu bij 'Extra - Verwijzingen' de Microsoft Scripting Runtime aanvinken. Dit omdat we FileSystemObject gebruiken!
Blader naar beneden door de lijst en selecteer Microsoft Scripting Runtime

Stap 5: Terug in de presentatie kiezen we nu voor 'ALT-F8 - Maak koppelingen relatief - Uitvoeren'

Kijken we nu bij 'Bestand - koppeling naar bestanden bewerken' dan zien we dat het pad is verdwenen!!

 

Pagina 3 van 7

Excel Software Shop

Web Analytics