遅い Excel スプレッドシートを修正する 10 のトリック (Excel のスピードアップ)

私たち全員を結びつけるものが 1 つあるとすれば、それは遅い Excel スプレッドシートに追いつくためのフラストレーションでなければならないでしょう。

データが少ないときはパフォーマンスへの影響は無視できるかもしれませんが、ワークブックにデータや計算をどんどん追加していくと、より深刻になっていきます。

Excel がどのように作られているかによって、大きなデータ セットでは遅くなることがあります。

10 Tips to Handle Slow Excel Spreadsheets

ここでは、遅い Excel スプレッドシートを少し速くして、時間とフラストレーションを節約するための 10 のヒントを紹介します (クリックすると特定のセクションにジャンプします)。

1. 揮発性関数を避ける (必須)。
2. ヘルパー列を使用する。
3. 配列数式を避ける (可能な場合)。
4. 条件付き書式を注意深く使う。
5. Excelテーブルと名前付き範囲を使う。
6. 使用しない数式を値に変換する。
7. 参照するデータをすべて1つのシートにまとめておく。
8. 参照に行/列全体を使用しない
9. 手動計算モードを使用する
10. より高速な数式テクニックを使用する

Avoid Volatile Formula

Volatile Formula は理由があってそう呼ばれています。 たとえば、セルで NOW 関数を使用すると、ワークシートに変更があるたびに数式が再計算され、セルの値が更新されます。

これには追加の処理速度が必要で、結局遅い Excel ワークブックになってしまいます。

経験則として、揮発性の数式は避けてください。

Use Helper Columns

Helper Columns は、Excel で最も過小評価されている設計構造の 1 つです。 多くの人がヘルパー列の作成を敬遠しているのを見てきました。

Don’t Do That.

「ヘルパー列」を使用する最大のメリットは、配列式を避けることができるかもしれないということです。 私は、配列式に反対しているわけではありません。 むしろ、これらはいくつかの状況で素晴らしいものになり得ると信じています。 しかし、1 つの長い数式ですべてを実行しようとすると、Excel ワークブックのパフォーマンスに影響を及ぼします。 しかし、多くの場所で配列式を使用する必要がある場合、ヘルパー列の使用を検討してください。

ヘルパー列が使用される例をいくつか挙げます：

• 数式を使用してデータを自動的にアルファベット順に並べ替える。
• Excel で複数のドロップダウンリストを繰り返し作成する

Avoid Array Formulas

Array Formulas には独自のメリットがありますが、スピードはそのうちの 1 つではありません。

上記で説明したとおり、配列数式では多くのデータ（セル参照）を取り込んで解析し、結果を表示させることができます。 しかし、それには時間がかかります。

配列式を回避する方法（ヘルパー列の使用など）がある場合は、常にその道を歩んでください。 当たり障りのないデータがとても美しく見えるようになります。 自分で比較する代わりに、セルの色やアイコンを見るだけで、他の人と比べてどうなのかがわかります。

しかし…ここで問題があります。

Word of advice – Use it Cautiously.

Use Excel Tables and Named Ranges

Excel Table and Named Ranges are two amazing features that hold your data and makes referenceing super easy. 慣れるまで時間がかかるかもしれませんが、使い始めると、人生は簡単で速くなります。

データ駆動型ダッシュボードを作成する場合、データを Excel テーブルに変換することはほとんど常に良い考えです。

また、数式をより理解しやすくするという利点もあります。

たとえば、何が理解しやすいかというと、=Sales Price-Cost Price OR =SUM(A1:A10)-SUM(G1:G10)

使わない数式を静的値に変換する

これは当然といえば当然ですね。 必要ないときは、保管しないでください。

数式がたくさんあると、Excel ワークブックが遅くなります。 そして、使用されてもいない数式がある場合 – 誰を非難すべきかわかっているはずです。 経験則として、数式が不要な場合は、静的な値に変換する（値として貼り付ける）方がよいでしょう。

Read More: How to quickly convert formulas to values.

参照するデータをすべて 1 つのシートに保持する

これは必ずしも可能ではないかもしれませんが、これができれば、あなたの Excel シートはより速くなることを保証します。

参照として行/列全体を使用しない (A:A)

この項目をリストに入れた唯一の理由は、数式で行/列全体の参照を使用している人を多く見かけるからです。 これは悪い習慣であり、避けるべきです。

行/列にはデータのあるいくつかのセルしかないと思うかもしれませんが、Excel はそのように考えません。 行/列全体を参照する場合、Excel はそれを良い召使として振る舞い、とにかくチェックします。 その分計算に時間がかかります。

9. 手動計算モードを使用する

私はただ、さまざまなフォーラムやブログですでに百万人が言っていることを繰り返しているだけです。 手動計算を使用すると、Excelが独自の決定を下すのではなく、計算するときにExcelに指示する柔軟性が得られます。 これは、Excel ワークブックを高速化するものではありませんが、遅い Excel スプレッドシートを使用している場合、Excel に何度も再計算させないことで確実に時間を節約できます。

• 手動モードに切り替えるには、［数式］タブ -> 計算オプション -> 手動（F9キーを押して再計算）

Use Faster Formulas Techniques

Excel では同じことを行うために多くの数式や数式-組合せが提供されています。

• IF と ISERROR の組み合わせの代わりに IFERROR を使用する (IFERROR がない Excel 2003 以前を使用している場合を除く)。
• Use MAX(A1,0) instead do IF(A1>0,A1,0) – これは私が Excel 氏、別名 Bill Jelen から学んだ素晴らしいアドバイスです。 彼の研究によると、MAX オプションは IF オプションよりも 40% 速いそうです (私はこの統計を額面どおりに受け取る準備ができています)。

  VLOOKUP の代わりに INDEX/MATCH コンボを使用する – これは眉唾かもしれませんが、100 列ものデータを持っているなら、VLOOKUP が速くなるわけがない、というのが真実です。
  .

• TRUEとFALSEを1と0に変換するために-（二重否定）を使用します（1倍したり0を加えたりする代わりに）。 この速度向上は、大規模なデータセットで顕著になります。

これは網羅的なリストなのでしょうか。 決してそうではありません。 これらは、出発点として共有する価値があると思ういくつかの良いものです。

Excel のスピードアップ テクニックをマスターしたいのであれば、多くの Excel の専門家による良い仕事があります:

• 75 Speed-up tips by Chandoo (smartly done by crowdsourcing).
• Decision Models Website.
• Mr.Excel Message Board (ここを探索すれば、たくさんのヒントが見つかるでしょう).

あなたも、遅い Excel スプレッドシートに取り組むのに役立つ多くのヒントを持っていると確信しています。 コメント欄で私たちとそれを共有してください。

私はまた、1つの要求を持っています。 遅いExcelスプレッドシートの作業の痛みは、私たちの多くが日常的に経験していることです。 もし、これらのテクニックが役に立つと感じたら、それを他の人と共有してください。 そのような方々の苦痛を和らげ、利益を得ることができます。