エクセルに関する質問です

質問

エクセルの計算式について教えて下さい。

例えば、以下のようにエクセル上で、A列に日付, B列に時間、C列に数値１、D列に数値2が入力されています。

このシートから、時間ごとにC列のなかで一番大きな数値の時間（B列）とD列の中で一番小さい数値の時間（B列）を抽出する計算式が知りたいです。

ご存知の方がいたら教えて下さい。MAXやMIN関数を利用するのだと思いますが....

A 　　　　　B 　　　C 　　　D
2011/1/4 9:00:00 10360 10330
2011/1/4 9:05:00 10350 10300
2011/1/4 9:10:00 10380 10310

宜しくお願いします。

回答No.7

tom04 · ベストアンサー

No.2・6です！
前回、両方ともそのつもりで回答しました。

No.2に
＞仮定されているように、毎営業日で、最大値と最小値のある時間帯を抽出したいのです。
＞10年分のデータが10年分のシートに分かれて保有しています。

とあるので、1年分のデータが1Sheetにあるものとして、別途Sheetを追加し、
そのSheet上で操作した方が良いというアドバイスがNo.6です。

1年分のデータのSheetで操作したい日付でオートフィルタを掛け
（↑A列の日付でオートフィルタを掛けます）
表示されたものだけを追加した↓の画像のSheetのA2セル以降に貼り付ければちゃんと表示されると思います。
表示されたものをデータとして残したいのであれば表示された部分だけを
コピー　→　残したいセルを選択　→　右クリック　→　「形式を選択して貼り付け」
→　「値」にチェックを入れOK
この繰り返しで大丈夫だと思います。

今一度画像をUPしてみます。
いままでと全く同じコトなのですが、今回は作業列を3列設けてみました。

画像の作業列1E2セルに
=IF(B2="","",HOUR(B2))

F2セル（配列数式）に
=IF(C2=MAX(IF($E$2:$E$1000=E2,$C$2:$C$1000)),E2,"")

G2セル（配列数式）に
=IF(D2=MIN(IF($E$2:$E$1000=E2,$D$2:$D$1000)),E2,"")

としてE2～G2セルを範囲指定　→　G2セルのフィルハンドルでかなり下までオートフィルでずぃ～～～！っとコピー！

そしてI列の数値はご自身で入れておきます。

J2セルは
=IF(COUNTIF(F:F,I2),INDEX(B:B,MATCH(I2,F:F,0)),"")

K2セルは
=IF(COUNTIF(G:G,I2),INDEX(B:B,MATCH(I2,G:G,0)),"")

J・K列は配列数式ではありません。
これをオートフィルで下へコピーすると
画像のような感じになります。

※　配列数式の操作は前回の説明通りです。
※　結局今までの回答をそのままを繰り返しただけのようですが、
まだこれでもダメでしょうか？m(_ _)m

お礼

tom04様

何度もご教示頂き本当にありがとうございました。教えて頂いた通り、別に集計シートを作成し、元データのシートを年度毎に出して、そこから毎営業日毎に9:00から11:00までの2時間の時間帯で最初に出てくるそれぞれの最大値と最小値が抽出出来ました。

本当に助かりました。お礼申し上げます。これから2001年分まで遡って抽出し、集計しなくてはならないので、手作業が未だあり大変ではありますが、お蔭様でかなり作業時間を減らせそうです。

ありがとうございました。また、機会ありましたら、ご教示宜しくお願い致します。

回答No.9

MackyNo1

皆さんへの補足回答を見ていると、質問内容がわからなくなってきました。

