EXCELでの月別 日別の勤務表同時作成について

質問

現在、ＥＸＣＥＬ２００７にて、シート１には月別のシフト表を年と月を変えると自動でその月の日にち、曜日も変わるように作成しております。縦軸には氏名、横軸には日にち、曜日を並べており、縦に各人の出勤、退社を記入させるように作成しております。このシート１をもとに、別シートで日にちを選ぶと、その日に入っている氏名と勤務時間をガントチャート形式（時間帯別）で、自動的に表示作成できるようにしたいのですが、関数がわからず、現在、苦戦しております。
どなたか、月別勤務表から、その月のある日にひ分だけを抽出し、その日に入っている者のシフト時間を表示できるようにするための方法を教えていただけないでしょうか？

シート１のサンプル
　××××年××月
　　　　　　　　　１日　２日　３日　４日・・・・
　　　　　　　　　月　　火　　水　　木・・・・・　　　
Ａ君　出勤　　9:00
　　　　退社　12:00
Ｂ君　出勤　　10:00
　　　　退社　　19:00

上記のような表で、たとえば、１日（月）にシフトに入っている者だけを抽出し、別シートで
日別のガントチャートを作成したいです

回答No.1

KURUMITO

シート1にお示しの表が有るとして、A1セルには例えば2012年8月と入力します。これで数式バー上では2012/8/1のように表示されますね。C2セルから右横の列には1日、2日・・のように表示させるわけですがC2セルに次の式を入力してBM2セルまでドラッグコピーします。

=$A$1+COLUMN(A1)-1

A4セルから下方には名前を1行おきに、B4セルから下方には出勤、退社が繰り返し入力されているとします。

A列の氏名について日付によっては休みで時間が空白の場合もあるでしょう。その日の出勤の状態を把握しやすいために作業列を作って対応することにします。
AI2セルに1、AJ2セルに２、・・・・BM2セルに31までを入力します。

=IF(MOD(ROW(A1),2)=1,IF(INDEX($C4:$AG4,COLUMN(A1))<>"",MAX(AI$3:AI3)+1,""),"")

その後にシート2にガントチャートを作成することにします。
例えば2012/8/1の日の表を作るのでしたらA1セルに2012/8/1と入力します。
2行目には例えば8:30から30分おきに時間を表示するとしてB2セルには8:30、C2セルには9:00、・・・AG2セルまで入力します。
その後に8月1日の出勤者の名前をA3セルから下方に表示させることにしてA3セルには次の式を入力して下方にドラッグコピーします。

=IF(OR($A$1="",COUNTIF(INDEX(Sheet1!$AI:$BM,4,MATCH(DAY($A$1),Sheet1!$AI$2:$BM$2,0)):INDEX(Sheet1!$AI:$BM,1000,MATCH(DAY($A$1),Sheet1!$AI$2:$BM$2,0)),ROW(A1))=0),"",INDEX(Sheet1!$A$4:$A$1000,MATCH(ROW(A1),INDEX(Sheet1!$AI:$BM,4,MATCH(DAY($A$1),Sheet1!$AI$2:$BM$2,0)):INDEX(Sheet1!$AI:$BM,1000,MATCH(DAY($A$1),Sheet1!$AI$2:$BM$2,0)),0)))

その後に条件付き書式を設定して該当の人が勤務している時間帯には赤色などの表示をさせることにします。そのためにはB3セルからAG列の下方、例えばAG100セルまでを範囲として選択します。
その上で「ホーム」タブから「条件付き書式」を選択し「新しい書式ルール」をクリックします。
「数式を使用して…」にチェックをして下の窓には次の式を入力します。

=AND(OR(B$2<>"",$A3<>""),AND(B$2>=INDEX(Sheet1!$C:$BM,MATCH($A3,Sheet1!$A:$A,0),MATCH($A$1,Sheet1!$C$2:$BM$2,0)),B$2<=INDEX(Sheet1!$C:$BM,MATCH($A3,Sheet1!$A:$A,0)+1,MATCH($A$1,Sheet1!$C$2:$BM$2,0))))

上の式を入力するにはどこかのセルに上の式を入力してそれをコピーして「貼り付ける」のがよいでしょう。
「書式」をクリックして「塗りつぶし」のタブから赤色を指定してOKします。

お礼

御礼が遅くなりまして、大変申し訳ございません。お教えいただきました関数で早速作成してみました。８割はできたのですが、最後の条件付書式の関数を
条件式書式の関数欄に書こうとすると、「条件付書式に他のワークブックまたはブックへの参照は使用できません」というメッセージが出まして、時間帯部分に
色表示や関数を記載することができないのですが、原因がお分かりでしたら
お教えいただけますと助かります。