☰

为组中的每一行填充其他列中的列

tag-icon • tag-icon microsoft-excel
为组中的每一行填充其他列中的列

我正在进行数据迁移练习，并且有超过 100K 行的 as/sheet，因此我需要一个超出手动更新的解决方案。

为了便于本文，我将简化问题：我有一个包含三列（A、B 和 C）的 as/sheet，标题 [数据类型] 如下：名称 [文本]、ID [数字]、创建日期 [日期]。数据已按 A 列排序，以刻意突出该列中的重复值。ID 是唯一的；日期是不是独特的。

在该示例中，有 9 行可以根据 A 列中的重复值“分组”为三组。因此，第 2 行和第 3 行的 A 列中的值为abc，第 4、5 和 6 行的 A 列中的值为def，第 7-10 行的 A 列中的值为ghi。

第一个任务是确定每个“行组”中最新的 CreateDate。因此，在此示例中，第 2 行和第 3 行的 CreateDate 为 5/11/1999，第 4 行至第 6 行的 CreateDate 为 3/12/2001，第 7 行至第 10 行的 CreateDate 为 5/11/1999。我使用{=MAX(IF(A2=$A:$A,$C:$C))} D 列中的数组公式来执行此操作。

下一个任务很困难。现在我已经确定了每个行组的最新日期，我想将与该 LatestDate 相对应的 ID 放在 E 列（在行组的每一行上），以便结果看起来像下面示例中的 E 列。但我需要使用公式/函数来执行此操作，而不是手动执行。这是针对迁移项目的，因此不必很漂亮。

将匹配的 ID 放在同一行的 E 列中很容易（IF 语句）——参见第 3、6 和 9 行——但我并不假装这就是解决方案。但我不知道如何用相同的 ID 填充每个组中的其他行——在我附加的示例中，应该是第 2、4 和 5 行、第 7 和 8 行以及第 10 行。我不能只在 C 列中进行查找，因为这些值不是唯一的。在尝试了各种公式但徒劳无功后，我不知所措。我猜它需要依赖于 C 列（匹配的值）和诸如INDEX和之 类的函数MATCH……或者可能是 VBA。

     +------+------+------------+------------+-----------------+
     |  A   |   B  |     C      |     D      |        E        |
+----+------+------+------------+------------+-----------------+
|  1 | Name |  ID  | CreateDate | LatestDate | CorrespondingID |
|  2 | abc  |   1  | 4/12/1998  | 5/11/1999  |         2       |
|  3 | abc  |   2  | 5/11/1999  | 5/11/1999  |         2       |
|  4 | def  |   3  | 1/12/1999  | 3/12/2001  |         5       |
|  5 | def  |   4  | 5/11/1999  | 3/12/2001  |         5       |
|  6 | def  |   5  | 3/12/2001  | 3/12/2001  |         5       |
|  7 | ghi  |  17  | 1/17/1999  | 5/11/1999  |        55       |
|  8 | ghi  |  42  | 2/4/1999   | 5/11/1999  |        55       |
|  9 | ghi  |  55  | 5/11/1999  | 5/11/1999  |        55       |
| 10 | ghi  |  83  | 3/28/1999  | 5/11/1999  |        55       |
+----+------+------+------------+------------+-----------------+

（较短的示例可作为图像使用。）

答案1

我的参考是这里. TLDR：使用非数组版本的索引匹配公式。

在 E2 中输入：

=INDEX(B:B,MATCH(1,INDEX((A2=A:A)*(D2=C:C),0,1),0))

想法：内部 index() 生成一个符合名称和日期条件的 0 和 1 列表。然后使用外部 index() 从 A:A 中“加载”名称。

答案2

此解决方案使用偏移匹配方法。使用匹配查找日期，然后根据日期偏移查找相应的 ID：

=OFFSET(C2,MATCH(D2,C2:C,0)-1,-1)

将其放入 E2 单元格，然后向下拖动到 E

答案3

B对于给定的行（例如，第 2 行），您想要从行中 找到 ID（列 ）n（即 Cell  ），其中名称 ( ) 等于当前行中的名称 ( )，而 CreatedDate ( ) 等于当前行中的 LatestDate ( )。行 BnAnA2CnD2n是唯一的，因为 ID 是唯一的，所以“这”行是最大行。一个合乎逻辑的答案是对 Column 公式进行微小的扩展 D：

=MAX(IF(AND(A2=$A:$A,D2=$C:$C), $B:$B))

不幸的是，AND在数组公式中似乎不起作用。因此我们使用一个标准技巧： TRUE = 1（或除零以外的任何数字）和 FALSE = 0，这样我们就可以AND用乘法来模拟（AND(TRUE,TRUE) = TRUE 就像 1 × 1 = 1，  =  就像 0 × AND(FALSE,anything)FALSE任何事物 = 0）。因此我们将上式改为

=MAX(IF((A2=$A:$A)*(D2=$C:$C), $B:$B))

（当然，以数组公式形式输入，使用Ctrl++ ）：ShiftEnter

请注意，这适用于非唯一 CreatedDates。即使 CreatedDates 的顺序与 ID 的顺序不同，它也能正常工作，如上例所示（其中数据按 Columns A和 排序B）。

相关内容