Excelの質問です。A列に、指定した文字列があれば、その文字列があるセルの行を、Sheet2に移動させたいです。

質問

今、Sheet1のA列、B列、C列に、以下のようなデータがあります。

A----------B----------C　・・・

どんぶり(長野県)----------ソースかつ丼----------駒ヶ根市・伊那市　　・・・
ラーメン(岐阜県)----------高山ラーメン----------高山市
蕎麦(長野県)----------高遠そば----------伊那市高遠町
どんぶり(長野県)----------さくら丼----------飯島町
その他麺類(長野県)----------ローメン----------伊那市高遠町
どんぶり(長野県)----------ほたる丼----------辰野町
どんぶり(長野県)----------紫輝彩丼----------宮田村
蕎麦(長野県)----------木曽すんきそば----------木曽町
どんぶり(長野県)----------ごぼとん丼----------松川町

・・・

このようなデータが、1000行ぐらいあるのですが、この中から、「A列に"どんぶり"もしくは"蕎麦"という用語を含むセルがあれば、そのセルがある行全てのデータ(A～L列ぐらいまであります。)を、Sheet2に移動させる」といったことは、できないでしょうか？

今は、「フィルタ→テキストフィルタ」で、A列に「"どんぶり"を含むor"蕎麦"を含む」という条件でソートを行い、ソートされたものをコピー⇒sheet2にペーストというやり方を実践しているのですが、このテキストフィルタを使ったやり方には、限界を感じていまして。

文字数の都合上、記載はしていないのですが、データは1000行ほどあり、A列には「パスタ(●●県)」「カレー(●●県)」といったデータもあります。
その中から、例えば「"どんぶり"を含むor"蕎麦"を含むor"パスタ"を含む」のように、文字列の条件を3つ以上指定する場合、テキストフィルタを使ったやり方では、それができないのです(ToT)

何か別のスマートな方法がございましたら、回答いただけないでしょうか？
よろしくお願いします<m(__)m>

回答No.3

KURUMITO · ベストアンサー

解答Ｎｏ2です。
ごめんなさい。Ｍ2セルに入力するセルに誤りがありました。次のようにしてください。

=IF((COUNTIF(A2,"*どんぶり*")+COUNTIF(A2,"*蕎麦*")+COUNTIF(A2,"*パスタ*"))>0,MAX(M$1:M1)+1,"")

パスタ*の次に"が抜けていました。

お礼

ありがとうございます！
ほんと気付かなくてすいません、完全にKURUMITOさんにおんぶにだっこしてもらって(>_<)

Sheet2に入力する関数は、かなり複雑ですね(汗)
もし、Sheet1のM列に0以外の数字がなければ、「""(空白)」を返し、それ以外の場合は、

INDEX(Sheet1!$A:$L,MATCH(ROW(A1),Sheet1!$M:$M,0),COLUMN(A1))

を、返すということで。

COLUMN関数↓
http://www.ecken.co.jp/func.htm
は、わかりやすいサイトを調べて理解できました。
初めてみたもので。

INDEXはなんか難しい印象があります・・・配列が「Sheet1!$A:$L」、行番号が「MATCH(ROW(A1),Sheet1!$M:$M,0)」、列番号が「COLUMN(A1)」ですか・・・「Sheet1!$A:$L」で、範囲を固定して、その範囲の中から、「MATCH(ROW(A1),Sheet1!$M:$M,0)」なので、A1の行番号と等しくなる、M列の番号を探すのですね。

「照合の種類」が"0"なのは、「検査値に完全に一致する場合」を調べるからみたいですね。

A列とM列に等しいデータを持つ行の番号が確定すれば、「COLUMN(A1)」で列の番号を指定し、IF関数における"偽"の場合を表示させるという感じですかね・・・いやはや、勝手に復習してしまってすいません。

ありがとうございました<m(__)m>

回答No.2

KURUMITO

シート1にはお示しの表があり1行目には項目名が2行目から下方にデータがあるとします。また、Ｌまでは表の範囲にあるとします。
シート1では作業列としてＭ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF((COUNTIF(A2,"*どんぶり*")+COUNTIF(A2,"*蕎麦*")+COUNTIF(A2,"*パスタ*))>0,MAX(M$1:M1)+1,"")

ここでひらがなや漢字の文字列以外の*やカッコなどを含めて必ず半角英数文字で入力します。

シート2では1行目にはシート1の項目名をコピーして貼り付けます。
Ａ2セルには次の式を入力してＬ2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTIF(Sheet1!$M:$M,ROW(A1))=0,"",INDEX(Sheet1!$A:$L,MATCH(ROW(A1),Sheet1!$M:$M,0),COLUMN(A1)))

これでシート1でどんぶり、蕎麦、パスタが入力されている行はすべてシート2に表示されます。検索の文字列がさらに増す場合には作業列でCOUNTIF関数を追加すればよいでしょう。

お礼

KURUMITOさんが書いてくださった関数を貼り付けてみたのですが、「入力した数式は正しくありません」と、エラーメッセージが出てきてしまいまして・・・ちゃんと半角英数にも変換したのですが・・・(ToT)
http://onnsei-okiba.radilog.net/image/8AD6909482AA82A482DC82AD93K97p82B382EA82DC82B982F1.jpg
貼りつける場所が違うのでしょうか！？

回答No.1

cistronezk

＞その中から、例えば「"どんぶり"を含むor"蕎麦"を含むor"パスタ"を含む」のように、文字列の条件を3つ以上指定する場合、テキストフィルタを使ったやり方では、それができないのです(ToT)

フィルタオプションなら３つ以上の条件も可能です。
http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-filter2.html#syousai

表に見出しを付けて抽出条件を、たとえば

＜分類＞
どんぶり
蕎麦
パスタ

のように入力し、その範囲を「フィルタオプションの設定」の「検索条件範囲」に指定します。

抽出先も指定できます。ただし別シートに出力するには、出力先の別シートでフィルタを実行する必要があります。

お礼

なるほど、リストを作ればいいのですね！
ありがとうございます(^_^;)