Shulou(Shulou.com)11/24 Report--

The title of the original text: "which Excel master figured out this random lottery technique, it's amazing!" "

A year ago, we published an article, "I used Excel to make an annual lottery, and then the boss clicked." The core of this is to get unrepeatable random integers

There are many ways to get unrepeated random integers, iterative computation is too much, and VBA programming is a bit of a pain in the ass.

Today, Xiaohua will share with you some practical function formulas, the awesome ones!

1. The function of RAND function in auxiliary ranking method is to generate random numbers between 0 and 1, so we only need to generate a group of random numbers through RAND function, and then rank them, there is a great probability that a group of unrepeated random integers can be obtained.

Column C-Auxiliary column formula:

= RAND () B2Mui-do not repeat the random integer formula:

= RANK (C2meme, C2RV)) Formula description:

The RAND function can return a random number between 0 and 1

The RANK function returns the ranking of a number in a column of numbers relative to other values.

Therefore, Mr. into a random value, and then calculate the ranking value of each number, you can get a set of random integers.

PS: the formula will be recalculated by pressing the [F9] key, and the ranking group can be refreshed by recalculation to get a new non-repetitive random integer. Other cases in this article are the same.

These two functions are the most common methods for us to take unrepeated random numbers.

It is relatively simple and easy to learn.

However, the fatal Bug of this formula is that the random numbers generated by each RAND function are independent, that is, there is a very small chance that these random numbers are equal to each other, resulting in repeated ranking values. As shown below:

The auxiliary method of RAND function is not rigorous and needs auxiliary columns, so it can only be regarded as an entry-level solution of unrepeated random integers.

Next, Xiaohua to introduce two more rigorous methods, may be more difficult, we focus on understanding the solution ideas, full of practical information, we must insist on watching!

2. The core problem of successive elimination to obtain unrepeated random integers is: how to make the existing numbers no longer appear?

The solution is to remove the numbers from the pool of decimated numbers, and then take random numbers from the remaining numbers, so that a set of unrepeated random integers can be generated.

The following formula is set according to this train of thought.

B3mura-successive elimination formula:

