PowerAutomateDesktop＆PowerQueryでWEBとExcel操作を自動化。その②
前回の記事をお読みくださった方、ありがとうございました！
とにかくこんな方法があるよ、こんなことできるよ、と1日も早く伝えたくてnoteを書き始めましたが、文が長いな…と思いながら書いてます💦笑。端的にわかりやすく伝える修行の１つとしても取り組んでいければと思います✊また、今のところは、ExcelでVlookUPをよく使っていてマクロも見よう見まねで作ってみた事はあるけど…な、わたしのような感じの方に向けて急いで共有したくて書いている為、丁寧な手順書にはなっていない事をご了承ください(__) でも今回は自身の復習かつ社内向けマニュアルの準備を兼ねており詳しめになっています。
今回作っている一連の業務の流れは前回のnoteにも書きましたが以下の通り。
① Webサイト(A)へアクセスし、ID・PASSを入力してログインする。
② チェックボックスへチェックを入れて、csvデータをダウンロードし、名前を付けて所定フォルダへ保存する。×複数回。
③ ②のcsvデータを結合する→条件に合うデータをプルダウンで抽出する→csvデータには入っていない情報を別途Excelからvlookupで紐づけて、今回Webサイト(B)に取り込みたい対象データを整える。
④ ③のデータをWebサイト(B)の取込用Excelフォーマット（※常に所定のフォルダにフォーマットを保管してある）に転記し、フォーマット名に今日の日付を加えた名前を付けて、所定フォルダへ保存する。
⑤ Webサイト(B)へアクセスし、ID・PASS・2段階認証PASSを入力してログインし、④のExcelファイルをアップロードする。
前回は①②⑤について書きました。今回は③④についてです！
1.PowerQueryを使ってデータを結合・整形する
PowerQuery/パワークエリをご存知なかった方にはぜひ一度使ってみて感動してほしい。最初のnoteでご紹介しましたが動画でまずできる事をイメージして取り掛かっていただく流れお勧めです！
では、さっそく手順です。
要は、これまではVlookupなどの関数でデータを結合したり関数やフィルタの抽出・並び替えの機能で行っていた事を、1つのExcelファイルに結合したいファイルを全て読み込んで、PowerQueryエディター上で編集する事になります。そして、一度PowerQueryエディターで設定すれば、2回目以降は自動更新ができる為とても楽になります！
1）データの読み込み
以下練習用に２つのファイルを用意しました。よければお使いください。
まずは「基本データ」を読みこんでいきます。
今回はcsvファイルを読み込むのですが、普通のExcelファイルはもちろん、PDF（※データがPDF化されたようなもののみ対象）やデータベースなどから読み込む事も可能です。インポートすると以下のような画面になるので、「データの変換」をクリックします。
すると、PowerQueryエディターに取り込まれた状態で画面が開きます。
この画面上で、基本的にはマウス操作のみで、これまでは関数などを使ってやっていたことを完結することができます。
①性別：女→女性、男→男性に置換
いつもならctrl+Hでやることですね。別のシステムにデータインポートする際にこういったデータ変換多いと思います。
該当の列を選択して「値の置換」をクリック。
要領はいつものExcelと同じ感じです。詳細設定で完全一致だけを設定することもできて便利！（例：女→女性だけの設定の場合、女子→女性子と変換される。完全一致だと完全に「女」だけを変換対象として認識する）そして、OKした後は以下のような画面になり、加えた作業ステップが右側に表示されています。
自動的につけられた名前だと後から見たときに分かりにくいので、右クリックでわかりやすい名前に変更しておくとよいです。
そして、Excel画面のように「戻る」ボタンがQueryエディターの中にはないのですが、ステップの左側にある「×」を押すと、登録した工程を取り消すことができますし、取り消さずとも前のステップをクリックすると、ステップを加える前の状態のデータを確認する事もできるようになっていてとても便利です。同じように「男→男性」も変換していきます。
②入社日：2021年5月のみを抽出
見たまんまです。フィルターからチェックしてOKしていきます。いつものように昇順降順への並び替えも可能ですし、空データを削除する事もできます。
ステップの名前も分かりやすくしておきます。
これでこのデータへの加工が終わったので、Excelへ読み込みます。
ホームタブ左上の「閉じて読み込む」をクリックします。
このようにテーブルとしてExcelに読み込まれます。右側に「クエリと接続」という画面が表示され、どのクエリデータと接続されているかがわかります。この表示を閉じた後にまた確認したい際は、データタブの「クエリと接続」をクリックすると表示させることができます。
そしてPowerQueryクエリの強いところは次！この元となる基本データに新しいデータが追加されていったとしても、ボタン１つで随時最新データに更新が可能です。毎度加工する必要がありません。
「すべて更新」をクリックすると、最新データに紐づいて先ほどまでに設定した作業ステップが自動で実行されます。作業ステップが多いデータほど感動です！VBAが得意な方はVBAでおやりになっていたところだと思います。でもVBAが難しい方にもマウス操作だけでこんな自動更新設定ができる。これはほんとにすごい機能だと思います！VBAが得意な方もぜひ場面によって使い分けてさらに楽をしてほしいと思います。そして、今回は説明に入れませんが、この更新ボタンを押す作業さえも、VBAやPADで自動化することも可能です。
注意点として、クエリに接続するデータの元ファイルの保存場所やファイル名を変えてしまうともう一度つなぎ直しになってしまって自動更新ができないので、システムからダウンロードしたデータを加工するよう際は、必ずダウンロードした最新ファイルは常に同じ名前にしておく必要があります。
2）データの結合
同じ要領で「所属データ」を読み込んでいきます。
今回はこのデータへ加工することはないので、早速先ほどのデータに結合していきます。「閉じて読むこむ」ボタンから「閉じて次に読み込む」をクリックして、以下のように「接続の作成のみ」にしてOKにしてください。新たなテーブルを作るのではなく、既存のテーブルにデータを結合して表示したいだけの際はこちらの形で読み込むとよいです。
「所属データ」が接続専用として接続されました。そして、上記画像の「基本データ」をクリックして接続設定を追加していきます。
①データを新たに結合＝クエリのマージ
別のデータを結合する際は、「クエリのマージ」をクリックし、表示された以下の場面でどのデータと何を共通キーとしてデータ結合するかを設定します。
真ん中のプルダウンから接続したいデータを選択した後、キーとなる項目をクリックして色がついた状態にし、ＯＫを押します。ここで結合の種類を設定することもできますが、単純に全データを接続するだけならこのままでＯＫです。
接続されました。この状態ではまずデータテーブルが紐づいただけの状態です。「マージされたクエリ数」とステップが表示されていますので、ここも「所属データと接続」など分かりやすく名前を変えておきます。
②結合されたデータテーブルの展開
結合した「所属データ」の右上の「←→」ボタンをクリックすると、データテーブルのうちどの項目を表示させるかの設定ができます。ここではキーとしたID以外を結合します。
このように結合されました。
列の項目名が「所属データ.時給」のように、データテーブル名.項目名と自動で表示されてしまうので、読み込んだ後見やすいように、それぞれ「時給」「配属先」「連絡先」と項目名を修正します。
項目名をタブルクリックすると変更が可能です。
列の並び替えも、ドラッグ＆ドロップで可能です。これでデータの完成なのでまた読み込んでいきましょう。
結合された状態で読み込まれました！これで手順③の工程が終了です。
結合するデータが複数ある際はこのファイル読み込み→クエリのマージ→各種設定→閉じて読み込み、の作業を繰り返していきます。接続データが複数あったとしても、一度作ってしまえば、次回からは「すべて更新」をクリックするだけで複数ファイルとの結合・加工作業が完了してしまってとっても楽！PowerQuery万歳！
この完成したファイルは次のPADの説明でも利用しますので、名前を付けて保存しておいてください。
他にも、値の型を変換（セルの書式設定的な）したり、列を追加して計算式を入れたり、If,else,thenのように条件式を追加することなんかもできます。
ぜひYoutube動画でPowerQueryを検索いただいたり（先日の記事のメンタエクセルのTakuyaさん他たくさん動画がUPされています）、先日の記事でもご紹介したこちらの鷹尾さんのご本からぜひ学んでください！BlogでTipsを拝見することもできます。https://modernexcel7.hatenablog.com/
２.PowerAutomateDesktopを使って、Excel→Excelへデータ転記・名前をつけて保存。
さて、次は先ほどPowerQueryで整形したデータを、「Webサイト(B)の取込用Excelフォーマット（※常に同じフォルダへ保管されている）に転記し、フォーマット名に今日の日付を加えた名前を付けて、所定フォルダへ保存する。」という作業をPADで自動化していきます。
今回の学習の元は前回までにもご紹介のロボ研さんのYouTube動画とチュートリアルです！
1）Excelを起動→読み取る対象シート（アクティブなシート）を設定する
アクションパーツからExcelの起動をドラッグ＆ドロップで追加すると、以下のような画面が出てきますので、次のように設定します。
空のドキュメント（ファイル）を開くことも可能ですが、ここでは指定したいドキュメントがありますので「次のドキュメントを開く」とし、ドキュメントパスを追加して保存します。もしこのドキュメントに読み取りパスワードがかかっている場合、「＞詳細」の欄でパスワードの入力も可能です。「＞生成された変数」に「ExcelInstance」と表示されています。この変数はPADが自動生成してくれるのですが、表示個所をクリックして自分に分かりやすい文言に変更することも可能です。変数＝「あれ」「それ」と捉えていただくとよいと思います（ひぐまさんのYouTubeブログラミング講座からの受け売りです）後の設定で、この呼び出したファイルのことを、毎回長ったらしいドキュメントパスで呼ぶのは面倒なので、さっきあそこで呼び出したあのファイル、さっきあっちで呼び出したあのデータ、みたいなものを変数というものに置き換えている感じと捉えていただくと分かりやすいのではないかと思います。
次に呼び出したExcelの中で読み取りたい対象シートを設定します。
設定が画面が出てきます。ここで対象となるExcelインスタンス（ファイルと捉えてよいと思います）に先ほどの変数が入っており、先ほどのファイル、あれ、が指定されていることが確認できます。
読み取りたいシートはアクティブ（動かせる）状態にすることが必要なので、ワークシート名に対象シートの名前を入力して保存します。さきほどPowerQueryの練習で作ったファイルの該当シートは以下になっていると思いますので、この名を入力します。※変数は必ず％％囲まれます。
2）アクティブなシートから読み取る範囲を設定する
今回読み取りたい範囲は以下の通りです。
この最終行、最終列を判断する為のアクションが次の「Excelワークシートから最初の空の列や行を取得」になります。
このように変数が2つ生成されます。
★FirstFreeColumn＝最初の空の列（ABCの縦数）
★FirstFreeRow＝最初の空の行（123の行数）
保存してPADを実行してみると、デザイン画面右側の変数に以下のように最初の空の列と最初の空の行の数字が入っていることが確認できます。こうして空の列と行を見つけることで、そこから1行または1列引いた数が最終列・最終行であると判定できるように作っていきます。
次に、「Excelから読み取る」アクションを追加します。
A2：最終行列までを読み取る場合はこのような登録の仕方をします。
％FirstFreeColumn-1％＝最初の空の列から1を引いた位置
％FirstFreeRow-1％＝最初の空の行から1を引いた位置
を表しています。
ですが今回は転記先のフォーマットに転記したい内容が丸ごと全部ではないので小分けにして読み込んでいきます。どういうことかというと、
このように転記先のフォーマットの項目が元データと丸ごと同じ形ではないので、A～D列の内容と、G列の内容を分けて読み込んでから転記していくことになります。（※一旦私が思いついた方法をご紹介させていただいています。もっと簡単ないい方法があれば知りたい！）
このようにExcelシートから読み取りのアクションを２つ追加します。
これで転記したいデータの読み取り設定が完了しました。
3）読み取ったデータをWebサイト(B)の取込用Excelフォーマットに転記
今回利用する取込用Excelフォーマットです。よろしければお使いください。
先ほどと同じようにまずは「Excelの起動」アクションで取込用フォーマットを開き、アクティブなシートを指定します。
このフォーマットの変数は「ExcelInstance2」ですね。
起動からアクティブ化の完了です。
次は転記ですが、アクションは「Excelワークシートに書き込み」を追加します。
まずは「ExcelDate（A2：D列最終行まで）」を転記します。
転記先フォーマットの開始位置と、転記したいデータの変数の関係は以下の通りです。
次に「ExcelDateseibetsu（G2：G列最終行まで）」を転記します。
書き込みの完了です。一度ここまで実行して確かめてみてください。途中途中で実行してみながらちゃんと動くか確かめながら進めるのがおすすめです。
4）転記したシートに現在日付を含む名前を付けて保存して閉じる
アクションから「現在の日時を取得します」を追加します。
取得から日時と日付のみかを選択できますが、今回は日付を選択します。
取得された日付が「CurrentDateTime」変数に格納されます。
Excelを保存して閉じるには、「Excelを閉じる」アクションを追加します。
転記した「入社.xlsx」のExcelインスタンスは「ExcelInstance2」でしたね。
ここでファイル名を「入社20210528」のように本日の日付をファイル名に入れて保存するには、以下のように「入社」と「.xlsx」の間に先ほど取得した現在日付の変数を追加します。
「ｘ」をクリックすると変数の選択画面が出てきます。「CurrentDateTime」をさらにクリックすると、「.Year」「.Month」「.Day」と細分化された変数も選択できるようになっているので、それを「入社」と「.xlsx」の間に入れて保存します。
最後に、「ExcelInstance」、転記元のファイルも保存しておきましょう。
こちらは保存不要なので「ドキュメントを保存しない」で追加します。
さあ、これで今回のPADフローができあがりです！実行してみてください！ちゃんと転記されて名前がついて保存され、Excelも閉じられて完了しているはずです。実際私が作成したものは、もっと項目数が多く、転記先のシートも多数ありましたが、基本的にはこの流れで項目数とシート数分設定を繰り返せばOKです。フローが長くなると後から見た時に分かりにくくメンテナンスしづらくなるので、以下のように「サブフロー」という機能を使って転記先のフォーマットごとに作るなどするのもお勧めです。
最初作るのに少し時間が必要ですが、一度作ってしまえば後はあっという間に作業を完了してもらえます。そしてどうでしょう、今回の転記について、VBAを教えて引き継ぐよりもPADの方が楽そうに感じたりしませんか？わたしはこれに関してはPADの方が引き継いでもらう人にハードルが低そうな気がしました。
3.まとめ
① Webサイト(A)へアクセスし、ID・PASSを入力してログインする。
② チェックボックスへチェックを入れて、csvデータをダウンロードし、名前を付けて所定フォルダへ保存する。×複数回
ここは前回の記事でPADで作成ができました。PADの実行ボタンを押しましょう。
③ ②のcsvデータを結合する→条件に合うデータをプルダウンで抽出する→csvデータには入っていない情報を別途Excelからvlookupで紐づけて、今回Webサイト(B)に取り込みたい対象データを整える。
ここは今日のnote前半でPowerQueryで作成できましたので、今後は「すべて更新」をクリックするのみです。※ここもPADで自動化できます
④ ③のデータをWebサイト(B)の取込用Excelフォーマット（※常に所定のフォルダにフォーマットを保管してある）に転記し、フォーマット名に今日の日付を加えた名前を付けて、所定フォルダへ保存する。
ここは先ほどPADで作成できました。PADの実行ボタンを押しましょう。
⑤ Webサイト(B)へアクセスし、ID・PASS・2段階認証PASSを入力してログインし、④のExcelファイルをアップロードする。
ここは前回PADで2段階認証のログインを作成しました。アップロードするところはまだ成功できていない為今回はご紹介できませんでした。PADを実行してログインした後、手動でアップロードしてください。
いかがでしたでしょうか？現在の業務ですぐに使えそうなものはありませんでしたか？
PowerQueryはやってみるとすぐわかると思います。複数のデータをVlookUPで定期的に結合している業務がある方はぜひ今すぐ使ってみてほしいです。WEBとExcelにまたがった定型的な業務がある方もぜひPADでできないか？を検討してみてほしいです！！
分かりづらい部分もあったかと思いますが、とにかく1日でも早くこういったものがあるよ！と知っていただきたくて書きました。少しでもどなたかの業務改善のヒントになったらうれしいです。
最後までお読みくださりありがとうございました！！
今後もこういった実際に業務で作ったツールや、未経験から総務情シスの担当になった者として学んだことなど、noteに書いていけたらいいなあと思います。それではまた。