エクセルで時間ごとの最大値、最小値を導き出したい。

質問

初めまして、よろしくお願いします。
　エクセルで分ごとの最大値と最小値を求める関数を教えて頂きたく、よろしくお願いします。

例）
　　　　　A1　　　　　　　　　B２
2007/9/6 15:11:54　　　５
2007/9/6 15:10:43　　　1
2007/9/6 15:10:33　　　22
2007/9/6 15:10:20　　　21
2007/9/6 15:10:16　　　54
2007/9/6 15:10:10　　　23
2007/9/6 15:10:08 　　 12
2007/9/6 15:09:54　　　 3
2007/9/6 15:09:24　　　34
"
"
と言うデーターを。
　　　　　　　　　　 　 最大値　　　　　最小値
2007/9/6 15:11:00　　　・・　　　　　　・・
2007/9/6 15:10:00　　　５４　　　　　　　１
2007/9/6 15:09:00　　　・・　　　　　　・・
　　　　　”
　　　　　”

回答No.8

Wendy02 · ベストアンサー

こんばんは。

一度、試していただきたいのは、最小値が出ている数式を、数式バー上でコピーして、
そのまま、同じ行の、最大値に貼り付けて、そこで、MIN とあるものを、MAX に変えてみてください。

なお、そろそろ、「補足側」でなく、「お礼側」にコメントつけてください。コメントが不足した場合に、「補足」につけてくださればよいです。そうすると、不定期な書き込みでも、こちらには、確実、メールの連絡が入るようになりますので、回答がしやすくなります。すぐに、返事が出来るわけではありませんが、よろしくお願いします。

お礼

回答ありがとうございます。指摘して頂いたように直しましたらうまくいきました。最後まで指導して頂き、大変感謝しております。本当にありがとうございました。

回答No.7

Wendy02

こんばんは。

>最大値の値に＃VALUB!と表示され、数字がでないので調べてみたのですが、A列に時間が表示されているが、B列に値が未記入の時にエラーがでるようです。

理由は分かりました!

最小値で、たまたま選んだ数式は、最大値でも、生きることが分かりましたので、そのまま、MAXに変えてしまいました。

未記入には、「""（長さ0の文字列）」が入っているようですね。

=IF(SUMPRODUCT(($A$1:$A$100>=$E2)*($A$1:$A$100<$E3)*($B$1:$B$100<>""))=0,"",MAX(OFFSET($A$1,SUMPRODUCT(MATCH(1,($A$1:$A$100>=E2)*($A$1:$A$100<E3),0))-1,1,SUMPRODUCT(($A$1:$A$100>=E2)*($A$1:$A$100<E3)))))

ちょっと脱線してすみませんが、やってみて、やはり、私は、以前、同様の質問に出会ったことがあります。この種の質問は時々見かけますが、今回、なぜ出来なかったか考えてみましたが、特に違うのは、データに、数値が入っていなかったり、長さ0の文字列になっていたりすることですね。今回は、どうも、偶然のたまものでした。お待たせしてすみませんでした。

補足

毎回回答頂き、ありがとうございます。この式を入れてみたのですが、エラーは表示されなくなりましたが、値の表示もされず、未記入の状態になりました。解りましたらよろしくお願いします。自分で考えられず、申し訳ありません。

回答No.6

Wendy02

こんばんは。

>最大値ではこの式でもエラーがでてしまうようです。

ですから、最大値側は、どんなデータでエラーが出るのか詳細がないと、今の時点では分からないのです。最大値側は、基本的には、その考え方自体が変えたわけではありません。

最小値側は理解したのですが、最大値側のエラーに対しては良く理解してはいないのです。
・範囲の問題なのでしょうか？
・どんなにエラー値が出るのでしょうか？

補足

回答をいただきありがとうございます。
　大変失礼しました。最大値の値に＃VALUB!と表示され、数字がでないので調べてみたのですが、A列に時間が表示されているが、B列に値が未記入の時にエラーがでるようです。未記入欄に０を入力すれば正しく値が表示されるのですが、もしも未記入でも何かうまくいく方法がありましたら、よろしくお願いします。最小値の場合はエラーが出ずに正しく表示されました。

回答No.5

Wendy02

こんばんは。#1で書いた者です。

