【大至急お願いします！！】エクセルを使ってシフト表を作成したい

【大至急です！！】
エクセルを使ったローテーションの作成方法を教えてください！！
エクセル初心者です。

人事異動で以下のような窓口当番のローテーションを作成することになりました。

会社のパソコンのセキュリティ上フリーソフトは使用できず、また、私自身のパソコンスキルからエクセルを使用して作成するよりほかないと考えています。
（私自身はマクロは使えません。）

エクセルのバージョンは２０１０です。

どのような方法があるか詳しくお教え下さい。

１．1０名程度で２つの窓口を担当する。

２．１つの窓口に１名の担当者がつきます。

３．担当者は午前と午後で交代する。（＝２名×２名で１日つき４名が必要）

４．休暇や繁忙時期を考慮する必要があるため、適宜担当できない日を考慮する必要がある。（繁忙期や休暇というのは、人によって取得日が違うため個別対応が必要という意味です。）

５．担当者の経験が分かれるため、１０名を２グループに分け、なおかつ顔合わせもランダムになるようにしたいです。



ざっくりしているかもしれませんが、以上です。宜しくお願いします。

質問者からの補足コメント

• すみません、ご指示頂いたとおりにやったつもりですが、どうもうまくいきません。
  =If(C11<>"x",2-CountIf(C$10:C10,"x"),999)の部分が理解できません。
  １０名をまとめてやっているのですが、コピペなどで失敗しているのかもしれません。
  時間がないため何とか追加のご教示をいただけないでしょうか？

  No.2の回答に寄せられた補足コメントです。 補足日時：2015/04/04 22:12

• お忙しい中本当にありがとうございます。
  
  何とか形をなしてきたようですが、質問にお書きした通り、窓口が午前・午後各２名ある場合は各列にコピペして増やせば良いのでしょうか（どうも窓口必要人数と合いません）。
  また、知識がなく本当に申し訳ありませんが、お教えいただいた形の場合、何か入力するたびに再計算されるのですが、そもそもそうゆうものなのでしょうか？また、ちょうどいい塩梅の時に保存することは可能なのでしょうか？
  
  質問ばかりで本当にごめんなさい。
  他の方がお書きになっている通り、パソコンに詳しい方からすれば無茶な質問であることは理解しております。

  No.4の回答に寄せられた補足コメントです。 補足日時：2015/04/05 09:44

• 度々すみません。
  窓口を２名にするため、列を複写していったところ午後当番→同じ方が午前当番となってしまう事例が発生しています。
  そのようなやり方ではまずいのでしょうか？
  また、何度再計算しても各人の当番回数がかなりばらついてしまいます。
  その場合は手計算しかないのでしょうか。

    補足日時：2015/04/05 10:07

No.5 ベストアンサー 回答者： -Ken-Ken- 回答日時： 2015/04/05 14:24

>窓口が午前・午後各２名ある場合は各列にコピペして増やせば良いのでしょうか

いや、全員を2つのグループに分けてそれぞれのグループから1人ずつという風に理解していたので、その表は一人しか選びません。だって、経験によって2つのグループに分けるのですから、多分ベテランと新人のグループに分けるんでしょう？ですから、ベテラン用の表と新人用の表を2つつくってそれぞれから1人ずつ選ぶという使い方を想定しています。
もし一つの表で2人選ぶということであれば(もちろんそれが要求仕様なのですが)、根本的に作り替えなければならないので、申し訳ないですがお手伝いできないです。
ただ、別の方がアイデアをお持ちかもしれませんので、その「担当者の経験が分かれるため、１０名を２グループに分け」が具体的にどういう意味なのか捕捉されておくとよいでしょう。不躾ながら正直言うとベテラン用と新人用で分けてそれぞれから一人ずつ選べばいいんじゃないかなぁ、としか思えないのです。

