【Excel】月間シフトから日別シフトを自動作成したい

上図の月間シフトから、下図のような日別のシフトを作成したいと考えています。
社員は始業、終業時間を手入力、パートは右側の個別シフトからINDEXで表示させています。(0は休日です。)
・社員は変形労働制なので始～終の勤務(終に数字がない場合は9時間勤務)
・パートはDが10時～16時、Eが9時～15時半
以上の条件で、A1の日付を変更しただけで自動で下図のような表を作成するにはどうしたら良いのでしょうか？

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

• 日別シフトでは可能であれば出勤の従業員のみを表示したいと考えていました。
  複雑な条件設定が必要となるようであれば全員表示で出勤の者のみ色付けできるようにしたいです。

    補足日時：2018/04/29 10:53

• 日別シフトでは可能であれば出勤の従業員のみを表示したいと考えていました。
  複雑な条件設定が必要となるようであれば全員表示で出勤の者のみ色付けできるようにしたいです。

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/04/29 10:55

No.6 回答者： vide 回答日時： 2018/05/13 22:06

【 重要！！ 】

回答No.4,5 には、重大な欠陥があります。
また、回答者は、質問者が回答の数式の利用によって生じたあらゆる損害に関して一切の責任を負いません。

No.5 回答者： vide 回答日時： 2018/05/07 07:19

回答No.4【訂正】

※以下、シート2「日別シフト」を編集。
1. B3セルに、=IF(HLOOKUP(DAY(A$1),Sheet1!C:AG,ROW(B3)+ROW(B1)-1,FALSE)="D",10,IF(HLOOKUP(DAY(A$1),Sheet1!C:AG,ROW(B3)+ROW(B1)-1,FALSE)="E",9,HLOOKUP(DAY(A$1),Sheet1!C:AG,ROW(B3)+ROW(B1)-1,FALSE))) と入力。
2. C3セルに、=IF(HLOOKUP(DAY(A$1),Sheet1!C:AG,ROW(B3)+ROW(B1)-1,FALSE)="D",16,IF(HLOOKUP(DAY(A$1),Sheet1!C:AG,ROW(B3)+ROW(B1)-1,FALSE)="E",15.5,IF(OR(B3=0,HLOOKUP(DAY(A$1),Sheet1!C:AG,ROW(B3)+ROW(B1),FALSE)<>0),HLOOKUP(DAY(A$1),Sheet1!C:AG,ROW(B3)+ROW(B1),FALSE),B3+9))) と入力。

※こちらの数式でないと社員数の増大に対応できません。

No.4 回答者： vide 回答日時： 2018/05/06 16:07

※画像上が シート1「月間シフト」、画像下が シート2「日別シフト」、パートの就業形態「D,E」は半角とした。

※以下、シート2「日別シフト」を編集。
1. B3セルに、=IF(HLOOKUP(DAY(A$1),Sheet1!$1:$12,ROW(B3)+ROW(B1)-1,FALSE)="D",10,IF(HLOOKUP(DAY(A$1),Sheet1!$1:$12,ROW(B3)+ROW(B1)-1,FALSE)="E",9,HLOOKUP(DAY(A$1),Sheet1!$1:$12,ROW(B3)+ROW(B1)-1,FALSE))) と入力。
2. C3セルに、=IF(HLOOKUP(DAY(A$1),Sheet1!$1:$12,ROW(B3)+ROW(B1)-1,FALSE)="D",16,IF(HLOOKUP(DAY(A$1),Sheet1!$1:$12,ROW(B3)+ROW(B1)-1,FALSE)="E",15.5,IF(OR(B3=0,HLOOKUP(DAY(A$1),Sheet1!$1:$12,ROW(B3)+ROW(B1),FALSE)<>0),HLOOKUP(DAY(A$1),Sheet1!$1:$12,ROW(B3)+ROW(B1),FALSE),B3+9))) と入力。
3. B3:C3セルを、社員人数分オートフィル（下へ連続コピー）。
4. D3セルを選択し、[ホーム][条件付き書式][新しいルール][数式を使用して～]を選択し、「次の数式を満たす場合に値を書式設定」に、=AND(D$2>=$B3,D$2<$C3,$B3<>0) と入力して、書式を設定。
5. E3セルを選択し、[ホーム][条件付き書式][新しいルール][数式を使用して～]を選択し、「次の数式を満たす場合に値を書式設定」に、=OR(AND($B3<>0,D$2>=$B3,D$2<$C3,$C3-D$2<>0.5),$B3-D$2=0.5) と入力して、書式を設定。
6. D3:E3セルを、勤務表（グラフ）セル全体にコピー。
7. B2セルを選択して、[Shift][Ctrl]＋[↓]、もう一度[↓]で最下行まで選択して、[ホーム][並べ替えとフィルタ][フィルタ]を選択すると、B2セルにプルダウンメニューが表示される。
8. B2セルのプルダウンメニューから、[数値フィルタ][指定の値に等しくない]を選択し、「0」を入力。「と等しくない」が選択された状態で[OK]。
9. 日別シフトで、出勤の従業員のみが表示される。

No.3 回答者： vide 回答日時： 2018/05/06 14:38

検討中です。

No.2 回答者： まこと. 回答日時： 2018/04/30 02:16

出勤の従業員のみというのは、VBAとかで組まないと複雑になりそうです。

全員表示で良いなら、色付けするシートに各出勤者の始業時刻、終業時刻を持たせるようにして、その始業時刻、終業時刻を元に、条件付き書式で色を塗れば良いと思いますよ。

No.1 回答者： zab_28258 回答日時： 2018/04/29 08:55

最後の時間帯で社員Ａがどっか行っちゃいました？