Excelで資料を作るとき、多くの人は最初に「完成形の表」を作ろうとします。たとえば、月別の売上表や担当者別の一覧表などです。
しかし、Excelを使いこなしている人はこの順番で作りません。いきなり表を作ることはしないのです。
まず作るのは、集計のための「データ」 です。
なぜなら、最初にレイアウトされた表を作ってしまうと、その表は特定の目的にしか使えなくなるからです。
- 別の切り口で集計したくなる
- 新しいデータを追加する
- 違う形式のレポートを作る
こうしたことが起きるたびに、表を作り直すことになります。
一方で、整理されたデータを最初に作っておけば、表は後からいくらでも作ることができます。
- 月別の集計
- 担当者別の集計
- 商品別の分析
- グラフやレポート
すべて同じデータから作ることができます。
つまりExcelでは
「データを作る」ことと「表を作る」ことを分ける
という考え方がとても重要なのです。
この記事では、Excelができる人が実践している 「いきなり表を作らない理由」と「正しいデータの作り方」 を解説します。
さらに実務での実践のために、 Power Query と Power Pivot を活用した業務フローをご紹介します。
よくあるExcelの使い方
最初から報告用の表を作る
多くの人はある数値を報告する場合、いきなり完成形の表を作り始めます。
例えば次のような表を作成します。一見すると小計まで入っていて良く整理された表で全く問題無いように見えます。
問題はその後に起こる
この表は確かに求められている形に整理されているので、違和感を感じることは無いと思います。
しかし、実はそこが 落とし穴 なのです。
問題はその表を作成した後に起こります。例えば 次のようなニーズ に直面したことはないでしょうか？
- 翌月もその表を更新して報告
- 集計されていなかった案件が見つかり修正
- 前年との比較が必要となる
- 別部門の値も加えて集計することになった
最初に作成した表があった上で、これらのニーズに対応しようとすると非常に手間になります。
多くの場合は次のような結末になってしまいます。
- 最初から表を作成し直すことになる
- 表を工夫して拡張するが複雑になってしまう
- 表を変形させた結果、Excel 関数式にミスがあって誤った集計値となる
特に、Excel関数式のミスなどは経験された方が多いのでは無いでしょうか？
これらの問題はこれまでの仕事にやり方では回避することは不可能です。
Excelができる人の考え方
まずは「データ」を作る
Excel ができる人はある数値の報告を求められた場合、上記の例のようにいきなり表を作成することはしません。
その代わりに「データ」を作ります。
ここでいう「データ」とは テーブルデータ と呼ばれるものです。
テーブルデータとは何か
テーブルデータとは図のようなルールに則ったデータです。
一見シンプルですが、強制力の強いルールで例外は認められません。
例えば「日付」列に「未定」というような値を入れることはNGです。
何故、このようなルールを強制するのでしょうか？
テーブルデータの知られざる真価
シンプルですが、シチュエーションによってはやっかいなテーブルデータのルール。
しかし、このルールを強制することでデータは利用価値が非常に高いものになります。
このルールに従ったデータであれば、何万行、何百万行のデータとなったとしてもデータ処理ツールにより非常に高速かつ正確に処理が行えるのです。
つまり、この十数年で飛躍的に向上したコンピューティングパワーを存分に活用できる のです。
多くの人が進化したコンピューターの性能を生かし切るような仕事はなかなかできていません。
しかし、このテーブルデータを使うことでコンピューティングパワーを味方につけることが可能になるのです。
テーブルデータには Excel の「テーブル書式」
実は Excel に標準搭載された「テーブル書式」の機能を使えばこのルールに基づいたテーブルデータを簡単に作ることができます。
テーブル書式には次のような機能が備わっています。
- 列名の重複を回避（重複する名前を入れると自動修正）
- セルの結合不可（元のセルが結合されていた場合は自動で解除）
- データ範囲の自動拡張（列方向も行方向も）
したがって、最初にやるべきことは Excelの「テーブル書式」を適用したテーブルを作成することです。
テーブル書式について興味を持たれた方は テーブル書式の解説記事 をご覧ください。
テーブルデータにすると何が良いのか
テーブルデータを最初に作成することで次のようなメリットが生まれます。
なお、テーブルデータを作成しておけばピボットテーブルや Excel関数 を用いることで集計表をいくつでも簡単に作成できるということは覚えておいて下さい
この方法については後ほど詳しく解説します。
集計の自由度が高い
テーブルデータを一つ作成しておけば例えば次のような様々な切り口での集計表を瞬時に作成することが可能です。
- 月別
- 担当者別
- 商品別
しかもテーブルデータから複数の集計表を同時に作成していくということも可能です。
レイアウト変更に強い
ピボットテーブルを利用することで表の体裁や集計項目の順番などを簡単に変更することができます。
これらはユーザーのマウス操作で自在に編集できて、集計はコンピューターがやってくれるので、Excel 関数 の入力ミスのような問題は一切起きません。
データを追加しても壊れない
テーブルデータは次の行や列に値を入力すると自動で拡張されて、集計も自動的に更新されます。
これについても、Excel 関数式 をユーザーが編集する必要が無いので、人間によるミスを回避することが可能です。
表を「後から作る」手順（Power Pivot の活用）
テーブルデータの作成が終わったらいよいよアウトプットとしての表を作成します。
ピボットテーブルで集計する
ピボットテーブルは集計表を瞬時に作成できるツールです。
これまで紹介してきたような集計区分の変更や、体裁の変更などこれまで人が手作業で時間をかけてやっていたことを簡単な操作で実現できます。
テーブルデータがあれば次の手順でピボットテーブルはすぐに作成することができます。
ピボットテーブルを作成する手順
- 作成したテーブル内の任意のセルを選択
- メニューから「挿入」をクリック
- 「ピボットテーブル」をプルダウン
- 「テーブルまたは範囲から(T)」をクリック
- ダイアログが出るので「OK」ボタンをクリック
これだけでピボットテーブルを作成できます。あとはピボットテーブルをマウスで操作するだけで様々な形の集計表を作成することができます。
ピボットテーブルは非常に高性能で高機能なので身に付けると大変便利です。
ピボットテーブルについて理解を深めたい方は、「ピボットテーブルの入門記事」 を是非ご覧下さい。
グラフも簡単に作れる
表ではなく、グラフで視覚的に確認したい場合はピボットテーブルに連動するグラフを作成します。
このグラフは特別な操作は不要で、次のようにして簡単に作成することができます。
このグラフが素晴らしいところはピボット操作をするとリアルタイムで次々と形を変えて表示されるところです。
このため、データ分析作業に非常に有効です。このような機能が Excel に標準で搭載されていことは素晴らしいことですね。
ピボットテーブルにグラフをつける手順
- ピボットテーブルの任意のセルを選択
- メニューから「挿入」をクリック
- グラフの種類を選択してクリック
表を「後から作る」手順（Excel 関数の活用）
テーブルデータと相性の良いのは Power Pivot だけではありません。
Excel 関数の中でも SUMIFS / AVERANGEIFS / COUNTIFS 関数 はテーブルデータを活かす機能がついています。
それは、テーブルの列を指定した集計ができるという点です。
通常の Excel 関数 はセルを指定するので、集計の際に範囲の指定が不十分となるミスが発生するリスクが大きいです。
しかし、これらの関数では一度 テーブルの列を指定して作成しておけば、テーブルの行データが増減してもそれに連動して正しく集計結果を返してくれます。
この関数の具体的な使い方は次のようになります。
=SUMIFS(テーブル1[売上],テーブル1[支店名],”駒場支店”,テーブル1[日付],”>=2024/4/1″)
解説
この関数式は次のような処理をしています。
- テーブル1の 売上 の値を集計
- 集計条件1：支店名は 駒場支店
- 集計条件2：日付は 2024年4月1日 以降
さらに便利になる方法
Power Queryでデータ作成を自動化する
作成したテーブルデータをそのまま集計するのも良いですが、更に高度なデータ処理をするには Power Query を活用します。
Power Query ではテーブルデータに対して次のようなデータ処理をすることが可能です。
- 他のテーブルデータとの結合（VLOOKUP よりも高度な処理）
- データの加工（数値計算、文字列の抽出など）
Power Query 入門記事
Power Query について理解を深めたい方は、「Power Query 入門記事」 を是非ご覧下さい。
まとめ
Excel ができる人は最初に「表」を作らない理由と、洗練されたワークフローをご紹介しました。
洗練されたワークフローは、
- まず「テーブルデータ」を作る
- 集計は「Power Pivot」や「SUMIFS関数」などで行う
という２ステップで構成されています。
さらに、より拡張性があるワークフローは、
- 「テーブルデータ」作成
- 「Power Query」でデータ加工
- 「Power Pivot」や「SUMIFS関数」などで集計
という３ステップです。
少し手間のように見える仕事を最初にこなすことで後の仕事を格段に楽にすることができるのです。
次に読むおすすめ記事
Power Pivot による集計は従来のSUM関数での集計を全く過去のものにしてしまうような革新的なものです。
Power Pivot を学んでみようと思われた方は、Power Pivot の入門記事 を是非ご覧ください。
