Filter according to digital features.
For numerical fields, Excel # 039 filtering function provides rich options, as shown in Figure 4-79. Common expressions are greater than, less than, not equal to, between and so on. And above average, below average, custom filtering, etc.
Figure 4-79 Rich digital filtering options
Figure 4-80 is part of the data of a stock quotation table, and the information of the top six stocks needs to be filtered out. The operation steps are as follows.
Step 1: Select any cell in the data area, such as cell C2, and click Filter under the data tab to enable the filtering function of the data list. At this point, the filter button in the ribbon is highlighted and appears in the header cells of all fields in the data list, as shown in Figure 4-8 1.
Figure 4-80 Quotation Data Sheet
Figure 4-8 1 tab ribbon enables filtering.
Step 2: Click the "Filter" button in cell E 1, and then click the "Top 10 Digital Filter" command in the pop-up drop-down menu to open the "Auto Filter Top 10" dialog box, as shown in Figure 4-82.
Figure 4-82 opens the first 10 dialog box of automatic filtering.
Step 3: In the dialog box, set it to Max, Max and Item, and click OK to close the dialog box. The first six messages with the most increase will be filtered out, as shown in Figure 4-83.
Figure 4-83 Settings dialog box
Filter according to date characteristics.
For the date data field, the filter drop-down menu will display the relevant options of date filtering, which is rich in functions, as shown in Figure 4-84. Date filtering has the following characteristics.
Figure 4-84 Filter menu of date field
1. By default, the date grouping list does not display the specific date directly, but displays it in hierarchical form after grouping by year, month and day.
2.Excel provides a large number of preset dynamic filtering conditions, taking the comparison result between the date in the data list and the current date as the filtering conditions. For example, yesterday, tomorrow, this week, this month, this quarter and so on.
3. Commands under the menu are only filtered by time period, regardless of year. For example, the condition indicates the fourth quarter of any year in the data list, as shown in Figure 4-85.
Figure 4-85 Extended Menu
Although Excel provides many filtering conditions about date features, it can only be used for dates, not for time. Therefore, unlike "morning" and "afternoon", the filtering function of Excel only regards time as a number.
In addition, if you want to cancel the date grouping status in the filter menu, so as to filter according to a specific date value, you can click Options under the File tab, switch to the Advanced tab in the pop-up Excel Options dialog box, uncheck the check box in the display options of this workbook command group to group dates according to the automatic filter menu, and then click OK.
Figure 4-86 Cancel the default date grouping status in the filter menu
Use wildcards to filter fuzzy conditions.
Sometimes, the criteria used to filter data can explicitly specify the content of an item. For example, all employees with Chinese characters in their names, products with B in the third place in the product number, etc. In this case, you can use wildcards to filter.
Figure 4-90 is the inventory list of some data of a company, which is already in the screening state, and the third commodity number needs to be screened out as quotB.
Figure 4-90 Inventory List of a Company
Click the filter button in cell B 1, and then click the text filter custom filter in the pop-up drop-down menu to open the dialog box of custom automatic filtering method. Select quot equals quot, and enter quot? In the combo box on the right. B * quot, Click OK to close the dialog box, as shown in Figure 4-9 1. In addition, if it is only a single filter condition, you can also directly enter quot? In the search box of the filter menu. B * quot, click the confirm button to filter, as shown in figure 4-92.
Figure 4-9 1 Setting custom automatic shielding method
Figure 4-92 Using wildcards to filter in the search box
The screening results are shown in Figure 4-93.
Figure 4-93 Data List Filtering Results
Heart of the King 2 Click on the demo.