【Excel】Power Queryで「列の分割」を条件付きで行う!Excel文字位置指定の分割テクニック

Excelで大量のデータを扱う際、特定の文字位置で列を分割したい場面があります。しかし、単純な区切り文字での分割では対応できない場合があります。このような場合に役立つのがPower Queryの機能です。この記事では、Power Queryを使って文字位置を指定して列を分割する方法を解説します。これにより、複雑なデータ整形作業を効率化できます。

Power Queryの「列の分割」機能は、区切り文字だけでなく、指定した文字数や範囲で列を分割することも可能です。さらに、特定の条件を満たす行のみに分割を適用するといった、より高度な処理も実現できます。本記事を読めば、Excelでのデータ加工の幅が大きく広がります。

Power Queryの「列の分割」機能の概要

Power Queryの「列の分割」機能は、既存の列を複数の列に分割するための強力なツールです。この機能は、区切り文字(カンマ、タブなど)を指定して分割するだけでなく、文字数や特定のパターンに基づいて分割することもできます。これにより、Excelの標準機能では難しい、柔軟なデータ整形が可能になります。特に、データが一定の規則性を持っているものの、単純な区切り文字では分割できない場合に威力を発揮します。

この機能を使うことで、例えば「氏名」列を「姓」と「名」に分けたり、「住所」列を「都道府県」「市区町村」「番地」などに細分化したりすることが容易になります。また、処理が自動化されるため、手作業によるミスを減らし、作業時間を大幅に短縮できる点も大きなメリットです。

文字位置を指定して列を分割する仕組み

Power Queryで文字位置を指定して列を分割する基本的な仕組みは、テキスト操作関数を組み合わせることです。主に「テキストの分割」機能の「区切り記号による分割」ではなく、「指定した文字数ごと」や「指定した位置」といったオプションを利用します。さらに、条件分岐を伴う分割を行う場合は、「カスタム列の追加」機能でM言語(Power Queryの内部言語)を記述して、より複雑なロジックを実装します。

具体的には、テキストの先頭から指定した文字数で分割したり、テキストの末尾から指定した文字数で分割したりできます。あるいは、テキストの中央付近にある特定の文字位置を基準に分割することも可能です。これらの操作をPower Queryエディター上で行うことで、再利用可能なクエリとして保存し、データの更新時に自動的に適用させることができます。

Power Queryで文字位置を指定して列を分割する手順

Power Queryを使用して、指定した文字位置で列を分割する手順を解説します。ここでは、例として「商品コード」列を、最初の5文字とそれ以降の文字に分割するケースを想定します。

1. Excelでデータを準備する
   Excelシートに分割したいデータが含まれる列を用意します。例として、A列に「商品コード」があるとします。
2. Power Queryエディターを開く
   Excelのリボンメニューから「データ」タブを選択し、「データの取得と変換」グループにある「テーブルまたは範囲から」をクリックします。これにより、Power Queryエディターが開きます。
3. 「列の分割」機能を選択する
   Power Queryエディターで、分割したい列(例: 商品コード列)を選択します。次に、「ホーム」タブまたは「列の追加」タブにある「列の分割」をクリックし、ドロップダウンメニューから「指定した文字数ごと」を選択します。
4. 分割位置を指定する
   「列の分割」ダイアログボックスが表示されます。「分割位置」で「左から」を選択し、数値を「5」と入力します。これにより、左端から5文字目で分割されます。必要に応じて「右から」を選択したり、分割する列を複数指定したりすることも可能です。
5. 分割を実行する
   「OK」をクリックすると、指定した文字数で列が分割されます。元の列は指定した文字数ごとに新しい列に分割されます。
6. 結果をExcelに読み込む
   「ホーム」タブの「閉じて読み込む」をクリックして、処理結果をExcelシートに新しいテーブルとして読み込みます。

条件付きで列を分割するテクニック

Power Queryでは、単に文字位置で分割するだけでなく、特定の条件を満たす行に対してのみ分割を適用したり、分割後の列名を条件によって変えたりといった、より高度な処理が可能です。ここでは、カスタム列を追加して条件分岐を行う方法を解説します。

条件に基づいて分割を適用する

例えば、「商品コード」の長さが5文字以上の場合は最初の5文字とそれ以降に分割し、5文字未満の場合はそのままの文字列を保持したい場合を考えます。この場合、「カスタム列の追加」機能を使用します。

1. 「カスタム列の追加」を選択する
   Power Queryエディターで、「列の追加」タブを選択し、「カスタム列」をクリックします。
2. M言語で条件分岐を記述する
   「カスタム列」ダイアログボックスで、新しい列名(例: 「分割後商品コード」)を入力します。カスタム列の数式欄に、以下のようなM言語を記述します。

   if Text.Length([商品コード]) >= 5 then Text.Start([商品コード], 5) else [商品コード]

   この数式は、「商品コード」列の文字数が5文字以上であれば、その最初の5文字を取得し、そうでなければ元の「商品コード」をそのまま使用するという意味です。

3. 条件に基づいて分割した列を追加する
   「OK」をクリックすると、条件に基づいて分割された新しい列が追加されます。
