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]ボタンをクリックするとワークシートの右端に「クエリと接続」が表示されます。

ここに表示されている"クエリ"とは、要するに先ほど読み込んだデータです。マウスポインタを合わせると、プレビューや情報が表示されます。

また、それぞれのクエリを右クリックするとメニューが表示されます。

ここで[編集]を実行すると、右クリックしたクエリをPower Queryエディタで開けます。今回は[読み込み先]をクリックします。実行すると先の[データのインポート]ダイアログボックスが表示されますので[テーブル]を選択して[OK]ボタンをクリックします。

実行すると3つのデータが合体された表の完成です。

ここで注目していただきたいのは、セルにはVLOOKUP関数などの数式ではなく、値が入っているということです。

もちろん、元データが変更されると、こちらの表も更新されます。セルのVLOOKUP関数がデータを調べているのではなく「取得と変換」がデータベース的にやっていることなので、再計算に時間がかかることもありません。