[[20150209033455]]　『シフト作成』（kenzo）　ページの最後に飛ぶ

『シフト作成』（kenzo）

シフト表にマクロ

エクセル2003のシフト表にマクロを用いたいと考えいます。

縦列Ｊ6からJ36までのセルには日付が入力されていて、
縦列 K6からK36までのセルには曜日が入力されています。
曜日は固定ではなく、毎月変わります。
横列Ｌ5からAR5には名前があり、Ｌ6：AR36までにシフトが入力されています。

 休みのマークは 空白　です
 出勤時は「1」「2」「10」などの数字が入力されています。
このようなファイルで
日曜日から土曜日までの7日間区切りで参照した場合に
 もしこの7日間に空白が無かった場合、
その7日間分のセルを赤色に変化させるようなマクロは組めますでしょうか？
まったくのど素人です。

 詳しい方ご教授願います。

＜使用 Excel：Excel2003、使用 OS：Windows7 ＞

 本件、おそらく　条件付き書式で対応できると思いますが、マクロがお望みということ？

（β） 2015/02/09(月) 06:52

言葉足らずで申し訳ありません。
現状　3つの勤務があり　1を入れると黄色　2で紫　それ以外が水色で塗りつぶしていて
条件尽き書式は3つ使っていてこれ以上はできません。
これで印刷をしているので上司も変えたくないそうです。
変則勤務で月に4～5日休みを取らせていたのですが、日曜日から土曜日の間に
必ず1日は休みを取らないといけなくなりました。
ＣＯＵＮＴＢＬＡＮＫ関数で1週ずつ空白があるか確認していますが
月がかわると日曜日の位置がずれ入力も少し手間になります。
マクロでやれればうれしいのですが
お願いします。

（kenzo） 2015/02/09(月) 11:18

 月初と月末は1週間がかけるために、判定できないと思いますがどうするのでしょうか。
 それとも日付は日曜日で始まるのでしょうか。
 そうであれば判定は固定で出来そうですが。
（Mook） 2015/02/09(月) 11:40

月初と月末では別シートを作成しており、前と同じくＣＯＵＮＴＢＬＡＮＫ関数で
空白を数えています。
あとMookさんの質問の　それとも日付は日曜日で始まるのでしょうか。の意味がよくわからないのですが
これは月の初めの1日が日曜日ということでしょうか
前任から引継ぎエクセルも初心者です。
（kenzo） 2015/02/09(月) 11:57

日曜日から土曜日までの7日、という縛りは必要ないように思います。例えば、水曜から翌週の水曜まで、
8日間連続だって、休みが少ないことに変わりがないですから。

この考え方で着色する例。

 Sub test()
    Dim i As Long
    Dim j As Long
    Dim iMax As Long
    Dim jMax As Long
    Dim iCou As Long
    Dim iSt As Long
    Dim iEd As Long

    iMax = Cells(5, Columns.Count).End(xlToLeft).Column
    jMax = Cells(Rows.Count, "J").End(xlUp).Row
    Range("L6", Cells(jMax, iMax)).Font.Color = vbBlack

    For i = 12 To iMax
        iCou = 0
        For j = 6 To jMax
            If Cells(j, i).Value <> "" Then
                iCou = iCou + 1
            Else
                iCou = 0
            End If

            If 6 < iCou Then
                iSt = Cells(j, i).End(xlUp).Row
                If iSt < 6 Then
                    iSt = 6
                End If
                iEd = Cells(iSt, i).End(xlDown).Row
                Range(Cells(iSt, i), Cells(iEd, i)).Font.Color = vbRed
            End If
        Next j
    Next i
 End Sub
