Hello friends, today let's talk about the advanced version of the filtering function - advanced filtering.
1. Filter the records under the specified conditions
As shown in the following image, you want to filter out all the records in the Data table that are Credit in the Product Classification field.
First, write the field name you want to filter and the criteria you want to filter in the blank cell.
Note that the field name must be exactly the same as the field name in the data table, otherwise it will not be filtered.
Click any cell in the data region, click the Advanced button under the Data tab, and then select the conditional region and the region where the data is stored as prompted.
2. Screen some records that meet the requirements
As shown in the following image, you want to filter all the Customer Names that are Credit Lines in the Product Classification field.
First, write the name of the field to be filtered and the criteria to be filtered, and then write the field title "Customer Name" in the target area.
Click any cell in the data region, and → Advanced Data.
In the Advanced Filtering dialog box, select the criteria area, click in the Copy To text box, and select the cell for which you just entered the field title.
Tick the box on "Select Duplicate Records".
3. Filter records that meet the two criteria
The criteria area for advanced filtering is a bit prescient.
If you write a condition on the same line, you want to filter records that meet both conditions.
The condition in the figure below indicates that the "Product Classification" is "Credit Loan", and the "Handling Department" is all the records of the "Business Department".
If you write a condition on a separate line, you will filter for records that match one of the two conditions.
The condition in the figure below indicates that all records in the "Product Classification" are "Credit Loan" or "Handling Department" is the "Business Department".
The relationship between the same row filter conditions is "and", and the relationship between different row filter conditions is "or", you just need to remember this rule.
4. Set the exact matching conditions
Advanced filtering uses fuzzy matching by default, as long as the cell contains keywords, it will be considered eligible, and if you want to get exactly the same content as the filter, you need to do a little bit of work.
As shown in the figure below, if you only need a record of "massage chair", the condition needs to be written as a single quotation mark with a half-width, an equal sign, and a filter condition:
'= Massage chair
5. Put the filter results into a new worksheet
If you want to put the filter results in another worksheet, you can switch to the worksheet where the filter results are stored, and then perform advanced filtering.
Advanced filtering is easy to use and efficient. A fly in the ointment: When the data source or filtering changes, the results of the advanced filter will not be refreshed, and the filtering operation can only be re-executed once.
Well, that's all for today, I wish you all a good mood for the day~~
Graphic production: Zhu Hongzhong