☰

如何重新组织两列重复的数据

tag-icon • tag-icon microsoft-excel worksheet-function
如何重新组织两列重复的数据

如果标题让人困惑，请见谅。我几乎整晚都在努力解决这个问题，但我不知道该如何表述。任何帮助都将不胜感激。

我有一张电子表格，其中 A 列是姓名，B 列是身份证号码。它看起来像这样

Sally     1004
Sally     1005
Sally     1006
Robert    1007
Robert    1008

等等。

我有另一个电子表格，由于某些情况，我无法以任何方式合法地过滤具有如下名称的电子表格

Sally
Sally
Robert
Robert
Robert

它们都是按字母顺序排列的，但正如您在 Sally 的例子中看到的，她在第一张表中出现的次数比在第二张表中出现的次数多，这完全没问题。但是，Robert 在第二张表中出现的次数比在第一张表中出现的次数多，我需要它返回某种错误。我还需要 Sheet2 具有 ID 号，以便它们出现。这是最终目标

Sally     1004
Sally     1005
Robert    1007
Robert    1008
Robert    #N/A           (or any other error)

我花了几个小时试图找到一个可行的数组公式，但没有成功。如果您知道我应该怎么做，我将不胜感激。

答案1

使用以下公式：

=INDEX(Table1[ID],SMALL(IF(A2=Table1[name],ROW(Table1[name])-ROW(Table1[[#Headers],[name]]),10^9),COUNTIF(Sheet2!$A$1:A2,A2)))

当然这也是一个数组公式。

  • IF(A2=Table1[name],ROW(Table1[name])-ROW(Table1[[#Headers],[name]]),10^9)
    • 返回源表中包含实际名称的行的行号
      （通过表标题的行号进行更正，以便在索引函数中正常工作）
    • 返回10^9其他人（只是为了得到一个大数字而不是令人困惑的SMALL函数，也许""也会起作用）
  • COUNTIF(Sheet2!$A$1:A2,A2)- 计算当前行上方的名称数量
  • SMALL(IF(...),10^9),COUNTIF(...))- 返回下一个最小（更正后的）行号
  • =INDEX(Table1[ID],SMALL(...))- 返回ID

为了公式的更好的可读性，我将您的第一个范围转换为表格，当然您也可以使用地址。

在此处输入图片描述

相关内容