【Excel】Power Queryで列を分割する方法!Excelの区切り文字や文字数で自動分割する手順

Excelで大量のデータを扱う際、1つの列に複数の情報が混在していると、分析や集計が困難になることがあります。この問題を解決するために、Power Queryを使って列を分割する方法を知りたい方が多いでしょう。Power Queryを使えば、区切り文字や文字数に基づいて列を自動的に分割できます。この記事では、Power Queryで列を分割する具体的な手順を解説します。

Power QueryはExcelに標準搭載されている強力なデータ整形ツールです。このツールを活用することで、手作業では時間のかかる複雑なデータ編集作業を効率化できます。特に、列の分割はデータ分析の前処理として非常に頻繁に行われる操作です。この記事を読めば、Power Queryを使って自在に列を分割できるようになります。

Power Queryで列を分割する仕組み

Power Queryは、データの取得から整形、変換までを一貫して行える機能です。列の分割は、Power Queryエディター上で実行される「ステップ」として記録されます。このステップは、元のデータが変更されても自動的に適用されるため、データの更新時に手作業で再編集する必要がありません。Power Queryは、テーブル形式のデータを解析し、指定された条件に基づいて新しい列を作成または既存の列を置き換えます。

Power Queryで列を分割する手順

Power Queryを使って列を分割するには、主に「区切り記号による分割」と「文字数による分割」の2つの方法があります。どちらの方法も、Power Queryエディターの「列の分割」機能から実行できます。

1. Excelでデータを準備する
   まず、分割したい列を含むExcelファイルを準備します。データをテーブル形式に変換しておくと、Power Queryでの取り込みがスムーズになります。Excelメニューの「挿入」タブから「テーブル」を選択し、データ範囲を指定して「OK」をクリックします。
2. Power Queryエディターを開く
   Excelで「データ」タブをクリックし、「データの取得と変換」グループにある「テーブルまたは範囲から」を選択します。これにより、Power Queryエディターが開きます。
3. 分割したい列を選択する
   Power Queryエディターのプレビュー画面で、分割したい列ヘッダーをクリックして選択します。
4. 「列の分割」機能を選択する
   「ホーム」タブまたは「列の分割」タブにある「列の分割」ボタンをクリックします。ドロップダウンメニューが表示されるので、分割方法を選択します。

1. 区切り記号による列の分割手順

この方法は、特定の文字(例: カンマ、スペース、タブ、コロンなど)を区切りとして列を分割したい場合に有効です。例えば、「氏名,部署名」のような形式の列を「氏名」と「部署名」に分けたいときに使用します。

1. 「区切り記号による分割」を選択する
   「列の分割」ドロップダウンメニューから「区切り記号による分割」を選びます。
2. 区切り記号を指定する
   「区切り記号で分割」ダイアログボックスが表示されます。「区切り記号」のドロップダウンリストから、使用したい区切り記号を選択します。一般的な区切り記号(カンマ、スペース、タブ、セミコロン、コロン)はプリセットされています。もしリストにない記号を使いたい場合は、「カスタム」を選択して直接入力します。
3. 分割方法を選択する
   「左端の区切り記号」「右端の区切り記号」「各区切り記号ごと」のいずれかを選択します。通常は「各区切り記号ごと」を選択して、全ての区切り文字で分割します。
4. 分割オプションを設定する
   「詳細オプション」を展開すると、「指定した回数で分割」というオプションがあります。例えば、最初の区切り記号だけで分割したい場合に「1」を指定します。通常は、このオプションは変更せず、そのまま「OK」をクリックします。
5. 結果を確認する
   指定した区切り記号に基づいて、元の列が新しい複数の列に分割されます。新しい列には自動的に「列名.1」「列名.2」のような名前が付けられます。
6. 列名を変更する
   必要に応じて、新しい列ヘッダーをダブルクリックして、分かりやすい名前に変更します。
7. データ型を変換する
   分割された列のデータ型が意図したものと異なる場合(例: 数値がテキストになっている)、列ヘッダーの左側にあるアイコンをクリックして、適切なデータ型(整数、10進数、テキストなど)に変更します。

2. 文字数による列の分割手順

この方法は、固定の文字数で列を分割したい場合に便利です。例えば、商品コードの最初の5文字と残りの部分を分けたい場合などに使います。この機能は、Excel 2019以降のバージョンで利用可能です。Excel 2016以前では、この機能は提供されていません。

1. 「文字数による分割」を選択する
   「列の分割」ドロップダウンメニューから「文字数による分割」を選びます。
2. 分割方法を選択する
   「開始位置」または「終了位置」で分割するかを選択します。「開始位置」は、指定した文字数から分割を開始し、それ以降を新しい列にします。「終了位置」は、指定した文字数までを1つの列とし、それ以降を次の列にします。通常は「開始位置」で、固定長で区切ります。
3. 文字数を指定する
   「文字数」の入力フィールドに、分割したい文字数を半角数字で入力します。例えば、最初の3文字で分割したい場合は「3」と入力します。
