Excel vlookup结合SQL表 [英] Excel vlookup incorporating SQL table

问题描述

我有一个Excel电子表格，用于计算我们销售的产品的成本（恰巧是汽车保险）。本产品根据各种信息计算;客户年龄，邮政编码，车辆，职业等。

I have an Excel spreadsheet which I use to calculate the cost of a product we sell, (happens to be car insurance). This product is calculated based on various pieces of information; the customers age, postcode, vehicle, occupation, etc etc.

我将信息输入到我的电子表格的主标签上，然后执行许多vlookup公式来返回相关数字从电子表格的其他标签。我的一个vlookup的一个例子是：

I enter information onto the main tab of my spreadsheet and then perform lots of vlookup formulas to return the relevant figures from other tabs of the spreadsheet. An example of one of my vlookups is:

= VLOOKUP（G1，RatesInsurerX！A36：B986786,2，FALSE）

=VLOOKUP(G1,RatesInsurerX!A36:B986786,2,FALSE)

所以这样查看我输入到单元格G1中的值，它匹配到名为RatesInsurerX的工作簿选项卡上找到的数据。

So this looks up the value I enter into cell G1 an matches it to the data found on the workbook tab called RatesInsurerX.

正如你可以看到的那样表包含近100万行，而excel正在开始挣扎，所以我想知道是否有一种完全相同的方式，只执行对SQL表的查找，而不是RatesInsurerX工作簿？

As you can see this particular table contains nearly 1 million rows and excel is starting to struggle so I was wondering if there is a way of doing exactly the same thing only performing the lookup against a SQL table rather than the RatesInsurerX workbook?

推荐答案

好消息是，您可以在没有 VBA 的情况下执行此操作，但如下所示：

Good news is you can do this without VBA, quite a few steps though as follows:

1。首先添加一个新的工作表，称之为有意义的东西，如 PoscodeLookup 。

1 . First add a new sheet, call this something meaningful like PoscodeLookup.

2。下一步去数据并选择其他来源和 Microsoft Query ：

2 . Next go to Data and select Other Sources and Microsoft Query:

3。下一步选择（或创建）一个可以将数据连接到数据库的ODBC数据库，并选择此选项（您可能需要输入用户/密码）。

3 . Next select (or create) an ODBC data source that can connect you to your database asd select this (you may need to enter user/pass).

4。查询设计师将要求您选择一个表格，只需点击关闭。

4 . The query designer will ask you to select a table, just click close.

5。接下来选择SQL按钮：

5 . Next select the SQL button:

6。现在输入SQL查询以获取所需的单个值，使用示例邮政编码，例如：

6 . Now enter the SQL query to get the single value you need, using an example postcode eg:

SELECT TOP 1 [Value] FROM [MyTable] WHERE [Postcode] = 'AB12 1AA';

7。现在点击OK和OK，这应该在窗口中返回一个值。

7 . Now hit OK and OK which should return a single value in the window.

8。点击工具栏中的返回数据

8 . Click on Return data in the toolbar:

9。现在回到Excel中的属性中提示：

9 . Now back in Excel hit properties in the prompt:

10。现在，请在定义选项卡中将您输入的邮政编码更改为？：

10 . Now change the post code you entered into a ? in the definition tab:

11。再次点击OK，然后再提示输入一个值，输入一个有效的邮政编码，然后按回车键，现在应该在单元格A1中给出你想要的值。

11 . Hit OK and OK again and it will prompt for a value, enter a valid postcode and hit enter, this should now give you the value you want in cell A1.

12 。现在点击单元格A1，然后转到数据> 属性然后连接属性：

12 . Now click in the cell A1 and go to Data > Properties then Connection properties:

13。现在在定义选项卡中，底部有一个参数按钮，在这里可以填写如下：

13 . Now in the definition tab you have a Parameters button at the bottom, in here you can fill out as below:

注意，目标单元格是您输入邮政编码的单元格，如果您希望在邮政编码更改时重新运行查询，请勾选刷新框。

Note, the target cell is the cell where you enter the postcode, tick the refresh box if you want this to re-run the query when the post code changes.

应该是这样，让我知道如果不行。

That should be it, let me know if it doesn't work.

这篇关于Excel vlookup结合SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！