エクセルで複雑なシフト表の作り方

質問

こんばんは、エクセルで少し複雑なシフト表を作成したいです。
作り方（もしくはwebページの紹介）、フリーソフトがありましたら教えて頂きたいです。
・以下のような１週間の基本ファイルがあります。
横軸に日～土
縦軸に１・２・３で分けた時間のパターンがあり、交わるところに勤務場所別に分けた従業員の名前が記入してあります。
・従業員別のシフトカレンダー（普通のカレンダーに勤務場所と時間が書いてあります。）勤務場所別カレンダー（普通のカレンダーに従業員名と時間が書いてあります。）
基本ファイルから従業員及び勤務場所別のカレンダーがうまく反映するように作成したいです。
文章では説明が難しいのですが、なるべく簡単にシフトの変更などもボタンでできるようにしたいです。
そういった事に詳しいサイトやフリーソフトがありましたら教えて下さい。
よろしくお願いします！！！！

回答No.5

noname#79209 · ベストアンサー

勤務場所別のシフト表です。Sheet2にします。
フォーマットはSheet2と全く同じです。
従って、とりあえずSheet1の全てをSheet2へコピーします。

A1に勤務場所を入力します。
B1～H7までを削除します。
B2に、
=INDEX(Sheet3!$B$2:$H$57,+(COLUMN()-2)*8+ROW(),MATCH($A$1,Sheet3!$B$2:$H$2,0))
として、これをB2からH2までコピーします。
以上で終了です。

補足

長々とありがとうございます。
やってみます！！！

回答No.4

noname#79209

Sheet1：個人別シフト表です。
同じ人が、複数の時間帯、勤務場所があるため、一日内のシフト回数が不定となります。
このため、一般のカレンダーのような、日別の表示は無理です。
そこで、小学生の時間割のような表にせざるを得ませんでした。
イメージは以下のようになります。

　　Ａ　　　　　Ｂ　　　　Ｃ　　　　Ｄ　　　　Ｅ　　　　Ｆ　　　　Ｇ　　　　Ｈ
１　織田　　　日　　　 月　　　　火　　　 水　　　 木　　　　金　　　 土
２　　1　　　　尾張　　尾張　　尾張　　尾張　　尾張　　尾張　　尾張
３　　2　　　　信濃　　信濃　　信濃　　信濃　　信濃　　信濃　　信濃
４　　3　　　　甲斐　　甲斐　　甲斐　　甲斐　　甲斐　　甲斐　　甲斐
５　　4　　　　三河　　三河　　三河　　三河　　三河　　三河　　三河
６　　5　　　　安芸　　安芸　　安芸　　安芸　　安芸　　安芸　　安芸
７　　6　　　　尾張　　尾張　　尾張　　尾張　　尾張　　尾張　　尾張

A1に従業員の名前を入力します。
B1:=Sheet3!$A$2
C1：=B1+1 として、D1～H1へコピーし、B1～H1の表示書式を「aaa」に設定。
A2～A7にシフトの名前を入力しておきます。
B2：
=IF(ISNA(MATCH($A$1,OFFSET(Sheet3!$A$1,(COLUMN()-2)*8+ROW(),1,1,7),0)),"",INDEX(Sheet3!$B$2:$H$2,1,MATCH($A$1,OFFSET(Sheet3!$A$1,(COLUMN()-2)*8+ROW(),1,1,7),0)))
として、B2～H7までコピー。

でOKでしょう。
次は、勤務先別です。

回答No.3

noname#79209

> 勤務場所が二つある場合や同じ日に２つの時間帯で勤務している場合
基本的に1日当たりの勤務時間帯と勤務場所とのマトリクス表が基本となり、
これを曜日毎に作っておくことになるでしょう。
Sheet3：曜日別、勤務場所別シフト表
A1：=EOMONTH(TODAY(),-1)+1　として表示書式を「yyyy/m」
A2：=$A$1-WEEKDAY($A$1)+ROW()-1
として、表示書式に「aaaa」を設定します。
※「ROW()-1」の「-1」はデータが2行目から始まっているからで、3行目からなら「ROW()-2」となります。

同じ勤務時間帯に同じ人が入るとマズイので、条件付書式を設定して重複を発見しやすくしておきます。
B3～H8を選択しておき、「書式」－「条件付書式」で「数式が」として
「=SUMPRODUCT(($B3:$H3=B3)*1)>1」として、書式に「パターン」の「色」を指定して「OK」を押します。
これで、ヨコ方向に同じ名前があれば背景に色がつきます。

ここで、A2～H8を選択してコピーし、A10～A16へ貼り付けます。
次にA10の数式を、「=A2+1」に変更します。
あとは、A10～H16を選択してコピーし、
A18～H24、A26～H32、A34～H40、A42～h48、A50～H56へ貼り付けます。

　　Ａ　　　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　Ｈ
１　2007/11
２　日曜日　尾張　三河　駿河　甲斐　越後　信濃　安芸
３　１　　　織田　徳川　今川　武田　上杉　真田　毛利
４　２　　　今川　武田　上杉　真田　毛利　織田　徳川
５　３　　　上杉　真田　毛利　織田　徳川　今川　武田
６　４　　　毛利　織田　徳川　今川　武田　上杉　真田
７　５　　　徳川　今川　武田　上杉　真田　毛利　織田
８　６　　　織田　徳川　今川　武田　上杉　真田　毛利
９
10　月曜日　尾張　三河　駿河　甲斐　越後　信濃　安芸
11　１　　　織田　徳川　今川　武田　上杉　真田　毛利
12　２　　　今川　武田　上杉　真田　毛利　織田　徳川
13　３　　　上杉　真田　毛利　織田　徳川　今川　武田
14　４　　　毛利　織田　徳川　今川　武田　上杉　真田
15　５　　　徳川　今川　武田　上杉　真田　毛利　織田
16　６　　　織田　徳川　今川　武田　上杉　真田　毛利
17
というフォーマットです。

