『膨大なデータを輸送会社、入退場時間を抽出し、計算したい。』（Bulls）
いつもお世話になっております。
前回投稿した質問を解決して以来、タスクが非常に進んでおります。
度々質問して申し訳ありませんが、膨大な元データを毎回手作業で編集しています。
元データから輸送会社ごとに計算したいです。
しかし、私の実力をはるかに超えるスキルが必要なので、作成できなくて困っています。
非常識な奴だなと思われるのを承知で、
どなたか作成していただけますでしょうか。
解説もつけていただけると幸いです。
よろしくお願いいたします。
元データ
________________________________________________________
会社名 | 入退 | 日付 | 時 刻 |
… | … | … | … |
完成イメージ
__________________________________________________________
会社名 | 入場平均時間 | 早 | 遅
… | … | … | … |
＜ 使用 Excel：unknown、使用 OS：unknown ＞
バージョンが2021だとして。 ([[20250903105822]] 『特定の頭文字から始まる文字を消したい』（Bulls）を参照)
元データのA列目が会社名で1行目が見出しで最大100行目までだとして。 完成イメージシートの
A2:=UNIQUE(FILTER(元データ!A2:A100,元データ!A2:A100<>"")) B2:=IF(A2:A100<>"",AVERAGEIFS(元データ!D2:D100,元データ!A2:A100,A2:A100,元データ!B2:B100,"入場"),"") C2:=IF(A2:A100<>"",MINIFS(元データ!D2:D100,元データ!A2:A100,A2,元データ!B2:B100,"入場"),"") D2:=IF(A2:A100<>"",MAXIFS(元データ!D2:D100,元データ!A2:A100,A2,元データ!B2:B100,"入場"),"")
ではどうだろうか？
ただ、確認を365で行ったためうまくいかない場合はすまない。 （ねむねむ） 2025/09/24(水) 13:44:49
あと、質問する場合にはバージョンを必ずいれるのと、例を挙げるならばなるべく元データと結果が対応するようにして ある程度、回答側でも値の検証ができるようにしておいてくれ。 （ねむねむ） 2025/09/24(水) 13:47:39
ぱっとみ、
1）オートフィルターで入場だけ抽出して別シートにコピペ
2）同じく退場だけ抽出してコピペ
3）それぞれの表を日付と会社名で並び替え
そうしたら、それぞれの同じ行に対になるデータがこないだろうか？
同じになるなら、
4）2つの表を合体させる
そうしたら、入場時間は簡単に計算できるのではないだろうか？
その後は、
5）ピボットテーブルで集計
で期待した表ができないでしょうか？
必要なら、
6）ピボットテーブルの結果をコピペ
して、整形なり修飾なりで見やすく加工したらいいかと思いました。
ただし、入場と退場が日付を跨いだり、１日に複数回出入りがあった場合は、
うまくいくかわかりませんが。。。
回答側でテストできるくらいのデータ（ちゃんと入場と退場が対になるようなデータが
があるとよりよいかと思いました。
（まっつわん） 2025/09/24(水) 14:13:23
最初は私も入場平均時間というのは退場時間-入場時間の平均かと思ったが
完成イメージ上で 08:13:18 10:03:52 等となっているので単に入退が入場になっているものの時刻の平均でいいのではないだろうか？
（ねむねむ） 2025/09/24(水) 14:27:58
こんな感じの例で説明してもらうと明確になるかもしれません。 計算が面上のデータだけで算出できるものが望ましいのです。
元データシートにあるデータをもとに、
A列 B C D
1 会社名 入退 日付 時刻
2 会社A 入場 250915 08:15:00
3 会社A 退場 250915 08:30:00
4 会社B 入場 250915 08:30:00
5 会社B 退場 250915 08:50:00
6 会社A 入場 250918 08:25:00
7 会社A 退場 250918 08:40:00
8 会社B 入場 250918 08:50:00
9 会社B 退場 250918 09:10:00
以下の分析表を作成したいと考えています。 会社ごとの、平均的な入場時刻、最も早かった入場時刻、最も遅かった入場時刻です。
A B C D
1 会社名 平均入場時刻 最早入場時刻 最遅入場時刻
2 会社A 08:20:00 08:15:00 08:25:00
3 会社B 08:40:00 08:30:00 08:50:00
(2)すでに回答いただいていますが、こういうことですね。 （対象範囲や条件の絞り込みは三者同一の方がわかりやすいかと思います）
A2セル =UNIQUE(FILTER(Sheet1!A2:A9,Sheet1!A2:A9<>"")) これはA2以下にスピルされます。 B2セル =AVERAGEIFS(Sheet1!$D$2:$D$9,Sheet1!$A$2:$A$9,$A2,Sheet1!$B$2:$B$9,"入場") C2セル =MINIFS(Sheet1!$D$2:$D$9,Sheet1!$A$2:$A$9,$A2,Sheet1!$B$2:$B$9,"入場") D2セル =MAXIFS(Sheet1!$D$2:$D$9,Sheet1!$A$2:$A$9,$A2,Sheet1!$B$2:$B$9,"入場") B2:D2セルを下にコピーします。
(3) なお、動的配列を使えば、一括して計算ができるので、コピーしなくてもよいと思います。 A2セルは上記どおり B2セル =AVERAGEIFS(Sheet1!D2:D9,Sheet1!A2:A9,A2#,Sheet1!B2:B9,"入場") C2セル =MINIFS(Sheet1!D2:D9,Sheet1!A2:A9,A2#,Sheet1!B2:B9,"入場") D2セル =MAXIFS(Sheet1!D2:D9,Sheet1!A2:A9,A2#,Sheet1!B2:B9,"入場")
A2#と言う書き方にある#記号はスピル範囲指定子と呼ばれ、 「A2に書かれた式によりスピルで展開された範囲」を指定できます。 つまり、A2#と書くことで、A2から実際に企業名があるセルまでの範囲（例：A2:A10とか）を 指定することができます。
なお、シート名は適宜修正して下さい。(Sheet1⇒元データ） またデータの大きさをみて行数は適宜修正して下さい。9じゃなく100とか1000とか） （xyz） 2025/09/25(木) 08:28:00
ねむねむさんの提示された式ですが、（シート名は変更しています） | A2: =UNIQUE(FILTER(Sheet1!A2:A100,Sheet1!A2:A100<>"")) | B2: =IF(A2:A100<>"",AVERAGEIFS(Sheet1!D2:D100,Sheet1!A2:A100,A2:A100,Sheet1!B2:B100,"入場"),"") | C2: =IF(A2:A100<>"",MINIFS(Sheet1!D2:D100,Sheet1!A2:A100,A2,Sheet1!B2:B100,"入場"),"") | D2: =IF(A2:A100<>"",MAXIFS(Sheet1!D2:D100,Sheet1!A2:A100,A2,Sheet1!B2:B100,"入場"),"") よくよく見ると問題があるようです。（私は直ぐには気が付きませんでした。）
平均入場時刻はうまくいきますが、最早入場時刻と最遅入場時刻は想定結果が得られないと思います。
例えば、最早入場時刻 =IF(A2:A100<>"",MINIFS(Sheet1!D2:D100,Sheet1!A2:A100,A2,Sheet1!B2:B100,"入場"),"") は、A2:A100<>""なので動的配列のモードになっています。 そのとき、企業名を指定する引数は、A2という単一セルです。 この場合は、これがA2:A100と言う配列と同一形式のものに自動変換が起きると考えられます。 （言語によっては、昇格(promotion)とかブロードキャストとか呼ばれることがあると思います。）
で、実際にどうなるかというと、A2と同一のものが並んだ配列に変換されるので、 すべての企業で同一企業名のものに条件が限定され、どの行にも同じ結果が得られてしまうと思われます。
このあたり、動的配列が導入されたことによって、既存の関数でどうのような振る舞いになるのか （少なくとも私は数式に関する経験が乏しいので）注意が必要かと思っています。 マイクロソフト社のドキュメント類で、こうした自動変換の内容について明示的・論理的に説明されたものを ご存じのかたがおられたら教示いただきたく思います。
さらに余談ですが、これに関連して、 https://note.com/kadukijob/n/n91fc4ee881cb と言うサイトが扱っているようでした。
エッジケースも含めて、納得的な説明はなかなか難しい印象です。 ベテランさんにとってみれば、こんなこと常識？なのでしょうか、私にはよく理解できませんでした。 感想としては、「結局何をされたいのか、そこから改めて出発するというアプローチのほうがいいんじゃないの？」という はなはだ第三者的な感想しか浮かびませんでした。
（xyz） 2025/09/25(木) 19:21:57
xyzさん、ご指摘ありがとうございます。
Bullsさん、最大・最小も平均のように修正してくれ。
C2: =IF(A2:A100<>"",MINIFS(Sheet1!D2:D100,Sheet1!A2:A100,A2:A100,Sheet1!B2:B100,"入場"),"") D2: =IF(A2:A100<>"",MAXIFS(Sheet1!D2:D100,Sheet1!A2:A100,A2:A100,Sheet1!B2:B100,"入場"),"")
言い訳すると最初に単一行の式をたてて後からスピル対応に変更したのだが最大・最小のほうは修正漏れしてしまった。
（ねむねむ） 2025/09/26(金) 08:33:06
だからなに？ （ねむねむ） 2025/09/26(金) 11:01:54
平均入場時間と言う言葉からすると、退場時刻ー入場時刻から求める滞在時間的な意味と受け止めるのは自然。 （「時間」と「時刻」はハッキリ意味が違うのです。） その前提に立ったまっつわんさんのコメントは、残念ながら外していると思います。
皆さんから回答のコメントがあったのですから、余り時間を置かずに反応したほうがいいと思います。 回答で分からない所があれば、あなたがそれを質問して下さい。
前回のCOUNTIFSが理解されていれば同様な話だと思います。
複数の条件を同時に満たすもの（つまり複数のAND条件を満たすもの）を限定し、 その個数を求めるものがCOUNTIFSです。 今回は、個数ではなく、平均値や最大値、最小値に変わっただけで、 考え方はどれも同じです。
質問者さんが放置しているので余計なコメントが付くのですよ、 質問者さんしっかり対応して、本来の質疑応答を進めて下さい。 （xyz） 2025/09/27(土) 11:41:21
まずは、AVERAGEIFSのヘルプを読むことと、 （xyz） 2025/09/25(木) 08:28:00 の(2)を例にサンプルで動作を確認しながら、 式の意味を理解するのが先決かと思います。
動的配列を利用するに越したことはありませんが、 別に必要な分だけコピーしても問題ありません。 （もちろん式の意味を理解していることが絶対条件ですけど）
式の内容を理解せずに形だけ真似るようなことだけしていると、その都度引っ掛かってしまうので、 まずは式の内容を簡単な例で納得するまで試すことをお薦めします。
（xyz） 2025/09/28(日) 14:28:52
出張の為、長期不在にしていました。
お礼を言うのがおそくなり、大変申し訳ありません。
（Bulls） 2025/10/01(水) 12:13:49
(1) > A2セル =UNIQUE(FILTER(Sheet1!A2:A9,Sheet1!A2:A9<>"")) これはA2以下にスピルされます。 これはA2:A9セルのなかで空白でないものだけ抽出し、 さらにUNIQUEで重複を除いたものにしています。
(2) > B2セル =AVERAGEIFS(Sheet1!$D$2:$D$9,Sheet1!$A$2:$A$9,$A2,Sheet1!$B$2:$B$9,"入場") については、まず前回のCOUNTIFSの理解が土台になります。
=COUNTIFS($A$1:$A$685,"Aさん",$D$1:$D$685,">=0:10:00",$D$1:$D685,"<0:15:00") について復習です。
それぞれの要素の意味をきちんと理解することです。
=COUNTIFS($A$1:$A$685,"Aさん",$D$1:$D$685,">=0:10:00",$D$1:$D685,"<0:15:00")
┗----------------┛┗-------------------┛┗--------------------┛
(a)A列が"Aさん"と一致し、
かつ (b)D列が0:10:00 以上であり
かつ (c)D列が0:15:00 未満
であるようなデータは何件あるか、ということです。
重要なことは、 ・3つの条件をそれぞれ、「対象セル範囲」と 「条件」の"組み"で表現すること ・それぞれの条件を同時に満たす、つまり"かつ"条件であること、 言い換えれば、 AND(条件判定1,条件判定2,条件判定3) であること です。
(3)
=AVERAGEIFS(Sheet1!$D$2:$D$9,Sheet1!$A$2:$A$9,$A2,Sheet1!$B$2:$B$9,"入場")
┗------------------┛┗---------------------┛
の意味は、
・元データのA列が、集計用シートのA2セルにある企業名に一致し、
かつ
・元データのB列が、"入場" と一致する
場合に、
その条件を満たす行の Sheet1!$D$2:$D$9のデータ(入場時刻)だけを対象にして
その平均を返しなさい、という意味です。
その式を下にコピーすれば、結果が得られます。
(4) 慣れないうちは上記のコピー方式で十分だと思いますが、 配列化することも可能です。
=AVERAGEIFS(Sheet1!$D$2:$D$9,Sheet1!$A$2:$A$9,$A2,Sheet1!$B$2:$B$9,"入場") の ↑の部分を 例えば、$A2:$A3のようにすれば、配列(要素2個)を指定したことになります。
その結果はそれぞれの条件に従って計算して得られる結果を 要素2個の配列として返してくれます。(動的配列の利用） そしてそれらは、下にスピルされて表示するわけです。
（xyz） 2025/10/01(水) 18:40:53
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.