スケジュールのシフトを簡単に作成したい

質問

販売の仕事をしていてシフト作成を毎月しているのですが
人数が11人分とかなり大変で作成と調整でとっても時間がかかるので
アナログ作業をなんとかデジタル処理できないかと考えています

実際に行っている作成要領としては、
１．あらかじめ個人別に希望の休みを固定して決定
２．指定休をまず５日連勤にならないようにある程度ふりわける
３．勤務の〆日が１５日（社員区分によっては１０日）なので
　　月の前半・後半で取れる休みが決まってきます
４．一日に必要な動員数を考慮しながら休みを調整します
５．他店の応援要請もありますのでそれも調整します
６．休みが決定したらシフト（早番、遅番等）を偏らないように振り分けます

５，６の調整が出間取ります
シフト作成のみならず他の仕事が山積みなのでかなりの負担になっています。

プログラミングはまったくやったことがなく
上記の条件はこんな関数をつかったら、などがわかれば
参考書を購入して作りたいと思うのですが、どのような内容の指導書が
あればいいのかすらわからない状況です

あと仕上がりのイメージとしては、
＊○月○日～×日までの指定休は何日ですか？
＊希望休を入力して下さい
などの条件を入力する画面から入力した後
自動計算でラフ案のスケジュールをつくり
そこから細かい調整ができる、という作業ができればいいなと思っています

必要な関数の種類と、ＶＢＡが必要だと思うのでこの項目が掲載している
指導書を使いなさい、等のアドバイスを宜しくお願いします

ちなみにプログラミングはおろか、エクセルも初級者ですが
独学するやる気はありますので、どなたか精通した方、ご伝授ください！！
はやくスケジュール作成の苦労から解放されたいんです…

回答No.1

nishi6

指定休の入力から考えだすと大変だと思います。また、プログラムしづらい要素が多いので、Excelを『作業の補助をしてくれる道具』にする方針で考えてみました。土日と指定休の関係が不明なため日曜日は休みで。指定休=1ヵ月に５日としてみました。Sheet1で処理をします。

○A2に月の1日を入力します。(2001/10/1のように)
　A3は　=A2+1　として、これをコピーしA32まで貼り付けます。A33に表題『指定休計』
　A2～A32まで書式を『月日』にします。これでA2に入力すれば日付が変わります。
　10月31日などがあれば消します。
○B2は　=A2　として、これをコピーしB32まで貼り付けます。
　B2～B32まで書式をユーザー定義で『(aaa)』にします。曜日の表示です。
○C1～M1までは氏名を入力(11名)
　日曜日には『***』などを入力しておきます。(入力しないための目印)
○C2～M32の範囲に範囲名 Kinmu を付けます
○N1に表題『休み』、N2に算式　=COUNTIF(C2:M2,"指定休")　N3からN32までコピー
○O1に表題『出勤者』、O2に算式　=COUNTA($C$1:$M$1)-N2　O3からO32までコピー
○P1に表題『必要動員数』。P2からP32については、その日の必要動員数を入力します。　
　P2に条件付き書式を設定(書式→条件付き書式)
　　『数式が』『=P2>O2』として、書式→パターン→例えば赤を選択、OK、OK
　　P2を選択してコピー。P3～P32に形式を選択して貼り付け→書式、OK
○Q1に表題『早番』、Q2に算式　=COUNTIF(C2:M2,"早番")　Q3からQ32までコピー
○R1に表題『遅番』、R2に算式　=COUNTIF(C2:M2,"遅番")　R3からR32までコピー
○C33に算式　=COUNTIF(C2:C32,"指定休")
　C33に条件付き書式を設定(書式→条件付き書式)
　　『セルの値が』『次の値に等しくない』、『5』として、書式→パターン→例えば赤を選択、OK、OK
　C33を選択してコピー。D33～M33に貼り付け(指定休を１ヵ月に5日とした)

これで、範囲名Kinmuの中に『指定休』と入力すると個人の指定休の日数計と各日の出勤者が計算されます。
同時に、出勤者＜必要動員数になるとP列のそのセルが赤くなります。個人の指定休計も5日にならないと赤のままです。
次に、『指定休』と入力する方法ですが面倒なので仕掛けを作ります。(Worksheet_BeforeRightClick)
ツール→マクロ→Visual Basic Editor でVBE画面に移動し、プロジェクトエクスプローラのSheet1をダブルクリックして出てきたコードウインドウに下の『Private～End Sub』をコピーして貼り付けます。
Sheet1に戻り、範囲名Kinmuの中で右クリックすると『指定休』と入力されると思います。既に『指定休』と入力されていれば、逆に消えるはずです。初期入力したあと右クリックをして調整して赤いセルが消えるようにします。

早番、遅番は『等』となっており、『偏らない』、『シフトの仕方(サイクル)』が不明なので早番、遅番についてもマウスで入力できるようにしてみました。ダブルクリックで、早番→遅番→未登録と切り替わります。(BeforeDoubleClick)
全てを１画面で見えるように調整すると作業がしやすいと思います。　ご参考に。

ツール→マクロ→Visual Basic Editor でVBE画面に移り、表示→プロジェクトエクスプローラでプロジェクトエクスプローラを表示し、Sheet1をダブルクリックして出てきたSHeet1のコードウインドウに下記モジュールを貼り付けます。(Private Sub～End Sub)

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
　　If Target.Cells.Count <> 1 Then Exit Sub

　　Dim rg As Range
　　Set rg = Application.Intersect(Range("Kinmu"), Target)
　　If Not rg Is Nothing Then
　　　　With Target
　　　　　　If .Value = "" Then
　　　　　　　　.Value = "指定休"
　　　　　　Else
　　　　　　　　.Value = ""
　　　　　　End If
　　　　　　Cancel = True
　　　　End With
　　End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
　　If Target.Cells.Count <> 1 Then Exit Sub

　　Dim rg As Range
　　Set rg = Application.Intersect(Range("Kinmu"), Target)
　　If Not rg Is Nothing Then
　　　　With Target
　　　　　　Select Case .Value
　　　　　　　　Case "": .Value = "早番": .HorizontalAlignment = xlLeft
　　　　　　　　Case "早番": .Value = "遅番": .HorizontalAlignment = xlRight
　　　　　　　　Case "遅番": .Value = ""
　　　　　　End Select
　　　　　　Cancel = True
　　　　End With
　　End If
End Sub

参考URL：

http://www2.odn.ne.jp/excel/waza/

お礼

さっそくのご回答いただいていたのにもかかわらず
質問をした後のPCの調子が悪く、お礼が大変遅くなりすみません。
急ぎで回答が欲しいといいつつ、忙しく、ぜひ近いうち時間を作って
チャレンジしてみます！
もし途中で分からなくなったら、補足の回答がいただけましたら幸いです
自信がないので…
またご報告させていただきます！！