☰

在电子表格中显示距当前日期最多 X 天的值

tag-icon • tag-icon worksheet-function spreadsheet libreoffice-calc openoffice-calc
在电子表格中显示距当前日期最多 X 天的值

我希望得到一些帮助，以理清过去几年我在 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 中其他参数的结果。

相关内容