エクセル 顧客マスタを利用してお中元リストを作りたいのですが。

質問

エクセル　顧客マスタを利用してお中元リストを作りたいのですが。
顧客名、住所、電話番号などの情報が入った顧客マスタをエクセルに落として、それを利用して
会社のお中元リストを作ろうと思います。

「お中元リスト」シートの顧客名欄にお中元を出したい顧客名を入力すると、「顧客マスタ」シート
から同じ顧客名を探し出し、お中元リストの住所欄、電話番号欄が自動で埋まる仕組みのものを作り
たかったのですが上手くいきません。

VLOOKUP関数を使ってお中元リストを作ってみたのですが、実際に顧客名を入力しようとすると
正式な顧客名がわからないことが多いことに気が付きました。
その顧客が株式会社なのか有限会社なのか、先（株）か後（株）か、「○○産業」だったか「○○工業」
だったか等で、正式な顧客名（顧客マスタに登録されている顧客名）が分らない為に、結局顧客マスタ
を見ながら顧客名を入力しなければならない有様です。

「入力規則」のリストを使おうと考えましたが、顧客マスタの件数が多く（2,000件以上）、リストから
選ぶのも大変です。

できればインターネットの検索みたいに入力途中に候補を表示してくれるような仕掛けを作れれば素
晴らしいのですが。　何かいい方法をご存知でしたら教えて下さい。

回答No.1

keithin · ベストアンサー

普通に工夫できる範囲では。
A1に「○○工業」まで打ち込んでEnterし，関数の方は
=IF(COUNTIF(マスタ!A:A,"*"&A1&"*"),VLOOKUP("*"&A1&"*",マスタ!A:E,3,FALSE),"")
としておくと，先(株)や後(株)，有限会社などまでは探して拾ってくれます。

無理矢理な事をすれば，入力欄の上に2000行ほど行を隠しておいてマスタの会社名を生データでコピーしていれておき，オートコンプリートで候補を出させるような事もやればできます。それでも，前方一致しか効かないですが。
マクロを使ってみるにも,Excelではセルに文字を入力している「最中」に割り込んでマクロを動かせません。セルにデータを記入するんじゃなく，マクロでで入力画面から完全に自作するような事をするれば多少は何とかなります。そこまでマニアックなことをする程では無いと思いますが。

お礼

教えていただいた方法で手早くリストを作成することができました。
大変有難うございました。

回答No.6

tom04

こんにちは！
No.3さんと重複してしまいますが、表を作ってしまったので参加させてもらいます。

↓の画像でSheet2に「顧客データ」があり、Sheet1のA2セルに検索したい文字を入力すると
その文字が含まれる顧客をA3セルにリスト表示させる方法はどうでしょうか？

Sheet2の作業列D2セルに
=IF(Sheet1!$A$2="","",IF(ISNUMBER(FIND(Sheet1!$A$2,$A$2:$A$100)),ROW(Sheet1!A1),""))
E2セルに
=IF(COUNT($D$2:$D$100)<ROW(Sheet1!A1),"",INDEX($A$2:$A$10,SMALL($D$2:$D$100,ROW(Sheet1!A1))))
という数式を入れ、D2・E2セルを範囲指定しE2セルのフィルハンドルでダブルクリック、又はオートフィルで下へコピーします。
これでSheet1のA2セルに文字を入力するとその文字が含まれる顧客がE列に表示されます。
（数式は100行目まで対応できるようにしています。）

このE列のE2～適当な行まで範囲指定　→　名前ボックスに直接「候補」と入力します。
この名前はどんな名前でも構いません。
（当方使用のExcel2003の場合は　メニュー　→　挿入　→　名前　→　定義　から名前定義してもOK）

そしてSheet1のA3セルをアクティブにし、
メニュー　→　データ　→　入力規則　→　リストから
元の値の欄に
=候補
としています。
これでA3セルはA2セルの文字が含まれるものがリスト表示されますので、
B2セルは
=A3
として住所・電話番号等はVLOOKUP関数で対応できると思います。

