[[20170319004925]]　『特定の文字を含むセルを別シートに抽出させたい』（ゆみぽん）　ページの最後に飛ぶ

『特定の文字を含むセルを別シートに抽出させたい』（ゆみぽん）

こんばんわ。

特定の文字を含むセルを別シートに抽出したいです。

sheet1　と　Sheet2　に名前の一覧があります。
sheet1　はB列に名前が
sheet2　はC列に名前がずらっと入っています。

その中から特定の文字を含むものを
sheet3　のD列にずらっと表示させたいです。

<sheet1>
B
山田
田中
山内
岡野

<sheet2>
C
吉田
川村
鈴木

・・・ここからたとえば「田」を含む人だけ抽出して
下記のように表示させたいです。

<sheet3>
D
山田
田中
吉田

よろしくお願いします！

＜使用 Excel：Excel2007、使用 OS：unknown ＞

フィルターで、「田」を含むものだけを抽出し、シート３にコピペ
これをシート１とシート２でそれぞれ実行するとよいです。

（マナ） 2017/03/19(日) 06:55

マナさん

ありがとうございます。
手動では教えていただいた方法でできるのですが
毎日の作業になりますのでできれば自動で行いたいのです。

自動でできる方法を、お待ちしております。
言葉足らずですみません。
（ゆみぽん） 2017/03/19(日) 17:30

 >自動でできる方法

 イメージしているのは、関数処理ですか？　マクロ処理ですか？

 マクロ処理でよければ、マナさんアドバイスの操作をマクロ記録して、それをブラッシュアップすれば
 ワンクリックで自動処理ということになりますね。

（β） 2017/03/19(日) 18:05

βさん

コメントありがとうございます！
複数の人間が使うのと、私自信がメンテナンスできなさそうなので
マクロではなく出来れば関数で処理したいです・・・
（ゆみぽん） 2017/03/21(火) 13:39

マクロがだめだと、わたしにはこんなことしかできません。

１）Sheet3のD2に=Sheet1!B2
２）下方向に、D1001までコピー
３）Sheet3のD1002に=Sheet2!C2
４）下方向に、D2001までコピー
５）Sheet3のD列を選んでフィルター
６）D1の▼をクリックし、検索欄に「田」と入力

データを更新したときは、
７）もう一度、D1の▼をクリックし、検索欄に「田」と入力

（マナ） 2017/03/21(火) 21:26

＞毎日の作業になりますので

毎日データだけが更新されるのではなく
シート自体が新しくなるなら無理でした。

（マナ） 2017/03/21(火) 21:36

 マナさん回答と同じく、各シート１行目がタイトル、データは２行目から。
 Sheet1、Sheet2 ともに　データが1000行以内　として。

 Sheet3 の右のほうたとえば Z列を作業列として使います。

 名前ボックスに Z2:Z1001 といれEnter。その状態で　数式バーに　 =IF(COUNTIF(Sheet1!B2,"*田*"),Sheet1!B2,"")　これを Ctrl/Enter で入力。
 名前ボックスに Z1002:Z2001 といれEnter。その状態で　数式バーに　 =IF(COUNTIF(Sheet2!C2,"*田*"),Sheet2!C2,"")　これを Ctrl/Enter で入力。

 で、D2 : =IFERROR(INDEX(Z:Z,SMALL(IF(Z$2:Z$2001<>"",ROW(Z$2:Z$2001)),ROW(A1))),"")
 これを　Ctrl/Shift/Enter で入力し、下に D2001 あたりまでフィルコピー。

 2000行の配列数式ですのでスマートではないですが。
 通常数式の回答も、専門家さんからアップされると思います。

（β） 2017/03/21(火) 22:18

前提
１．Sheet1とSheet2のデータは最大1000行と仮定します。
２．Sheet1とSheet2に作業列（仮にH列）を使います。

Sheet1とSheet2のH列につぎの式を入力します。

H1: =IF(ISERROR(FIND("田",C1)),"",2+ROW(A1)/1000)

必要数、下にコピーします。

