特定期間中の各IDログのMINの日付とMAXの日付を抽出したい

Question

前提・実現したいこと

Google スプレッドシートを使っております。
関数を使って、最終的な結果イメージのD列、E列に、
特定期間中の、ID別　INのMINの日時、OUTのMAX日時を表示させたいです。
IDは特定期間を過ぎると、使い回しされます。

《最終的な結果イメージ》

《各IDのIN/OUTのログのシート》
ログになるため、何万行となります。

条件

・IDの利用開始日時～利用終了日時の間で、
各IDのログシートから、INのMINの日付とOUTのMAXの日付を抽出したい
・これらのIDは利用期間が終わると、再利用されます。
・ログシートが何万行となるため、可能であればARRAYFORMULAを活用できれば幸いです。

試したこと

・QUERY、VLOOKUPやMINIFSなど試みましたが、
どのような組み合わせで行うべきかわからなかったため、相談をさせていただきました。

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

Answer 1

個々のIDに対して 範囲（開始日時~終了日時）が違うとのことなので、
力技でARRAYFORMULA を使う方法があるのかもしれませんが、
可読性低くなるしスピードもあまり上がらないんじゃないかと思います。

よって、素直にここはMINIFS、MAXIFSで取得することを推奨。
ログシートが集計結果用のスプレッドシーと 別のスプレッドシートならば、
式内にIMPORTRANGEを入れずに、一度 集計結果用のスプレッドシートの適当なシート（シート2）に
IMPORTRANGEで必要になるHIJ列を引っ張ってきたもの（シート2のABCに入れることを想定）を 範囲に指定したほうが良いかと。

（例 これを下にコピペしていくイメージ。MAXIFSも同じような感じ）
=MINIFS('シート2'!$C$1:$C,'シート2'!$A$1:$A,D2,'シート2'!$B$1:$B,"IN",'シート2'!$C$1:$C,">="&B2,'シート2'!$C$1:$C,"<="&C2)

あまりに遅いようなら関数を諦めて、GASでgetValuesして処理した方がいいかもです。

Comments

[Nozomi0302]

sawaさん
教えていただき、ありがとうございました！
いただいた内容で、欲しい情報を抽出することができました！
ありがとうございます！！！

Answer 2

《最終的な結果イメージ》の方のシートの A,B,C列は固定されているという想定で書きます。
（単にIDごとのINの最小、OUTの最大なら ピボットテーブル作るのが早いです）

一番簡単なのは、別シートでINとOUTのMIN,MAXを取得するピボットテーブル作って、
それをVLOOKUPでヒットさせる式をARRAYFORMULAすることです。

ピボットテーブル無しで一つの式で書きたい場合は、
QUERY でピボットして こんな感じ→ select G,MAX(I),min(I) group by G pivot I
で抽出した範囲を VLOOKUPしたものを ARRAYFORMULA で展開するのが良いんじゃないかと。

※データ範囲(最終行）が固定でない場合は、QUERYを入れ子にして G列空白を除外
※ARRAYFORMULA の最終行が固定でない場合は、VLOOKUPのエラー回避用に IFEEROを追加
※VLOOUPの指数？ （何番目の行を参照するか）は、'任意の数字'±COLUMN(D2:C)で調整すると
式一つで綺麗に処理できます

Comments

[Nozomi0302]

sawaさん
ご回答ありがとうございます！

これらのIDは使い回しされてしまうため、IDごとのINのMIN値、OUTのMAX値ではなく、
特定期間の間の、ID別　INのMIN値、OUTのMAX値を表示させたいです。
冒頭の説明文がわかりづらいですね。。。編集いたします。

特定期間の条件をどこかに組み込んだ上で、表示させることはできますでしょうか。
教えていただけますと幸いです。

[sawa]

特定期間は、結果シートのA列～B列の日時ということでしょうか？
これは、ID毎に違いますか？それとも 全体てのID 同じ条件でよいのでしょうか？

[Nozomi0302]

sawaさん
お返事が遅くなって申し訳ありません！
お返事いただき、ありがとうございます。

特定期間は、仰せの通り【結果シートのA列～B列の日時】となります。
説明がうまくできず、大変恐縮ではございますが、
画像を変更してみました。

ひとつのＩＤは特定期間中は独占されますが、特定期間が終わると、また再利用されます。

例えば、
バナナが　特定期間中に、あるＩＤを独占しています。
その間にバナナは、ある場所でIN/OUTのログを残します。
バナナがIDを使い終えると、次はみかんが独占します。

バナナのINのMIN日付、OUTのMAX日付を抽出したいと考えております。

[sawa]

理解しました。個々のIDに対して 範囲（開始日時~終了日時）が違うとなると、
ARRAYFORMULA を諦めて、ここにMINIFS,MAXIFSでやった方がよさそうな気がします。
回答もそっちバージョンを記載しておきます