エクセル・順位関係のデータ抽出

質問

エクセルの関数に関する質問です。

名前　１点数　１順位　２点数　２順位
○○　　○　　　○　　　○　　　○　
○○　　○　　　○　　　○　　　○　
○○　　○　　　○　　　○　　　○　

となっている表がsheet1にあったとき、１順位と２順位のいずれかが20位以内に入る人のデータだけを集めた表を、sheet2に抽出したいです。関数などを用いて、sheet1の表にデータを入れ込めば一発で結果がsheet2に表示されるようなフォーマットを作りたいのですが、方法を教えてください。

回答No.5

jindon · ベストアンサー

jindonです。

質問をよく読んでいませんでした。

sheet2のA2に

=INDIRECT("sheet1!a"&SMALL(IF((Sheet1!$C$2:$C$100<=20)+(Sheet1!$E$2:$E$100<=20),ROW($A$2:$A$100)),ROW(A2)))

として、CTRL + SHIFT + ENTER で確定、0が出るまで下方コピー。

sheet2のB2に

=VLOOKUP($A2,Sheet1!$A$2:$E$100,COLUMN(A1)*2,FALSE)

として、C2にコピー。

B2 と C2 を下方コピーです。

回答No.4

jindon

配列式です

データが100行までとして

F2に

=INDIRECT("a"&SMALL(IF(($C$2:$C$100<=20)+($E$2:$E$100<=20),ROW($A$2:$A$100)),ROW(A1)))

として、Ctrl + Shift キーを押しながら Enter キーで確定します。数式が{ }で括られたことを確認してください。
必要分下方コピー

C列、又はE列が20以下のA列の値を返します。

回答No.3

imogasi

（例データ）Sheet1のA1:F８．B列D列は手抜きで略。F列は下記の関数式の結果。
A列　　　B列　　　C列　　　　D列　　　E列　　　F列　
順位１ 順位２
a 2 10 1
b 34 5 2
c 31 65
d 23 34
f 19 18 3
g 6 45 4
h 16 18 5
F2に=IF(OR(C2<21,E2<21),MAX($F$1:F1)+1,"")
と入れて、下に複写。結果は上記のとおり。
（Sheet２に）
A2に=INDEX(Sheet1!$A$2:$E$8,MATCH(ROW()-1,Sheet1!$F$2:$F$8,0),COLUMN())
と入れて、＋ハンドルを出し、E列まで横に引っ張り、その後、A2:E2を範囲指定し＋ハンドルを出してA6まで引っ張る。
A7以下に引っ張ってもエラーを出ないようにする関数式は考えてください。取りあえず略。

回答No.2

SAKURAMYLOVE

ちょっと工夫が要りますが、以下の手順でやってみてください。
(1)Sheet1の表にA列とB列を挿入してセルＣ１を名前、Ｄ１を１点数、Ｅ１を１順位・・・とする。
(2)Ａ２に以下の式を入力し、必要行までコピーする
　=IF(OR(E2<=20,G2<=20),"○","")
(3)Ｂ２に以下の式を入力し、必要行までコピーする
　=COUNTIF($A2:A$2,"○")
(4)Sheet2にＡ１にNo.、Ｂ１に名前、Ｃ２に１点数、Ｄ１に１順位・・・、Ｆ１に２順位を入力する。
(5)Ａ２以下、適宜1,2,3,...と連番をふる
(6)Ｂ２からＦ２まで以下の式を入力し、以下下の行へコピーする。
　ただし、Sheet1のデータの範囲は100行まであるものとします。
　B2=IF(ISNA(VLOOKUP($A2,Sheet1!$B$2:$G$100,2,0)),"",VLOOKUP($A2,Sheet1!$B$2:$G$100,2,0))
　C2=IF(ISNA(VLOOKUP($A2,Sheet1!$B$2:$G$100,3,0)),"",VLOOKUP($A2,Sheet1!$B$2:$G$100,3,0))
　D2=IF(ISNA(VLOOKUP($A2,Sheet1!$B$2:$G$100,4,0)),"",VLOOKUP($A2,Sheet1!$B$2:$G$100,4,0))
　E2=IF(ISNA(VLOOKUP($A2,Sheet1!$B$2:$G$100,5,0)),"",VLOOKUP($A2,Sheet1!$B$2:$G$100,5,0))
　F2=IF(ISNA(VLOOKUP($A2,Sheet1!$B$2:$G$100,6,0)),"",VLOOKUP($A2,Sheet1!$B$2:$G$100,6,0))

Sheet1のA,B列は、作業用ですので、必要がなければ、非表示にしてください。

回答No.1

haruka1234567890

１順位　と　２順位を左の列にコピー

名前の列の左に2列挿入し１順位　と　２順位を表示
（A1には、=E1　B1には、=G1　とする）

そうすれば、VLOOKUP関数が使えます。