[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複雑すぎて・・・助けてください』（ゆき）
下記のような表があります。
1月10日から16時間戻りたい
16時間をセルで色つけしたいです
1月31日から8時間戻りたい
8時間をセルで色つけ
1日を8時間としてください
1月10日と16時間のセルの入力したら
自動に色つけが出来る計算式？マクロ？方法を教えてください
1月1日 1月2日…1月5日…1月10日……1月31日
1月5日 1月10日 16時間
1月8日 1月31日 8時間
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
たとえば 1月5日 1月10日 16時間 と入力したら どこに色をつけたいのですか？ それと、1月5日 や 16時間 のセルは表示書式でそうしているだけで、中身は 日付型等の数値ですか？ それとも、本当に、そういう【文字列】なんですか？
（β） 2015/01/06(火) 10:11
ということは 1月5日 1月10日 16時間 の行では、A列の 1月5日は 無視 ですか？ ちなみに A列の 1月5日 というのは、どういう意味の値なんですか？
いずれにしても、本件、条件付き書式で対応できるかと思います。
（β） 2015/01/06(火) 10:40
時間に指定するのは、必ず8時間単位？ 10時間とか、違っていた場合はどうすれば？
1日が8時間で、必ず8時間単位でしか入力しないなら、引く値は何で日数にしないのです？
あと、何行何列に何が入力されているのか。結果は何列なのかを明確に。実例ももっと沢山。
色塗り対象セルは、1日が1列ですか？ 8時間単位なら、1日が3列だったりしませんか？
（???） 2015/01/06(火) 10:46
追加で。
1日8時間なら16時間というのは2日分ですよね。1月7日～1月10日 だと 4日間になりませんか？
12:17 一部訂正 3日間 -> 4日間
（β） 2015/01/06(火) 10:47
横から失礼します。 A B C D E ･･･ AH 1 1/1 1/2 1/31 2 1/5 1/10 16 3 1/8 1/31 8
2行目は1/5から2日後（1/7）から1/10 3行目は1/8から1日後（1/9）から1/31 に色を付けたいということですか？
あとC列は必ず8の倍数を入力するのですか？ （se_9） 2015/01/06(火) 10:49
あぁ、そういうことでしたか、A列の意味は。 であれば（C列が、必ず８の倍数だとして）
・D2:AH●までを選択し ・D2がアクティブな状態で ・条件付き書式 数式が=AND($A2+$C2/8<=D$1,D$1<=$B2) 色書式で好きな色を
これでいかがですか？ （β） 2015/01/06(火) 13:40
A B C D E ･･･ AH 1 1/1 1/2 1/31 2 1/5 1/10 16 3 1/8 1/31 35 4 1/25 1/28 6
説明不足で本当にすみません。
Ｄ～ＡＨのセルにに色を付けたいです
Ａ列は作業開始
Ｂ列は作業完了日
Ｃ列は作業時間
と考えて頂けると助かります。
一日の労働時間は8時間です
完了日までに終わるには最悪いつから作業を開始しなければならないかを
表現したいので
色つけ範囲
2列目は1/9～1/10
3列目は1/27～1/31 ← 35÷8＝4日と3時間なので、3時間は1日として考えたい
4列目は1/28 ← 8時間に満たないですが1日とする
これでお願いします
（ゆき） 2015/01/06(火) 13:58
ただし、そうなるとサンプルデータの場合、1/10や1/31は土曜日。除外するはずの日まで入力があり得るかどうかが問題。
実際に日付部分に土日を入力することはあるのですか？ 休日出勤とか？ この場合も含めた例を挙げてください。
開始が土日とか、終了が土日とか、両方土日とか、減算すると土日とか…。
だいぶ複雑な条件になりそうなので、これはマクロが良いかもですねぇ。
（???） 2015/01/06(火) 15:15
マクロじゃないと無理ですか？
私、エクセル初心者なのでマクロはなおさら？？？です。
（ゆき） 2015/01/06(火) 15:24
完了日が土日祝だった場合、何日さかのぼればいいのかの計算が複雑すぎると思います。
(ぱっとどのように計算すればいいか、思いつきません)
数式でやりたいのであれば作業列を作って、
完了日を入力した際にどこかで土日祝日を判定した方がいいかと。
(土日祝日に当たる場合は×にして、営業日の場合は○を表示するとか)
×になった場合は○になるまで日にちを入力し直さなければいけませんが、
まだ計算式は複雑にならずに済むかと思います。
ちなみに祝日判定は祝日の一覧表をどこかに作成しておく必要があります。
（どなみ） 2015/01/06(火) 15:49
WORKDAY関数の使い方についてはお調べください。
（とりあえず） 2015/01/06(火) 16:17
Sub test()
Dim i As Long
Dim iw As Long
Dim iC1 As Long
Dim iC2 As Long
Range("D1").CurrentRegion.Interior.ColorIndex = xlNone
For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
iC1 = DateDiff("d", Range("D1").Value, Cells(i, "B").Value) + 4
iC2 = iC1
iw = Weekday(Cells(i, "B").Value)
If iw = 7 Then
iC1 = iC1 - 1
ElseIf iw = 1 Then
iC1 = iC1 - 2
End If
iw = Application.RoundUp(Cells(i, "C").Value / 8, 0)
iC1 = iC1 - iw + 1
If iC2 < iC1 Then
iC1 = iC2
End If
Range(Cells(i, iC1), Cells(i, iC2)).Interior.Color = RGB(128, 128, 255)
Next i
End Sub
（???） 2015/01/06(火) 16:31
(とりえず)さんの式をこちらで設定して試しましたが B列 1/10 （土）あるいは 1/11 (日) で、C列 いずれも 8 にしたんですが 結果、いずれも、その日（1/10 あるいは 1/11)に色が付きました。
当方、関数は苦手ですが、やるとすれば以下のような式を条件付き書式にあたえます。
必要日数 ROUNDUP(必要時間/8,0)
完成必須日 IF(指定完成日が営業日なら,指定完成日,そうじゃなければ指定完成日の翌営業日の前の営業日)
指定完成日が営業日なら ・・ AND(AND(WeekDay(指定完成日)>=2,WeekDay(指定完成日)<=6),COUNTIF(祭日,指定完成日)=0) 翌営業日や前営業日はWorkday関数で。
開始必要日 WORKDAY(完成必須日,-(必要日数-1),祭日)
で、その日（１行目の日）が開始必要日以上で、完成必須日以下なら色づけ
これらをすべてつなぎ合わせると、ずいぶん長い式になりそうです。
（β） 2015/01/06(火) 17:16
14:02に土日祝日を除くという条件が追加されましたが、
上の例示から、土曜が作業完了日になることはあるようですので、
そのような数式を組んだつもりです。
つまり、土曜は作業完了日の対象となるが、作業日の対象とはならない。
作業完了日が日曜になることは考慮していません。
まぁ、とりあえずの式ですので ^^
（とりあえず） 2015/01/06(火) 17:46
例にもありますが1/10は土曜日になります。
そうなると作業完了は1/9にしてほしいので
作業開始は1/8になります
（とりあえず） 2015/01/06(火) 18:06
祭日なし
=AND(D$1<=WORKDAY(WORKDAY($B2,INT(WEEKDAY($B2,2)/6),),-INT(WEEKDAY($B2,2)/6),),D$1>=WORKDAY(WORKDAY(WORKDAY($B2,INT(WEEKDAY($B2,2)/6),),-INT(WEEKDAY($B2,2)/6),),-FLOOR($C2/8,1)+(MOD($C2,8)=0),))
祭日あり（祭日の場所あってるか自信なし）
=AND(D$1<=WORKDAY(WORKDAY($B2,INT(WEEKDAY($B2,2)/6),祭日),-INT(WEEKDAY($B2,2)/6),祭日),D$1>=WORKDAY(WORKDAY(WORKDAY($B2,INT(WEEKDAY($B2,2)/6),祭日),-INT(WEEKDAY($B2,2)/6),祭日),-FLOOR($C2/8,1)+(MOD($C2,8)=0),))
（とりあえず） 2015/01/06(火) 18:29
VBAはいやかもしれませんが、VBAです。 シートタブを右クリックしてコードの表示を選んだところにコピペしてください。 リスト内のどこかに入力されれば自動実行します。 なお、コードの最初のほうで祭日リストを設定しています。ここは実際のシート名と場所にしてください。 また、リストの最終行の下には少なくとも１行の空白行、リストの最終列の右には少なくとも１列の空白列があるという前提のコードにしています、
試して不具合あれば指摘願います。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Dim cY As Range
Dim cX As Range
Dim a As Range
Dim xDate As Long '指定完成日
Dim xTime As Long '必要時間 整数で入力されていると想定。
Dim xDays As Long '必要日数
Dim zDate As Date '完成必須日
Dim sDate As Date '開始必要日
Dim d As Long
Dim tDate As Date '一行目の日付
Dim hList As Range '祭日リスト
Set hList = Sheets("holiday").Range("A1:A20") '★実際のシート名と場所に変更
With Target.Parent.Range("A1").CurrentRegion
If Intersect(Target, .Cells) Is Nothing Then Exit Sub
Set r = .Offset(1, 3).Resize(.Rows.Count - 1, .Columns.Count - 3)
End With
r.Interior.ColorIndex = xlNone
For Each cY In r.Columns(1).Cells
xDate = cY.Offset(, -2).Value
xTime = cY.Offset(, -1).Value
xDays = WorksheetFunction.RoundUp(xTime / 8, 0)
d = Weekday(xDate)
If d >= vbMonday And d <= vbFriday And WorksheetFunction.CountIf(hList, xDate) = 0 Then
zDate = xDate
Else
zDate = WorksheetFunction.WorkDay(xDate, 1, hList)
zDate = WorksheetFunction.WorkDay(zDate, -1, hList)
End If
sDate = WorksheetFunction.WorkDay(zDate, -(xDays - 1), hList)
For Each cX In Intersect(cY.EntireRow, r)
tDate = cX.EntireColumn.Range("A1").Value
If tDate >= sDate And tDate <= zDate Then cX.Interior.Color = vbCyan
Next
Next
End Sub
（β） 2015/01/06(火) 19:02
To (とりあえず)さん
こちらで用意したテストデータに対して新しい祭日含みの式で条件付き書式設定しましたら、 ↑でアップした私のコードと同じ結果になりました。 (とりあえず)さんの式が正しければ私のコードも正しいということで、ほっとしてます。
To (ゆき)さん
ということで、VBAが、おいやなら、条件付き書式でどうぞ。 （β） 2015/01/06(火) 19:10
うーん。
（とりあえず） 2015/01/06(火) 22:53
βさんへ
ＶＢＡの日本語の部分はそのままでいいのですか？
それと
'★実際のシート名と場所に変更 のところは、Sheet1G12:L15 みたいな書き方でいいのですか？
とりあえずさんへ
祭日 のところは祭日リストを選択ですよね？
（ゆき） 2015/01/07(水) 09:10
「ＶＢＡの日本語の部分はそのままでいいのですか？ 」
はい。 もっとも、これを海外でも使おうという予定があれば、日本語が化けるかもしれませんし 何よりも、日本語がわからない人には困ったコメントになりますが。
「★実際のシート名と場所に変更 のところは、Sheet1G12:L15 みたいな書き方でいいのですか？」
Set hList = Sheets("holiday").Range("A1:A20")
↓
Set hList = Sheets("Sheet1").Range("G12:L15")
（β） 2015/01/07(水) 10:49
A2 作業開始日
B2 作業完了日
C2 作業時間
D2 完成必須日
E列以降1行目 日付
条件付き書式
=AND(E$1<=$D2,E$1>=WORKDAY($D2,-FLOOR($C2/8,1)+(MOD($C2,8)=0),祭日))
祝日リストの範囲に 祭日 と名前定義してもらえば、そのまま使っていただくことも可能かと。
（とりあえず） 2015/01/07(水) 10:54
D2=WORKDAY(WORKDAY(B2,IF(OR(WEEKDAY(B2,2)>5,COUNTIF(祭日,B2)),1,0),祭日),-IF(OR(WEEKDAY(B2,2)>5,COUNTIF(祭日,B2)),1,0),祭日)
（とりあえず） 2015/01/07(水) 10:56
「祭日 のところは祭日リストを選択ですよね？」
老婆心ながら。 たとえば G12:L15 の範囲に適用列あるいは見出し行なんかがある場合、私のVBAコードではOKですが 数式として与える「祭日」の範囲は「日付」の部分のみを指定してください。
参考
http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu/workday.html
http://kokodane.com/kan28.htm
（β） 2015/01/07(水) 11:08
横から失礼します
(とりあえず)さんの式、レイアウトをもとのままにして、数式をコンバインすると
=AND(D$1<=WORKDAY(WORKDAY($B2,IF(OR(WEEKDAY($B2,2)>5,COUNTIF(祭日,$B2)),1,0),祭日),-IF(OR(WEEKDAY($B2,2)>5,COUNTIF(祭日,$B2)),1,0),祭日),D$1>=WORKDAY(WORKDAY(WORKDAY($B2,IF(OR(WEEKDAY($B2,2)>5,COUNTIF(祭日,$B2)),1,0),祭日),-IF(OR(WEEKDAY($B2,2)>5,COUNTIF(祭日,$B2)),1,0),祭日),-FLOOR($C2/8,1)+(MOD($C2,8)=0),祭日))
これで試してOKでした。
以上、ご報告まで。
（β） 2015/01/07(水) 19:58
条件付き書式はこんなのでいいと思います。
=MEDIAN(WORKDAY($B2+1,INT(-$C2/8),祭日),D$1,WORKDAY($B2+1,-1,祭日))=D$1
（半平太） 2015/01/07(水) 21:01
※INT()は無くてもよかったです (^^ゞ =AND(WORKDAY($B2+1,-$C2/8,祭日)<=D$1,D$1<=WORKDAY($B2+1,-1,祭日)) ＜追記＞2015/01/08(木) 10:27
ただ、1/3～1/20の22時間としたときに1/17と1/18にも色がついてしまします。
つかないようにするにはどうしたらいいのでしょうか？
（ゆき） 2015/01/13(火) 14:02
ルールが不明瞭な気がするのですが、結局、土日祝祭日は全部つけないと云うことなんですか？
であれば、これ ↓ =AND(WORKDAY($B2+1,-$C2/8,祭日)<=D$1,D$1<=$B2,WORKDAY(D$1+1,-1,祭日)=D$1)
（半平太） 2015/01/13(火) 16:24 <追記> 不要化した条件を修正しました。2015/01/13(火) 19:17
出来ました。有難うございます。
もう一つ追加条件なんですが・・・
たまに（規則性はないので祭日と同様にどこかに打ち込みが必要とは思うのですが）
土曜日出勤があるので、その日に該当するときは色をつけたいのですが無理でしょうか？
（ゆき） 2015/01/14(水) 16:20
＞たまに（規則性はないので祭日と同様にどこかに打ち込みが必要とは思うのですが） ＞土曜日出勤があるので、その日に該当するときは色をつけたいのですが無理でしょうか？
無理と云う程ではないですが、面倒な条件式になります。 土／日に出勤するリストが「土日出勤」と云うリストに打ち込まれているとして、これ(↓)
=MATCH(D$1,LARGE(-99*(COUNTIF(祭日,$B2+1-ROW($C$1:$C$49))+(WEEKDAY($B2-ROW($C$1:$C$49))>5))*(COUNTIF(土日出勤,$B2+1-ROW($C$1:$C$49))=0)+$B2+1-ROW($C$1:$C$49),ROW(INDIRECT("A1:A"&CEILING($C2/8,1)))),0)
※ 戻る時間の最大値は、1箇月相当の「248時間」以内と想定してのものです。
（半平太） 2015/01/14(水) 19:49
※ 戻る時間の最大値は、1箇月相当の「248時間」以内と想定してのものです。
とありますが・・・
1月31日で終わりではなく横にずーっと続きます。（12/31まで）
そうなると条件式はかわりますか？
（ゆき） 2015/01/19(月) 09:52
＞>※ 戻る時間の最大値は、1箇月相当の「248時間」以内と想定してのものです。 ＞とありますが・・・ ＞1月31日で終わりではなく横にずーっと続きます。（12/31まで）
1行目の日付じゃなく、C列の「戻る時間」の話ですよ？
現実的にはセイゼイ50時間くらいと思っております。 ※過去最大が何時間か調べたことありますか？
（半平太） 2015/01/19(月) 12:14
=MATCH(D$1,LARGE(-99*(COUNTIF(祭日,$B2+1-ROW($C$1:$C$49))+(WEEKDAY($B2-ROW($C$1:$C$49))>5))*(COUNTIF(土日出勤,$B2+1-ROW($C$1:$C$49))=0)+$B2+1-ROW($C$1:$C$49),ROW(INDIRECT("A1:A"&CEILING($C2/8,1)))),0)
この式の
ROW($C$1:$C$49)
と
"A1:A"
の意味を教えてください。
（ゆき） 2015/01/19(月) 13:52
ROW($C$1:$C$5) だったら配列の{1;2;3;4;5} と同じです。
・・なので、ファクターが49個まである配列になる、と云うことです。 幾つにするかは回答者としてのセンスで決めています。(テキトーってことです） これへの回答がないので、これ以上コメントできません。 ↓ ＞ 現実的にはセイゼイ50時間くらいと思っております。 ＞ ※過去最大が何時間か調べたことありますか？
"A1:A" は単なる文字列です。 そこだけ切り取っても意味をなしません。
INDIRECT関数の引数に仕立て上げ、
セル範囲を取得しようとするものです。
※INDIRECT("C1:C3")なら、C1:C3のセル範囲と同じです。
何故そんな回りくどいことをするかと言えば、 上と同様、最終的にはRow関数の引数にして、 配列を作るためですが、こちらはテキトーな行数ではまずく、 色付ける日付の数と合わせないとならないので、 そんな形で処理しています。
（半平太） 2015/01/19(月) 16:42
数式入れたのですが#NUM!になってしまいます。
下記の表が正式な分です。
縦はそのつどずーっと増えます。（1年分なので）
A B C D E,F G,H ･･･ ABG,ABF 4 名前 開始 完了 時間 1/1 1/2 12/31 5 6 あ 1/5 1/10 16 7 い 1/8 1/31 35 8 う 1/25 1/28 6
どこが違うのかわからないで苦戦しています。
上記の表で数式をお願いします。
（ゆき） 2015/01/20(火) 10:51
なかなか解決しないですねぇ。。
当初の質問からだいぶ変貌しておりますので、 フレッシュな頭で解決策を考えられる人の回答がいい様な気がします。
なので、私はこれでドロップアウトいたします。
他の方のレスが付くまで、しばらくお待ちください m(__)m
（半平太） 2015/01/20(火) 14:43
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.