在 Excel 或 OpenOffice 中的公共列上合并两个电子表格

发布于 2024-10-02 13:08:41 字数 560 浏览 4 评论 0 原文

我有两个带有公共列的 CSV 文件，我想在公共列上将表“连接”在一起。

例如：将“A”与“B”连接等于“结果”。如果一个表具有另一个表中不存在的键值，则将其保留为空白。

== 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

我知道如何使用 SQL 数据库执行此操作，但我从未使用“Excel”或“OpenOffice.org Calc”执行此操作

建议？

原文

I have two CSV files with a common column and I want to "Join" the tables together on the common column.

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.

== 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"

Suggestions?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

也只是曾经 2024-10-09 13:08:41

在 Excel 中，vlookup 可以完成您所要求的部分操作。具体来说，您可以使用 vlookup 执行左外连接或右外连接，但不能使用完整外连接（如表结果）。

要为上面的示例执行外部联接，请将以下内容添加到“Table B”的 C2（或复制“Table B”，然后执行此操作）：

=vlookup(
    a2, # the cell value from the current table to look up in the other table
    table_a!$1:$174832718, # 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

然后您应该能够扩展它以处理多行和多个导入列。

In Excel, vlookup can 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).

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):

=vlookup(
    a2, # the cell value from the current table to look up in the other table
    table_a!$1:$174832718, # 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.

回复收藏 0 原文

苏别ゝ 2024-10-09 13:08:41

在 Excel 中，您可以使用 VLOOKUP 来实现此目的。
假设您在 Excel 的 A 列和 B 列中列出了表 A 中的数据。
表 B 中的数据列在 E 列和 F 列中。
现在，转到 C 列中的第一行并输入：

=VLOOKUP(A:A,E:F,2,FALSE)

这告诉它尝试将 A 列与 E 列匹配，并抓取靠近我们找到它的位置的第二列中的所有内容并将其放置在 C 列中。
现在自动填充 C 列中的其余行以匹配其余数据。

In Excel, you use VLOOKUP for 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:

=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.

回复收藏 0 原文