代码之家 › 专栏 › 技术社区 › Rexksvii

Excel-根据ID、开始日期和顺序自动填充日期列(参考)

excel-formula excel

Rexksvii · 技术社区 · 7 年前

在我的工作簿中,我有4列。A列是ID,B列是生效日期(开始日期),C列是序列号(这是A列和B列中重复数据的参考号),最后是D列,结束日期。我想匹配A、B和C列,看看它是否是同一员工当天的最后一条记录(由ID确定)。

例如(我想要的示例结果):

 A        B        C        D
1001  12/03/2018   1    12/03/2018
1001  12/03/2018   2    12/03/2018
1001  12/03/2018   3    12/03/2018
1001  12/03/2018   4    31/12/2018
1001  13/03/2018   1    13/03/2018
1001  13/03/2018   2    31/12/2018
1001  14/03/2018   1    31/12/2018
1002  12/03/2018   1    12/03/2018
1002  12/03/2018   2    31/12/2018
1002  13/03/2018   1    31/12/2018

如您所见,2018年3月12日,员工ID 1001有4条记录,因此A列和B列将重复4次,因此我在C列中添加了一条记录,以确定哪条记录首先进入工作表。那么,前3条记录的结束日期(D列)将与B列的日期相同,但最后一条记录(序号4)的结束日期将为2018年12月31日。我需要通过比较列A、B和C来自动填充列D。

之前,我发现了这样一个IF函数

=IF(COUNTIF(A:A,$A2)=$C2,DATEVALUE("31/12/2018"),$B2)

如果ID 1001在序列4处停止,则其工作,但如果为ID 1001插入新日期(例如,日期为2018年3月13日的1001),则其不工作。

2 回复 | 直到 7 年前

Glitch_Doctor 7 年前

=IF(SUM(IF(A:A&B:B=$A2&$B2,1))=$C2,DATEVALUE("31/12/2018"),$B2)

这是一个数组公式,通过验证 Ctrl键 + 转移 + 进来 仍在公式栏中时。

SUM(IF(A:A&B:B=$A2&$B2,1)) 被用作 COUNTIFS() 因为我需要将日期值附加到您的ID以生成每天唯一的值 COUNTIFS() 仅对范围而不是数组进行操作。

A1&B1 是一种懒惰的实现方式 CONCATENATE(A1,B1) 为了将字符串值绑定在一起,我们对 IF() 符合逻辑,以便我们可以评估阵列:

{"100143171";"100143171";"100143171";"100143171";"100143172";"100143172";"100143173";"100243171";"100243171";"100243172"}

而不是只使用:A:

{1001;1001;1001;1001;1001;1001;1001;1002;1002;1002}

我们需要它作为数组公式的原因是 SUM() 评估来自的所有返回值 如果() 单独进行。要了解公式在各个部分中的作用,最好的方法是在突出显示部分时使用F9。您需要缩小结果的范围,使其不至于成为整列,因为它需要在公式栏中进行调整。

gries 7 年前

如果不想使用数组公式,可以始终使用 =COUNTIFS()

=IF(COUNTIFS(A:A,A2,B:B,B2)=C2,DATEVALUE("31/12/2018"),B2)

推荐文章

Arthur Filipe da Silva · 如何将数据集转换为存在/不存在矩阵?[副本]

2 年前

Thinkpad · Excel引用基于字符串的范围

2 年前

haku · 如何从作为某个值的子字符串的查找表中获取第一条记录?

2 年前

user161284 · 是否基于在多个单元格中找到某个字符串来设置单元格的条件格式?

2 年前

Gabriel Pierce · 返回Excel中的最新条目

3 年前

mak47 · 如何基于另一个单元格将单元格值限制为最大值?

3 年前

FINISHUT · 如何在excel中合并多个序列?

3 年前

Faye D. · 把1_2转到GetOne 2

3 年前

D T · 如何为Excel Com设置公式?

3 年前

CodingN00b · 仅当日期在特定范围内时才运行excel公式

3 年前