【EXCEL関数】違うシートに抽出結果を出したい

質問

一覧表から「○」の条件が付いたデータのみを別シートに関数で抽出したいです。
フィルタではなく関数を使用したいのは、抽出したデータから名前の定義を使用して
リスト表示をさせたいためです。ご教示のほど、宜しくお願いします。

シート１は一覧表となっており、Ａ列に「○」のついたデータのみをシート２に表示

シート１（一覧表）
　　Ａ　 Ｂ
１　 ○　　山田太郎
２　 ×　　佐藤次郎
３　 ○　　高橋三郎
４　 ×　　鈴木四郎
５　 ×　　中村五郎

シート２（抽出結果を表示させる）
　　Ａ　 Ｂ
１　 ○　　山田太郎
２　 ○　　高橋三郎
３　
４　
５

フィルタオプション、当方の考え得る限りの配列関数等、色々試しましたが、
不勉強のために上手く結果が表示されませんでした。
ご教示のほど、宜しくお願い致します。

回答No.3

dogs_cats · ベストアンサー

vlookupは検索元データの左側に作業列が無いといけませんが、index　matchでは右側に検索データがあっても構いません。

sheet1 C列を作業列
C1式
=IF(A1="○",ROW(),"")

sheet2　A1式
=IFERROR(INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)),"")
A１をB1にコピー
A1~B1を下方へオートフィル。
データが無い場合は空白を返すので、想定より多いセル範囲に数式をコピーしておけば、sheet1のデータが修正されても追随します。

お礼

ありがとうございます！
画像付きで分かりやすそうだったので一番に試してみたところ、
あっという間に解決しました！
数式の意味はこれからじっくり考えてみますが、本当に助かりました！

解決したのでベストアンサーにさせていただきます。
他の方もありがとうございました！

回答No.5

Prome_Lin

私の回答は、無視してください。

質問者の問題を「VBA」で解いてみたくなっただけです（自分の勉強用です）。

Option Explicit
Sub Test()
Dim c, i As Integer
c = 0
For i = 1 To Worksheets(1).Range("A1").End(xlDown).Row
If Worksheets(1).Cells(i, 1).Value = "○" Then
c = c + 1
Worksheets(2).Cells(c, 1).Value = "○"
Worksheets(2).Cells(c, 2).Value = Worksheets(1).Cells(i, 2).Value
End If
Next i
End Sub

一応、説明しておきます。

シート「１」の１行目から「Ａ」列にデータのある最終行（この場合、データが抜けている行があると、その前の行までになってしまいます）までを繰り返す。

シート「１」の「Ａ」列に「○」を発見したらシート「２」の「ｃ」行目の「Ａ」列に「○」を、「Ｂ」列にシート「１」からお名前を取り込む。

以上です。

お礼

あ、ありがとうございます！
いつかそんな難しいことにも挑戦してみます！

回答No.4

bunjii

シート2のA1へ次の数式を設定します。
=IF(COUNTIF(シート１!$A$1:$A$10,"○")>=ROW(),INDEX(シート１!A:A,LARGE(INDEX((シート１!$A$1:$A$10="○")*ROW(A$1:A$10),0),COUNTIF(シート１!$A$1:$A$10,"○")+1-ROW())),"")
右へB1までオートフィルコピーし、そのまま下へ10行までオートフィルコピーすれば10行分が完成します。
元データ（シート1）の行数が10行以上のときは数式内の行番号を変更してください。
数式の解説が必要のときは補足してください。

お礼

ありがとうございます！
他の方の回答で解決しましたが、いただいた数式も試してみます！

回答No.2

kagakusuki

今仮に、Sheet3のA列を作業列として使用するものとします。
　まず、Sheet3のA1セルに次の様な関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="○",ROW(),"")

　次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。

　次に、Sheet2のB1セルに次の様な関数を入力して下さい。

=IF(ROWS($1:1)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,SMALL(Sheet3!$A:$A,ROWS($1:1))))

　次に、Sheet2のA1セルに次の様な関数を入力して下さい。

=IF($B1="","","○")

　次に、Sheet2のA1～B1のセル範囲をコピーして、Sheet2のA列～B列の2行目以下に貼り付けて下さい。

　尚、もしSheet1のB列に入力されている氏名の中に、同じ名前が複数回現れる事が無い事が保障されている場合には、作業列を使わなくとも抽出する方法はあります。
　その場合、Sheet2のB1セルに入力する関数を次の様なものとすればOKです。(Sheet2のA列の関数には変更ありません)

=IF(ROWS($1:1)>COUNTIF(Sheet1!$A:$A,"○"),"",INDEX(Sheet1!$B:$B,IF(ROWS($1:1)=1,MATCH("○",Sheet1!$A:$A,0),MATCH("○",INDEX(Sheet1!$A:$A,MATCH(INDEX(B:B,ROW()-1),Sheet1!$B:$B,0)+1):INDEX(Sheet1!$A:$A,ROWS(B:B)),0)+MATCH(INDEX(B:B,ROW()-1),Sheet1!$B:$B,0))))

回答No.1

keithin

サルでも出来る簡単な方法としては

Sheet1：
Ａ　Ｂ　　Ｃ
印　検索　内容
○　　１　あれ
×　　　　これ
○　　２　それ
　　　　　どれ
×　　　　やれ

Ｂ２：
=IF(A2="○",COUNTIF(A$2:A2,A2),"")
以下コピー
と準備

Sheet2：
Ａ　　Ｂ
番号　内容
　１　あれ
　２　それ

A2:
=IF(ROW(A1)>MAX(Sheet1!B:B),"",ROW(A1))
B2:
=IF(A2="","",VLOOKUP(A2,Sheet1!B:C,2))
以下コピー

といった具合に順繰り拾っていけば，間違えなく出来ます。

お礼

ありがとうございます！
他の方の回答で解決しましたが、いただいた数式も試してみます！