Zelfde verjaardag

  • 18/07/2007

Inleiding

Houd je vast voor wat nu volgt, want we duiken de statistiek in! Deze bijdrage met bijhorende VBA-code gaat over de kans dat X mensen uit een groep van Y mensen op dezelfde dag jarig zijn. Wat we nodig hebben is combinaties, permutaties, en faculteiten, hopelijk zit dat niet te ver weg bij jullie. En nu het werk achter de rug is, kan ik jullie verklappen dat het toch niet zo simpel geweest is voor mij, maar het is wel gelukt! (Anders had ik het hier niet gezet hoor…). Drie avonden van berekeningsproblemen en vierkante ogen laten jullie maar simpelweg voorbijgaan, en we gaan meteen over naar de oplossing. Hier is de VBA-code, nog betrekkelijk kort als je het mij vraagt:

VBA-code

Function KansZelfdeVerj(AantPers As Integer, _
    Hoevl As Integer, _
    Optional Inequality As String = "", _
    Optional Schrikkelj As Boolean = False)


' Wim Gielis
' https://www.wimgielis.com


'''''
' Gecustomiseerde functie om de kans te berekenen dat X personen
' uit een groep van Y personen op dezelfde dag jarig zijn.
' Geschreven uit nieuwsgierigheid ;-)
' 09/06/07, revised 10/06/07
'''''

    ' Noot: Elke dag heeft dezelfde kans qua verjaardag:
    ' er wordt abstractie gemaakt van
    ' verschillen qua geboortes per seizoen

    Dim AantDagen As Integer
    Dim j As Integer, K As Integer

    AantDagen = IIf(Schrikkelj, 366, 365)

    Select Case Inequality

        Case "<"

            KansZelfdeVerj = 0

            For j = 0 To Hoevl - 1

                If j <> 1 Then KansZelfdeVerj = _
                    KansZelfdeVerj + KansZelfdeVerj(AantPers, j, "", Schrikkelj)

            Next j

            Exit Function

        Case ">"

            KansZelfdeVerj = 1 - KansZelfdeVerj(AantPers, Hoevl + 1, "<", Schrikkelj)
            Exit Function

    End Select

    Select Case Hoevl

        Case 1

            KansZelfdeVerj = "Nogal onzinnige berekening."

        Case Is > AantPers

            KansZelfdeVerj = "Te weinig personen"

        Case Is < 0

            KansZelfdeVerj = "Kies een positief aantal mensen"

        Case Else:

            If AantPers = 0 And Hoevl = 0 Then

                KansZelfdeVerj = "Nogal onzinnige berekening."
                Exit Function

            End If

            With Application.WorksheetFunction

                If Hoevl = 0 Then

                    KansZelfdeVerj = KansZelfdeVerj + .Permut(AantDagen, AantPers) / (AantDagen ^ AantPers)

                Else

                    For K = 1 To Int(AantPers / Hoevl)

                        KansZelfdeVerj = KansZelfdeVerj + .Combin(AantDagen, K) _
                        * .Permut(AantPers, Hoevl * K) * .Permut(AantDagen - K, AantPers - Hoevl * K) _
                        / ((.Fact(Hoevl) ^ K) * (AantDagen ^ AantPers))

                    Next K

                End If

            End With

    End Select


End Function

Gebruiksinstructies

Hoe werkt het? Open de VBA editor door in Excel te kiezen voor Extra > Macro > Visual Basic Editor (of Alt + F11 te drukken). Doe dan Insert > Module en plak de code die hierboven staat. Sluit het venster. Als je de kans wilt kennen dat 3 mensen uit een groep van 8 dezelfde verjaardag hebben, typ dan in een cel, bv. A1:

=KansZelfdeVerj(8;3)

en druk op Enter. Het resultaat zou er dan moeten staan. Je mag tot 120 personen gaan in de grote groep. Er zijn 2 uitbreidingen, telkens een extra parameter in de functie wordt daarvoor gebruikt. Als je de kans wilt kennen dat 3 mensen OF MEER uit een groep van 8 dezelfde verjaardag hebben, typ dan:

=KansZelfdeVerj(8;2;">")

Let op de 2 en het groter dan-teken: voor 3 of meer mensen typ je dus een 2 en >. Iets analoog doe je voor "minder dan". Als je de kans wilt kennen dat MINDER DAN 3 mensen uit een groep van 8 dezelfde verjaardag hebben, typ dan:

=KansZelfdeVerj(8;3;"<")

Hier typ je dus een 3 en een <. Een tweede uitbreiding zal in de praktijk weinig verschil uitmaken, het is gewoon voor de volledigheid gegeven. Je kan opgeven of je een schrikkeljaar wilt of niet:

=KansZelfdeVerj(8;3;"<";WAAR)

WAAR duidt aan dat je een schrikkeljaar wil hebben, ONWAAR of niets typen leidt tot een normaal jaar van 365 dagen. Snap je de syntax? Als je de kans wilt kennen dat exact 9 mensen uit een groep van 12 dezelfde verjaardag hebben, berekend in een schrikkeljaar, typ:

=KansZelfdeVerj(12;9;WAAR)

Update: tijdens het surfen kwam ik deze pagina tegen. Het bevestigt dat mijn berekeningen juist waren…





Homepage

Rubriek onderdelen

Over Wim

Wim Gielis is Business Intelligence consultant en Excel expert

Andere links