[[20040122084639]]　『該当する文字列データを複数表示させる方法』（すがやっち）　ページの最後に飛ぶ

『該当する文字列データを複数表示させる方法』（すがやっち）

早速ですが質問させていただきます。宜しくお願いいたします。
自社と取引のある業者をＤＢとしてファイルにしています。1行目はＩＮＤＥＸであり、データ自体は2行目からです。A列には業者のコード（業者毎にユニークなもの）、B列には業者名、C列には電話番号、D列には住所を表示しています。データはシート1です。

別のシート（シート2）に業者の検索シートを作成したいと思っています。具体的には、業者名の一部をA1に入力すると、B列に、その入力した文字列を含んでいる業者名を全て表示するといった仕組みにしたいのです（例：千葉興業、東京建物、横浜産業、千葉海上火災、千葉物流といった会社が存在するとする。A1に"千葉"と入力すると、B列に、上から千葉興業、千葉海上火災、千葉物流と表示される）。

さらに可能であればですが、B列に表示された業者名にリンクが自動的につくようにし、その業者名をクリックすると、別表（同じシート2内に作成するとする）にその業者のデータ（コード、業者名、電話番号、住所）を表示させるようにしたいと思います。

宜しくお願いいたします。分かりにくい点は補足したいと思います。

 リストから、キーワードの一部を入力して複数の検索値を指定範囲に抽出したい
 ということですね。
 検索関数については他のログからある程度理解していらっしゃるようなので、
 私の過去に作ったものを少しだけヒントとして紹介します。

 通常検索関数はリストからひとつのレコードしか引き出すことができません。
 そこで、リストのレコードにユニークな値を与えておきます。
 私の場合は「よみがな」を利用しました。
 「よみがな」がリストのC列に入っていた場合、レコードの最後に
 =LEFT(C2,1)&SUMPRODUCT((LEFT($C$2:C2,1)=LEFT(C2,1))*1)
 などと入力しておいてフィルドラッグ。「ア1」「ア2」「カ1」・・・
 という「読み仮名の一文字目が？の何番目」というフィールドを追加します。
 これをとりあえず「新規フィールド」と呼んでおきます。

 検索側
 読み仮名一字から該当するレコードのコードをまず検索します。
 コード検索がB2から始まるとすると下記の式を適当な数B2以下へ貼り付けます。

 =INDEX(リストのコード範囲,MATCH(読み入力セル&ROW()-1,新規フィールド,0))

 読み仮名入力セルには読みの一字のみ入力します。
 「ア」を入力するとB2には「ア1」のコード番号、B3には「ア2」のコード番号を
 求めることができます。コード番号さえ求めることができればあとはVLOOKUP関数で
 他のフィールド（業者名や住所）は求めることができます。

 よかったら応用してみてください。
 (KAMIYA)

（なお）です。
下記マクロで似たようなことができると思います。

 シート2のA1に入れた文字を頭に含むシート１のB行の文字を
検索し、該当行を20番の列まで、シート2に転記します。

Sub test1()

   'XX = Application.InputBox("検索文字を入れてください")
   xx = Worksheets(2).Cells(1, 1)
   Worksheets(1).Activate

  ' For Each C In Worksheets(1).UsedRange
  For Each c In Worksheets(1).Columns(2).Cells

   If xx = "" Then
     Exit Sub

   Else

   If c.Value Like xx & "*" Then
    ri = c.Row

    'MsgBox (ri)
    lastrow = Worksheets(2).Cells(Rows.Count, 1).End(xlUp).Row

   For cno = 1 To 20
   Worksheets(2).Cells(lastrow + 1, cno) = Worksheets(1).Cells(ri, cno)

   Next

   End If

 End If

 Next

End Sub

（KAMIYA）さん、ありがとうございます。
しかし実際に上記の数式を設定しても、確かにコードは列に表示されますが（コードを入力してVLOOKUPで情報を引っ張ればいいかもしれませんが）、肝心の業者名が出てきません。"あ"を入力したらあ行の業者のコードが表示されるのは確かに便利かもしれませんが、その度ごとに一つずつコードを入力して業者を探していたのでは、実情にかなわないのです。

コードはユニークですから、コードを入力して業者のデータを引っ張るのはVLOOKUPで可能であると思いますし、実際にそういった検索シートを作りました。しかし今回の場合はその逆です。例えば業者名から業者のコードやその他の情報を引っ張ると考えると、実際同じ文字列を含んだ業者はたくさんあります。貪欲な考え方で申し訳ないのですが、今回は入力した文字列から複数の業者名をダイレクトに列に表示させたいのです。

