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で、「有無」の列を追加して、処理を行わせる。
その手順だが、「列のピボット」を行う前の段階で、[列の追加]タブの左端にある[例からの列]−[すべての列から]を選択する。すると、プレビュー領域に新規列の入力領域が表示されるので、ここに「あり」や「○」など、「有無」列に相当する内容を入れる。2行ほど同じものを入れると自動的に全ての行に同じものが入力されるようになる。これは、入力した項目から、Power Queryが残りの行の内容を推測した結果だ。
こうして対象となる列を作成したら、あとの手順は同一だ。なお、この場合、機能がない項目は空欄となる。空欄ではなく、「×」にしたいこともあるだろう。最後の段階でPower Queryのプレビューを見ると、完成したマトリックス表で空欄になっている部分には、「null」と表示されている。「値の置換」で検索する値を「null」とすれば、これを任意の文字に置き換えることが可能だ。
「有無」のないリスト表をマトリックス表に変換する(1)
上述のサンプルデータは、「有無」列で機能の有無を表現していたが、機能があるものだけのリスト表でも同じ情報を表現できる。対象となる表を選択して、[データ]タブの[テーブルまたは範囲から]をクリックする。[データの作成]ダイアログでは、「先頭行をテーブルの見出しとして使用する」にチェックを入れること。
「有無」のないリスト表をマトリックス表に変換する(6)
機能がない部分は「null」となっているので、これを「×」に変換する。「A」から「D」まで選択し、[ホーム]タブの[値の置換]をクリック、[値の置換]ダイアログで「null」を「×」に置換する。
Power Queryで記録(保存)したクエリを編集・更新するには
記録(保存)したクエリは、[データ]タブの[接続](Excel 2016)/[クエリと接続](Excel 365)をクリックすると表示される「作業ウィンドウ」に表示される。作業ウィンドウで該当のクエリをダブルクリックすると、[Power Queryエディター]ウィンドウが開いて、クエリを編集することが可能だ。
また、Power Queryで行った操作は、ウィンドウ右側にステップとして記録されている。各ステップを選択すると、その段階での実行結果がプレビュー領域に表示される。
Power Queryで行った操作を編集する(1)
[データ]タブの[クエリと接続]をクリックすると、作業ウィンドウにクエリが表示される。クエリをダブルクリックすると、[Power Queryエディター]ウィンドウを開くことができる。
Power Queryで行った操作を編集する(2)
[Power Queryエディター]ウィンドウの右側にある「適用したステップ」は、ウィンドウ内で行った作業(コマンド)の履歴に対応している。このステップは、削除や挿入、変更が可能だ。
このステップは、後からでも削除や新規挿入が可能だ。もちろん、操作を間違えたときには、削除することで操作前の状態に戻ることもできる。ステップをダブルクリックすると、操作時に表示されたダイアログが表示されるので、そこでパラメーターを修正することも可能だ。
なお、この各ステップは「M Formula」(M式、M言語、M数式言語)と呼ばれる言語で表現されており、慣れてくれば、[Power Queryエディター]ウィンドウの[表示]タブにある[詳細エディター]を使って、直接M Formulaを編集することもできる。ただ最初のうちは、ダイアログなどでステップを修正した方がいいだろう。
[詳細エディター]ウィンドウの画面
[Power Queryエディター]ウィンドウの[表示]タブにある[詳細エディター]をクリックすると、[詳細エディター]ウィンドウが開く。ここで、直接M Formulaを編集することもできる。
どの場合でも、最後に[閉じて読み込む]を行えば、作成した表が更新される。[Power Queryエディター]のウィンドウを[×(クローズ)]ボタンなどで閉じ、確認のメッセージボックスで[破棄]を選択すれば、その回に行った編集などを破棄して、[Power Queryエディター]ウィンドウを開く前の段階(前回のPower Queryの結果)に戻ることができる。どの場合も元の表は変更されない。
Power Queryでクエリを作成したら元の表の列名をむやみに変更しない
Power Queryでは、クエリに元の表を変換する手順を記録している。このため、元の表のデータが修正、追加されても、自動的にPower Queryで変換した表を更新できる。
しかし、元の表の先頭行にある列名を変更してしまうと、クエリを書き換える必要が出てくるので注意してほしい。この作業は少し面倒なので、Power Queryを採用したら元の表の列名を変更しないようにすること。どうしても変更したい場合、[Power Queryエディター]ウィンドウを開いて、クエリの操作として列名を変更するとよい。
Power Queryは、さまざまな可能性を持つ。[列のピボット]ボタンのそばには[列のピボット解除]というボタンがあり、これを使うとマトリックス表をリスト表に変換することも可能だ。もちろん、マトリックス表は、Power Queryで作成したものでなくても構わない。
Copyright© Digital Advantage Corp. All Rights Reserved.