Excel。手早く、複数続く項目行ごとに項目行数が違っても色分けしたい
＜条件付き書式・MOD関数＞
テーブルにすると、一行おきに塗り分けることができますが、次の表のように、一塊の項目行ごとに、塗り分けるには、どうしたらいいのでしょうか？
4行目まで渋谷店が続いているので、塗りつぶされていて、5～6行目の新宿は別の項目なので、塗りつぶさない。
データ量が増えれば目視で行うのは大変ですし、かといって、Excel VBAでマクロをつくるのも面倒です。
このような場合、使う機能は、「条件付き書式」だろうというのはわかるのですが、問題となるのが、どのような計算式を設定したらいいのかということです。
6行目と7行目のように切り替わるところは、「<>」という同じでないという、比較演算子を使えば発見することはできます。
ただ、7行目から10行目の池袋店が、別の繰り返しという判断をすることが、簡単にできません。
そこで、D列に色分けの判断をするための結果をつくり、そのデータをもとに、条件付き書式を設定するという2段階の方法で、設定していきます。
D2に次の数式を設定します。
=MOD((B1<>B2)+D1,2)
オートフィルで数式をコピーします。
「1」と「0」がちょうど、項目ごとにわかれて算出することできました。
あとは、「1」だったら塗りつぶすように条件付き書式を設定すればいいわけですが、先に数式を説明しておきます。
=MOD((B1<>B2)+D1,2)
MOD関数は、除算した結果の「余り」を算出する関数です。
引数を確認していきましょう。
(B1<>B2)ですが、D2の場合で考えると、B1は「店舗名」でB2は「渋谷」なので、等しくありません。
よってこの条件は成立しています。
つまり「TRUE」というわけですね。
Excelでは、「TRUE」は「1」です。
このあたいに、前のデータと同じなのかどうかを判定させたいので、その上のセルであるD1の値「0」を足しています。
そのため、(B1<>B2)+D1は「1+0=1」ということで、「1」。
これを2で割るわけですね。
その余りは「1」。
D3は、
=MOD((B2<>B3)+D2,2)
という数式に変わります。
B2とB3は等しいので、成立しないので、「FALSE」と算出されます。
FALSEは、Excelでは「0」なので、0+1(D2の値)=1。
この「1」を2で除算した余りは「1」ということで、上の行と同じ項目という判断ができるわけです。
このように、この数式をつかうことで、項目ごとに分けることができる数値を作ることができたわけです。
ここまでくれば、あとは、条件付き書式を設定する工程に入ります。
A2:C12を範囲選択して、ホームタブの条件付き書式にある「新しいルール」をクリックします。
新しい書式ルールダイアログボックスが表示されます。
「数式を使用して、書式設定するセルを決定」にして、=$D2=1 と数式を設定したら、書式ボタンをクリックして、塗りつぶす色を設定します。
その後、OKボタンをクリックすると、項目ごとに塗り分けることができます。
最後に、D列に算出した値が表示されたままだとカッコ悪いので、表示形式をつかって見えないようにしていきます。
D2:D12を範囲選択して、セルの書式設定ダイアログボックスを表示します。
これで、完成しました。
条件付き書式の新しいルールは、手入力で行う必要があります。
そのため、複雑な条件のため、大きな数式を作る場合は、別のセルにわけて、その結果を使う方が、設定しやすいかもしれませんね。