>お教えいただいた形の場合、何か入力するたびに再計算されるのですが、そもそもそうゆうものなのでしょうか？
そうです。ですから、エクセルの設定を手動計算にする必要があります。
リボンに「計算」というタブがあります。そこに「計算方法の設定」という項目がありますから、そこで設定します。詳しくはこちらをどうぞ↓。
https://121ware.com/qasearch/1007/app/servlet/re …
再計算するにはF9を押します。

>午後当番→同じ方が午前当番となってしまう事例が発生しています
それはそうなると知っていました。午前と午後で交代するということだけだったので、日付が変われば午後と午前でつながってもいいという意味だと思っていました。でも午後-午前も一緒に禁止する方が実装するのは簡単です。Plan　Optimized　の部分は第一日目の午前を除いて、全部同じにすればいいです。つまり第一日目の午後をそのままま全シフトにコピーすれば午後-午前もなくなります。
ただ前の月の最後のシフトとの関係は人間が確認しなければならないです(これは前のバージョンでも同じ)。

>何度再計算しても各人の当番回数がかなりばらついてしまいます。
そうですね。それは手で調整することを想定しています。私の手元では5人の表を作ったので何回かやるといい感じのが出てくるのですが、それでも特定の期間にかたまってしまうというようシフト表になってしまいます。10人でやるとさらに理想的なシフト表ができにくいかもしれません。でもまるっきり白紙の状態から手で作るよりはかなり楽になるのと思うのですが。
また、本質的な解決方法じゃないですが、過去の3シフトに入っていた人からは選ばない、というような条件を付け加えると、少しはましになるようです。「過去の3シフト」の縛りを加えるには、Plan　Optimized　の項目で第2日目の午後シフトを　=If(CountIf(B24:D24,"√")>0,"x",If(E4="x","x","")) として下と右にコピーしていきます。ただし、この縛りを入れると、とても規則的なシフト表になるとか、誰も入れない日がいくつも出てくるとか、別の問題も出てきます。

>パソコンに詳しい方からすれば無茶な質問であることは理解しております。
私はそうは思いませんが、ただエクセルのファイルのままで渡せないとかいうのがありますので、こういう掲示板でやり取りするとちょっと時間かかるのはたしかですね。

この回答へのお礼

ご親切に本当にありがとうございます。
とりあえず何とか直近の分は完成しました。
頂戴したアドバイスをもとにさらに精度を高めてまいります。
本当に、本当にありがとうございました。

お礼日時：2015/04/05 18:13

No.4 回答者： -Ken-Ken- 回答日時： 2015/04/05 00:30

あ、10人でやってるんですか？

私、エラーの印としていっつも999を使う物ですから、大して考えもせずに999にしたのですが、実はこうすると8人までしか使えないのです。この場合。
そのことを直してB列の17行から21行までの数式を書いておきます。まず5人で私の添付した写真のように作ってから、各項目をそれぞれ5人ずつ新しい行を挿入して10人用を作った方が簡単ですよ。増やしたところは5人目のところから下にコピーするだけで使えるように作ったつもりです(17行目から21行は例外。ここはコピーした後に1,2,3,4,5のところを6,7,8,9,10...と直していかなければならない)。

B列の17行から21行までのは、これをB列にコピーして、残りの列はB列をコピーしていけばいいです。
=If(B10<>"x",1-CountIf(B9:B$10,"x");0)
=If(B11<>"x",2-CountIf(B$10:B10,"x");0)
=If(B12<>"x",3-CountIf(B$10:B11,"x");0)
=If(B13<>"x",4-CountIf(B$10:B12,"x");0)
=If(B14<>"x",5-CountIf(B$10:B13,"x");0)

ついでですからその後も書いておきます。
Allocatedの行
=MOD(Round(Rand()*B$15,0),B$15)+1

