How to filter the corresponding values in Excel values

Here's how:1Filter the data in column A for 1:

2.Data, filtering:

3.Deselect all and select only 1

4.The screening results are as follows:

1.Enter the formula in the second maximum:

large(b3:b28,2)

Meaning of the formula: large (data region, nth maximum). The large function is used to find the nth maximum value in a given data region, where n is a positive integer.

For example, large(b3:b28,5) is the fifth number in the region b3:b28 from largest to smallest.

2.Fill to the right to find the second largest value of product B.

3.In the same way, you can find the top 5 data.

1. First of all, select all the values that you need to filter.

2. Then there is the toolbar Conditional Formatting to highlight the cell rules of the reed display and other rules.

3. At this time, a dialogue basket will appear, and there are many options in it, which can be selected according to your own needs.

4. After you have selected it, click Format.

5. In the dialog basket that appears, the fill above the dot is filled in to choose your favorite color, and finally the dot is OK.

6. At this time, you will see that all the values that are greater than 70 points and less than 80 points are marked with red baskets.

1. Automatic filtering: Open the **excel file that needs to be filtered, click on the cell with the mouse to locate any cell area containing data, and click the menu bar - data - filter - automatic filter.

2. Drop-down arrow: After selecting automatic filtering, the drop-down arrow appears in the data column, and click the drop-down arrow to display the filter menu. Each drop-down menu corresponds to a filter category.

3. Filtering method: Click on the number filter to filter the data for a specific data or a certain numerical interval for the ** file containing a large amount of data. For example, we need to filter out items with a historical score of less than 85 points, click on the number to filter, select less than, and enter 85 to filter.

4. Establish a region: If you want to screen out the scores of multiple categories, such as students with scores greater than 80 points in four subjects, we need to establish a conditional area in advance, and empty three or more rows above the overall data, corresponding to the input data classification and the required condition restrictions.

5. Advanced filtering: Click the menu bar - Data - Filter - Advanced Filter to open the menu of advanced filtering.

6. List area and condition area: After opening the advanced filter, you can enter the cell range or use the mouse to check the required area, and check the list area and the condition area respectively.

7. Result acquisition: If you need to let the filtered data be displayed elsewhere alone, check the box to copy the filtered results to other locations, and manually click the cells that need to be stored.

Click OK to get the filter results.

1.Let's take a look at the filter list and the corresponding data, as shown in the following figure, there is data content in the filter list.

2.Blank line, enter 29, 30, in the filter list, you will find that the wax is missing two values, 29 and 30, the reason is because there is an empty line.

3.Enter the data in the blank line, and the filter list also changes, with two values, 29 and 30.

4.On another empty line, enter 33, and it is still missing from the filter list.

5.Click "Advanced Filtering" next to "Filtering", and carefully observe the list area, it is not difficult to find that the area does not contain data after 29 30.

6.Therefore, select all the ** content, click on the coarse ant to slide and "filter" twice, and a complete list of sieves will appear.

<><2: Enter =if(countif()) in the blank space of the same line corresponding to the name in sheet1

3: Then switch to sheet2, select all names and press enter.

4: Switch to sheet1 again, and this function becomes =if(countif(sheet2!a1:a44))

Note: In this step, the letters (in our case A) and numbers (in our case 1 and 44) are preceded by the symbol $, =if(countif(sheet2!).$a$1:$a$44))