指定した値以上の中で最小値を出したい

時刻データで
0:00～23:59までの時刻が入った勤務時間レコードがあります。
例えば、
10/1,8:30
10/2,0:30
10/2,1:35
10/2,5:15
10/2,9:00
10/2,21:15
というデータが入っていたとして以下の様に集計したいです。
10/1の開始時間 8:30、終了時間 1:35
10/2の開始時間 9:00、終了時間 21:15

それを、MINIFS関数とMAXIFS関数を使って、
業務開始時間と業務終了時間を出したいのですが、
MINIFS関数だと0:30が出てしまいますが、実は前日勤務の退勤時間です。
例えば「3:00」を境にして深夜3:00以降の一番早い時間を開始時間として、
深夜3:00より早い時間は前日の終了時間として出すにはどうしたら良いでしょうか？

No.7 回答者： goomania 回答日時： 2022/11/05 01:23

No.6です。

前回までの回答はXLOOKUP関数の使えないバージョンでも利用できます。

ご質問者のバージョンではXLOOKUP関数が使用できることが判っているので、rexfanさんの回答のように、XLOOKUP関数を使う方法もあります。

前回回答同様に回答No.2の添付画像のようなデータがあったとして、D2セル、E2セルに記述する数式は

D2=XLOOKUP(A2+"3:00",($A$2:$A$13+$B$2:$B$13)/($C$2:$C$13=$C2),$A$2:$A$13+$B$2:$B$13,"",1)

つまり、
D2=XLOOKUP(指定日付+"3:00",(日付データ範囲+時刻データ範囲)/(氏名データ範囲=指定氏名),日付データ範囲+時刻データ範囲,"",1)･･･････････①

E2=XLOOKUP(A2+"26:59",($A$2:$A$13+$B$2:$B$13)/($C$2:$C$13=$C2)/($A$2:$A$13+$B$2:$B$13>$D2),$A$2:$A$13+$B$2:$B$13,"",-1)

つまり、
E2=XLOOKUP(指定日付+"26:59",(日付データ範囲+時刻データ範囲)/(氏名データ範囲=指定氏名)/(日付データ範囲+時刻データ範囲>勤務開始時刻),日付データ範囲+時刻データ範囲,"",-1)･････････････②

という数式を記述して下方向へコピーすることになります。

上記②の数式中、rexfanさんの回答にある「+"27:00」ではなく、「+"26:59」と記述しておりますが、下記のようなデータがあった場合、

10/1,8:30,山田
10/2,0:35,山田
10/2,3:00,山田
10/2,11:35,山田

「+"27:00"」だと、
10/1　勤務開始時刻2022/10/1 8:30 勤務終了時刻2022/10/2 3:00
10/2　勤務開始時刻2022/10/2 3:00 勤務終了時刻2022/10/2 11:35
という結果になり、開始時刻にも終了時刻にも「3:00」が登場してしまいます。
本来求めたいのは、
10/1　勤務開始時刻2022/10/1 8:30 勤務終了時刻2022/10/2 0:35
10/2　勤務開始時刻2022/10/2 3:00 勤務終了時刻2022/10/2 11:35
ですので、「+"26:59"」としています。

No.6 回答者： goomania 回答日時： 2022/11/04 16:39

>ABC列のデータを欄外のE列以降に表して頂いていますが、

>C列の名前の右にそれぞれ表示できないでしょうか？
>D列開始、E列終了つまり、重複があってもOKという事になります。

ということなので、数式を調整してみました。
前回投稿のNo.2の添付画像のようなデータがあったとして、1行目は見出しだとすると、D2セル、E2セルから数式を記述することになります。

D2セルに

=IFERROR(1/AGGREGATE(14,6,($A$2:$A$13+$B$2:$B$13>=A2+"3:00")/($A$2:$A$13+$B$2:$B$13)*($C$2:$C$13=C2),1),"")

