VBA Excel中的排列 [英] Permutations in VBA Excel

问题描述

我正在尝试生成字符数组的所有可能组合.输入数组具有n个字符，5 <= n <= 7，并且我想生成包含所有C(n，5)组合的第二个数组A(C(n，5)，5).数组中字符的顺序并不重要.

I am trying to generate all the possible combinations of an array of characters. The input array has n characters, 5 <= n <= 7, and I would like to generate a second array A( C( n , 5 ) , 5 ) that contains all the C( n , 5 ) combinations. The order of the characters in the array isn't important.

这里是一个示例: 输入数组:{A，B，C，D，E，F}，所以n = 6 输出数组应为:

Here is an example: input array: { A, B, C, D, E, F } , so n = 6 output array should be:

{A B C D E},
{A B C D F},
{A B C F E},
{A B F D E},
{A F C D E},
{F B C D E},

对于n = 5和n = 6，这非常简单，但对于n = 7，则变得非常复杂.有人知道我该怎么做吗?

This is pretty simple for n=5 and n=6, but gets very complicated for n=7. Does anyone know how should I make this ?

谢谢

推荐答案

只是找到了一种递归使其避免双重结果的方法.该代码非常丑陋，因为我没有时间考虑如何在此处使用循环.

Just found one way to make it recursively and avoid double results. The code is pretty ugly cause I didn't have time to think how to use the loops here.

公共函数Permutacao(card1作为字符串，card2作为字符串，card3作为字符串，card4作为字符串，card5作为字符串，可选card6作为字符串，可选card7作为字符串)

Public Function Permutacao(card1 As String, card2 As String, card3 As String, card4 As String, card5 As String, Optional card6 As String, Optional card7 As String)

Dim A(1 To 7) As String
Dim aux_A(1 To 7, 1 To 6) As String
Dim aux2_A(1 To 6, 1 To 5) As String
Dim final_A(1 To 42, 1 To 6) As String

n = 5

A(1) = card1
A(2) = card2
A(3) = card3
A(4) = card4
A(5) = card5


If Not IsMissing(card6) Then
    A(6) = card6
    n = 6
End If
If Not IsMissing(card7) Then
    A(7) = card7
    n = 7
End If

If n = 5 Then

    final_A(1, 1) = A(1)
    final_A(1, 2) = A(2)
    final_A(1, 3) = A(3)
    final_A(1, 4) = A(4)
    final_A(1, 5) = A(5)

ElseIf n = 6 Then

    k = 1
    final_A(k, 1) = A(1)
    final_A(k, 2) = A(2)
    final_A(k, 3) = A(3)
    final_A(k, 4) = A(4)
    final_A(k, 5) = A(5)

    k = 2

    final_A(k, 1) = A(1)
    final_A(k, 2) = A(2)
    final_A(k, 3) = A(3)
    final_A(k, 4) = A(4)
    final_A(k, 5) = A(6)

    k = 3

    final_A(k, 1) = A(1)
    final_A(k, 2) = A(2)
    final_A(k, 3) = A(3)
    final_A(k, 4) = A(6)
    final_A(k, 5) = A(5)

    k = 4

    final_A(k, 1) = A(1)
    final_A(k, 2) = A(2)
    final_A(k, 3) = A(6)
    final_A(k, 4) = A(4)
    final_A(k, 5) = A(5)

    k = 5

    final_A(k, 1) = A(1)
    final_A(k, 2) = A(6)
    final_A(k, 3) = A(3)
    final_A(k, 4) = A(4)
    final_A(k, 5) = A(5)

    k = 6

    final_A(k, 1) = A(6)
    final_A(k, 2) = A(2)
    final_A(k, 3) = A(3)
    final_A(k, 4) = A(4)
    final_A(k, 5) = A(5)

