「モダンExcel」とは？ 面倒な並び替えや計算式の入力を省いて生産性の大幅アップが実現

最近よく耳にする「モダンExcel」。実は一般的なExcelと大きく変わりません。
表計算をするExcelに外部データとの連携や加工ができる「PowerQuery（パワークエリ）」と、複雑なクロス集計ができる「PowerPivot（パワーピボット）」が搭載され、難解なデータ解析が誰でも行えるようになったのが主な変更点です。これによって従来のExcelよりも飛躍的に生産性が上がったため、Microsoftが「モダンExcel」と呼ぶようになりました。
この記事ではモダンExcelにできる基本的な動作を説明します。

モダンExcelでどんなことができるのか

複数の飲食店を経営している会社があるとします。統括マネージャーは日々の売上データを各店舗から吸い上げているはずです。その際、どの店舗の売上が多いか、ロードサイド型と繁華街型、フードコート型でどのような違いがあるかを分析することが多くなります。場合によっては、その日の気温やエリア人口、曜日、時間帯での違いや相関関係を分析することもあるかもしれません。こうしたデータは新規出店の材料となり、重要な意思決定に必須の数字となります。

しかし、そのような分析を行うためには、これまでは分析する前のデータ収集が大変でした。担当者は各店の売上データが入った情報をCSVなどで出力し、データをExcelに貼り付け、出店タイプや曜日、時間帯などでそれぞれデータを並べ、場合によってはシートを切り分けて集計するかもしれません。通常のExcelスキルだとそれが当然です。

モダンExcelを使いこなすことにより、データの接続や変換、結合などを自由に行うことができ、更新ボタンを押せば最新のデータを取り込んで集計することも可能です。

PowerQueryとは？

PowerQueryとPowerPivotは2010年以降のExcelに対応していますが、標準実装されたのは2016年です。2010年、2013年のExcelはPowerQueryやPowerPivotをダウンロードしてインストールする必要があります。

PowerQueryはファイル、データベース、Webサービスなどからデータを読み込み、好きな形に加工してExcelに出力ができます。複数のデータを読み込め、Excel内にそれを格納することができます。

たとえば、飲食店の深夜の売上を分析するため、店舗別に客単価を出したい場合を想定します。日次データは昼、夜、深夜に分かれ、それぞれに客数と売上データが入っていたとします。通常のExcelの場合、深夜営業している店舗を抽出し、任意の列に計算式で「売上÷客数」を入力して客単価を出します。翌日になれば、昨日分のデータをまた追加する必要があります。100店舗であれば、100行追加しなければなりません。毎日その分を追加して、計算式を入れるのは手間がかかります。PowerQueryを使えば、更新ボタンを押すだけで欲しいデータを抽出することができます。

PowerQueryの使い方

簡単に使い方を説明します。任意のフォルダに基データを入れるフォルダを作成します。実行用のExcelを作成し、Excelの「データ」タブを押して任意のデータ形式を選択します。先ほど作成した基データを読み込んで、実行用Excelと連携させます。

データのヘッダーにフィルターがかかっていることを確認します。通常のExcelのフィルターと同じように、ここで深夜営業している店舗の抽出が可能です。

客単価のエリアを足すためには、「カスタム列」を選択します。例名に「客単価」などと入力し、カスタム列の式に「売上÷組数」を設定します。計算式は右のボックスにある「使用できる列」から選択します。「OK」を押すと一律で計算式が入力されます。

PowerPivotとは？

PowerPivotは主にPowerQueryの後工程を担うツールです。PowerQueryはデータを読み込み、加工をして集計しやすい形にした上で更新が自動でできるものでした。PowerPivotはデータを集計してグラフなどで視覚的に訴えるアウトプットを作成するものです。

たとえば、繁華街への飲食店の出店を計画していて、深夜営業をするかどうか迷っていたとします。担当者は深夜営業している繁華街型店舗の過去データを集め、深夜営業が有利なのかどうかを判断する必要があります。その場合、繁華街型の深夜の客数、客単価、売上、エリア人口、駅の乗降人数、深夜営業に必要な従業員数、時給などのデータをまとめておくと、スムーズな意思決定ができます。

それぞれのデータを集めて計算式を入れ、加工するところまでがPowerQuery、集計して視覚的に表し、有利・不利までの意見を出すのがPowerPivotというイメージです。

PowerPivotの使い方

PowerQueryで作成したデータベースと接続します。Excelの「挿入」タブから「ピボットテーブル」をクリックします。「OK」を押すと新しいピボットテーブルが作成されています。フィールドを選択すると、エリア別、出店タイプ別、時間帯別などでクロス集計ができます。

更に「挿入」タブから「ピボットグラフ」を選択すると、ピボットテーブルに連動したグラフの作成ができます。これにより、視覚的に訴える資料が作成できます。

まとめ

モダンExcelを使いこなせば、データ収集や分類を自動で行うマクロを組まなくても複雑なデータ処理や更新が行えます。経理や経営企画だけでなく、マーケティングでも大いに役立つでしょう。生産性の向上にもつながり、ぜひ習得したいスキルの一つといえます。多くの解説本が出ているため、興味がある人は購入することをお勧めします。決して難しいものではありません。慣れてしまえば誰にでもできるものです。