Power Queryを使用してデータ集計作業効率UP!!

お疲れ様です。
業務効率UPのため、日々奮闘中の事務員兼主婦です。
職場の業務効率化として、ノーコードツールのGlideで色々なプロトタイプを作成しています。

Let's時短！Power Queryで集計作業効率UP！

毎月のことなのですが、業務の中で実績集計や資料作成に有り得ないほど時間を費やしています。何故、そんなに時間がかかるかというと、データの抽出に関してはある程度タイミングを見計らって行えば問題ないのですが、集計する際に職場特有の難点があり、１番の軸となる店コードというものに数字と英字が混在しているため、VLOOKUP関数などでエラーが生じてスムーズに作業が進まないのです。何万件もあるデータを同じ書式に置き換えてから、他の関数も使用してグラフなどを作成すると１シート集計するだけで１日かかってしまっています。

以前、Power Automateを使用して自動化にもチャレンジしてみました。

しかし、私の職場ではセキュリティの関係や、その都度応用した集計作業が発生するため使用までに至っていません。そこで他にも作業軽減できるものがないか調べ、今回Excelに装備されているPower Queryを使ってみる事にしました。主に困っていた点は以下の項目です。

①文字列や数値の変換
②日付の表記統一
③データの紐づけ

完成品

使用ツール

・Excel Power Query

操作方法

１. データの貼り付け

①今回はセキュリティの関係上、先に必要なサンプルデータを準備しましたが、データベースやデータソースから直接取り込むことも可能です。まず、新しいExcelブックを開きます。データタブのデータの取得でファイルからのExcelブックからを選択し、準備しておいたデータを取得します。

②202312実績をデータをインポートします。

③ナビゲーターで202312実績をクリックし表示させて読み込みます。

④クエリと接続の緑色部分、202312実績をクリックします。

⑤Power Queryエディター画面になります。クエリの設定の適用したステップ欄に作業した内容が表示されていきます。

⑥続いて、新しいソースから202401実績と202402実績をインポートし、同じように読み込ませていきます。

⑦202401実績をインポート。

⑧202401実績を読み込み。

⑨202402実績を読み込み。

⑩クエリ追加からクエリを新規クエリとして追加を選択します。

⑪追加画面で今回は3つ以上のテーブルを選択し、利用可能なテーブルから追加するテーブルへ追加して1つのテーブルにします。

⑫OKボタンを押すと1つのテーブルになりました。クエリの設定のプロパティで名前を「実績」に変更しておきます。

２．形式の変換

①店コードについて、数字と英字が混在しているため統一させます。まず、行を選択します。

②右クリックで型の変更でテキストへ変更します。

③店コードが一瞬で統一されました。今回はサンプルデータですが、実際に職場で何万件もあるデータで試してみたところ、１～２分程度で統一されました！すごい時短です。

３．日付表示の変更

①計上日を年月（202401）の表示に変更します。まず、計上日を選択します。

②いったん列の分割から、区切り記号による分割を選択します。

③テキスト列の分類に使用される区切り記号をしてします。今回は/記号の出現ごとに分割し、引用符文字はなしにしてOKボタンを押します。

④計上日が3つに区切られ3列となりました。自動的に月日の表示が変更されてしまったので、クエリ設定の適用したステップから変更された型１を左側の✕で削除します。

⑤月日の表示が元に戻りました。続いて年と月の列を結合させます。該当する列を選択し、列のマージをクリックします。

⑥列のマージで選択された列を結合する方法を選びます。今回は区切り記号なしにして、新しい列名を年月にしてOKを押します。

⑦年月が結合されました。

⑧日付の列は必要ないので削除します。

⑨続いて、この実績表に支店担当者を紐づけします。新しいソースをからファイルのExcelブックを選択します。

⑩支店担当表をインポートし、OKボタンを押します。

⑪PowerQueryエディター画面になります。

⑫実績の時と同じように、店コードを型の変更でテキストにします。

⑬左側のクエリから実績を表示させ、クエリのマージ（結合）を行います。

⑭マージの画面でテーブルを結合させる列を選択します。結合の種類は完全外部としOKボタンを押します。

⑮テーブルがすべて結合された状態です。ここから表示させたい部分を選択していきます。

⑯今回残したい表示は支店名と担当者なので、その２つにレ点を入れてOKボタンを押します。

⑰結合表示されました。

⑱閉じて読み込ませます。

⑲完了しました。

作業結果

ナント、一晩かかっていたデータ書式の置き換え作業が、ほんの2～3分で終わってしまいましたしかも、他の作業も同じで短時間で、あっという間に終わってしまいます。これは使える！と即座に上司へ報告しました。

使用してもらった感想

早速、上司に使用してもらいました。「え？もう終わったの？！これって毎月データ更新もできるんだよね？」とのご質問を頂き、自動で更新させる設定にするか、都度更新ボタンを押せばデータが入れ替わっても、前回と同じ作業を自動でやってくれることを説明したところ、「コレ、覚えます！」と宣言して頂きました

感想

今まで、本当に資料作成に時間がかかり過ぎてきたので、とても大助かりです。Excelの進化は素晴らしいと思いました。反面、まだまだExcelの機能を使いこなせていないな～と改めて感じてしまいました。これからも、さらに勉強が必要なようです。

参考資料

ここまでお付き合い頂きまして、ありがとうございました。