Practical Excel skills sharing: Two magical skills will help you see the secret of counting unique numbers!

How to count the number of unique data? I guess many friends have read a lot of similar articles, but most of them give formulas and explain them a little. At the time, they understood it, but when they encountered problems, they were confused again. In the final analysis, they still didn’t understand the formula. principle. In fact, understanding the principle of this formula is not as difficult as everyone imagines. As long as you know these two magical skills, you can crack the secret of the formula.

# Count the number of unique data. I believe many friends have encountered such a problem at work.

The usual approach is to extract the non-duplicate data first and then count the numbers. The method of extracting non-duplicate data has been shared before. There are basically three methods: advanced filtering, pivot table and deleting duplicates.

In fact, it is very convenient to use formulas to count the number of unique data. For example, in the situation below, you need to count the number of unique customers:

Usually there are two formulas for counting the number of unique data. Today I will share with you the principle of the first formula.

Routine 1: Combination of SUMPRODUCT and COUNTIF

First let’s take a look at the formula input process:

The operation is not difficult, but the difficulty is that many people do not understand the principle of the formula =SUMPRODUCT(1/COUNTIF(B2:B23,B2:B23)).

I understand each function individually, but when combined together I feel confused. I believe this is the feeling shared by many beginners. In fact, it is not as difficult to understand the principle of this formula as everyone thinks. As long as you can use a tool called formula evaluation and a function key called F9, you can crack the secret of the formula. The specific process will be introduced below.

Select the cell where the formula is located and click the Evaluate Formula button.

The usage of this function is very simple, as shown in the figure:

The underlined part indicates that it will be displayed soon. As for the location of the results, you can see from the picture that the first thing to calculate is the COUNTIF(B2:B23,B2:B23) part. Click "Evaluate" to see what results you can get.

We got a set of numbers that represent the number of times each customer code appears. For example, the first 5 means that customer 42337 appears five times. This is also COUNTIF The most basic functionality.

Continue to click "Evaluate" and you can see the results of 1/COUNTIF, as shown in the figure:

## Each number obtained by dividing #1 by COUNTIF is a decimal. For example, if it appears five times, the result is 1/5, which is 0.2.

This step is purely numerical calculation and is not difficult to understand. Click evaluate again to get the final result.

Where did this 5 come from?

This is the most frequently asked question. In fact, to put it bluntly, it is still a mathematics problem.

For example, the customer 42337 appears 5 times in total. This is the result calculated by COUNTIF. Then use 1/ again to get 5 0.2, and then sum up to 1.

This calculation process is the core of the formula. The final summation result of each customer is 1, and the total number of customers is the number of non-duplicate customers.

The above are the steps for using formula evaluation to crack the formula principle. For some experienced drivers, they prefer to use the F9 function key to crack the formula.

It is necessary to note that for some keyboards, you must hold down the Fn key and then press F9.

Let’s talk about how to use F9.

To use F9, you must understand the order of calculations in the formula. In other words, which part you want to know, you must select the corresponding content in the edit bar and press the F9 key.

For example, to understand the COUNIF part, you can do this:

For the use of F9, it is very important to accurately select the calculation content. One more bracket will An error message is displayed. For example, if you accidentally select the last bracket, pressing F9 will prompt you that there is a problem with the formula.

After using F9 to display the results, you can click ✖ on the left side of the editing bar, or press the Esc key to exit. If you accidentally press Enter, you can use Undo or the Ctrl Z key combination to return to the original formula.

Routine 2: Combination of COUNT and MATCH

This formula is a little more difficult, let’s take a look at the operation process.

This formula is an array formula. Remember to press Ctrl Shift and Enter after completing the input. Braces will automatically appear on both sides of the formula.

Formula=COUNT(1/(MATCH(B2:B23,B2:B23,0)=ROW(1:22)))Three functions are used, COUNT, MATCH and ROW, regardless of operation or principle, this formula is more difficult than the first formula.

So why do we need to introduce this formula?

This is because some of the ideas and methods used in this formula will be encountered repeatedly in many powerful formulas. Therefore, understanding the second routine will help improve the ability to use the formula.

Getting back to the topic, let’s use formula evaluation to crack the principle of this formula.

Simply speaking, MATCH has three parameters, search value, search area and search method. What the formula obtains is the position where the search value first appears in the search area. Click to find value to see the result.

Let’s look at customer 42337, which appears five times in total. The results obtained by the MATCH function are all 1, indicating that the position where this customer first appeared is 1.

It should be emphasized that this 1 is the position in the search range, and our search range starts from the second line.

For this set of data obtained by MATCH, you must understand its meaning. Continue point evaluation to get the result of this part of ROW.

ROW can get the row number corresponding to the parameter. For example, ROW(A1), the result is 1, and ROW(1:22), the result is the first 22 rows. Number, that is, the group of numbers 1 to 22.

Note that the range in the formula MATCH(B2:B23,B2:B23,0)=ROW(1:22) is different. MATCH is from 2 to 23 rows, which is actually 22 rows of data, while ROW The range is based on the actual number of rows of data.

Continue to evaluate. The formula will determine whether the set of data obtained by MATCH is consistent with the set of data obtained by ROW. The result is a set of logical values.

It can be found from the results that the formula result is TRUE at the position where each customer first appears.

It is necessary to popularize the knowledge of logical values ​​here.

There are six comparison symbols in Excel, = (equal to), > (greater than), (less than), >= (greater than or equal to), (less than or equal to), (not equal to) , equal to is used in this example.

The result of the comparison is a logical value. There are two logical values, TRUE and FALSE. TRUE means the result is correct, and FALSE means the result is incorrect.

For example, 1>2, the result of this comparison is FALSE.

When logical values ​​are used for addition, subtraction, multiplication, and division with numbers, TRUE is equivalent to 1, and FALSE is equivalent to 0.

In this step of calculation, the number 1 and this set of logical values ​​are used for calculation. When the denominator is TRUE, 1/1 gets 1; when the denominator is FALSE When 1/0 will get the wrong value, the denominator is zero.

Click evaluate to see the result.

If you understand the above principles, the final result will be easy to understand.

Because COUNT only does one thing, counting several numbers. In this set of results, only five 1's are numbers, so the final result is 5.

Many times, 1/ is replaced by 0/. Maybe this is a habit of experts.

When you truly understand the principles of the formula, 1/ and 0/ will no longer be the cause of your troubles.

This concludes the analysis of the principle of the second formula. In this formula, many common skills used in advanced formulas are used, such as using ROW to obtain an array, and using various comparison operations to obtain a set of logical values. , and then obtain some error values ​​through calculation of logical values ​​(error values ​​are not useless at all). There is no difference in most cases except for some special cases whether to use 0/ or 1/.

Okay, the analysis of the two formulas for counting the number of non-duplicate data has come to an end. If you still encounter any formulas that cannot be cracked, you can leave a message and tell the editor, and we will figure it out together.

Related learning recommendations: excel tutorial

The above is the detailed content of Practical Excel skills sharing: Two magical skills will help you see the secret of counting unique numbers!. For more information, please follow other related articles on the PHP Chinese website!