Excel定型業務自動化への第一歩 Power Queryでリスト→◯×表（マトリックス表）変換：Tech TIPS
製品の購入を検討したり、他社製品との機能を比較したりするのに、機能の有無を○×でまとめた「マトリックス表」がよく使われる。ただ、実際に作ろうと思うと意外と面倒で間違えやすい。そこで、機能の一覧リスト（リスト表）を作成し、そこからマトリックス表に変換する方法を紹介しよう。
この記事は会員限定です。会員登録（無料）すると全てご覧いただけます。
対象：Excel 2016／2019／365
マトリックス表をPower Queryで作成する機能の有無などをまとめたリストをPower Queryで自動的にマトリックス表に変換できる。Power Queryを使うことで、元のリストを変更すると、自動的にマトリックス表に反映することができて便利だ。
レポート文書では、他社製品との比較などで、他社製品との機能などで、機能の有無を○×でまとめた「マトリックス表（マトリクス表）」を作ることがあるのではないだろうか。しかし、元になる情報は、機能を列挙したリストになっていることが多く、マトリックス表の作成は面倒な作業だ。
このような場合、「Microsoft Excel（エクセル）」を使えば、簡単にリストをマトリックス表に変換できる。
マトリックス表とリスト表
マトリックス表は縦横に並べた項目の交点に関連する情報を入れたもの、リスト表は複数の列を使い、それぞれの列に関連する情報を入れたものだ。この2つは、同じデータの表現形式の違いであり、同一のデータをこの2つの表形式で表現できる。項目が数値のみの場合、これを「クロス集計表」と呼ぶこともある。
マトリックス表は、人が見るときに理解しやすい形式であるため、文書などに入れる表を作るときに向いている。しかし、コンピュータで情報を処理するならリスト表の方が扱いやすい。
また、アプリケーションやハードウェア機器などが出力するデータはリスト表形式になっていることが多い（そのため、これをログ形式と呼ぶこともある）。さらに、リスト表は、構造が単純であるため、行を追加していくだけで、データを増やすことが可能だ。一方、マトリックス表では、全体を作り直す必要がある。
Excelのピボットテーブルを使うことで、リスト表をマトリックス表に簡単に変換することが可能だ。ピボットテーブルに関しては、Tech TIPS「【Excelステップアップ】ピボットテーブルをマスターして、データ集計の達人になる」を参考にしてほしい。
しかし、ピボットテーブルで扱う情報は、合計や平均を計算できる数値に限られる。例えば、機能の有無などの非数値の情報を扱うことはできない。こうした非数値のマトリックス表は、Power Query（パワークエリ）で自動作成することが可能だ。この方法でリスト表からマトリックス表を作れば、リスト表側が更新されてデータが追加されたような場合でも、マトリックス表を自動で再作成できる。
なお、Power Queryでは、数値のマトリックス表も作成が可能だ。下表は、ExcelのピボットテーブルとPower Queryを比較したものだ。用途に応じて使い分けるといいだろう。
| 機能 | 集計対象 | フィルター | 表示値の変換 | 編集作業 |
|---|---|---|---|---|
| ピボットテーブル | 数値のみ | 可 | 不可 | ［Excel］ウィンドウ |
| Power Query | 非数値／数値 | 可 | 可 | ［Power Queryエディター］ウィンドウ |
| ピボットテーブルとPower Query | ||||
リスト表からマトリックス表を作る
ここでは、仮想的なハードウェア製品の機能とそれを搭載する「機種」のリスト表をサンプルデータとして、マトリックス表に変換する作業をPower Queryで行ってみる。
Power Queryを使えば、マトリックス表を自動的に更新できるため、データの追加や削除といった作業が簡単になる。また、「有効」を「○」に、「無効」を「×」といったように表現の置き換えも簡単に行えるといったメリットもある。
下画面は、元になるリスト表だ。「機能」列には11種類の機能があり、「機種」列には、4種類の機種がある。3つ目の「有無」には、機種が機能を持っているかどうかが記述されている。
しかし、ここには、注釈がついており、単純な「あり」「なし」になっていない点に注意してほしい（単純な2値であれば、手間はかかるがピボットテーブルでも何とか行える）。
マトリックス表を作るには、表内のセルを選択した状態で［Ctrl］＋［A］キーで表全体を選択してから、［データ］タブにある［取得と変換］−［テーブルから］（Excel 2016）／［データの取得と変換］−［テーブルまたは範囲から］（Excel 365）をクリックする。
すると、［データの作成］ダイアログが現れるので、［先頭行をテーブルの見出しとして使用する］にチェックをして［OK］ボタンをクリックする。これで［Power Queryエディター］ウィンドウが開き、選択したテーブルが読み込まれた状態になる。横軸にしたい列（この例では「機種」列）を選択し、［変換］タブにある［任意の列］−［列のピボット］をクリックする。
［列のピボット］ダイアログが開くので、「値列」のリストボックスで［有無］列を選ぶ。さらに、［詳細オプション］をクリックして、展開してから「値の集計関数」で［集計しない］を選び、［OK］ボタンを押す。
［集計しない］は、値列のセル値をそのままマトリックス表の要素とするためのものだ。値列が数値の場合に「集計関数」を選ぶことで、Excelのピボットテーブルのように数値のマトリックス表を作成することも可能だ。
Power Queryのプレビュー領域には、マトリックス表ができているはずだ。なお、このプレビュー領域は、多数のデータがある場合、全てを表示せず、表の左上部分のみを表示することに注意してほしい。
次に「あり」と「なし」を「○」と「×」に変換する。それには、対象となる列（この例では「機種A」から「機種D」まで）を選択状態にして、［ホーム］タブの［変換］−［値の置換］をクリックする（リボンの［変換］タブにも［値の置換］があるが2つの機能から1つ選ぶ必要があり、ちょっと面倒）。
［値の置換］ダイアログが表示されるので、「検索する値」に「あり」を指定し、「置換後」に「○」を指定して［OK］ボタンを押す。もう1回、リボンの「値の置換」をクリックし、同じように「なし」を「×」に変換する。
これで、リスト表がマトリックス表に変換できた。［ホーム］タブの左端にある［閉じて読み込む］−［閉じて読み込む］をクリックすれば、新規のワークシートにマトリックス表が表示される。もし、元データとなるリスト表が更新された場合、［データ］タブにある［すべて更新］をクリックすると、自動的に元データの変更がマトリックス表に反映される。
リスト表をマトリックス表に変換する（1）対象となる表を選択して、［データ］タブの［テーブルまたは範囲から］をクリックする。［データの作成］ダイアログでは、「先頭行をテーブルの見出しとして使用する」にチェックを入れること。
リスト表をマトリックス表に変換する（4）マトリックス表が作成できるので、「あり」を「○」、「なし」を「×」に変換して分かりやすくする。それには、「機種A」から「機種D」まで選択し、［ホーム］タブの［値の置換］をクリック、［値の置換］ダイアログで置換を行う。
Power Queryは、元の表からマトリックス表を作る手順を「クエリ」として記録しているため、元データが変更されても、自動でマトリックス表を更新できる。
応用例：「有無」の項目がないとき
この例で挙げたリスト表は、個々の機能について機種ごとに機能の有無を表の列として表現していた。だが、リスト表によっては、存在するものだけがリストに入っている形式がある。このとき有無の列は、全て「あり」なので、これを省略したリストでも同じ情報を表現できる。
このような場合でも、同じ手順でマトリックス表にすることが可能だ。ただ、「列のピボット」では、必ず対象となる列を指定する必要があるので、Power Queryで、「有無」の列を追加して、処理を行わせる。
Copyright© Digital Advantage Corp. All Rights Reserved.
ITmediaはアイティメディア株式会社の登録商標です。