条件に合致する文字列を別シートに抽出するには

こんにちは。

エクセルで特定の条件にマッチするセルの隣のセルの文字列を別シートに抽出したいのですが、
どの関数を使えば良いか分かりません…。

ご存知の方がいらっしゃいましたら、ご回答頂ければ助かります。


※シート１に以下の様に入力されているとして、B列が「0」の隣のセル（A列）の文字列をシート２に抽出したいと思っています。

【シート１】
　　 A 　　B
1　[鈴木] [0]
2　[佐藤] [5]
3　[高橋] [0]

　　　↓

【シート２】
　　 A
1　[鈴木]
2　[高橋]

※佐藤は「5」なので抽出しない。


よろしくお願い致します。

No.2 ベストアンサー 回答者： bunjii 回答日時： 2014/11/15 08:31

1つの関数で目的の処理が出来るものはありませんので、幾つかの関数を組み合わせて1つの数式を構成します。

Sheet2の1行目から下へ抽出するときは次の数式で良いと思います。
=IFERROR(INDEX(Sheet1!A$1:A$3,SMALL(INDEX((Sheet1!B$1:B$3=0)*ROW(B$1:B$3),0),COUNTIF(Sheet1!B$1:B$3,">0")+ROW())),"")
Excel 2007以降のバージョンで再現できるはずです。
処理対象は元データがA1からB3までになっていますのでデータの行数に合わせて数式の行番号を変更してください。
尚、入れ子構造のINDEX関数で内側はSMALL関数に与える配列値を加工するためのものであり、他の方法でも処理可能です。
基本的には次の数式になります。
=IFERROR(INDEX(Sheet1!A$1:A$3,SMALL((Sheet1!B$1:B$3=0)*ROW(B$1:B$3),COUNTIF(Sheet1!B$1:B$3,">0")+ROW())),"")
この数式は計算過程で関数に与える値が配列値を扱うため数式バーへ入力後、Ctrl+Shift+Enterで確定しなければなりません。
確定後に数式バーの表示が数式全体を{}で括られた状態になります。
貼付画像はExcel 2013で検証した結果です。
D列がSheet2と等価の状態にしてありますので試してください。

No.5 回答者： ExcelDevTool 回答日時： 2014/11/21 16:19

なぜ関数を使わなければならないですか。

B列に対して、フィルタをかけて出た結果をシートにコピーすればいいじゃないですか。

No.4 回答者： mike_g 回答日時： 2014/11/15 20:30

Sheet1!D1: =IF(AND(B1<>"",B1=0),ROW(),"")

Sheet2!A1: =IF(ROW()>COUNT(Sheet1!D:D),"",INDEX(Sheet1!A:A,SMALL(Sheet1!D:D,ROW())))

No.3 回答者： MackyNo1 回答日時： 2014/11/15 18:35

１つの式で表示するなら、以下の式を入力して下方向にオートフィルコピーしてください。

=INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$B$1:$B$100>0)*1000+ROW($1:$100),),ROW(1:1)))&""

ちなみに、Sheet1のデータ範囲が１行目から１００行目のセル範囲にない場合は、$B$1:$B$100と$1:$100の数字だけを変更してください（数式が重くなるので必要以上に範囲を広げないこと）。

No.1 回答者： keithin 回答日時： 2014/11/15 03:35

非常にしばしば寄せられるご相談の一種ですが、エクセルには「条件に合致する一連の結果を列記してくれる関数」は有りません。

（他のソフト、たとえばGoogle SpreadSheetなど、にはそういう便利な関数が用意されているものもあります）


とは言え今はエクセルのお話なので。



方法１：簡単な方法　添付図参照
Ｂ列に名前
Ｃ列に数字、ただし簡単のためゼロじゃなく「１」を記入したデータを抽出する
Ａ１には
=IF(C1=1,COUNTIF(C$1:C1,C1),"")
以下コピー

シート２のＡ１：
=IF(ROW(A1)>MAX(Sheet1!A:A),"",VLOOKUP(ROW(A1),Sheet1!A:B,2))
またはエクセル２００７以降をご利用の場合は
=IFERROR(VLOOKUP(ROW(A1),Sheet1!A:B,2,FALSE),"")
以下コピー



方法２：イミフメイな方法
Ａ列に名前
Ｂ列に数字、ただし簡単のためゼロじゃなく「１」を記入したデータを抽出する

シート２のＡ１：
=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!B$1:B$99=1,ROW(Sheet1!B$1:B$99),9999),ROW(A1)))&""
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、以下コピー。