☰

您能為我的 Excel 公式推薦任何速度增強方法嗎？我需要將其應用到超過 500,000 個單元格，因此任何節省都很重要

tag-icon • tag-icon microsoft-excel worksheet-function
您能為我的 Excel 公式推薦任何速度增強方法嗎？我需要將其應用到超過 500,000 個單元格，因此任何節省都很重要

情境

我需要進行一些具有所需開始和結束日期的活動，並檢查它們是否合理，因為考慮到只能同時進行 3 項（例如）活動。由於進行的活動永遠不會超過 3 項，因此在我的日程安排內，我需要允許活動延遲。雖然我知道這可以在 MS Project 中完成，但我需要專門在 Excel 中進行。

問題

我已經成功地在 Excel 中製定了這個公式，對於短時間內（最多約 30,000 個單元格）內的少量任務，它完全按照預期工作。然而，我需要將其應用於更多的活動，持續時間更長（總共可能多達 500,000 個單元 - 目前大約需要 10 分鐘才能完成！）。您能推薦對我的配方進行任何改進嗎？我自己已經做了很多優化，並研究了一些其他選項（見下文）：

我目前的方法

當前方法的螢幕截圖以及指示性配置文件

請注意，我的方法依賴於為活動分配優先編號，並據此排序（這只是最早的「開始」持續時間）。

產生輪廓/甘特圖的公式解釋：

  1. 如果它是甘特圖中的第一行($B9 = 1)，請不要想太多，因為您之前沒有其他活動：如果您在所需日期內，只需在每個單元格中添加1 ( IF(AND (AO$3>=$C9,AO$3<=$D9),1,""))。
  2. 對於其他行...公式正在檢查 (A) 您是否在正確的日期範圍內 (B) 您上方是否已經有 3 項活動正在進行 (C) 您是否已在此活動中輸入了足夠的 1。

（我正在使用 Excel 表格，我希望公式在每一行中保持一致，這就是為什麼我在同一個公式中有第一點和第二點）

目前的增強嘗試：

  1. 我沒有對整個範圍求和，而是嘗試設定 SUM(OFFSET(...)) ，以便需要求和的單元格數量更少。然而，嘗試過這一點後，這只是意味著公式缺少一些活動，因此導致一些活動儘管受到限制仍然開始，或者持續時間超過預期持續時間。
  2. 我嘗試每週/每月進行一次，而不是每天進行一次。雖然確實加快了計算速度，但這種粒度級別並沒有給出準確的結果 - 所以我需要堅持每天。

翻譯：博士：

有沒有辦法進一步優化 =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,""),""),"")

最後將 SUM 替換為 COUNT

=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（顯然每列都不同）並像處理任何其他行一樣處理第一行。

接下來，對現有完成的行進行SUM()或的計算。COUNT()您似乎有足夠的行，幾乎所有行都以完成狀態存在，因此，如果您首先測試日期是否使其感興趣，那麼無論如何您都必須檢查這些行。先做這件事，然後將測試的日期減少到很少的幾個。同樣，該計算無論如何都會運行，相對於它移動它既不會獲得也不會損失，但首先這樣做會減少許多不同的計算。

其他答案和/或評論中提到的是命名範圍的想法。我非常關注命名範圍和幫助器列（甚至幫助器頁面），但是存在於單元格而不是命名範圍中的約束根本不重要......如果您將其移動到不會出現的位置一直改變位置。 Excel 建立一個計算方案，並且在第一次計算之後，僅觸發其中發生變化的分支的計算。將約束放置在不變的位置並且不要編輯它...Excel將永遠不需要花費時間重新計算等。將其放在不會看到這種持續變化的地方，並且不需要重新計算。命名範圍是一種簡單、美妙的方法，但如果您願意，您可以簡單地重新排列電子表格的該方面，並且如果您想時不時地更改它，或者更多，您可能希望用戶這樣做。但是，在這種情況下，如果您更改約束，您的公式將完全改變電子表格的結果，並且您幾乎不可能希望這樣做，那麼為什麼將其保留在工作表中而不是命名範圍中？命名範圍還有其他優點，但對這個問題來說並不重要。但如果你這樣做，把它放在它的地址不會不斷變化的地方（很大程度上，“變化就是變化”，無論發生什麼變化），並且速度問題並不重要。

我從未讀過直接尋址算術（例如SUM()與字串操作之類COUNT()的速度）的內容。看起來其他人已經讀過這樣的內容並且字串操作更快。在這種情況下，將所有內容更改SUM()為 useCOUNT()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()簡單地不起作用，你必須使用`IF(ISERROR())}，所以你能做的只會有一點收穫。但超過 50 萬個公式，一點點仍然可以發揮很大作用。

至於甘特圖方面，我想這就是條件格式（“CF”）提高其速度的地方。 （我沒有看到這樣的參考，但有人認為帶有 1 的甘特圖不像使用 CF 來繪製漂亮的線條那樣可能。正如 所提到的Mobus，有比使用 CF 更好的方法。補充一下Mobus，一個可以選擇任何合適的“塊”字符，如有必要，只需使用“對齊|水平”下的“填充”功能即可使其填充單元格，因此匹配大小和形狀不太重要，但您仍然需要調整字體以匹配字元到行的高度REPT()。

但為了讓它「唱歌」（特別是當您使用上面的方法來大大減少所涉及的計算時），您可以透過該TEXT()函數呈現輸出並建立其格式化字串以包括字體顏色。這將允許您為每一行指定不同的顏色，與上面和下面的行不同，因此顏色永遠不會彼此相鄰，從而提高可讀性。請注意，該部分位於單元端公式中，而不是位於 CF 中，其中包含所有其他問題，而不僅僅是其速度影響。

轉向 Access 或類似程序，除了不一定可供您和您的用戶使用外，似乎也不會提供令人難以置信的幫助，因為 500,000 個單元格似乎主要是“右側的單元格堆積”而不是行堆積。有 100,000 個任務，其中有 5 個單元，每個單元都有計算，需要 Access 或類似的或可能專用於專案的東西。但是，擁有500 行，右側有三年的日期（每行1,000-1,100 次計算）並不需要資料庫程式（儘管它仍然對專用軟體感到痛苦……但這並不總是可能的，無論痛苦與否）疼痛）。與流行的「知識」相反，Excel 並不是某種「一月糖蜜」的慢速計算機，而 SQL 也並不總是某種聰明、閃亮、以某種方式存在於當今世界的 41 世紀神童。無論如何，您都在執行上述操作，因此計算負擔已減少到今天的一小部分，所以...

相關內容