Excel arrange Functions Permut

Source: Internet
Author: User
Tags prepare

First, Permut grammar

Permut (Number,number_chosen)

Number is a positive integer that represents the count of objects.

Number_chosen a positive integer representing the number of objects in each arrangement.

Parameter description:

If two arguments are not integers, they will be truncated to rounding.

If number or Number_chosen is a non-numeric type, the function Permut returns an error value #VALUE!.

If number≤0 or Number_chosen < 0, the function Permut will return an error value #NUM!.

If number < Number_chosen, the function Permut returns the error value #NUM!.

The function is consistent with the mathematical arrangement, except that the function only returns the number of permutations without giving a specific arrangement.

Ii. Examples of Permut

For example, out of 3 players, select two players to queue, there are several queuing methods, this is the arrangement, and order, the order is different, it is considered as a different arrangement.

We can use the formula: =permut (3,2) to produce the result, 6

We now use a manual arrangement to understand how this function is arranged, assuming that the names of the three players are a, B, C, respectively.

So, there are six ways to arrange the scheme:

1, A,b 2, A,c 3, B,c 4, B,a 5, C,a 6, C,b

Note that the function only returns the number of permutations and does not give a specific arrangement.

If you want to understand the arrangement, you can only use VBA to implement it, see Code:

For I =1 to 6

For II = 2 to 7

For III = 3 to 8

For IIII = 4 to 9

Cells (k+1,1) = i & II & III & IIII

K = k+1

Next Iiii,iii,ii,i

The function of this code is, from 9 objects inside, any choice 4 to arrange the scheme.

Code is used in a VBA environment, and the arranged scheme is automatically populated in the cells in the current worksheet.

Next, I'll show you the rest of the VBA code:

Sub Permutation Example 1 ()

Dim A (1 to 7) as String ' the character to be sorted

Dim results (1 to 7) as String ' staging result

Dim I as Integer ' loop variable

Set rescol = New Collection ' initialization result set

A (1) = "A" initializes the character to be arranged

A (2) = "B"

A (3) = "C"

A (4) = "D"

A (5) = "E"

A (6) = "F"

A (7) = "G"

Insert result, a ' arrange

Sheets (1). Columns ("A:a"). ClearContents ' Clean up the result position, prepare output result

For i = 1 to Rescol.count ' outputs the results because in Excel, so the output to the cell

Sheets (1). Cells (i, 1) = Rescol (i) ' If not in Excel, can output to where needed

Next

End Sub

Sub Permutation Example 2 ()

Dim A () as String ' to arrange characters

Dim result () as String ' staging results

Dim total as Integer ' how many characters

Dim I as Integer ' loop variable

Total = Sheets (2). Cells (1, 1) ' Gets the total number of characters

If Total > Num Then

MsgBox "Too many characters, out of program design"

Exit Sub

End If

Set rescol = New Collection ' initialization result set

ReDim A (1 to total) ' redefine the array based on the number of characters

ReDim result (1 to total)

For i = 1 to Total

A (i) = CHR (i + 64) ' Initialize the character to be arranged

Next I

Insert result, a ' arrange

Sheets (2). Columns ("B:b"). ClearContents ' Clean up the result position, prepare output result

For i = 1 to Rescol.count ' outputs the results because in Excel, so the output to the cell

Sheets (2). Cells (i, 2) = Rescol (i) ' If not in Excel, can output to where needed

Next

End Sub

Related Keywords:
Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth \$300-1200 USD