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

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

![Glide用バナー.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/ddd0c08e-61d0-ea48-ca8f-476f8995601f.png)


https://qiita.com/kiku_2005/items/67f67ca8340dab7206de

https://qiita.com/kiku_2005/items/8483dc00e7d1af716b94

https://qiita.com/kiku_2005/items/10c22d8b6b6c32ebf0be

https://qiita.com/kiku_2005/items/953d67e6c7c75a285b32


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

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

![computer_tsukare.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/40ee488f-eeb7-1096-6c3b-7a739cbc7802.png)

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

https://qiita.com/kiku_2005/items/5a8930f79b08b38f6a9f

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

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

# 完成品

![㊳クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/cec7825e-eb2e-0eae-54ec-c979afc4f6bd.png)

## 使用ツール
・[Excel Power Query](https://support.microsoft.com/ja-jp/office/%E3%82%AF%E3%82%A8%E3%83%AA%E3%82%92%E4%BD%9C%E6%88%90-%E8%AA%AD%E3%81%BF%E8%BE%BC%E3%81%BF-%E3%81%BE%E3%81%9F%E3%81%AF%E7%B7%A8%E9%9B%86%E3%81%99%E3%82%8B-excel-power-query-ca69e0f0-3db1-4493-900c-6279bef08df4)


# 操作方法
##  １. データの貼り付け
①今回はセキュリティの関係上、先に必要なサンプルデータを準備しましたが、データベースやデータソースから直接取り込むことも可能です。まず、新しいExcelブックを開きます。`データ`タブの`データの取得`で`ファイルから`の`Excelブックから`を選択し、準備しておいたデータを取得します。
![クエリ①.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/b2d88b0e-4e12-c0f7-a57a-f2038672a1a9.png)

②202312実績をデータをインポートします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/935d8458-9c7f-1f0e-644e-ee26bc8ffbfa.png)

③ナビゲーターで202312実績をクリックし表示させて読み込みます。
![③クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/cf59422d-4966-51fe-4dd8-bab3a21fcceb.png)

④クエリと接続の緑色部分、202312実績をクリックします。
![④クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/3fcb6cdd-8706-a3d3-8182-6345d8c7be6e.png)

⑤Power Queryエディター画面になります。クエリの設定の適用したステップ欄に作業した内容が表示されていきます。
![⑤クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/cb655d3c-b292-d08a-649b-57fcb89557eb.png)

⑥続いて、新しいソースから202401実績と202402実績をインポートし、同じように読み込ませていきます。
![⑥クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/907545f5-0c11-4178-2947-7f6a89397d26.png)

⑦202401実績をインポート。
![スクリーンショット 2024-03-11 045120.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/020ac4b1-c1ed-9084-196d-1f0960d86e5f.png)

⑧202401実績を読み込み。
![⑦クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/72d7ae8d-6918-4eab-2c96-2b374978eeab.png)

⑨202402実績を読み込み。
![⑨クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/bf5727c2-bf03-b23d-7438-1fbf3d7db567.png)
![⑩クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/ec8388e8-dc17-87d9-0087-0e4311b73ea5.png)

⑩クエリ追加からクエリを新規クエリとして追加を選択します。
![⑪クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/97f25512-1942-39e3-2343-33ee3004bb54.png)

⑪追加画面で今回は3つ以上のテーブルを選択し、利用可能なテーブルから追加するテーブルへ追加して1つのテーブルにします。
![⑫クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/4ff34e3e-423e-ea53-3113-84555b1d7354.png)
![⑬クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/e0a78075-caa2-8ced-7297-6f4487da0b2c.png)

⑫OKボタンを押すと1つのテーブルになりました。クエリの設定のプロパティで名前を「実績」に変更しておきます。
![⑭クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/be806bd0-7de5-333c-4654-95786e143a17.png)
![⑮クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/ac583a86-d414-d6e1-06b2-615499820352.png)


## ２．形式の変換
①店コードについて、数字と英字が混在しているため統一させます。まず、行を選択します。
![⑯クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/88cf62a7-2c34-80b6-bc6c-9c38508363d4.png)

②右クリックで型の変更でテキストへ変更します。
![⑰クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/fac18639-6bdc-37a2-5bef-68e16b30c87f.png)

