Excel Statistics repeat occurrences

Exams in school is a regular thing, the examiner is usually not fixed, the number of examinations, the workload of the examination of the statistics on enough statisticians to worry about. To know, the allowance is issued according to the number of examiners, the wrong, but I am sorry to the teachers of the Labor yo! I used to be "traditional" stupid method, the point of the Proctor to arrange the table counting, dizziness, brain swelling, not to say, a little inattentive also easy to make mistakes. Until the search in Excel to retrieve, found two functions, only to be completely out of the abyss of misery, once and for all.

The first step, because you want to use Excel for statistics, the supervisor data in the Proctor schedule is copied to Excel. In fact, Excel to make the form of the function is also quite good, may wish to use Excel to arrange the scheduling of the Proctor system directly. This example stores the Proctor data in Sheet1. As shown in Figure 1.

In the second step, insert a new worksheet and rename it to "statistics." Enter a list of all possible teacher lists, such as column A.

In the third step, enter the formula in the adjacent cell to the right of a person name, for example, the name in the B3 cell is "John", then enter "=countif (Sheet1!$1:$65536,concatenate (" * ", B3," * ") in the C3)" (You can also use the formula panel to enter )。 Here you use the COUNTIF and concatenate two functions, the first is to calculate the number of cells in a range that meet the given criteria, and the second is the condition, which is the string that contains the name in the B3. The result of the calculation appears after the carriage return. Click the C3 cell to see the formula bar, as shown in Figure 2.

In step fourth, you copy the formula to a cell that corresponds to another name, or you can drag the fill with the fill handle. As you can see, the number of times the supervisor is automatically shown in the cell following each name. As shown in Figure 3.

Here are two places to note: The reference area is to use an absolute application so that the formula error does not occur when the population is populated, and secondly, to use the concatenate to add a wildcard * number to both before and after the name you are looking for so that the name appears in a cell where it can be counted.

Of course, it is also very important that the examination arrangements and the number of examiners in the name must be identical, otherwise, can be undone.