Okay, so let's say I have two tables in Excel. I want to create a third table that looks up data from the other two and combines them. I'll explain what I mean
好的,我们假设我在Excel中有两个表。我想创建一个第三个表来查找其他两个数据并将它们组合起来。我会解释我的意思
Here is my first table:
这是我的第一张桌子:
It has a number, which is like a primary key, and a corresponding name for each entry.
它有一个数字,就像一个主键,每个条目都有一个相应的名称。
I also have a second table:
我还有第二张桌子:
This one just contains some random info, and there's numerous entries. The Client Name is not listed here, but it does have the key for the client that each entry corresponds to.
这个只包含一些随机信息,并且有很多条目。此处未列出客户端名称,但它确实具有每个条目对应的客户端密钥。
I want to create a third table that shows me all the Client Info data, but replaces the key with the actual client name. So basically, wherever it sees a key of "1", it'll look that up from the first table and find "Comet" instead.
我想创建第三个表,向我显示所有客户端信息数据,但用实际客户端名称替换密钥。所以基本上,无论它在哪里看到“1”键,它都会从第一个表中查找并找到“Comet”。
The desired table would look like this:
所需的表格如下所示:
What kind of formula would I need to pull data from one table based on a value? Note that all my tables are in different worksheets.
我需要什么样的公式才能根据值从一个表中提取数据?请注意,我的所有表都在不同的工作表中。
2 个解决方案
#1
1
As you put an "SQL" tag, I assume that you are also looking for an SQL based answer. So in addition to @teylyn suggestion, you can use the following SQL query if you are working with SQL databases:
当你输入一个“SQL”标签时,我假设你也在寻找一个基于SQL的答案。因此,除了@teylyn建议之外,如果您正在使用SQL数据库,则可以使用以下SQL查询:
SELECT table1.Client_Name, table2.Client_Info
FROM table1
RIGHT JOIN table2 ON table1.ID_Number = table2.ID_Number
Here is what this query does:
以下是此查询的作用:
The RIGHT JOIN will return all rows from the right table (here table2), with the matching rows in the left table (here table1) according to the condition specified by the ON clause (here, if the ID Number is the same). Then, the SELECT clause will return a table with ONLY the columns specified after the keyword SELECT.
RIGHT JOIN将根据ON子句指定的条件(此处,如果ID号相同)返回右表(此处为table2)中的所有行,左表(此处为table1)中的匹配行。然后,SELECT子句将返回一个表,该表只包含在关键字SELECT之后指定的列。
#2
2
Assuming that the three tables are in Sheet1, Sheet2, Sheet3, this is the formula you need in Sheet3, cell A2
假设这三个表在Sheet1,Sheet2,Sheet3中,这是Sheet3中需要的公式,单元格A2
=vlookup(Sheet2!A2,Sheet1!$A2$b$6,2,false)
Copy down as many rows as there are rows in Sheet2
向下复制与Sheet2中的行一样多的行
If you need the client info from Sheet2, use this in Sheet3, cell B2 and copy down
如果您需要Sheet2中的客户端信息,请在Sheet3,单元格B2中使用它并复制下来
=sheet2!B2
#1
1
As you put an "SQL" tag, I assume that you are also looking for an SQL based answer. So in addition to @teylyn suggestion, you can use the following SQL query if you are working with SQL databases:
当你输入一个“SQL”标签时,我假设你也在寻找一个基于SQL的答案。因此,除了@teylyn建议之外,如果您正在使用SQL数据库,则可以使用以下SQL查询:
SELECT table1.Client_Name, table2.Client_Info
FROM table1
RIGHT JOIN table2 ON table1.ID_Number = table2.ID_Number
Here is what this query does:
以下是此查询的作用:
The RIGHT JOIN will return all rows from the right table (here table2), with the matching rows in the left table (here table1) according to the condition specified by the ON clause (here, if the ID Number is the same). Then, the SELECT clause will return a table with ONLY the columns specified after the keyword SELECT.
RIGHT JOIN将根据ON子句指定的条件(此处,如果ID号相同)返回右表(此处为table2)中的所有行,左表(此处为table1)中的匹配行。然后,SELECT子句将返回一个表,该表只包含在关键字SELECT之后指定的列。
#2
2
Assuming that the three tables are in Sheet1, Sheet2, Sheet3, this is the formula you need in Sheet3, cell A2
假设这三个表在Sheet1,Sheet2,Sheet3中,这是Sheet3中需要的公式,单元格A2
=vlookup(Sheet2!A2,Sheet1!$A2$b$6,2,false)
Copy down as many rows as there are rows in Sheet2
向下复制与Sheet2中的行一样多的行
If you need the client info from Sheet2, use this in Sheet3, cell B2 and copy down
如果您需要Sheet2中的客户端信息,请在Sheet3,单元格B2中使用它并复制下来
=sheet2!B2