haku1569　Excel でらくらく データ分析！

前回の日曜大工の工程表では作業中の工程の数をカウントして山積みの縦棒グラフをスパークラインで作ってみましたが、



ウッドデッキの様に、組立キットの場合は“組立”や“加工”等の工程は不要になります。買ってきたキットを説明書を見ながら組み立てて塗装して完成になります。

その時、“設計”や“加工”の工程が空白なままだと、やらなきゃいけないのにやってないのか、やらなくてもいい工程なのかがそこだけ見ただけででは分かりませんよね。つまり見える化がされてません。

そこで、やらなくても良い工程には“ーー→”を入力して分かりやすくしてみました。



あれっ、“加工”の山積みの棒グラフが無くなっちゃってます。
計算エリアには“####”が表示されちゃっています。
日付（シリアル値）の計算に文字列が入ってきたからですね。計算結果が崩れちゃってるんですね。



なので、ここには文字列は入力できません。
どーしよーかなー。。。
と言うことで！？
条件付き書式で数字の“1”が入力された時に書式のユーザー定義で“ーー→”を表示させることにしましょう！
一種のエラー回避です。。。(^_-)-☆

では、まず条件付き書式を設定する範囲を選択します。



で、「条件付き書式」から「新しいルール」を選択します。



「新しい書式のルール」で「指定の値を含むセルだけ（1を入力したセル）を書式設定」を選択します。
「セルの値」が「次の値に等しい」にして値は「1」を入力します。



で、書式は「表示形式」の「ユーザー定義」で「種類」に“ーー→”を入力します。
上の“サンプル”が“ーー→”と表示されるのを確認しておきましょう！
んで、「OK」します。



前の画面で書式のプレビューを確認したら「OK」します。



で、ウッドデッキの“設定”と“加工”のところに“1”を入力します。



と、ほらっ！“ーー→”が表示されて山積みもちゃんと表示されていますね。



んで、なんで“1”かと言うと、まず“0”だと空白セルと混同されて空白セルにも“ーー→”が表示されています。それ以外の数字なら何でもOKです。計算エリアでは日付の入っているセルの右側のセルの値と比較をしていますが、日付のシリアル値は例えば2017年1月1日は“42,736”と数字の1桁等より十分大きいので、山積みの計算には何ら影響を与えません。

めでたし、めでたし！(^O^)




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

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

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

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


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


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


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


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


列O〜Qも同様に式を入力します。


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


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


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


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


次は“列H”を選択して


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


後は同様にして、“列L”まで設定します。


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


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



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

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

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

Excelの条件付きセルで工程表に日付を入力した時に作業中の工程が目立つ様にセルに色を付けてみます。「条件付き書式」で設定します。

こんな、日曜大工の工程表があります。
普通“工程表”と言うと、横軸が時間のいわゆるガントチャートが一般的ですが、専用のソフトで作成する物が多く、なかなか汎用的に使えるのが少ないと思います。
なので、横軸に工程にして、そこに日付を入れることで作業の進み具合や、山積み状況が分かるようにした表をExcelで作ってみようと思います。
こんな表です


で、作業中の工程に日付を入力すると自動的にセルに色を付ける様にしています。
これで、今作業中の工程がどれかがすぐ分かるようになります。
で、例えばウッドデッキが塗装に入ったり、下駄箱再塗装も塗装に入ると、今まで色がついていた工程はこれも自動的に色が消える様になっています。
こんな感じです。


では、早速やってみましょう。
1列ずつ、「条件付き書式」でセルの書式設定を行っていきます。
まず、最初の“設計”の列（ここでは、“列G”）の範囲を選択します。


メニューの「ホーム」、「スタイル」、「条件付き書式」から「新しいルール」を選択します。


