Excelで他の表から値を取得するときは何を使っていますか？VLOOKUP、HLOOKUP、XLOOKUPなどを使っていると思います。（もしかしたらINDEX MATCHを使っているコアな方もいるかも）
数式が1、2個程度で、データが数万件程度ならレスポンスは気にならないかもしれません。しかし、数式の数が多くなるとExcelが固まることもあります。
そんなときは、Power Queryを使えばレスポンスが改善できます。
前提条件として、検証したパソコンはSurface Laptop 2を使っています。
スペックは次のとおりです。
CPU：Intel(R) Core(TM) i5-8250U CPU @ 1.60GHz 1.80 GHz
メモリ：8.00GB
ストレージ：SSD
Excelの数式（VLOOKUPなど）を使った場合の時間を計測した
テストデータとして30万件用意しました。VLOOKUPで1列突合した場合は7.5秒、VLOOKUPで3列突合し、計算式3列を追加した場合は28.5秒かかりました。
30万件のデータをVLOOKUPで1列突合する
VLOOKUPで商品名から単価を取得する数式を書きました。
商品マスタは次のデータを使いました。
この数式を30万行までコピーする時間を計測したところ、7.5秒ほどでした。
7.5秒ぐらいなら全然待てるのでは？と思いますが、数式が増えればそんなことも言っていられなくなります。
30万件のデータをVLOOKUPで3列突合し、計算式3列を追加する
単価、製造工場、製造原価(1個)を商品マスタから取得するように、VLOOKUPを使った数式を3列追加しました。また、金額（数量×単価）、製造原価（数量、製造原価(1個)）、粗利（金額ー製造原価）を求める計算式を3列追加しました。
オレンジ色がVLOOKUPを使った数式、黄色が計算式になります。
この数式を30万行までコピーする時間を計測したところ、28.5秒ほどでした。
なかなか時間がかかっていますね。ではこれをPower Queryを使えば何秒ほどで終わるでしょうか？
Power Queryを使う場合、VLOOKUPは「クエリのマージ」、掛け算などの数式は「カスタム列」を使います。
Power Queryでの実装方法を解説します
Power Queryで実装した場合、「VLOOKUPで3列突合し、計算式3列を追加した」場合は12秒でした。Excelの数式では28.5秒だったので半分以下ですね。※VLOOKUPで1列突合した場合は差がわかりにくいため、検証から除外しました。
Power Queryでの実装方法を解説します
データを取得する
Excelの表をPower Queryで取得する必要があります。売上明細と商品マスタを取得します。
売上明細を取得する
「データ」タブを開き、売上明細の表を範囲選択し、「テーブルまたは範囲から」をクリックします。
「テーブルの作成」ポップアップが表示されます。OKをクリックします。
Power Queryエディターが開きます。「クエリの設定」の「名前」を分かりやすいよう「売上明細」に変更します。
「売上日」列のデータ型が「日付/時刻」になっているため日付型に変更します。「変換」タブを開き、データ型の▼をクリックし、日付を選択します。
「現在のものを置換」をクリックします。
「ホーム」タブを開き、「閉じて読み込む」をクリックします。
売上明細が取得できました。
商品マスタを取得する
「データ」タブを開き、商品マスタの表を範囲選択し、「テーブルまたは範囲から」をクリックします。
「テーブルの作成」ポップアップが表示されます。OKをクリックします。
Power Queryエディターが開きます。「クエリの設定」の「名前」を分かりやすいよう「商品マスタ」に変更します。特に加工の必要はないため、「閉じて読み込む」をクリックします。
商品マスタが取得できました。
データを加工する
売上明細と商品マスタが取得できました。次は2つの表をひとつにまとめて、新規列追加を行います。
「クエリのマージ」で2つの表をまとめる
Power Queryエディターを開くと、クエリが2つ（売上明細と商品マスタ）あることを確認してください。
この2つのクエリ（表）を1つにまとめます。売上明細を選択した状態で、「クエリのマージ」の横にある▼をクリックし、「クエリのマージ」を選択します。
商品マスタを選択します。
売上明細と商品マスタを結合するための照合列をそれぞれクリックします。今回は商品名で照合します。OKをクリックします。
売上明細と商品マスタが一つにまとまりました。売上明細に「商品マスタ」列が追加されています。
「商品マスタ」列の↔️をクリックすると、商品マスタの中身を展開できます。売上明細に存在する「商品名」はチェックを外します。「元の列名をプレフィックスとして使用します」はチェックを外し、OKをクリックします。
プレフィックスがつくと、「商品マスタ.商品名」といった感じで、列名の頭にクエリ名が付与されますが、不要であればチェックを外したほうがスッキリします。
商品マスタの中身が展開されました。単価(円)、製造工場、製造原価はVLOOKUPで取得したときと同じ結果になります。
「製造原価」列は1個あたりの原価のため、列名をダブルクリックして、「製造原価(1個)」に修正しておきましょう。
「カスタム列」で金額などの計算式を追加する
数式が入った列を追加する場合は、「列の追加」タブを開き、「カスタム列」をクリックします。
「新しい列名」に列名、「カスタム列の式」に数式を入力します。数式では他の列の値を参照できます。「使用できる列」をダブルクリックすると、「カスタム列の式」に列名を自動入力してくれます。
製造原価、粗利も同様にカスタム列を追加して、数式を入力していきます。
カスタム列などの列追加に関しては次の記事で詳細に解説しています。
データの更新
カスタム列の追加が終わったら、「ホーム」タブを開き、「閉じて読み込む」をクリックします。
クエリの実行は12秒ほどで終わり、Excelの数式を使った場合と比べると半分以下の時間になりました。
しかし、その後「列の幅の調整中」と表示され、これに割と待たされます。列幅を自動で調整してくれているのですが、正直おせっかいだと思うときは設定をオフにします。
「データ」タブを開き、「プロパティ」をクリックします。
「列の幅を調整する」のチェックを外し、OKをクリックします。
これで、クエリ実行後に「列の幅の調整中」がなくなり、早く更新できるようになります。
最後に
Excelは数式を使えば使うほど重たくなってきます。パソコンのスペックによってはExcelが固まることもあります。Power Queryを使ったほうが、更新が早くなるため、大量データの処理や大量の数式を使っている場合はPower Queryの活用を検討ください。
