ExcelのPower Queryを使った顧客データの処理を自動化する方法

Power Queryとは

Power Query（パワークエリ）とは、マイクロソフト社が提供するデータ変換およびデータ準備の処理機能の名称です。Power Queryを使うと、Excelなどの別のデータを簡単に取得、再計算、整形することなどができます。接続した外部のデータとは別のデータベースを新たに作るような形になるため、接続先のデータが改変されないのが特徴です。さらに、元データが更新された場合は、更新部分も含めてPower Queryを実行できるので、データに関する処理の「自動化」とも言える機能です。

Power Queryはマイクロソフト社のさまざまなツールで採用されており、ExcelではExcel2016から標準機能になりました。M言語と呼ばれる数式で記録されますが、Excelでは直感的な操作のみでもExcel同士のデータの変換や結合を指示できます。

出典：マイクロソフト社Excel の Power Query について

Power Queryでできること

Power Queryでできることは、データ処理の自動化です。Excelファイルはもちろん、CSVファイルのデータと統合したり、SQLサーバーと接続してビッグデータの一部を取得したりすることができます。元データを改変することなく、行の追加や削除、テーブルの変更が自在にでき、定期的に接続したデータを更新して最新の状態にすることができます。

データベースからデータを取得して加工する、という点で「Excel」と同じマイクロソフト社の製品である「Access」がありますが、Power Queryは「Excel」内で完結でき、かつ直感的に操作できることから、データ活用のハードルを下げてくれますす。

展示会リードと顧客管理・商談管理のファイルをPower Queryで接続しよう

突然ですが、あなたの会社がこれまでに展示会に出展していたら、その展示会で取得したリードのうち、今何件受注できているかすぐに回答できますか？顧客管理・商談管理用のExcelを使っていたとしても、展示会出展で得たリード（見込み客）リストは別に持っていて、すぐに集計するのは難しいのではないでしょうか？これを解決するのがPower Queryです。

例えば、①展示会主催者から提供されるリード一覧のCSVファイル、②自社の「顧客管理・商談管理テンプレート」というExcelファイル、の2つが存在するとします。①と②にはそれぞれさまざまな顧客情報が入っています。ただそれぞれにはメールアドレス、という情報が入っているため、②の商談管理シート内にある受注企業の窓口担当者のメールアドレスが、①のリストにどれくらい存在するか、を可視化することを目的にPower Queryを使います。使用するファイルは以下の2つです。

「展示会リードサンプル.csv」をダウンロード

「顧客管理・商談管理テンプレート.xlsx」をダウンロード

ゴールはこれだ

Power Queryが設定できたExcelファイルの動作を見てみましょう。画面のExcelファイルには、上記のファイルのデータが複数のシートに分かれて入っています。画面上の商談管理シートには、ステータス（F列）が「受注」となっている商談が表示されています。さらに、これまでに存在しなかった「展示会リードチェック（J列）」が加わっています。Excelのリボンの[すべて更新]をクリックすると、それまでシートには無かった企業「△△△商事株式会社」が2行目に現れた上、J列には「展示会リストから受注！」と表示されました。この「△△△商事株式会社」の商談が、展示会で得たリードからの受注であることが分かりました。

ゴールはこの状態です。順番に解説します。

新規のExcelに2つのファイルを接続しよう

まず、効果測定用の新規のExcelファイルを作成します。その新規ファイル上で、先ほどの「展示会リードサンプル」と「顧客管理・商談管理テンプレート」を接続します。いきなりPower Queryの出番です。

まずは、「展示会リードサンプル」を接続します。[データ]>[データファイル指定]をクリックし、[データソースへの選択]画面で「テキスト/CSV」をダブルクリックします。

次に表示される[データソースへの接続]ウィンドウで、会社の共有フォルダなど、「展示会リードサンプル」を保存した場所からファイルを選択して[データの取り出し]、その後[次へ]を押下します。

[データの取得（Power Query）]ウインドウにCSVファイルのサンプルが表示されたら[読み込む]を押下します。

画面がExcelに戻り、以下のように表示されれば接続は完了です。

次に、「顧客管理・商談管理テンプレート」を接続します。[データ]>[データファイル指定]をクリックし、[データソースへの選択]画面で「Excelブック」をダブルクリックします。

保存場所の「顧客管理・商談管理テンプレート」を選択した後、Excelの中のどのシートのデータを接続するかを選択できる画面になります。ここで、「見込み客・取引先マスタ」と「商談管理」の2つを選択してから[読み込む]をクリックします。このExcelファイルのうち、今回の効果測定に使うシートは2つだけだからです。

これで2つのファイルの接続が完了し、効果測定用のExcelファイルに必要なデータがそろいました。

データを整形しよう

データの接続ができました。ただ、シートによっては、見出しにしたい行がきちんと認識されていなかったり、分析には不要な行、列が入っていたりします。このデータの整形にもPower Queryが力を発揮します。データの読み込みの段階で見出しの行を変更したり、不要な列を削除する設定をしましょう。

見込み客・取引先マスタシートについて、以下を設定します。

次に、商談管理シートについて、以下の処理を設定します。

