文字列が連結された形で外部データを受け取り、Microsoft Excel にインポートする必要がある場合があります。通常、文字列の一部のみが必要な場合でも、データセット全体をインポートします。
例えば、取引番号のリストを受け取り、その中に顧客ID番号が含まれているとします。Excelでは、顧客ID番号の部分だけを使って、その顧客と顧客名を含むテーブルとのリレーションシップを作成できます。こうすることで、レポート作成時に、閲覧者は意味のない番号ではなく、顧客名だけを見ることができます。
このような場合は、Power Queryの抽出機能と列分割機能を使用して、区切り文字列をコンポーネントに抽出できます。ここではMicrosoft 365 DesktopとMicrosoft ExcelのPower Queryを使用しています。Power QueryはExcel 10までの旧バージョンでもご利用いただけます。このチュートリアルのMicrosoft Excelデモファイルをダウンロードできます。
ジャンプ先:
- ExcelのPower Queryを使うべき理由
- Power Queryにデータを取り込む方法
- Power Query の抽出オプションを使用して区切られた文字列を抽出する方法
- Power Queryの列分割を使用して区切られた文字列を抽出する方法
ExcelのPower Queryを使うべき理由
Excel の文字列関数、テキストの列分割、フラッシュ フィルを使用することもできますが、次のような理由により使用できない場合があります。
- テキストを列に書き込むと、元のデータが上書きされます。
- データは Excel 内に存在しない可能性があります。Power Query は Excel で使用できますが、Power Query では Excel だけでなく、さまざまなソースからデータをインポートできます。
- ソース データには、Excel にインポートできる行がさらに含まれています。
- もっと複雑な処理には Power Query を使用する必要があるかもしれませんが、元のエントリのサブセットを抽出するのは、その最初のステップにすぎません。
データがExcelにある場合は、関数や数式を使うこともできますが、熟練者でない限り、少し時間がかかります。必要な構文をスラスラと書いて、最初から正しく実行できる人は少ないでしょう。Power Queryは高速で、Excel関数の専門知識は必要ありません。
Power Queryにデータを取り込む方法
ここでは、TableCustomerID というテーブルにいくつかの区切り文字列が入ったシンプルな Excel シートを使います。デフォルトのテーブル名を変更する必要はありませんが、複数のテーブルがある場合は、意味のある名前を付けた方が作業がしやすくなります。ただし、データは Table オブジェクトとして書式設定されている必要があります。そうでない場合、Power Query によってデータ範囲の変換を求めるメッセージが表示されます。
参照: Microsoft Excel の Power Query でテーブルを作成してデータを入力する方法は次のとおりです。
顧客識別番号のリストがあり、それぞれが3つのセクションに分かれているとします。さらに、ハイフン文字が3つのセクションの区切りとして機能します（図A）。各文字列の中央の要素を使用するのは、その部分が実際に各顧客を識別するセクションだからです。残りの2つの要素は、顧客の居住地域と取引番号を識別します。
図A
最初のステップは、次のようにデータを Power Query に読み込むことです。
1. テーブル内の任意の場所をクリックします。
2. 「データ」タブをクリックします。
3. [データの取得と変換] グループで、[テーブル/範囲から] をクリックします。
これで完了です。図 Aに示すシンプルなテーブルが Power Query に追加されました。
Power Query のデータを使用して、セクションの抽出を開始できます。
Power Query の抽出オプションを使用して区切られた文字列を抽出する方法
Power Queryでデータを抽出する方法は2つあります。まずは、元の値のサブセットを返す「抽出オプション」を使用します。まずは以下の手順で行います。
- [変換]タブをクリックします。
- 「テキスト列」グループの「抽出」ドロップダウンをクリックします。図Bに示すように、いくつかのオプションがあり、大部分は説明を要しません。ここでは区切り文字のオプションについて見ていきます。それぞれのオプションがどのような機能を果たすのか理解できるでしょう。
図B
- 顧客 ID フィールドのヘッダーをクリックして列を選択した後、必要に応じて [変換] タブをクリックします。
- テキスト列グループで抽出をクリックします。
- 「区切り文字の前のテキスト」オプションを選択します。表示されるダイアログでハイフン文字（図C）を入力し、「OK」をクリックします。
図C
図 Dに示すように、このオプションは区切り文字の前の最初の文字のみを返します。
図D
元のデータを取り戻すには、図 Eに示す [適用されたステップ] ペインで [区切り記号の前の抽出されたテキスト]ステップを右クリックし、コンテキスト メニューから [削除] を選択して削除します。
図E
参照: Microsoft Power Query を使用して重複を検索する方法。
次に、次のオプション「区切り文字の後のテキスト」でも同じ操作をしてみましょう。プロンプトが表示されたら、ハイフン文字を入力して「OK」をクリックすると、図Fに示す結果が表示されます。今回は、Power Queryによって最初の2文字（最初の数字と最初のハイフン）が削除されます。
図F
もう一度、抽出ステップを削除し、「区切り文字間のテキスト」オプションを選択して元のデータを取り戻します。今回は、Power Query によって2つの区切り文字の入力が求められます。この場合、どちらもハイフン文字です（図G）。
図G
[OK]をクリックすると、図 Hに示す結果が表示されます。
図H
さて、各文字列を3つのセクションに分割する別の方法を見てみましょう。ただし、文字列から部分を抽出するのではなく、文字列自体を分割します。続行する前に、元のデータを取り戻してください。
Power Queryの列分割機能を使うと、文字列を複数の部分に分けて返すことができます。例えば、セクションごとに1列ずつ、合計3列のデータを取得したいとします。これを実現するには、次のように列分割機能を使用します。
- 列を選択したら、[ホーム] タブをクリックします。
- [変換] グループで、[列の分割] をクリックします。
- 最初のオプション「区切り文字別」をクリックします。
- 表示されるダイアログでは、Power Queryがニーズを的確に判断してくれるので、特に操作する必要はありません。「区切り記号の出現箇所ごとに分割」セクション（図I）で、Power Queryが「区切り記号の出現ごとに分割」を選択していることを確認してください。
図I
- [OK]をクリックすると、図 Jの結果が表示されます。
図J
このオプションは、区切り文字を使用して各文字列を 3 つの列に分割し、各セクションの開始位置と終了位置を決定します。
ご覧のとおり、ExtractとSplit Columnはどちらもデータを素早く分割するのに役立ちます。どちらも頻繁に使用することになるでしょう。
