エクセルで期間内の最大、最小、以上、以下を求めたい

質問

エクセル2002、2003を使用してます。
下記のような場合、どのように式を作ればよろしいでしょうか？

A列に土日祝日以外の降順日付があります。（300～400行程度）
B列に順不同の整数があります。（日付の数と同数）※マイナスはありません。
また、A・B列の１～３行目程度にタイトル、項目名などの文字列もあります。

　　A　　　　　　　B
2012/9/7　　　5897
2012/9/6　　　155
2012/9/5　　　826
2012/9/4　　　7402

上記から、特定期間内（例えば、2012/1/10～2012/2/10等）で、B列の最大値、最小値、F3のF4以上、F3のF4以下を求めたいです。
計算式はセル参照を使用したいので、下記のようにしました。

F1=開始日
F2=終了日
F3=整数
F4=パーセント数字

出来る限り自分で調べまして下記のような計算式まで近づけたのですが、日付が昇順だとこれで出来るのですが、降順だと正しく求められません。降順に対応するにはどこを訂正したらよろしいでしょうか？
最大値を求める式：=MAX(INDIRECT("b"&MATCH($F$1,A:A)):INDIRECT("b"&MATCH($F$2,A:A)))
最小値を求める式：=MIN(INDIRECT("b"&MATCH($F$1,A:A)):INDIRECT("b"&MATCH($F$2,A:A)))

また、F3のF4以上、F3のF4以下を求める式はまったく分かりませんでした。
どうかご教示下さいますよう宜しくお願い致します。

回答No.1

keithin · ベストアンサー

ご質問で挙げられたようなデータなのでしたら
最大値：
=MAX(INDEX(B:B,MATCH(F1,A:A,0)):INDEX(B:B,MATCH(F2,A:A,0)))
最小値：
=MIN(INDEX(B:B,MATCH(F1,A:A,0)):INDEX(B:B,MATCH(F2,A:A,0)))
のようにできます

もし実際のデータが「連続した日付ではない」場合は
最大値：
=MAX(IF((F1<=A1:A999)*(A1:A999<=F2),B1:B999))
と記入し、コントロールキーとシフトキーを押しながらEnterで入力
最小値：
=MIN(IF((F1<=A1:A999)*(A1:A999<=F2)*(B1:B999>0),B1:B999))
と記入し、コントロールキーとシフトキーを押しながらEnterで入力
といった具合にすることもできます

指定期間内であってＦ３＊Ｆ４％以上の最小の数：
=MIN(IF((F1<=A1:A999)*(A1:A999<=F2)*(B1:B999>=F3*F4),B1:B999))
と記入し、コントロールキーとシフトキーを押しながらEnterで入力

指定期間内であってＦ３＊Ｆ４％以下の最大の数：
=MAX(IF((F1<=A1:A999)*(A1:A999<=F2)*(B1:B999<=F3*F4),B1:B999))
と記入し、コントロールキーとシフトキーを押しながらEnterで入力

お礼

皆様、ご回答くださりましてありがとうございます。
皆様のご回答を元に色んなケースを想定して動作確認を繰り返しておりました為に、お礼が遅くなりました。申し訳ございませんでした。
お陰様でなんとか理想的な形にすることが出来ました。
ご回答の中で一番理想形に近かったのは、No.1の方のお答えでした。
私の説明不足だった点があり、少し修正も加えましたが、どうやっても出来なかった事が出来るようになって、今まで手動でやってたものが自動で出来るようになり作業効率も正確性も格段に上がり、ほんとに皆様に感謝しております。
私自身もとても勉強になりました。ありがとうございました！！

回答No.5

tom04

こんばんは！
一例です。
（F4セルはパーセント表示にしておきます）

↓の画像で説明します。
作業用の列を1列設けます。
作業列C2セルに
=IF(COUNTBLANK($F$1:$F$2),"",IF(AND(A2>=$F$1,A2<=$F$2),ROW(A1),""))
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

E7～H7はすべて配列数式になってしまいますので、Shift＋Ctrl＋Enterキーで確定します。

E7セルに
=IF(COUNTBLANK(F1:F2),"",MAX(IF(C1:C1000<>"",B1:B1000)))

F7セルに
=IF(COUNTBLANK(F1:F2),"",MAX(IF(C1:C1000<>"",B1:B1000)))

G7セルに
=IF(SUMPRODUCT(($B$2:$B$1000>=$F$3*$F$4)*($C$2:$C$1000<>""))>=ROW(A1),INDEX($B$2:$B$1000,SMALL(IF($B$2:$B$1000>=$F$3*$F$4,IF($C$2:$C$1000<>"",$C$2:$C$1000)),ROW(A1))),"")

H7セルに
=IF(SUMPRODUCT(($B$2:$B$1000<=$F$3*$F$4)*($C$2:$C$1000<>""))>=ROW(A1),INDEX($B$2:$B$1000,SMALL(IF($B$2:$B$1000<=$F$3*$F$4,IF($C$2:$C$1000<>"",$C$2:$C$1000)),ROW(A1))),"")

最後にG7・H7セルを範囲指定　→　H7セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

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

※　極端にデータ量が多い場合、配列数式にするとPCにかなりの負担をかけてしまいます。　
※　作業列を使うのであればもう少し作業列を使って、配列数式にしない方法が良いかもしれません。

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

お礼