商談管理シートにメールアドレス（I列）とリードチェック列（J列）を追加しよう

商談管理シートには、これまで受注した商談が表示されています。これだけでは、受注した商談が展示会リードに該当するか判別ができません。そこで、I列には、この商談の窓口担当者のメールアドレスを見込み客・取引先マスタシートから転載するようにします。さらにJ列には、I列に表示したメールアドレスが展示会リードサンプルシートに存在するか調べる数式を入力します。

I列1行目に「メールアドレス列」と入力。I2セルに、C列のNo.をキーにして、この商談の担当者のメールアドレスを「見込み客・取引先マスタ」シートから転載する式を入力。I2セルの式は次の通りです。I2セルを入力すると、I3セル以下の式も自動で入力されます。

Excelに使われている関数	説明
XLOOKUP関数
	I2セルを解説します。 この式は、XLOOKUP関数を使用して、特定の条件でセルの値を検索し、その結果に応じて条件分岐を行うためのものです。以下が各部分の意味です。 [@[NO.]]：テーブルのカラム NO. から取得される現在のセルの値（この式が使用されているセルの行の NO. 列の値）。 見込み客・取引先マスタ!$A$2:$A$1048576：テーブル 見込み客・取引先マスタ の A 列全体。この範囲で XLOOKUP 関数は検索を行います。 見込み客・取引先マスタ!$H$2:$H$1048576：テーブル 見込み客・取引先マスタ の H 列全体。この範囲から返り値が取得されます。 "該当なし"：検索結果がない場合に表示される値。 したがって、この式は、「見込み客・取引先マスタ シートの $A$2:$A$1048576 でセル NO. の値を検索し、対応する行から H 列の情報を返す。検索結果がない場合は "該当なし" と表示する」という意味になります。

J列1行目に「展示会リードチェック」と入力。J2セルに、I列のメールアドレスをキーとして、「展示会リードサンプル」シートにあるかを調べあった場合は「展示会リストから受注！」の文字列を表示、無かった場合は「該当なし」を表示する式を入力します。J2セルの式は次の通りです。J2セルを入力すると、J3セル以下の式も自動で入力されます。

Excelに使われている関数	説明
IF関数 ISERROR関数 XLOOKUP関数
	J2セルを解説します。 この式は、XLOOKUP関数を使用して I2 の値を 展示会リードサンプル シートの $F$2:$F$1048576 の範囲で検索し、その結果に基づいて条件分岐を行います。以下が各部分の意味です。 I2：この式が適用されているセルの行の I 列の値。 XLOOKUP(I2, 展示会リードサンプル!$F$2:$F$1048576, 展示会リードサンプル!$F$2:$F$1048576)： - I2 の値を 展示会リードサンプル シートの $F$2:$F$1048576 の範囲で検索し、その結果を返します。この部分が XLOOKUP 関数で、検索対象の値が見つからない場合、ISERROR関数でエラーを検知できるようになっています。 ISERROR(XLOOKUP(...))： - XLOOKUP関数の結果がエラー（#N / A）かどうかを判定します。エラーがあれば ISERRORは TRUE を、なければ FALSE を返します。 IF(ISERROR(XLOOKUP(...)), "該当なし", "展示会リストから受注！")： - ISERRORの結果が TRUE（エラーがある場合）であれば、"該当なし" と表示します。エラーがなければ FALSE で、つまり検索結果が見つかった場合は "展示会リストから受注！" と表示します。 したがって、この式は、「展示会リードサンプル シートの $F$2:$F$1048576 でセル I2 の値を検索し、検索結果があれば "展示会リストから受注！"、なければ "該当なし" と表示する」という意味になります。

更新を確認しよう

以上で全ての設定が完了したので、Power Queryを設定した効果測定用の新規のExcelファイルを任意の場所に保存しましょう。データタブの[すべて更新]を押下すると、設定したPower Queryの処理が行われ、データが更新されます。

元データの情報がどのように効果測定用に反映されるか、を見てみましょう。分かりやすいように、動画の画面の上半分が元データとなる「顧客管理・商談管理テンプレート」、下半分が効果測定のExcelファイルを並べました。「顧客管理・商談管理テンプレート」でステータスを変更して保存し、効果測定用のファイルの[すべて更新]のアイコンをクリックすると、シートが変化することが分かります。

元データは随時営業担当者が更新し、分析の担当者は必要なタイミングで効果測定用のExcelの更新アイコンをクリックするだけで済みます。分析の担当者はこの展示会リード経由の売上実績をどうとらえるか、という「解釈」に時間を使う必要があり、本来の分析業務を効率化することができます。

Power Queryの注意点

複数のデータの変換や統合に力を発揮するのがPower Queryです。ただ、Excel2016で登場した機能のため、自在に扱える人ばかりではないでしょう。参照元に指定していたファイルを誰かが移動してしまってPower Queryによる更新が動かなくなった場合、Power Queryを理解している人と、ファイルの移動先を把握している人がいなければ復活できない、という事態を想定しておくことが必要です。

Power QueryもVBA同様、職場の複数人が対応できる状況が望ましく、さらには、重要なファイルの格納場所を安易に移動してしまわないような管理体制も必要でしょう。