Like and see, form a habit; The clothes are new, the man is old.

Wechat search [Yixin Excel] to pay attention to this different we-media person.

In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.

When we are dealing with data, we should often encounter the need to perform some queries on the data. If multiple queries are required, it will be troublesome to operate the filter function every time.

As shown in the figure above, it not only supports single condition query, for example, enter “male” in the top gender, click the query button, and all students of “male” will be displayed below. And also support multi-condition query, such as query gender is male, birth year 2000, click below the query can be found all meet the conditions of the students.

This seemingly superior query system is actually very simple, neither using complex functions, nor using awesome VBA programming.

Today I want to share with you how advanced filtering and recording macros can be used to implement a simple query mini-system.

1. Add a query area

First add three blank rows above the source data, and then add the corresponding query criteria.

You can then insert a graph as a query button. Specific operations are as follows:

Click the Shapes drop – down list in the Illustrations command group under the Insert TAB and select the rounded rectangle in Rectangle.

When you move the mouse over the cell area, you will see the cursor change to a small black cross. Then hold down the left mouse button to draw the appropriate rounded rectangle. Double-click the graphic and enter “Query”, then you can set the query button font style, Microsoft Yahei font, bold, and horizontal center and vertical center.

2. Record macros

The main purpose of recording macros is to record the whole process of calling advanced filtering function to achieve the query function and save it as a function.

First click “Record macro” in the “Code” command group under the “Development Tools” TAB, bring up the record macro dialog box, and then the macro name: Query, click OK button.

Then click Advanced in the Sort and Filter command group under the Data TAB to bring up the Advanced Filter dialog box. Set “List area” to the source data area (A4:E15 cell area), set “Condition area” to the query area (A1:E2 cell area), and click OK.

Finally, click “Stop recording” in the “Code” command group under the “Development Tools” TAB. At this point the macro is recorded.

3. Query button function realization

Query button query function is also very simple, just need to specify the query button macro, pointing to the recorded macro named “query”. Select the query button as follows, right-click the mouse and choose Specify macros to bring up the Specify macros dialog box.

Then select “Query” and click the OK button.

Here the whole query small system is completed. Here’s a look at the process and its effects.

After finishing production, we recall the whole process, actually USES two knowledge, is an advanced screening function, one is recording macros, these two functions are not complicated, but make little feature is useful, skillfully use all kinds of basic functions and together, sometimes play an unexpected effect.

Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.

If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.

This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.