エクセルで工程表・作業表を作っています 塗りつぶしの自動化についてお教えください

質問

エクセルで工程表・作業表をつくることになりました。

A1セルに項目名「年月日」A2セルに項目名「曜日」
以下A3　A4・・・と作業氏名が入っています
B1から右方向に2005/01/01の形式で連続して
年月日が入っています
B2はB1を参照してWeekday関数を入れて　曜日を表示しています。

ここからなのですがA3から下方向に作業者名が入力されていますが
曜日に関係なく　ばらばらに「休」と文字を入れて
この作業者がその日は休みであることをあらわしています。

ある人のセルに数字を2種類入力して　作業日数分塗りつぶして
その作業の種類を色で塗り分けたいのですが
2005/11/24　ある作業者の欄に数字を３（三日間という意味）
を入れると
24日２５日２６日が自動的に塗りつぶすようなことは
可能でしょうか？
そして　たとえば25日の欄に「休」を入っている場合には
24日は塗りつぶして　２５日は「休」のままで塗りつぶさなくて
２６日と２７日をまた塗りつぶすという判別の方法があればと
思っています。

関数で可能なのか　もしくはセルをクリックなどして
ユーザーフォームを立ち上げて入力を促すマクロなどでないと
難しいのか　いかがでしょう？
文章がわかりにくかったら申し訳ありませんが
補足の解説をさせていただきますので
よろしくお願いいたします

回答No.3

BLUEPIXY · ベストアンサー

シートマクロで一応それなりに動くモノを作ってみました。
まだ、チェックが甘いかもしれません・
Private Sub Worksheet_Change(ByVal Target As Range)
Dim 日数 As Integer, x As Range

If Target.Row >= 3 And Target.Column >= 2 Then '作業日数を入れる領域である
If IsNumeric(Target.Value) Then '日数として数値が入力された
日数 = Int(Target.Value)
Call 色抜き(Target)
Call 色つけ(Target, 日数)
Target.Activate
Else
If Target.Value = "休" Then '"休"が入力された
If Target.Interior.ColorIndex <> xlColorIndexNone Then '既に色つけがされている（作業日である）
Set x = 数字のセルは(Target)
If Not x Is Nothing Then
Range(x, x.Offset(0, x.Value - 1)).Interior.ColorIndex = xlNone
Call 色つけ(x, x.Value)
Target.Activate
End If
End If
End If
End If
End If
End Sub
Private Sub 色つけ(セル As Range, 日数 As Integer)
'指定されたセルから右に日数分色つけする
セル.Activate
Do While 日数 > 0
If ActiveCell.Value <> "休" Then
ActiveCell.Interior.Color = RGB(&HFF, &HE4, &HE1)
日数 = 日数 - 1
End If
ActiveCell.Offset(0, 1).Activate
Loop
End Sub
Private Sub 色抜き(セル As Range)
'指定されたセルから右に色が付いていたら色抜きする
セル.Activate
Do
ActiveCell.Interior.ColorIndex = xlColorIndexNone
ActiveCell.Offset(0, 1).Activate
If Not IsEmpty(ActiveCell.Value) And IsNumeric(ActiveCell.Value) Then '他の作業日数指示が有る場合止め
Exit Do
End If
Loop While ActiveCell.Interior.ColorIndex <> xlColorIndexNone Or ActiveCell.Value = "休"
End Sub

Private Function 数字のセルは(セル As Range) As Range
セル.Activate
Do While ActiveCell.Column >= 2
ActiveCell.Offset(0, -1).Activate
If Not IsEmpty(ActiveCell.Value) And IsNumeric(ActiveCell.Value) Then
Set 数字のセルは = ActiveCell
セル.Activate
Exit Function
End If
Loop
Set 数字のセルは = Nothing
End Function

お礼

回答ありがとうございます

早速ためさせていただきましたら
考えていた通りの動作が確認できました。
解説文まで書いてくださって　本当にありがとうございます。

この内容を勉強させてもらって　活用させていただきます。
セルに日数と色指定を入力するバージョンに向けて
本当にためになりました
ありがとうございます

回答No.6

noname#52504

今更ですが、マクロを使わない場合を参考までに。

基本的な考え方は、
「シートをもう１枚使って計算（標示立て）はそっちでやり、名前を介して条件付書式で塗る。」
だけです。

以下、質問者様のレベルであれば必要以上にくどい記述かと思いますが、ご容赦くださいませ。

０．質問にあるような位置、様式の表がSheet1にあるとします。

１．Sheet1入力部分の表示形式を設定。

　　　"作業A" 0 "日間";"作業B" 0 "日間";"休";"えら～"

※作業Aについては正数で、作業Bについては負数で日数を、休日は0を入力する仕様。
文字列を入力すると"えら～"を表示。

※休日を0で入れる仕様は、テンキーだけで入力できた方が便利かなと思ったまでです。
将来他の表から参照で引っ張るような処理を考えるなら、空白セルとの混乱を避けるために文字列で入れるようにした方が良いかもしれません。

２．別のシート（Sheet2とします)のB3セルに数式を入力。

　　=IF(Sheet1!B3="",(ABS(A3)-1)*SIGN(A3),(Sheet1!B3=0)*A3+Sheet1!B3)

※休日を文字列で入れる仕様なら

　　=IF(Sheet1!B3="",(ABS(A3)-1)*SIGN(A3),IF(Sheet1!B3="休",A3,Sheet1!B3))

３．２の式を右・下方向にコピー

