Excel filtering feature Application

Every time after the exam, the teacher always to the students to pick and choose, now have a computer, all this can be done to excel, a lot of simple query application can be achieved with the Excel filter function.

Figure 1 is a student performance registration form, so let's take this as an example to see how Excel filters out the data we need.

First, automatic screening

Objective: To sift out the students with Chinese score "greater than 90" and "less than 60" in Chinese.

The implementation process is as follows.

1. Select any cell in the datasheet and perform the data → filter → AutoFilter command, at which point a Drop-down button appears to the right of each column heading (see Figure 1).

2, click the Drop-down button on the right side of the language, and then in the shortcut menu that pops up (see Figure 1), select the Customize option and open the Customize AutoFilter dialog box (Figure 2).

3. Click the Drop-down button to the right of the top left box, and in the dialog box that appears, select the greater than or equal option, and then enter the value "90" in the upper-right box, select the or option, click the Drop-down button to the right of the lower left box, select the "less than" option in the shortcut menu that appears, and then enter a value in the lower-right box "60" (see Figure 2).

4. After setting up, click "OK" button, the data that match the condition is filtered out immediately (the result of the filter is shown in Figure 3).

Tip: On the basis of the above filter results, the "math" column continues to filter, set the filter criteria to "greater than or equal to" "90", you can "language" and "math" scores are "greater than or equal to 90" points of the students screened out.

Second, advanced screening

Objective: To select the students who are "Chinese" or "maths" score greater than or equal to "90".

The implementation process is as follows.

1, the first implementation of the "data → filter → automatic filter" command, undo the "AutoFilter" feature. This step can be omitted if the AutoFilter action is not previously performed.

2, respectively selected D66, D67, E66, E68 cell, input content: Language, >=90, mathematics, >=90 (Figure 4).

3. Select any cell in the score table, perform the data → filter → advanced filter command, and open the Advanced Filter dialog box (Figure 5).

4, select the Copy filter results to another location option.

Tip: You may not select this option, but in order to preserve existing data, it is advisable to save the filtered results to a different location.

5. Click the red button to the right of the condition range, at which point the dialog box is reduced to a floating state bar (Figure 6), with the mouse selected D66 to the E68 condition area, and then press the red button on the right side of the floating state bar to return to the Advanced Filter dialog box.

6, click the Red button to the right of copy to, at which point the dialog box shrinks to a floating state bar (Figure 7), selects the 1th cell (such as A69) that holds the filtered results, and then presses the red button to the right of the floating status bar to return to the Advanced Filter dialog box.

Tip: The data area system is usually automatically added to the default area, and if you need to change it, follow the above procedure.

7, after the completion of all settings, click the "OK" button, the data will be eligible to be filtered out.

Tip: If you change the criteria range to the style shown in Figure 8 (that is, conditional values are in the same row, and then perform the filtering system above, only the students with "language" and "math" scores are "greater than or equal to" "90" are screened out.