第31回 VBA会 佐藤さん PowerQuery
佐藤嘉浩承旨/MOS365&2019全試験コンプ・日商PCプロフェッショナル@Officeの魔法使い
今夜はOfficeの魔法使いこと 佐藤嘉浩さんがPowerQueryとVBAからの活用法を教えてくれます。さてさてどんな内容か楽しみぃぃ。
（いきなり黒背景かっこいいな）
（縦書きキタｗ）
（PowerQueryとは、一覧表の専門家！）
（大事なことは！「クリエ」ではありません！クエリです！><）
（まずはパワクの元となるテーブルの特徴から）
（PowerQueryとVBAは組み合わせて使うべし！）
テーブルのデータがない状態からデータを削除するとエラーになるのはテーブルの面倒なところ。1個データを書き込んで回避するかOnErrorで回避します。
（これはDataBodyRangeプロパティのことだな）
（いろいろな機能を組み合わせると開発効率向上！）
ここからハンズオン！
末尾の題材.xlsxを参照
（この表をPowerQueryで作っているとな・・・）
これを作っていきます。
ハンズオン Step0 事前準備
シートは案件、見積、支出、見積作成、支出入力
（案件毎に複数の見積もり項目がある）
（支出も案件毎に複数の項目がある）
こういったバラバラな表を整えるのがPower Queryが得意！
ハンズオン Step1 テーブル化
まずは全部テーブルにする！
データの一つを選んでCtrl+T！
（CTRL+L でもいけるぜ！。TableとListの頭文字らしいぜぇ）
（テーブの削除は、右クリックで「テーブルの削除・行」でOK）
（テーブルの名前は分かりやすく設定しましょう）
同じように見積と支出もテーブル化！
ついでに見積作成と支出入力
テーブルは便利な機能満載だが、データ量が多いと重くなるかも。
ハンズオン Step2 Power Queryでデータ読込
Power Query というメニューはない。
テーブルまたは範囲の取得ボタンはここ
（Power Queryエディターキタァァァァ！）
PowerQueryのデータは厳密。日付には時刻も含まれる。空欄はnull。
時刻が不要な場合は、日付型に変更。
この変更はここに反映される。これをM言語と呼ぶ。
閉じて次に読み込むを選択する。
今回は表示させたくないので、「接続の作成のみ」を選択する。
画面右側の「クエリと接続」画面が表示される。
見積シートと支出シートも同様にPowerQueryで処理するんちょ。
ハンズオン Step4 案件番号毎集計PowerQuery
見積の集計表を作成する。
見積を右クリックして参照を選択
見積(2)が作成される。見積を変更すると見積(2)にも反映される。
名前は「見積集計」に変更
案件毎にグループ化を行う。
これで見積集計シートの作成が完了。
（・・・えっ？もう？早っや！）
同様に支出集計を作成する。
（左メニューのところからも「参照」が使えるのね。）
案件一覧も作成
ハンズオン Step5 クエリのマージ
クエリのマージ
案件一覧と見積集計をマージ
見積集計のなかから見積額を表示する。
次に支出集計をマージする。
支出額だけを表示する。
一旦「閉じて次に読み込む」
ハンズオン Step6 案件一覧の作成
利益額を追加するために再編集
今回はカスタム列を追加。
ハンズオン Step7 データ更新
元データを触ってみる。一部案件の支出を消してみる。
あれ？更新されてないね。
PowerQueryの結果は自動更新されない。「すべて更新」で更新される点は要注意。
ちょこっと小技。列幅の自動設定。
PowerQueryはここまで。
ここから別の技の披露
Vlookupでテーブル参照するとテーブル名が入る。
WorksheetSelectionChangeイベントを使って、案件一覧シートのセルを選択したらその案件番号を見積作成に転記する。
転記したあと、シートと次の見積もり項目を選択。このほうが次が記入しやすい。これでクリック直後から書き込める。
次は見積作成の内容を見積テーブルにに追記するプログラム。
（・・・目も追いつけなくなったので...完成品はこちら！m(_ _)m）
終わりかと思ったらまだあった
（魔法使いはスピル推し(^^))
雑談いろいろ
御礼と投げ銭ご協力のお願い
今回、なんと80名以上の方にご参加いただきました。
多くの方から投げ銭もいただきありがとうございました。
あっ！忘れてたって方はこちらからお願いいたします。
・PayPayへの直接送金 (しゃあ専用PayPayID=charpay0059）
・note有料記事の購入（投げ銭ページ購入は1回のみ可）
・noteサポート（何度でも可）
・楽天キャッシュ（TwitterDM・リンク送付）
この記事が気に入ったらサポートをしてみませんか？