Q&A
sawaさん
ご回答ありがとうございます！
これらのIDは使い回しされてしまうため、IDごとのINのMIN値、OUTのMAX値ではなく、
特定期間の間の、ID別 INのMIN値、OUTのMAX値を表示させたいです。
冒頭の説明文がわかりづらいですね。。。編集いたします。
特定期間の条件をどこかに組み込んだ上で、表示させることはできますでしょうか。
教えていただけますと幸いです。
前提・実現したいこと
Google スプレッドシートを使っております。
関数を使って、最終的な結果イメージのD列、E列に、
特定期間中の、ID別 INのMINの日時、OUTのMAX日時を表示させたいです。
IDは特定期間を過ぎると、使い回しされます。
《最終的な結果イメージ》
《各IDのIN/OUTのログのシート》
ログになるため、何万行となります。
条件
・IDの利用開始日時～利用終了日時の間で、
各IDのログシートから、INのMINの日付とOUTのMAXの日付を抽出したい
・これらのIDは利用期間が終わると、再利用されます。
・ログシートが何万行となるため、可能であればARRAYFORMULAを活用できれば幸いです。
試したこと
・QUERY、VLOOKUPやMINIFSなど試みましたが、
どのような組み合わせで行うべきかわからなかったため、相談をさせていただきました。
何卒よろしくお願いいたします。
回答2件
0
ベストアンサー
個々の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して処理した方がいいかもです。
投稿2020/04/09 02:28
編集2020/04/09 02:59
総合スコア3002
0
《最終的な結果イメージ》の方のシートの 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)で調整すると
式一つで綺麗に処理できます
投稿2020/04/06 08:31
総合スコア3002
特定期間は、結果シートのA列～B列の日時ということでしょうか？
これは、ID毎に違いますか？それとも 全体てのID 同じ条件でよいのでしょうか？
sawaさん
お返事が遅くなって申し訳ありません！
お返事いただき、ありがとうございます。
特定期間は、仰せの通り【結果シートのA列～B列の日時】となります。
説明がうまくできず、大変恐縮ではございますが、
画像を変更してみました。
ひとつのＩＤは特定期間中は独占されますが、特定期間が終わると、また再利用されます。
例えば、
バナナが 特定期間中に、あるＩＤを独占しています。
その間にバナナは、ある場所でIN/OUTのログを残します。
バナナがIDを使い終えると、次はみかんが独占します。
バナナのINのMIN日付、OUTのMAX日付を抽出したいと考えております。
理解しました。個々のIDに対して 範囲（開始日時~終了日時）が違うとなると、
ARRAYFORMULA を諦めて、ここにMINIFS,MAXIFSでやった方がよさそうな気がします。
回答もそっちバージョンを記載しておきます
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2020/04/10 02:18