How to operate the number of unique data in an Excel table

php editor Banana will introduce to you how to operate the number of unique data in Excel tables. In Excel, counting the number of unique data is a common requirement. Through simple operations, the number of different values ​​in the table can be quickly and accurately obtained. Below we will introduce in detail how to use Excel's built-in functions or filtering functions to achieve this goal, allowing you to easily process data and improve work efficiency.

1. Use automatic filtering

Use [Ctrl Shift L] to [automatically filter] the table.

Click on the product column and count how many products there are.

If the product type is relatively single, this is a quick way.

However, if there are a lot of products, then the workload will soar if you have to count them one by one.

2. Delete Duplicates

With the help of the [Delete Duplicates] function in the [Data] tab, you can also easily search for product categories.

First copy the product column data behind the original table.

Here you can use the shortcut operation [Alt A M] to open [Delete Duplicates] more quickly.

However, using this method requires repeating the operation every time the data is updated, which is also very time-consuming.

3. Conditional statistics

You can also use the [countif function].

Also add auxiliary columns after the original table.

Enter the formula in the second row of the auxiliary column: COUNTIF($C$2:C2,C2)

In the first parameter range of this formula, the starting point needs to be locked, and the end point does not need to be locked. Locked, copied to all rows.

You can see in the auxiliary column how many times the product appears in the table.

Use another formula: COUNTIF(H:H,1) to calculate the quantity of the product.

4. With the help of array function

In the cell, enter the formula: SUM(1/COUNTIF(C2:C13,C2:C13))

Press [Ctrl Shift Enter] to end the formula entry, and you can get the product category in one step.

Note: The formula ending with Ctrl Shift Enter is an [array formula]. You can see in the cell edit bar that curly brackets will be automatically added outside the formula.

The advantage of this method is that the results can be obtained directly without the need to build auxiliary columns.

The above is the detailed content of How to operate the number of unique data in an Excel table. For more information, please follow other related articles on the PHP Chinese website!