＞このデータの9:00-11:00の時間内でのC列の最大値と、D列の最小値の時間(B列に表示されている時間）を抽出したいというのが目的です。

私の回答に対する補足と違うような気がします。
9:00-11:00までの固定した2時間の時間帯だけの最大値と最小値を表示したいということでしょうか？
また、確認ですが日付ごとのその時間帯の時刻を求めたいということでしょうか？

応答を見ていると、質問者からの回答に対する返答が無いようですが（表示形式を変えるとうまく表示できたのか、結果が希望のデータとどのように違うのかなど）、実際にまとめたいリストの形式はどのようなレイアウトになっているのでしょうか？

また確認ですが、元データは9:00-11:00以外の時間帯のデータも含まれているのですよね。

皆さんからの回答が無駄にならないように、再度言いますが、まとめたいリストのレイアウトの説明を以下のように例示してください（２時間の時間帯の例）。

日付　　　最大値　最小値
　2/1　　　　9:55　　10：20
　2/2　　　10:55　　　9：20
　2/3　　　　9:25　　10：40
　2/4　　　　9:50　　10：30

お礼

MackyNo1　様

何度も返信・ご教示頂き本当にありがとうございました。分かり難い質問の仕方で時間を無駄にさせてしまったこと、お詫びいたします。一応問題は解決出来ました。

本当にありがとうございました。大変参考になりました。また、機会ございましたら、宜しくお願い致します。

また、その他に回答頂きました皆様、本当にありがとうございました。

回答No.8

MackyNo1

>毎営業日の9:00-10:00の間での最大値と最小値が出た時間をそれぞれ抽出したいと考えてます。

すでに回答した数式で表示できると思いますが、提示した数式は最大値または最小値が出現した最後の時間を取得する式になっています。

集計は１時間単位で良いのでしょうか？

もしその場合、最初に出現する時間を表示するなら、例えば以下のような数式になります。

最小値の時間
=MIN(INDEX((MIN(INDEX((HOUR($B$2:$B$100)<>HOUR($F2))*10^10+$D$2:$D$100,))<>$D$2:$D$100)+$B$2:$B$100,))

回答No.6

tom04

No.2です！

＞10年分のデータが10年分のシートに分かれて保存・・・
とありますので、同一Sheetに1年分のデータがある訳ですかね？

そういうことだとしての一案です。

前回のSheetは「抽出用」のSheetとして別に作成してみてはどうでしょうか？

そして各Sheetの検索したい日付でオートフィルタでフィルタを掛けます。
そして表示されたA～D列のデータを「抽出用」SheetのA2セルにコピー＆ペーストすれば
ちゃんと表示されると思います。

尚、「抽出用」Sheetの数式は前回同様にしておきます。

前回アップした作業列（E列）には0.375とか0.41667といった数値が表示されると思いますが
これは時刻のシリアル値になりますので、そのままでも問題ないのですが
セルの表示形式から「時刻」を選択すればちゃんと9：00　10：00　と表示されるはずです。

それからG列のデータはご自身で
9：00　10：00　11：00　・・・
と手入力してください。

これでこのSheetにデータを貼り付ければOKだと思います。

※　前回書いたように、配列数式の操作は必ず行ってみてください。

今度はちゃんと表示されますかね？m(_ _)m

お礼

NO2様

度々の返信ありがとうございます。　既に投稿しましたが、主旨は以下です。時間の表示はできました。

データの一部を下記に貼り付けます。

A B C D
date
2011/1/49:00:001036010330
2011/1/49:05:001035010330
2011/1/49:10:001035010330
2011/1/49:15:001035010330
2011/1/49:20:001035010340
2011/1/49:25:001035010340
2011/1/49:30:001036010340
2011/1/49:35:001036010350
2011/1/49:40:001036010350
2011/1/49:45:001036010350
2011/1/49:50:001037010350
2011/1/49:55:001036010340
2011/1/410:00:0　1036010350
2011/1/410:05:0　1037010350
2011/1/410:10:0　1037010360
2011/1/410:15:0　1037010360
2011/1/410:20:0　1038010360
2011/1/410:25:0　1038010370
2011/1/410:30:0　1038010370
2011/1/410:35:0　1039010370
2011/1/410:40:0　1039010380
2011/1/410:45:0　1039010380
2011/1/410:50:0　1039010380
2011/1/410:55:0　1039010380
2011/1/411:00:0　1038010380

これはこの日付のデータの9:00-11:00までにデータです。このデータの時間帯は5分刻みになっています。このデータの9:00-11:00の時間内でのC列の最大値と、D列の最小値の時間(B列に表示されている時間）を抽出したいというのが目的です。　

また、例えばC列で同じ最大値やC列で同じ最小値が複数出てきますが、その場合は最初に出てきた時間を表示させたいです。（例えば、上のデータではC列は10390が最大値で、これは10:35から10:55まで表示されているが、このような場合は10:35に最初に表示された時間だけを抽出したいと思っています。

これは一部ですが、当然2011年は12月の最終営業日までデータはあります。また、データ2001年まで遡り同様の作業をしなくてはなりません。

お分かりになりますでしょうか?　宜しくご教示お願いします。

回答No.5

MackyNo1

>CとD列に数字が表示されました。この数字を時間帯に表示させるにはどうすれば良いのでしょうか?

G列とH列の間違いですよね。
もし、0.????のような数字が表示されているなら、セルの書式設定で表示形式を時刻にしてください。

＞また、数式を見るとhourとなっています。これは例えば、9：00からスタートして1時間帯での最大値と最大値と最小値を抽出するという式なのでしょうか?

その通りです。

＞具体的に今しようとしていることは、例えば、例にある2011/1/4の9:00から11;00までのCとD列の各データ数値からこの9:00から11:00までの時間帯でのC列の最大値とD列の最小値を抽出することです。

時間帯の決め方が良くわかりませんが、特定の時間範囲ならその区切りはどのようにするのでしょうか？
単純にデータ全体での最大値の日付と時間を表示したいならもっと簡単な数式で表示できます。

＞そして、2011年だけではなく2001年まで遡って同様の作業をしようとしています。

１つのシートにすべての年度のデータが入っていて、年度ごとの最大値の時刻などを調べたいのでしょうか？

いずれにしろ、質問がいま一つ不明確ですので、最終的に表示したいデータのまとめ方のレイアウトを具体的に例示してください。

お礼

度重なる返信ご指導ありがとうございます。　説明が良くないですね。申し訳ありません。データの一部を下記に貼り付けます。

A B C D
date
2011/1/4 9:00:00 10360 10330
2011/1/4 9:05:00 10350 10330
2011/1/4 9:10:00 10350 10330
2011/1/4 9:15:00 10350 10330
2011/1/4 9:20:00 10350 10340
2011/1/4 9:25:00 10350 10340
2011/1/4 9:30:00 10360 10340
2011/1/4 9:35:00 10360 10350
2011/1/4 9:40:00 10360 10350
2011/1/4 9:45:00 10360 10350
2011/1/4 9:50:00 10370 10350
2011/1/4 9:55:00 10360 10340
2011/1/4 10:00:0　10360 10350
2011/1/4 10:05:0　10370 10350
2011/1/4 10:10:0　10370 10360
2011/1/4 10:15:0　10370 10360
2011/1/4 10:20:0　10380 10360
2011/1/4 10:25:0　10380 10370
2011/1/4 10:30:0　10380 10370
2011/1/4 10:35:0　10390 10370
2011/1/4 10:40:0　10390 10380
2011/1/4 10:45:0　10390 10380
2011/1/4 10:50:0　10390 10380
2011/1/4 10:55:0　10390 10380
2011/1/4 11:00:0　10380 10380

これはこの日付のデータの9:00-11:00までにデータです。このデータの時間帯は5分刻みになっています。このデータの9:00-11:00の時間内でのC列の最大値と、D列の最小値の時間(B列に表示されている時間）を抽出したいというのが目的です。　

また、例えばC列で同じ最大値やC列で同じ最小値が複数出てきますが、その場合は最初に出てきた時間を表示させたいです。（例えば、上のデータではC列は10390が最大値で、これは10:35から10:55まで表示されているが、このような場合は10:35に最初に表示された時間だけを抽出したいと思っています。

これは一部ですが、当然2011年は12月の最終営業日までデータはあります。また、データ2001年まで遡り同様の作業をしなくてはなりません。

お分かりになりますでしょうか?　宜しくご教示お願いします。

補足

PS

→１つのシートにすべての年度のデータが入っていて、年度ごとの最大値の時刻などを調べたいのでしょうか？

年度毎にシートは分かれています。毎営業日の9:00-10:00の間での最大値と最小値が出た時間をそれぞれ抽出したいと考えてます。

回答No.4

MackyNo1

以下のような数式を入力すればご希望の時間帯が表示できます。

C列の最大値
=MAX(INDEX((MAX(INDEX((HOUR($B$2:$B$100)=HOUR($F2))*$C$2:$C$100,))=$C$2:$C$100)*$B$2:$B$100,))

D列の最小値
=MAX(INDEX((MIN(INDEX((HOUR($B$2:$B$100)<>HOUR($F2))*10^10+$D$2:$D$100,))=$D$2:$D$100)*$B$2:$B$100,))

１時間ごとの時間帯を入力する欄は一番上のセルに開始時間の「9:00」を入力し、その下のセルには以下のような関数を入力して下方向にオートフィルする方が丸め誤差が生じないので良いと思います。

=TEXT(F2+"1：00","h:mm")*1

お礼

どうもありがとうございました。

CとD列に数字が表示されました。この数字を時間帯に表示させるにはどうすれば良いのでしょうか?

また、数式を見るとhourとなっています。これは例えば、9：00からスタートして1時間帯での最大値と最大値と最小値を抽出するという式なのでしょうか?

具体的に今しようとしていることは、例えば、例にある2011/1/4の9:00から11;00までのCとD列の各データ数値からこの9:00から11:00までの時間帯でのC列の最大値とD列の最小値を抽出することです。そして、2011年だけではなく2001年まで遡って同様の作業をしようとしています。

何か良い方法があればご教示宜しくお願いします。

回答No.3

KURUMITO

日付が入ってしかも時間ごとでC列の値が最大となる時間や最小となる時間を表示させたいとのことですね。
ここではある指定した日付での時間ごとのデータを表示させることにしますので、日付を含めて変わるデータを表示させるのでしたらここでの作業を参考に作ってみてください。
A列からC列までのデータが2行目から下方に入力されているとします。式が複雑になりますので作業列を作って対応することにします。
E2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(A2="","",A2+HOUR(B2)/24)

次にお求めのデータを表示させるわけですが例えばF2セルに表示したい日付を2011/1/4のように入力します。G2セルには表示したい時間で9:00、G3セルには10:00、G4セルには11：00のように下方に入力します。
H1セルにはC列で最大値を持つB列での時間、I1セルにはC列で最小値となるB列での時間などと文字列を入力します。
H2セルには次の式を入力して下方にオートフィルドラッグコピーします。

=IF(COUNTIF($E:$E,$F$2+$G2)=0,"",INDEX($B:$B,MATCH(MAX(INDEX($C:$C,MATCH($F$2+$G2,$E:$E,0)):INDEX($C:$C,MATCH($F$2+$G2,$E:$E,0)+COUNTIF($E:$E,$F$2+$G2)-1)),$C:$C,0)))

また、H2セルの式をI2セルにドラッグコピーします。その上で式の中でMAXの文字をMINの文字に変更します。次のような式になります。

=IF(COUNTIF($E:$E,$F$2+$G2)=0,"",INDEX($B:$B,MATCH(MIN(INDEX($C:$C,MATCH($F$2+$G2,$E:$E,0)):INDEX($C:$C,MATCH($F$2+$G2,$E:$E,0)+COUNTIF($E:$E,$F$2+$G2)-1)),$C:$C,0)))

この式を下方にオートフィルドラッグコピーします。
なお、H及びI列のセルの表示形式は時刻にします。
F2セルの日付を変えることで指定した日の時間ごとのデータを得ることができます。
最後にE列の作業列が目障りでしたら列を選択して右クリックし「非表示」を選択すればよいでしょう。

お礼

どうもありがとうございました。

凄い長い式ですね。今試してみます。丁寧に説明して頂きありがとうございました。

回答No.2

tom04

こんばんは！
＞時間ごとにC列のなかで・・・
とありますので質問では9時台のデータだけですが、
実際は他の時間帯のデータもある訳ですよね？

一応そういうコトだとしての一例です。
（日付の部分は無視していますので、A列は同一日とします。）

一例です。
データは2行目からあるとします。
↓の画像のように作業用の列を1列設けています。
作業列E2セルに
=IF(B2="","",FLOOR(B2,"1:00"))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

そしてH2セルに
=IF(COUNTIF(E:E,G2),INDEX($B$2:$B$100,MATCH(MAX(IF($E$2:$E$100=G2,$C$2:$C$100)),$C$2:$C$100,0)),"")

I2セルに
=IF(COUNTIF(E:E,G2),INDEX($B$2:$B$100,MATCH(MIN(IF($E$2:$E$100=G2,$D$2:$D$100)),$D$2:$D$100,0)),"")

どちらの配列数式になりますので、Shift+Ctrl+Enterキーで確定します。
この画面からコピー＆ペーストする場合は各セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので
Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

最後にH2・I2セルを範囲指定　→　I2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　数式は100行目まで対応できるようにしています。

参考になりますかね？m(_ _)m

お礼

どうもありがとうございました。

教えて頂いた通りやってみました。ご指摘の仮定は全て正しいです。E2セルにご指摘の式をペーストしてある時間帯まで下にコピーしました。数字が表示され途中で数字が変わりました。これは多分時間を表示すべきだと思うのですが、どうすれば良いでしょうか？この数字を時間に変更しなくてはならないと思います。

また、G列は何か数式を入力しないのでしょうか。それとも直入力で時間を表示させるのでしょうか?

HとI列にそれぞれご指摘の数式をペーストして下までのある時間帯までコピーしましたが、何も表示されません。

仮定されているように、毎営業日で、最大値と最小値のある時間帯を抽出したいのです。10年分のデータが10年分のシートに分かれて保有しています。

そこで、関数を見つけて各年度毎の毎営業日にある時間帯での最大値と最小値を抽出しようとしています。

何か関数でスピーディーに出来る方法があれば、伴わせてご教示頂ければ幸いです。宜しくお願い致します。

回答No.1

MarcoRossiItaly

お示しのデータがA～D列の1行目から入力されていると仮定します。

A列とC列以外のどこかのセルに、次の式を入力してください。

=INDEX($B:$B,MATCH(MAX($C:$C),$C:$C,0),1)

各関数の意味は、ネットで検索して調べましょう。
「B:B」の代わりに「$B$1:$B$100」などと記述されていても差し支えありません。

最小値のほうは、「MAX」を「MIN」に書き換えてください。
なおMINでは、未入力のセルは無視されるので、そのセルが最小とはなりません。

最大最小のときの時間を抽出するだけなら関数でなくても、「ピボットテーブル」（リボン「挿入」の中）でもできます。

更に簡単なのは、「並べ替え」（リボン「データ」の中）を実行後に、いちばん上やいちばん下の行を見るという方法もあります。
通し番号を先に振っておけば、いつでも元の順序に戻せます。

お礼

教えて頂きありがとうございます。

ピボットテーブルでも可能なんですね。知りませんでした。試行錯誤でやってみます。

ありがとうございました。