（???） 2015/02/09(月) 12:29

 ＞現状　3つの勤務があり　1を入れると黄色　2で紫　それ以外が水色で塗りつぶしていて 
 ＞条件尽き書式は3つ使っていてこれ以上はできません。 
  条件付き書式の方が優先するので、
 　条件付き書式を完全に解除して、全セルにマクロで色づけするか、
 　赤を第一優先の条件付き書式にして、水色にすべきセルをマクロで付けるか
 しかないのでは？

 ＞月初と月末では別シートを作成しており
  これって面倒じゃないですか？
 MookさんがSuggestしたように、当月のスタートが日曜日でない場合は、
 前月最終日曜日からの表にした方が少し簡単になるのではないかと思いますけど、どんなもんなんでしょうか？

  ＞日曜日から土曜日までの7日、という縛りは必要ないように思います。 
 何曜日をスタートにするかは任意でしょうが、ぴったり4週間の月は滅多にないので、
 翌月初週の判定で同じ問題が生じると思うけどなぁ。

（半平太） 2015/02/09(月) 12:49

 先のコメントの補足ですが、

 >日曜日から土曜日の間に必ず1日は休みを取らないといけなくなりました。 
 というのは、
 4/1 2   3   4   5   6   7   8   9   10  11
 水  木  金  土  日  月  火  水  木  金  土
                 休 
 というときに、3/29(日)～3/31(火）に休みがなければ 4/1～4/4 は赤にしたいということ
 かを確認したかったのですが、

 >月初と月末では別シートを作成しており、
 ということのようですね。半平太さんからもコメントいただいたように、ちょっと面倒そうです。

 >それとも日付は日曜日で始まるのでしょうか。
 の意図は

 3/29 30  31  4/1 2   3   4   5   6   7   8   9   10  11
 日   月  火  水  木  金  土  日  月  火  水  木  金  土
 のように端数を前後の月に振って先頭が必ず日曜になるよう調整したカレンダーに
 するという意味で、書きました。
（Mook） 2015/02/09(月) 12:54

？？？さん　ありがとうございます。
ただ半平太さんのご指摘のとうり条件付書式が優先されているので
上司に相談して条件付書式を解除する方向でいこうと思います。
あと指摘の月初と月末では別シートを作成しており

  これって面倒じゃないですか？
自分自身でもかなり面倒だとおもっています。
なので半平太さんMooKさんの言っておられる先頭が
必ず日曜日になるようにしたいと思います。
それでそのようにした場合は半平太さんの
条件付き書式を完全に解除して、全セルにマクロで色づけするか、
赤を第一優先の条件付き書式にして、水色にすべきセルをマクロで付けるかの
方法がまったくわからないので教えてもらえませんか
あと申し訳ありませんが今から仕事にでかけるので
返信が夕方以降になります。
よろしくお願いします。
文字が大きくなったり小さくなっていますが気にしないでください。

（kenzo） 2015/02/09(月) 13:30

私の案では、背景色は条件付き書式で既に塗られている事から使えないため、文字色で表現しています。
だから、現状の条件付き書式はそのままで良いのですが、いかがでしょう？
（条件付き書式だけでは、最大3つしか指定できない制限がありますから）
（???） 2015/02/09(月) 13:45

？？？さん　説明不足で申し訳ありません。
例えば　1をいれて黄色で塗りつぶしていますが文字色も同じ色になるように
しています。
ですので条件式書式が優先されて色が変わりません。
試しに文字色の色を解除して使ってみたのですが
７連続勤務の場所は赤色に変わっています。
ですが週に一回(この場合は日～土)休んでいても色が変わっています。
(このマクロに関して意味が全く理解出来てないので申し訳ありません。）
後　横列のAT～BIに数式を入れてますが　そこも全部赤色に変わっています。
　　縦列の37行目～下にも式を入れてますが　そこも全部赤色に変わっています。
補足として1と2は背景色と同じ文字色が使われています。それ以外の数字は水色で文字色の指定はないです。
頭の中が？となっています。
（kenzo） 2015/02/09(月) 16:58

 １．方針
 　赤、黄色、紫は、条件付き書式で付ける
 　水色は、マクロで色づけする。
 　日付は、数式で生成する

 ２．方法
 (1)条件付き書式(対象範囲は、L6:AR42　となります
 　第１順位　赤色、条件式　=COUNTBLANK(OFFSET(L6,1-WEEKDAY($J6),0,7))=0
 　第２順位　黄色、従前と同じ方法なので省略
 　第３順位　紫色、同上

 (2)水色（マクロのChangeイベントを使って行います)
 　シフト表のシート見出しを右クリックして、「コードの表示」を選ぶと
 　画面中央にVBEの白いエリアが出ますので、後記プログラムを貼り付けてください。

 (3)日曜から始まる日付の自動生成
 　(1) J6セル =K5+1-WEEKDAY(K5)
 　(2) J7セル =J6+1
 　　　J7セルの数式をJ42までコピー

 <K5セルに　2015/4　と入力したところ>
  行  __J__  ___K___  ___L___  ___M___  ___N___
   5  日付   2015/4   氏名01   氏名02   氏名03 
   6  3/29   日            1        2        1 
   7  3/30   月            1                   
   8  3/31   火            1        2        2 
   9  4/1    水            1                   
  10  4/2    木            1                 2 
  11  4/3    金            1       10       10 
  12  4/4    土            1                 1

  34  4/26   日                              1 
  35  4/27   月           10       10        1 
  36  4/28   火            1        1        1 
  37  4/29   水                              1 
  38  4/30   木            2        2        1 
  39  5/1    金                               
  40  5/2    土                                
  41  5/3    日                               
  42  5/4    月

 ３．使い方、
 　月初になると、その時点にあるデータは前月のデータとなりますので、クリアする必要があるのですが、
 　クリアする前に最終週のデータを再利用したいので、最終週の日曜日から月末までをコピーして、
 　L6セルを先頭に貼り付けてください(値の貼り付け)。
 　それが、当月における前月最終週データとなります。
 　ただし、前月末が丁度土曜日なら、当月月初が日曜日なので、この作業は要りません。

 　次に、K5セルに当月の初日を入れる（通常、2015/4　と叩けば、2015/4/1に自動変換されます）
 　日曜日から始まる日付がJ6セル以下に表示されます。
 　
 　それから、月初から末日までのシフトデータ範囲をドラッグ選択して、Delキーでクリアすれば、
   月初の初期化処理は完了です。
 　
 　あとは、各人の当月シフトの数値を入れれば、その都度、条件付き書式と通常塗りつぶしで色がつきます。

 ’シフト表のシートモジュールに貼り付けるマクロ(↓)

 Private Sub Worksheet_Change(ByVal target As Range)
     Dim cel As Range

     If Not Intersect(target, Range("L5:AR42")) Is Nothing Then
         Application.ScreenUpdating = False
         　　Rem 塗りつぶし処理
             For Each cel In Intersect(target, Range("L5:AR42"))
                 If Not IsEmpty(cel.Value) Then
                     cel.Interior.ColorIndex = 8   '水色
                 Else
                     cel.Interior.ColorIndex = xlNone
                 End If
             Next
         Application.ScreenUpdating = True
     End If
 End Sub

（半平太） 2015/02/09(月) 21:05

 一言云い忘れました。

 水色のセルは、値が変化した時に色がつきます。(その方がレスポンスはいい）
 なので、既に入力されているセルには色は付きません。

 この為、追いつきで既存のセルに水色を付けるには、L6:AR42をコピーして、同じ所に貼り付けてください。
 それで、同じ値に「変化した」扱いになり、水色がつきます。

（半平太） 2015/02/09(月) 22:49

夜分遅く申し訳ありません。
半平太さん　ありがとうございます。
出来ました。毎回　先月月末分　当月分　来月月初分とシートをつくっていたのですが
これが１シートで処理でき　空白を一つずつ数えるという作業がなくなりました。
作業がかなり楽になりました。
Mookさん　？？？さんにもお世話になりました。
時間が時間ですのでこれにて失礼させて頂きます。

（kenzo） 2015/02/10(火) 02:39

コメント返信：

[ 一覧(最新更新順) ]

[[20150209033455]] 『シフト作成』（kenzo） ページの最後に飛ぶ

[[20150209033455]]　『シフト作成』（kenzo）　ページの最後に飛ぶ