我有一组如下数据。请注意，并非所有日期都已显示。
我正在寻找一个 Excel 365 公式，当传递样本日期时，将返回表内早于或等于样本日期的日期，相应的值是第一个大于 0 的值。
- 因此，对于 2 月 1 日至 8 日，公式将返回 01-Feb-21，因为这是表中的第一个日期。
- 对于 10 日至 16 日，它将返回 10 日，因为这是值首次超过 0 的日期。
- 对于表的第 17 个，它将返回第 17 个。
我的一次尝试：
=SUMPRODUCT(--(Table1[Date]<=D2),--(Table1[Value]>0),Table1[Date])- 对于 2 月 2 日，前两个日期为真，前两个数字也为真，因此它将日期相加并返回 08-Mar-42。
D2在上述公式中包含一个在 2 月 1 日至 2 月 25 日范围内的日期。
我已尝试处理=MIN(IF((Table1[Date]<=D2)*(Table1[Value]>0),Table1[Date],"-"))2 月 1 日的返回值。
任何帮助将不胜感激。
答案1
有趣的问题。
为了帮助我理解这一点，我用以下文字表达出来：
我们希望返回参数日期之前或当天的所有日期中的最小值，介于值为零的最大日期和参数的日期之间
我认为逗号后面的部分是比较棘手的部分，因为系列中后面的日期可能会有多个行前面带有零。
无论如何，我认为这会起到作用：
=MIN(FILTER(Table1[Date],Table1[Date]>IFERROR(MAX(FILTER(Table1[Date],(Table1[Value]=0)*(Table1[Date]<=Table1[@Date]))),MIN(Table1[Date])-1)))
为了将其分解，我们首先要获取参数 date 之前值为零的日期列表。我们这样做：
=FILTER(Table1[Date],(Table1[Value]=0)*(Table1[Date]<=Table1[@Date]))
但是从该列表中，我们只想要最新带有零的行，因此我们也使用 MAX：
=MAX(FILTER(Table1[Date],(Table1[Value]=0)*(Table1[Date]<=Table1[@Date])))
这里的问题是，对于第一组行（2 月 8 日之前），没有包含较小日期且为零的行，因此它返回 #CALC！错误：
为了解决这个问题，我们将其包装在 IFERROR 中，这样如果上述公式返回错误，我们就让它返回整个日期系列的最小值：
=IFERROR(MAX(FILTER(Table1[Date],(Table1[Value]=0)*(Table1[Date]<=Table1[@Date]))),MIN(Table1[Date]))
我们在这里试图获取的是（零行日期）和（表中第一个日期之前的最大日期）集合中的最大日期。后一点意味着我们需要从 IFERROR 的第二个参数中减去 1，这样它就不会返回 2 月 1 日（第一个有值的日期），而是返回前一天：
=IFERROR(MAX(FILTER(Table1[Date],(Table1[Value]=0)*(Table1[Date]<=Table1[@Date]))),MIN(Table1[Date])-1)
好的，这很好。现在我们有了每行最近的零行的日期（或表中第一个日期之前的日期）。
所以我们需要过滤以下日期那date 但早于或等于参数 date，则获取结果范围内的最小值。
这个公式可以做到这一点：
=FILTER(Table1[Date],Table1[Date]>IFERROR(MAX(FILTER(Table1[Date],(Table1[Value]=0)*(Table1[Date]<=Table1[@Date]))),MIN(Table1[Date])-1))
然后，为了获取日期列表中最小的日期，只需将整个日期包装在 MIN 中，如我的答案顶部所示。
答案2
您可以嵌套过滤函数vlookup来获得您想要的结果，例如：
=VLOOKUP(DATEVALUE("02/16/2021"),FILTER(A2:B18,B2:B18>0),2,true)
答案3
尽管不稳定，你可以尝试OFFSET()一下：
公式C2：
=IF(OFFSET([@Val],-1,0)=0,[@Date],IF([@Val]=0,OFFSET([@Date],-1,2),MIN([@Date],OFFSET([@Date],-1,2))))