Querying and filtering using SQL statements in Excel

This article transferred from: http://blog.sina.com.cn/s/blog_5fc375650102e1g5.html

Today on Weibo to see the @ data Analysis Select share an article on the "Use of SQL statements in Excel to achieve accurate query", feel very useful, share to everyone.

On Weibo, someone replied to comments that directly with VLOOKUP, or Import database query processing is good, not more efficient, more flexible; in fact, the first intuitive feeling is such a son, but we want to one step, this article of the application scenario, the use of the prerequisite is what? I think of the following aspects: ① data volume is not very large time; ② database is not very suitable for importing databases, or to convert, but it seems troublesome; ③ use VLOOKUP more students, believe that the match is not so accurate, and will return "#N/A error value", In addition VLOOKUP each return is a column of values, ④ in the Excel environment, can be very good with tables, charts, using the data refresh function once and for all to complete the regular chart of the self-made. In the circumstances that I think of, I believe it will be more efficient to use this method. Another point, this article refers to this feature points and tips to tell you a message, in fact, in Excel can also be used for data query and database query (in this functional area there are database queries Oh, to study on their own).

To get back to the original source, we will share the use of this function module in the form of examples.

Warm tip : It is understood that Excel2007 and the above version has this function, 2003 version of either pass the study, or to upgrade their own version.

Like the next 2 tables, table 1 contains the name, time, Training content field data, Table 2 includes the name, title, Annual Salary field data, we can see 2 tables have a Name field.

Table 1

Table 2


Now I would like to have a record of the training in table 1 on the list of statistics in 2. Human flesh realization or VLOOKUP way of course this simple case can be achieved, but to learn extrapolate, learning Fanjun is a simple example to explain to you (still tangled back to the beginning of the article to think about the preconditions and you can think of the scene can be used). Here we introduce the method of using simple SQL statements in Excel to realize the integration and filtering of data between different tables.

The first and most important is to name the two tables by selecting the "Define name" option and then right-clicking the table, as shown below

When clicked, a naming dialog box appears

Here, table 1 and table 2 are named Table1 and Table2 respectively.

Then select the "Data" tab above and select "from Microsoft Query" option under "from other sources"

In the dialog box that pops up, select Excel files*, and use the Query Wizard create/Edit query below the dialog box to tick off, as shown in

Then click "OK" and the "Select Workbook" dialog box appears, where you can select the worksheets that contain table 1 and table 2 sample.xlsx

After clicking OK, the Add Table dialog box appears, as shown in

Here to add Table1 and Table2 all over again, after adding the query should be as shown

At this point, click the button for the SQL statement entered in Figure 10, which pops up the dialog box for the input SQL statement, as shown in

In the code is this, lazy classmate can directly ctrl+c/ctrl+v:
SELECT Table1. Name, Table1. Time, Table1. Training content, Table2. Name
From Table1,table2
WHERE Table1. Name = Table2. Name

The basic meaning is to filter the records in table 1 and the names in table 2 from table 1. The SELECT statement is one of the most basic and most important statements in the SQL language, and with the constraints after the where statement, most of the data query and filtering work can be implemented, and the syntax is not difficult, and a little learning will be done. After entering the code, click OK to see the filtered data table, as shown in

The next task is to return the filtered data table to the EXCEL worksheet, select "File" in the menu- -"Return data to Microsoft Excel" as shown in

The next steps should not be wordy.

At this point, the tutorial is complete. The use of SQL statements in Excel can be more flexible, accurate, efficient data filtering and matching, summary, calculation, etc., the article mentioned in the example is the simplest case, we can combine their work scenes to play more tricks, I believe there will be some gains.

Thanks to the original Durward, original: http://www.yingzheng.com/forum.php?mod=viewthread&tid=2084402

Querying and filtering using SQL statements in Excel