とりあえず、Sheet3の分をコメントしておきます。
続きを書きますので、まだ締め切らないで下さいね。

お礼

長々とご親切にありがとうございました。
仕上がってからお礼しようと思ったのですが、まだしばらくかかりそうなので一旦締め切らせて頂きます。また質問を見かけたらよろしくお願い致します！！
本当に感謝です！！！

回答No.2

noname#79209

Sheet3をマスター用シートとして、
Sheet3：
A1：=EOMONTH(TODAY(),-1)+1　として表示書式を「yyyy/m」
B2：=$A$1-WEEKDAY($A$1)+B$1　としてC2～H2へコピー
A3～A12を選択し、「勤務時間」と名前をつけておく。
３行目以下は下記のように入力しておく。
　　Ａ　　　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　Ｈ
１　2007/11　１　　　２　　　３　　　４　　　５　　　６　　　７
２　　　　　　　日 　　月 　　火 　　水 　　木 　　金 　　土
３　１　　　織田　徳川　今川　武田　上杉　真田　毛利
４　１　　　尾張　三河　駿河　甲斐　越後　信濃　安芸
５　２　　　今川　武田　上杉　真田　毛利　織田　徳川
６　２　　　駿河　甲斐　越後　信濃　安芸　尾張　三河
７　３　　　上杉　真田　毛利　織田　徳川　今川　武田
８　３　　　越後　信濃　安芸　尾張　三河　駿河　甲斐
９　４　　　毛利　織田　徳川　今川　武田　上杉　真田
10　４　　　安芸　尾張　三河　駿河　甲斐　越後　信濃
11　５　　　徳川　今川　武田　上杉　真田　毛利　織田
12　５　　　三河　駿河　甲斐　越後　信濃　安芸　尾張

Sheet1は、従業員別カレンダーとして、
Sheet1：
A1：=Sheet3!$A$1
B1：=Sheet3!B$2　として、表示書式を「aaa」と設定したうえで、C1～H1へコピー。
B2：=B$1　として、表示書式を「d」と設定したうえで、C2～H2へコピー。
B3：=IF(ISNA(MATCH($A$2,Sheet3!B$3:B$12,0)),"",INDEX(Sheet3!B$3:B$12,MATCH($A$2,Sheet3!B$3:B$12,0)+1,1))
とし、C3～H3へコピー。
B4：=IF(B3="","",INDEX(勤務時間,MATCH($A$2,Sheet3!B$3:B$12,0),1))
として、C4～H4へコピー。
B5：=B2+7　として、C5～H5へコピー。
B6～H7：B3～B4の内容をコピー。
B8～H16：B5～H7をブロックごとに、3行分づつコピー。

これで、B2に従業員名を入力すれば、自動的に各日付の下に勤務場所と勤務時間が表示されます。

sheet2は勤務場所別カレンダー
sheet2：
基本的にはSheet1と同じ。
3行目：
=IF(ISNA(MATCH($A$2,Sheet3!B$3:B$12,0)),"",INDEX(Sheet3!B$3:B$12,MATCH($A$2,Sheet3!B$3:B$12,0)-1,0))
4行目：
=IF(B3="","",INDEX(勤務時間,MATCH($A$2,Sheet3!B$3:B$12,0),1))
3行目～4行目を、6～7行目、9～10行目、12～13行目、15～16行目へコピーすればOK。
これで、B2に勤務場所を入力すれば、自動的に各日付の下に従業員名と勤務時間が表示されます。

一応、実験済ですがお望みの結果でなければご容赦。。。

補足

ありがとうございました。
ご教授頂いた内容で自分なりに作り変えてかなり作表が進みました。
ただ、色々考えてみたのですが、勤務場所が二つある場合や同じ日に２つの時間帯で勤務している場合のカレンダーへの反映方法が分かりません。何か方法はございませんでしょうか？
よろしくお願い致します。

回答No.1

noname#153814

少し整理させてください。
子供の学校の時間割のようなものでいいのでしょうか。
　日　　月　　火
　織田　徳川　豊臣
１尾張　三河　中村
　６～　７～　８～

　豊臣　織田　徳川
２中村　尾張　三河
　８～　６～　７～

＊基本ファイルというと、横軸に曜日、縦軸に数字だけの空白の予定表ですね。
＊で、たとえば水曜日に「１」と入力すれば、「徳川、三河、７～」と表示され、「２」と入力すれば「織田、尾張、６～」と表示さればいいのでしょうか。

補足

ご返答が遅くなってすみません。下記、おっしゃった通りです。

＊基本ファイルというと、横軸に曜日、縦軸に数字だけの空白の予定表ですね。
＊で、たとえば水曜日に「１」と入力すれば、「徳川、三河、７～」と表示され、「２」と入力すれば「織田、尾張、６～」と表示さればいいのでしょうか。

基本ファイルの週間データが月間のカレンダー２種に反映するという形です。できたら１日だけ時間変更なども何か表に入力したら反映するようにしたいです。
マクロになるのでしょうか？