我正在研究一个excel公式,以基于excel表查找下一个可用的日期。我有两个表,A和B,表A具有日期和结果列,表B具有日期和计数,我想根据表A和B中的日期计数将表B中的日期获取到表A的结果列中。请注意,表A“结果”列中的日期是预期的输出,并且始终与表A的日期列中的日期相同或更高。日期格式为月/日/年
table A
Date Result
01-02-2018 01-02-2018
01-02-2018 01-02-2018
01-05-2018 01-05-2018
01-05-2018 01-05-2018
01-05-2018 01-07-2018
01-05-2018 01-07-2018
01-06-2018 01-07-2018
01-07-2018 01-10-2018
01-09-2018 01-10-2018
01-11-2018 01-12-2018
01-12-2018 01-12-2018
table B
Date Count
01-02-2018 3
01-03-2018 1
01-04-2018 3
01-05-2018 2
01-07-2018 3
01-10-2018 3
01-12-2018 2
好吧,这个任务花了我比预期更长的时间,最后我在几个助手专栏的帮助下提出了一个解决方案(不确定是否可以选择吗?)。
因此,假设您具有以下命名范围:
Table A
(这是可选的,因为我没有在解决方案中使用它...);Table B
。首先,我将您“扩展”Table B
到一个列出所有日期的列表结构:
J2
我的示例中“单元格”中第一个帮助器列的公式为:
=SUM($I$2:I2)
这是要查找中的计数的总计
Table B
。
Cell中第二个帮助器列的公式为M2
:
=INDEX(TblB_Date,MATCH(1,--(ROW(A1)<=$J$2:$J$8),0))
这是通过
Table B
使用行位置作为参考来“布置”所有日期,并将其与计数的总计进行比较并返回相应的日期。鉴于这是一个数组公式,您必须在编辑栏中完成公式后按Ctrl+ Shift+ Enter,否则它将无法正常运行。然后,您只需将公式向下拖动即可应用。请注意,我给第二个帮助器列
M2:M18
(不包括M19
错误)命名为TblB_Date_Expanded。
下一步是根据您的条件在TblB_Date_Expanded中查找所需日期的位置
Table B
只能使用不超过其对应的计数。单元格中的公式E2
为:
=MAX(MATCH(AGGREGATE(15,6,TblB_Date_Expanded/(A2<=TblB_Date_Expanded),1),TblB_Date_Expanded,0),E1+1)
A2
第一次约会在哪里Table A
,您将在下一个屏幕截图中看到。
逻辑是使用AGGREGATE函数返回
TblB_Date_Expanded
基于中给定日期的最接近开始日期Table A
,然后使用MATCH函数返回行位置,最后使用MAX函数将位置增加(1
如果已经使用了相同的位置)(在上面的行中)。
在已知行位置的情况下,我们可以TblB_Date_Expanded
使用以下公式轻松返回相应的日期:
=INDEX(TblB_Date_Expanded,E2)
想法,我无法克服的困难部分是在帮助器列中使用“循环引用”或“自我引用”。换句话说,我不确定如何使用单个公式返回值的数组,其值取决于其先验值。我希望有人可以提出一种更优雅的方式来解决这个问题:)
如果您有任何问题,请告诉我。干杯:)
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句