つまり、
=IFERROR(1/AGGREGATE(14,6,(日付データ範囲+時刻データ範囲>=指定日付+"3:00")/(日付データ範囲+時刻データ範囲)*(氏名データ範囲=指定氏名),1),"")

という数式を記述して下方向へオートフィル等でコピーし、さらにE2に

=IFERROR(1/(1/AGGREGATE(14,6,($A$2:$A$13+$B$2:$B$13<A2+"27:00")*($A$2:$A$13+$B$2:$B$13)*($C$2:$C$13=C2)*($A$2:$A$13+$B$2:$B$13>D2),1)),"")

つまり、
=IFERROR(1/(1/AGGREGATE(14,6,(日付データ範囲+時刻データ範囲<指定日付+"27:00")*(日付データ範囲+時刻データ範囲)*(氏名データ範囲=指定氏名)*(日付データ範囲+時刻データ範囲>勤務開始時刻),1)),"")

という数式を記述して下方向へオートフィル等でコピーすれば良いと思います。

※データ範囲はご質問者のデータ範囲に併せて修正してください。
※前回の数式から簡素化できる部分は簡素化しました。

No.5 回答者： rexfan 回答日時： 2022/11/03 17:26

ＮＯ４です。

さらに、スタッフ単位に調べたい場合は
「xlookup複数条件検索」で調べてコードを加工すれば
可能だと思います。

No.4 回答者： rexfan 回答日時： 2022/11/03 17:13

XLOOKUPが使えます。

例ですのでセル名は直してください。

開始時間　=XLOOKUP(M108+"3:00",$J$102:$J$107+K102:K107,$K$102:$K$107,"X",1)

終了時間　=XLOOKUP(M108+"27:00",$J$102:$J$107+K102:K107,$K$102:$K$107,"X",-1)

ポイントは3時が切れ目とする場合
作業日（M108）に3時間加えた日時刻で直後（１）の時刻、27時間を加えた日時刻で直前（-１）の時刻を求めるようにJ列+K列の日時＋時刻に対して
検索をかけている。

切れ目の時刻を任意に直せば動きます。

No.3 回答者： goomania 回答日時： 2022/10/31 15:48

No.2です。

大変失礼しました。ご質問を読み返してみたら

>「3:00」を境にして深夜3:00以降の一番早い時間を開始時間として、
>深夜3:00より早い時間は前日の終了時間として出す

というご希望なので、勤務開始時刻の条件に等号(＝)を付けて、終了時刻の条件には等号を付けないというのが正しいことになります。

つまり、勤務開始時刻の数式の中の
$A$2:$A$13+$B$2:$B$13>E2+"3:00"*1
は
$A$2:$A$13+$B$2:$B$13>=E2+"3:00"*1
に修正し、
勤務終了時刻の数式の中の
$A$2:$A$13+$B$2:$B$13<=E2+"27:00"*1
は
$A$2:$A$13+$B$2:$B$13<E2+"27:00"*1
に修正する必要があるということになります。

また、個人を識別するための例として、前回回答の添付画像は氏名を使用していますが、社員番号などユニークなものにする方が好ましいのはいうまでもありません。

No.2 回答者： goomania 回答日時： 2022/10/31 15:25

添付画像を御覧ください。

>MINIFS関数でなくても良いと言いましたが、MINIFS関数を使ったもう一つの理由は、
>この勤怠レコードは全スタッフ混在しているため、IFSを使いました。

とのことなので、添付画像のA列～C列のようなデータがあるものと想像しました。

このデータからE列～F列に日付、氏名を入力するとG列、H列に勤務開始時刻、勤務終了時刻が表示されるようにするのが目的です。

データ範囲は添付画像に併せて、$A$2:$C$13としていますが、ご質問者の実際のケースに併せて変更してください。

勤務開始時刻を表示するG2セルに

=IFERROR(1/AGGREGATE(14,6,1/(($A$2:$A$13+$B$2:$B$13>E2+"3:00"*1)*($A$2:$A$13+$B$2:$B$13)*($C$2:$C$13=F2)),1),"")

