ファイルを開いた時、セルをクリックした時など何をするにも再計算が始まって何分か止まってしまいます。

Question

エクセル2010を使っておりますが、あるエクセルファイルにおいてSUMIFS関数を何万個と使っており、非常に重かったため、その関数を全て組み直しました。

INDEX、MATCHを組み合わせて（「INDEX(○○!$A:$G,MATCH($B14&N$1,○○!$A:$A,0),7)」というように組んであります）、SIMIFSやVLOOKUPを使わないようにしたのですが、それが原因なのかはっきりしないのですが、さらに重くなってしまいました。

SUMIFSを何万個と使っていたころは、ファイルを開くときは普通の動きで、フィルタやソートをかけたり編集したりするときにものすごく遅かっただけなのですが、今回の修正後は、開いた時点で「再計算　○%」と表示されて、それが１００％になって普通に開くところまでいくのに数分かかってしまいます。
また、どこかのセルをクリックしただけでも、同じく「再計算　○%」と表示されてしまったり、保存するだけでも、同じく再計算が始まったりで、全く使い物にならない状態です。
（開くだけで再計算が始まるあたりも不思議です）

どうすれば軽くなるのか、ご教示願います。

CoalTar · Accepted Answer

VLOOLUPとINDEX/MATCHの計算速度は、ほとんど変わらない。
ただし、MATCHを作業列に置くことで速度向上を図れる。
ちなみに、
>INDEX(○○!$A:$G,MATCH($B14&N$1,○○!$A:$A,0),7)
は
INDEX(○○!$G:$G,MATCH($B14&N$1,○○!$A:$A,0))
とすることが可能。

SUMIFSとSUMPRODUCTを使った数式は圧倒的にSUMIFSの方が早い。

問題のSUMIFSとINDEX/MATCHだが、比較対象になりえなかったので比較していない。
一般的にはINDEX/MATCHの方が早いはずだが、、、。

>どうすれば軽くなるのか、ご教示願います。
もし、すべてのシートにおいて、[Ctrl]+[End]で最終セルの位置を確認てみる。1048576行だったら問題あり。
名前の定義でエラーがあるものがない確認してみる。

揮発性関数を多用していないか確認してみる
RAND、NOW、TODAY、OFFSET、CELL、INDIRECT、INFO
※INDEX、ROWS、COLUMNS、AREASは揮発性関数ではありません

[Alt]+[Ctrl]+[Shift] +[F9] 完全な再計算(時間がかかると思う)を一度実行して、保存し閉じる。
とか
Excel97-2003形式で(別名で)保存してみる
その後、現形式で変換(ファイル - 変換)してみる

図のリンク貼り付けがあると遅くなる傾向にある

WindFaller · Answer

#2の回答者です。

『Excel 2010 のパフォーマンス: 計算パフォーマンスの強化』
https://msdn.microsoft.com/ja-jp/library/office/ff700515(v=office.14).aspx

こちらの内容は、ちょっとややこしいですが、実に、興味深い内容です。
「計算に 1 ～ 10 秒かかるようになると、ユーザーは確実に手動計算に切り替えます。ユーザーのエラーが増え始め、不愉快になります。特に、その傾向は反復的な作業で顕著に見られ、思考の流れを保つのが困難になります。」

再計算時には、次の再計算のみが行われます。
「•変更された、または要再計算としてフラグが設定されたセル、数式、値、または名前。 
　•再計算が必要なセル、数式、名前、または値に依存しているセル。
　•揮発性関数と条件付き書式。」
どうやらすべてではないらしいです。

揮発性関数のこともここで出てきます。
「RAND()、NOW()、および TODAY() 、OFFSET()、CELL()、INDIRECT()、および INFO() 」

以前は揮発性関数として記載されていたが、「INDEX()、ROWS()、COLUMNS()、および AREAS()」は揮発性関数ではなくなっている、ということです。