4. 必要に応じてさらに分割する
   この新しい列をさらに「指定した文字数ごと」で分割するなど、必要に応じて追加の処理を行います。

分割後の列名を条件によって変更する

分割した列のヘッダー名を、元のデータや分割条件によって動的に変更したい場合も、「カスタム列の追加」機能でM言語を記述します。例えば、商品コードの最初の文字が「A」で始まる場合は「コードA」、それ以外の場合は「コードB」といった具合です。

1. 「カスタム列の追加」を選択する
   「列の追加」タブから「カスタム列」を選択します。
2. M言語で列名分岐を記述する
   新しい列名(例: 「コード種類」)を入力し、数式欄に以下のようなM言語を記述します。

   if Text.StartsWith([商品コード], "A") then "コードA" else "コードB"

   これは、「商品コード」が「A」で始まれば「コードA」という文字列を、そうでなければ「コードB」という文字列を新しい列の値として設定します。

3. 列名を設定する
   この値に基づいて、後続の処理で分割した列のヘッダー名を変更する、あるいはこの列自体をヘッダー名として使用するなどの応用が可能です。

Power Queryで列を分割する際の注意点

Power Queryで列を分割する際は、いくつかの注意点があります。これらの点に留意することで、予期せぬエラーや意図しない結果を防ぐことができます。

データの一貫性を確認する

文字位置による分割は、データが一定のフォーマットに従っていることを前提としています。もし、分割したい列のデータ形式が不均一な場合、エラーが発生したり、意図しない分割結果になったりする可能性があります。例えば、商品コードの長さがバラバラであったり、不要な文字が含まれていたりすると、分割位置がずれてしまいます。事前にデータのクリーニングを行い、一貫したフォーマットになっているか確認することが重要です。

分割後のデータ型に注意する

列を分割すると、新しい列が作成されます。これらの新しい列のデータ型が、意図した型(数値、テキスト、日付など)になっているか確認しましょう。Power Queryは自動的にデータ型を推測しますが、常に正しいとは限りません。特に、数字に見える文字列(郵便番号など)がテキスト型として扱われるべき場合、数値型に変換されてしまうと先頭のゼロが消えるといった問題が発生する可能性があります。必要に応じて、分割後にデータ型を明示的に変更してください。

エラー処理を考慮する

分割処理中にエラーが発生した場合、その行の処理が停止したり、エラー値が表示されたりすることがあります。例えば、指定した文字数よりも短い文字列に対して分割を試みた場合などに発生しやすいです。このようなエラーを回避するためには、M言語の `try…otherwise` 構文などを用いて、エラーが発生した場合の代替処理を定義しておくことが有効です。これにより、データ全体としての処理を継続させることができます。

Excel 2019以前のバージョンとの互換性

Power QueryはExcel 2016以降に標準搭載されていますが、Excel 2019以前のバージョンでは、アドインとして追加インストールが必要な場合があります。また、一部の高度な機能やUIの表示が、バージョンによって若干異なる可能性があります。基本的な「列の分割」機能は多くのバージョンで利用可能ですが、複雑な条件分岐などを実装する際は、使用しているExcelのバージョンを確認し、必要であれば最新版へのアップデートを検討すると良いでしょう。

Power QueryとExcel標準機能の比較

Power Queryの「列の分割」機能は、Excelの標準機能と比較して、どのような違いがあるのでしょうか。それぞれの特徴を理解することで、適切なツールを選択できるようになります。

項目	Power Query (列の分割)	Excel標準機能 (区切り位置指定ウィザード)
分割方法	区切り文字、指定文字数、指定位置、正規表現	区切り文字、固定長
条件分岐	カスタム列でM言語により柔軟に実装可能	直接的な条件分岐機能はない
自動化・再利用性	クエリとして保存でき、データ更新時に自動実行可能	手作業での再実行が必要
データ量への対応	大量データでも比較的効率的に処理	データ量が多いと動作が重くなる傾向
学習コスト	M言語を習得すると高度な処理が可能	直感的な操作で比較的容易

Power Queryは、一度設定した処理をクエリとして保存し、データの更新時に自動実行できる点が大きな強みです。これにより、定型的なデータ加工業務を大幅に効率化できます。一方、Excelの「区切り位置指定ウィザード」は、一時的なデータ整形や、簡単な区切り文字・固定長での分割には手軽で便利です。しかし、複雑な条件処理や繰り返し行う作業には向いていません。

まとめ

この記事では、Power Queryを使用して文字位置を指定し、条件付きで列を分割するテクニックを解説しました。Power Queryの「列の分割」機能と「カスタム列の追加」を組み合わせることで、Excelの標準機能では難しい、柔軟で高度なデータ整形が可能になります。これにより、データ加工にかかる時間と手間を大幅に削減できます。

今回学んだPower Queryの列分割テクニックを活用して、日々のデータ処理業務を効率化しましょう。さらに、Power Queryの他の機能(データの結合、グループ化、ピボット解除など)を習得することで、より複雑なデータ分析やレポート作成もスムーズに行えるようになります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】