vba - Combinations of 4-digit numbers whose individual digits sum to 5

问题描述:

I'm working on a scorecard at work which has columns representing 4 possible outcomes, for example:

Successful,

unsuccessful,

Exceptional,

Other

Each staff member is assessed 5 times in the month against those ratings. So 1 person might have 3 successful, 2 exceptional, 0 unsuccessful, 0 other.

So the max instances of each outcome is 5 but the total sum of instances can't be more than 5.

I could try to type out all the combinations (and get them wrong) - is there any function/formula/VBA that anyone knows of that will list out all of the possible combinations of outcomes for me?

e.g. 5000,4100,4010,4001,3200,3020,3002,3110,3011, etc...

网友答案:

Since your numbers can range from 0005 to 5000, you could just write a simple loop that tests each number to see if the digits total 5:

Sub GetPermutations()
    Dim i As Long
    For i = 5 To 5000
        If SumDigits(i) = 5 Then Debug.Print Format$(i, "0000")
    Next
End Sub

Function SumDigits(s As Variant) As Long
    Dim i As Long
    For i = 1 To Len(s)
        SumDigits = SumDigits + CLng(Mid$(s, i, 1))
    Next
End Function

Alternatively:

Dim w As Long, x As Long, y As Long, z As Long
For w = 0 To 5
For x = 0 To 5
For y = 0 To 5
For z = 0 To 5
    If w + x + y + z = 5 Then Debug.Print w & x & y & z
Next
Next
Next
Next

网友答案:

c#:

// numbering 1 to 4 - i.e.
// 1 sucessful, 2 unsucessful, 3 exception, 4 other
for(int i=1;i<4;i++)
{
 for(int n=1;n<4;n++)
 {
  for(int d=1;d<4;d++)
  {
   for(int q=1;q<4;q++)
   {
    if(i+d+n+q<=5)
     Console.WriteLine(i+n+d+q+", ");
   }
  }
 }
}

EDIT: just saw you asked for vba:

For number1 As Integer = 1 To 4 Step 1
 For number2 As Integer = 1 To 4 Step 1
  For number3 As Integer = 1 To 4 Step 1
   For number4 As Integer = 1 To 4 Step 1
    if(number1+number2+number3+number4<=5) Then
     Debug.WriteLine(number1.ToString & number2.toString &number3.toString &number4.ToString & ",");
    End If
   Next number4
  Next number3
 Next number2
Next number1

The most simple solution I could think of, there's probably better though.

网友答案:

Thanks for your help everyone - i managed to use a combination of suggestions by starting with the number 1, auto-filling down to 5000, then "text to columns", fixed width to separate the digits, sum to 5, filter and hey presto! Seems to have produced the right number of possible combinations adding up to 5.

Thanks again for your help!

分享给朋友：

您可能感兴趣的文章：

随机阅读：

栏目列表

推荐内容

热点内容

动力学知识库

vba - Combinations of 4-digit numbers whose individual digits sum to 5