エクセル関数（抽出）について

質問

宜しくお願い致します。
Ａ列にランダムな数字が入力されている。（重複する数字はない）
Ｂ列にランダムな数字が入力されている。（重複する数字がある）

ここで、Ａ列のセルの数字をＢ列の任意値分全部抽出したいのです。
　　　Ａ　　　Ｂ
１　８１５　　９
２　３３０　　８
３　２０９　　６　
４　１１０　　９　
５　　２４　　９
６　７８２　　５
７　　　３　　９

これを以下のようにする方法を教えて下さい。

９　８１５
９　１１０
９　　２４
９　　　３

※このＡ列の”９”もセルの中で指定したいのです。

回答No.3

moon_piyo · ベストアンサー

抽出先などの指定がなかったので

C1: 抽出する条件値
D,E列: 抽出先領域

と勝手に設定してみました

下記のセルにそれぞれ式をいれてください

C1: 9
D1: =IF(COUNTIF($B$1:$B$99,$C$1)>=ROW()-ROW($B$1)+1,INDEX(B:B,SMALL(INDEX(ROW($B$1:$B$99)*($B$1:$B$99=$C$1)+70000*($B$1:$B$99<>$C$1),),ROW()-ROW($B$1)+1)),"")
E1: =IF(COUNTIF($B$1:$B$99,$C$1)>=ROW()-ROW($B$1)+1,INDEX(A:A,SMALL(INDEX(ROW($B$1:$B$99)*($B$1:$B$99=$C$1)+70000*($B$1:$B$99<>$C$1),),ROW()-ROW($B$1)+1)),"")

D1:E1を下方コピー(99行目まで)

回答No.8

maccheroni

A、B列にも、抽出する列にも列見出しがないと仮定して書きます。

C1に　９と入力。
D1に
=IF(COUNTIF(B:B,$C$1)<=ROW(),"",INDIRECT("A"&SMALL(IF($B$2:$B$20=$C$1,ROW($B$2:$B$20)),ROW())))
と入力し、Ctrlを押しながらShiftを押しながらEnterを押して確定します。
{=IF(COUNTIF(B:B,$C$1)<=ROW(),"",INDIRECT("A"&SMALL(IF($B$2:$B$20=$C$1,ROW($B$2:$B$20)),ROW())))}
という表示になったら、
D1を下までコピーします。

回答No.7

char0078

#5です。たびたびすいません。
B1の数式は　=IF($A$1=D1,ROW(),"")　でした。
！を１に読み替えてくださいm(__)m

回答No.6

char0078

#5です。表部分がつぶれちゃったので訂正します（汗）

　　　Ａ　　Ｂ　　　Ｃ　　　Ｄ　　Ｅ　　　Ｆ
１　　９　　１　　８１５　　９　　９　　８１５
２　　　　　　　　３３０　　８　　９　　１１０
３　　　　　　　　２０９　　６　　９　　　２４
４　　　　　４　　１１０　　９　　９　　　　３
５　　　　　５　　　２４　　９
６　　　　　　　　７８２　　５
７　　　　　７　　　　３　　９

回答No.5

char0078

A B C D E F
1 9 1 815 9 9 815
2 330 8 9 110
3 209 6 9 24
4 4 110 9 9 3
5 5 24 9
6 782 5
7 7 3 9

ご質問のA列、B列をC列、D列に移動しました。

A1には抽出条件である9を入力。

B1に　=IF($A$!=D1,ROW(),"")　と入力し、7行目までコピー。

E1に　=IF(ISERROR(VLOOKUP(SMALL($B$1:$B$7,ROW()),$B$1:$D$7,3,FALSE)),"",VLOOKUP(SMALL($B$1:$B$7,ROW()),$B$1:$D$7,3,FALSE))　と入力し、7行目までコピー。

F1に　=IF(E1="","",VLOOKUP(SMALL($B$1:$B$7,ROW()),$B$1:$D$7,2,FALSE))　と入力し、7行目までコピー。

これでE列、F列に抽出されます。

A1はD列の中から任意の値を入力してください。

数式は表が7行として作っていますので、行が増えたらE1とF1の式の　$B$7、$D$7を変更してください。あとは上記手順どおりで出来ます。

回答No.4

Wendy02

#1 さんのおっしゃるようなエレガントさには欠けるとは思いますが、以下のような式はどうですか？

A10:～
=IF(COUNTIF($B$1:$B$7,9)>=ROW(A1),INDEX($A$1:$B$7,SMALL(IF($B$1:$B$7=9,ROW($A$1:$A$7),""),ROW(A1)),2),"")

配列数式ですから、式を入力した後に、F2 を押して、Shift+Ctrl+Enterで、入力の確定をし直してください。

B10:～
=IF(COUNTIF($B$1:$B$7,9)>=ROW(B1),INDEX($A$1:$B$7,SMALL(IF($B$1:$B$7=9,ROW($A$1:$A$7),""),ROW(B1)),1),"")

こちらも、上記と同様に、式を入力した後に、Shift+Ctrl+Enterで、入力の確定をし直してください。

回答No.2

papayuka

B列でソートすれば並ぶと思うけど、、、

他には、
タイトル行を付けて、オートフィルタ
タイトル行を付けて、フィルタオプション

意味が違う？

お礼

御回答ありがとうございます

補足

大変恐縮ですが、全て関数でお願いしたいのです。

回答No.1

shkwta

複雑な関数を使ってエレガントに求める方法があるかもしれませんが、ここでは単純な方法を考えてみました。

仮に、数字が入力されているのは、1～30行とします。

(1) C1に、抽出する数字（たとえば9）
(2) C2に、=COUNTIF(B1:B30,C1)
(3) D1に、=IF(B1=$C$1,COUNTIF($B$1:B1,$C$1),"")
(4) E1に、=A1
(5) D1～E1をD2～E30にコピー
(6) F1～F30に、1～30の数を入力
(7) G1に、=IF(F1<=$C$2,$C$1,"")
(8) H1に、=IF(F1<=$C$2,VLOOKUP(F1,$D$1:$E$30,2,FALSE),"")
(9) G1～H1を、G2～H30にコピー

お礼

御回答ありがとうございます。

補足

その複雑な方法を宜しくお願い致します。