その下。
=If(B17=B$23,"√","")
=If(B18=B$23,"√","")
=If(B19=B$23,"√:,"")
=If(B20=B$23,"√","")
=If(B21=B$23,"√","")

No.3 回答者： tom04 回答日時： 2015/04/04 22:17

こんばんは！

お望みでないVBAになりますが・・・
ちょっとやってみました。

↓の画像のようにE列2行目から人員名を羅列しておきます。
そして、B・C列に組合せを表示するようにしてみました。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() '//この行から
Dim i As Long, k As Long, cnt As Long, lastRow As Long
Dim str1 As String, str2 As String
Dim c As Range, r As Range, mySet As Range, myRng As Range

'//組合せつくり（ランダムに表示）
cnt = 1
For i = 2 To Cells(Rows.Count, "E").End(xlUp).Row
For k = i + 1 To Cells(Rows.Count, "E").End(xlUp).Row
cnt = cnt + 1
Cells(cnt, "F") = Cells(i, "E") & "_" & Cells(k, "E")
Next k
Next i
lastRow = Cells(Rows.Count, "F").End(xlUp).Row
With Range(Cells(2, "G"), Cells(lastRow, "G"))
.Formula = "=rand()"
.Value = .Value
End With
Range(Cells(2, "F"), Cells(lastRow, "G")).Sort key1:=Range("G2"), order1:=xlAscending, Header:=xlNo
Range("G:G").ClearContents

'//窓口に割り振り
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(2, "B"), Cells(lastRow, "C")).ClearContents
cnt = 1
Do
cnt = cnt + 1
Set myRng = Cells(cnt - 1, "B").Resize(2, 2)
For i = 2 To Cells(Rows.Count, "F").End(xlUp).Row
str1 = Left(Cells(i, "F"), InStr(Cells(i, "F"), "_") - 1)
str2 = Mid(Cells(i, "F"), InStr(Cells(i, "F"), "_") + 1, Len(Cells(i, "F")))
Set c = myRng.Find(what:=str1, LookIn:=xlValues, lookat:=xlPart)
Set r = myRng.Find(what:=str2, LookIn:=xlValues, lookat:=xlPart)
Set mySet = Range("B:C").Find(what:=Cells(i, "F"), LookIn:=xlValues, lookat:=xlWhole)
If mySet Is Nothing Then
If c Is Nothing And r Is Nothing Then
If WorksheetFunction.CountA(Cells(cnt, "B").Resize(, 2)) = 0 Then
Cells(cnt, "B") = Cells(i, "F")
Else
Cells(cnt, "C") = Cells(i, "F")
End If
End If
End If
If WorksheetFunction.CountA(Cells(cnt, "B").Resize(, 2)) = 2 Then Exit For
Next i
If cnt = Cells(Rows.Count, "A").End(xlUp).Row Then Exit Do
Loop
End Sub '//この行まで

※　F列にはE列から重複がないように2人抜き出しランダムに表示しています。（すべての組合せ）
※　同じ人が2日連続しないようにしています。
※　すべての組合せが表示されるとループが終わるようにしていますので、
空白セルが出てくるかと思います。
※　質問の④に関してはExcel任せでは難しいので
目て見て人員配置を変更してみてください。m(_ _)m

No.2 回答者： -Ken-Ken- 回答日時： 2015/04/04 14:54

添付した写真みたいなシフト表ジェネレータを作ってみました。

こんな風に使うものです。
まず2グループに分けるのは手で分けて、それぞれにグループごとにジェネレータを作ることを想定しています。とりえず5人のグループとして作ってみました。シフトをランダムに入れていますので、それぞれのグループごとに作ったシフト表を合わせれば顔合わせはランダムになるはずです。
計算は手動計算にしておきます。シフト表を作るときにF9で再計算して、Allocationの部分を別のシートに値貼り付けでコピーして使います。√マークがシフトに入る人です。
Planのところには、入ることのできないシフトに「x」でマークしておきます。5人全部にマークすると誰も選びません。
ランダムにシフトを入れるのに、RAND関数を使っています。ところがこれ結構偏って値を吐き出す気がするんですよね。シフト表をリリースする前に特定の人にシフトが偏っていないかどうかチェックする必要があります。

