エクセルのPower Queryはこんな時も便利！ クロス表を“使えるデータ”に分解

見やすい表が扱いやすいデータではない

　Excelで作成する表には、行と列の見出しを用意して、それぞれの交差する位置の値に着目する“クロス表”があります。例えば、以下のような営業案件の管理表を「チームとエリアごとにまとめて入力する」といった形式です。各セルには複数の企業名が改行されて入力されており、資料としては見やすく、直感的に状況を把握できます。

営業先のリストをチームとエリアに分けて整理した表の例

　しかし、このままでは案件数の集計や、担当者別の分析、さらに、他のツールとの連携といった処理がうまく行えません。Excelのフィルターや関数を使おうとしても、1セルに複数の値が入っていることが大きな障壁になります。

　「見やすい表」と「処理しやすいデータ」は別物であり、データを“分解”して扱う必要があるわけです。今回は、1つのセルに複数の値が入力されたクロス表を例に、Excelでデータを扱いやすい形式へ変換する方法を紹介します。

1つのセルの分解にはTEXTSPLIT関数が使える

　1つのセルに入力された複数のデータを分解するだけなら、以前に紹介したTEXTSPLIT関数が利用できます。改行コードを区切り文字として指定して、セル内のデータを横または縦に展開できます。例えば、セル内で改行されている企業名を縦に分解する場合は、次のように指定します。

TEXTSPLIT関数を利用して、セルB2の改行位置で分解した例。CHAR(10)は改行を意味する

　ただし、今回のように表全体のセルに複数の改行が含まれている場合、すべてのセルに対して個別に処理を行うのは現実的ではありません。

　また、別の方法として、セルの内容をテキストエディターなどにコピーし、改行位置を整形してExcelに貼り付け直す処理もありますが、「複数の列」と「複数の行」にまたがるクロス表では困難です。エリアや担当者といった情報を維持しながら分解する必要があるため、単純なテキスト処理では対応しきれません。

　結果として、手作業による分解はミスが発生しやすく、データ量が増えるほど現実的ではなくなります。

Power Queryで表全体を一括変換する

　このようなケースでは、Power Queryを使ってデータを変換するのが有効です。表全体を対象にして「列の展開」や「行の分割」といった処理を一括で行えます。今回は、エリアを基準にして、チームと営業先のリストを行方向に分割します。

表全体を選択して、［データ］タブにある［テーブルまたは範囲から］をクリックする

自動的にテーブルに変換されるので、見出し行として指定されるセル範囲が正しいことを確認する。［先頭行をテーブルの見出しとして使用する］にチェックを付けて［OK］をクリックする

Power Queryが新しいウィンドウで表示される

元の表の列見出し（「エリア」列）を選択して［変換］の［列のピボット解除］－［その他の列のピボット解除］を選択する

エリアに対応するチームと営業先に分割された。現段階ではチーム名が「属性」、営業先が「値」となっている

数式バーの値を直接編集して「チーム」と「営業先」に書き換えて［Enter］キーを押す

見出しを変更できた。「営業先」列を選択しておく

［列の分割］－［区切り記号による分割］を選択する

［カスタム］が選択され、「#(lf)」と入力されていることを確認する。他の項目が選択されている場合は、後述の方法で修正する

分割の方向は［行］を選択する。1つのセルに入力されている営業先を改行の位置で縦に分割するという意味。［OK］をクリックする

「営業先」列が分割された。［ホーム］タブの［閉じて読み込む］をクリックする

新しいテーブルとして読み込まれる。エリアとチーム、営業先が1つの行ごとに整えられたので、並べ替えや絞り込みができるようになった

　上記の手順で「#(lf)」と表示されていない場合は、［カスタム］を選択して、［特殊文字を使用して分割］にチェックを付けます。［特殊文字を挿入］の一覧から［改行］を選択します。「#(lf)」と表示されていることを確認してください。余計な空白が含まれていると正しく動作しないので注意してください。

［カスタム］を選択して、［特殊文字を使用して分割］にチェックを付ける。［特殊文字を挿入］の一覧から［改行］を選択する

　「見る」ためには優れていますが、「処理する」には不向きな構造の表でも、Power Queryを活用すればギャップを簡単に埋めることができます。分析や集計を前提としたデータ管理を行う場合は、こうした変換テクニックを押さえておくと安心です。