Excelファイルの規模が大きくなり過ぎて 動作が重く なったり、複雑過ぎてバグを探せない などの問題を起こすことが良くあります。
これは表計算ソフトの宿命的な問題とも言えます。
この記事では、この問題の 原因と対策をご紹介 致します。
この方法を身に付ければ、Excelファイルの中身はスッキリと整理され読み易い上に、動作も軽快となり、作業の効率が各段に向上すると思います。
規模の大きいExcelファイルの扱いに困っている方 には必見の知識ですので、是非参考にしてみて下さい。
解読不能になる原因
Excelファイルが解読不能で手をつけられなくなってしまう原因は大きく分けて以下の２点になります。
（原因その１）整理されず散在するデータ
Excelを使う場合、いきなりシートにデータを入力 してSUM関数などを使って簡単に集計表を作ることが多いと思います。
また、これらの集計表をシートの あちこちに乱雑に作ってしまう ケースが多いです。
何故ならExcelのシートは自由度が高く、どのセルからでもデータを入力できてしまうからです。
そうすると、データは整理されないまま シートのあちこちに散在してしまい、まとまった扱いが難しくなってしまいます。
（原因その２）Excel関数 の参照の連鎖
Excelは セルを参照 して計算する Excel関数 を駆使して使うのが一般的です。
Excel関数 は便利な反面、繰り返し使う ことでセル参照が からまったスパゲッティ のようにこんがらがって解読不能になってしまいます。
Excel関数の参照先を幾つも辿っていくと、だんだん分からなくなってしまい、解読を諦めてしまった経験をした方は多いのでは無いでしょうか？
人間の頭は通常なら３ステップぐらい しか記憶できないので、それ以上のステップを理解するには、メモを残すなどの綿密な整理が必要となってしまうのです。
スッキリ解決！対策とは？
ご説明した原因は皆さんも思い当たるのでは無いかと思います。しかし、これらの問題は冒頭でも説明したように 表計算ソフトの宿命 のようなもので、対策は簡単ではありません。
Excelは 自由に シート上にデータを入力したり、Excel関数を入力したりすることができることが問題の元になっているのです。
ではどうすれば良いか？対策は、Excelの使い方に一定のルールを決める というものです。
そのルールとは次のたった２つです。しかし、理解して身に付けるには少々努力が必要かもしれません。
でも、この考え方を身に付ければExcelの使い方が大きく飛躍すると思いますので、しっかりと理解して実践してみて下さい。
（ルールその１）「正しいデータ」を作る
「正しいデータ」とは？
広い意味では１つのセルに入力された値も「データ」になります。しかし、コンピュータの世界では「データ」とは 大量に扱うことが可能な形式 のものを指します。
データベースでは、まさにこの 大量に扱うことが可能な形式 のデータを蓄積し、高速で処理することが可能です。
コンピュータは図のようなデータを扱うのが大変得意なのです。
コンピュータが得意ということは、この形式でデータを作ってあげれば あとはコンピュータが様々な難しい処理をしてくれる ということです。
ですから、この「正しいデータ」を是非作るべきということです。
「正しいデータ」を作るにはまず設計が必要
図で示したように「正しいデータ」を作るには データをルールに当てはめる 必要があります。このため データの形 、つまり 列の構成 を慎重に設定する必要があります。
とは言ってもまずは、必要なデータを列に当てはめていけば良い 訳ですから、難しく考えなくても良いでしょう。
一つ覚えておいて欲しい 大切なルール は、「なるべく重複したデータを作らない」ということです。
例えば、図のような売上データがあった場合、得意先の住所が 何度もデータに現れてきています。これは明らかにムダです。
これを図のように ２つのデータに分けて重複を排除 することでムダを無くすことができます。
「データを２つに分けたら後でどうやって使うんだ？」と思われた方、ご安心下さい。後で出て来る Power Query を使えばカンタン！ です。
「正しいデータ」はどうやって作る？
Excel では「正しいデータ」をシート上に作成する機能が備わっています。それが 「テーブルとして書式設定」 することです。
図のように操作することで簡単に「正しいデータ」を作成できます。
「正しいデータ」は Power Query で加工する
先ほど「あとはコンピュータが様々な難しい処理をしてくれる」と述べました。それは Excel では Power Query を使って実現できます。
Power Query の詳しいことは別の記事に譲りますが、通常の Excel関数 で処理するような 計算や加工 から、「データ」同士を クリック操作だけで結合 したりすることが可能です。
特に「データ」の結合は、Excel関数なら VLOOKUP関数 や INDEX と MATCH関数の組み合わせ を駆使していた人も多いと思います。
しかし、Power Query を使えばそんな労力は不要で、簡単かつ強力に「データ」を結合することが可能です。
まだ Power Query をご存知ない方は次の記事を参照してみて下さい。
（ルールその２）参照先は「データ」に限定
Excel関数 を使って計算をするのは、何らかの 新たな「値」 を知りたいからだと思います。この 新たな「値」 を得るために、これまでは データ ⇒ Excel関数 ⇒ Excel関数 ⇒ Excel関数… と参照の連鎖をしていたはずです。
しかし、（ルールその１）で「正しいデータ」を作成できていれば、必要な情報は全て「正しいデータ」から取り出せるはず です。
ですから、図のように シンプルな構図 で Excel ファイルを構築していくことが可能なのです。
Excelファイルの内容がシンプルであれば、より大きなデータ を扱ったり、より複雑なデータ処理 をすることが可能になります。勿論 バグも大幅に削減 できるはずです。
データ参照は Power Query / Power Pivot / SUMIFS関数
「データ」からの参照で新たな「値」 を集計するにはいくつか方法があり、それぞれ特徴がありますので以下に解説させて頂きます。
Power Query
Power Query でクエリを作成しておけばデータが更新されても毎回同じデータ処理を一気に実行してくれます。
グループ集計や表のピボットなど一通りの処理が可能です。特に定型パターンでのデータ処理があるなら Power Query でクエリを作成しておくメリットがあります。
一方、クエリは固定的なのでタイムリーに集計方法を変更するのには向きません。
Power Pivot（ピボットテーブル）
ユーザーの操作に合わせてタイムリーに集計を変化 させることが可能なのが Power Pivot です。
基本は従来のピボットテーブルと同じですが、Power Pivot では DAX関数という高度なデータ処理関数 を利用することができ、ピボットテーブルにはできなかったような計算もこなすことができます。
ユーザーの操作に合わせて様々な集計を瞬時にこなしてくれるので、データを分析して新たな知見を発見したい場合 には非常に有効です。
一方で、毎回同じパターンでの集計が必要な場合は、Power Query や次の Excel の SUMIFS関数 を使う方が賢明でしょう。
何故なら「簡単に変更できる」ということは、「簡単に変わってしまう」ということで、決まった集計値が欲しい場合には不向き です。
SUMIFS関数
これはご存知の方も多いと思いますが、Excel の関数の一つです。
作成した「データ」を指定して集計ができます。特に、この関数は 引数にテーブルの列名を指定することができる ので、データが増減した場合でも、関数式を変更することなく正しく集計をしてくれます。
Excelの関数式ですから他の Excel関数と組み合わせて複雑な計算をさせることも可能です。
関連記事
Power Query, Power Pivot, SUMIFS関数 に関連した当サイトの記事へのリンクをご参考まで掲載しておきます。よろしければコチラも読んでいただくと更に応用知識が広がるかもしれません。
まとめ
Excel ファイルがどんどん大きくなって、しまいには 解読不能、修正不能、計算不能 となってしまう問題に対して、シンプルかつ強力な解決策 をご紹介しました。
Excel の機能の進化 により、従来の表計算ソフトとしての 限界を突破 して、更に高いレベルでのデータ処理が可能となっています。
私たちも進化した Excel を使いこなして 仕事の中身も進化 させていきましょう。
最初は小さいデータで 始めてみればハードルは低いはずです。
この方法を身に付ければすぐに、より大きな仕事を楽々こなせるようになる と思いますので、是非トライしてみて下さい。
