EXCELの 編集 検索キーを使わず 検索したい

質問

EXCELの 編集　検索キーを使わず　シート上で検索文字を指定して
検索された　全ての　セルを表示したい。　
第１表 CODEー名前表

CODEを探す
A列 B列 C列 D列
1行 CODE 名前 CODE
2行 1001 あいうえお 1001
3行 1002 ああいうえ 1002
4行 1003 おあいうえ 1003
5行 1004 えおいうえ 1004
6行 1005 うえおあい 1005
7行 1006 うえああい 1006

第１表 CODEー名前表　から”あ”の含まれる全ての　名前　CODEを 第２表みたいに　作りたいのです

第２表
CODEを探す
A列 B列 C列 D列
1行 検索する　名前 ＊あ＊
2行
3行 検索された　名前 検索された　CODE
4行 あいうえお 1001
5行 ああいうえ 1002
6行 おあいうえ 1003
7行 うえおあい 1005
8行 うえああい 1006

マクロを使わない方法で　お願いします。
１：検索された　文字の足し算
２：文字の足し算をするごとに　１行増やす
３：改列
４：SUMPRODUCTで　文字の足し算は出来ますか？

回答No.1

Wendy02 · ベストアンサー

こんにちは。

>１：検索された　文字の足し算
意味が分からないです。C言語でもなければ、文字の加算はできないと思います。

>３：改列
改行は分かるけれども、改列って、そもそもなんですか？

検索された　名前 検索された　CODE
というだけなら、
B1 が、検索する文字（ワイルドカードの全角は不可　＊->*）

A4：
=IF(B4="","",VLOOKUP(B4,Sheet1!$A$1:$C$20,2))

B4：
=IF(ROW(A1)>COUNTIF(Sheet1!$B$2:$B$20,$B$1),"",INDEX(Sheet1!$A$1:$B$20,SMALL(INDEX(--SUBSTITUTE(COUNTIF(INDIRECT("Sheet1!B"&ROW($A$2:$A$20)),$B$1)*ROW($A$2:$A$20),"0",10^5),,),ROW(A1)),1))

後は、下にドラッグコピーすればよいと思います。
この種類のものは、多くは、フィルタオプションの処理が最適です。
しかし、入れるたびに変化させるためには、イベント・ドリブン型マクロが必要です。

お礼

>１：検索された　文字の足し算
意味が分からないです。C言語でもなければ、文字の加算はできないと思います。
第２表　で　名前 ＊あ＊ で検索をかけ　「 あいうえお 1001」を求めたいので第１表のB列 C列が文字の加算されたら　便利と思います。　

>３：改列
改行は分かるけれども、改列って、そもそもなんですか？
「 あいうえお 1001」が　「 あいうえお」と「 1001」で別の列に出てきたら　便利です。

補足

第１表の　飛び飛びの行のCODEを探したいのです

第１表 CODEー名前表

CODEを探す
A列 B列 C列
1行 CODE 名前 CODE
2行 1001 あいうえお 1001

13行 1002 ああいうえ 1002

104行 1003 おあいうえ 1003

1005行 1004 えおいうえ 1004

2006行 1005 うえおあい 1005

3007行 1006 うえああい 1006

第２表
CODEを探す
A列 B列 C列
1行 検索する　名前 ＊あ＊
2行
3行 検索された　名前 検索された　CODE
4行 あいうえお 1001
5行 ああいうえ 1002
6行 おあいうえ 1003
7行 うえおあい 1005
8行 うえああい 1006

回答No.3

Wendy02

こんばんは。#1 です。

>第１表の　飛び飛びの行のCODEを探したいのです

それは、範囲を広げればよいだけです。

>3007行 1006 うえああい 1006
ただ、かなりワークシートが重くなることは覚悟してください。

>「 あいうえお 1001」を求めたいので第１表のB列 C列が文字の加算されたら　便利と思います。　

それ自体は、VLOOKUP の範囲で、列数を3にすれば、その隣のセルのものも出てきます。

C列：
=IF(B4="","",VLOOKUP(B4,Sheet1!$A$1:$C$20,3))
<範囲は適当に換えてください。>

補足のようすからすると、まだ、試していないようにも思われますが、ともかく、ご自身で考えながら試してみてください。

ただ、あまり複雑な要望をして、現実にできたとしても配列関数のメモリの限界がありますので、手が付けられなくなる可能性があります。Excel 2002以上は、配列のデータ演算数の限界はなくなったものの、やはり、5～6,000程度が限界のようです。それ以上は、データベース関数やマクロなどに頼らないと思ったようには行きません。

お礼

難しくて分かりません。済みません。

回答No.2

noname#204879

Sheet1　　　　　　　　　　　　 Sheet2
　　A　　　　B　　　　C　　　　　　　　 A　　　　　　　 B
1　CODE　名前　　　　TEMP　　　1　検索する名前　　あ
2　1001　あいうえお　　 2　　　2
3　1002　ああいうえ　　 3　　　3　検索された名前　検索されたCODE
4　1003　おあいうえ　　 4　　　4　あいうえお　　　1001
5　1004　えおいうえ　　　　　　5　ああいうえ　　　1002
6　1005　うえおあい　　 6　　　6　おあいうえ　　　1003
7　1006　うえああい　　 7　　　7　うえおあい　　　1005
8　　　　　　　　　　　　　　　8　うえああい　　　1006
　　　　　　　　　　　　　　　 9

Sheet1!C2: =IF(ISERROR(FIND(Sheet2!B$1,B2)),"",ROW())
　　　 此れを下方にズズーッと複写

Sheet2!A4: =INDEX(Sheet1!$A:$B,SMALL(Sheet1!$C:$C,ROW(A1)),3-COLUMN(A1))
　　　 このセルに次の条件付き書式を設定
　　　　　　数式が　　 → =ISERROR(A4)
　　　　　　フォント色 → 白
　　　 このセルを右隣のセル(B4)に複写
範囲 Sheet!A4:B4 を下方にズズーッと複写

お礼

難しくて分かりません。済みません。