{= SMALL (IF (COUNTIF ($Baud1ROW B2 row ($1 ROW 10)), ROW ($1 ROW ())} PS: this formula contains an array operation. After entering the formula, you need to press the [Ctrl+Shift+Enter] function to operate correctly.

The formula is more complex, we follow my train of thought, further understand the principle of the formula.

B3 cell formula description:

① COUNTIF ($Baud1 B2MagneLow ($1VO10))

The main purpose of this step is to count the values through the Countif function, and then determine whether the last cell value of the current cell has occurred in 1 to 10.

The current cell is B3, and $B$1:B2 is the cell before it. B$1:B2 locks only the starting cell, representing the previous cell from the first cell B1 to the current cell, which covers all decimated integers.

ROW ($1 10) returns an ordered array of 1 to 10 {1: 1, 2, 3, 4, 5, 5, 6, 8, 9, 10}. The COUNTIF function counts whether this array appears in all extracted integers, and returns 1 if it occurs, otherwise 0.

Since only the number 7 appears in $B$1:B2, and cell B2 is currently 7, the COUNTIF array operation returns the array:

{1; 2; 3; 4; 5; 6; 7; 8; 9; 10}

{0; 0; 0; 0; 0; 0; 1; 0; 0}

② IF (①, "", ROW ($1))

As shown in the following figure, the result array {0 / 0 / 0 / 0 / 0 / 0 / 0 / 0 in ① is taken as the logical judgment value, and 1 is equivalent to TRUE,0 and equivalent to FALSE.

According to the logical judgment value of the IF function, TRUE returns an empty "" and FALSE returns the corresponding number.

So the whole formula segment ② completes the replacement of the numbers that have appeared in $B$1:B2 in the ordered array {1 / 2 / 3 / 4 / 5 / 6 / 7 / 9 / 10} with null, realizing successive elimination.

Replace the 7 in the B2 cell with an empty "" so that the subsequent random number does not draw the previous value.

{1; 2; 3; 4; 5; 6; "; 8; 9; 10}

③ {SMALL (②, RANDBETWEEN (1m 12m row ()}

The main purpose of this step is to take numbers at random.

The SMALL function is mainly used to extract the k-th minimum value in the array, ignoring null values.

RANDBETWEEN is responsible for randomly selecting a value as k within a specified range.

To ensure that the probability of each number being extracted is consistent and error-free, the minimum value of k (that is, Bottom) must be equal to 1, and the maximum value (that is, TOP) must always be the same as the number of digits in the ② result array.

In the formula, 12-ROW () is used as the value of TOP. ROW () is used to return the number of rows in the current cell. At B2, 12-ROW () is 10, decreasing 1 line by line and B3 is 9, which happens to be the same as the number of digits remaining in ② {1; 2; 3; 4; 5; 6; "; 8; 9; 10}.

The RANDBETWEEN function randomly takes the number k between 1 and the remaining number 9, then uses the SMALL function to take the number m corresponding to k, and finally obtains an unrepeated random number, and so on, a set of unrepeated random integers can be obtained.

After mastering the random unrepeated integer formula, with a little deformation, we can realize high-end operations such as random lottery with the help of INDEX function. The picture below is a case of using successive elimination method to randomly select five winners.

D3Murt-random lottery formula:

{= INDEX ($Aph2VlRO 11 Magnum small (IF (COUNTIF ($DUB1VOLYD2)), "", ROW (($1MAB1Row10)), RANDBETWEEN (1LRW ())})

3. If the out-of-order residual method can randomly disrupt the numbers in the specified range, then a group of unrepeated random numbers can be obtained by extracting the numbers in the disturbed order.

The following formula is to add any multiple of 10 to the ordered array to disrupt the order of numbers, and then use the LARGE or SMALL function to take values in turn, take the remainder, that is, the final generation of random unrepeated integers.

B2:B11 array formula-- out-of-order remainder method:

{= MOD (LARGE (RANDBETWEEN (ROW (1:10) ^ 0) 10) * 100+ROW (1:10), ROW (1:10))}

PS: this formula is an array formula, and its output is a set of numbers, which needs to occupy B2:B11 cells to display correctly.

Therefore, select the B2:B11 cell, enter the formula, and then press [Ctrl+Shift+Enter]. You can't press [Ctrl+Shift+Enter] to perform an array operation in a single cell like the successive elimination formula, and then drag the fill, remember!

The following is a simple formula explanation.

Description of the formula:

ROW (1:10) ^ 0 takes the ordered array A {1 / 2 / 10 / 4 / 5 / 7 / 9 / 10 to the power of 0, and obtains 10 constant arrays B {1 / 1 / 1 / 1 / 1 / 1 / 1 / 1 / 1 / 1 / 1 / 1 / 1.

PS: in mathematics, any non-zero power of 0 results in 1.

RANDBETWEEN (ROW (1:10) ^ 0pl 10)

The RANDBETWEEN function is used to generate 10 independent random integers from 1 to 10 (repeatable), resulting in a random array C {10 + 10-3-8-4-4-10-5-10-7}.

The array C*100+ROW (1:10) enlarges array C by 100 times and adds the ordered array A to the array D {1001 / 602 / 303 / 804 / 205 / 206 / 508 / 809 / 710}.

The characteristic of array D is that the Mantissa of array D is ordered array A {1 / 2 / 3 / 4 / 5 / 6 / 7 / 9 / 10}, but its size is determined by the random array C {10 / 6 / 3 / 8 / 8 / 4 / 4 / 7}, so that the ordered array An is randomly disturbed.

{= MOD (LARGE (RANDBETWEEN (ROW (1:10) ^ 0) 10) * 100+ROW (1:10), ROW (1:10), 100)} take the largest number from 1st to 10th in turn through the LARGE function, and then take the remainder of 100. each number in the array A {1 / 3 / 4 / 5 / 6 / 7 / 8 / 9 / 10} can be re-stripped out.

But at this time, their order has been rearranged according to the size of the random array C, and the formula calculation result array E {1 / 7 / 4 / 10 / 2 / 8 / 9 / 5 / 3 / 6} is obtained. The array operation fills the resulting array in the B2:B11 cell in turn.

The random grouping problem can be easily solved by using the out-of-order residual formula.

Let's take a brief look at a basic case.

As shown in the figure below, a group of names are randomly divided into two groups.

B2:B11 array formula-- out-of-order remainder method:

{= MOD (MOD (LARGE (RANDBETWEEN (RANDBETWEEN (1:10) ^ 0) 10) * 100+ROW (1:10), ROW (1:10), 2) + 1 & "Group"}

If you learn these three methods, it will not be difficult for everyone not to repeat random values.

4. Finally, there are three function formulas shared by Xiaohua to obtain random unrepeated integers, including:

❶ assisted ranking method:

Build secondary columns through RAND, and then rank using RANK

❷ successive elimination method:

Use IF+COUNTIF to eliminate existing values, and use SMALL to take values randomly

❸ out-of-order residual method

Construct a random array * 100 + ordered array, sort it with LARGE, and then take the remainder by MOD.

Have you learned it? Now you don't have to worry about random team formation in the usual lucky draw and team building.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaohua

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.