如何在Google电子表格中，如果同一列中的值重复，突出显示单元格？

Question

如何在Google电子表格中，如果同一列中的值重复，突出显示单元格？

google-sheetsconditional-formatting

296

我正在寻找谷歌电子表格的公式，用于在同一列中值重复时高亮显示单元格。

请问有人可以帮助我吗？

- user3331309

6个回答

73

虽然zolley的回答对于这个问题是完全正确的，但这里提供了一个更通用的解决方案，适用于任何范围，以及解释：

    =COUNTIF($A$1:$C$50, INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1

请注意，在此示例中，我将使用范围A1:C50。第一个参数（$A$1:$C$50）应替换为您想要突出显示重复项的范围！

突出显示重复项：

选择需要标记重复项的整个范围。
在菜单栏中选择：格式 > 条件格式...
在 应用于范围 中，选择应用规则的范围。
在 如果格式符合以下条件，则设置格式 中，从下拉菜单中选择 自定义公式。
在文本框中插入给定的公式，并调整范围以匹配步骤 (3)。

为什么它有效？

COUNTIF(range, criterion)，将会比较range中的每个单元格和criterion，该参数将被类似于公式的方式进行处理。如果没有提供特殊运算符，它将会将范围内的每个单元格与给定单元格进行比较，并返回符合规则（在此情况下是比较）的单元格数量。我们使用了一个固定的范围（带有$符号），以便我们始终查看完整的范围。

第二个块，INDIRECT(ADDRESS(ROW(), COLUMN(), 4))，将返回当前单元格的内容。如果将其放置在单元格内部，文档将会抱怨循环依赖关系，但在这种情况下，该公式将被评估为在单元格内部，而不改变它。

ROW() 和 COLUMN() 将分别返回给定单元格的行号和列号。如果未提供参数，则将返回当前单元格（这是基于1的，例如，B3将返回ROW()为3，COLUMN()为2）。

然后我们使用：ADDRESS(row, column, [absolute_relative_mode])将数字行和列转换为单元格引用（如B3）。请记住，在单元格的上下文中，我们不知道它的地址或内容，我们需要内容才能进行比较。第三个参数负责格式化，4返回INDIRECT()所喜欢的格式。 INDIRECT()将获取单元格的引用并返回其内容。在这种情况下，是当前单元格的内容。然后回到起点，COUNTIF()将针对范围内的每个单元格测试我们的单元格，并返回计数。

最后一步是使我们的公式返回一个布尔值，通过将其变成逻辑表达式：COUNTIF(...) > 1。使用> 1是因为我们知道至少有一个与我们相同的单元格。那就是我们的单元格，它在范围内，因此将与自身进行比较。因此，要指示重复项，我们需要找到2个或更多与我们匹配的单元格。

来源：

文档编辑器帮助：COUNTIF()
文档编辑器帮助：INDIRECT()
文档编辑器帮助：ADDRESS()
文档编辑器帮助：ROW()
文档编辑器帮助：COLUMN()

- Selfish

4

遗憾的是这对我不起作用。尽管有很多重复项，但它并没有突出显示任何内容。不确定为什么，听起来像一个好的解决方案。 - JVC

1

@Amanda - 是的，稍微有点创意就可以实现。我这周不在，但是过一周半再联系我，我很乐意帮忙。想法是创建一个AND语句（使用*）和另一个检查，所以大致如下：`((COUNTIF(...))*(NOT(ISBLANK(INDIRECT(...当前单元格...))))。这是我在手机上能做到的最好的了。 :) - Selfish

2

这正是所需的解决方案。它就像一个HASHMAP一样工作，用于识别重复项。 - Akh

"=countif(A:A,A1)>1" 公式突出显示了一些不是重复项的字段；没有时间进行调试，但是这个公式没有同样的问题。 - Jeff Axelrod

1

运行得很好，谢谢@Selfish！节省了我的时间。注意：唯一需要更改的是$A$1:$C$50 - 根据所涉及的列进行相应更改。我更喜欢这种更通用的方法，而不是zolley的方法。 - boldnik

显示剩余4条评论

32

@zolley的回答是正确的。只需添加一个GIF和步骤供参考。

进入菜单 格式 > 条件格式..
找到 如果符合以下条件，则设置格式为..
在字段 自定义公式是 中添加 =countif(A:A,A1)>1。
- 注意：将字母 A 更改为您自己的列。

- maheshwaghmare

2

在突出显示重复项后，我如何按颜色对它们进行筛选？ - Moeez

@Faisal 只能使用脚本或插件。 - player0

28

从“文本包含”下拉菜单中选择“自定义公式为:”，并写入：“=countif(A:A, A1)>1”（不带引号）。

我完全按照zolley的建议去做了，但应该做出一些小修正：使用“自定义公式是”而不是“文本包含”。然后条件渲染将起作用。

- Alex Barkun

1

我没有给你点踩（我今天才看到这个问题），但是根据你的要求，这里有一个对你回答的评论。我认为公式中不需要分号。此外，在 Google Sheets 的条件格式面板中，我仍然看到一个下拉菜单。至少，我点击选择选项，就会出现一长串选项，我通常称之为下拉菜单。最初出现在下拉菜单中的选项通常是“文本包含”，因此用户通常会单击它以访问下拉菜单。 - Paul de Barros

感谢您的评论，Paul。也许我误解了：我理解"文本包含"(或"如果格式为...")有一个子下拉菜单"自定义公式是:"。此外，"文本包含"应该有相应的子字符串值，而不是"= countif(A:A,A1)> 1;"。事实上这是另一种方法。 - Alex Barkun

21

在C列中突出显示重复项：

=COUNTIF(C:C, C1) > 1

Explanation: 这里的C1并不是指C列中的第一行。因为此公式是通过条件格式规则进行评估的，所以在检查公式是否适用时，C1有效地指当前正在评估以确定是否应该应用突出显示的任何行。(因此，如果对你有意义的话，它更像是INDIRECT(C &ROW())！)实质上，在评估条件格式公式时，任何引用第1行的内容都会针对运行公式的行进行评估。(是的，如果您使用C2，则表示要检查当前正在评估的下一行的状态。)

所以这个公式的意思是，在整个C列中计数出与当前正在评估的单元格中的任何值相同的出现次数，并且如果超过1个(即该值存在重复项)，则：应用突出显示。(因为整个公式的计算结果为TRUE。)

仅突出显示第一个重复项：

=AND(COUNTIF(C:C, C1) > 1, COUNTIF(C$1:C1, C1) = 1)

说明：这只会在两个COUNTIF都为TRUE时（它们出现在一个AND()函数内）进行突出显示。

要计算的第一个术语（COUNTIF(C:C,C1)>1）与第一个示例完全相同；只有当C1中的内容重复时，它才为TRUE。（请记住，C1实际上是指当前要检查是否应该突出显示的行）。

第二个术语（COUNTIF(C$1:C1,C1)=1）看起来相似，但有三个关键差异：

它不搜索整个C列（就像第一个公式一样：C:C），而是从第一行开始搜索：C$1 （$强制它严格查看第一行，而不是正在计算的任何行）。

然后在当前正在评估的行C1处停止搜索。

最后说=1。

因此，只有当在当前行之前没有重复项时（这意味着它必须是重复项中的第一个），它才为TRUE。

与第一个术语结合使用（它只有在此行具有重复项时才为TRUE），这意味着只会突出显示第一次出现的重复项。

突出显示第二个及以后的重复项：

=AND(COUNTIF(C:C, C1) > 1, NOT(COUNTIF(C$1:C1, C1) = 1), COUNTIF(C1:C, C1) >= 1)

说明：第一个表达式与以往相同（如果当前评估的行在所有行中是重复的，则为TRUE）。

第二个表达式与最后一个完全相同，只是它被取反了：它周围有一个NOT()。所以它忽略了第一次出现的情况。

最后，第三个表达式捕捉到2、3等重复项。COUNTIF(C1:C, C1) >= 1从当前评估的行开始搜索范围（C1:C中的C1）。然后它仅在此行以下（包括此行）存在一个或多个重复项时（应用高亮）才评估为TRUE：>= 1（它必须是>=而不仅仅是>，否则最后一个重复项将被忽略）。

- RedYeti

3

我尝试了所有选项，但都没有起作用。

只有谷歌应用程序脚本才帮助了我。

来源：https://ctrlq.org/code/19649-find-duplicate-rows-in-google-sheets 在您的文档顶部

1.- 转到 工具 > 脚本编辑器 2.- 设置您的脚本名称

3.- 粘贴此代码：

function findDuplicates() {
  // List the columns you want to check by number (A = 1)
  var CHECK_COLUMNS = [1];

  // Get the active sheet and info about it
  var sourceSheet = SpreadsheetApp.getActiveSheet();
  var numRows = sourceSheet.getLastRow();
  var numCols = sourceSheet.getLastColumn();

  // Create the temporary working sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var newSheet = ss.insertSheet("FindDupes");

  // Copy the desired rows to the FindDupes sheet
  for (var i = 0; i < CHECK_COLUMNS.length; i++) {
    var sourceRange = sourceSheet.getRange(1,CHECK_COLUMNS[i],numRows);
    var nextCol = newSheet.getLastColumn() + 1;
    sourceRange.copyTo(newSheet.getRange(1,nextCol,numRows));
  }

  // Find duplicates in the FindDupes sheet and color them in the main sheet
  var dupes = false;
  var data = newSheet.getDataRange().getValues();
  for (i = 1; i < data.length - 1; i++) {
    for (j = i+1; j < data.length; j++) {
      if  (data[i].join() == data[j].join()) {
        dupes = true;
        sourceSheet.getRange(i+1,1,1,numCols).setBackground("red");
        sourceSheet.getRange(j+1,1,1,numCols).setBackground("red");
      }
    }
  }

  // Remove the FindDupes temporary sheet
  ss.deleteSheet(newSheet);

  // Alert the user with the results
  if (dupes) {
    Browser.msgBox("Possible duplicate(s) found and colored red.");
  } else {
    Browser.msgBox("No duplicates found.");
  }
};

4.- 保存并运行

不到3秒钟，我的重复行就被着色了。只需复制粘贴脚本即可。

如果您不了解Google应用脚本，这些链接可能会对您有所帮助：

https://zapier.com/learn/google-sheets/google-apps-script-tutorial/

https://developers.google.com/apps-script/overview

希望这能帮到您。

- JRichardsz

这段代码可以修改以仅将列中重复的字段突出显示为红色，而不是整行吗？ - Amr

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- zolley · Accepted Answer

尝试以下步骤：

选择整个列
点击格式
点击条件格式
点击添加另一个规则（或编辑现有/默认规则）
将如果格式单元格设置为：自定义公式是
将值设置为：=countif(A:A,A1)>1（或将A更改为您选择的列）
设置格式样式。
确保范围应用于您的列（例如，A1：A100）。
点击完成

任何写在A1:A100单元格中的内容都将被检查，如果存在重复项（出现超过一次），则它将被标记颜色。

对于使用逗号 (,) 作为小数分隔符的区域设置，参数分隔符很可能是分号 (;)。因此，请尝试：=countif(A:A;A1)>1。

对于多列，请使用countifs。