③店コードが一瞬で統一されました。今回はサンプルデータですが、実際に職場で何万件もあるデータで試してみたところ、１～２分程度で統一されました！すごい時短です。
![⑱クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/2b04a79a-43b2-297a-555c-1f9336d7806a.png)


## ３．日付表示の変更
①計上日を年月（202401）の表示に変更します。まず、計上日を選択します。
![⑲クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/6857f3a6-f7df-b937-3a36-a3337c88bb37.png)

②いったん列の分割から、区切り記号による分割を選択します。
![⑳クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/cffc0811-6651-a4fb-fe2c-0c163dc9bbda.png)

③テキスト列の分類に使用される区切り記号をしてします。今回は`/`記号の出現ごとに分割し、引用符文字は`なし`にして`OK`ボタンを押します。
![㉑クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/87c6a21a-dccf-c551-ad65-3c3d7cac0cec.png)

④計上日が3つに区切られ3列となりました。自動的に月日の表示が変更されてしまったので、クエリ設定の適用したステップから`変更された型１`を左側の`✕`で削除します。
![㉒クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/19b400f9-d57d-0bfc-9dde-808c8b861877.png)

⑤月日の表示が元に戻りました。続いて年と月の列を結合させます。該当する列を選択し、列のマージをクリックします。
![㉓クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/c9ad5be5-8315-fc53-5090-37e6aa793c30.png)

⑥列のマージで選択された列を結合する方法を選びます。今回は区切り記号なしにして、新しい列名を年月にしてOKを押します。
![㉔クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/ede73276-5c18-d230-e35c-6b2580e95ecc.png)

⑦年月が結合されました。
![㉕クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/214e78f5-87eb-8e1c-edfc-0ed5ffb31d8f.png)

⑧日付の列は必要ないので削除します。
![㉖クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/62b2ceef-97f0-e1ba-1024-c4ad0def5976.png)

⑨続いて、この実績表に支店担当者を紐づけします。新しいソースをからファイルのExcelブックを選択します。
![㉗クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/0158fec8-48db-c2f3-42ad-540f33888de7.png)

⑩支店担当表をインポートし、OKボタンを押します。
![㉘クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/0a480f61-0df2-be7d-2f73-90045f4f02e8.png)
![㉙クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/d3e7c84c-8470-3dd0-0df2-dd77bab0b1de.png)

⑪PowerQueryエディター画面になります。
![㉚クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/6a1f1c80-d67d-2a78-82fd-5362f1ac06d0.png)

⑫実績の時と同じように、店コードを型の変更でテキストにします。
![㉛クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/ff4d3f85-be93-0103-9e21-4e3ab4ab2def.png)

⑬左側のクエリから実績を表示させ、クエリのマージ（結合）を行います。
![㉜クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/a3360027-843f-7454-0e75-1643d9ec0917.png)

⑭マージの画面でテーブルを結合させる列を選択します。結合の種類は完全外部としOKボタンを押します。
![㉝クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/514b95e2-01cc-5a9f-9472-0a6bd2fbc302.png)

⑮テーブルがすべて結合された状態です。ここから表示させたい部分を選択していきます。
![㉞クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/9a1ee946-1e48-e271-bc13-6e94a59bb9e2.png)

⑯今回残したい表示は支店名と担当者なので、その２つにレ点を入れてOKボタンを押します。
![㉟クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/a2724ea6-acd5-173b-13ff-17cf3b2841ce.png)

⑰結合表示されました。
![㊱クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/9e880d76-d469-8bcd-b53a-11de50a84e8a.png)

⑱閉じて読み込ませます。
![㊲クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/aebfaee6-09e2-c62e-5d6a-71aa81829b11.png)

⑲完了しました。
![㊳クエリ.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/cec7825e-eb2e-0eae-54ec-c979afc4f6bd.png)


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

![businessman_dekiru_woman.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/81864cf7-d0e3-04c2-2787-475c3af994ed.png)


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

![businessman_dekiru.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/1dbcf234-e9e2-a386-b9b2-cab9b44b8bd0.png)




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

![business_senryaku_sakuryaku_woman.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3570716/b5e63c38-98ea-4824-2b12-c38a4986d305.png)



# 参考資料

https://youseful.jp/microsoft/excel/powerquery-1/

https://youseful.jp/microsoft/powerquery-merge/

https://udemy.benesse.co.jp/business/office/what-is-powerquery.html


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