☰

Excel 是否可以检查数组中最新的可能日期？

tag-icon • tag-icon microsoft-excel
Excel 是否可以检查数组中最新的可能日期？

例子

如示例表所示，只有发生交易时，余额才会显示在表格中。在此之前，余额将与上个月相同。

例如：2021 年 8 月未显示在表中，因为该月没有进行任何交易。因此，2021 年 8 月余额应等于之前的可用余额，即 2021 年 6 月。

然而，交易是在 2021 年 10 月进行的。因此，2021 年 10 月余额应该等于 2021 年 10 月余额。

11月发生了3笔交易。因此，11月余额应该等于11月最新交易显示的余额。

我正在尝试制作一张包含一年中每个月的表格，并从示例表格中获取正确的余额。但是，如果我使用索引匹配，我只能选择发生交易的月份，而不是 2021 年 8 月。

Excel 是否可以确定并自动回归到最新的可用余额？

答案1

有不同的方法可以解决这个问题。

如果你坚持一个公式，那就让我们一起构建它吧。

这MATCH() 函数将完成大部分工作。它有一个惊人的特性： MATCH 找到最大值

因此，借助 MATCH() 我们将找到所需行的编号，借助 INDEX() 我们将获得所需的值： =INDEX(<Balance_range>;MATCH(...))

MATCH() 将查找格式为 2012-07 的日期值。这很容易做到，使用TEXT() 函数：TEXT(<Date_cell>;"YYYY-MM")

任务中最困难的部分是将月份的字母名称转换为其数值，即月份的序数。 MATCH() 也可以做到这一点： MATCH(<Cell_from_MM>; {"Jan"\"Feb"\"Mar"\"Apr"\"May"\"Jun"\"Jul"\"Aug"\"Sep"\"Oct"\"Nov"\"Dec"};0)

因此，使用公式

{=$B$3:$B$12&"-"&TEXT(MATCH($A$3:$A$12; {"Jan"\"Feb"\"Mar"\"Apr"\"May"\"Jun"\"Jul"\"Aug"\"Sep"\"Oct"\"Nov"\"Dec"};0);"00")

我们可以从列中获取年月数组毫米和是

MM  YYYY    Formula
Dec 2020    2020-12
Jun 2021    2021-06
Oct 2021    2021-10
Nov 2021    2021-11
Nov 2021    2021-11
Nov 2021    2021-11
Dec 2021    2021-12
Dec 2021    2021-12
Jan 2022    2022-01

MATCH() 函数将尝试在此数组中找到合适的值。

现在让我们将所有这些部分收集到一个公式中：

{=INDEX($C$3:$C$12;MATCH(TEXT(F3;"YYYY-MM");$B$3:$B$12&"-"&TEXT(MATCH($A$3:$A$12; {"Jan"\"Feb"\"Mar"\"Apr"\"May"\"Jun"\"Jul"\"Aug"\"Sep"\"Oct"\"Nov"\"Dec"};0);"00")))}

这是一个数组公式，必须使用组合键完成输入Ctrl+Shift+Enter

结果可能是这样的： 结果表

单元格 F3 中的值为 2021-01-01，单元格 F4 中的值为=EOMONTH(F3;1)

相关内容