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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.