>１　データーの範囲が、記入されているセル以上である場合（データーが乗っていない未記入のセルを含む場合）エラーがでてしまうようです。

データの範囲というのは、[時間（E列）]のことですか？もし、ある一定の時間でしたら、その時間の範囲をリストで作ってもらわないといけません。

これに関しては、理解できておりませんので、具体的な例をみないとはっきりしません。

>２　同じ最小値がでている場合や一つしかデーターが乗っていない場合なのか、最小値が１００００とでてしまう時がある。

まったく、データがない場合などを含むとすれば、こうすれば、追い出しは利きますから、未記入と0の区別はされます。（ややこしいような気がします）

最小値
=IF(SUMPRODUCT(($A$1:$A$100>=$E2)*($A$1:$A$100<$E3)*($B$1:$B$100<>""))=0,"",MIN(OFFSET($A$1,SUMPRODUCT(MATCH(1,($A$1:$A$100>=E2)*($A$1:$A$100<E3),0))-1,1,SUMPRODUCT(($A$1:$A$100>=E2)*($A$1:$A$100<E3)))))

前後しますが、最大値も、まったくデータのない場合の追い出しをしておきます。

=IF(SUMPRODUCT(($A$1:$A$100>=$E2)*($A$1:$A$100<$E3)*($B$1:$B$100<>""))=0,"",MAX(INDEX(($A$1:$A$100>=$E2)*($A$1:$A$100<$E3)*$B$1:$B$100,,)))

動的データは、みなさん、ほとんど内情を明かさないのです。中には、POSシステムで、その内容を明かせられないという人もいましたが、5人中4人は、動的データであることさえ公開しません。インターネットやRS232CやLANケーブルからデータが入ってきて、それを、入ってきたと同時に集計を取るという目的にしたものです。

以下は、典型的な動的なデータです。

http://oshiete1.goo.ne.jp/kotaeru.php3?qid=2305870　（解決していません）

http://oshiete1.goo.ne.jp/kotaeru.php3?qid=2481287　

他にも、多数ありますが、締めてあるだけで、内情は、ほとんど未解決です。

なお、後学のためというか、

=(TEXT($E$2,"yyyy/m/d h")&":"&ROW(A1))*1

の方式は、浮動小数点丸め誤差を防ぐための、[小数点固定法]と呼ぶテクニックのひとつです。

補足

回答ありがとうございます。最小値はうまくできたのですが、最大値ではこの式でもエラーがでてしまうようです。

回答No.4

noname#204879

A　　　　　B　 C　　　　　D　　　　　 E　　F
1　 2007/9/6 15:11:54　 5　　　　　　　　　　　　MAX　MIN
2　 2007/9/6 15:10:43　 1　　 2007/9/6 15:11:00　　5　　5
3　 2007/9/6 15:10:33　22　　 2007/9/6 15:10:00　 54　　1
4　 2007/9/6 15:10:20　21　　 2007/9/6 15:09:00　 34　　3
5　 2007/9/6 15:10:16　54
6　 2007/9/6 15:10:10　23
7　 2007/9/6 15:10:08　12
8　 2007/9/6 15:09:54　3
9　 2007/9/6 15:09:24　34
10

E2: {=MAX(IF(($A$1:$A$100>=$D2)*($A$1:$A$100<($D2+"0:1")),$B$1:$B$100,""))}
F2: {=MIN(IF(($A$1:$A$100>=$D2)*($A$1:$A$100<($D2+"0:1")),$B$1:$B$100,""))}

【備考】何れも配列数式です

補足

回答ありがとうございます。この式ですとなぜかA列の認識がうまくいかないようです。

回答No.3

imogasi

関数・関数というが、ピヴォットテーブルなどの方が考えやすいのではないですか。
例データ　元はA列とC列
A1:ｃ１０（B列は列挿入、後述）
A列　　　B列　　C列
日付時刻 時刻 計数
2007/09/06 15/11/54 15:11:00 5
2007/09/06 15/10/43 15:10:00 1
2007/09/06 15/10/33 15:10:00 22
2007/09/06 15/10/20 15:10:00 21
2007/09/06 15/10/16 15:10:00 54
2007/09/06 15/10/10 15:10:00 23
2007/09/06 15/10/08 15:10:00 12
2007/09/06 15/09/54 15:09:00 3
2007/09/06 15/09/24 15:09:00 34

