エクセル2010、FINDを使った関数について

質問

エクセル2010で、一覧から検索後、あいまい検索で抽出する関数があれば教えて下さい。

例えば
シート2
A列
A2＝ABCDE 100/50R10
A3＝FGH 100/55R16
A4＝FGH 100/50ZR10
A5＝ABCDE 100/40ZR10

とデータがあるとして、
シート1の
AB列
AB3＝100/50R10

と入っていれば、

AE列
AE3＝ABCDE 100/50R10
AE4＝FGH 100/50ZR10

と表示することは可能でしょうか？

検索ワードが
「100/50R10」

で100/50R10と入っていたり、100/50ZR10と入っているものがあり、
両方出るようにしたいです。

分かる方、教えて下さい。

回答No.5

Nouble

横合いから済みません
計算負荷を気にされている
其のよう
なので
やや軽減案を

ＡＥ4＝Offset('マスタ一覧'!$A$1,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10001+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A1))-1,0,1,1)

※注:データ量１万件以上は
　　改変前の仕様より、使用不可
　　但し、式中の
　　10000と10001を、
　　各々増やせば、対応可能

イメージで言えば
Range("A:A").Cells(2,0)
と
Range("A1").Offset(2 - 1,0)
此の違い
で、しょう
か？

INDEXは
主メモリー容量越え
此を、引き起こし易い
ので

可能なら
作用を回避した方がいい
かと

HDDにメモリーを
取りに行かざるを得ない状態
此は
今尚、Pc鈍重化
此の、主たる原因の１つです
し

また、
データ処理は、
一般論として
アルゴリズム毎で定まる
一定の関数の元
取り扱い量に比例して
鈍化します
から

あと、
恥ずかしながら
&""
此の意味が、解りませんでした
が、無くても此なら動く
かと

ただ、手元にエクセルがないもので
動かなかったら済みません

補足

ありがとうございます。
＝Offset('マスタ一覧'!$A$1,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10001+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A1))-1,0,1,1)

試しましたが値の更新と出てうまく、表示されませんでした。。

回答No.4

MackyNo1

>10行検索結果が出て、10行表示させたい場合は
ＡＥ4＝INDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A1)))&""
AE5は・・・・
であっていますか？

数式を入力しているAB3セルのシートと別のシート（マスター一覧シート）から検索する場合は、基本的に記載されている数式（オートフィルコピー）でOKです（ROW関数の引数にはシート名は不要ですが）。

ただし、上記の数式は計算負荷の高い配列数式を使用していますので、必要以上にオートフィルコピーしない（数式セルを増やさない）ことと、「マスタ一覧'!$A$2:$A$1000」のデータ範囲を少し余裕を見た必要最小限の範囲（現在データ数が200行程度なら、データの追加分を考慮してA2:A300程度の範囲にする）に設定することがお勧めします。

回答No.3

MackyNo1

＞検索条件のセルはAB列（AB3）なのですが、もし検索条件の部分がセル参照する場合、
"100*50*10"を"＄AB3"で良いのでしょうか？

AB3セルに「100＊50＊10」と入力して$AB$3のように行も絶対参照にしてください。

＞ROW(A1)))&""ですが、今いちROWの意味が分かってないのですが、

ROW(A1)の部分は数字の1,2,3・・・のような連続数字を取得するためでセル番地とは関係ありません（そのセルに入力する場合もROW(A1）またはROW(1:1)のようにします）。

数式については下方向にオートフィルするので、この部分が1,2,3・・・のように変化していくことになります。

ちなみに横方向にオートフィルして連続番号を取得する場合は、COLUMN(A1)またはCOLUMN(A:A)のような数式にします。

数式の最後の「&""」の意味は、該当データがない場合にはA列の10000行以降の空白セルを参照することになりますが、この時「0」と表示されるのを避け、空白文字列を返すようにするためです。

補足

なるほど。ありがとうございます。

10行検索結果が出て、10行表示させたい場合は

ＡＥ4＝INDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A1)))&""

AE5はINDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A2)))&""

AE6はINDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A3)))&""

AE7はINDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A4)))&""

AE8はINDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A5)))&""

AE9はINDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A6)))&""

・・・・

であっていますか？

回答No.2

MackyNo1

>数字が100 50 10 とはいっているデータをすべて表示することはできるのでしょうか？

この条件なら以下のような式になります。

=INDEX(A:A,SMALL(INDEX(ISERR(SEARCH("100*50*10",$A$2:$A$1000))*10000+ROW($A$2:$A$1000),),ROW(A1)))&""

ちなみに検索条件の部分はセル参照することもできます。

お礼

何度もすいません。
検索結果をAE列に設定したいのですが、
AE3=INDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A1)))&""

としたのですが、ROW(A1)))&""ですが、今いちROWの意味が分かってないのですが、
AE4はINDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A2)))&""

AE5はINDEX('マスタ一覧'!A:A,SMALL(INDEX(ISERR(SEARCH($AB$3,'マスタ一覧'!$A$2:$A$1000))*10000+ROW('マスタ一覧'!$A$2:$A$1000),),ROW(A3)))&""

としていけば良いのでしょうか？

補足

ありがとうございます！！

検索条件のセルはAB列（AB3）なのですが、もし検索条件の部分がセル参照する場合、
"100*50*10"を"＄AB3"で良いのでしょうか？

回答No.1

MackyNo1

>検索ワードが「100/50R10」で100/50R10と入っていたり、100/50ZR10と入っているものがあり、 両方出るようにしたいです。

通常なら、2つのデータは部分一致もしていない異なるデータだと思うのですが・・・
例示された抽出基準の規則性を文章で説明するようにしてください。

たとえば、英文字、スペース、数字/数字、英文字、数字のような文字列があり、抽出条件は「数字/数字」の部分が一致し、その次の英文字部分に検索文字の英文字が含まれ、最後の数字が一致するデータを抽出対象としたい、のように抽出基準を明確にしてください（各パートの文字列の長さなどの条件があれば、それも提示してください）。

たとえば、単純に検索ワードを含むデータをすべて抽出するにはかなり複雑な数式が必要ですが、さらにご希望の条件（現時点では基準が不明ですが）を付加すると、関数で表示させるのはあまり実用的な方法ではないと思います。
このような複雑な抽出条件の場合は、数式を使ったフィルタオプションの設定を利用することをお勧めします。
抽出条件が明確になれば、具体的な数式を提示することができると思います。

補足

ご回答ありがとうございます。

この数字がサイズなのですが、検索ワードが100/50R10であれば、

数字が100 50 10 とはいっているデータをすべて表示することはできるのでしょうか？