エクセルで個人別に並んだ勤務表を日別にする場合の数式を教えてください。

エクセルで個人別に並んだ勤務表を日別にする場合の数式を教えてください。


現在表の上にあるような個人別の勤務表があります。
それとは別に下のような日にち毎の一覧もあります。
（今回は画像にする都合上同じシート内に載せましたがｌ本来は別々のシートです。）
上の表に時間を入れると同時に下の表にも反映するように数式？を入れました。

10/1に入れたのと同じような感じだったので、
そのまま他の日にちにも数式をコピーしたところ
当然ながら参照されるセルも動いてしまうので、うまくいきません。
これらを入力するには、１つずつ手で数式を入れていくしかないんでしょうか？？

あと、勤務のない日が「0：00」と表示されるのを空欄にするやり方も
併せて教えていただければ幸いです。

本当の表では25人分あり、それを30日分するにはかなりの労力が必要なため
詳しい方のお知恵お借りしたくて質問させていただきました。
シフトの提出期限が迫ってますので、若干焦り気味です(>_<)
よろしくお願いいたします。

No.3 回答者： KURUMITO 回答日時： 2010/10/05 11:02

次のようにすればよいでしょう。

お示しの上の表は仮に勤務表とシート名をします。
そこで各月にそれぞれシートを作るとして例えばシート1を画面に表示したのちにシート名見出しでシート12をＳhiftキーを押しながらクリックすることで同じ作業グループが形成されます。
そこでシート1について次の操作をします。
Ａ1セルには例えば2010/10/1のように入力して右クリックし「セルの書式設定」から「表示形式」の「ユーザー定義」で種類の窓には　yyyy"年"m"月分" とでも入力します。これで2010年10月分と表示されます。
次にＡ2セルには次の式を入力します。

=IF(MOD(ROW(A1),30)=1,IF(MONTH($A$1+INT(ROW(A1)/30))<>MONTH($A$1),"",$A$1+INT(ROW(A1)/30)),"")

なお、この式では25人分のということですので1日につき最大の人数を30人まで表示できるようにしています。
Ｂ3セルには出勤時間、Ｃ3セルには退勤時間と入力します。
Ａ4セルには次の式を入力します。

=IF(INDEX(勤務表!$1:$1,(MOD(ROW(A1),30)-1)*2+2)=0,"",INDEX(勤務表!$1:$1,(MOD(ROW(A1),30)-1)*2+2))

Ｂ4セルには次の式を入力してＣ4セルまでオートフィルドラッグします。

=IF($A4="","",IF(OR(COUNTIF(勤務表!$A:$A,INDIRECT("A"&MATCH(10^10,$A$1:$A2)))=0,A4=""),"",INDEX(勤務表!$A:$CA,MATCH(INDIRECT("A"&MATCH(10^10,$A$1:$A2)),勤務表!$A:$A,0),IF(COLUMN(A1)=1,MATCH($A4,勤務表!$1:$1,0),MATCH($A4,勤務表!$1:$1,0)+1))))

その後にＡ4セルからＣ4セルを選択してＣ4セルの右下隅にある■のオートフィルボタンをクリックしてＣ31セルまでドラッグします。

これが1日分の表になりますね。
次にＡ2セルからＣ31セルまでを範囲として選択したのちに右下隅にある■のオートフィルボタンをクリックしてＣ９３１セルまでドラッグします。

これによってその月々に応じた最終日までの表が出来上がります。
その後にＡ2セルからＡ９３1セルの表示形式を10月1日のように、また、Ｂ４セルからＣ９３１セルの表示形式を時刻で　9:00 のようにします。

以上ですべての作業は終わりますのでシート見出しを右クリックして「作業グループの解除」を選択します。すべてのシートに10月のデータが表示されますが、それぞれのシートのＡ1セルにまとめたい月の日付を2010/11/1のように入力すれば良いでしょう。

勤務表では毎日のデータをどんどん入力してゆくことでよいでしょう。
なお、勤務のない日を空欄にするとのことですが、勤務表でその日の日付を削除すれば空欄になります。式の上で直すこともできますが、後になって気が付いたのでごめんなさい。式には組みこんでありません。

No.2 回答者： hananoppo 回答日時： 2010/10/05 05:47

上の勤務表のシート名を「勤務表」とします。

また、下の日別一覧は別シートにあるものとし、そのA1セルに「10月1日」と入力されているものとします。

B3～C6セルには次の数式を入力します。

B3 =IF(VLOOKUP(A1,勤務表!$A$3:$I$7,2)="","",VLOOKUP(A1,勤務表!$A$3:$I$7,2))
C3 =IF(VLOOKUP(A1,勤務表!$A$3:$I$7,3)="","",VLOOKUP(A1,勤務表!$A$3:$I$7,3))
B4 =IF(VLOOKUP(A1,勤務表!$A$3:$I$7,4)="","",VLOOKUP(A1,勤務表!$A$3:$I$7,4))
C4 =IF(VLOOKUP(A1,勤務表!$A$3:$I$7,5)="","",VLOOKUP(A1,勤務表!$A$3:$I$7,5))
B5 =IF(VLOOKUP(A1,勤務表!$A$3:$I$7,6)="","",VLOOKUP(A1,勤務表!$A$3:$I$7,6))
C5 =IF(VLOOKUP(A1,勤務表!$A$3:$I$7,7)="","",VLOOKUP(A1,勤務表!$A$3:$I$7,7))
B6 =IF(VLOOKUP(A1,勤務表!$A$3:$I$7,8)="","",VLOOKUP(A1,勤務表!$A$3:$I$7,8))
C6 =IF(VLOOKUP(A1,勤務表!$A$3:$I$7,9)="","",VLOOKUP(A1,勤務表!$A$3:$I$7,9))

※ 実際は、エディタやワープロで1列分の数式を作成した後、まとめてコピーすればよいでしょう。

このように1日分の表を作成すれば、後はコピーするだけでよいです。

No.1 回答者： coro56_56 回答日時： 2010/10/05 03:51

無い知恵を振り絞ってみて、lookup関数で自分で作ってみましたが、３人でも結構大変でした。

ここで回答するには情報量（関数）が多すぎて書ききれないですし、
質問者さんがそれらを入力するのにも手間もかかりすぎてしまいます。

もっと素晴らしい解法があると思います。すみません。

一応３人だけの回答です。lookup関数と、$を使いました。

Ｂ14=LOOKUP($A12,$A$3:$A$7,$B$3:$B$7)
Ｃ14=LOOKUP($A12,$A$3:$A$7,$C$3:$C$7)
Ｂ15=LOOKUP($A12,$A$3:$A$7,$D$3:$D$7)
Ｃ15=LOOKUP($A12,$A$3:$A$7,$E$3:$E$7)
Ｂ16=LOOKUP($A12,$A$3:$A$7,$F$3:$F$7)
Ｃ16=LOOKUP($A12,$A$3:$A$7,$G$3:$G$7)

日別の欄の
Ａ１２の「日付」を変えると個人別の「日付」に対応しますので、１日分だけ全員の関数を入力すると、
コピーで３１日分すればＯＫです。

Ｂ14=IF((LOOKUP($A12,$A$3:$A$7,$B$3:$B$7))=0,"",LOOKUP($A12,$A$3:$A$7,$B$3:$B$7))

にすると、「0：00」が消えます。
が、さらに入力がたいへんです。