複数の列を1列に集約できる「データ型の作成」

今回は「データ型の作成」というコマンドを使って、複数の列に記録されているデータを1列に集約する方法を紹介していこう。この機能は「カスタムデータ型」と呼ばれているもので、通常とは異なる、独特なデータの管理方法となる。少し使いづらい部分もあるが、こういった機能があることを知っておいても損はないだろう。

複数の列を1列に集約できる「データ型の作成」

カスタムデータ型の作成手順

Excelの各セルには、データを1個ずつ記録していくのが基本となる。一方、これから紹介する方法を利用すると、各セルに「複数のデータ」を記録することが可能となる。かなり独特なデータの管理方法になるが、少ない列数で多くのデータを管理したい場合に活用できるので、こういった使い方があることも覚えておくと役に立つだろう。

今回は、以下の図に示した会員名簿を例に、解説を進める。このデータ表には、各会員の氏名、生年月日、性別、メールアドレス、電話番号、住所のデータが記録されている。

「Power Query エディター」に取得した会員名簿

住所に関連するデータは、郵便番号/都道府県/市区町村/住所1/住所2という具合に5列にわたって記録されている。これらのデータを「カスタムデータ型」に変換して、1列に集約する方法を紹介する。

住所に関連するデータ

まずは集約する列を同時に選択する。今回の例の場合、「郵便番号」の列を選択し、Shiftキーを押しながら「住所2」の列名をクリックすればよい。これで「郵便番号」〜「住所2」の列を選択できる。続いて、「変換」タブにある「データ型の作成」をクリックする。

集約する列の選択

「データ型の作成」コマンド

以下の図のような設定画面が表示される。「データ型の名前」には、集約後の列名を自由に入力すればよい。「列の表示」には「どの列のデータを代表値として表示するか?」を指定する。今回の例では「都道府県」の列を代表値として表示することにした。すべて指定できたら「OK」ボタンをクリックする。

データ型の表示方法の指定

選択していた5つの列が1列に集約され、その代表値として「都道府県」のデータが表示される。また、列名の左側に「カスタムデータ型」(リンクされたデータ型)であることを示す「2重菱形」のアイコンが表示される。

1列に集約されたデータ

これで「Power Query エディター」での作業は完了。「閉じて読み込む」をクリックして、このデータ表をExcelに出力した様子も紹介しておこう。Excelに出力したデータ表は以下の図のように表示されている。現時点では「住所」の列に都道府県のデータしかないように見えるが、実際には各セルに5個ずつデータが記録されている。

Excelに出力したデータ表

カスタムデータ型のExcel操作

続いては、Excelでカスタムデータ型を扱うときの操作手順を紹介していこう。最初に紹介するのは、各セルに記録されているデータの閲覧方法だ。

各セルに表示されている「2重菱形」のアイコンをクリックすると、そのセルに記録されているデータが「カード」として表示される。今回の例の場合、郵便番号/都道府県/市区町村/住所1/住所2とった5つのデータが表示されるのを確認できるだろう。

カードを表示した様子

これらのデータを「独立した列」として追加表示することも可能だ。この場合は、以下の図に示したアイコンをクリックし、列として表示したいデータを選択すればよい。たとえば「市区町村」のデータを選択すると、表の右端に列が追加され、そこに「市区町村」のデータが表示されるようになる。

列の追加(フィールドの抽出)

追加された列(1)

同様の手順を繰り返して、別のデータを「列」として表示することも可能だ。以下の図は、「市区町村」に加えて「住所1」のデータを追加表示した例だ。

追加された列(2)

このようにカスタムデータ型を使うと、ひとつの列に「複数のデータ」を記録できるようになる。各セルに記録されているデータ群は、必要に応じて「列」として展開することも可能である。こうすることで、少ない列数で多くのデータを管理できるようになる。

なお、展開された列は「該当データを参照するリンク」となっていることに注意する必要がある。このため、参照元の列(カスタムデータ型の列)を削除してしまうと、展開された列は「#FIELD!」のエラーになってしまう。

「住所」の列を削除した場合

先ほど示した例の場合、「住所」の列(カスタムデータ型)から特定のフィールドを抽出することにより「市区町村」や「住所1」のデータを表示していたことになる。当然ながら、抽出元の「住所」の列を削除すると、データを正しく抽出できなくなりエラーが発生してしまう。

つまり、展開された列には「データ」ではなく「リンク」が記録されている訳だ。カスタムデータ型を利用するときは、こういった点にも注意しながら作業を進めていく必要がある。

カスタムデータ型の入れ子

複数の列を集約したカスタムデータ型を、さらに「データ型の作成」で集約することも可能だ。続いては、カスタムデータ型の入れ子について紹介していこう。

たとえば、先ほど示した例とは別に、メールアドレス/電話番号/携帯電話番号の3列を集約する処理を行ったとしよう。集約後の列名は「メール・電話」とし、代表値として「メールアドレス」を表示するように指定した。

カスタムデータ型の作成

カスタムデータ型に変換した列

これで「メール・電話」と「住所」の列が、それぞれカスタムデータ型として扱われるようになる。これらふたつの列を選択し、さらに「データ型の作成」を実行すると、階層構造のあるカスタムデータ型を作成できる。

さらにカスタムデータ型を作成

上図のように設定した場合、「メール・電話」と「住所」の2列が「連絡先」という列に集約され、その代表値として「メール・電話」のデータが表示されるようになる。なお、「メール・電話」の列もカスタムデータ型となるため、実際には、その代表値である「メールアドレス」のデータが表示されることになる。

入れ子にしたカスタムデータ型の列

「閉じて読み込む」をクリックして、このデータ表をExcelに出力した様子も紹介しておこう。「連絡先」の列にある「2重菱形」のアイコンをクリックすると、「メール・電話」と「住所」のふたつのカスタムデータ型がカード内に表示される。各データを閲覧するときは、このカード内にある「2重菱形」のアイコンをクリックすればよい。