![在电子表格中显示距当前日期最多 X 天的值](https://linux22.com/image/1600975/%E5%9C%A8%E7%94%B5%E5%AD%90%E8%A1%A8%E6%A0%BC%E4%B8%AD%E6%98%BE%E7%A4%BA%E8%B7%9D%E5%BD%93%E5%89%8D%E6%97%A5%E6%9C%9F%E6%9C%80%E5%A4%9A%20X%20%E5%A4%A9%E7%9A%84%E5%80%BC.png)
我希望得到一些帮助,以理清过去几年我在 LibreOffice Calc 中遇到的数据输入和摘要电子表格的混乱局面。电子表格相关部分的总体布局如下:
工作表:“数据输入”
|A |B |... |L |
|-----|-----|-----|-----|
|Date |Name |... |Value|
工作表:“摘要”
|A |... |E |G |
|-----|-----|------------|------------------------|
|Name |... |Total Values|Values from last 90 days|
目前,一切正常,但我希望在摘要表(即 G 列)中添加一个新功能。E 列运行一个复杂的公式来搜索数据输入表中的每一行,其中摘要列 A 中的单元格与数据输入列 B 中的单元格匹配。公式如下:
=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A1)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)))
我知道这绝对是令人困惑的。我将尝试简化以下公式:
=SUMPRODUCT((B1:B100=A1)*ISNUMBER(L1:L100),(L1:L100))
我想知道如何修改摘要列 G 的此公式,使其搜索仅限于最近 90 天。
我应该看到的一个例子:
工作表:“数据输入”
|A |B |...|L|
|--------|-----|---|-|
|19-08-13|Name1|...|2|
|19-07-25|Name2|...|1|
|19-01-01|Name1|...|3|
工作表:“摘要”
|A |...|E|G|
|-----|---|-|-|
|Name1|...|5|2|
|Name2|...|1|1|
编辑:以下出现 502 错误:
=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A6)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91))
答案1
这个问题描述了两个问题。一个是尝试调试一个长公式。通常,关键是将公式分解成其组成部分,并测试每个部分以查看其作用。首先保持逻辑块完整(即几个一起工作的表达式),以确定公式的哪个部分不起作用。如果错误不明显,请将该部分分解成其组成部分。
通过复制和粘贴每个部分来做到这一点,然后调整粘贴的部分使其成为一个独立的公式(例如添加等号)。如果问题是括号不平衡,请复制包含所有括号的整个表达式,然后删除其他内部表达式及其括号,这将更容易发现;仅从这个练习中,剩下的括号不平衡可能就会变得显而易见。复制和粘贴将确保问题包含在您的测试中;重新输入公式可以修复错误,并且测试时一切都正常,因此它不具有诊断目的。
另一个问题是将结果限制为过去 90 天。这可以类似于公式已经将结果限制为匹配名称的方式完成 - 在 SUMPRODUCT 中的表达式列表中添加一个测试。为了简单起见,我将省略使用 INDIRECT 构建范围的复杂性,而只显示固定范围。在上下文中,附加参数可能如下所示:
=SUMPRODUCT( ... (L1:L100), (A1:A100>TODAY()-91) ... )
(L1:L100) 只是为了在问题中的简化示例中显示位置。
日期以天数形式存储,因此值以天为单位。您可以直接添加或减去天数。表达式>TODAY()-91
测试比今天早不超过 90 天的日期。这假设您的数据不能包含未来日期,因为如果您不扩展公式来限制未来日期,这些日期也会包括在内。整个表达式是一个逻辑测试,返回 TRUE ( 1
) 或 FALSE ( 0
)。SUMPRODUCT 将数组结果的其余部分乘以这些值,结果为零或 SUMPRODUCT 中其他参数的结果。