[GO] Using SQL statements in Excel for precise queries

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

Click OK and then pop up the dialog to add the table, as shown here to add Table1 and Table2 all over again, after the addition is complete, the query should look like the following

At this point, click the Input SQL Statement button in Figure 10, pop up the dialog box of the input SQL statement, as shown in the code is like 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

[GO] Using SQL statements in Excel for precise queries