加えて希望を申し上げるならば、業者名とコードが同時に表示されるような表が出来れば、あとはユニークなコードを使って、VLOOKUPで個別の情報（電話番号とか住所）を引っ張ってくることができます。

申し上げた内容がわかりにくいかもしれないのですが、要は一つの業者のデータを得るのに、コードだけでは不十分だという事なんです。コード＋業者名が表示されれば、短時間で一つのデータに絞り込む事が可能になると思うのです。

私が何か勘違いをしていると思われるようでしたら、ご指摘いただきたいと思います。分かりにくい点は補足します。宜しくお願い申し上げます。

 返事が遅れました。すみません。ちょっと風邪で伏せっておりました。
 さて、
 =INDEX(リストのコード範囲,MATCH(読み入力セル&ROW()-1,新規フィールド,0))
 では気にいってもらえなかったみたいですね。

 先に書いたとおりヒントのつもりだったので、全部はあえて書いていません。
 少し意地悪でしたね。

 「業者コードがユニークなものであればVLOOKUP関数で情報は引き出せる」
 つまり、コードが表示される右側にもさらにVLOOKUP関数を埋め込んでおけば
 「読み入力」→「コード検索」→「コードから業者情報検索」
 という風にできます。
 さらに、別にコードを引っ張り出さなくても

  =INDEX(リストの業者名範囲,MATCH(読み入力セル&ROW()-1,新規フィールド,0))
 とすれば直接読みからの業者名の一覧ができます。

 ついでなので、もうひとつ別の方法。
 業者名一部入力で該当する業者リストを作成します。
 すがやっちさんの提示したように

 Sheet1の名簿が次のようになっているとします。

       A       B       C      D    E
 1   コード 業者名 電話番号 住所
 2   （以下業者情報）

 検索側のシートは（仮にSheet2とします）次のようになっているとします。
 A2に入力したキーワードで検索し、B2:Enの範囲へ情報を表示します。
       A       B       C       D       E
 1 キーワード コード 業者名 電話番号 住所
 2

 Sheet1!E2へ次の数式を入力し件数分数式をコピペ。
 検索のカナメの数式です。
 =SUMPRODUCT((SUBSTITUTE($B$2:B2,Sheet2!$A$2,"")<>$B$2:B2)*1)

 Sheet2!B2へ下記の数式
  =IF(MAX(Sheet1!$E:$E)<ROW()-1,"",INDEX(Sheet1!A:A,MATCH(ROW()-1,Sheet1!$E:$E,0)))
 これをSheet2!C2:E2へコピペ。
 このコピペでできたB2:E2の数式を下の行へ適当にコピペしてください。
 多少多めで良いです。

 できましたらA2に業者名の一部キーワードを入力してみてください。
 これも先のINDEX関数の応用ですが、数式は二つしか使っていません。
 (KAMIYA)

 数式の差し替えふぇす。
 =SUMPRODUCT((SUBSTITUTE($B$2:B2,Sheet2!$A$2,"")<>$B$2:B2)*1)

 は次でも同じ結果が得られます。
 数式の数が多いとき、再計算の負担を考えると絶対にこっちのほうが良いです。
 =COUNTIF($B$2:B2,"*"&Sheet2!$A$2&"*")

 なぜ上記SUMPRODUCT～でやってたのかは不明。
 多分考えすぎら。
 (KAMIYA)

 おいおい、KAMIやん風邪ひいとんのに無理しなはんなや。
 責任感強いんはおよそ察しがつきますけどナ、風邪には静養が一番でっせ。
 インフルエンザやったらたいそがらずに医者に行くべきやで、ホンマ。

 アカン、エライ熱やがな、誰か代打頼むで！
 　　　代打に不適（弥太郎）

 生還してまいりました。（笑い）
 弥太郎さんお気遣いありがとうございます。
 幸いインフルエンザではなかったようです。
 が、体調良くもないので、ほどほどにしておきます。
 (KAMIYA)
 ↑前回の書き込みもヘンですね・・・。
 風邪ひきの様子がはまっているのでそのままにしておきますか。

私も書き込んで更新したのですが、どうやらバッティングしてしまい、更新に失敗したようです。KAMIYAさん、風邪の具合はいかがでしょうか？

ご多忙のところ大変申し訳ないのですが、また質問させてください。教えていただいたとおり（>業者名一部入力で該当する業者リストを作成します。以下）、数式を設定しても正常な値が得られないのです。おそらく、実際に私が業者のデータを設定しているセルが、教えていただいたものと異なるからだと考えています。

