☰

突出显示两个 Excel 电子表格之间的差异

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting
突出显示两个 Excel 电子表格之间的差异

电子表格 A 中的数据有多列，但我只需要比较：

Column A - order #
Column C - count
Column D - date

电子表格 B 也有很多列，但我需要比较：

Column A - order #
Column B - count
Column C - date

当行中的数据只有这 3 列相同时，我需要在两个电子表格中突出显示行中的 A 列。在此示例中，第 6 行和第 14 行具有不同的日期，第 7 行和第 15 行具有不同的数量，因此它们不会突出显示。

点击例如

答案1

此方法依赖于您的第一个电子表格中的一些辅助列，或者如果您愿意的话，依赖于单独的工作表。

MATCH() 将返回数组中给定值的行号。

INDEX() 在另一个指定的数组中查找此值

因此，在辅助列（假设为 X 列）的第 1 行中，您希望=MATCH(A1,SheetName!A:A,0) 这将返回另一张工作表中包含 A1 中的订单号的行。

然后在接下来的 2 列中，您需要=INDEX(SheetName!C:C,$X1) =INDEX(SheetName!D:D,$X1)。这将返回该行中另一张表的计数和日期。

然后，在最后一列中，您需要=And(Y1=C1,Z1=D1)。如果所有内容都相同，这将返回 TRUE。

然后，如果愿意的话，使用条件格式来突出显示。

如果您愿意，可以将所有这些压缩为一个公式，并用作条件格式设置条件，此外，vlookup 在这里也可以像索引匹配一样工作。我将它们分开提供给您，因为我认为这样更容易理解发生了什么以及它为什么有效，但我建议您尝试压缩它，作为练习。

我希望这有帮助。

请注意，如果订单号在查找列表中重复，则此方法不起作用，因为它只查找表中的第一个匹配项。

答案2

您可以使用条件格式，使用“使用公式确定要格式化的单元格”来实现。简短的版本是，您需要在位于 Sheet1 上的第一个表格和位于 Sheet2 上的另一个表格上使用以下公式，这两个表格都从 A1 开始：

=AND(MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)>0; INDEX(Sheet1!$A:$D;ROW();3)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2); INDEX(Sheet1!$A:$D;ROW();4)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);3))

此公式假设以下情况：

  • 两个表格位于两个单独的工作表中，分别称为 Sheet1 和 Sheet2
  • Sheet1 包含您的第一个表格，其中 A、C 和 D 列与 Sheet2 上的第二个表格的 A、B 和 C 列进行比较。
  • 在第一张表的条件格式区域上，选择 $A:$A 或 $A2:$AXX （其中 XX 是一个足够大的数字，可以包含所有值） - 如果上述等式成立，则这只对颜色有影响

当它不能按“预期”工作时：

  • 如果第二张表中有多个相同的订单号，则只会查找并比较第一个匹配项
  • 通过应用于 Sheet2 表，它不会返回正确的颜色，需要更改一些引用值

使用的功能：

  • AND()- 仅当所有参数都为 true 时才返回 true
  • MATCH()- 在选定范围内查找值（本例中为列）
  • INDEX()- 通过明确说明行号和列号，从矩阵/范围返回单元格引用
  • ROW()- 不带任何参数返回当前行号

现在让我们看看一些组件：

  • INDEX(Sheet1!$A:$A;ROW();1)当条件格式运行时，这将返回当前比较的单元格引用
  • MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)这里我们在第二张表上寻找与当前单元格匹配的单元格，如果匹配，则返回值高于 0，否则给出警告/错误。
  • INDEX(Sheet1!$A:$D;ROW();3)- 我们在这里查找第一个表（Sheet1）的第3列（别名C列）的值。
  • INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2)- 这将从 Sheet2 表中获取第二列。我们已经有了放置订单 ID 的行，该方程位于此函数的中间，用于确定行。
  • 第三个AND()参数与前两点相同，只是改变了列号。

笔记：

  • 为了找到正确的行，我仅使用列：Sheet1!$A:$A和Sheet2!$A:$A
  • 当我需要获取另一列时，我需要将该列扩展为矩阵/表，其中最后一列至少是我将引用的最后一列，如果是 Sheet1 中的第 4 列，则别名 D 列Sheet1!$A:$D

通过了解以上内容，您需要能够修改公式以使其也可以在 Sheet2 表中使用。

答案3

这个很好，并且可以通过如下的条件格式轻松完成。

设置

对于下面的示例公式，数据位于单元格 Sheet1!A1:C3 和 Sheet2!A1:C3 中。

条件格式公式

突出显示单元格范围工作表2，通过公式添加条件格式并输入以下公式：=AND(Sheet1!$A1=$A1,Sheet1!$B1=$B1,Sheet1!$C1=$C1)...并选择一种颜色来突出显示您的结果。

锁定列而不是行很重要（即在字母前有美元符号但没有数字）。

关于条件格式公式中使用的引用，单元格引用应为相应数据列的最上部单元格。因此，如果两列订单号都从 A1 开始，则按照上述方法将其保留为 A1。但如果在第一个选项卡上要考虑的第一个订单号在第 4 行，但在第二个选项卡上它在第 7 行，则将公式修改为 AND(Sheet1!$A4=A7...)，等等。

如果有什么需要澄清的话请告诉我！

相关内容