EXCEL random function instance

 

Excel random function instance

ExcelHow to randomly select non-repeated numbers in: ExcelContainsRandom FunctionsRand (), but Rand () only select 0 ~ A random number between 1, and the two numbers are not necessarily repeated. Therefore, the rank function is used. 1. Assume that ~ 5 distinct numbers are randomly selected in 100. 2. In A1 ~ Enter = rand () in a100 (). In A1 ~ 100 numbers of 0 to are randomly selected in a100 ~ A random number between 1. 3. In C1, enter = rank (A1, A: a); In C2, enter = rank (A2, A: a); In C3, enter = rank (A3,: a )...... And so on. ; Target cell C1 ~ C5. Actually, C1 ~ A1 ~ is displayed in C5 ~ A5 is sorted among 100 random numbers. 4. 1 ~ has been extracted successfully ~ Five random numbers in 100. Press Ctrl + R to refresh. 1. generate random numbers (1) It is relatively simple to generate a random number. If it is equal to Rand (), a random number between 0 and 1 is generated; (2) If it is an integer, use = int (RAND () * 10 to represent an integer ranging from 0 to 9, and so on; (3) To generate a random real number between A and B, use = rand () * (B-A) +, if you want an integer, use = int (RAND () * (B-a) + A; to slightly expand it, You can generate a fixed-digit integer. Note: If you want to use the rand () function to generate a random number that does not change with cell computing, you can enter "= rand ()" in the editing column to maintain the editing status, then press F9 to permanently change the formula to a random number. However, this can only be changed permanently one by one. If there are too many numbers, you can select all and paste them at a proper position by right-clicking, select "select Paste" and then select "value" to copy the value generated by the previously copied random number formula (instead of the formula! 2. generate random letters Random lowercase letters: = char (INT (RAND () * 26) + 97) Random uppercase letters: = char (INT (RAND () * 26) + 65) Random uppercase/lowercase mixed letters: = char (INT (RAND () * 26) + If (INT (RAND () * 2) =, 97 )) 3. Generate a random mix of six-digit letters and numbers = CONCATENATE (IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 9 + 48), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 25 + 65), CHAR (INT (RAND () * 25 + 97), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 9 + 48), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 25 + 65), CHAR (INT (RAND () * 25 + 97), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 9 + 48), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 25 + 65), CHAR (INT (RAND () * 25 + 97), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 9 + 48), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 25 + 65), CHAR (INT (RAND () * 25 + 97), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 9 + 48), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 25 + 65), CHAR (INT (RAND () * 25 + 97), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 9 + 48), IF (INT (RAND () * 2) = 0, CHAR (INT (RAND () * 25 + 65), CHAR (INT (RAND () * 25 + 97 ))))) 4. Generation of random, non-repeating numeric sequences In some cases, we need to generate a random sequence that is not repeated. For example, we want to simulate shuffling, and remove a deck of playing cards and then disrupt the remaining 52. The stupid method is to check whether a random number exists after each random number is generated between 1 and 52. If this is the first occurrence, it is placed in the sequence; otherwise, a random number is generated again for the check. In ExcelUsing this method in the worksheet will cause multi-layer if nesting, which is difficult to bother with. It is simpler to do in the VBA, but the efficiency is too low. The more it gets to the back end of the sequence, the worse the efficiency is. Of course, there is also a better way. In VBA, assign a (1)-a (52) to 1-52 respectively, and then perform 52 cycles, for example, the second generation of s is a random number r between 1 and 52, which swaps a (s) with a (r). In this way, the original sequence is disrupted and a non-repeated random sequence is obtained. This algorithm in VBA is very easy to implement. However, for the sake of universality and security, sometimes we do not want to use VBA. Let's look at how to use the built-in function in worksheet to implement this function. (1) Fill in "= INT (RAND () * 52) + 1" between the A1-A52 to generate a random number between 1 and 52. Note that there are duplicates here. (2) Fill in 1-52 between the B1-B52 (3) Fill in the C54-BB54 1-52 (4) Fill in "= IF (ROW () = C $54, INDEX (B $1: B $52, INDEX ($ A $1: $ A $52, C $54), IF (ROW () = INDEX ($ A $1: $ A $52, C $54), INDEX (B $1: B $52, C $54), B1 ))". Sub-item explanation: A: ROW () = C $54. If the current ROW is equal to the sequence number of the current switch B: INDEX (B $1: B $52, INDEX ($ A $1: $ A $52, C $54), return to select A1 from B1 to B52: the C54 value in A52 C: IF (ROW () = INDEX ($ A $1: $ A $52, C $54), otherwise, IF the current ROW is equal to A1: the C54 value in A52, then: D: INDEX (B $1: B $52, C $54), return the C54 value in B1: B52 E: If none of the above conditions is met, return B1 (5) Copy C1 to the area C1: BA52. (6) In BA1: BA52, we obtain a random sequence that is not repeated. A new sequence can be generated by F9. Generate a random six-digit password = INT (RAND () * (899999-10001) + 100001 EXCELThe first two digits are uppercase letters, the middle four digits are lowercase letters, and the last two digits are numbers. = CHAR (65 + INT (RAND () * 16) & CHAR (65 + INT (RAND () * 16) & CHAR (97 + INT (RAND () * 16) & CHAR (97 + INT (RAND () * 16) & CHAR (97 + INT (RAND () * 16 )) & CHAR (97 + INT (RAND () * 16) & INT (RAND () * 10) & INT (RAND () * 10)