Excel's UNIQUE function is used to extract unique values in data. 1. The basic syntax is =UNIQUE(array, [by_col], [occurs_once]), where array is the data area, by_col controls to judge by row or column, and occurrence_once determines whether to return a value that only appears once; 2. Common uses include extracting a list of unique values, such as obtaining a list of non-duplicate people from sales records; 3. You can set occur_once to TRUE to obtain completely non-duplicate records, such as finding customers who have only placed an order once; 4. Combined with functions such as SORT, FILTER, COUNTA, etc., you can realize functions such as deduplication, conditional filtering, and counting the number of unique values, which greatly improves data processing efficiency.

Excel's UNIQUE function is a very practical tool, especially convenient when dealing with duplicate data. It can help you quickly extract unique values from a set of data, saving you time to manually filter or use complex operations such as advanced filtering and pivot tables.

Let’s take a look at the basic usage of UNIQUE functions and several common application scenarios.

Basic syntax: How is UNIQUE used?

The basic structure of the UNIQUE function is as follows:

 =UNIQUE(array, [by_col], [occurs_once])

• array : The data area you want to deduplicate, such as A1:A10.
• by_col (optional): Whether to compare by column, default is by row. If set to TRUE, it means to judge uniqueness by columns.
• occurs_once (optional): Whether to return only once value. The default FALSE returns all unique values, and TRUE only retains items that are completely unduplicated.

For example, if you have a column of names with duplicates and want to extract the unique list, you can write it as:

 =UNIQUE(A1:A10)

Common use 1: Extract a list of unique values

This is one of the most commonly used scenarios. For example, you have a sales record table with the name of the salesperson. You want to know who is involved in the sales.

Use this directly at this time:

 =UNIQUE (name column range)

You can get a list of people that are not repeated. This method is also applicable to fields such as product name, customer number, and region.

Tips:

• If your data source is a table, you can directly use structured references, such as =UNIQUE(Table1[姓名]) , which is clearer and easier to maintain.
• The results will automatically overflow and there is no need to drag the fill.

Common Use Two: Only keep records that are completely unrepeatable

Sometimes we want not only "unique", but also want these values to appear only once in the entire dataset. At this time, the third parameter occurs_once must be used.

For example, if you have an order form and want to find out those customers who have only placed an order once, you can write it like this:

 =UNIQUE(A2:A100, FALSE, TRUE)

This returns the client name that appears only once. This function is very useful when performing data analysis and abnormal detection.

Use with other functions: the combination technique is more powerful

UNIQUE itself is already very useful, but if it is combined with other functions, such as SORT, FILTER, COUNTA, etc., more advanced functions can be implemented.

To give a simple example, if you want to reorder the output results first, you can write it like this:

 =SORT(UNIQUE(A2:A100))

This gives you a unique list arranged in alphabetical or numerical order.

Common combinations:

• =SORT(UNIQUE(...)) : sort after deduplication
• =FILTER(..., COUNTIF(...) = 1) : combine FILTER to find the unique value under certain conditions
• =COUNTA(UNIQUE(...)) : count the number of unique values

Basically that's it. Although the UNIQUE function is simple, it can greatly improve the efficiency of data sorting if used properly. The key is to understand its parameters and applicable scenarios, and combine it with some other functions to easily deal with many common data cleaning tasks.

The above is the detailed content of How to use the UNIQUE function in Excel. For more information, please follow other related articles on the PHP Chinese website!