VLOOKUP関数と同じことをする
「取得と変換」を使って、VLOOKUP関数と同じことができます。先に結果だけお話ししておきますが、Excelに取り込むデータにVLOOKUP関数自体は含まれていません。計算結果だけです。だから再計算は起こりません。
まず、Excelのブック「2018-12.xlsx」があります。ここには「社員コード」と「地域コード」しか入力されていません。
「社員コード」に該当する名前は、社員マスターに入力されています。これはCSVファイル「社員マスター.csv」です。
最後に「地域コード」に該当する県名です。これはAccessのデータ「県名マスター.accdb」として作りました。
では、いきます。[データ]タブを開き[データの取得]ボタンをクリックし、[ファイルから]-[ブックから]をクリックします。
ファイル(2018-12.xlsx)を選択して[インポート]ボタンをクリックします。
ナビゲーター画面で[編集]ボタンをクリックします。
実行すると、Power Queryエディタが開いて、次のようになります。
次に「社員マスター.csv」と[県名マスター.accdb]を開きます。Power Queryエディタのリボン右端にある[新しいソース]ボタンをクリックして、[ファイル]-[テキスト/CSV]をクリックします。
ファイルを選択して[インポート]ボタンをクリックします。
Power Queryエディタに読み込んだ「社員マスター.csv」を見ると、1行目の"社員コード"と"名前"がタイトルと認識されていません。こんなときは[1行目をヘッダーとして使用]ボタンをクリックします。
続いて同じ手順で「県名マスター.accdb」を開きます。
「県名マスター.accdb」を開いたら、最初に読み込んだブックを開きます。Power Queryエディタの左端にある[クエリ]で最初の[Sheet1]を選択します。
続いてVLOOKUP関数みたいなことをします。[クエリのマージ]ボタンをクリックします。
表示される[マージ]画面の中ほどにあるドロップダウンリストボックスで「社員マスター」を選択します。続いて、上の[Sheet1]で[社員コード]をクリックして選択します。さらに、下の[社員マスター]でも同じ[社員コード]をクリックして選択します。
[OK]ボタンをクリックすると、データの右端に[社員マスター]のデータが読み込まれます。データはすべて"table"と表示されています。この[社員マスター]のタイトルにある小さなボタン(これ、なんていうボタンだろ)をクリックします。
実行すると、オートフィルタみたいなリストが表示されますので、[社員コード]チェックボックスをオフにし、[元の名前をプレフィックスとして使用します]のチェックボックスをオフにします。
[OK]ボタンをクリックすると名前が表示されます。これ[社員コード]を「社員マスター.csv」からVLOOKUP関数したのと同じです。
これは後でやってもいいのですが、ついでですから今読み込んだ[名前]列のタイトルを左にドラッグして位置を変更しておきましょう。
同じ手順で[社員マスター]も読み込みましょう。
これも必須ではありませんが、[社員コード]列と[地域コード]列は削除しましょうか。2つの列を選択して[列の削除]ボタンをクリックします。
Power Queryエディタでの編集は終了です。[閉じて読み込む]ボタンをクリックしてメニューを開き[閉じて次に読み込む]をクリックします。
表示される[データのインポート]ダイアログボックスで[接続の作成のみ]を選択します。もしここで[テーブル]を選択すると、今読み込んでいる3つのファイルがすべて別々のワークシートに独立して読み込まれてしまいます。ここでは、3つのデータをすべて開きたいのではなく、3つのデータを集約した最初のデータ(2018-12.xlsx)だけを読み込みたいんです。そんなときは一度[接続の作成のみ]を選択します。
[OK]ボタンをクリックするとワークシートの右端に「クエリと接続」が表示されます。
ここに表示されている"クエリ"とは、要するに先ほど読み込んだデータです。マウスポインタを合わせると、プレビューや情報が表示されます。
また、それぞれのクエリを右クリックするとメニューが表示されます。
実行すると3つのデータが合体された表の完成です。
ここで注目していただきたいのは、セルにはVLOOKUP関数などの数式ではなく、値が入っているということです。
もちろん、元データが変更されると、こちらの表も更新されます。セルのVLOOKUP関数がデータを調べているのではなく「取得と変換」がデータベース的にやっていることなので、再計算に時間がかかることもありません。