質問: 【大至急お願いします!!】エクセルを使ってシフト表を作成したい 【大至急です!!】 エクセルを使ったローテーションの作成方法を教えてください!! エクセル初心者です。 人事異動で以下のような窓口当番のローテーションを作成することになりました。 会社のパソコンのセキュリティ上フリーソフトは使用できず、また、私自身のパソコンスキルからエクセルを使用して作成するよりほかないと考えています。 (私自身はマクロは使えません。) エクセルのバージョンは2010です。 どのような方法があるか詳しくお教え下さい。 1.10名程度で2つの窓口を担当する。 2.1つの窓口に1名の担当者がつきます。 3.担当者は午前と午後で交代する。(=2名×2名で1日つき4名が必要) 4.休暇や繁忙時期を考慮する必要があるため、適宜担当できない日を考慮する必要がある。(繁忙期や休暇というのは、人によって取得日が違うため個別対応が必要という意味です。) 5.担当者の経験が分かれるため、10名を2グループに分け、なおかつ顔合わせもランダムになるようにしたいです。 ざっくりしているかもしれませんが、以上です。宜しくお願いします。
回答: 添付した写真みたいなシフト表ジェネレータを作ってみました。 こんな風に使うものです。 まず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回に一回くらいはちょうどバランスの取れたシフト表ができる気がします。