4. 分割を実行する
   「OK」をクリックすると、指定した文字数で列が分割されます。
5. 結果を確認・修正する
   分割された列のヘッダー名を分かりやすく変更し、必要であればデータ型を修正します。

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

Power Queryでの列分割は非常に便利ですが、いくつかの注意点があります。これらの点に留意することで、より正確で効率的なデータ整形が可能になります。

区切り記号が複数存在する場合の挙動

「区切り記号による分割」で「各区切り記号ごと」を選択した場合、指定した区切り記号が複数回現れると、その都度列が分割されます。例えば、「A,B,C」というテキストをカンマで分割すると、「A」「B」「C」の3つの列になります。もし「A,B」だけを分割し、「C」はそのまま残したい場合は、分割回数を指定するオプション(「詳細オプション」→「指定した回数で分割」)を適切に設定する必要があります。

指定した区切り記号が存在しない場合

分割したい列に指定した区切り記号が存在しない場合、その行は分割されず、元の列の内容がそのまま新しい列にコピーされます。これは意図した動作かもしれませんが、データにばらつきがある場合は確認が必要です。

文字数による分割の制限

「文字数による分割」は、固定長での分割に特化しています。もし、分割したい文字列の長さが一定でない場合、意図した通りに分割できない可能性があります。そのような場合は、「区切り記号による分割」や、より高度なカスタム列の追加機能(M言語)を使うことを検討する必要があります。

Excelのバージョンによる機能の違い

前述の通り、「文字数による分割」機能は、Excel 2019以降で利用可能です。Excel 2016以前のバージョンでは、この機能は提供されていません。古いバージョンのExcelを使用している場合は、代替手段として「テキストを列に分割」機能(Excelの標準機能)や、Power Queryのカスタム列機能(M言語)を使って同様の処理を実装する必要があります。

重複した列名の扱い

分割によって生成された列名が元の列名と重複する場合、Power Queryは自動的に連番などを付与して区別しようとしますが、予期せぬ名前になることがあります。分割後は、必ず列名を確認し、必要であれば分かりやすい名前に変更することが重要です。

Power Queryの「カスタム列の追加」機能による応用

Power Queryには、「カスタム列の追加」という機能もあります。これは、数式(M言語)を使って新しい列を作成する機能で、より複雑な条件で列を分割したい場合に強力な手段となります。例えば、特定の文字列を含む部分だけを抽出したい、複数の条件を組み合わせて分割したい、といった高度な要求に対応できます。

例えば、「氏名」と「部署名」が「氏名(部署名)」のように混在している場合、括弧で囲まれた部分だけを抽出したいとします。この場合、「カスタム列の追加」で以下のような数式を入力できます。

数式例:

Text.BetweenDelimiters([元の列名], "(", ")")

この数式は、指定した開始区切り文字(ここでは開き括弧)と終了区切り文字(ここでは閉じ括弧)の間にあるテキストを抽出します。このように、カスタム列を使えば、標準の「列の分割」機能では難しい柔軟なデータ操作が可能になります。

Power Queryで列を分割するメリット

Power Queryで列を分割することには、多くのメリットがあります。これらのメリットを理解することで、データ整形作業の効率を大幅に向上させることができます。

1. 手作業の削減と時間短縮

Excelの標準機能で列を分割する場合、手作業でコピー&ペーストを繰り返したり、「区切り位置指定ウィザード」を何度も実行したりする必要があります。Power Queryを使えば、一度設定した分割処理は、データの更新時に自動で適用されるため、手作業が劇的に削減され、大幅な時間短縮につながります。

2. 一貫性と正確性の向上

手作業でのデータ編集は、ヒューマンエラーが発生しやすく、一貫性を保つのが難しい場合があります。Power Queryは、定義されたステップに基づいて処理を実行するため、常に同じ結果が得られ、データの一貫性と正確性が向上します。

3. 複雑なデータ整形への対応

Power Queryは、単なる列分割だけでなく、データのクリーニング、結合、ピボット解除など、多岐にわたるデータ整形機能を提供します。これらの機能を組み合わせることで、複雑なデータ構造を持つデータソースも、分析に適した形に効率的に変換できます。

4. データ更新への柔軟性

元のExcelファイルや外部データソースが更新された場合でも、Power Queryの「更新」ボタンをクリックするだけで、一連の整形処理が自動的に再実行されます。これにより、常に最新のデータで分析を進めることができます。

まとめ

Power Queryを使えば、Excelのデータを区切り文字や文字数に基づいて効率的に列分割できます。これにより、手作業によるミスを減らし、データ整形にかかる時間を大幅に短縮できます。今回解説した「区切り記号による分割」や「文字数による分割」の手順をマスターすることで、より高度なデータ分析の前準備が容易になります。さらに、複雑な分割が必要な場合は、「カスタム列の追加」機能を活用しましょう。Power Queryの列分割機能を使いこなし、データ分析の効率を一層高めてください。

📊

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

🏆

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