　　右方向：日数以上の列数
　　下方向：作業者数以上の行数

４．Sheet2の表に名前を付ける

　　・範囲は“A1セルから”、数式をコピーした部分の右下端まで
　　・以下、"WorkTable"という名前にしたとします。

５．Sheet1の入力部分に条件付書式を設定する

　　（アクティブセルがB3なら)
　　・条件1 数式が　=AND(B3<>"",B3=0)　書式：赤
　　・条件2 数式が　=INDEX(WorkTable,ROW(),COLUMN())>0 書式：緑
　　・条件3 数式が　=INDEX(WorkTable,ROW(),COLUMN())<0 書式：青

※休日を文字列で入れる仕様なら、条件1の数式はB3="休"でOK
※休日を塗らない場合も、条件だけは入れて下さい（書式は設定しない）

６．必要に応じて
　　・Sheet1を列抜きができないように保護する。
　　・入力規則を設定する。
　　・Sheet2を非表示にする。

Excel2003で動作確認

欠点
●Sheet1の列を抜くと参照エラーが起きます。
　回避するには、列を抜けないよう保護するか、
　Sheet1の見出し行をSheet2にリンク貼り付けしてLookUpするなどの対策が必要です。

●表示形式、名前、条件付書式…と色んな機能をつまみ食いするので、後から見て全体の構造が掴みにくい。

●日数・作業者数・PCのスペック等によっては、処理速度に問題が出るかも。

●Sheet2の数式で埋める部分は、日数・作業者数以上でなければなりませんが、
　随時増やすのは煩雑ですし、予め大量に埋めておくのはムダっぽい（苦笑
　表を拡張する機能だけマクロ化するのは本末転倒（笑

●原理的に作業の種類は３種類までです（条件付書式の限界）
　※文字列で入力＆作業シート２枚（作業種／日数）とか、或いはいっそコード化して桁別に演算すれば、３種類までは塗り分けられます。

●こういう表示形式の使い方は、仕様・運用方法が確定していれば結構便利ですが、思わぬ場面で痛い目にあうことがあります（経験者談）
特にオートフィルタとの相性は最悪です。

*************************************************
マクロを使うよりこっちの方が良いというつもりではありません。

・汎用性（つぶし、応用が利く）
・保守性（維持管理改良改造が楽）
・堅牢性（ミスや無知で壊れない）
いずれの点から考えても、マクロを使った方が絶対に賢いと思います（笑

ただ（幸いにして質問者様は該当しないようですが）
・セキュリティポリシー上の問題とか、
・セキュリティシステム上の問題（配布や有効化に伴う問題）とか、
・自分が退職したら引き継ぐ人間がいないとか（笑　←私の場合はこれ
マクロを使いにくい状況もままありますので。

乱長文スミマセン

お礼

お返事おそくなりまして
もうしわけないです。

関数でもこういうことが可能とは
想像もしていなかったので驚きです。

頑張って勉強していきたいとおもいます
大変参考になりました

回答No.5

banker_U

#4です。大変申し訳ないのですが、断念します。
すいませんでした。

以下の方法で、うまくいくかなあといろいろ考えたのですが、うまい方法は思いつきませんでした。

考えた方法：
１．基本的に全セルに計算式が入る。
２．休暇セルには計算式をいれずに数値で0を入力。
３．数字を入れたセルは計算式で右に行くに従って１づつ減る(最小１)。
４．休暇でもなく、塗りつぶしもしないセルは計算値が-1になるようにする。
５．そのうえで、「表示形式」を「ユーザー設定」で　0;;"休"　(場合によっては0も入れない)。
６．条件付書式で1以上を塗りつぶす設定をする。

という方法なんですが、計算式が結局IF文をネストさせる形しか思いつきませんでした。
これだと、#4でも書いたとおり、休暇の連続数に限界があるので、面白くありません。
MATCH関数か何かでうまくいくかなあと思ったのですが、
やっぱりだめでした。

お騒がせしてすいませんでした。
どなたかフォローできる方いらっしゃいましたらお願いします。

回答No.4

banker_U

もう回答が出ているようなので、なんだったら無視してもらってもいいです。

「休」の連続は最大何日ぐらいですか？
５日ぐらいまでなら、マクロを使わずに関数と条件付書式でいけそうな気もします。(それを超えると関数のネスト制限で多分アウト)
正直言って、この手の話はまさにイベント駆動型のマクロで解決すべきと思いますが、ちょっと面白いパズルとして関数で考えてみたいので、しばらく締め切らないで空けといてもらえませんか？重ねて言っときますが、失礼な話だと思ったら、無視してもらってもいいです。

補足

こんばんわ
「休」の連続する日数は5日超えるか超えないかで
微妙なところではあります。
実際のシフトを組む上での運用なので
絶対「超えません」ともいえない面がありますので。
私自身としましては
皆様からいただけるご回答は
すべてためになる内容ばっかりですので
お気になさらないで下さい。

回答No.2

BLUEPIXY

土日などは関係なく、「休」だけが休み（作業日を飛ばす）と考えていいのですか？

補足

さっそくありがとうございます。

言葉足らずでもうしわけありません。
カレンダー上の土曜日や日曜日、そして祝日祭日には一切
関係なく　作業者ごとに前もって入力されている
「休」だけを飛ばす（塗りつぶさない）ということになります。

回答No.1

ASIMOV

「休」が２日以上続く場合も有りですか？

補足

早速ありがとうございます
「休」が連続する場合もあります。
日数の入力数も2桁はありえます。