次に各セルに埋めた計算式。1日めの午後シフト(C列)のBさんを例に説明します。
Planの部分は上に書いた通り手で記入する場所です。

Plan optimizedは基本は　=If(C5="x","x","") としてPlanの部分を転記しているだけですが、午前午後の連チャンを避けるために、午後シフトの部分については　=If(B26="√","x",If(C6="x","x","")) としています。つまり午前にシフトがはいっていたらその人を対象から外してxをつけているわけです。

Candidateの部分で、そのシフトに入ることのできる人の数を数えています。=CountIf(C10:C14,"")。この結果は後で使います。

No.の部分は、そのシフトに入ることのできる人に対し上から順に番号を振っています。対象外の人には999を表示するようにしました。=If(C11<>"x",2-CountIf(C$10:C10,"x"),999)。2-CountIf(C$10:C10,"x")の部分の2はBさんに固有の番号です。Aさんから順にこの部分は1､2､3､4､5となっています。だからBさんの場合は2です。

Allocationの部分が実際にリソースを割り当てているところです。
Allocatedの部分でそのシフトに入るべき人の番号を計算しています。=MOD(Round(Rand()*B$15,0),B$15)+1。具体的に書くと、Rand関数で0-1までの実数をランダムに選びそれにその日に入ることのできる人数をかけて0-(人数)までの実数にしています。その結果を四捨五入して0-(人数)までの整数に直します。さらにその結果をその日にシフトに入ることのできる人の数で割って、その余りを求め1を足してているのです。

で、その下の部分は、=If(C18=C$23,"√","")として、Allocatedの番号とNo.の部分でふった番号が一致する人に√でマークしているだけです。

上に書いた通り、Rand関数が完全にランダムな実数を吐き出しているような気はしません。また、四捨五入したり、余りを求めたりする過程でランダムさが崩れているかもしれませんが検証していません。
今手元でやってみると、なんとなく片寄のあるシフト表を作ります。そういうときは手動で調整する想定です。あるいはF9を押して計算しなおしてみてください。5回に一回くらいはちょうどバランスの取れたシフト表ができる気がします。

この回答へのお礼

素晴らしいご回答ありがとうございます。
ぜひとも活用させていただきたいと思いますが、なんせ初心者にはわかりにくく、このエクセルの表があれば最高ですね。

お礼日時：2015/04/04 21:05

No.1 回答者： hallo-2007 回答日時： 2015/04/04 11:32

＞適宜担当できない日を考慮する必要がある。

と
顔合わせもランダムになるようにしたい
＞とは。相異なる条件なので、パソコンには無理でしょう。
担当者の名前を入れる時に、管理者が色々と考慮しながら入力する事になります。

一例ですが
当番スケジュール　と云うシートを準備
　Ａ　　Ｂ　　　　　Ｃ　　　　Ｄ
日付　午前・午後　担当者１　担当者２
4/4　　午前　　　　Ａさん　　Ｆさん
4/4　　午後　　　　Ｂさん　　Ｇさん
・・・・
担当者名　と云うシートを準備
　担当者１　　担当者２
　Ａさん　　　　Ｆさん
　Ｂさん　　　　Ｇさん
・・・・
　Ｅさん　　　　Ｊさん

５名づつ、２組分けて名前を入れたシートを準備しておく。
Ｃ列には、担当者１のセルの範囲を張り付けていく
Ｄ列には、担当者２のセルを張り付けていきますが、
基本、一行ずらしながら、或いは、考慮しながら張り付けていく。
もし、１１名で、５名と６名でしたら、張り付けるときに
勝手にずれていきますけどね。

もう一枚、本日の当番表　と云うシートを準備して
特定のセルに、今日の日付をいれたら
今日の当番（午前と午後）の人の名前が表示されるように
関数を考える。

こんな感じでは如何でしょうか。