#2の書き込みで、
　「INDEX(○○!$A:$G,MATCH($B14&N$1,○○!$A:$A,0),7)」
　は、よく見たら配列を使っていませんでしたし、配列数式に換えても、ほとんどスピードは変わらなかったです。ただ、参照範囲が大きいか小さいかの違いは影響あるように思えます。

私が言いたいのは、一列全部とかいっても、2003では、全部は読まなかったようですが、今は、読んでいるという記憶があったからです。

上記のサイトでは、こう書いてあります。

「•配列数式や関数で使用する範囲のサイズを最小にします。」
「•配列数式やメガ関数は、別の補助的な列や行に分割します。」
「SUM や SUMIF などの関数で使用するセル数を最少にします。計算時間はセルの使用数に比例します (未使用のセルは無視されます)。」

ここらは、#3さんも一部ご指摘していただいているようですが、実証するには、ご質問者さんが試していただくのが一番早いような気がします。

WindFaller · Answer

こんにちは。

気にはしていたのですが、私は、マクロが専門で、数式の話にはややこしい話が多いのですが、たまたまですが、Microsoft でこんな内容を見つけました。なんとなく聞いていたのですが、Excel 2003以前と仕様が変わったという所がポイントだと思います。

私の話がわけが分からないのだったら、リンクだけでもみてください。

アプリ側で対処する方法としては、以下があります。
・Excel　大きなデータモデルの操作を取り消す機能を無効にする。
　　時間がかかる操作が発生したときに警告を表示する。
　　次の数（単位：1000個)以上のセルを処理する場合(U): 33,554
    (今、試してみないので、ここの単位が良くわからないです。33,554 × 1,000 かもしれない)

『Excel 2010 のパフォーマンス: パフォーマンスの問題を最適化するヒント』
https://msdn.microsoft.com/ja-jp/library/office/ff726673(v=office.14).aspx

「全列および全行の参照」

「代替アプローチとして、全列の参照を使用することもできます ($A:$A など)。たとえば、この参照は列 A の行をすべて返します。したがって、データをどれだけ追加しても、追加したデータは必ず参照に含まれます。」

「•Excel 2007 よりも前のバージョンの配列数式は全列の参照を処理できません。Excel 2007 の配列数式は全列の参照を処理できますが、列のすべてのセル (空白セルを含む) が計算されるため、計算が遅くなることがあり、特に行数が 100 万を超えると、計算速度は低下します。」
という内容は、逆に、Excel 2007以降は、すべてを参照するということ？
そうしたら、かなりまずいですね。

「•Excel の多数の組み込み関数 (SUM、SUMIF) は、列内の最後に使用された行を自動認識するので、全列の参照を効率的に計算します。一方で、SUMPRODUCT などの配列計算関数は、全列の参照を処理することも、列内のすべてのセルを計算することもできません。」
これも、全部参照するということですよね。これもまずい、ということですね。

それでこういう数式に変わったのだろうと思いますが、
>「INDEX(○○!$A:$G,MATCH($B14&N$1,○○!$A:$A,0),7)

これもINDEX関数側が、配列ですから、結果的には、最後まで読んでいるわけですよね。配列数式は揮発性ですから、値を留めておくことができません。値をその都度求めようとするので、再計算が始まってしまいます。

プログラム的解決する（非VBA)
そこで、ユーザー定義関数(非同期のユーザー定義関数)が書かれていますが、この前も別の場所で書きましたが、今のところ、それはVBAではありませんので、ちょっと、実現するには時間がかかりそうです。

『非同期のユーザー定義関数』
https://msdn.microsoft.com/ja-jp/library/office/ff475859.aspx

angkor_h · Answer

ツール→オプション→計算方法で、「手動」にしてみて下さい。
そこで実行指示をしない限りは再計算は始まらないはずです。

ファイルを開いた時、セルをクリックした時など何をするにも再計算が始まって何分か止まってしまいます。

VLOOLUPとINDEX/MATCHの計算速度は、ほとんど変わらない。

#2の回答者です。

こんにちは。

ツール→オプション→計算方法で、「手動」にしてみて下さい。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング