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