[[20140803202012]]　『文字の一部で他の文字を検索する』（バドファン）　ページの最後に飛ぶ

『文字の一部で他の文字を検索する』（バドファン）

 sheet1
A	　　　　B	　　　　C
	あああ1	
	ああああ1	
	いい6	
	ううう3

 sheet2
A	　　　　B
トマト	　ああ
りんご	　うう
すいか	　いい

 やりたい事
sheet2のB列の文字で上から順番にsheet1のB列の文字を順次上から検索し
一部同じならsheet2のA列を抽出しsheet1のA列に入力する

 又、A列上下違っていたら10列ほど下に罫線を入力する。

 A	　　　　B	　　　　C
トマト	　あああ1	
トマト	　ああああ1
－－－－－－－－－－－－－－－－－－－－
すいか	　いい6
－－－－－－－－－－－－－－－－－－－－
りんご	　ううう3	
－－－－－－－－－－－－－－－－－－－－

 同じ様な物を探しましたが応用がききません宜しくお願いします。

＜使用 Excel：Excel2007、使用 OS：Windows7 ＞
（バドファン） 2014/08/03(日) 20:32

 Sheet1

 A1 =LOOKUP(1,0/(LEFT(B1,MAX(1,INDEX(LEN(Sheet2!B$1:B$20),)))=Sheet2!B$1:B$20),Sheet2!$A$1:$A$20)
 下へコピー。

 ＞又、A列上下違っていたら10列ほど下に罫線を入力する。

 10「列」下って意味がよーわからんので
 罫線は条件付書式かね？
　
　
（GobGob） 2014/08/04(月) 08:12

 ＞又、A列上下違っていたら10列ほど下に罫線を入力する。
 １０列幅分、下線を引くっていう意味ではないですか。

 いずれにせよ、条件付き書式だと思いますが。
（Mook） 2014/08/04(月) 08:37

 あー。なるほどっす。

 A1からJ4を選択して条件付書式の数式で

 =AND($A1<>"",$A1<>$A2)  下罫線設定
　
（GobGob） 2014/08/04(月) 09:05

そうです、10列幅分のほうが分かり易かったです。
（バドファン） 2014/08/04(月) 17:56

GobGobさん　お世話になります。
Sheet1

 A1 =LOOKUP(1,0/(LEFT(B1,MAX(1,INDEX(LEN(Sheet2!B$1:B$20),)))=Sheet2!B$1:B$20),Sheet2!$A$1:$A$20)
の関数で出来ました。意味はよく分からないけど数百行この関数で抽出して値コピーしたらいい。
ありがとうございました。

Mookさんもありがとうございました。

マクロでは
If s Like "*ああ*" Then で*　*の中に順次　*うう*　　　*いい*とか順番に入れて検索方法は難しいのですかね？

（バドファン） 2014/08/04(月) 21:24

GobGobさん　行の多いので行ったら38行以降がｴﾗｰ#N/Aが出ます、数が多いとダメなんですね。

やっぱりマクロじゃないとダメなのかなぁ。
（バドファン） 2014/08/05(火) 18:09

 ＞行の多いので行ったら38行以降がｴﾗｰ#N/Aが出ます、数が多いとダメなんですね。 
 試した式と、エラーが出るサンプルデータをのせてみられてはどうでしょう？
 　
（HANA） 2014/08/06(水) 16:35

