コンテクスト
希望する開始日と終了日を持ついくつかのアクティビティを取得し、同時に実行できるのは 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
行の 99.9% では定数比較は必要ないため、行 1 の孤立したケースを削除することで CPU 時間をいくらか節約できます。ヘッダーのすぐ下に空白行を 1 つ挿入し、最初の 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,"")
次に、AND の代わりにネストされた IF を選択します。これにより、ウィンドウ条件がすでに FALSE の場合、Excel は CPU を集中的に使用する「SUM」を毎回計算する必要がなくなります。IF を、false になる可能性が最も高い順に並べます。
=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,""),""),"")
見た目も重要だから：
すでに説明したように、条件付き書式は使用できません。ただし、1 の代わりに「█」のような ASCII 文字を使用できます。
=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
ほんの 2、3 の簡単な変更で、速度が大幅に向上します。主な変更点は、セルの分析方法の順序を変更することです。
Excel は、可能な限り左から右に、検出されたチャンクごとに計算し、数式ロジックによって強制される場合にのみ逸脱します。例外の 1 つは、テストを実行すると、可能であれば、結果に直接進むことです。
IF()したがって、長い数式の最初の部分としてテストがあり、テストの結果が単純な結果である場合、他の分岐は評価されません。You have such a thing that would cut away almost all of the calculating you are doing.
これはAO$4:AO4制約に対するテストです。計算が失敗すると、すぐに出力され、それ以上の処理は行われません。そのセルで終了するだけです。そのため、最初にそれをテストするように数式を並べ替えます。こうすることで、すべての行ではなく、3 行だけがこのテスト以降の計算を参照します。
(「最初」について言えば、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???
定期的に (毎週または毎月がお勧め)、完了した行をすべてコピーして、その値を貼り付けます。これで完了です。永久に終わります。おそらく、これまで構築した 15,000 行ではなく、数式が残っている行は 100 行です。(一度に 3 つのタスクを実行すると、2,000 のタスクを並べることはできないのは明らかです。そのため、100 を選択しましたが、20 でさえ妥当と思われます。) そのため、これらの計算は二度と実行されることはありません。速度の向上を考えてみてください。
現状と比べればそれは信じられないことだ。
そうなると、興味深い利点も生まれます。最大のメリットは、容量が増減した場合に制約を変更できることです。前述のように、変更すると現在のところ過去の結果が完全に破壊されます。しかし、値に移行することで、古い資料は影響を受けません。変更したいですか? 変更ポイントより上のすべての行の値をコピーして貼り付け、変更し、再度変更するまで進めます。
もう 1 つは、計算負荷が小さくなるため、ポイントがはるかに小さくなるものの、動的アドレッシングなどの機能を、より広範囲に使用できるようになることです。
ちなみに、処理速度を上げるために使用するヘルパー列は、別の列の数式を簡単にするために「難しいタスクを分離して実行する」という通常の方法とは異なります。この場合のポイントは、メインの数式の変更されない部分をヘルパー列に分離し、Excel がそれらを 1 回だけ計算し、その後は何らかの変更があった場合にのみ計算するようにすることです。したがって、一般的に、数式に 11 個のパラメーターがあり、7 個がまったくまたはほとんど変更されない場合は、それらの効果がヘルパー列でのみ発生するように数式を改訂し、残りの数式ではそれらの結果を 1 つのパラメーターのように読み取ります。これは... 大幅な改訂になる可能性があり、パラメーターの処理方法にまったく異なるアプローチが必要になりますが、通常は実行でき、それらの部分は再計算されないため、すべてがはるかに高速に実行されます。ただし、場合によってはできないこともあります (IFERROR()単純に機能しない場合があり、`IF(ISERROR()))。そのため、できることはほんの少しのメリットしかありません。しかし、500,000 を超える数式では、少しの改訂でも大きな効果があります。
ガントチャートの側面については、条件付き書式 (「CF」) が遅れをとるのはそこだと思います。(そのような言及は見ませんでしたが、1 のガントチャートは、CF を使用してきれいな線を描くほどにはありそうにないと思われます。 が述べたようにMobus、CF を使用するよりも良い方法がありますMobus。前述のことに加えて、適切な「ブロック」文字を選択し、必要に応じて、配置 | 水平の「塗りつぶし」機能を使用してセルを塗りつぶすことができるため、サイズと形状を一致させることはそれほど重要ではありませんが、文字を行の高さに合わせるためにフォントを調整する必要があります。REPT()サーバーでも。
しかし、それを「効果的に」するには (特に、上記を使用して計算を大幅に削減した場合)、関数を通じて出力を表示しTEXT()、フォントの色を含む書式設定文字列を構築することができます。これにより、各行の行に、上と下の行とは異なる色を指定できるため、色が互いに重なることがなくなり、読みやすくなります。この部分はセル側の数式にあり、速度の影響だけでなく、他のすべての問題を伴う CF にはないことに留意してください。
Access や類似のプログラムへの移行は、必ずしもあなたとあなたのユーザーが利用できるわけではないことを除けば、500,000 個のセルは、行が積み重なるのではなく、ほとんどが「右側のセルが積み重なる」だけなので、驚くほどの助けにはならないようです。100,000 個のタスクがあり、それぞれに計算を行う 5 つのセルがある場合、Access または類似のもの、あるいはプロジェクト専用のものが必要になります。しかし、右側に 3 年間の日付がある 500 行 (行ごとに 1,000 ～ 1,100 回の計算) がある場合、データベース プログラムが必要になることはありません (専用ソフトウェアがまだ必要ですが... 痛みがあってもなくても、常に可能であるとは限りません)。一般的な「知識」とは反対に、Excel は「1 月の糖蜜」のような非常に遅い計算マシンではなく、SQL は今日の世界に何らかの形で存在する、輝かしい 41 世紀の神童であるとは限りません。いずれにせよ、上記のことを行うので、計算の負担は現在の負担のごく一部にまで軽減されます。