今回は、ExcelのPower Queryで列を分割してデータを整える方法を紹介します。
列の分割が役立つ場面
ExcelでCSVやシステム出力データを扱うと、1つのセルに複数の情報が入っていることがあります。たとえば「部署名-社員番号」「姓
名」「商品コード_色_サイズ」「住所の都道府県と市区町村」などです。このような列は、そのままだと集計や検索がしにくくなります。
Excel Power Query
列の分割を使うと、区切り文字や文字数を手がかりにして、1つの列を複数の列へ分けられます。通常のワークシート関数でも分割できますが、Power
Queryなら同じ手順を更新で繰り返せるため、毎月届くデータや定期的な集計に向いています。
たとえば、商品コードが「A-100-RED」のような形なら、ハイフンで分割してカテゴリ、番号、色を別列にできます。後でカテゴリ別に集計したり、色だけで絞り込んだりしやすくなります。
分割前に確認すること
列を分割する前に、元データのルールを確認します。見た目では同じように見えても、区切り文字が半角だったり全角だったり、空白の数が違ったりすることがあります。ルールを把握せずに分割すると、行によって列の位置がずれる場合があります。
- 区切り文字が統一されているか
- 空白が余分に入っていないか
- 分割後に必要な列数が決まっているか
- 空欄や例外行が含まれていないか
- 元の列を残す必要があるか
「姓 名」のように空白で分割する場合、姓や名の中に空白が入るデータがないか確認します。住所のように表記の幅が広いデータは、単純な区切りだけではきれいに分けられないことがあります。
元データの列は残しておく
分割後に結果を確認するため、最初は元の列を残しておくと安心です。Power
Queryでは列を複製してから分割できます。元列と分割後の列を見比べながら確認すれば、分割ミスに気づきやすくなります。
確認後に不要であれば、最終的な出力から元列を削除します。作業途中で消してしまうと、分割のやり直しやチェックがしにくくなります。
Power Queryで列を分割する流れ
Power Queryエディターでは、対象列を選んで「列の分割」を使います。区切り文字で分ける方法がよく使われますが、文字数、位置、大文字小文字の変化など、データに応じた分割方法があります。
- Excelの「データ」タブから対象データをPower Queryに読み込む
- Power Queryエディターで分割したい列を選ぶ
- 「列の分割」を選択する
- 区切り文字や文字数などの条件を指定する
- 分割結果の列名を変更する
- データ型を設定する
- 必要に応じて不要な列を削除する
区切り文字で分割する場合は、ハイフン、カンマ、スラッシュ、スペースなどを指定します。複数回出てくる区切り文字をすべて分割するのか、最初の1回だけ分割するのかも確認します。
列名は分割後すぐに変える
Power Queryでは、分割後の列名が「列名.1」「列名.2」のようになることがあります。このままだと後の手順で意味が分かりにくくなります。分割した直後に「カテゴリ」「商品番号」「色」など、内容が分かる列名に変更します。
列名を早めに整えると、後でフィルターや集計をするときに迷いません。複数人で使うクエリでは、列名の分かりやすさが保守のしやすさにつながります。
区切り文字で分割するときのコツ
区切り文字で分割する場合は、区切り文字そのものがデータ内に別の意味で使われていないか確認します。商品名や住所にハイフンが入ることもあります。区切り文字がデータ内に複数の意味で使われていると、意図しない場所で分割されます。
- 区切り文字の種類を確認する
- 最初の区切りだけ使うか、すべて使うか決める
- 分割後の列数が行ごとにずれていないか見る
- 空欄の行を確認する
- 分割後に不要な空白を削除する
空白で分割する場合は、前後の余分な空白を取り除く処理も合わせて使うと整いやすくなります。Power
Queryには文字列のトリミング機能があるため、分割前または分割後に空白を整理できます。
分割しすぎた列は結合で戻せる
分割の指定を誤ると、必要以上に列が増えることがあります。Power
Queryでは適用したステップを削除してやり直せます。また、分割後に必要な列を結合して整えることもできます。
ただし、やり直しが多い場合は、元データのルール確認が足りない可能性があります。数件だけで判断せず、先頭、中間、末尾の行を見て、例外がないか確認します。
文字数で分割する方法
コードや固定長データでは、区切り文字ではなく文字数で分割するほうが向いています。たとえば先頭2文字が地域、次の4文字が番号、末尾が枝番のようなルールです。
文字数で分割する場合は、全角と半角の扱い、桁数不足、空欄を確認します。コード列は数値として扱うと先頭のゼロが消えることがあるため、文字列として読み込むのが基本です。
- 固定桁のルールを確認する
- 先頭ゼロを残す
- 文字列型として扱う
- 桁数が足りない行を確認する
固定長の分割は、ルールがそろっていれば安定します。一方で、入力ミスがある行は分割位置がずれるため、分割後にエラーや空欄を確認します。
分割後のデータを活用する
列を分割した後は、集計や抽出に使いやすくなります。商品コードからカテゴリを取り出せば、カテゴリ別売上の集計に使えます。氏名を姓と名に分ければ、宛名や並べ替えの処理がしやすくなります。
Power Queryで整えた結果は、Excelテーブルとして読み込み、ピボットテーブルやグラフの元データにできます。分割手順がクエリに残るため、元ファイルを差し替えて更新しても同じ処理を再利用できます。
列の分割は、データを使いやすい形に直すための前処理です。分割すること自体が目的にならないように、後で何に使うかを考えて列を設計します。
まとめ
ExcelのPower Queryで列を分割すると、1つのセルに入った複数の情報を整理できます。区切り文字、文字数、位置など、元データのルールに合わせて方法を選ぶことが大切です。
分割前には、区切り文字の統一、空白、例外行、列数のずれを確認します。元列を複製してから分割すれば、結果を見比べながらチェックできます。
Excel Power Query
列の分割を使いこなすと、CSVやシステム出力データを集計しやすい形に整えられます。分割後は列名とデータ型を整え、更新しやすいクエリとして管理しましょう。