To select random data from a table, you need to use function in Excel "Random Numbers". It's ready generator random numbers in Excel. This feature is useful when performing a spot check or when conducting a lottery, etc.
So, we need to hold a drawing of prizes for buyers. Column A contains any information about buyers - first name, or last name, or number, etc. In the column in we set the function of random numbers. Select cell B1. On the "Formulas" tab in the "Function Library" section, click on the "Math" button and select the "RAND" function from the list. You do not need to fill in anything in the window that appears. Just click on the "OK" button. Copy the formula by column. It turned out like this.This formula puts random numbers less than zero. For random numbers to be greater than zero, you need to write the following formula. =RAND()*100
When you press the F9 key, the random numbers change. You can choose each time from the list of the first buyer, but change random numbers with the F9 key.
Random number from rangeExcel.
To get random numbers in a certain range, set the function "RANDBETWEEN" to mathematical formulas. Set the formulas in column C. The dialog box is filled in like this.
We indicate the smallest and most big number. It turned out like this. You can use formulas to select from a list with random numbers the names and surnames of buyers.
Attention! In the table, random numbers are placed in the first column. We have such a table.
In cell F1, we write a formula that will transfer the smallest random numbers.
=SMALL($A$1:$A$6,E1)
We copy the formula to cells F2 and F3 - we select three winners.
In cell G1 we write the following formula. She will choose the names of the winners at random from column F. =VLOOKUP(F1;$A$1:$B$6;2;0)
This is the table of winners.

If you need to select winners in several nominations, then press the F9 key and not only random numbers will be replaced, but also the names of the winners associated with them.
How to disable updating random numbers inExcel.
To prevent the random number from changing in the cell, you need to write the formula manually and press the F9 key instead of the Enter key so that the formula is replaced by a value.
In Excel, there are several ways to copy formulas so that the links in them do not change. See description simple ways such copying in the article "

Good day, dear reader!

Recently, there was a need to create a kind of random number generator in Excel within the boundaries desired task, and it was simple, taking into account the number of people, choose a random user, everything is very simple and even banal. But I was interested, what else can be done with the help of such a generator, what they are, what their functions are used for this and in what form. There are many questions, so I will gradually answer them.

So, what exactly can we use this mechanism for:

• First of all: we can, for testing formulas, fill in the range we need with random numbers;
• Secondly: to form questions of various tests;
• third: for any random distribution of tasks in advance among your employees;
• fourthly: for simulation of various processes;

…… and in many other situations!

In this article, I will consider only 3 options for creating a generator (macro capabilities, I will not describe), namely:

Create a random number generator using the RAND function

With the RAND function, we are able to generate any random number in the range from 0 to 1 and this function will look like this:

=RAND();

If the need arises, and it most likely does, use a random number of great importance, you can simply multiply your function by any number, for example 100, and get:

=RAND()*100;
But if you don't like fractional numbers or just need to use integers, then use this combination of functions, it will allow you after the comma or just discard them:

=ROUND((RAND()*100);0);

=SELECT((RAND()*100),0)
When it becomes necessary to use a random number generator in some specific, specific range, according to our conditions, for example, from 1 to 6, you must use the following construction (be sure to secure the cells with):

=RAND()*(b-a)+a, where,

• a - represents lower bound,
• b - upper limit

and full formula will look: =RAND()*(6-1)+1, and without fractional parts you need to write: = REV(RAND()*(6-1)+1;0)

Create a random number generator using the RANDBETWEEN function

This function is simpler and started to please us in the basic configuration of Excel, after the 2007 version, which made it much easier to work with the generator when you need to use a range. For example, to generate a random number in the range from 20 to 50, we will use the following construction:

=RANDOMBETWEEN(20,50).

Create a generator using the AnalysisToolPack add-in

In the third method, no generation function is used, but everything is done using the add-on Analysis Tool Pack(This add-in is included with Excel). The tool built into the spreadsheet editor can be used as a generation tool, but you need to know if you want to change the set of random numbers, then you need to restart this procedure.