ElseIf n = 7 Then

    k = 1
    aux_A(k, 1) = A(1)
    aux_A(k, 2) = A(2)
    aux_A(k, 3) = A(3)
    aux_A(k, 4) = A(4)
    aux_A(k, 5) = A(5)
    aux_A(k, 6) = A(6)

    k = 2
    aux_A(k, 1) = A(1)
    aux_A(k, 2) = A(2)
    aux_A(k, 3) = A(3)
    aux_A(k, 4) = A(4)
    aux_A(k, 5) = A(5)
    aux_A(k, 6) = A(7)

    k = 3
    aux_A(k, 1) = A(1)
    aux_A(k, 2) = A(2)
    aux_A(k, 3) = A(3)
    aux_A(k, 4) = A(4)
    aux_A(k, 5) = A(7)
    aux_A(k, 6) = A(6)

    k = 4
    aux_A(k, 1) = A(1)
    aux_A(k, 2) = A(2)
    aux_A(k, 3) = A(3)
    aux_A(k, 4) = A(7)
    aux_A(k, 5) = A(5)
    aux_A(k, 6) = A(6)

    k = 5
    aux_A(k, 1) = A(1)
    aux_A(k, 2) = A(2)
    aux_A(k, 3) = A(7)
    aux_A(k, 4) = A(4)
    aux_A(k, 5) = A(5)
    aux_A(k, 6) = A(6)

    k = 6
    aux_A(k, 1) = A(1)
    aux_A(k, 2) = A(7)
    aux_A(k, 3) = A(3)
    aux_A(k, 4) = A(4)
    aux_A(k, 5) = A(5)
    aux_A(k, 6) = A(6)

    k = 7
    aux_A(k, 1) = A(7)
    aux_A(k, 2) = A(2)
    aux_A(k, 3) = A(3)
    aux_A(k, 4) = A(4)
    aux_A(k, 5) = A(5)
    aux_A(k, 6) = A(6)

    c = 1

    k = 1

    While k <= 7

        If k < 2 Then
            final_A(c, 1) = aux_A(k, 1)
            final_A(c, 2) = aux_A(k, 2)
            final_A(c, 3) = aux_A(k, 3)
            final_A(c, 4) = aux_A(k, 4)
            final_A(c, 5) = aux_A(k, 5)

            c = c + 1

        End If

        If k < 3 Then


            final_A(c, 1) = aux_A(k, 1)
            final_A(c, 2) = aux_A(k, 2)
            final_A(c, 3) = aux_A(k, 3)
            final_A(c, 4) = aux_A(k, 4)
            final_A(c, 5) = aux_A(k, 6)

            c = c + 1

        End If

        If k < 4 Then

            final_A(c, 1) = aux_A(k, 1)
            final_A(c, 2) = aux_A(k, 2)
            final_A(c, 3) = aux_A(k, 3)
            final_A(c, 4) = aux_A(k, 6)
            final_A(c, 5) = aux_A(k, 5)

            c = c + 1
        End If

        If k < 5 Then

            final_A(c, 1) = aux_A(k, 1)
            final_A(c, 2) = aux_A(k, 2)
            final_A(c, 3) = aux_A(k, 6)
            final_A(c, 4) = aux_A(k, 4)
            final_A(c, 5) = aux_A(k, 5)

            c = c + 1
        End If

        If k < 6 Then

            final_A(c, 1) = aux_A(k, 1)
            final_A(c, 2) = aux_A(k, 6)
            final_A(c, 3) = aux_A(k, 3)
            final_A(c, 4) = aux_A(k, 4)
            final_A(c, 5) = aux_A(k, 5)

            c = c + 1
        End If

        If k < 7 Then

            final_A(c, 1) = aux_A(k, 6)
            final_A(c, 2) = aux_A(k, 2)
            final_A(c, 3) = aux_A(k, 3)
            final_A(c, 4) = aux_A(k, 4)
            final_A(c, 5) = aux_A(k, 5)

            c = c + 1

        End If

        k = k + 1

    Wend


End If

Permutacao = final_A

结束功能

这篇关于VBA Excel中的排列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！