実際のところは、データシート（上記でいうSheet1）では、各業者のデータは3行目から表示しています（コードはC列、つまりC3から。業者名はD列、つまりD3からです）。また、検索シート（上記でいうSheet2）では、実際文字列を入力するセルはF33、コード（の候補）が表示されるセルはA36、業者名（の候補）が表示されるセルはF36です。

セルの設定が以上のようなものだとすると、設定する数式はどのようなものになるでしょうか？ご教授いただきたいと思います。わかりにくい点は補足します。

補足（修正）です。

コード（の候補）が表示されるセルはA36、業者名（の候補）が表示されるセルはF36です。

→コード（の候補）が表示されるセルはA36以下の列（例　10件であればA36:A45）、業者名が表示されるセルはF36以下の列（例　10件であればF36:F45）です。

何度も申し訳ありません･･･。

 こんにちは。KAMIYAさんではありませんが、別案です。意味を取り違えていたら無視してちょ。
 Sheet1のB2:E12にCODE、業者名、電話番号、住所の順にデータが有るとします。（2行目は項目名）
作業列を設けます。B列に列挿入し、B3に
=IF(Sheet2!$F$33="","",IF(ISNUMBER(MATCH("*"&Sheet2!$F$33&"*",D3:D$12,FALSE)),IF(MATCH("*"&Sheet2!$F$33&"*",D3:D$12,FALSE)=1,ROW(),""),""))
 とし、B12まで下方コピー。
 Sheet2のF36に
=IF(SUM(Sheet1!$B$3:$B$12)>0,IF(ISERROR(INDIRECT("Sheet1!"&ADDRESS(SMALL(Sheet1!$B$3:$B$12,ROW()-35),5))),"",INDIRECT("Sheet1!"&ADDRESS(SMALL(Sheet1!$B$3:$B$12,ROW()-35),4))),"")
 （同一の業者名があると困りますが）A36に
=IF(F36="","",INDEX(Sheet1!$C$3:$C$12,MATCH(F36,Sheet1!$D$3:$D$12,FALSE)))
 でそれぞれ45行目まで下方コピー。
 A34に
=IF(SUMPRODUCT(($G$36:$G$45<>"")*ROW(G36:G45))=0,"",IF(COUNTA($G$36:$G$45)>1,"チェック箇所多し！",INDIRECT(ADDRESS(SUMPRODUCT(($G$36:$G$45<>"")*ROW($G$36:$G$45)),1))))
 B34に 
=IF(OR($A$34="チェック箇所多し！",$A$34=""),"",VLOOKUP($A$34,Sheet1!$C$3:$F$12,COLUMN(),FALSE))
 この式をC34:D34まで右方向にコピー。
 F33に『千葉』と入力。検索表示された一覧に対し、選択する行のG列に何か入力します（リンクの代替）。
 A34:D34に、そのCODE、業者名、電話番号、住所がそれぞれ表示されます。
 レイアウトが、異なる場合は、ADDRESS関数の中を変更する必要があります。
少し、手直ししました。　お試しあれ。。。(sin)

 Sheet1
       A       B       C      D       E     F    G
 1
 2                   コード 業者名 電話番号 住所
 3                  （以下業者情報）

 Sheet2
       A       B    C   D   E    F
 32                            キーワード
 33                            （一部入力）
 34
 35  コード                     業者名
 36 （コード候補）              （業者名候補）

 こんな感じなんでしょうか。
 基本的には対象となるセルのアドレスをうまく調整していただければ
 良いのですが、挑戦してみましたか？

 Sheet1!G3へ
 =COUNTIF($D$3:D3,"*"&Sheet2!$F$33&"*")
 この式をデータの全レコードに対してコピー
 この式はSheet2F33へ入力された文字列を含むレコードの通し番号を作製します。

 Sheet2!A36へ
 =IF(MAX(Sheet1!$G:$G)<ROW()-35,"",INDEX(Sheet1!C:C,MATCH(ROW()-35,Sheet1!$G:$G,0)))
 この式をA37以下のセルへ適当な数コピー
 この式はSheet1!G3:Gnの通し番号から順番にコードを抜き出してきます。
 A36なら、Sheet1!G3:Gnの範囲で最初に「1」になったレコードの「コード」項目を参照します。
 A37ならSheet1!G3:Gnの範囲で同じく「2」の「コード」項目を参照します。

 Sheet2!F36へ
 =IF(MAX(Sheet1!$G:$G)<ROW()-35,"",INDEX(Sheet1!D:D,MATCH(ROW()-35,Sheet1!$G:$G,0)))
 この式をF37以下のセルへ適当な数コピー
 原理は前の式と同じです。業者名を参照するのでINDEX関数でSheet1!D:Dを参照するのが
 違うだけです。

 「ROW()-35」なんかは何を計算しているのか不思議かもしれませんが、
 「行番号から35を引く」という意味です。
 36行目に入力した数式はSheet1!G3:Gnの「1」を
 37行目に入力した数式はSheet1!G3:Gnの「2」を参照してきてほしいので
 このような書き方をしています。
 手入力でこの部分を1，2，3と入力しても良いのですが、
 行数分数式を入力する羽目になるので、
 このように行番号を利用する人が多いみたいです。
 この部分を「ROW(A1)」とする人もいます。
 (KAMIYA)