To gain access to this undeniably useful add-on, you first need to use the dialog box "Add-ons" install this package. If you already have it installed, then the matter is small, select the menu item "Data" - "Analysis" - "Data Analysis", select in the list offered by the program and click "OK".

In the window that opens, we select the type in the menu "Distribution", then specify additional parameters that change based on the type of distribution. Well, the final step is an indication "Exit Interval", exactly the interval where your random numbers will be stored.

And that's all I have! I really hope that the question of creating a random number generator, I opened completely and you understand everything. I would be very grateful for the comments left, as this is an indicator of readability and inspires me to write new articles! Share with your friends read and like!

Don't think too much. So you create problems that were not there in the first place.

Friedrich Nietzsche

We have a sequence of numbers, consisting of almost independent elements that obey given distribution. Usually evenly distributed.

You can generate random numbers in Excel in different ways and ways. Let's take a look at the best of them.

Random Number Function in Excel

1. The RAND function returns a random uniformly distributed real number. It will be less than 1, greater than or equal to 0.
2. The RANDBETWEEN function returns a random integer.

Let's look at their use with examples.

Selecting random numbers with RAND

This function does not require any arguments (RAND()).

To generate a random real number between 1 and 5, for example, use the following formula: =RAND()*(5-1)+1.

The returned random number is evenly distributed over the interval .

Each time the worksheet is calculated, or the value in any cell in the worksheet changes, a new random number is returned. If you want to save the generated population, you can replace the formula with its value.

1. We click on a cell with a random number.
2. Highlight the formula in the formula bar.
3. Press F9. AND ENTER.

Let's check the uniformity of the distribution of random numbers from the first sample using the distribution histogram.

The range of vertical values ​​is frequency. Horizontal - "pockets".



RANDBETWEEN function

The syntax of the RANDBETWEEN function is (lower bound; upper bound). The first argument should be less than a second. Otherwise, the function will throw an error. The bounds are assumed to be integers. The formula discards the fractional part.

An example of using the function:

Random numbers with 0.1 and 0.01 precision:

How to make a random number generator in Excel

Let's make a random number generator with the generation of a value from a certain range. We use a formula like: =INDEX(A1:A10;INTEGER(RAND()*10)+1).

Let's make a random number generator in the range from 0 to 100 with a step of 10.

From the list text values you need to choose 2 random. Using the RAND function, we compare text values ​​in the range A1:A7 with random numbers.

Let's use the INDEX function to select two random text values ​​from the original list.

To choose one random value from the list, apply the following formula: =INDEX(A1:A7,RANDBETWEEN(1,COUNT(A1:A7))).

Normal Distribution Random Number Generator

The functions RAND and RANDBETWEEN produce random numbers with a single distribution. Any value with the same probability can fall into the lower limit of the requested range and into the upper one. It turns out a huge spread from the target value.

Normal distribution means that most of the generated numbers are close to the target. Correct the RANDBETWEEN formula and create an array of data with normal distribution.

The cost of goods X is 100 rubles. The entire batch produced is subject to a normal distribution. The random variable also follows a normal probability distribution.

Under such conditions, the average value of the range is 100 rubles. Let's generate an array and build a graph with a normal distribution for standard deviation 1.5 rubles.

We use the function: =NORMINV(RAND();100;1.5).

Excel calculated which values ​​are in the range of probabilities. Since the probability of producing a product with a cost of 100 rubles is maximum, the formula shows values ​​​​close to 100 more often than the rest.

Let's move on to plotting. First you need to create a table with categories. To do this, we divide the array into periods:

Based on the data obtained, we can form a diagram with a normal distribution. The value axis is the number of variables in the interval, the category axis is the periods.

Random numbers are often useful in spreadsheets. For example, you can populate a range with random numbers to test formulas, or generate random numbers to simulate a variety of processes. Excel provides several ways to generate random numbers.

Using the RAND function