Sheet3!D1: =IFERROR(INDEX(INDIRECT("Sheet"&INT(SMALL(Sheet1:Sheet2!$H$1:$H$10,ROW(A1)))&"!B1:C100"),MOD(SMALL(Sheet1:Sheet2!$H$1:$H$10,ROW(A1)),1)*1000,INT(SMALL(Sheet1:Sheet2!$H$1:$H$10,ROW(A1)))),"")

この式も必要数、下にコピーします。
データが1000行を超える場合には式の「1000 → 10000」などと修正します。

（メジロ） 2017/03/21(火) 22:41

＞Sheet1とSheet2のH列につぎの式を入力します。
＞H1: =IF(ISERROR(FIND("田",C1)),"",2+ROW(A1)/1000)

この部分の式の書き込みをミスしました。
以下のように訂正してください。

Sheet1!H1: =IF(ISERROR(FIND("田",B1)),"",1+ROW(A1)/1000)

Sheet2!H1: =IF(ISERROR(FIND("田",B1)),"",2+ROW(A1)/1000)

（メジロ） 2017/03/21(火) 22:45

度々申し訳ございません。

行数の過程は「1000行」ではなく「1000行未満」とします。
（メジロ） 2017/03/21(火) 22:53

マナさん

ありがとうございます。
やってみましたが毎回「田」を指定しなければならないのでちょっと無理でした。
でもありがとうございました！
（ゆみぽん） 2017/03/28(火) 17:55

βさん

ありがとうございます！
やってみましたが、なんかうまくいきません・・ちょっとチャレンジしてみます。

関係ないかもしれませんが「名前ボックス」に入れるときはどこのセルを選択した状態でいれればいいですか？
（また、これってどういう意味のある作業ですか？）

エクセルに詳しくないので、面白く興味があります！
（ゆみぽん） 2017/03/28(火) 17:57

 >（また、これってどういう意味のある作業ですか？）

 たとえば

 名前ボックスに Z1002:Z2001 といれEnter。その状態で　数式バーに　 =IF(COUNTIF(Sheet2!C2,"*田*"),Sheet2!C2,"")　これを Ctrl/Enter で入力。

 この部分で説明します。

 『ふつう』にやれば

 ・ Z1002 に　=IF(COUNTIF(Sheet2!C2,"*田*"),Sheet2!C2,"")　を　ふつうに Enter で入力する。
 ・ それを Z2001 まで　下にフィルコピー

 です。

 で、この　1000セルへの数式を一括して入力する方法として

 ・マウスでZ1002:Z2001 を選択する
 ・=IF(COUNTIF(Sheet2!C2,"*田*"),Sheet2!C2,"")　を　Ctrl/Enter で入力する。

 これで、↑で書いた　『ふつう』にやった場合と同じ結果になります。

 領域が小さければマウスで選択するのも苦ではないと思いますが 1000セルですから
 スクロールしているうちに手が滑って、何度も選択のやり直しをしなければいけないかも
 しれません。（とくにβは不器用なので）

 そういった場合に、名前ボックスに Z1002:Z2001 といれて Enter　すると、
 ここで指定した領域が選択されます。

 つまり、マウスでZ1002:Z2001 を選択する　のと同じことになります。

（β） 2017/03/29(水) 08:41

メジロさん

ありがとうございます！
出来ました！

なにか空白のセルと計算をさせてるんでしょうか・・？
全然式の意味を理解できなかったですが、やりたい事は出来たので感動です！
面白いですね・・エクセル。
（ゆみぽん） 2017/03/29(水) 10:47

βさん

説明ありがとうございます！
これは便利ですね！
永遠と下まで選びながらスクロールさせることが時々ありましたので
今度活用させていただきます！

聞きたい事以外のことも知れてうれしいです！
（ゆみぽん） 2017/03/29(水) 10:49

この式で最初はうまくいきました。

ところが元のシートsheet1とsheet2の名前を変更したら出来なくなってしまいました。
式の中の3ケ所の名前もシートの名前に変えましたが出来なくなりました・・
日本語の名前だとムリだったりするのでしょうか

（ゆみぽん） 2017/03/31(金) 16:09

 ぱっと見ですが、INDIRECTの後ろの"Sheet"もシート名を指定していますね。
 INT関数で整数を返しているので、今までSheet1とかSheet2だった名前を変えたいなら整数の部分はそのままにした方がよろしいかと。
（2Win） 2017/04/03(月) 08:56

