2017年6月11日日曜日

Excel VBAで数学 8

 Excel VBAを使った簡単な数学プログラミングの続きです。

 今回は組み合わせの計算です。
 学校の数学でさんざん考えた記憶がありますが、コンピュータにやらせるとあっさりしたものです。

 ソースコードは、ご自由にご利用ください。ただし、趣味のプログラムなので、保証はありません。

Option Explicit

Dim row As Long


Public Sub Macro()
    Dim N As Long
    Dim m As Long
    Dim num() As Byte
   
    row = 1
   
    N = 5
    m = 3
    ReDim num(m - 1) As Byte
    'nHm num, N, m, 0
    'nPm num, N, m, 0
    nCm num, N, m, 0
End Sub

'nHm
'重複あり組み合わせ
Public Sub nHm(num() As Byte, N As Long, m As Long, pos As Long)
    Dim i As Long
   
    If pos < m Then
        For i = 0 To N - 1
            num(pos) = i + 1
            nHm num, N, m, pos + 1
        Next i
    ElseIf pos = m Then
        For i = 0 To m - 1
            Sheet1.Cells(row, i + 1) = num(i)
        Next i
       
        row = row + 1
    End If
End Sub

'nPm
'順列 (permutation)
Public Sub nPm(num() As Byte, N As Long, m As Long, pos As Long)
    Dim i As Long
    Dim j As Long
   
    If pos < m Then
        For i = 0 To N - 1
            For j = 0 To pos - 1
                If i + 1 = num(j) Then Exit For
            Next j
           
            If j = pos Then
                num(pos) = i + 1
                nPm num, N, m, pos + 1
            End If
        Next i
    ElseIf pos = m Then
        For i = 0 To m - 1
            Sheet1.Cells(row, i + 1) = num(i)
        Next i
       
        row = row + 1
    End If
End Sub

'nCm
'組み合わせ (combination)
Public Sub nCm(num() As Byte, N As Long, m As Long, pos As Long)
    Dim i As Long
    Dim j As Long
   
    If pos < m Then
        For i = 0 To N - 1
            For j = 0 To pos - 1
                If i + 1 <= num(j) Then Exit For
            Next j
           
            If j = pos Then
                num(pos) = i + 1
                nCm num, N, m, pos + 1
            End If
        Next i
    ElseIf pos = m Then
        For i = 0 To m - 1
            Sheet1.Cells(row, i + 1) = num(i)
        Next i
       
        row = row + 1
    End If
End Sub

0 件のコメント:

コメントを投稿