SQL 在 Excel 或 OpenOffice 的公共列上连接两个电子表格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4160243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join two spreadsheets on a common column in Excel or OpenOffice
提问by Steven smethurst
I have two CSV files with a common column and I want to "Join" the tables together on the common column.
我有两个带有公共列的 CSV 文件，我想在公共列上将表“连接”在一起。
For example: Join 'A' with 'B' equals 'Result'. If a one table has a key value that does not exist on in the other table its just left as blank.
例如：将 'A' 与 'B' 连接等于 'Result'。如果一个表具有另一个表中不存在的键值，则将其留为空白。
== Table A == == Table B == == Table result ==
Name ,Age Name ,Sex Name ,Age ,Sex
Bob ,37 + Bob ,Male => Bob ,37 ,Male
Steve ,12 Steve ,Male Steve,12 ,Male
Kate , 7 Kate , 7 ,
Sara ,Female Sara , ,Female
I know how to do this with an SQL database but I have never done it with "Excel" or "OpenOffice.org Calc"
我知道如何使用 SQL 数据库执行此操作，但我从未使用“Excel”或“OpenOffice.org Calc”完成此操作
Suggestions?
建议？
回答by robert
In Excel, vlookupcan do part of what you're asking. Specifically, you can use vlookup to do a left or right outer join, but not a full outer join (like your table result).
在 Excel 中，vlookup可以完成您所要求的部分任务。具体来说，您可以使用 vlookup 进行左外连接或右外连接，但不能进行完全外连接（如您的表结果）。
To do an outer join for your example above, add the following to the C2 of "Table B" (or copy "Table B" and then do this):
要为上面的示例执行外部联接，请将以下内容添加到“表 B”的 C2（或复制“表 B”，然后执行此操作）：
=vlookup(
a2, # the cell value from the current table to look up in the other table
table_a!:4832718, # the other table
# don't manually type this--select the entire
# other table while the cursor is editing this
# cell, then add the "$"s--Excel doesn't
# automatically add them
# (the syntax here is for different sheets in
# the same file, but Excel will fill this in
# correctly for different files as well)
2, # the column to get from the other table (A=1, B=2, etc.)
FALSE) # FALSE=only get exact matches TRUE=find approx. matches if no exact match
You should then be able to expand it to deal with multiple rows and multiple imported columns.
然后，您应该能够扩展它以处理多行和多个导入的列。
回答by BeemerGuy
In Excel, you use VLOOKUPfor that.
Assume you have the data in Table A listed in columns A and B in Excel.
And the data in Table B list in columns E and F.
Now, go to the first row in column C and enter:
在 Excel 中，您可以使用VLOOKUP它。
假设您在 Excel 的 A 列和 B 列中列出了表 A 中的数据。
表 B 中的数据在 E 列和 F 列中列出。
现在，转到 C 列的第一行并输入：
=VLOOKUP(A:A,E:F,2,FALSE)
This tells it to try to match column A with column E, and grab whatever is in the 2nd column near where we found it and place it in column C.
Now autofill the rest of the rows in column C to match the rest of the data.
这告诉它尝试将 A 列与 E 列匹配，并获取我们找到它的位置附近的第二列中的任何内容并将其放置在 C 列中。
现在自动填充 C 列中的其余行以匹配其余数据.
回答by endriju
If you can use Excel, there is a Query from Excel Files function:
如果您可以使用 Excel，则有一个来自 Excel 文件的查询功能：
- Define name for primary table - Table A (Formulas tab -> Define name)
- Define name for secondary table - Table B
- Go to Data tab, select "From Other Sources", and from the dropdown, select "From Microsoft Query"
- Select your CSV file and confirm that you want to merge the columns manually
- In the following window "Query from Excel Files", drag&drop the Name column of Table A into the Name column of Table B - a link between these columns will be created
- Go to File menu, click "Return Data to MS Office Excel", an Import Data dialog will pop up
- Select the sheet into which you would like the matched data to be imported
- Click OK - you should see matched data with columns from both tables
- 定义主表的名称 - 表 A（公式选项卡 -> 定义名称）
- 定义辅助表的名称 - 表 B
- 转到“数据”选项卡，选择“来自其他来源”，然后从下拉列表中选择“来自 Microsoft Query”
- 选择您的 CSV 文件并确认您要手动合并列
- 在下面的“从 Excel 文件查询”窗口中，将表 A 的名称列拖放到表 B 的名称列中 - 将创建这些列之间的链接
- 转到文件菜单，单击“将数据返回到 MS Office Excel”，将弹出导入数据对话框
- 选择要将匹配数据导入到的工作表
- 单击确定 - 您应该会看到与两个表中的列匹配的数据
Or if you don't mind uploading your CSV files to an online service, you can use for example http://www.gridoc.com/join-tablesand join the spreadsheets using drag&drop (Disclaimer: I am author of the tool).
或者，如果您不介意将 CSV 文件上传到在线服务，您可以使用例如http://www.gridoc.com/join-tables并使用拖放加入电子表格（免责声明：我是该工具的作者） .
Hope this helps.
希望这可以帮助。