皆様、ご回答くださりましてありがとうございます。
皆様のご回答を元に色んなケースを想定して動作確認を繰り返しておりました為に、お礼が遅くなりました。申し訳ございませんでした。
お陰様でなんとか理想的な形にすることが出来ました。
ご回答の中で一番理想形に近かったのは、No.1の方のお答えでした。
私の説明不足だった点があり、少し修正も加えましたが、どうやっても出来なかった事が出来るようになって、今まで手動でやってたものが自動で出来るようになり作業効率も正確性も格段に上がり、ほんとに皆様に感謝しております。
私自身もとても勉強になりました。ありがとうございました！！

回答No.4

K Kazz（@JazzCorp）

期間（Ｆ１，Ｆ２）がデータよりも上下にはみ出していても可、逆転も可（下へのはみ出しはMATCH関数の「照合の型」のお陰）
ＭＡＸ：
=MAX(INDIRECT("B"&(MATCH(MIN(F$2,A$1),A:A,-1))):INDIRECT("B"&MATCH(MIN(F$1,A$1),A:A,-1)))
ＭＩＮ：
=MIN(INDIRECT("B"&(MATCH(MIN(F$2,A$1),A:A,-1))):INDIRECT("B"&MATCH(MIN(F$1,A$1),A:A,-1)))

Ｆ３、Ｆ４を使った問題は意味不明、同じくＭＡＸ／ＭＩＮなのか、数なのか？
Ｆ４（％）の定義も曖昧、１以下の値なのか、100までの値なのか？
いずれにしても補助計算列を使って検証を簡単にできるようにした方が賢明。

お礼

皆様、ご回答くださりましてありがとうございます。
皆様のご回答を元に色んなケースを想定して動作確認を繰り返しておりました為に、お礼が遅くなりました。申し訳ございませんでした。
お陰様でなんとか理想的な形にすることが出来ました。
ご回答の中で一番理想形に近かったのは、No.1の方のお答えでした。
私の説明不足だった点があり、少し修正も加えましたが、どうやっても出来なかった事が出来るようになって、今まで手動でやってたものが自動で出来るようになり作業効率も正確性も格段に上がり、ほんとに皆様に感謝しております。
私自身もとても勉強になりました。ありがとうございました！！

回答No.3

hallo-2007

後順に対応させたいなら
MATCH($F$2,A:A)の照合の型　で　-1を指定することが可能です。
MATCH($F$2,A:A,-1)と修正してみてください。
正し、開始日と終了日が実際にデータにない日を入力した場合などに
開始日、終了日を含むのか含まないのかで、微妙に答えが違うかもしれませんので、十分に検証してみてください。

＞F3のF4以上、F3のF4以下を求める式はまったく分かりませんでした。
求めいたいのは、データの個数でよろしいでしょうか？
同様な式で　COUNTIF関数で考えてみてはいかがでしょうか。

ただ、データ数もそうは多くないので、SUMPRODCT関数で総当たりに条件をあてはめてもそうは重くならないかと。

=SUMPRODUCT((A2:A400>F1)*(A2:A400<F2)*(B2:B400>F3*F4)*(B2:B400<F3*(1+F4)))
こんな感じでしょうか？

お礼

皆様、ご回答くださりましてありがとうございます。
皆様のご回答を元に色んなケースを想定して動作確認を繰り返しておりました為に、お礼が遅くなりました。申し訳ございませんでした。
お陰様でなんとか理想的な形にすることが出来ました。
ご回答の中で一番理想形に近かったのは、No.1の方のお答えでした。
私の説明不足だった点があり、少し修正も加えましたが、どうやっても出来なかった事が出来るようになって、今まで手動でやってたものが自動で出来るようになり作業効率も正確性も格段に上がり、ほんとに皆様に感謝しております。
私自身もとても勉強になりました。ありがとうございました！！

回答No.2

kybo

最大値

=MAX(OFFSET(INDIRECT("b"&MATCH($F$2,A:A,-1)),1,0):INDIRECT("b"&MATCH($F$1,A:A,-1)))

最小値

=MIN(INDIRECT("b"&MATCH($F$2,A:A,-1)):INDIRECT("b"&MATCH($F$1,A:A,-1)))

>F3のF4以上、F3のF4以下

とは、例えばF3が5000でF4が50%だと、F3×F4＝2500以下、以上という意味でしょうか？

とすると、

以下の数式をCtrlキーとShiftキーを押しながらEnter

F3のF4以上の最小値
=MIN(IF((A1:A1000>=F1)*((A1:A1000<=F2)*(B1:B1000>=(F3*F4))),B1:B1000))

F3のF4以下の最大値
=MAX(IF((A1:A1000>=F1)*((A1:A1000<=F2)*(B1:B1000<=(F3*F4))*(B1:B1000>0)),B1:B1000))

お礼

皆様、ご回答くださりましてありがとうございます。
皆様のご回答を元に色んなケースを想定して動作確認を繰り返しておりました為に、お礼が遅くなりました。申し訳ございませんでした。
お陰様でなんとか理想的な形にすることが出来ました。
ご回答の中で一番理想形に近かったのは、No.1の方のお答えでした。
私の説明不足だった点があり、少し修正も加えましたが、どうやっても出来なかった事が出来るようになって、今まで手動でやってたものが自動で出来るようになり作業効率も正確性も格段に上がり、ほんとに皆様に感謝しております。
私自身もとても勉強になりました。ありがとうございました！！