Presented in excel function RAND generates a uniform random number between 0 and 1. In other words, any number between 0 and 1 has equal probability be returned by this function. If you need random numbers with large values, use a simple formula multiplication. The following formula, for example, generates a uniform random number between 0 and 1000:
=RAND()*1000 .

To limit the random number to integers, use the function ROUND:
=ROUND((RAND()*1000);0) .

Using the RANDBETWEEN function

To generate uniform random numbers between any two numbers you can use the function RANDOMBETWEEN. The following formula, for example, generates a random number between 100 and 200:
=RANDOMBETWEEN(100,200) .

In versions earlier than Excel 2007, the function RANDOMBETWEEN only available during installation additional package analysis. For compatibility with previous versions (and to avoid using this add-on), use a formula where a represents the bottom, a b- upper limit: =RAND()*(b-а)+а. To generate a random number between 40 and 50, use the following formula: =RAND()*(50-40)+40 .

Using the Analysis ToolPack Add-in

Another way to get random numbers in a worksheet is to use the add-on Analysis Tool Pack(which came with Excel). This tool can generate non-uniform random numbers. They are not generated by formulas, so if you need a new set of random numbers, you must restart the procedure.

Get access to the package Analysis Tool Pack by selecting Data Analysis Data analysis. If this command is missing, install the package Analysis Tool Pack using the dialog box add-ons. The easiest way to call it is to press Atl+TI. In the dialog box Data analysis select Random number generation and press OK. The window shown in Fig. 130.1.

Select a distribution type from the dropdown list Distribution, and then set additional options (they change depending on the distribution). Don't forget to set the parameter output interval, which stores random numbers.

Function RAND() returns a uniformly distributed random number x, where 0 £ x< 1. Вместе с тем путем несложных преобразований с помощью функции RAND() you can get any random real number. For example, to get a random number between a and b, it is enough to enter the following formula in any cell of the Excel table: =RAND()*( b-a)+a .

Note that since Excel 2003 the function RAND() has been improved. It now implements the Wichman-Hill algorithm, which passes all standard tests for randomness and guarantees that repetition in a combination of random numbers will begin no earlier than 10 13 generated numbers.

Random number generator in STATISTICA

To generate random numbers in STATISTICA, double-click in the data table (in which the generated numbers are supposed to be written) on the variable name. In the variable specification window, click the button Functions. In the window that opens (Fig. 1.17) select Math and select a function Rnd .

RND(X ) - generation of uniformly distributed numbers. This function has only one parameter - X , which specifies the right boundary of an interval containing random numbers. In this case, 0 is the left border. To fit in general form functions RND (X ) into the variable specification window, just double-click on the function name in the window Function Browser . After specifying numerical value parameter X you have to press OK . The program will display a message about the correct spelling of the function and ask for confirmation about recalculating the value of the variable. After confirmation, the corresponding column is filled with random numbers.

Task for independent work

1. Generate rows of 10, 25, 50, 100 random numbers.

2. Calculate descriptive statistics

3. Build histograms.

What conclusions can be drawn about the type of distribution? Will it be balanced? How does the number of observations affect this conclusion?

Lesson 2

Probability. Simulation of a complete group of events

Lab #1

Laboratory work is an independent study with subsequent defense.

Lesson objectives

– Formation of stochastic modeling skills.

– Understanding the essence and connection of the concepts "probability", "relative frequency", "statistical definition of probability".

– Experimental verification of the properties of probability and the possibility of calculating the probability random event by experience.

- Formation of skills to study phenomena that have a probabilistic nature.

The events (phenomena) observed by us can be divided into the following three types: reliable, impossible and random.

credible An event is called an event that will definitely occur if a certain set of conditions is met. S.

Impossible An event is called an event that is certain not to happen if a set of conditions is met. S.

Random An event is called an event that, under the implementation of a set of conditions S, can either occur or not occur.

The subject of probability theory is the study of probabilistic regularities of mass homogeneous random events.

