1, we take the generation of random numbers in the range of 1- 10 as an example to describe the method.
Enter the formula = small (if (countif ($ a $1:a1,row($ 1: $1)) = 0, row ($1:).
Then press Ctrl+Shift+Enter to end the array formula.
2. Then, drop the fill formula to cell A 12, that is, fill 1 1 cell. Why should we fill the cell of 1 1 when we only need the number of1? This is because the last number will not change, so to generate 10 random numbers, 1 1 cells need to be filled, and to generate 1 1 random numbers, 12 cells need to be filled. In addition, 65438 in the formula
3. After generating random numbers, every time you press F9 (or other cells for calculation), the random numbers will be refreshed and a group of random numbers will be regenerated. Attention!
4. Well, a set of non-repetitive random numbers has been generated. Now let's look back at how this formula is realized. First look at the first parameter of the small function, which is the digital area. Here, if and Countif functions are used to realize whether the cell area above this cell has the number of the current cell, if so, it will return False, if not, it will return this number, thus generating a series composed of numbers not in the above cell and Flase. The following figure shows the result obtained by pressing F9:
5. The second parameter of the small function is to take the minimum value from this sequence. In the obtained number, random functions rand and row are used to randomly extract a number from this number, so as to get the number in this numerical order in sorting. Through such an array formula, a set of non-repetitive random numbers can be generated.
Method 2: VBA method
1 and VBA are powerful, and many algorithms can be used to realize random numbers. Here is a simple method. First, press Alt+F 1 1 in Excel to open the VBE interface, double-click the worksheet to generate a random number, and enter the code in the blank on the right.
Enter the code as follows:
Sub m()
Range ("A:A"). The "ClearContents" here is column A, and it should be modified as needed.
For i = 1 to 10, there are 10 numbers, which can be modified as needed.
kkk:
Irregular
x = Int(Rnd * 10) + 1
If applied. CountIf(Range("A:A "), x) = 0 then
Cell (i, 1) = x
other
Go to kkk
If ... it will be over.
Next, I
End joint
2, the code principle is very simple, that is, randomly generating numbers. If column A has this number, it will be randomly generated again until there is no such number; Use a loop to generate a set of numbers.
3. Press F5 to execute the code, and you will see that a group of random and non-repetitive numbers are generated in the corresponding column in the corresponding table.