ﾃﾞｰﾀが何千行も有る会社のﾃﾞｰﾀなのでダメですが単に数字とアルファベットが混在した文字列です、
1FB101、5GS200、とかが有る文字を　その一部で検索する、1FB、　5GS2とかです数が多いとダメなんですかね、関数はA1 =LOOKUP(1,0/(LEFT(B1,MAX(1,INDEX(LEN(Sheet2!B$1:B$20),)))=Sheet2!B$1:B$20),Sheet2!$A$1:$A$20)　　
教えていただいた（GobGob）さんの関数です
（バドファン） 2014/08/06(水) 22:40

 ＞ﾃﾞｰﾀが何千行も有る会社のﾃﾞｰﾀなのでダメですが
 全部を載せてもらう必要はありません。
 不具合が出るサンプルデータであれば　データ量が少なくても有益です。
 むしろ、大量のデータをのせて
 「この中のどこかのデータが不具合を起こしているから
 　突き止めて、対応できる式を考えて」
 っていう方が、よくないと思います。

 実際に不具合が出るデータを持っておられるのですから
 少しずつデータを少なくして　悪さをしているデータを探してみてください。

 例題では、sheet2のB列の最大文字数は「2」
 　　　　　sheet1のB列も二文字以上だったり、必ずヒットするものになっています。
 そのあたりのデータの違いじゃないかと思いますが。

 ＞関数はA1 =LOOKUP(1,0/(LEFT(B1,MAX(1,INDEX(LEN(Sheet2!B$1:B$20),)))=Sheet2!B$1:B$20),Sheet2!$A$1:$A$20)
 これだと、20セルまでの範囲ですよね？
 Sheet2の方のデータは20件しか無いのでしょうか？

 ＞If s Like "*ああ*" Then で*　*の中に順次　*うう*　　　*いい*とか順番に入れて検索方法は難しいのですかね？ 
 ループ処理について調べてみてください。
 たとえば、Sheet2をアクティブにして
    Sub TEST()
    Dim i As Long
        For i = 1 To Range("B" & Rows.Count).End(xlUp).Row
            MsgBox "*" & Range("B" & i).Value & "*"
        Next
    End Sub
 を実行してもらうと、*　*の中に順次B列の値が入って　メッセージボックスに表示されます。
 　
（HANA） 2014/08/07(木) 08:59

お世話になります。
例題では、sheet2のB列の最大文字数は「2」　→（最大文字数は2～5で一定では、有りません）　　

 　　　　　sheet1のB列も二文字以上だったり　→（検索される文字も5以上でこちらも一定では有りません）

範囲はSheet2の方のデータに合わせて変更してます。

ﾃﾞｰﾀ量を減して色々やってましたが、

　来週またマクロともどもトライしてみます。

（バドファン） 2014/08/08(金) 12:38

 マクロ向きだと思います。
 そちらに焦点を絞ってやってみられるのが良いと思います。
 　
（HANA） 2014/08/08(金) 15:15

 こんな感じかな

 =LOOKUP(1,0/((LEFT(B1,LEN(Sheet2!B$1:B$20))=Sheet2!B$1:B$20)*(Sheet2!B$1:B$20<>"")),Sheet2!$A$1:$A$20)

 =LOOKUP(1,0/(SUBSTITUTE(B1,Sheet2!$B$1:$B$10,"")<>B1),Sheet2!$A$1:$A$10)
（By） 2014/08/08(金) 16:02

お世話になります。
（By）さんの　　=LOOKUP(1,0/((LEFT(B1,LEN(Sheet2!B$1:B$20))=Sheet2!B$1:B$20)*(Sheet2!B$1:B$20<>"")),Sheet2!$A$1:$A$20)で試したらOKでした。2万行でもOKでした。

=LOOKUP(1,0/(SUBSTITUTE(B1,Sheet2!$B$1:$B$10,"")<>B1),Sheet2!$A$1:$A$10)は希望の品名が抽出できなかった。

最初の関数を使用して下記のマクロで約2秒です。（2万行で）
Sub tyuusyutu()
Dim g As Long
g =Range("B" & Rows.Count).End(xlup).Row
Range("A4:A" & g).ClearContents
Range("A4:A" & g).Formula = _
"=LOOKUP(1,0/((LEFT(B1,LEN(Sheet2!B$4:B$500))=Sheet2!B$4:B$500)*(Sheet2!B$4:B$500<>"""")),Sheet2!$A$4:$A$500)"
Range("A4:A" & g).Value = Range("A4:A" & g).Value
End Sub

すばらしい関数です、良く理解できませんがありがとうございました。

（バドファン） 2014/08/11(月) 21:44

コメント返信：

[ 一覧(最新更新順) ]

[[20140803202012]] 『文字の一部で他の文字を検索する』（バドファン） ページの最後に飛ぶ

[[20140803202012]]　『文字の一部で他の文字を検索する』（バドファン）　ページの最後に飛ぶ