Like and see, form a habit; What is lost is gained.
Wechat search [Yixin Excel] to pay attention to this different we-media person.
In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.
Unconsciously arrived at the end of the year, the New Year will start from tomorrow. As time goes by, I can’t help feeling a lot.
Of course at this time everyone should be looking forward to the year-end bonus, annual meeting bar. Today, I would like to share with you a small lottery game made by Excel. You can draw one person at a time or many people at a time and will not repeat. If you are still worried about how to draw the lottery in the annual meeting, come and learn.
We first to make a time to draw a person, a person to solve three natural problems is not big.
So let’s think about how do we do this lottery game? A raffle is a game in which a random person is drawn from a group of people.
1. First, have a list of employees’ names, which solves the problem of having a lot of people first;
2, with data, we want to find a value from inside, we should first think of LOOKUP and reference class functions, such as: LOOKUP, VLOOKUP, INDIRECT, INDEX, MATCH, OFFSET, FILTER and other functions. This solves the problem of taking one person from a bunch of people;
3. So how to solve the problem of random extraction? So let’s first think about what random class functions are there? For example, RAND, RANDARRAY, and RANDBETWEEN;
So we have the general idea, and then we can pick the function.
RAND function: returns a random number between 0 and 1;
RANDARRAY function: returns a random array of numbers between 0 and 1;
RANDBETWEEN function: returns a random number between two specified numbers;
From the perspective of the three random functions, the RANDBETWEEN function is more in line with our requirements, because we can choose the maximum and minimum values of the two specified numbers required by this function. This returns a random line number of the employee’s name.
It happens that the INDEX function can look up values based on the line number.
As shown above, we enter some employee names in column A and then enter the formula “=INDEX(A:A,RANDBETWEEN(1,12))” in cell C5. Press AND hold F9 to start the lucky draw and the employee name will start to scroll. When you release F9, you will get the winning list. Let’s see the effect.
In this way, the simple function of drawing one person is completed. To draw multiple people, you only need to fill C5 cells down as follows:
After watching the animation carefully, you should find that although the production of three people is good, but there is a repetition, as shown in the picture below.
This is because if you randomly generate an integer between 1 and 12, the worst case scenario will be the 13th time. Obviously, this method has problems with picking more than one person.
Then we can’t use line numbers as random numbers because it’s too easy to repeat. We can add an auxiliary column after the name to generate a random number using the formula “= RAND ()”.
We saw that RAND generated 9 decimal places, and it was almost impossible to see a repeat value.
We can then use the RANK function to RANK the secondary columns, so that we can get the random RANK of each column. Since there is no repetition, this RANK is also used as the row number. So we can use INDEX again. =INDEX(A:A,RANK(B1,B:B))
You only need to adjust the number of cells of the winning person.
Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.
If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.
This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.