エクセルで指定した数字以下の数字を抽出して抜き出す

質問

エクセルで指定した数字以下の数字を抽出して抜き出したいです
例としては

A列　B列
いぬ　1
ねこ　2
うし　3
とら　1
はと　2

この表から空いているセルに任意の数字を入力して
それ以下の数字を別のセルに抽出したいです

たとえば2を入力したら
いぬ　1
ねこ　2
とら　1
はと　2

このような結果を別のセルに抽出したいです
オートフィルターとマクロは使わない方向でお願いします

似たような質問としては下のURLでしたが
http://okwave.jp/qa/q3200952.html?from=recommend

これでは指定した数字と同じものしか抽出出来なかったので質問させていただきました

回答No.4

bunjii · ベストアンサー

>似たような質問としては下のURLでしたが
>http://okwave.jp/qa/q3200952.html?from=recommend
>
>これでは指定した数字と同じものしか抽出出来なかったので質問させていただきました
同様の数式で提示の条件をExcel 2013で検証しました。
貼付画像のD1セルに次の数式をセットします。
D1セルを右にE1へ、下へE10までオートフィルでコピーしました。
=IFERROR(INDEX(A:A,LARGE(INDEX(($B$1:$B$10<=2)*($B$1:$B$10<>"")*ROW($B$1:$B$10),0),COUNTIF($B$1:$B$10,"<=2")-ROW()+1),1),"")
INDEX関数が入れ子になっていて、内側のINDEX関数は検索結果の配列をLARGE関数へ行番号の配列数として返します。
LARGE関数で数式の行に応じた検索結果の行番号を取り出して、外側のINDEX関数へ返します。
計算過程のエラーをIFERROR関数でエラーが起ったセルへ空の文字列（""）を返します。
内側のINDEX関数で配列値の論理演算を目的に合うよう組み込むことが重要です。
($B$1:$B$10<=2)*($B$1:$B$10<>"") → {1;1;0;1;1;0;0;0:0:0}
この部分が2以上で、然も、空欄ではないセルの配列が返りますので、これに行番号の {1;2;3;4;5;6;7;8;9;10} を乗じて {1;2;0;4;5;0;0;0;0;0} と言う配列数がLARGE関数へ返されます。
LARGE関数で取り出す順番は COUNTIF($B$1:$B$10,"<=2") が4を返しますので ROW() は数式の行番号が1のとき、4-1+1 → 4 となり、ROW()が2のときは 4-2+1 → 3 となり、順に取り出せます。
数式の位置が5行目以降のときはLARGE関数が0を返しますので外側のINDEX関数が#NUM!エラーを返しますので、IFERROR関数で空の文字列をセルへ返します。

お礼

丁寧な解説ありがとうございます
<=2の部分をセルに打ちこまれた数字になる様に変更で求めている機能になりました

回答No.3

Nouble

sheet1にあるデータを参照し
セルC1に書かれた値
其以下のものを抽出します

=INDIRECT(ADDRESS(SMALL(IF(sheet1!$B$1:$B$100<$C$1,ROW($B$1:$B$100),SIGN(ROW($B$1:$B$100))*9999999999),ROW(A1)),COLUMN(A1),1,1,"sheet1!"))

=OFFSET(sheet1!$A$1.SMALL(IF(sheet1!$B$1:$B$100<$C$1,ROW($B$1:$B$100),SIGN(ROW($B$1:$B$100))*9999999999),ROW(A1))-1,COLUMN(A1)-1,1,1)

どちらかを
適切な一つのセルに書き込み後
横に一つの
縦に必要量フィルください

回答No.2

Chiquilin

> 質問させていただきました
「したいです。お願いします」を質問とはいいませんけどね。

=IF(COUNTIF($B$1:$B$5,"<="&2)<ROW(C1),"",INDEX(A:A,SMALL(INDEX(($B$1:$B$5>2)*5+ROW($1:$5),0),ROW(C1))))

お礼

日本語が不自由でごめんなさい

回答No.1

MackyNo1

E1セルに上限数値を入力し、E2セル以下に動物の抽出結果を表示させるなら以下の式を入力して下方向にオートフィルします。

=INDEX(A:A,SMALL(INDEX(($B$2:$B$100>2)*100+ROW($2:$100),),ROW(1:1)))&""

動物名に重複データがない条件なら、F2セルには以下の式を入力し下方向にオートフィルします。

=IF(E2="","",VLOOKUP(E2,A:B,2,0))

お礼

画像付の解説ありがとうございます
おかげで全体の流れが掴みやすかったです