2017年09月18日

工程表に日付を入力した時、色を付けたセルを移動させてみた-2 / Excel / 条件付き書式

前回の日曜大工の工程表では、条件付き日付の数式で入力した日付から式を作ってセルの色付けを書式設定していましたが、後々のメンテナンス等を考えると、条件付き書式の数式は出来るだけシンプルにしておいた方がいいと言うことが判明!

なので、色を付ける条件式は別途計算式を入力するセルを設けて、色を付ける書式はそのセルの値によって色の有無を設定するようにしました。
こんな感じで、右側に計算式を入力するセルを配置しました。
20170917-001.JPG

この計算エリアは、“列G”〜“列L”の条件付き書式を設定する列に“列M”〜“列R”を対応させて、計算エリアのセルの値が“1”になった時、“列G”〜“列L”のセルがピンク色になるように書式設定をします。
IMG.jpg

で、計算エリアのセルを“1”にする条件は、
各工程に入力された日付の値(シリアル値)がその右側に入力されているセルの値より小さい時は“0”、大きい時は“1”を入力するようにします。
つまり、入力されている日付右側にそれより後の日付の入力がない時(つまり、空欄を意味しています)ピンク色になるようにします。
IMG_0001.jpg

“列M”には
“=IF(SUM(H4:L4)-G4>=0,0,1)”
列Gの右側のセルの値の合計から列Gの値を引いた結果が0以上の時(右側に日付の入力がある時)“0”、そうでない時(右側に日付の入力がない時)“1”
と入力します。
20170917-002.JPG

同様に“列N”には
“=IF(SUM(I4:L4)-H4>=0,0,1)”
列Hの右側のセルの値の合計から列Gの値を引いた結果が0以上の時(右側に日付の入力がある時)“0”、そうでない時(右側に日付の入力がない時)“1”
と入力します。
20170917-003.JPG

列O〜Qも同様に式を入力します。
20170917-005.JPG

“列R”には“完成日”に入力があった時、その日が今日の時に“1”今日以外だったら“0”を入力するようにします。
“=IF([@完成]=TODAY(),1,0)”と入力します。
20170917-006.JPG

以上で、計算エリアの式の入力は完了です。
ちなみに、こんな計算結果になっています。
20170917-001.JPG

では、この計算結果が“1”になった時にセルをピンク色にする条件付き書式を設定していきます。
1列ずつ設定していきます。
まず、“列G”の範囲を選択して「スタイル」の「条件付き書式」から「新しいルール」を選択します。
20170917-007.JPG

数式に、
“=$M4=1”(列Mが“1”の場合)
書式はセルをピンクで塗り潰しにします。
20170917-008.JPG

次は“列H”を選択して
20170917-009.JPG

数式に、
“=$N4=1”(列Nが“1”の場合)
書式はセルをピンクで塗り潰しにします。
20170917-010.JPG

後は同様にして、“列L”まで設定します。
20170917-011.JPG

と、このように対応する計算エリアのセルが“1”になっている工程がピンクに色が付いています。
20170917-012.JPG

前回と結果は同じですが、書式設定の計算式を複雑にしてしまうとどんな計算式にしたのか後から確認するのも面倒だし、式のコピペも出来ないし、編集も面倒だし、、、と言うことでこっちの方が絶対いいです!



次ページ:スパークラインを使って縦棒グラフのデータバーみたいなのを作ってみた

前ページ:工程表に日付を入力した時、色を付けたセルを移動させてみた-1

関連ページ
同じセルに複数の条件付き書式を設定してみた
書式を設定するセルと条件を設定するセルが異なる場合に条件付き書式を設定してみた
隣のセルの値と比較してセルの書式を設定する
条件付き書式で1行おきのセルの書式を設定してみた


posted by haku1569 at 15:40| Excel スタイル | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。