▶ 今回ご紹介するもの以外にも取り込み時にいろいろな前処理をすることができます。
はじめに
今回もEXCELでのデータ処理を扱います。今回のテーマは自動化です。たとえば、毎月データを取得して、毎月同じ集計をするのであれば、自動化しておくことでミスも防げるし、時間の節約にもなります。今回は簡単な方法で自動化する方法をご紹介します。
サンプルデータ
今回はあるサービスの会員から寄せられる次のような申請データです。申請時間と申請内容の項目があり、申請内容は「退会」「変更なし」「追加契約」という値を持ちます。
やりたいこと
システムからデータを取得するとき、差分抽出ができず、毎回全データ(2019年からのデータ)が出力されるとします。ただ、集計対象とするのは2020年9月以降であるとき、
- いちいち取得したデータを手作業で加工せずに、
- 必要な2020年9月以降のデータのみを抽出し
- 自動的に集計される
というのがやりたいこととしましょう。
自動化
ではここから自動化していきましょう。まずはデータを取り込み、次に、このデータに施す処理を定義していきます。この処理をPowerQueryを利用して設定しておけば、翌月以降はデータを更新するだけで自動的に集計処理がされます。
データを取り込む
EXCELでのデータの取り込みは「データ」メニューの「データの取得と変換」リボンから「テキストまたはCSVから」を選択して、表示される「データの取り込み」ダイアログでファイルを指定します。
プレビューが表示されるので、文字化け等に問題がなければダイアログ下部にある「データの変換」をクリックします。ここまではこれまでに紹介した手順ですね。文字化け等がある場合は、「元のファイル」のプルダウンメニューから文字コードを指定できるので、データに合った文字コードを指定するようにしましょう。
処理を定義する
すると、PowerQueryが起動して次のような画面となります。
画面中央にデータが表示されます。ここで、まず列項目の要否判断をし、必要と判断した烈項目のデータ型を設定しておきましょう。ここでは、Noという列は不要なので、削除しておきましょう。列名の上で右クリックをすると表示されるメニューから「削除」をクリックするだけです。
このようにデータに対する処理をおこなうと、画面右側の領域にある「クエリの設定」の「適用したステップ」に処理ステップが追加されます。ここでは「削除された列」が追加されました。処理をやり直したければ、追加されたステップを削除したり、「適用したステップ」にある各ステップをクリックすることで前の状態に戻ることができます。
次に残った列のデータ型はどうでしょうか？
「Date Submitted」は日付/時間型、「申請内容」はテキスト型になっているので問題なさそうです。「Date Submitted」は列名がわかりにくいので「申請日時」としておきましょう。列名を変更するには、列名をダブルクリックします。すると編集モードになり変更が可能となります。
次に不要なデータをフィルタするようにしましょう。今回は2020年9月以降のデータのみを取り込みたかったのでした。「申請日時」の列名の横にある▼をクリックしてください。
すると、上記のようなメニューが表示されます。ここから「日付/時刻フィルター」を選びましょう。するとメニューが表示されるので、そこから「次の値より後…」を選んで下さい。
「行のフィルター」ダイアログが表示されます。ここで条件を設定していきます。
ここではカレンダーによる設定が可能です。「値の入力または選択」の横にあるカレンダーマークをクリックしてください。
ここで2020/9/1を設定して、ダイアログ下部の「OK」ボタンをクリックしてください。これで2020/9/1以降のデータのみに絞ることができました。
また、このクエリにわかりやすい名前を付けておきましょう。画面右側の「クエリの設定」領域にある「プロパティ」で名前を設定することができます。ここでは、「申請データ」とすることにしましょう。
ここまでできたら、PowerQueryの「ホーム」メニューから「閉じて読み込む」をクリックしてください。
これでEXCELにデータが取り込まれます。データはテーブルデータとして取り込まれます。
集計を定義する
あとは、集計したい形を定義しておきましょう。ここではピボットテーブルで集計しておきましょう。画面上部の「挿入」メニューの「テーブル」リボンから「ピボットテーブル」を選択してください。
すると「ピボットテーブルの作成」ダイアログが表示されます。
先ほどクエリに「申請データ」という名前を付けてデータを読み込みましたね。分析データの選択では、この名前を利用することができます。また、ピボットテーブルレポートを配置する場所は、ここでは「新規ワークシート」としましょう。次のような設定をしてください。
OKをクリックすると新しいシートが開きます。ここで集計を定義してください。たとえば次のように月ごと・申請内容ごとの申請件数を集計します。
これで完成です。翌月以降データが更新されたら、今回取り込んだデータの元ファイルを上書きするようにします。その後、「データ」メニューの「クエリと接続」リボンの「すべて更新」をクリックすると、データが更新されるようになります。
このように一度、処理を定義しておくと、データが新しくなっても、列の削除、列名の変更、データのフィルタ（2020/9/1以降のデータ）が自動で適用されてデータを取り込むことができ、取り込んだデータを参照したピボットテーブルのレポートも更新することができます。
まとめ
いかがでしたか？毎回データが新しくなるたびに同じような処理をしていませんでしたか？また、PowerQueryは処理のステップを持っているので、どのような処理をするか考えるときにも重宝します。試行錯誤の中で前の状態に戻したい時もクリック一つです。これは便利ですね。是非身に着けておきましょう。
▶ データソースの参照先を変更する場合は、以下の記事を参考にしてください。
コメント