「新しい書式ルール」の画面から
「数式を使用して、書式設定するセルを決定」を選択します。
数式を“=$G4>SUM($H4:$L4)”(列Gのセルの値がその右側の列のセルの値の合計より大きい場合）と入力します。


「書式」を設定します。
塗りつぶしの色をピンクにして、「OK」します。


「新しい書式ルール」の画面に戻りプレビューを確認したら「OK」します。


隣の“列H”も同様な設定をします。
数式は“=$H4>SUM($I4:$L4)”(列Hのセルの値がその右側の列のセルの値の合計より大きい場合）と入力します。


“列I”、“列J”も同様に設定します。
“列K”は右隣の工程は１つ（完成）しかないので、合計は行いません。
数式は“=$K4>$L4”(列Kのセルの値がその右側の列Lのセルの値より大きい場合）と入力します。


すると、日付を入力しただけで、自動的にセルに色がついたり消えたりします。


あっ、それから一番上のエアコン室外機カバーは行全体がグレーに色が付いていますが、これは“完成”に日付が入ると全体にグレーになるように条件付き書式で設定しています。


次ページ：
前ページ：

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

Excelで同じセルに複数の条件付きセルを設定してみました。
それぞれのルールの優先順位の設定で表示される書式が変わってきます。

前回、“列C”の値より“列B”の値が大きい場合に“列B”のセルを赤くする条件付き書式を設定しました。


今度はそこに、但し書きを追加してみようと思います。
こんなんです「但し、“列C”の値が“1”の場合は“列B”の値に関わらず“列B”のセルは赤くしない」みたいな、、、つまり、“列C”が“1”だったら、“列B”は赤くならないってことです。

では早速、書式設定する“列B”の範囲を選択します。


メニューから「ホーム」、「スタイル」、「条件付き書式」から「新しいルール」を選択し「新しい書式ルール」の画面を開きます。
「数式を使用して、書式設定するセルを決定」を選択し、
数式を“=$C3=1”（C3が1の時）と入力します。
「書式」は「塗りつぶし」を「色なし」にします。


と、このように“列C”が“1”のところは、“列B”の“5”が“1”より大きいのにも関わらずセルが赤くなっていません。


「ルールの管理」を見てみましょう。


と、「色なし」のルールが上になっています。
“ルール（表示順で適用）”と書かれています。
この上の「色なし」のルールを選択して▼を押して表示順を下げてみたらどうなるでしょうか？


と、このように下に下がりました。


するとこのようになりました。
“列C”が“1”で「色なし」のルールが適用された後に、「赤」のルールが適用され、セルは赤く表示されました。


このように、同じセルに複数の条件付き書式が設定された場合はそのルールが適用される順位で表示結果が変わります。
思うような表示されない場合は「ルールの管理」を見て、順位が間違っていないか確認してみましょう！

次ページ：
前ページ：隣のセルの値と比較してセルの書式を設定する

関連ページ
・書式を設定するセルと条件を設定するセルが異なる場合に条件付き書式を設定してみた
・条件付き書式で1行おきのセルの書式を設定してみた

Excelで隣のセルの値と比較した結果でセルの書式を設定してみます。
「条件付き書式」を使用します。

この様な表があって、“列B”の値が“列C”の値より大きい時、その“列B”のセルの背景色を赤に設定してみます。


つまり、こんなことです。


書式を設定する範囲（赤くする範囲）を選択します。


メニューの「ホーム」、「スタイル」から「条件付き書式」を開いて「新しいルール」を選択します。


「新しい書式ルール」の画面が開きます。
まず、ルールの種類を「数式を使用して、書式設定するセルを決定」にします。
数式は“=$B3>$C3”（列Bが列Cより大きいとき）と入力します。
次に「書式」を設定します。


いつもの「セルの書式設定」画面が開きます。
セルの塗りつぶしを赤にして「OK」します。


「新しい書式ルール」画面に戻ってプレビューを確認したら「OK」します。


すると、こんな風になりました。


ちなみに、設定内容を変更する場合は、
「条件付き書式」から「ルールの管理」を選択します。


「条件付き書式ルールの管理」画面で、変更するルールを選択して「ルールの編集」を選択します。


すると「書式ルールの編集」画面が開きますので、ここで設定内容を変更することが出来ます。


次ページ：同じセルに複数の条件付き書式を設定してみた
前ページ：書式を設定するセルと条件を設定するセルが異なる場合に条件付き書式を設定してみた

関連ページ
・条件付き書式で1行おきのセルの書式を設定してみた

Excelの条件付きセルで離れたところのセルの条件で書式を設定させてみます。
「数式を使用して、書式設定するセルを決定」を使用します。

こんな、送り物のリストがあります。


ここで、「発送」の列に“済”と入力されるとその行全体がグレーになる様にしてみます。
こんな感じ、、


書式を設定する範囲を選択します。


メニューの「ホーム」、「スタイル」、「条件付き書式」から「新しいルール」を選択します。


「新しい書式ルール」の画面から
「数式を使用して、書式設定するセルを決定」を選択します。
数式を“=$F3="済"”（列Fが“済”の場合）と入力します。
「書式」を設定します。


塗りつぶしの色をグレーにして、「OK」します。


「新しい書式ルール」の画面でプレビューを確認したら「OK」します。


するとこのように行全体がグレーになりました。


「条件付き書式」と言うと、上の方に表示される予め設定されたルールを使用しがちです。


「新しい書式ルール」でも安易に「指定の値を含む・・・」等を選択してしまいがちですが、、、


それらは、全て書式を設定する範囲と条件を設定するセルが同じになってしまいます。
なので、今回の様に別の場所のセルの条件で書式を設定することは出来ないのです。
「数式を使用して、書式設定するセルを決定」は文字通り数式を使用しなければならないので、とっつき辛くて、めんどくさいところはありますが、使い道はかなり広がりますよ！！

次ページ：書式を設定するセルと条件を設定するセルが異なる場合に条件付き書式を設定してみた
前ページ：

関連ページ
・同じセルに複数の条件付き書式を設定してみた
・条件付き書式で1行おきのセルの書式を設定してみた

Excelの「条件付き書式」に関数を使って1行おきのセルの書式を設定してみます。
“ROW関数”、“ISEVEN関数”を使用します。
“1行おきの値を合計してみた”で“ROW関数”と“ISEVEN関数”で1行おきの値の合計を求めましたが、「条件付き書式」では、関数も設定することができ、合計と同じ考え方で1行おきの書式を設定することができます。


例えばこんな受注データがあります。日毎の“数量”と“金額”が縦に配置されています。


ここで、金額は通貨表示にして“\”マークを付けたいと思います。それからセルの背景色もピンク色にしてみましょう。

まず、値が入力されている“D列”を選択範囲にします。


「ホーム」「スタイル」「条件付き書式」から「新しいルール」を選択します。


「新しい書式ルール」画面が開きます。
「ルールの種類を選択・・・」で「数式を使用して、書式を設定するセルを決定」を選択します。


次に「次の数式を満たす場合に値を書式設定」で、
“ISEVEN(ROW())”と入力します。
これは“ISEVEN関数”に“ROW関数”をネスト（入れ子）したもので、
“ROW関数”でセルの行番号を求めます。次に“ISEVEN関数”でそれが、奇数か偶数かを判別します。
“ISEVEN関数”は偶数だったら“TRUE”、奇数だったら“FALSE”と表示するので、「次の数式を満たす・・・」とは結果が“TRUE”になることを言います。


次に「書式」のボタンを押します。
すると見慣れた「セルの書式設定」画面が開くので、ここでセルの書式を「表示形式」から「通貨」を選択し、


「塗りつぶし」から色を選択します。


その後「新しい書式ルール」の画面で「OK」します。


すると、このように金額の値のセルに“\”マークが表示され、セルの背景も桃色になりました。


基本的に、1行おきに書式を設定しなければならない様なデータは作らないようにすべきですが、意図せず1行おきのデータを処理しなければならない時は裏技として活用してください。。。


関連ページ
書式を設定するセルと条件を設定するセルが異なる場合に条件付き書式を設定してみた
同じセルに複数の条件付き書式を設定してみた
1行おきの値を合計してみた
行番号を求める / ROW
奇数か偶数かを求める / ISODD、ISEVEN
関数一覧（カテゴリ別）
関数一覧（名前別）
エラー値について（#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか）

Excelの「スタイル」の設定で数値データが入力されているセルにバーインジケータを表示してみます。
表に入力されている数字だけでは、その値を読み取らないと大きさや、全体の増加・減少の傾向が読み取れません、また入力ミス等のイレギュラーなデータも見落とされてしまう場合があります。
だからと言っていちいちグラフにするのも面倒だという場合、セルのスタイルの設定で値の見える化を簡単に行うことができます。


例えば“Cafe backyard”という店の日毎の来店者数のデータがあります。


“列C”を範囲指定します。


「ホーム」「スタイル」「条件付き書式」から「データバー」を選択し、好みの色を選択します。ここでは青を選択しました。


すると、セルの中に値の大きさに対応した横棒グラフが表示されました。


セルの幅を広げるともっと見やすくなりますよ。


んで、このデータバーの解除方法ですが、、
設定した“列C”を選択して、


「ホーム」「スタイル」「条件付き書式」「ルールのクリア」から「選択したセルからルールをクリアする」を選択します。


すると解除できます。