Do IT ノート

4tトラック乗ってるやつがいい感じにIT記事を捻り出してゆく

Excel条件付き書式講座②偶数行だけに背景色を設定

条件付き書式講座②は、前回よりもステップアップして

偶数行だけ」に書式を設定していきます。

なかなか丁寧に解説を心がけているので、長ったらしいかもしれません。

 

 

数式を使用した条件付き書式の設定

 

講座①で作ったシフト表を使って説明していきます。

 

f:id:bokudriven:20201014204316p:plain

シフト表

こんなシフト表があります。

友達から

21日って空いてる?仕事?

と聞かれました。

「ちょい待ち」

 

あなた(んえええ太郎)は、

画像として保存されたシフト表をスマートフォンから確認して見ました。

 

 

f:id:bokudriven:20201014204717p:plain

拡大したシフト表

 

あれ…オレって何行目だっけ?

確か下から3〜4行目だったと思うんだけど…

オレはF出勤なのか?それとも休み(空欄)なのか?

 

 

こういうことってよくありません?

1週間ないしは1ヶ月を網羅したシフトというのは、

1枚の画像(紙)で全容を確認できるという点では便利なのですが、

このように細部を確認するうえでは不便な構造をしています。

↑ここは教科書みたいな解説必要ないけどね。

 

ここで、少しでもシフトを追いやすくするため、

偶数行だけに背景色を設定する条件付き書式を設定してみましょう。

 

 

f:id:bokudriven:20201014205230p:plain

 

 

まず、画像のようにシフト部を範囲選択します。

次に、「ホーム」タブ右半分あたりにある「条件付き書式」から、

セルの強調表示ルールその他のルール…

と進んでいきます。

 

f:id:bokudriven:20201014205409p:plain

 

出てきたダイアログボックスにおいて、

指定の値を含むセルだけを書式設定」▼

数式を使用して、書式設定するセルを決定

に変更します。

 

 

前回(講座①)では、

アルファベットの「A」やら「C」、「F」やらが含まれるセルの背景色を変更するように設定しました。

 

今回は「A」だの「C」だの文字を指定するのではなく

Excelの数式を使って、条件に一致したセルの書式を変更するよう設定します。

 

f:id:bokudriven:20201014205556p:plain

数式を入力

 

 

数式を使用して条件を設定する解説

 

数式の入力欄には次のように入力します。

(構造はちょいあとで解説)

 

=MOD(ROW(),2)=0

 

 

条件付き書式において、

書式設定するセル※ = 条件に一致したセル です。

※ここでいう書式設定するセルとは、条件付き書式で指定した背景色や文字色、文字サイズなどの設定が反映されるセルという意味です。

 

数式を使用して条件を指定する場合、

数式の結果が正しいかどうか、で判断されます。

 

IF関数でいうと、

 