Events are called incompatible if the occurrence of one of them excludes the occurrence of other events in the same trial.

Several events form full group , if at least one of them appears as a result of the test. In other words, the occurrence of at least one of the events of the complete group is a certain event.

Events are called equally possible if there is reason to believe that none of these events is more possible than the others.

Each of the equally likely test results is called elementary outcome.

The classic definition of probability is: event probability BUT is the ratio of the number of outcomes favorable to this event to the total number of all equally possible incompatible elementary outcomes that form a complete group.

BUT is defined by the formula ,

where m- the number of elementary outcomes that favor the event BUT, n- the number of all possible elementary outcomes of the test.

One of the shortcomings of the classical definition of probability is that it does not apply to trials with an infinite number of outcomes.

Geometric definition probability generalizes the classical to the case an infinite number elementary outcomes and represents the probability of a point falling into an area (a segment, a part of a plane, etc.).

So the probability of an event BUT is defined by the formula , where is the measure of the set A(length, area, volume); is a measure of the space of elementary events.

Relative frequency, along with probability, belongs to the basic concepts of probability theory.

Relative event frequency called the ratio of the number of trials in which the event occurred to the total number of trials actually performed.

Thus, the relative frequency of the event BUT is determined by the formula , where m is the number of occurrences of the event, n – total number tests.

Another disadvantage of the classical definition of probability is that it is difficult to indicate the grounds for considering elementary events as equally probable. For this reason, along with classic definition also enjoy statistical definition probabilities, taking the relative frequency or a number close to it as the probability of an event.

1. Simulation of a random event with probability p.

Random number is generated y y≤ p, then event A has occurred.

2. Simulation of a complete group of events.

We enumerate the events that form a complete group by numbers from 1 to n(where n- the number of events) and make a table: in the first line - the number of the event, in the second - the probability of occurrence of an event with the specified number.

Event number			…	j	…	n
Event Probability			…		…

Let's split the segment into axes Oy points with coordinates p 1 , p 1 +p 2 , p 1 +p 2 +p 3 ,…, p 1 +p 2 +…+p n-1 on n partial intervals Δ 1 , Δ 2 ,…, Δ n. In this case, the length of the partial interval with the number j is equal to the probability pj.

Random number is generated y, evenly distributed on the segment . If a y belongs to the interval Δ j, then the event A j has come.

Laboratory work No. 1. Experimental calculation of probability.

Goals of the work: modeling random events, study properties statistical probability events depending on the number of trials.

Lab work we will do it in two stages.

Stage 1. Simulating a symmetrical coin toss.

Event A consists in the loss of the coat of arms. Probability p events A equals 0.5.

a) It is required to find out what the number of trials should be n, so that with a probability of 0.9 the deviation (by absolute value) relative frequency appearance of the coat of arms m/n from probability p= 0.5 did not exceed the number ε > 0: .

Carry out calculations for ε = 0.05 and ε = 0.01. For calculations, we use the corollary from integral theorem Moivre-Laplace:

Where ; q=1-p.

How are the values ​​related? ε and n?

b) Conduct k= 10 episodes n tests in each. In how many series is the inequality fulfilled and in how many it is violated? What will be the result if k→ ∞?

Stage 2. Modeling the implementation of the outcomes of a random experiment.

a) Develop an algorithm for modeling the implementation of experience with random outcomes according to individual assignments(see appendix 1).

b) Develop a program (programs) for modeling the implementation of the outcomes of the experiment a certain finite number of times, with the obligatory preservation of the initial conditions of the experiment and for calculating the frequency of occurrence of the event of interest.

c) Compile a statistical table of the dependence of the frequency of occurrence given event from the number of experiments.

d) According to the statistical table, construct a graph of the dependence of the frequency of an event on the number of experiments.

e) Compile a statistical table of deviations of the event frequency values ​​from the probability of occurrence of this event.

e) Reflect the obtained tabular data on the graphs.

g) Find the value n(number of trials) to and .

Draw conclusions from work.