Experts proficient in EXCEL data screening

EXCEL masters about data filtering

A1 input:

=TEXT(SMALL(IF(MMULT(N(ISNUMBER(FIND({0,1,2},TEXT(ROW($1:$1000)-1,"000")))),{1;1; 1})=3,ROW($1:$1000)-1,9999),ROW(A1)),"[

After pressing [CTRL SHIFT and Enter], pull down to fill in the formula.

If you need to modify the three numbers you want to find, just modify {0,1,2} in the formula

If the situation is like what you listed, please set it as shown:

Then select column B and execute "Conditional Formatting" - use a formula to define conditions, the formula is:

=COUNT(FIND($D$1:$F$1,B2))=3

Then set the format - font, bold, red, OK, and it's done.

How to filter data using EXCEL

Excel has a small flaw, that is, it cannot automatically identify duplicate records. In order to clear these duplicate records, some friends manually delete them one by one, which is time-consuming and laborious.

Although Excel does not provide us with the function of clearing duplicate records, we can also use its advanced filtering function to achieve the same purpose. Today, the author will explain to you a little trick on how to use Excel's "Advanced Filtering" to cleverly delete duplicate records.

(Note: The techniques described in this article have been tested in Microsoft Excel 2003 environment)

The specific steps are as follows:

1. Open an Excel document with duplicate records. As shown in Figure 1 (Note: This picture has been processed with Photoshop, and the colored parts are repeated records)

2. Select all the records in the chart (note that the header row of each column should also be selected at this time, otherwise the filtered data table will no longer contain the header row), execute the "Data" menu → "Filter→Advanced Filter" command

3. In the pop-up "Advanced Filtering" dialog box, select the "Copy filter results to other location" item, and click the range button behind the "Copy to" selection box to select an area to store the filtered data. (Note: This must not overlap with the cell where the original data is located, otherwise the data table will be confused). Finally, after checking the "Select non-duplicate records" checkbox, click the "OK" button. .

4. At this time, Excel will automatically delete all duplicate records. After confirming that they are correct, you can copy the newly generated data list to a new worksheet and continue to use it.

The above is the detailed content of Experts proficient in EXCEL data screening. For more information, please follow other related articles on the PHP Chinese website!