☰

简单方法 - 标准 Excel 操作

tag-icon • tag-icon microsoft-excel worksheet-function
简单方法 - 标准 Excel 操作

假设我在 Excel 中有一些数据（而不是在实际数据库中）。在一张工作表中，我有数据，其中一列用作 ID，并且我已确保此列中的值是唯一的。在另一张工作表中，我也有一些数据，同样有一列可以用作 ID，并且它也是唯一的。如果工作表 1 中的第 N 行有某个值，而工作表 2 中的第 M 行有相同的值，那么我确信第 N 行和第 M 行描述的是同一个现实世界对象。

我想问的是：如何在不编写任何宏的情况下获得与完整外连接等同的效果？通过功能区访问的公式和所有函数都可以。

一个小的“播放数据”示例：

第 1 页：

Dostoyevski    Russia
Pushkin        Russia
Shelley        England
Flaubert       France
Hugo           France
Eichendorff    Germany
Byron          England
Zola           France

第 2 页：

Shelley        Percy Bysshe
Eichendorff    Josef Freiherr Von
Flaubert       Gustave
Byron          Lord
Keller         Gottfried
Dostoyevski    Fyodor
Zola           Emile
Balzac         Honoré de

期望输出（排序并不重要）：

Dostoyevski    Russia   Fyodor
Pushkin        Russia
Shelley        England  Percy Bysshe
Flaubert       France   Gustave
Hugo           France
Eichendorff    Germany  Josef Freiherr von
Byron          England  Lord
Zola           France   Emile
Keller                  Gottfried
Balzac                  Honoré de

对于所有被这种情况吓坏的人：我知道这是错误的做法。如果我有选择的话，我会不是使用 Excel 来实现这一点。然而，有很多情况需要务实的解决方案，但统计表明，无法应用更好的（从 IT 角度来看）解决方案。

答案1

简单方法 - 标准 Excel 操作

首先，将两个表的两个关键列复制/粘贴到单个新表中作为单个列。

使用“删除重复项”获取所有唯一键的单一列表。

然后，添加两列（在本例中），每张表中的每个数据列添加一列。我建议您也使用格式为表格选项，因为它会使您的公式看起来更美观。使用 vlookup，使用以下公式：

=IFERROR(VLOOKUP([@ID],Sheet4!A:B,2,FALSE),"")

其中Sheet4!A:B表示源表数据表的每个相应值。IFERROR 可防止在 vlookup 不成功时出现难看的 #N/A 结果，在这种情况下会返回空白单元格。

这将为您提供结果表。

表 3：

在此处输入图片描述

表4：

在此处输入图片描述

结果数据：

在此处输入图片描述

结果公式（Ctrl+~将切换此项）：

在此处输入图片描述

内置 SQL 查询

您也可以使用内置 SQL 查询来执行此操作。它...不太方便用户使用，但可能是一个更好的用例。这可能需要您将“源”数据格式化为表格。

  1. 单击新工作表中的单元格
  2. 转至数据 --> 来自其他来源 --> 来自 Microsoft Query
  3. 在数据库选项卡下选择 Excel 文件*，然后点击确定
  4. 选择工作簿
  5. 选择以下四个字段：
    • 在此处输入图片描述
  6. 在看到 1990 年代格式的警告时，单击“下一步”和“确定”
  7. 下列的这些说明创建第一个左外连接。在我的例子中，我使用“国家”表作为左源，使用“名称”表作为右源。
    • 在此处输入图片描述
    • 这只给出了一些行（因为你是通过 ID 加入的）

  8. “创建减法连接然后将其添加为联合”部分更加复杂。

    • 以下是减法连接配置：在此处输入图片描述
    • 从 SQL 按钮复制此连接的 SQL：

    • 选择countries$.ID，countries$.Val1，names$.ID names$，.Val2 从 {oj C:\Users\Username\Desktop\Book2.xlsx. countries$ countries$LEFT OUTER JOIN C:\Users\Username\Desktop\Book2.xlsx. names$ names$ON countries$.ID = names$.ID} 其中（names$.ID 为空）

  9. 返回您创建的第一个外连接。手动编辑 SQL 并

    • 添加Union到底部
    • 将上面的减法连接文本添加到连接的底部
  10. 点击 SQL 按钮左侧的“返回数据”按钮
    • 此时，您可能希望编辑 SQL 以仅选择所需的特定数据。我发现隐藏结果中的列更容易。
  11. 将查询放在某处并确认其位置
    • 在此处输入图片描述

不适合胆小者。但是如果你想要一个绝佳的机会来查看 Office 中一些你可能已经很久没有更新过的部分，那么这是一个绝佳的机会。

答案2

作为替代解决方案，我可以建议Power Query? 这是微软推出的一款免费 Excel 插件，基本上可以完成这类任务。其功能实际上也将直接包含在 Excel 2016 中，因此具有前瞻性。

无论如何，使用 Power Query，步骤非常简单：

  1. 将两个表作为查询导入 Power Query 编辑器。
  2. 对它们执行合并查询转换，设置适当的连接列并将连接类型设置为完全外部。
  3. 将结果表加载到新工作表中。

它的好处是，一旦设置完成，如果您对基础数据表进行了更改，只需点击“数据”>“全部刷新”，Power Query 结果表也会更新。

答案3

半模拟（外连接）的快速方法是获取第二个列表并将其直接粘贴到第一个列表下方，即您的（主键）都位于同一（主）列中。然后对该主列进行排序，您将得到一个交错列表，然后执行转置 IF 方程（超级用户弄乱了表格布局视图...）：

列表A 列表B

萨姆蓝色蒂姆 32874 蒂姆红色玛丽 5710 克里斯绿色古斯塔夫 047 弗雷德蓝色
玛丽黑色

复制/粘贴/排序，结果如下所示：AB chris green fred blue gustav 047 mary 5710 mary black sam blue tim 32874 tim red

然后单元格 c1 的公式： (c1)=if(A1=A2,B2)

结果如下：

ABC chris green FALSE fred blue FALSE gustav 047 FALSE mary 5710 black mary black FALSE sam blue FALSE tim 32874 red tim red FALSE

对 C 进行排序以消除错误等。这是该想法的基本版本，如果需要更多数据，只需扩展它即可。-wag770310

相关内容