☰

困难的 Excel 公式

tag-icon • tag-icon microsoft-excel
困难的 Excel 公式

我正在尝试编写一个公式，以便生成下面的“目标：状态”列：

File Date    Frequency   Lag   Expected Date   Today's Date  Goal: Status   Current Formula
10/31/2020    1          15     12/15/2020      11/20/2020     Green          Green
9/30/2020     3          5      1/4/2021        11/20/2020     Green          Green
6/30/2020     3          15     9/15/2020       11/20/2020     Red            Red
9/30/2020     1          20     10/19/2020      11/20/2020     Yellow         Green
9/30/2020     1          30     11/30/2019      11/20/2020     Green          Green
9/30/2020     1          10     11/10/2019      11/20/2020     Yellow         Green

总体逻辑如下：

  1. 如果频率为 3 且文件日期与上一季度末相同，则为绿色。
  2. 如果频率为 1，且文件日期与上个月底相同，则为绿色。
  3. 如果文件日期不是上个月或季度末的日期，请使用预期日期（文件日期 + 频率 + 滞后）查看今天的日期是否早于该日期。如果是，则为绿色，因为我们预计下一个文件要等到预期日期过去后才会出现。
  4. 如果文件日期不是上个月或季度末的日期，或者预计日期与今天的日期相比已经过去两个月或更长时间，则为红色。
  5. 如果文件日期不是上个月或季度末的日期，或者预计日期与今天的日期相比已过去少于两个月或两个月以上，则为黄色。

逐行逻辑：

  1. 频率为 1，文件日期为 2020/10/31 - 绿色
  2. 频率为 3，文件日期为 2020 年 9 月 30 日 - 绿色
  3. 文件日期与上个月末或季度末不同，根据下一个文件的频率，预计日期为 2020 年 9 月 15 日。距离今天的日期已超过两个月，所以是红色的。
  4. 频率为 1，文件日期不是上个月末，预计日期距今天日期不到两个月，因此为黄色
  5. 频率为 1，但文件日期为 2020 年 9 月 30 日，但预期日期晚于今天的日期，早于预期日期，因此为绿色。
  6. 频率为 1，但文件日期为 2020 年 9 月 30 日，预计日期比今天的日期早不到两个月，因此为黄色。

我当前的公式考虑了最新月份和季度末，但没有考虑频率，因此它只适用于前两行：

=IF(A2=DATE(IF(AND(MONTH(TODAY())>=1,MONTH(TODAY())<4),YEAR(TODAY())-1,YEAR(TODAY())),IF(AND(MONTH(TODAY())>=1,MONTH(TODAY())<4),12,IF(AND(MONTH(TODAY())>=4,MONTH(TODAY()*2)<7),3,IF(AND(MONTH(TODAY())>=7,MONTH(TODAY())<10),6,9))),IF(AND(MONTH(TODAY())>=1,MONTH(TODAY())<7),31,30)),"Green",IF(A2=(EOMONTH(TODAY(),-1)),"Green","Red"))

答案1

也许这会有所帮助。上个月底：

=EOMONTH(TODAY(),-1)

上季度末：

=EOMONTH(TODAY(),-MOD(MONTH(TODAY())-1,3) -1)

*通过确定当前第 1、2 或 3 季度的月份数并使用 EOMONTH 从当前日期中扣除该月份数来工作。例如，7 月是扣除 1 个月的月份，8 月是扣除 2 个月的月份，以追溯至 6 月底。

您可以使用公式 > 定义名称将这两个公式定义为名称“lastmonth”和“lastquarter”

现在您可以直接进行日期 IF 比较：

=IF(Frequency = 3, IF(date = lastquarter, "green", else...), IF(Frequency = 1, IF(date = lastmonth, "green", else...)

或更紧凑

=IF(date = IF(Frequency = 1, lastmonth, lastquarter), "green",  IF(today < expected date, "green",  today < EDATE(expected date,2), "yellow", "red")

相关内容