Power Queryで区切り記号で区切られたデータを別々のセルへ表示

1つのセルに複数のデータが入力されている場合は、それぞれのデータを分割して表示しないとデータベースにはなりません。

Power Queryの［列の分割］機能を使用すると、1つのセルのデータを簡単に分割することができます。

ここでは、セル内改行されたセルと、スペースで区切られたデータのセルを［列の分割］を使って別々のセルへ表示します。

また、分割後は列方向に並べるか、横方向に並べるかも選択できます。

参考Power Queryは、Excel2016以降では標準機能となっていますが、Excel2010とExcel2013はアドインをインストールすることで使用できます。

セル内改行されたセルを別々のセルに分割

以下のようにセル内改行されたデータがあります。

このデータを［県名］と［市］の列に分割したいと思います。

参考セル内で改行するには、［Alt］+［Enter］を使用します。セル内改行については、以下の記事で解説しています。

セル内改行を解除する関数なども紹介しています。

Power Queryエディターの［列の分割］

表内のセルを選択して、［データ］タブの［データの取得と変換］グループにある［テーブルまたは範囲から］をクリックします。

［テーブルの作成］ダイアログボックスが表示されますので、［先頭行をテーブルの見出しとして使用する］をオンにして、範囲を確認して［OK］ボタンをクリックします。

Power Queryエディターが起動しますので、［ホーム］タブの［変換］グループにある［列の分割］から［区切り記号による分割］をクリックします。

［列の分割］は［変換］タブの［テキストの列］グループにもあります。

［区切り記号による列の分割］ウィンドウが表示されます。

自動で［--カスタム--］が選択されて、改行コードには［#(lf)］が入力されていることを確認します。

区切り記号のコードは、自動で表示されます。

［分割の方向］は既定で［列］が選択されていると思いますので、そのままでOKです。

もし、気になるようであれば［詳細設定オプション］をクリックして展開して確認してみてください。

［OK］ボタンをクリックします。

2つの列に分割されたデータになります。

参考［区切り記号］の選択肢は以下のようになっています。

• コロン
• コンマ
• 等号
• セミコロン
• スペース
• タブ

また、改行コードの［#(lf)］を間違って消してしまった場合や何か不具合で表示されていない場合は、［特殊文字を使用して分割］の［特殊文字を挿入］から［改行］を選択します。

改行コードに［#(lf)］が表示されます。

列名を変更して読み込む

Power Queryエディターで列名を変更します。

列名をダブルクリックすると、カーソルが現れますので新しい名前を入力します。

または、［変換］タブの［任意の列］グループにある［名前の変更］をクリックします。

［名前の変更］は、列名で右クリックしたショートカットメニューにもあります。

参考Power Queryでは、1つのテーブルで同じ名前の列名にすることはできません。同じ名前を付けようとすると、メッセージウィンドウで警告されます。

閉じて次に読み込む

テーブルと同じシートに読み込みたいと思います。

［ホーム］タブの［閉じる］グループにある［閉じて読み込む］から［閉じて次に読み込む］をクリックします。

［データのインポート］ダイアログボックスが表示されます。

［テーブル］と［既存のワークシート］を選択してセルも指定します。そして、［OK］ボタンをクリックします。

以下のようにセル［C1］を基準にデータが読み込まれます。

テーブルを通常のセル範囲に変換

テーブルが不都合な場合は、テーブルを範囲に変換します。

ただし、範囲に変換すると、クエリとの接続は解除されます。接続したままにしておくと、元テーブルに変更があった場合は更新するだけでデータに反映されます。

読み込んだテーブル内のセルをクリックして、［テーブルデザイン］タブの［ツール］グループにある［範囲に変換］をクリックします。

以下のメッセージウィンドウが表示されますので、［OK］ボタンをクリックします。

テーブルでは、自動的にセルに塗りつぶしなどの書式スタイルが設定され、スタイル設定のまま［範囲に変換］を実行すると、スタイルはそのままになります。

スタイルをクリアするには、範囲選択して［ホーム］タブの［編集］グループにある［書式のクリア］をクリックします。

アクセスキーを使用するのであれば、キーボードから［Alt］→［H］→［E］→［F］の順に押します。

以下のように書式スタイルがクリアされます。

参考書式スタイルを残したくなければ、先にテーブルのスタイルをクリアして、［範囲に変換］を実行する方法もあります。

テーブルのスタイルをクリアして［範囲に変換］

テーブルの便利機能などは、以下の記事で解説しています。

ただ、テーブルに変換したら制限されることもあります。

フラッシュフィルを使用しても、セル内改行のデータを別々のセルへ分割することができます。

改行データを行方向に分割

以下のように［氏名］の列のみセル内改行された表があります。

このような表の場合は、Power Queryエディターで行に分割します。

［データ］タブの［データの取得と変換］グループにある［テーブルまたは範囲から］をクリックして、Power Queryエディターで表示します。

以下のように表示されます。

［氏名］列を選択して、［ホーム］タブの［変換］グループにある［列の分割］から［区切り記号による分割］をクリックします。

［区切り記号による列の分割］ウィンドウが表示されます。

［改行コード］は、自動で［#(lf)］が表示されます。

［詳細設定オプション］の［分割の方向］で［行］を選択して、［OK］ボタンをクリックします。

［詳細設定オプション］が展開されていない場合は、クリックすると展開されます。

［部署ID］と［部署名］の列は、分割前の情報が引き継がれます。

［ホーム］タブの［閉じる］グループにある［閉じて読み込む］をクリックすると、新しいシートへ読み込まれます。

シート名はテーブル名になります。

参考既存シートの指定セルに読み込む場合は、［閉じて次に読み込む］をクリックします。

以下の記事では、［コンマ］で分割しています。

スペースによる分割表示

スペースで区切られたデータの場合も列に分割することができます。

以下のように姓と名の間に全角スペースで区切られたデータがあります。

このデータを［姓］と［名］の列に分割します。

［データ］タブの［データの取得と変換］グループにある［テーブルまたは範囲から］をクリックして、Power Queryエディターで表示します。

そして、［ホーム］タブの［変換］グループにある［列の分割］から［区切り記号による分割］をクリックします。

［区切り記号による列の分割］ウィンドウが表示されます。

［改行コード］には、自動で［全角スペース］が表示されています。このまま［OK］ボタンをクリックします。

以下のように2つの列に分割されます。

列名をダブルクリックして、列名を変更します。

［ホーム］タブの［閉じる］グループの［閉じて読み込む］から［閉じて次に読み込む］をクリックします。

区切り記号が混在している場合

データに全角スペースと半角スペースが混在している場合もあると思います。

その場合は、［列の分割］を実行する前に［値の置換］でどちらかに統一します。

表内のセルを選択して、［データ］タブの［データの取得と変換］グループにある［テーブルまたは範囲から］をクリックして、Power Queryエディターを表示します。

［変換］タブの［任意の列］グループにある［値の置換］をクリックします。

または、ショートカットメニューから［値の置換］をクリックします。

［値の置換］ウィンドウが表示されます。

ここでは、［半角スペース］を［全角スペース］に変換します。

全角スペースは、半角スペース2個では認識されませんので気をつけてください。

［検索する値］に［半角スペース］、［置換後］に［全角スペース］を入力して［OK］ボタンをクリックします。

以下のように［全角スペース］に統一されますので、後は［全角スペース］を区切り記号として分割します。

Power Queryについて学習するなら、以下のテキストがお勧めです。

参考氏名を姓と名に分ける方法は、［区切り位置指定ウィザード］を使用する方法もあります。

関数を使う方法は、以下の記事で解説しています。