语境
我需要选择一些活动，并设定开始和结束日期，并检查这些活动是否合理，因为限制条件是只能同时进行 3 项活动。由于最多只能进行 3 项活动，因此在我的计划中，我需要允许活动延迟。虽然我知道这可以在 MS Project 中完成，但我需要在 Excel 中专门完成。
问题
我已经设法在 Excel 中制定了此方案，并且它完全按照预期运行，可以在很短的时间内完成少量任务（最多约 30,000 个单元格）。但是，我需要将其应用于更多活动，持续时间更长（可能总共最多 500,000 个单元格 - 目前大约需要 10 分钟才能完成！）。您能推荐一些方法来增强我的配方吗？我已经做了很多优化，并研究了一些其他选项（见下文）：
我目前的方法
请注意，我的方法依赖于为活动赋予优先级编号，并根据此编号排序（这只是最早的“开始”持续时间）。
生成概况/甘特图的公式解释：
- 如果它是甘特图中的第一行（$B9 = 1），不要想太多，因为您面前没有其他活动：如果您在所需日期内，只需在每个单元格中输入 1（IF(AND(AO$3>=$C9,AO$3<=$D9),1,"")）。
- 对于其他行...公式正在检查（A）您是否在正确的日期范围内（B）您上方是否已有 3 项活动正在进行（C）您是否已在此活动中投入了足够的 1。
（我使用 Excel 表格，希望公式在每一行都一致，这就是为什么我在同一个公式中使用第 1 点和第 2 点）
目前的增强尝试：
- 我没有对整个范围求和，而是尝试设置 SUM(OFFSET(...))，这样只需要对较少数量的单元格进行求和。然而，尝试这样做后，结果只是意味着公式缺少一些活动，因此导致一些活动尽管受到限制但仍开始或持续的时间超过预期时间。
- 我没有按天计算，而是尝试按周/月计算。虽然这无疑加快了计算速度，但这种粒度级别无法提供准确的结果 - 因此我需要坚持按天计算。
结论：
有没有办法进一步优化 =IF($B10=1, IF(AND(AO$3>=$C10,AO$3<=$D10),1,""), IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,""))。谢谢。
答案1
您需要处理的单元格数量很多，我会使用辅助列来计算实际开始和结束日期，并使右侧的计算更加简单。
- 实际启动公式：
=IF(COUNTIF($F$1:F1,">="&B2)<$C$11,B2,LARGE($F$1:F1,$C$11)+1) - 实际结束公式：
=E2+D2-1
现在，在日历部分，您可以使用如下公式：=IF(AND(X$1>=$E2,X$1<=$F2),1,"")
更新
公式如何起作用：
COUNTIF($F$1:F1,">="&B2)- 计算期望开始时间后完成的先前活动COUNTIF(... )<$C$11- 与约束进行比较=IF(.... ,B2,...- 保持可能的期望开始=IF(... ,... ,LARGE($F$1:F1,$C$11)+1)- 需要延迟，检查限制你开始的活动中哪个活动最早结束，+1 表示第二天开始
答案2
通过删除第 1 行的孤立情况，您可以节省一些 CPU 时间，因为 99.9% 的行不需要进行持续比较。只需在标题下方插入一个空白行并丢弃第一个 IF：
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,"")
AO$3<=$D10此外，和似乎SUM($F10:AN10)<$E10在进行相同的结束日期比较。不妨坚持使用不带求和的快速单一比较：
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,AO$3<=$D10,1,"")
接下来将命名范围“约束”定义为常量（指的是=3），以避免单元格查找：
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<constraint,AO$3<=$D10,1,"")
接下来选择嵌套 IF 而不是 AND，这样当窗口条件已经为 FALSE 时，Excel 就不需要每次都计算 CPU 密集型的“SUM”。按最高概率为假的顺序排列 IF：
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(SUM(AO$4:AO9)<constraint,1,""),""),"")
最后用 COUNT 替换 SUM
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,1,""),""),"")
因为外表也很重要：
正如我们已经讨论过的，条件格式是不可能的。但是，您可以使用像这样的 ASCII 字符“█”代替 1：
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(ROW(AO9)-ROW(AO$4)+1-COUNTBLANK(AO$4:AO9)<constraint,1,"█"),""),"")
但这样做的代价是计算前面“█”的数量会更加复杂。为了更快速，请使用 Webdings 字体格式化的数字 4（看起来几乎像这样 ►）：
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,4,""),""),"")
您可以使用 0-9 之间的任意数字并更改字体以获得更直观的“条形图”效果。但它必须是一个数字，否则 COUNT 将不起作用，您必须求助于较慢的“█”型 COUNTBLANK 公式。
答案3
只需做几件简单的事情就能大幅提高速度。最主要的是重新安排分析单元格的方式。
Excel 会尽可能地从左到右逐块计算，只有在公式逻辑强制它这样做时才会偏离。一种偏离是，如果它执行测试，它会直接得出结果，如果这是可能的。
因此，如果将IF()测试作为长公式的第一部分，并且测试的结果有简单的结果，它就不会评估其他分支。You have such a thing that would cut away almost all of the calculating you are doing.
这是AO$4:AO4针对约束的测试。如果计算失败，您将立即获得输出，无需进一步处理。它只是针对该单元格结束。因此，请重新排列公式以首先测试该单元格。这样，只有三行会进行比此测试更进一步的计算，而不是每一行都会进行计算。
（说到“第一”：正如Mobus所说，停止在每个单元格中进行“第一行”计算。使用我上面显示的范围AO$4:AO4（显然每列都不同）并像处理其他行一样处理第一行。然后它永远不会成为除其自身之外的行计算的一部分。范围锚定和扩展就像您现在所做的那样，您只需像处理其他行一样处理“第 1 行”。优化其唯一性并不能解决这个问题。）
SUM()接下来，对测试现有完成情况的行进行或的计算COUNT()。您似乎拥有足够多的行，几乎所有行都处于完成状态，因此如果您首先测试日期是否使其引起兴趣，那么您无论如何都必须检查这些日期。先做这件事，然后将要测试的日期减少到很少。同样，该计算无论如何都会运行，移动它既不会获得也不会失去它，但先做这件事会减少大量不同的计算。
其他答案和/或评论中提到了命名范围的概念。我全心全意地关注命名范围和辅助列（甚至辅助页面），但您的约束存在于单元格中而不是命名范围中根本无关紧要……如果您将其移动到不会一直改变位置的地方。Excel 会构建一个计算方案，在第一次计算之后，只会触发对其中有变化的分支的计算。将约束放在不变的位置，不要编辑它……Excel 永远不需要花时间重新计算等。将它放在数据区域下，让它在每次插入新任务时更改行，它就会这样做。把它放在不会看到这种持续变化的地方，它就不需要重新计算了。命名范围是一种简单、美妙的方法，但如果您愿意，您可以简单地重新排列电子表格的那个方面，如果您想不时更改它，或者更多，可能希望用户这样做。但是，在这种情况下，如果您更改约束，您的公式将彻底改变电子表格的结果，而您几乎不可能希望这样，那么为什么要将其保留在工作表中而不是命名范围中呢？命名范围还有其他优点，只是对这个问题并不重要。但是如果您这样做了，请将其放在地址不会不断变化的地方（很大程度上，“变化就是变化”，无论发生什么变化），这对速度问题无关紧要。
我从未读过直接针对算术SUM()与字符串操作COUNT()进行速度比较的文章。看起来其他人也读过这样的文章，并且认为字符串操作更快。在这种情况下，将所有内容更改SUM()为使用COUNT()COUNTA()我猜算术会获胜，但你猜对了。
是的，正如您的评论所说，只有任务开始的日期已经到来才重要，而不是希望的完成日期。所以只有检查这一点才重要。这是因为您的问题不包括如果任务太旧而放弃它。所以无论如何，如果它尚未完成并且是任务 1、2 或 3，它将被完成而不必担心其希望的完成日期。
然而，您所能做出的最大改变，实际上使上述所有内容（包括其他答案）相形见绌的是......STOP recalculating every line over and over again.
一旦任务完成，它将永远不会在所呈现的逻辑中再次被处理。所以why keep addressing it again and again???
定期（每周或每月）复制所有已完成的行并粘贴它们的值。这样它们就永远完成了。也许你只剩下 100 行公式，而不是你已经建立的 15,000 行。（一次执行 3 项任务，你显然不可能同时执行 2,000 项任务，所以我选择了 100 项，但即使 20 项似乎也更合理。）所以这些计算永远、永远、永远都不会再进行。想想那里的速度改进。
与目前的情况相比，这真是令人难以置信。
那么它也会有一些有趣的优势。最大的优势是，如果容量增加/减少，约束可能会发生变化。如上所述，如果改变，它目前会彻底摧毁过去的结果。但随着价值的转变，旧材料将不受影响。想改变它吗？复制并粘贴更改点上方所有行的值，更改它，然后继续，直到再次更改它。
另一个原因是，较小的计算负荷可以让你以更广泛的方式使用动态寻址之类的东西，尽管它们现在的点要小得多。
顺便说一句，使用辅助列来加快速度，其想法不同于通常的“单独执行一项困难的任务”以简化另一列中的公式。在这种情况下，重点是将主公式中不变的部分隔离在辅助列中，这样 Excel 就会计算一次，而且只有在它们以某种方式发生变化时才会计算。所以一般来说，如果公式中有 11 个参数，但 7 个参数从未或很少改变，那么修改公式，使它们的效果只发生在辅助列中，其余公式像单个参数一样读取它们的结果。那……可能需要进行大量修改，涉及对如何处理参数的不同看法，但通常可以做到这一点，然后那些部分就永远不会重新计算，因此一切都会运行得更快。但有时你不能{就像有时IFERROR()siumply 不起作用，你必须使用 `IF(ISERROR())}，所以你所能做的只是获得一点点。但超过 500,000 个公式，一点点改进仍然会有很大帮助。
至于甘特图方面，我想这就是条件格式（“CF”）逐渐显露头角的地方。（我没有看到这样的参考资料，但有人认为，使用 1 的甘特图不太可能像使用 CF 那样制作漂亮的线条。正如 所提到的Mobus，有比使用 CF 更好的方法。除此之外Mobus，还可以选择任何合适的“块”字符，如果需要，只需使用“对齐|水平”下的“填充”功能即可填充单元格，这样匹配大小和形状就不那么重要了，尽管您仍然必须调整字体以使字符与行高匹配。甚至REPT()服务器也是如此。
但要让它“唱”起来（特别是当您使用上面的方法大大减少所涉及的计算时），您可以通过该TEXT()函数呈现输出并构建其格式字符串以包含字体颜色。这样您就可以为每行的线条指定不同的颜色，与上下行不同，因此颜色永远不会相邻，从而提高可读性。请注意，该部分在单元格端公式中，而不是在 CF 中，CF 有所有其他问题，而不仅仅是它的速度影响。
转向 Access 或类似程序，除了不一定可供您和您的用户使用外，似乎不太可能带来太大帮助，因为 500,000 个单元格似乎主要是“右侧单元格堆积”，而不是行堆积。拥有 100,000 个任务，每个任务有五个单元格进行计算，这迫切需要 Access 或类似程序，或者专门用于项目。但是拥有 500 行，右侧有三年的日期（每行 1,000-1,100 个计算）并不迫切需要数据库程序（尽管它仍然需要专用软件……但这并不总是可能的，不管有没有）。与流行的“知识”相反，Excel 并不是某种“一月糖蜜”般的慢如蜗牛的计算机，SQL 也并不总是某种聪明、闪亮的 41 世纪神童，不知何故存在于当今世界。无论如何，您都在做上述事情，因此计算负担正在减少到今天的一小部分……