エクセル工程表で業者別にセルの色を変える方法を教えてください

質問

エクセル工程表で業者別にセルの色を変える方法を教えてください

皆さんこんにちは。YOSHIMAROといいます。

WEBでいろいろ調べながらエクセル2003で工程表を作成しています。

3行目以降のE列からAI列には「条件付書式」で「着工予定」から「完工予定」までセルの
色が変わるようにしています。
例えばE3のセル(わかりづらくて申し訳ありませんがS.I邸の9月1日を現すセル)に
は以下の条件付書式を設定しています。

条件1　数式が:=(AND($C3<=E$2,$D3>=F$2))　→　書式:ピンク

　　　　 (A列)｜(B列)　｜　　(C列)　｜ 　 (D列)　｜　 (E列以降)　　　　
(1行目) 邸名｜施工店｜ 着工予定｜完工予定 ｜ 9 月
(2行目) 　　　｜　　 　 ｜　　　　　 　 ｜ 　　 　 　　｜1 2 3 4 5 6 7 8 9 10 11 12 13 　　 　　　　　　　　　
(3行目) S.I 邸 ｜　　A　社｜　 9/10　｜ 9/20 ｜E3（工程欄　セルの色が変わる)
(4行目) H.U邸 ｜ 　 B　社｜　 9/05 ｜ 9/25 ｜E4(工程欄　セルの色が変わる)

ここで質問ですが施工店(A社、B社、・・・・)によってセルの色を変える方法がありま
すでしょうか。
施工店は20社ほどあり、しかも今後増減する可能性が大きいので完全にお手上げと
なってしまいました。

(1)マクロを使わなくてはならないか(そうなるとまったくわかりません)、関数で対
　応可能か。
(2)別のセルで施工店別の色を定義しておいて、各行で施工店のセル(例えばB3)と比
　較して日程のセルの色を決定するのだと想像していますがどのような方法がある
　のか。

上記2点も含めてお知恵を拝借できたらと思っております。

よろしくお願いします。

回答No.3

tom04 · ベストアンサー

こんばんは！
当方使用のExcel2003では条件付書式の設定は三つまでですので、
VBAでの方法でやってみました。

↓の画像のようにSheet2に社名と番号（←これはカラーインデックスの番号）を入力しておきます。
Sheet2の3行目は必要ないのですが、色見本として表示しているだけです。
こちらで一つ一つ設定するのは大変ですので勝手に1からの連番にしてみましたが、2は「白」19は「アイボリー」で画面上では色が薄すぎてよく判らないので適当に番号を変えています。
（好みの色がない場合はネットでカラーインデックスで検索してみてください。
56色程度はあったと思います）←数が正しいかどうかは自信がありません。

尚、日付部分はすべてシリアル値とします。
余計なお世話かも知れませんが、Sheet1のE2セルは
=IF(COUNTBLANK($E$1:$G$1),"",IF(MONTH(DATE($E$1,$G$1,COLUMN(A1)))=$G$1,DATE($E$1,$G$1,COLUMN(A1)),""))
という数式を入れ、31日のAIセルまでオートフィルでコピーしています。

そして、Sheet1のSheet見出し上で右クリック　→　コードの表示　を選択すると白い画面が出ますので、
↓のコードをコピー＆ペーストしてマクロを実行してみてください。

Sub test()
Dim i, j As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet2")
For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 5 To 35
If Cells(2, j) <> "" Then
If Cells(2, j) >= Cells(i, 3) And Cells(2, j) <= Cells(i, 4) Then
Cells(i, j).Interior.ColorIndex = WorksheetFunction _
.HLookup(Cells(i, 2), ws.Range("A1:T2"), 2, False)
End If
End If
Next j
Next i
End Sub

尚、着工日、完工日を変更する場合はすべてのセルの色を一旦「塗りつぶしなし」にして
もう一度マクロを実行しれば正しく表示されると思います。

以上、参考になればよいのですが・・・m(__)m

お礼

tom04さんありがとうございます。

参考にさせていただきます。
少し時間がかかりそうなので取り急ぎお礼コメントを入れさせていただきました。

おそらくわからない事が出てくると思いますのでそのときはここにコメントしたいと思います。
ご迷惑でなければアドバイスよろしくお願いします。

それでは今から挑戦してきます。

回答No.5

MackyNo1

横棒積み上げ棒グラフによる行程表の作成方法の概略は以下の通りです。

