範囲内の条件検索

質問

教えてください。。。
数値の入った、複数のセル内の">2000",">3000",">4000"...といったふうに設定し、SMALL関数を使った式を作りたいのですが、うまくいきません。
1列内を選択し条件に合った抽出をしたいのですが。。。

よろしくお願いします。

回答No.3

bunjii · ベストアンサー

>数値の入った、複数のセル内の">2000",">3000",">4000"...といったふうに設定し、SMALL関数を使った式を作りたいのですが、うまくいきません。
Excel 2007以降のバージョンを使用の場合は次の数式で良いと思います。
但し、質問に添付の画像ではH9からJ9に1000、2000、3000となっておりますが、K9に4000を追加して頂く必要があります。
H10=IFERROR(SUMPRODUCT(SMALL(($E$4:$E$29>=H$9)*($E$4:$E$29<I$9)*$F$4:$F$29,COUNTIF($E$4:$E$29,"<"&H$9)+COUNTIF($E$4:$E$29,">="&I$9)+ROWS(H$10:H10))),"")
SMALL関数の代わりにLARGE関数を使う方が少し簡単になります。
H10=IFERROR(SUMPRODUCT(LARGE(($E$4:$E$29>=H$9)*($E$4:$E$29<I$9)*$F$4:$F$29,COUNTIFS($E$4:$E$29,">="&H$9,$E$4:$E$29,"<"&I$9)-ROWS(H$10:H10)+1)),"")

お礼

完璧に表示されました。
ありがとうございます。
ただ、使ってる関数が知らない関数もあるので今から解読していきます*^^*
ありがとうございました。

回答No.2

tom04

No.1です。
投稿後気づきました。

＞複数のセル内の">2000",">3000",">4000"...といったふうに
というコトですので、前回の数式を少し変更して
=IFERROR(SMALL(IF(($E$1:$E$1000>H$4)*($E$1:$E$1000<=H$4+1000),$F$1:$F$1000),ROW(A1)),"")
として配列数式にしてください。
（等号が　あり・なし　の違いです）

※　1000・2000・・・の区切りがはっきりしませんので
「等号」を入れるか？入れないか？は適宜判断してください。m(_ _)m

お礼

ありがとうございます。
わたしの貼り付けがまずいのか、うまく表示がされませんでした--；
でも、新たな関数が発見できたので良かったです！！ありがとうございました。

回答No.1

tom04

こんばんは！
一例です。

↓の画像でH5セルに
=IFERROR(SMALL(IF(($E$1:$E$1000>=H$4)*($E$1:$E$1000<=H$4+1000),$F$1:$F$1000),ROW(A1)),"")
配列数式になりますので、Ctrl+Shift+Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　H5セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl+Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
これを列・行方向（行方向に関しては10行）にフィルハンドルでコピーすると
画像のような感じになります。

※　配列数式はPCにかなりの負担をかけ計算速度が落ちます。
データ量が極端に多い場合は別の方法を考える必要があります。
今回は1000目まで対応できる数式にしていますが、
3000行程度であれば問題ないと思います。m(_ _)m