Excel : Utiliser les fonctions FILTRE, UNIQUE et CHOISIRCOLS pour filtrer/extraire des données

Introduction

This tutorial will guide you on how to use the FILTER, UNIQUE, and CHOOSECOLS functions in Excel to filter and extract data from a table. These functions are essential for efficiently managing data, especially when preparing for the Microsoft Office Specialist (MOS) certification. By mastering these functions, you can streamline your data analysis and reporting processes.

Step 1: Understanding the FILTER Function

The FILTER function allows you to return a subset of data based on specified criteria.

How to Use FILTER

1. Syntax: The syntax for the FILTER function is:

   =FILTER(array, include, [if_empty])
   

   • array: The range of cells you want to filter.
   • include: The condition that must be met for the data to be included.
   • if_empty: (optional) Value to return if no data meets the criteria.

2. Example:

   • Suppose you have a table with sales data, and you want to filter for sales greater than 1000:

     =FILTER(A2:B10, B2:B10 > 1000)
     

   • This will return all rows where the sales in column B are greater than 1000.

Practical Tip

Be specific with your criteria to ensure you retrieve the correct data subset.

Step 2: Using the UNIQUE Function

The UNIQUE function helps you extract distinct values from a range.

How to Use UNIQUE

1. Syntax: The syntax for the UNIQUE function is:

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

   • array: The range of cells to extract unique values from.
   • by_col: (optional) TRUE to compare by column, FALSE (default) to compare by row.
   • exactly_once: (optional) TRUE to return values that occur exactly once.

2. Example:

   • To get a list of unique product names from a product list:

     =UNIQUE(A2:A10)
     

   • This will return all unique product names from the specified range.

Common Pitfall

Ensure that your range does not contain empty cells unless you want them included in your unique results.

Step 3: Applying the CHOOSECOLS Function

The CHOOSECOLS function allows you to select specific columns from a range.

How to Use CHOOSECOLS

1. Syntax: The syntax for the CHOOSECOLS function is:

   =CHOOSECOLS(array, col_num1, [col_num2], ...)
   

   • array: The range of cells from which to choose columns.
   • col_num1: The index number of the column to include.
   • Additional column numbers can be specified.

2. Example:

   • To select only the first and third columns from a data set:

     =CHOOSECOLS(A1:C10, 1, 3)
     

   • This will return only the first and third columns from the specified range.

Practical Tip

Use CHOOSECOLS to focus on relevant data for your analysis, enhancing readability and comprehension.

Conclusion

In this tutorial, you learned how to effectively use the FILTER, UNIQUE, and CHOOSECOLS functions in Excel to manage and analyze your data. By applying these functions, you can streamline your data extraction processes and enhance your efficiency in handling large datasets.

Next Steps

• Experiment with these functions on your own datasets to reinforce your understanding.
• Consider practicing with sample data to prepare for the MOS certification.
• Explore additional Excel functions to further enhance your data analysis skills.