主页 / computer / 问题 / 1472657
Accepted
hiigaran
Asked: 2019-08-19 11:57:38 +0800 CST

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

  • 772

我希望获得一些帮助来理清过去几年我在 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))
worksheet-function spreadsheet

1 个回答

  1. Best Answer

    这个问题描述了两个问题。一个是试图调试一个长公式。一般来说，这样做的关键是将公式分解成它的组成部分并测试每个部分以查看它在做什么。首先保持逻辑块完整（即，几个表达式一起工作），以确定公式的哪个部分不起作用。如果错误不是很明显，请将该部分分解为它的组成部分。

    通过复制和粘贴每个部分来做到这一点，然后调整粘贴的部分使其成为一个独立的公式（比如添加等号）。如果问题是括号不平衡，复制包含所有括号的整个表达式，然后删除其他内部表达式及其括号，这样更容易发现；从这个练习中，剩下的括号中的不平衡可能会变得很明显。复制和粘贴将确保问题包含在您测试的内容中；重新输入公式可以修复错误，并且在您测试时一切正常，因此它不能用于诊断目的。

    另一个问题是将结果限制为过去 90 天。这可以类似于公式已经将结果限制为匹配名称的方式来完成——向 SUMPRODUCT 中的表达式列表添加一个测试。为了简单起见，我将忽略使用 INDIRECT 构建范围的复杂性，只显示固定范围。在上下文中，附加参数可能如下所示：

    =SUMPRODUCT( ... (L1:L100), (A1:A100>TODAY()-91) ... )

    (L1:L100) 只是为了在问题的简化示例中显示位置。

    日期存储为天数，因此值以天为单位。您可以直接加减天数。该表达式>TODAY()-91测试比今天早不超过 90 天的日期。这假设您的数据不能包含未来的日期，因为如果您不扩展公式来限制它，那么这些日期也会被包括在内。整个表达式是返回 TRUE ( 1) 或 FALSE ( 0) 的逻辑测试。SUMPRODUCT 将数组结果的其余部分乘以这些值，结果为零或 SUMPRODUCT 中其他参数的结果。

    • 1

相关问题