今まで気になっていた、Power Query（パワークエリ）ですが、２つのシートをキーとなる値で連結したい場面に遭遇したので、調べてみました。
しかも、Power Queryだとフィルターも保存しておけるので、大変便利です。
連結とフィルターの他、ファイルを開くときに自動更新するようにしておきます。
Power Queryを使うにあたって、Office TANAKAさんのページを参考にしました。
Office TANAKA – 取得と変換[VLOOKUP関数と同じことをする]
Office TANAKAさん の「Office TANAKA – 取得と変換[VLOOKUP関数と同じことをする]」
早速ですが、自分でできるかやっていきたいと思います。
今日のレシピです。
１）Excelを開いて、マージ先ファイルを選択する
ダイレクトに「テキストまたはCSVから」を選択し、開いたwindowからファイルを選択する
もしくは、
データタブ ＞データの取得 ＞ファイルから＞テキストまたはCSVから
※データの取得をクリックすると、様々な種類のファイルを選択することができます。
尚、ファイルは１つしか開けないので注意
２）「データの変換」でクエリの画面に
データを確認し、「データの変換」
データがシステム等からダウンロードしてきた場合は、文字化けの可能性もあるので注意。
３）マージ元になるファイルを読み込む
新しいソース ＞ファイル選択
２）の画面で、他方のファイルし、データ変換を押す
※２）で読み込んだ画面は、左側の水色枠で正常に読み込んでます
※読み込んだファイルのヘッダーが図のように自動で追加されてしまった場合は、ホームタブにある「1行目をヘッダーとして使用」を選択し、１行目をヘッダーとして昇格させましょう
４）マージ先ファイルを選択 し、クエリのマージする
今回は、売上ファイルに商品名がなかったので、まず、売上のクエリを選択し、ホームタブ＞クエリのマージを選択
図の② ＝ キーとなる列選択
③ ＝品名の入っているクエリ選択
④＝キーとなる列選択
⑤＝図のもの、デフォルトのまま
→OKを押す
５）マージされたクエリを展開
品名という列が追加されたのを確認したので、①の左右方向の矢印ボタンをクリック
展開 、集計を選択できるWindowが出るので、非表示させる列名のチェックを外す
→OKを押す
※元も列名をプレフィックスとして使用しますのチェックは外します
６）列並びを修正
移動したい列にマウスを合わせドラッグ、移動先にドロップする
７）フィルターの修正
フィルターをかける列を選択、適当にフィルターすると、関数バーに関数が追加される。
関数バーに、赤枠の中のようにandで結ぶと、項目になかったとしても、フィルタが追加できる。
ワイルドカードは使えないらしいので、その場合「値を含むで」フィルタすると、each Text.Contains関数が得られ、似たようなことができます。
※今回はコード E0001でフィルターしました。
= Table.SelectRows(並べ替えられた列, each ([コード] <> “E0001” and [コード] <> “Z0002”))
８）Excelに接続を作成
閉じて読み込む＞閉じて次に読み込む
データのインポート画面
＞接続の作成のみ
→OK
９）Excelにクエリを読み込む
通常のExcel画面の右側に「クエリと接続」という画面が表示されていると思うので、読み込みたいものを選ぶ
更に、プレビューが出てきたら、三点リーダーをクリック
＞「読み込み先」を選択
テーブル を選択
→OK を押す
10）ファイルを開くときにデータ更新する設定
クエリの上で、右クリック
＞プロパティ
または
クエリタブ＞プロパティ
「ファイルを開くときにデータを更新する」にチェックを入れ
→OKを押す
11）できあがり
最初は、ちょっと戸惑ったものも、1、2回やってみるとすぐ覚えられますね。
ちなみに、出来上がりがテーブルじゃ困るというときは、
「テーブルデザイン」タブ＞ツール＞「範囲に変換」をクリックすると、テーブルが解除されます。
書式は、変わらなかったので、最初から、色なしでテーブルとしておいたり、あとから書式変更する必要があります。