エクセルチュートリアル①Power Queryを利用した簡単なデータ分析 その１ ファイル読み込み・マージ
Excel（エクセル）の関数やショートカットキーの説明は検索すれば山ほど見つかるのですが、パワークエリやパワーピボットなどの最新のExcel機能を説明する記事があまり無いようなので書いてみました。
ただ機能を説明しても実際の業務にどう生かせばよいのかわかりにくいので、架空の業務シナリオを通して実際のExcel機能を利用しながら説明していきます。
今回は第一弾として強力なETLツール※であるPowerQueryのよく使う機能について紹介したいと思います。
PowerQuery(パワークエリ）とは
・色んなファイル（あるいはデータベース）から必要なデータを検索・取得し、望ましい形に変換（絞り込み、結合）することに特化したExcelの標準機能
・UIでポチポチするだけで、（マクロを自分で作成できなくても）マクロで実行していた機能が実現できる。もっと高度なこともM言語というExcelの関数ライクな言語を利用することである程度実現可能
・Excelのワークシートに保持できる以上の情報（10万行以上）を保持して分析できる
・マクロよりもパフォーマンスが良い（処理速度が速い）場合が多い（気がする。マクロの作り方にもよりますし、ちゃんと測ったことはありません）
※ETLツールとは何ぞやという方はwikiをご参照ください。
このチュートリアルの内容
今回は架空のファミレスのPOSデータを利用して各店舗の売り上げの集計・分析を行いながら下記の方法を紹介します。すべてマクロを利用せずにエクセルの標準機能のみで実現可能です。
・複数のファイルのデータ読み込み・マージファイルの作成
・簡単なデータ加工の方法
・Vlookupを利用しないで、ある列とある列を関連付けて分析を行う
・ピポットテーブルの基本的な操作方法
・エクセル標準の表現方法を利用した簡易な図表の作成方法
・上記の一連の操作の自動化
※使用するデータは手作りしてる関係上実際の売り上げデータとして見ると不自然な点がありますがご容赦ください。
こんな人向け
・普段のExcelを利用した業務をノンプログラミングで効率化したい人
・何らかの事情でともかくMicrosoft製品を利用して仕事をする必要がある人
・毎回同じようなデータ加工・集計作業を行う人
・Office365等最新のExcel(Ecel2010以降)を利用できる人
・Excelの基本的な動作は把握している人（どこにどのタブがあるか分かり、関数を使った経験のある人）
・PowerQueryやPowerPivot等モダンエクセルを学ぶための入口がほしい人
キーワード：Excel,Power Query(パワークエリ),PowerPivot(パワーピポット),複数ファイルのマージ,ジョイン(結合),Excellで大量データの集計・分析,業務効率化
※書いてみたら思ったより長くなったので2つに記事を分けて紹介したいと思います。
その１：複数ファイルのマージ、読み込み方法とその自動化について
⇒本記事です。コンテンツとしては下記になります。
・特定のフォルダ配下にあるファイルを読み込む
・複数ファイルをマージして一つの集計用のファイルを作成する。
・分析に必要な加工を施す（データ型の設定等）
・分析用のマスタを読み込む
その２：読み込んだファイルをピポットテーブルで集計する方法について
⇒次記事です。コンテンツは下記となる予定です。
・①で読み込んだ複数のテーブルにリレーションシップを作成して分析をしやすくする
・ピポットテーブルでデータを集計する
・スライサーというフィルタ機能でデータをフィルタする
・特定の商品群で集計⇒個別の商品で集計というように粒度を変えながら集計を行う
・データバーを利用してデータの可視化を行う
業務シナリオ
貴方はファミレスチェーンの販売企画部で働いています。
ある日上司から下記のような指示を受けPOSデータを分析することになりました。
売上がよろしくないというのは、売上が下がっているということですので、まずは
①月毎の売り上げファイルをマージして各店舗毎の売上を時系列で把握
②何が原因で売上が落ちているのか
を目的に調べて見ましょう。ちなみに周りに聞いてみた感じだと、いろんな制約で「pythonなどが使えずExcelしか利用できない」という方はそこそこいるそうです。
１.元データを確認する
今回上司からインプットして渡されたのはデータは、売り上げデータと商品マスタ、店舗マスタのが記載されているファイルです。
※DLの上解凍して利用ください。パスワードは設定していません。
いずれのファイルもcsv形式で作成されており、売り上げデータのファイルは全て同一のレイアウトで作成されています（列の順番とその入力規則が同じということです）。試しに1ファイルをメモ帳で開くと下記のような構成となっています。
販売コードと売り上げ額で構成されてますね。販売コードはこれだけ見ると意味がわかりませんが”_”毎に意味のあるコードで区切られており、下記のように構成されています。
店舗コード_売り上げ日_購入番号_商品コード
それぞれのコードの意味は下記の通りです。
・店舗コード：1店舗に1つ付与されるコードで店舗を識別します。
・売り上げ日：その商品が売れた日です
・購入番号：ある区分の商品が売れた順番で付与されます。
・商品コード：どの商品が売れたかを識別するコードです。
商品マスタと店舗マスタも見てましょう。
商品マスタは、商品コードと商品名、商品区分、単価から構成されてます。商品に関係する情報を管理しているマスタですね。
店舗マスタは店舗名、店舗区分、店舗コードから構成されています。店舗に関係する情報を管理しているマスタですね。
後続の工程のために、分析対象のファイルを一つのフォルダに格納しておきます。今回は「分析対象」というフォルダを作成し、ここにファイルを格納しました。
2.売り上げデータのファイルをマージする
ファイルが複数に分かれていては分析がやりにくいので、とりあえず売り上げデータの全ファイルをマージします。ファイルのマージはPowerQueryのお家芸で簡単に実現出来ます。
Note: PowerQueryのコネクターについて
PowerQueryでは様々なデータにアクセス出来るように複数のコネクターが用意されてます。ローカルPC上にあるファイルはもちろん、SharePointやOneDrive等のクラウド上のファイルにもアクセス可能です。クラウド（特にSharePoint）上のデータソースへのアクセスはちょっとだけコツが必要ですが、それは次回以降説明します。
まずExcelを新規で開き、データタブから、データの取得をクリックします。今回は特定のフォルダ配下にあるファイルを全て読み込みたいので、ファイルから＞フォルダーからをクリックします。
するとフォルダーパスを指定するように言われるので、先ほど作成した売り上げデータ一式を格納したフォルダのパスを入力してOKをクリックします。
すると指定したフォルダ配下のファイルを読み込んだ結果が表示されます。「Name」列を確認すると、先ほど格納したファイルががすべてありますね。商品マスタ、店舗マスタまでマージされると意味がわからなくなるので、取り込むファイルを絞るためにデータの変換をクリックします。
すると下記のような画面が出てきます。
取り込むファイルをマージするため、Nameのフィルタを開き商品マスタと店舗マスタのチェックを外しOKをクリックします。
次にファイルをマージ・展開するのですが、Content列以外はいらない情報（=分析に利用しない)ですので削除しましょう。Content以外の列を選択して（ShiftあるいはCtrlを押しながらクリックすると複数列を選択できます。）列の削除タブをクリックします。
Content以外の列が削除されました。次にファイルの中身を開いて展開します。Contentの横の↓↓アイコンをクリックします。
するとFileの結合というウィンドウが現れます。ファイルのマージに際して、いくつかの設定ができるのですが、ここではなにも考えずに、OKを押しましょう。（ここら辺の設定は後でいくらでも修正できるので）
Note:ファイル名を削除しないほうが良いことも
今回は分析に使用しないのでファイル名の列（Name列）も削除しました。ファイル名を分析に利用したいときはこの列を削除せずに保持しておいて後続の過程を進めればOKです。その場合は上記の各データにプラスして「Name列」が存在し、そのレコードがどのファイル起因のデータなのかが識別できます。
実はエラーチェックの時は、どのファイル起因のデータかが識別できたほうが便利です。そのため普段の業務ではファイル名を残しておくことのほうが多いです。
ファイルがマージされました。販売コードのままでは分析がしにくいので、”_”でデータを分割しましょう。ホームタブ>列の分割>区切り記号による分割をクリックします。
区切り記号をカスタムを選び、”_”を入力します。今回は”_”が出るたびに分割してほしいので、分割は「区切り記号の出現ごと」を選択しOKをクリックします。
区切り文字で販売コードが分割されました。列名が分かりにくいので列名を変更します。列名の部分をダブルクリックすると列名の変更が可能ですので、左の列から、店舗コード、日付、販売No、商品コードに名前を変更します。
これで複数の売り上げファイルのマージが完了しました。
3.分析用準備（データの加工、マスタデータ読み込み）
先ほど読み込んだデータを分析するために加工しておきます。と言っても難しいことをするわけではなく、２でマージしたデータの型を正しく設定してあげるだけです。特に日付については、日付型であることを認識させてあげるだけで後続の分析の難易度が大きく変わりますので、必ず設定しましょう。
8桁の数字を日付と認識させる方法はいくつかあるのですが、ここでは文字列の操作によって日付型に変換する方法を紹介します。
Note：Excelの日付型について
エクセルをある程度触ったことのある方はご存じだと思いますが、エクセルでは日付のデータを6桁のシリアルナンバーとして保持しており、ユーザーの文化圏やニーズに合わせて表示する日付の形式を変更しています。例えば日本では西暦2020年1月1日を表すときは、2020/1/1と表記しますが、ある地域では、1/1/2020と表記するなど、同じ日付であるにも関らず日付の表現方法が異なります。（同じ日本でも、2020/1/1、2020年1月1日など複数の表現を利用していますよね）エクセルではこれらのユーザーのニーズを満たすために、一度日付を6桁のシリアルナンバーで保持し、セルの書式設定でニーズに合わせて表示形式を変更するという形式をとっているのです。
具体的にどういう操作を行うかというと、文字列操作を行って20200101を2020/01/01とスラッシュで区切った列を作成することにより日付型として認識させます。（エクセルが日付型で認識できる形に文字列を変換するということです）。日付列をベースに日付型のデータを保持する列を追加で作成します。
まず列の追加タブのカスタム列をクリックします。するとカスタム列というウインドウが現れます。列名がカスタムでは分かりにくいのでここではdateに列名を変更しておきましょう。スラッシュを挿入するための関数を入力していきます。テキストを加工するときはText関数を利用します。今回は数式欄に下記の数式を入力してyyyy/mm/ddの形式に変更します。
Text.Start([日付],4) & "/" & Text.Range([日付],4,2 )& "/" & Text.End([日付],2)
数式を入力したらOKをクリックします。ちゃんとdate列はyyyy/mm/ddの形式になっています。
※2020/05/04追記
Twitterにてエラーが出てdate列の作成がうまくいかないという相談をいただきました。その場合、Text関数が参照している「日付」列がテキスト型になっていないことが原因だと思われますので、「date」列追加前に下記の作業を行ってみてください。
日付列を右クリックして「型の変更」⇒「テキスト」ｗクリック。
※2020/05/04追記
Twitterにてエラーが出てdate列の作成がうまくいかないという相談をいただきました。その場合、Text関数が参照している「日付」列がテキスト型になっていないことが原因だと思われますので、「date」列追加前に下記の作業を行ってみてください。
日付列を右クリックして「型の変更」⇒「テキスト」をクリック
Note: PowerQueryの数式（M言語）
PowerQueryで文字列操作などを行う関数のようなもののことを”M言語”と呼びます。使用感はエクセルの関数に近いです。今回利用した3つの関数は、それぞれ下記のような機能を持っています。
Text.Start([日付],4)："日付"列の左端(=頭)から"4"文字を取得
Text.Range([日付],4,2 )："日付"列の"4"+1文字目から右の"2"文字を取得
Text.End([日付],2)："日付"列の右端(=末尾)から2文字を取得
エクセルで対応する関数をあげると上からleft関数,mid関数,right関数ですね。PowerQueryでは標準で搭載されている関数以外にもユーザーが作った便利関数があるので、これをDLして利用すると作業がよりはかどります。
最後に作成した列を日付型だと認識させるために、ホーム＞データ型＞日付をクリックして、データ型を日付に変更します。（日付型に変更されると数字がセル内で右に寄ります）
加工が終わったので、ホームタブ＞閉じて読み込むをクリックして、加工したデータを読み込みます。
すると慣れ親しんだエクセルのスプレッドシートに加工の結果が表示されます。全部のファイルをマージすると、12万行以上のデータになりますね。
マスタデータの読み込み
先ほどマージしたデータはシステム処理がしやすいように商品コードという形でデータを保持しています。ですがこのままでは実際にどのカテゴリのどの商品がどの程度売れたのかがパッとわかりません。
そこで今後の分析をしやすくするために、商品や販売店などの情報を持っている店舗マスタ、商品マスタを読み込んで、売上データと関連付ける準備をします。
マスタデータはcsvファイルでそれぞれ用意されているので、こちらを読み込めばいいですね。csv形式のファイルを2回読み込むだけなので、データタブ＞テキストまたはcsvからをクリックします。するとエクスプローラーが開かれるので、読み込みたいファイル（今回は商品マスタ）を選択してインポートをクリックします。
すると読み込みオプションに関する画面が出ます。今回は特に加工する必要はないので、「読み込み」をクリックして読み込みます。
正常に読み込まれました。同様に店舗マスタも読みこみます。
Note: ここまでの作業の自動化
今回は売上のファイル4か月分を分析対象としたので、これらを読み込みましたがほかの月のファイルも含めて分析したいとなった時はどうすればよいでしょうか。実はとても簡単で、
・今回作成した売上データを格納したフォルダ（分析対象と名付けたフォルダですね）に新たに格納した
・その後マージファイルを作成したエクセルブックを開いて、データタブ>すべて更新ボタンを押す
だけです。これで、これまで行ったデータのマージ、編集作業を行ってくれます。
というのも今回PowerQueryで行ったファイル読み込みから加工までの一連のステップは先ほど取り上げたM言語で記述・記憶がされており、作業手順としてエクセルブック上に保存されているのです。
以上で売上ファイルのマージ、マスタの読み込みが完了しました。次回はこのデータを集計して売り上げは下がった原因を探っていきましょう。
この記事が気に入ったらサポートをしてみませんか？