如何根据可用性表计算第二天/同一天?

用户名

我正在研究一个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


特里·W

好吧,这个任务花了我比预期更长的时间,最后我在几个助手专栏的帮助下提出了一个解决方案(不确定是否可以选择吗?)。

因此,假设您具有以下命名范围

  • TblA_Date是您中的所有日期Table A(这是可选的,因为我没有在解决方案中使用它...);
  • TblB_Date是您中的所有日期Table B

首先,我“扩展”Table B到一个列出所有日期的列表结构:

展开表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中查找所需日期的位置

  1. “对于表A中的每个日期,结果日期是同一日期或下一个更高的日期” 并且
  2. from的日期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] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

如何从给定时刻的一天日期时间第二天结束了吗?(JAVA)

MySQL:如何在同一行中显示前一天的数据和第二天的数据

如何找到第二天?

第二天,跳过周末

每月第二天到最后一天的Cron表达式

UserPrincipal.Current从一天到第二天抛出COMException

创建时间戳在同一天的新表

代码无法在同一天上载多个文件,并且无法创建第二天和下个月的上载

如何在php中找到第二天

如何将时差限制在同一天?

如何计算到下个月的第二天?

R:在提取同一天的最大次数后,如何根据累积数据计算2天持续时间的发生率?

Google云端硬盘上的远程存储库一天可以正常运行,第二天可以分解,而无需我触摸

Ubuntu硬盘一天装满,第二天清空

重击:计算第二天早上7点

如何在Python中查找同一小时(包括DST)的第二天的Unix时间戳?

计算从第二天开始的日期之间的工作日

计算事件发生的第二天

根据用户输入来计算一天:总是返回同一天

如何输出第二天的日期时间

在同一天在日历中设置时间将移至第二天

<=今天同一天的日期

需要在 MySQL 中找到从一天下午 5 点到第二天凌晨 5 点的总数(总和)

如何计算同一天的持续时间?

如何根据用户输入的日期获取第二天的日期?

如何在R中从下午5点到第二天凌晨2点算作一天?

如何计算同一天发生的客户行为?

随着时间的推移计算第二天有多少物品到达

Angular:当我按“n”键时,在输入字段日期中,我希望我的一天移到第二天