IF(MOD(ROW(),2)=0,"結果は0","結果は0以外)

 

としたとき、

 

MOD(ROW(),2) が 0 ならば

つまり、条件式が正しければ

"結果は0”

 

MOD(ROW(),2) が 0でないならば

つまり、条件式が正しくなければ

"結果は0以外

"

 

となりますが、条件付き書式ではIF関数と同じで、

条件式が正しければ→指定した書式を設定せよ

ということになります。

(条件式が正しくないセルでは書式を設定する必要がないので無視です。なので、条件式が正しくない時の書式は指定しませんね。)

 

 

 

そしてさきほどの数式(条件式)の構造解説です。

 

MOD関数 ... MOD(引数1,引数2)

引数1を、引数2で除算(割り算)したときの余りの数値を返します

 

引数1…ここでは、ROW()

引数2…ここでは、2

 

ROW()を2で割った余りを返すということです。

 

 

ROW関数とは

括弧の中の引数に指定したセルの行番号を返す関数です。

 

括弧の中に何も書かず(引数を指定せず)の場合は、

ROW関数を実行したセルの行番号を返します←ROW()なので今回はこっち

 

今回は

E14:Ai25のセル範囲を選択して条件付き書式を設定しているので、

 

セルE14では

ROW(E14)として設定されていて、

→戻り値は14。14行目にあるセルなので。

 

セルP21では

ROW(P21)として設定されている、

というイメージです。

→戻り値は21。21行目に(ry

 

 

 

数式の方に話を戻すと、

 

セルE14において、

MOD(ROW(),2)は、

 

MOD(14,2)

 

と置き換えることができます。

同様に、セルP21においてはMOD(21,2)ですね。

 

 

MOD関数では、

引数1を、引数2で除算(割り算)したときの余りの数値を返すので、

 

セルE14における、

MOD(14,2)は、

14を2で割ったときの余りの数値が返るので、

 

14÷2 = 7 余り 0

 

つまり、MOD(14,2)は、0に置き換えられます。

 

セルP21におけるMOD(21,2)は、

21を2で割ったときの余りの数値が返るので、

21÷2 = 10 余り 1で、返り値は1ですね。

 

つまり、MOD(ROW(),2)という数式によって、

 

セルが偶数行にあるなら0

セルが奇数行にあるなら1

 

という結果が返るようにできます。

 

 

↑↑の数式を利用して、

 

= MOD(ROW(),2) = 0

 

とすることで、

そのセルが偶数行にあるとき、

MOD(ROW(),2)という数式では0という数値が返るので

 

=MOD(ROW(),2) = 0 

という条件式は結果的に

= 0 = 0

となり、条件式が正しいことになります。

 

先述のとおり、条件付き書式では、

条件式が正しければ→指定した書式を設定せよ

ということになるので、

 

結果的に、偶数行にあるセルのみ、指定した書式を設定させることができるのです。

 

 

= MOD(ROW(),2) = 0

これを、

= MOD(ROW(),2) = 1

に変更すると、奇数行のみに設定できますね。

 

 

f:id:bokudriven:20201014205556p:plain

 

 

↑の、書式:のプルダウンメニューから、

ユーザー設定の書式…を選択し、

背景色(塗りつぶし)を好きなように設定して、

 

「偶数行だけに背景色を設定」

 

の条件付き書式は、設定完了です。お疲れさまでした。

 

f:id:bokudriven:20201014214735p:plain

偶数行設定後のシフト表

↑このままでは色がごちゃごちゃして見づらくもあるが、まっさらよりは目で追いやすくなっているはず。

 

 

 

大事な後処理

ひとつのセルに、いくつもの条件付き書式が設定されている場合、

思ったように書式が反映されないことがあります。

 

例えば、

f:id:bokudriven:20201014204316p:plain


このシフト表では、

アルファベットに応じてセルの背景色(塗りつぶし)を変更するような条件付き書式が設定されていますが、

さきほどの「偶数行だけに背景色」を設定してしまうと、

 

  • アルファベットの「A」だから、セル背景色を赤に設定するルール1
  • 偶数行だから、セル背景色を緑に設定するルール2

 

というように2つ以上のルールが混在してしまい、矛盾が生じます。

 

条件付き書式の機能上、いくつも条件付き書式のルールが存在する場合、

優先度順で適用されます。

 

例えば、ルール1よりもルール2(偶数行)のほうが優先度が高ければ、

ルール2で照らし合わせた条件に一致したとき、ルール2で設定した書式を反映し、

それより優先度の低いルール1以下は無視されます。

 

つまり、偶数行にある「A」セルは背景色が緑色で設定されます。

 

今回の「偶数行ルール」は、あくまでも

「目で追いやすくため」が目標であるため、

シフトのA出勤をわかりやすくするための「背景赤」ルールを妨害してはいけません。

(ここではそういうことにする)

 

この、ルールの優先度順は、以下のように変更(設定)できます。

 

ルールの優先度を変更

f:id:bokudriven:20201014214320p:plain

 

ホーム」→「条件付き書式」→「ルールの管理…

 

 

f:id:bokudriven:20201014215832p:plain

優先度の変更

ルールを選択し、ダイアログボックス右上の

ルールの順序を変更

で、優先度を変更できます。(上から順に優先度が高い)

上記画像のように設定すれば、

  • セルに含まれる文字によって背景色を変更

のルールがまず適用され、偶数行背景色設定のルールを無視することができます。

 

次回は、絶対参照を使用して、少し高度な位置指定も絡めて解説していこうかなぁ、と思ってます