スポンサーリンク
以前の記事で、列の中で値が変わるごとに行に色を付けるという処理を扱った。
その例が下図で、A列「第1カテゴリ」の値が変わるごとに、行全体の背景色が水色⇔無色と変わっている。
これは、Excelの通常機能で実現するのが難しく、同記事ではマクロで実現したわけだが、他の方法としてPowerQueryがかなり有効だ。
そこで、前回取り上げたインデックスの機能も使って、PowerQueryでやってみる。
グループ化
ではまずExcelデータをPowerQueryで読み込む。今回対象とする「第1カテゴリ」の列を選んで
リボン「ホーム」タブ
→「グループ化」
とする。
このグループ化は、非常に便利な機能で、私も今は大して使いこなせていない。
複数列をいっぺんにグループ化したり、グループ化した項目ごとに集計したりとか、色々なことができる。
今回は、グループ化の設定画面で、まず「新しい列名」という箇所に、最初は「カウント」とかなってるけど適当に名前を付ける。
今回はこれは後ですぐ消すので、何でも良いけど、ひとまず「まとめ」としておく。
そして次に、真ん中の「操作」で「すべての行」を選ぶ。
実はこの「グループ化」機能では、この「すべての行」を選ぶ割合がかなり多いと思う。
今回の場合、100行あるデータを20行に圧縮しようとかいうのでなく、100行は100行のままでいくので「すべての行」で行くというか、初めての場合はそういう理解で良いかと思う。
右端の「列」というのは無視して「OK」を押す。
すると、最初に選択しておいた「第1カテゴリ」を除いた他の列が非表示にされ「Table」と表示される。
グループ化機能って、何度も使ってるとこれがどうもややこしいんだけど、選択しておいた以外の列がまとめられ非表示になる。
インデックスの追加
ではこの「第1カテゴリ」だけが表示された状態で、前回取り上げたインデックス機能を使う。
リボン「列の追加」タブ
→インデックス列
だ。開始番号も0で良いだろう。
これでインデックスが追加される。
さて下図では、グループ化された「第1カテゴリ」ごとに、
「研究」→0
「通信」→1
「ネットワーク」→2
とインデックスが振られている。
そしてこの「第1カテゴリ」ごとインデックスは、「グループ化」を解除した後も保持されるのがミソだ。
私が個人的に使うグループ化機能は、グループ化したものを解除する前提で使うことが多い。
グループ化の解除
ではグループ化の解除だが、先ほど「まとめ」という名前で作った、グループ化で折りたたまれた列の右端にあるボタンを押すと、折りたたんだものを展開する確認画面が出てくる。
この中で「第1カテゴリ」が、非表示にしていないものと重複しているので、これはチェックを外して良いだろう。
また、下にある「元の列名をプレフィックスとして使用します」というのは、通常はチェックを外せば良いと思う。これのチェックを入れると、列名に「テーブル01.」とかいうデータ名称が付けられ長ったらしくなる。
設定ができたら「OK」を押す。
グループ化したものが展開され、インデックスも「第1カテゴリ」ごとに付けられた状態になる。
ここまで来たらExcelシートに書き出せば良いので
リボン「ホーム」タブ
→「閉じて読み込む」
としよう。
テーブルに条件付き書式を設定する
Excelシートに「テーブル」の形式でデータが出力される。
デフォルトでは1行おきに縞模様が付いていると思うが、今回はこれは邪魔なので、リボン「テーブルデザイン」タブの「縞模様(行)」のチェックを外しておく。
次に、「第1カテゴリ」の値が変わるごとに色を付けたいわけだが、今回はこれを「インデックス」列の値が偶数か奇数かで色が分かれるようにしたいと思う。
それを条件付き書式機能で設定するので、
リボン「ホーム」タブ
→条件付き書式
→「新しいルール」
とする。
条件付き書式のルール設定画面が出てくる。
まず「数式を使用して、書式設定するセルを決定」を選ぼう。
次に、D列のインデックスが奇数だったら背景色を変えるという設定として、ISODD関数を使って
=ISODD($D2)
と数式欄に入力する。
D列という列は固定にして、行は2行目から変動させていくので、$マークを「D」の前にだけ付ける$D2という相対参照の書き方は、息を吸うようにできなければならない。
次に書式設定の画面。
ひとまず、水色っぽい色を選んでおく。
条件付き書式のルール設定画面に戻るが、「OK」を押す。
D列のインデックスが奇数のとき、背景色が変わるように設定できた。
マクロの知識がなくとも、けっこう簡単に設定できて、これもPowerQueryの素晴らしさだ。
スポンサーリンク
