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

Question

エクセル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以下を求める式はまったく分かりませんでした。
どうかご教示下さいますよう宜しくお願い致します。

keithin · Accepted Answer

ご質問で挙げられたようなデータなのでしたら最大値： =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で入力

tom04 · Answer

こんばんは！一例です。（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

JazzCorp · Answer

期間（Ｆ１，Ｆ２）がデータよりも上下にはみ出していても可、逆転も可（下へのはみ出しは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までの値なのか？
いずれにしても補助計算列を使って検証を簡単にできるようにした方が賢明。

hallo-2007 · Answer

後順に対応させたいなら

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)))
こんな感じでしょうか？

kybo · Answer

最大値

=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))

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

ご質問で挙げられたようなデータなのでしたら

こんばんは！

期間（Ｆ１，Ｆ２）がデータよりも上下にはみ出していても可、逆転も可（下へのはみ出しはMATCH関数の「照合の型」のお陰）

後順に対応させたいなら

最大値

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング