Excelでシフト表を作成したときの時間別に稼働人数を数える方法

はじめましてオリたんといいます。

会社でExcelを使ってシフト表を作成することになったのですが

わからないことができてしまい質問をさせてもらうことにしました。

お忙しいみなさんのお時間を頂戴してしまうことをお許し下さい。

質問の内容は2点です。実際に使用するシフト表の画像を添付します。

Excelでシフト表を作成するときに時間別に何人が稼働(働いている人数)しているかをカウントしたいのですが
やり方が分かりません。

1点目　例えば午前8時～午前9時には何人が稼働しているかを確認するためにはどうすればよいでしょうか？

画像のセルを見てもらったらわかるように

セルB6　出→10(10：00)　出勤時間
セルC6　退→17(17：00)　退勤時間

となっています。

ちなみに時間別の稼働人数を求めたい場所はセルB32以降またはセルC32以降になります。

出と退のところの数値表示は変更せずにお願いします。(出10　退17　という表示のまま)

2点目　12時から17時には何人が稼働しているかを確認するためにはどうすればようでしょうか？

1点目と2点目とも
同じような数式になるのかもしれませんが

それすらもわからない素人です。

すみません。
みなさんのお力添え、どうかよろしくお願いします。

1の質問では
8時から24時の勤務時間に対して1時間ごとに稼働人数を求めたいということです。

2つ目の質問は

8時から10時

10時から14時

のような数時間ごとに何人が稼働しているかが知りたいです。

No.1 回答者： rukaandkaito 回答日時： 2019/09/17 17:06

該当の関数が存在するか分からないので、自分ならこうするという案を記載します

欄外（この場合W以降）に時間ごとの判定式を挿入し後は縦列をSUMするだけです
モチロン範囲は非表示等にしておきますが、例えば
W５＝８、Ｘ５＝９と判定したい時間の範囲を固定文字列で指定しておきます
Ｗ６＝IF(AND($B6<=W$5,$C6>=W$5),1,0)
あとは指定時間の範囲まで横にフィルして、３１行目までフィルすれば３２行目以降にはSUM関数のみで合計が求められます。
また、時間範囲にて判定を行いたい場合には２列にそれぞれ開始時間と終了時間を指定しておき、ANDの後ろの判定式の参照を変えれば対応可能です
W５＝９、Ｘ５＝１２
Ｗ６＝IF(AND($B6<=W$5,$C6>=X$5),1,0)
といった具合ですね

もっと便利な関数があるかもしれませんが取り急ぎです・・・

No.2 ベストアンサー 回答者： fujillin 回答日時： 2019/09/18 17:06

こんにちは

ご提示の、表の見方がはっきりとはしませんが、各行が一人の人を表していてB、C列が出退の時刻、これが縦に複数人分並べられているものと解釈しました。
縦に見て行って、ある時間帯に何人の人が勤務中かをカウントしたいというご質問と解釈しました。


時刻の数字がシリアル値か単なる数値か不明ですが、雰囲気から単純な整数と仮定しました。
（シリアル値の場合は、比較する値を全部シリアル値にすれば、同じ要領で計算できます）

ご質問の添付図では、複数の日にち分が並んでいますが、ある一日について求められれば、あとは応用可能と思いますので、以下の添付図は1日分のみのモデルで、残りは説明用に仮の表記に利用しています。

ご質問の表に合わせて、B6：C6以下に出退データが並んでいるものとます。
結論から先に書くと、調べたい時間帯をF1:F2に入力するものとして。（図では8～12）
H1セルに
　=SUMPRODUCT(($B$6:$B$50<F2)*(F1<$C$6:$C$50)*1)
の式を入力しておくと、8時から12時の間で（一部の時間帯の人も含む）勤務している人数が表示されます。

注1）「8～12時」の意として「8：00～11：59まで」と解釈して計算しますので誤解なきように。
注2）「8～12時」の間に途中から出社したり退社した人も全て数えます。



計算上の解釈と式の説明を、E列以降の下方（色付けした範囲）に示してあります。
E7から右は時刻を示し、各行の人の勤務中であれば○を示しています。
　E6セルに　=IF(AND($B6<=E$5,E$5<$C6),"○","")
6行目（11時～17時）の場合、11～16まで○表示で17は含みません。

この、○の数をcountifで数えたのが15行目です
　E15セル：　=COUNTIF(E6:E14,"○")
これに対して、1時間ごとの集計を直接求めた式が１７行目です
　E17セル：　=SUMPRODUCT(($B$6:$B$50<(E5+1))*(E5<$C$6:$C$50)*1)

これを開始時間、終了時間を別にして計算できるようにしたものが、上記のH1セルの式となっています。

No.3 回答者： LHS07 回答日時： 2019/09/20 12:34

表を作り直したほうがいいですね。

視覚的に自分でわかりやすいように考えなおしましょう。

じぶんが知らない関数で作ると関数がおかしくなったら重大なトラブルになります。