こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

-広告-

解決済みの質問

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

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

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

シート1(一覧表)
  A  B
1  ○  山田太郎
2  ×  佐藤次郎
3  ○  高橋三郎
4  ×  鈴木四郎
5  ×  中村五郎

シート2(抽出結果を表示させる)
  A  B
1  ○  山田太郎
2  ○  高橋三郎
3 
4 



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

投稿日時 - 2016-01-06 12:02:34

QNo.9106952

すぐに回答ほしいです

質問者が選んだベストアンサー

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)),"")
A1をB1にコピー
A1~B1を下方へオートフィル。
データが無い場合は空白を返すので、想定より多いセル範囲に数式をコピーしておけば、sheet1のデータが修正されても追随します。

投稿日時 - 2016-01-06 13:05:46

お礼

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

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

投稿日時 - 2016-01-07 11:37:21

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

-広告-
-広告-

回答(5)

ANo.5

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

質問者の問題を「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

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

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

シート「1」の「A」列に「○」を発見したらシート「2」の「c」行目の「A」列に「○」を、「B」列にシート「1」からお名前を取り込む。

以上です。

投稿日時 - 2016-01-06 21:31:21

お礼

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

投稿日時 - 2016-01-07 11:40:21

-広告-

ANo.4

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

投稿日時 - 2016-01-06 20:44:50

お礼

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

投稿日時 - 2016-01-07 11:39:40

ANo.2

 今仮に、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))))

投稿日時 - 2016-01-06 12:56:49

ANo.1

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

Sheet1:
A B  C
印 検索 内容
○  1 あれ
×    これ
○  2 それ
     どれ
×    やれ

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

Sheet2:
A  B
番号 内容
 1 あれ
 2 それ

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

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

投稿日時 - 2016-01-06 12:47:19

お礼

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

投稿日時 - 2016-01-07 11:39:16

-広告-
-広告-

あなたにオススメの質問

-広告-
-広告-