以上、長々と書きましたが
参考程度で目を通してみてください。m(__)m

お礼

今回は手早さを優先して別の方法でリストを作成致しましたが、
もっと元データの件数が多ければ次はこの方法でリストを作ろ
うと思います。
本当に有難うございました。

回答No.5

layy

参考意見で。

要件をもう少し工夫した方が良いでしょう。

あいまい検索→印刷、あいまい検索結果から次の印刷、また次の印刷、出し終えたら検索となるように株なら株だけ絞り込みした中でできるだけ繰り返し印刷しないと１件ずつ検索は手間です。それと、１度出したらマーク付けしないといけませんね。

もう１つシートを用意しあいまい検索結果を表示するところからやると良いのでは。

また、
こういうのは、名前より電話で検索のがよりヒットすると思います。時間があればカナ名も情報に入れたりします。

お礼

今後リスト作成の際には留意したいと思います。
有難うございました。

回答No.4

sumer45

まず顧客には必ず顧客コードというものを作ってください
これは1から始まる番号です（セルの表記は001,002 とかいうようになるように工夫してください）

で、何回か使っていれば顧客コードは頭に入ると思います　
顧客コードでVlookup指定をします

似たような名前がある場合　部分一致でセル範囲の検索をかけ　そこから
顧客コードを取得します

私はこの作業を　VBAでフォームを作ってやりますが
考え方はそのまま流用できるはずです

お礼

今回は別の方法でリストを作成致しました。
顧客コードで検索するということでしょうか。何れ試してみたいと思います。
有難うございました。

回答No.3

web2525

社名の一部を入力し該当する候補の一覧を入力規制のリスト表示する方法はいかがでしょうか

添付例解説
A1セルが社名の入力欄：社名の一部を入力
B2セルがリスト入力のセルになります
D列がリスト一覧
E列が顧客一覧
F列は作業列です

作業列：F列に
=IF(ISERR(FIND($A$1,E1)),"",ROW())
顧客データー分下方向ドラッグ

D列に
=IF(ROW()>COUNTIF(E:E,"*"&$A$1&"*"),"",INDEX(E:E,SMALL(F:F,ROW()),0))
適当な範囲に下方向ドラッグ

B1のリスト範囲をD:DまたはD列の適当な範囲を選択

後はB1に入力された社名を元にVLOOKUPで必要なデーターを取得してください

お礼

今回は別の方法でリストを作成致しました。
また別の表を作る時にこの方法も応用してみたいと思います。
本当に有難うございました。

回答No.2

jcctaira

VBAで関数を作ってみました。
【使用方法】
=名前検索(検索名,検索範囲)
検索名はオートフィルターのオプションとほぼ同じです。
　* 任意の数の文字
　? 任意の 1 文字
結果　複数件の場合…nn 件あります
　　　　０件の場合　　…該当なし
　　　　１件の場合　　…該当した会社名

【例】
=名前検索("*山*",顧客マスタ!A2:A2001)
=名前検索(B1,顧客マスタ!A2:A2001)

【VBA】
Function 名前検索(検索名, 検索範囲) As String
　Dim セル As Range
　Dim 件数 As Integer
　For Each セル In 検索範囲
　　　If Replace(Replace(セル, " ", ""), "　", "") Like 検索名 Then
　　　　　件数 = 件数 + 1
　　　　　名前検索 = セル
　　　End If
　Next
　If 件数 = 0 Then 名前検索 = "該当なし"
　If 件数 > 1 Then 名前検索 = 件数 & " 件あります"
End Function

注）該当した会社名からVLOOKUP関数で住所等を表示するようにして下さい。

お礼

VBAの心得が有りませんでしたので今回は別の方法でリストを作成致しました。
いずれVBAの勉強をしていきたいと思います。
本当に有難うございました。