Excel 2007 函数,最后三项

Excel 2007 函数,最后三项

我有两个问题,所以请耐心阅读。我正在制作一种银行电子表格。我将在四列(日期/金额/类型/说明)中输入收入/支出,我需要它来跟踪我的日常支出。我已经有了它,所以如果日期不是今天,它不会添加/减去它,直到它是。另外,我有两/三/四周后的内容。但是,我想添加一些类似于“最后三张薪水支票”的内容。“类型”列只有三个可能的条目,“收入”、“转账”和“支出”。如果我能找到一个适用于其中一个的函数,我相信我可以轻松地让它与其余的一起工作。我需要它显示最近的“收入”金额。例如:

日期 金额 类型

9 月 1 日 100 收入

9 月 2 日 100 费用

9 月 3 日 100 收入

9 月 4 日 100 费用

9 月 5 日 100 收入

9 月 6 日 100 收入

9 月 7 日 100 收入

假设今天是 9 月 6 日。我希望它显示 9 月 5 日的金额、9 月 5 日的金额和 9 月 3 日的金额。我不想显示费用,现在还不到 9 月 7 日。它必须有三个函数(每个框一个),那么我该如何让它执行最近的、倒数第二的等等?如果我能让它工作,我也可以编辑以获取描述,并在需要时将其重新调整为费用。

问题2:

我想要一个“年度检查”之类的功能。我该如何让它查找每种类型(与上文相同),然后设置截止日期,这样它就只有一年?

答案1

这可能有助于您获取每种类型的最近三笔交易。如果您已为数据表使用了命名范围,这将很有帮助。

要获取最近 3 个收入日期,可以使用以下数组公式:

=INDEX(dates,LARGE(ROW(dates)*(dates<=TODAY())*(types="Income"),XXX)-1,1)

在哪里:

日期-- 包含日期的单元格范围/列范围
类型-- 包含“收入”、“转账”或“支出”的单元格区域/列
XXX-- 包含 1(最近)、2(第二最近)或 3。

要获取最新的金额和说明,请将dates上述公式中的第一个实例替换为包含金额和说明的单元格范围。

=INDEX(amounts,LARGE(ROW(dates)*(dates<=TODAY())*(types="Income"),XXX)-1,1)

&

=INDEX(descriptions,LARGE(ROW(dates)*(dates<=TODAY())*(types="Income"),XXX)-1,1)

在下面的例子中,日期指的是A2:A100金额指的是B2:B100类型指代C2:C100描述指的是D2:D100

在此处输入图片描述

为了使公式起作用,需要对数据表进行排序根据日期(从最旧到最新)。务必使用Ctrl+ Shift+提交公式Enter。这还假设每种交易类型都有唯一的日期(即单个日期没有多个收入条目)。


如果您不想按日期对表格进行排序,您可以尝试以下数组公式:

=INDEX(dates,
 MATCH(SMALL(IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),XXX),
             IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),0),1)

在哪里:

日期-- 包含日期的单元格范围/列范围
类型-- 包含“收入”、“转账”或“支出”的单元格区域/列
XXX-- 包含 1(最新)、2(第二最新)或 3

再次,要获取最新的金额和说明,请将dates上述公式中的第一个实例替换为包含金额和说明的单元格区域。如下所示:

=INDEX(amounts,
 MATCH(SMALL(IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),XXX),
             IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),0),1)

=INDEX(descriptions,
 MATCH(SMALL(IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),XXX),
             IF((dates<=TODAY())*(types="Income"),(TODAY()-dates),""),0),1)

相关内容