日別の出退勤時間から時間別のシフト表を動的に作成したい

Question

お世話になります。

日別の出退勤時間を元に時間別の滞在状況を表にすることは可能でしょうか？

AND関数を使って「対象セルの時間が出勤時間より大きく、退勤時間より小さい」ときに条件付き書式で色をつけることで、時間別の滞在状況が把握できるかも、と思って下記リンクのような状態まで作ったのですが、日別と時間別で1行の単位が違うので単純にオートフィル的にコピー（形式を選択して貼り付けで「書式」を指定）すると時間別でも1行ごとに日が変わってしまうので、おかしなことになってしまいます。

数年分一気に作りたいため、手作業でコピーするのは現実的でなく、何かいい方法がないかご教示いただけるとありがたいです。

よろしくお願いいたします。

出退勤表

（環境：office365）

** モデレーター注 **

この質問は [Office | Excel | Windows 10 | Office 365 for business] のカテゴリーに投稿されましたが、内容から判断し、こちらのカテゴリに移動いたしました。適切なカテゴリーに投稿すると、返信や回答が得られやすくなり、同じ質問を持つ他のユーザーの参考にもなります。

Answer 1

添付ファイルのレイアウト（時間別シートのB列が日付シリアル値）なら時間別シートのDセル以下の条件付き書式を設定したい範囲を選択して以下の数式を使った条件付き書式を使用すれば、ご希望の範囲にまとめて色を塗りつぶすなどの設定ができます。

=AND(INDEX(日別!$A:$V,MATCH($B2,日別!$A:$A,0),MATCH(D$1,日別!$1:$1,0))<=$C2,INDEX(日別!$A:$V,MATCH($B2,日別!$A:$A,0),MATCH(D$1,日別!$1:$1,0)+1)>$C2)

ただし、9:00～12:00ならC列が9、10、11の3つのセルが塗りつぶされます。

16:30から19:00のような区切りの時間がない場合は、17、18のセルが塗りつぶされますので、30分単位にしたい場合はC列の値を30分単位にしてください。

Answer 2

解決しました。

凡ミスで、関数を項目名が記入されている1行目から入れていたため、1行上にずれていただけのようでした。

これで作りたかったものが完成し、時間別の滞在人数が一目で把握できるようになりました。

本当にありがとうございます！

Answer 3

お答えいただきありがとうございます。

ご指摘のとおり、30分の端数と休憩時間が今のままでは曖昧ですね。

やはりVBAのレベルになってくるんですね。

それなら地道に手作業でやるほうが結果的に早いかもしれないので、そちらで進めてみようと思います。

その踏ん切りがついただけでも相談させてもらってよかったです。

ありがとうございました。

Answer 4

指摘のとおり、

日別シートが1行/日、時間別シートが14行/日ですので、

オートフィル的な操作で省力化するのは困難と思います。

また、

小玉さんには16:30:00といった業務開始時間があります。、

この場合に、

時間別シートの16:00～17:00のセル（２３行目）をどのように染めるのか

はっきりしません。１つのセルの下半分だけ染めることはできませんから。

さらに昼休みは無いのか？も気になります。

おそらく、VBAを駆使しないと実現できない要求と思います。

少なくとも私は、VBAを使わない解が思いつきません。

Answer 5

すみません、リンクのファイルは「最終的にこんな感じにしたい」というものを静的に再現したものです。

（動的にすると現在の作りでは表示がおかしくなるので）