時間を分単位にする。
B列を挿入し、=TIME(HOUR(A2),MINUTE(A2),0)と入れてB10まで式を複写。結果上記B列
ーー
A2:C10を範囲指定して
データ
ピボット
レイアウトで
縦列時刻、データに「計数」、最大値
できた表のB列をC列に複写
もう一度表中で右クリック
「フィールドの設定」を選択し、最小値を選択
下記のような表になる。
ーー
最小値 / 計数
時刻 合計 合計
15:09:00 3 34
15:10:00 1 54
15:11:00 5 5

お礼

回答ありがとうございます。表には他の計算のための関数式が入っているのでピヴォットテーブルは考えていません。この回答は参考にさせて頂きたいと思います。

回答No.2

noname#95859

試しにやって見てください。
データのシートをcsv形式で別ファイルに書き出します。
（例 Book1.csv)
このファイルをテキストエディタで見ると、確かに「秒」まで入っているのですが、このファイルをダブルクリックしてエクセルで立ち上げると、
秒は取り込まれずに、すべて「00」扱いになります。
そのように変換した上で、全セルを選択して、ピボットを活用します。
行に、時間「A1」、データに「B2」を2回（最大値、最小値に対応します）
レイアウトを決めてOKを返すと、結果の１歩手前まで来ます。
（この結果は、最大値、最小値が縦に並んでいます）
得られたピボット表に「データ」のボタンみたいなものがあります。これを
ドラッグして合計？の文字のところに持ってきます。
この部分の意図するところ--とにかく横に持ってくるのです。
こうすることで、期待する、横方向に、最大値、最小値が並びます。

尚、データ処理を目的にする場合には、常に半角入力をするように心がけてください。スペースを全角で入れたりすると、結構、後で、苦労します。

お礼

回答ありがとうございます。当方関数式を知りたいので、今回の回答は参考にさせて頂きたいと思います。

回答No.1

Wendy02

こんにちは。

まず、これは静的なデータに限ります。いままで、同じような質問がありましたが、主に、動的なデータが多かったです。

　　E　　　　　　F　　　　　　G
時間　　　　　最大値　　　　最小値

E2:に基点となる時間を置きます。
2007/9/6 15:00:00

次に、E3 は、以下のような数式を置きます。
=(TEXT($E$2,"yyyy/m/d h")&":"&ROW(A1))*1

そのまま、2007/9/6 15:01:00 と、二つのセルの差で以って、ドラッグコピーしますと、途中で、「浮動小数点誤差」が発生して、小数点の中でズレが生じてしまいます。時間は、小数点の計算ですから、Excelでは、数学的な比較検索には失敗する可能性が高いです。

F2 :(現行では、100行までを想定していますが、それよりも多い場合は、A100 の部分を変更してください。5500程度までは、正確に出せるはずですが、それ以上は、Excelのスペックの問題で、保証できません。）

=IF(SUMPRODUCT(($A$1:$A$100>=$E2)*($A$1:$A$100<$E3)*1)=0,"",MAX(INDEX(($A$1:$A$100>=$E2)*($A$1:$A$100<$E3)*$B$1:$B$100,,)))

G2:「10^6」は、「0」を追い出すダミーですから、もし、最小値に、その値が出てくる場合は、「10^15」とありえない数値をおくとよいと思います。

=IF(SUMPRODUCT(($A$1:$A$100>=$E2)*($A$1:$A$100<$E3)*1)=0,"",SMALL(INDEX(SUBSTITUTE(($A$1:$A$100>=$E2)*($A$1:$A$100<$E3)*$B$1:$B$100,0,10^6)*1,,),1))

補足

回答ありがとうございます。この方法で試したところ、うまくいきましたが、いくつかの問題も発生しました。
１　データーの範囲が、記入されているセル以上である場合（データーが乗っていない未記入のセルを含む場合）エラーがでてしまうようです。
２　同じ最小値がでている場合や一つしかデーターが乗っていない場合なのか、最小値が１００００とでてしまう時がある。
です。もし改善できるようでしたら、教えて頂きたく、よろしくお願いします。また、”同じような質問がありましたが、主に、動的なデータが多かったです。”とありますが、どういうモノか興味がありますので、ページ先を乗せて頂ければありがたいです。