2Winさん

おはようございます！

"Sheet"というシートは存在しないのですが、（あるのはsheet1,sheet2,作業シート）
これが何かにかかわってきているということでしょうか？

sheet1とsheet2の名前をたとえば「栄」「福岡」と変えたい場合、この"Sheet"はどうすればいいのでしょうか。。

すみません、勉強不足で「整数の部分はそのまま」というのが意味がわからなくて・・

（ゆみぽん） 2017/04/03(月) 09:43

 いえ、"Sheet"&INT(SMALL(Sheet1:Sheet2!$H$1:$H$10,ROW(A1)))&"!B1:C100")
 これはSheetという文字の後ろに&を使って数字を足しているんですね。
 訳してみますと、
 "Sheet"という文字の後ろにSheet1とSheet2のH1からH10の範囲でA1の行番号(Row関数)番目に小さい数(SMALL関数)を小数点切捨て(INT関数)でくっつける となります。
 例えばSheet1とSheet2のH1からH10の範囲で１番目に小さい数が1だった場合は
 Sheet1というシート名を指定しているということになります。
 つまり"Sheet1!B1:C100"となるわけです。

 なので、シート名を変える場合は一番上に書いた式を変えないとエラーになります。
（2Win） 2017/04/03(月) 10:06

2Winさん

なるほどです！

ということは・・
そもそもシート名が日本語でお互い関連のない文字列だった場合（「名前1」「名前2」とかではなく「栄」「福岡」だった場合）はこの式ではつかえない・・ということになりますか。

Sheetっていう文字にくっつけることによって式が動いているのだから無理ってことですよね・・

シート名は随時変わると想定して運用するにはどのような式になるのでしょうか・・
（ゆみぽん） 2017/04/03(月) 10:44

 うーん、式が複雑なのでちょっと別の方法になりますが。

 Sheet3の作業列としてJ・K・L列を使います。
 栄シートの値はB2からB1001まで
 福岡シートの値はC2からC1001までとします。

 Sheet3 K1=IF(COUNTIF(栄!B2,"*田*"),栄!B2,"")
 1000行くらい下へコピー
 Sheet3 L1=IF(COUNTIF(福岡!C2,"*田*"),福岡!C2,"")
 1000行くらい下へコピー

 Sheet3 J1=IF(INDEX(K:L,MOD(ROW(K1001),1001)+1,ROW(K1001)/1001)=0,"",INDEX(K:L,MOD(ROW(K1001),1001)+1,ROW(K1001)/1001))
 2000行くらい下へコピー

 あとはD2に=IFERROR(INDEX(H:H,SMALL(IF(H$2:H$2001<>"",ROW(H$2:H$2001)),ROW(A1))),"")を配列変換（Ctrl + Shift + Enter)
 下に2000行くらいコピー

 βさんの掲示された方法とほとんど変わらないですが、これくらいしか思いつかないです。

（2Win） 2017/04/03(月) 17:27

2Winさん

ありがとうございます。

J・K・L列には「田」を含むセルが表示されました！

ですが、D2には何も表示されません。
下記の式でＨとA1を選んでいるのはなぜですか？
私のエクセルでは空白の行です・・。

=IFERROR(INDEX(H:H,SMALL(IF(H$2:H$2001<>"",ROW(H$2:H$2001)),ROW(A1))),"")

（ゆみぽん） 2017/04/03(月) 19:47

 あ、失礼しました。H列はJ列の間違いです。

 =IFERROR(INDEX(J:J,SMALL(IF(J$2:J$2001<>"",ROW(J$2:J$2001)),ROW(A1))),"")
（2Win） 2017/04/04(火) 08:55

 ちょこっと修正
  =IFERROR(INDEX(J:J,SMALL(IF(J$2:J$2002<>"",ROW(J$2:J$2002)),ROW())),"")
（2Win） 2017/04/04(火) 09:30

コメント返信：

[ 一覧(最新更新順) ]

[[20170319004925]] 『特定の文字を含むセルを別シートに抽出させたい』（ゆみぽん） ページの最後に飛ぶ

[[20170319004925]]　『特定の文字を含むセルを別シートに抽出させたい』（ゆみぽん）　ページの最後に飛ぶ