エクセルの関数

質問

A列に約５００行に渡ってランダムに５桁の数字（１００００～９９９９９でこの間抜けている数字はある）があります。（重複する数字はありません。）
　ここで、例えばB列にA列にある３００００～３９９９９までの該当する数字をB1、B2、B3・・・・と該当する数字だけ昇順に全部抽出したいのですが、この場合の式を教えて下さい。

回答No.6

DoragonFang · ベストアンサー

＃５ Doragonfang です。補足です。

>ただ、1行目にしないと正当に抽出しないんですね。

SAMLL関数の順位を指定する２番目の引数を ROW()関数で得ているからでしょう。

上から順に1、2、3と数字を入れていっても良いのですが、そんな迂遠な方法は意味がないので、出来るだけ簡単に１からの順番を得るためにこうしました。
ROW()関数は引数を指定しないと、自分自身のある行番号を数字で返します。
なので、この場合はＢ１セルでは、"1"、Ｂ２セルは"2"、・・と返しています。
したがって、たとえば、５行目から正常に表示されるのには５行目を"1"にしないといけないので、5-1=4 で、ROW() の部分を ROW()-4 と置き換える必要があります。
その例でいくと、

=IF(SUMPRODUCT(($A$1:$A$500<40000)*1)-SUMPRODUCT(($A$1:$A$500<=30000)*1)>=ROW()-4,SMALL($A$1:$A$500,SUMPRODUCT(($A$1:$A$500<=30000)*1)+ROW()-4),"")

という式になります（２カ所変更しています）。

回答No.5

DoragonFang

＃４　Doragonfangです。訂正です。

＃４だと降順になってしまいます。昇順でしたね。済みません。

Ｂ１に入れる式を次のにして、Ｂ２以下にコピーしてください。

=IF(SUMPRODUCT(($A$1:$A$500<40000)*1)-SUMPRODUCT(($A$1:$A$500<=30000)*1)>=ROW(),SMALL($A$1:$A$500,SUMPRODUCT(($A$1:$A$500<=30000)*1)+ROW()),"")

SMALLにします。

お礼

御回答ありがとうございます。まさにこれです。ただ、1行目にしないと正当に抽出しないんですね。

　皆様御回答ありがとうございました。この場をお借りしまして御礼申し上げます。

回答No.4

DoragonFang

少々複雑ですが、一発で出来る式を考えてみました。

Ｂ１セルに次の式を入力し、Ｂ２以下必要分（最大Ａ列のデータ分）、コピーしてください。

=IF(SUMPRODUCT(($A$1:$A$500>=30000)*1)-SUMPRODUCT(($A$1:$A$500>39999)*1)>=ROW(),LARGE($A$1:$A$500,SUMPRODUCT(($A$1:$A$500>39999)*1)+ROW()),"")

Ａ列の範囲（$A$1:$A$500）はデータ数に合わせて適宜変更してください。

SUMPRODUCT(($A$1:$A$500>=30000)*1)　の意味は、Ａ列のデータで、30000以上の数字の個数を求めています。*1が付いているのは、($A$1:$A$500>=30000)の比較文だけだとTRUEかFALSEが返って、式が0になってしまうため、*1で数字("1")にしています。

あとは式を見て頂いたら分かると思いますが、
IFは30000以上の数字の個数から40000以上の数字の個数を引いて、30000～39999の間の個数を求め、その個数以上は表示しない(""を表示する）ようにするためです。
そして、LARGE関数で、40000以上の数字の個数＋１目から、順に30000以上まで表示すればいいので、ROW()関数で1,2,3,4,5・・・の数字を得るようにしています。

たぶん、これでいいと思いますが、いかがでしょう？

回答No.3

imogasi

配列数式と言うもので一発で出きるようです。初めは半信半疑でしたが、こういう場合、配列数式の威力がすごいと思いました。
（データ）A1:A14
30000
30023
40002
20002
30234
30037
60001
50034
10011
32930
90234
40000
35555
30001
（関数式）
B列で適当の行数を範囲指定して
B1に=SMALL(IF(($A$1:$A$15>=30000)*($A$1:$A$15<40000),$A$1:$A$15,""),ROW())
といれて、SHIFTキーを押しながら、CTRLキーを押しつつ
ENTERをいれる。式の先頭と後尾に｛と｝が付く。
A15の１５は最終行より下であれば良い。
（式をOKWEBからコピーする場合B1に式を貼りつけ、数式バー部の式の最後をクリックしてSHIFTキーを押しながら、CTRLキーを押しつつ、ENTERをいれる。そしてB1で＋ハンドルを出して下方向へ引っ張り、＃Num!が出たら止める。
（結果）
30000
30001
30023
30037
30234
32930
35555

回答No.2

KenKen_SP

No.1です。不親切な回答でした。補足します。

500行ということで、No.1の方法を試される場合、B1セルの計算式を500行分オートフィルして下さい。

30000～39999の範囲にない数値がA列にある場合、B列には0が返り、範囲内の数字はそのままの値が返ります。B列に得られたその結果を、昇順ソートをかけ、0の値をもつセルを削除すれば、ご希望どおりの結果が得られるかと思います。

回答No.1

KenKen_SP

B1セルに計算式
=IF(AND(A1>=30000,A1<=39999),A1,0)

として、結果をコピー&値で貼り付けし、ソートします。0はセルごと削除。

でどうでしょう？