お返事遅れました。申し訳ありませんでした。
とても役に立ちました。候補を表示させる関数はエクセルではかなりすごいことですよね？
これからもいろいろな局面で、関数を使えるようにしていきたいと思います。

本当にありがとうございました。

以前の質問から、かなり間が開きましたが、(KAMIYA)さんの最後の解説について追加で
質問があります。

例えば、データ元のSheet1に同じコードと業者名の組み合わせが複数行あった場合、
重複データを除いて(つまり一つのデータのみ)データを表示させるような方法はある
のでしょうか？
※尚、コードは違うが業者名は同じ、もしくは業者名は違うがコードは同じといった
ようなデータはないと考えます。

もし可能であれば、上記の条件をSheet2のA36やF36の数式に組み合わせる事が出来れば
幸いです。
お手数ですが、宜しくお願い申し上げます。

 遅くなりました。なにぶんにも前のことなので、（自分で書いているこ
とを含めて）思い出すのに時間がかかりました。(笑）
個人的な意見としてはデータもとの方を重複の無いように管理しておく
のが要件であるとは思いますが、すがやっちさんの事情もあるでしょう
から、ご希望に沿った方向で回答したいと思います。
　
検索を行うSheet2の数式は変えないほうがよいと思います。
数式設計が難しくなりそうなこともありますが、
＜　この部分、よく考えたら変なこと書いてたので自主削除　＞
＜　不可能ではないような感じですが　　　　　　　　　　　＞
＜　いろいろ考えましたが、うまく数式作れませんでした　　＞
　
さて、私の結論。
Sheet2のA36以下の数式とF36以下の数式はG列の値が加算された行だけ
検索するようなつくりになっています。また、G列の値が加算されるのは
Sheet1のF33に入力された文字列がSheet2の業者名に含まれていた場合
です。
ということは、
検索のキーとしているSheet1のG3からの数式を重複値の場合は加算
しないでスキップするような数式構造にすれば解決すると見ました。
Sheet2！G3へ下記を入力。

=IF(SUMPRODUCT(($C$3:C3&$D$3:D3=C3&D3)*1)*(COUNTIF(D3,"*"&Sheet2!$F$33&"*"))=1,G2+1,G2)

 この数式をG列のデータ範囲へコピーしてください。
先の数式とは様相がだいぶ変わりましたが、初出のデータのときのみG列
の数値が加算されます。
なお、
数式の構造上、G2は空白にしておいてください。
何か見出しを表示したい場合には、セルの書式設定、表示形式のユーザー定義で
"見出し"などと設定して「０」を入力する必要があります。
(KAMIYA)

大変お忙しいところ、恐縮です。ありがとうございます。

数式の構造上、G2は空白にしておいてください。

ということですが、どのようにかその問題をクリアすることはできないでしょうか？
と、申しますのは、今回はIndexを1行目に設定し、実際のデータは2行目から始まって
います。ですので、当然G2は2行目ですから、データの開始行となるわけです。

Sumproduct関数は私にはあまりなじみがないですが、非常に多目的に使用できると
お見受けします。
いろいろ自分でも調べてみようと思います。

 どうしても文字列が入る場合はSUM関数を経由すれば良いですけどね。
行位置もちょっと調整。
=IF(SUMPRODUCT(($C$2:C2&$D$2:D2=C2&D2)*1)*(COUNTIF(D2,"*"&Sheet2!$F$33&"*"))=1,SUM(G1,1),SUM(G1))
(KAMIYA)

お返事が遅れまして申し訳ありませんでした。
いろいろとありがとうございました。

コメント返信：

[ 一覧(最新更新順) ]

[[20040122084639]] 『該当する文字列データを複数表示させる方法』（すがやっち） ページの最後に飛ぶ

[[20040122084639]]　『該当する文字列データを複数表示させる方法』（すがやっち）　ページの最後に飛ぶ