今回は「データ型の作成」というコマンドを使って、複数の列に記録されているデータを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重菱形」のアイコンをクリックすればよい。

  • カードを表示した様子(1)

すると、そのカスタムデータ型に記録されているデータが一覧表示される。なお、このカードの左上にある「←」をクリックすると、カードを元の表示(親レベルの表示)に戻すことができる。

  • カードを表示した様子(2)

このように、カスタムデータ型を入れ子にした場合は、(親)→(子)と階層を追ってデータを展開していく必要がある。

各データを「列」として追加表示するときも同様だ。まずは、子レベルのカスタムデータ型を「列」として追加表示する。

  • 列の追加(1)

続いて、追加表示された列を選択し、その中に記録されているデータを「列」として追加表示する。

  • 列の追加(2)

このように2段階の展開を行うことで、各データを「列」として表示することが可能になる。

  • 展開された「携帯電話番号」のデータ

上図に示した例の場合、「連絡先」→「メール・電話」→「携帯電話番号」という形でデータを展開したことになる。

このようにカスタムデータ型を入れ子にすることで、さらに列数を少なくすることも可能である。ただし、このようなデータ表が使いやすいか、と聞かれると回答に困ってしまう。少しくらい横長になっても、「普通にデータを管理した方が使いやすい」というケースが大半を占めるだろう。

よって、今回、紹介した内容を便利に活用できる場面はかなり限定的になると思われる。あまり実用的なテクニックとはいえないが、このような機能が用意されていることも学んでおくと、いつか役に立つときが来るかもしれない。念のため、覚えておくとよいだろう。