excel - 如何在 Excel 的多列中查找/突出显示重复值

Question

我需要查找/突出显示在我的工作表的 8 列中的每一列中出现的重复值（在此处找到）。这些列是从 D0 到 D8 并且其中有不同的行。我已经尝试过这里提供的解决方案。我使用的解决方案是；

(1) =LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF (COUNTIF(B$2:B$8,A$2:A$10)>0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>0,1,0)=2,0 ,1),0))))

和

(2) =INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $ A$2:$A$10)>0, 1, 0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>0, 1, 0)=2, 0, 1), 0))

我根据自己的工作表替换了这些值，但每次我都得到“0”作为答案，即使这是不可能的。只需查看前 2 行，就可以很容易地看出所有 8 列中都出现了值。

我是 Excel 的初学者，目前使用 Microsoft Excel 2016 for Mac（版本 16.16.14）。

标签： excelexcel-formulaexcel-2016

Answer 1

编辑#2

在您提供的第二个公式的嵌套IF函数中发现错误。

在您尝试执行的公式中，IF(IF(A)+IF(B)=2,0,1)但我认为正确的方法应该是IF((IF(A)+IF(B))=2,0,1)。

根据您提供的示例工作表，在 Cell 中输入以下公式J2：

=INDEX(A$2:A$861,MATCH(0,COUNTIF(J$1:J1,A$2:A$861)+IF((IF(COUNTIF(B$2:B$243,A$2:A$861)>0,1,0)+IF(COUNTIF(C$2:C$162,A$2:A$861)>0,1,0)+IF(COUNTIF(D$2:D$132,A$2:A$861)>0,1,0)+IF(COUNTIF(E$2:E$118,A$2:A$861)>0,1,0)+IF(COUNTIF(F$2:F$112,A$2:A$861)>0,1,0)+IF(COUNTIF(G$2:G$94,A$2:A$861)>0,1,0)+IF(COUNTIF(H$2:H$81,A$2:A$861)>0,1,0))=7,0,1),0))

请注意，它是一个数组公式，因此您需要在输入上述公式后执行以下操作：

选择范围，按CONTROL+U然后按COMMAND+RETURN

如果上述方法对您不起作用：

选择范围，按CONTROL+ U，然后按COMMAND+ SHIFT+RETURN

如果您仍然不清楚，请阅读此内容：如何在 Excel Mac 中使用数组公式（谁知道在 Excel for Mac 中使用数组公式是如此痛苦......）

向下拖动公式，直到列表返回#N/A错误，这意味着您已找到所有常见值。

假设您将此常用值列表命名为List，我在 Excel 2016 for Windows 中的经验是您返回源表，突出显示整个表，转到 下的条件格式Home Tab，转到New Rule...，转到最后一个选项，然后输入以下公式作为格式化规则：

=MATCH(A2,List,0)>0

鉴于Power Query在 中不受支持Excel 2011 and Excel 2016 for Mac，请忽略以下使用Power Query的方法。在这种情况下，FYI 电源查询方法比上述数组公式方法快得多……

从您的帖子和您提供的示例来看，我认为您希望找到所有8 列中出现的共同值，这意味着您将忽略出现在 7、6、5 或更小列中的值。

鉴于您正在处理大型数据集 ( 860 x 8)，我建议使用Power Query来解决问题。Power Query 是 Excel 2010 Professional Plus 和所有更高版本的 Excel 中的内置函数。

步骤是：

1. 使用选项卡下的From Table功能Data将数据加载到 Power Query 编辑器；
2. 突出显示所有列，然后使用选项卡下的Unpivot Columns功能Transform将表格转换为 2-Column 表格；
3. 通过计算不同的行来使用选项卡组列下的Group By功能，如下所示；TransformValue

4. 右键单击列标题右侧的过滤器8框以过滤列表，该列表将返回所有8列中出现的值；
5. 关闭并将列（实际上是一个单列表）加载到新工作表（默认情况下）。

以下是后台电源查询M码，仅供参考。所有步骤都使用编辑器的内置功能，非常简单。

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"d0", Int64.Type}, {"d1", Int64.Type}, {"d2", Int64.Type}, {"d3", Int64.Type}, {"d4", Int64.Type}, {"d5", Int64.Type}, {"d6", Int64.Type}, {"d7", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Value"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 8)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count"})
in
    #"Removed Columns"