データシートを再構築すると Excel COUNT が遅くなる

Question

私は、ライブ データベースからのデータで更新すると、データ シートへの参照が壊れないように、データ シートを削除して再作成する Excel アドインを使用しています。列ヘッダー行を除いたデータの行数をカウントするために、数式 COUNT('DataSheet'!A:A) を使用しています。データが更新されていない場合は、ほぼ瞬時に再計算されますが、アドインを使用してデータが更新されると、数式が計算を終了するまでカウントがゆっくりと増加します。ブックの速度が大幅に低下する他の同様の数式を使用する必要があります。

回避策は考えられますが、COUNT が通常どおり再計算される場合と、データが更新されたときに再計算される場合の動作の違いの原因を知る必要があります。現時点では、データは更新前と更新後の行数が同じであり、フォーマットされた範囲はデータよりも長くありません。

編集: これは、Excel がメモリ内でどのセルがアクティブ/入力されているかを処理する方法と関係があるのでしょうか?

Answer 1

@TheGreatO96 さん、ようこそ。

COUNT()の代わりにCOUNTA()を使ってみてください。しかし、あまり役に立たないのではないかと思います。アドインで最適化すべき点がいくつかあると思います。データベースの更新は揮発性のアクションであり、更新なしでは通常トリガーされない再計算をトリガーします。OFFSET、INDIRECT、NOWなどの揮発性関数を使用する場合、条件付き書式、大きなブックでは処理が遅くなります。 COUNT(...) の値が 1 つのセルで更新されるのを確認できるということは、マクロで大幅な遅延が発生している可能性があります。 コード内のいくつかの点を調整してみてください。

• 再計算を無効にしてマクロを開始します。残りのコードを実行します。再計算をオンに戻します。

• 画面更新を無効にしてマクロを開始します。残りのコードを実行します。画面更新をオンにします。

• イベントを無効にしてマクロを開始します。残りのコードを実行します。イベントをオンに戻します。

Ps. OFFSET 関数を廃止して相対的な名前付き範囲を使用するだけで、マクロの実行速度が 2 桁近く向上したことがあります。