まず準備としてE列に着工から完工までの期間を計算する数式「=D2-C2」を入力して下方向にオートフィルしておきます。
次に着工予定の列のデータ範囲を選択して、グラフウィザードで横棒積み上げ棒グラフをひとまず作成しておき、ウィザードの2/4の系列タブで値の欄にE2セルから下のデータ範囲を選択し、項目ラベルに使用の欄はA2から下のデータ範囲を指定します。
次に横軸を選択して右クリックから軸の書式設定で適当な日付形式を選択し、メモリタブで最小値をカレンダーの開始年月日を「2010/9/1」のように入力し、目盛間隔をたとえば「７」にしてOKします。
さらに縦軸の上で右クリックし、軸の書式設定から「軸を反転する」などのチェックをすべて入れてください。
着工予定の系列の上で右クリックし「データ系列の書式設定」から輪郭と領域をいずれも「なし」にします。
また、プロットエリアの上で右クリックしてプロットエリアの書式を適宜設定したち、凡例などを選択してDeleteで削除し、さらにプロットエリアの大きさやグラフを横長にするなどの調整をしてレイアウトを整えます。

新しいデータの追加に対応できるようにしたり、その他のグラフ作成上のノウハウはいろいろありますがひとまずここまでうまくできるか試してみてください。

ちなみに本題の施工店ごとに横棒の色を変えたいなら、グラフの系列を選択し、もう一度変更したい系列をシングルクリックで選択して、右クリックから「データ要素の書式設定」で色を変更し、同じ系列を順次選択しCtrl+Yの操作でまとめて色を変更するのが効率的な操作です。

お礼

MackyNo1さんこんにちは。

詳細な解説ありがとうございます。
挑戦してみます。

現在tom04さんのマクロと奮闘中です。
(仕事の空き時間にやっているのでなかなか進みませんが・・・)

実際に作っているフォーマットと質問のために簡略化したフォーマットの違いからなかなかうまくいきません。
質問しようにもその前段階から躓いています。

横棒グラフにも挑戦しますのでまたアドバイスお願いします。

回答No.4

MackyNo1

回答に一部間違いがありましたので訂正します。

積み上げ横棒グラフでデフォルトのままグラフを作成すると、すべての系列は同じ色の帯の棒グラフになります。
しかし、オートフィルタでデータを絞り込めば施工店ごとのグラフになりますので、色を分ける必要性はあまりないと思います。

添付画像の上がすべてのデータのグラフで、下がA社で抽出したときのグラフです。

これでご要望に沿ったグラフになっているでしょうか？

作成方法は、かなり複雑な手順を踏んで作成する必要がありますので、ひとまずこの段階で問題ないか確認してください。

回答No.2

MackyNo1

エクセル2003では条件付き書式の色は3つまでしか設定できませんので、基本的にご希望の操作はできません。

もし施工店別の工程表を作りたいなら、積み上げ横棒グラフを利用して工程表を作成することができます（この場合はすべての系列の色がばらばらになります）。

このグラフで便利な点は、オートフィルタでデータを抽出すると、そのデータだけのグラフを表示できることです。

このグラフを作成するには、期間を表示する補助列が1列だけ必要となりますが、もしこの方法でも良いというのであれば作成方法を提示することができます。

お礼

MackyNo1さんこんにちは。

会社でチェックしているのでコメントが遅くなり申し訳ありません。
さて、積み上げ横棒グラフを利用するというのは考えてもいなかったのでイメージが沸きません。

グラフの色は施工店ごとに色分けされるのですか?
オートフィルタを使うというのも新しい使い方がありそうですね。

面倒でなければ作成方法を教えていただけますか。

＊「お礼入力」より「補足入力」がよかったのでしょうか。
使い方を間違えていたら申し訳ありません。

回答No.1

mu2011

エクセルのグレードが２００７以上ならば、現状の条件付き書式設定していけばよいと思いますが、
（２）のような事はできないと思います。
エクセル２００３以下ならば残念ながら、ご要望の振る舞いをマクロ（ＶＢＡ）で組み込みしかありません。

素人ですが、施工店によって多種の色が表示される画面、紙面はとても見れたものではないと思いますが如何でしょうか。

お礼

mu2011さん回答ありがとうございます。
わかりづらいエクセル表(?)だったのに理解していただいて感謝です。

やはりマクロになってしまいますか。

施工店はセルの色を決めていて、施工店でソートしたときに施工店と同じ色のセルで工程も表現したいと思いました。
現場の工程が施工店ごとにどのくらい重なっているのか、新しい現場をアテンドするのにどの施工店にお願いできるかなどを視覚的に捉えられたら使いやすいと思ったのですが・・・

ありがとうございました。