という数式を記述しています。これを下方向へオートフィル等でコピーします。
これにより、指定日付の午前3時より後の最も小さい時刻を勤務開始時刻としています。

勤務終了時刻を表示するH2セルに

=IFERROR(1/(1/AGGREGATE(14,6,($A$2:$A$13+$B$2:$B$13<=E2+"27:00"*1)*($A$2:$A$13+$B$2:$B$13)*($C$2:$C$13=F2)*($A$2:$A$13+$B$2:$B$13>G2),1)),"")

という数式を記述しています。これを下方向へオートフィル等でコピーします。
これにより、指定日の勤務開始時刻より後、かつ、指定日付の翌日午前3時以前の最も大きい時刻を勤務終了時刻としています。

これで、E列～F列に日付、氏名を入力するとG列、H列に勤務開始時刻、勤務終了時刻が表示されるようになります。

ご質問者の目的は一応実現できるのではないかと思うのですが、勤務開始時刻、勤務終了時刻の日付と時刻を別セルに表示したいというようなニーズがある場合はさらに加工が必要になります。

この回答へのお礼

ありがとうございます。
その認識で合ってると思います。
ABC列のデータを欄外のE列以降に表して頂いていますが、
C列の名前の右にそれぞれ表示できないでしょうか？
D列開始、E列終了
つまり、重複があってもOKという事になります。

別の表からXlookup等でABCDE列から参照するためのシートになります。

お礼日時：2022/11/03 19:04

No.1 回答者： fujillin 回答日時： 2022/10/24 22:24

こんばんは

＞MINIFS関数とMAXIFS関数を使って、～～
MINIFS(やMAXIFS)でも可能と思いますが、条件式が面倒になりそうなので、別の方法にしてみました。
MINIFS(やMAXIFS)関数での回答をお求めの場合は、他の方の回答をお待ちください。

レイアウトが不明ですが、A列に日付、B列に時刻がともにシリアル値（＝エクセルの日付型、時刻型の値）で入っていると仮定しています。
※ 以下の計算では、3時間ずらして最大、最小を評価した後に、元に戻すという計算をしています。
※ ご質問文には「10/2の開始時間 9:00」とありますが、3:00以降の5:15で良いものと解釈しました。


・添付図では、A列に日付、B列に時刻がある場合に、
・求めたい日付の開始時刻をE列、終了時刻をF列に求める例です。
・D列は対象とする日付をシリアル値で入力してあります。

E2列セルに、
=MOD(AGGREGATE(15,6,A:A+B:B-"3:00"/(INT(A:A+B:B-"3:00")=D2),1),1)+"3:00"

F2セルに、
=MOD(AGGREGATE(14,6,A:A+B:B-"3:00"/(INT(A:A+B:B-"3:00")=D2),1),1)+"3:00"

の関数式を入力し、表示形式を時刻（h:mm)に設定し、それぞれ下方にフィルコピーしてあります。
※ 上式は計算の対象セル範囲を列全体にしてありますが、必要範囲に狭めた方が計算負荷は小さくなります。
※ 日付を超えた時刻値（＝10/1の終了時刻など）は、実際には「25:35」になっていますので、もしも、その後計算に使用する場合はご注意ください。

この回答へのお礼

MINIFS関数に拘ってるのではなく、その方法しか思いつかなかったからです。解決できるならどんな方法でも良いのですが、知らない関数を知れる良い機会となりました。
3時間ずらしてまた戻す、、、なるほどですね。
変数を使ってずらす範囲もちょうせいできそうです。

MINIFS関数でなくても良いと言いましたが、MINIFS関数を使ったもう一つの理由は、この勤怠レコードは全スタッフ混在しているため、IFSを使いました。
ご提示いただいた方法でスタッフ単位で出す方法、明日考えます！
ありがとうございました。

お礼日時：2022/10/24 22:47