エクセルの関数がわかりません

Sheet１にあるデータを、Sheet2にあるように、1日ごと、なおかつ
日直帯（8：30-16：59）
当直帯（17：00-8：29）
で「件数」を表示したいです。

6/1の日直帯は6/1 8:30-17:29まで
6/1の当直帯は6/1 17:30-6/2 8:29まで

でカウントしたいです。
COUNTIFS関数でトライしてみたのですが、いまひとつうまくいきません。
どなたか、お知恵を拝借できませんでしょうか。

宜しくお願い致します。

No.4 ベストアンサー 回答者： Belltreeriver 回答日時： 2022/06/06 11:35

COUNTIFS関数パターンです。

＜日直帯をカウント＞
B2セル
=COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,">="&TIMEVALUE("8:30"),Sheet1!B:B,"<"&TIMEVALUE("17:00"))

＜勤務総数-日直帯数＞←当直帯の数になる
C2セル
=COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,">0")-COUNTIFS(Sheet1!A:A,A2,Sheet1!B:B,">="&TIMEVALUE("8:30"),Sheet1!B:B,"<"&TIMEVALUE("17:00"))

各数式を下へコピペ

この回答へのお礼

教えていただきありがとうございました。

列や行を足さず、関数式だけで何とかしたかったので、なるほど、こういう式を書けばやりたかったことができたのか、と、とても勉強になりました。

私が思い描いていた通りのお答えを下さった方にベストアンサーを。
この度はありがとうございました。

お礼日時：2022/06/14 12:35

No.3 回答者： 銀鱗 回答日時： 2022/06/03 19:19

たぶん日付と時間を別々にしていることが原因だろうと思います。

日付も時間も「シリアル値」という「数値」になりますので、足しちゃってOK。
　”2022/6/1”+”8:30”　
こんなんでいいんです。
これで日を跨いだ日時を正しく評価できます。

・・・シリアル値・・・

ちなみに
　”2022/6/1”+”8:30”　
の結果を示したセルの表示形式を「標準」にすると
　44713.3541666667
という数値になります。
1900年1月1日0時0分を1として順番に数えて、44713.3541666667日目ってこと。

No.2 回答者： taglet 回答日時： 2022/06/03 18:39

以下のように作ってみてください。

・C1セルに「日直・当直」を記入

・Sheet1のC2セルに、以下の式を記入
=IF(AND(B2>=8.5/24,B2<17/24),"日直帯（8：30-16：59）","当直帯（17：00-8：29）")

・C3以降のセルに、C2セルをコピーして貼り付け

・A2セルを選択し、「挿入」タブ→「ピボットテーブル」を選択
テーブルの範囲として、元データの範囲を選択する。
配置する場所を、Sheet2にする。

・行フィールドに「日付」、列フィールドに「日直・当直」、データフィールドに「総数-日直・当直」を設定。

No.1 回答者： angkor_h 回答日時： 2022/06/03 18:02

それぞれ、次のように計算すれば、時間値が出ます。

> 6/1の日直帯は6/1 8:30-17:29まで
時間値=((6/1 17:29)-(6/1 8:30))*24=14.98[時間]

> 6/1の当直帯は6/1 17:30-6/2 8:29まで
時間値=((6/2 8:29)-(6/1 17:30))*24=14.98[時間]