エクセルのマクロの質問です。来店客の購入履歴を「来店記録」、会員登録してもらった顧客情報を「顧客名簿」というファイルに記録しています。

現在は相互に関連付けられていないのでデータを有効的に活用できていません。これをマクロによって作業の簡略化、データの有効活用を行いたいと考えています。
まず顧客が購入すると会員番号を「来店記録」書き留めます。この会員番号を「顧客名簿」で検索し、該当する顧客の旧会員番号、会員資格、名前、フリガナを「来店記録」に転記するマクロを作りたいと思います。
データを記録するのはエクセルの知識がほとんどない販売スタッフです。データベースソフトを使えればよいのですが以前導入に失敗しました。現行作業をあまり変えることなく行うのが今回の方針です。
マクロの知識が浅いため、解決方法そのものを教えていただければうれしいです。もしそのまま使える完全なコードややり方を回答していただいた回答者の方には500ポイントを差し上げます。よろしくお願いいたします。
※文字制限のため「来店記録」、「顧客名簿」のサンプルデータ、仕様の詳細はこの下にある「この質問・回答へのコメント」に記載いたします。

回答の条件
  • 1人2回まで
  • 登録:2008/05/15 16:28:36
  • 終了:2008/05/15 23:17:51

ベストアンサー

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692008/05/15 19:56:17

ポイント500pt

コメントでも少し書きましたが、検索方法で顧客名簿の項目を検索しているので

顧客名簿の列はA列から始まっていない場合を考慮して仕様に忠実にマクロを作ってみました。

ただし、来店記録はA列から始まっているとします。

Sub MacroSerch()
    If Selection.Value = "" Then Exit Sub
    Dim kensaku_keyword  As String
    Dim kensaku_retsu As String
    Dim c As Integer
    Dim obj As Range
    Dim i As Integer
    
    With Worksheets("来店記録")
        kensaku_keyword = moji(Selection.Value)
        kensaku_retsu = .Cells(1, Selection.Column).Value
        
        Set obj = Worksheets("顧客名簿").Columns(retu(kensaku_retsu)).Find(kensaku_keyword _
            , , xlValues, xlWhole, xlByColumns, xlNext, True, True)
        If obj Is Nothing Then
            MsgBox "該当する顧客が見つかりません。名前、フリガナ等で検索してみてください"
        Else
            For i = 2 To 6
                .Cells(Selection.Row, i).Value = Worksheets("顧客名簿").Cells(obj.Row, _
                    retu(.Cells(1, i).Value)).Value
            Next i
            .Cells(Selection.Row, 7).Value = Date
        End If
    End With
End Sub

'文字列に*を入れる
Function moji(st As String) As String
    Dim i As Integer
    Dim str As String
    str = "*"
    For i = 1 To Len(st)
        str = str & Trim(Mid(st, i, 1)) & "*"
    Next i
    moji = str
End Function

'項目が顧客名簿のどの列かを返す
Function retu(st As String) As Integer
    Dim c As Integer
    c = 1
    While Worksheets("顧客名簿").Cells(1, c).Value <> st
        c = c + 1
    Wend
    retu = c
End Function
id:icta

>SALINGERさん

いつもありがとうございます。

教えていただいたコードで希望通りの動作を確認しました。

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

新しい質問をhttp://q.hatena.ne.jp/1210860623へ投稿しました。

またご協力いただければ大変うれしいです。

2008/05/15 23:17:32
  • id:icta
    「来店記録」

    店舗 会員番号 旧会員番号 会員資格 名前 フリガナ 来店日 担当 売上
    新宿 D00572 D572 正 春丘 ハルオカ 2008/5/10 長岡 20000
    新宿 D00396 D396 正 服部 ハットリ 2008/5/10 長岡 5000
    原宿 D00645 D645 正 坂下 サカシタ 2008/5/10 佐藤 10000
    原宿 L00239 L239 正 杉本 スギモト 2008/5/10 佐藤 4000
    上野 D00146 D146 仮 落合 オチアイ 2008/5/11 長岡 8000
    上野 C00148 0148 仮 脇永 ワキナガ 2008/5/11 長岡 9000
    上野 C01329 1329 正 川畑 カワハタ 2008/5/11 吉田 15000
    上野 D00638 D638 正 長浜 ナガハマ 2008/5/11 吉田 6000


    「顧客名簿」

    連番 会員番号 旧会員番号 会員資格 名前 フリガナ  
    62 C03824 3824 正 安西 アンザイ
    63 C03825 3825 正 山内 ヤマウチ
    64 C03826 3826 正 藤下 フジシタ
    65 C03827 3827 正 望月 モチヅキ
    66 C03820 3820 仮 安藤 アンドウ
    67 C03839 3839 正 橋本 ハシモト
    68 T03830 3830 仮 吉本 ヨシモト


    ■仕様

    ○概要
    「来店記録」の会員番号列の最終行に会員番号を入力し、マクロを実行すると「顧客名簿」より該当する顧客を探し出し、「来店記録」に旧会員番号、会員資格、名前、フリガナを転記し、来店日に本日の日付を入れる。
    該当する顧客が見つからない場合は「該当する顧客が見つかりません。名前、フリガナ等で検索してみてください」というダイアログを表示する。
    会員番号記載違い、会員番号が分からない場合もあるので「来店記録」の旧会員番号列、名前列、フリガナ列でも同じように、最終行に旧会員番号、名前、フリガナを入力しマクロを実行すると「顧客名簿」から該当する顧客情報を見つけ転記し、来店日に本日の日付を入れる。
    検索には以下の「名前検索()」のルーチンを用いる。

    ○詳細
    ・「来店記録」でマクロを実行すると現在のセルの内容に1文字おきにアスタリスクを挿入し変数に保存。※C03827→*C*0*3*8*2*7* kensaku_keyword = *C*0*3*8*2*7*
    ・現在のセルのタイトル行(1行目)の内容を変数に保存。※kensaku_retsu = 会員番号
    ・「顧客名簿」のタイトル行から会員番号の列を探す。※会員番号は2列目にある
    ・会員番号列から*C*0*3*8*2*7*を探す。
    ・該当する行があれば、旧会員番号(3827)、会員資格(正)、名前(望月)、フリガナ(モチヅキ)をkaiinbangou、kyukaiinbangou、kaiinshikaku、namae、huriganaにそれぞれ代入。
    ・始めの「来店記録」のセルに戻り、上記の内容を出力、来店日に本日の日付を入れる。。
    ・該当する行がない場合はダイアログを表示。
    ・会員番号以外でも検索できるようにする。「来店記録」に顧客のフリガナをフリガナ列に入れマクロを実行すると該当する行を探し出し上記と同じように処理する。


    ○実際の運用
    1)「来店記録」の会員番号列最終行にC03827を入力
    :
    上野 D00638 D638 正 長浜 ナガハマ 2008/5/11 吉田 6000
       C03827

    2)マクロを実行する。
    ▼該当者がいる場合
    :
    上野 D00638 D638 正 長浜 ナガハマ 2008/5/11 吉田 6000
       C03827 3827 正 望月 モチヅキ 2008/5/12

    ▼該当者がいない場合
    ダイアログ「該当する顧客が見つかりません。名前、フリガナ等で検索してみてください」

    3)該当者がいない場合は別のデータで探す。例としてフリガナの場合
    :
    上野 D00638 D638 正 長浜 ナガハマ 2008/5/11 吉田 6000
                   モチヅキ

    4)マクロを実行する。
    ▼該当者がいる場合
    :
    上野 D00638 D638 正 長浜 ナガハマ 2008/5/11 吉田 6000
       C03827 3827 正 望月 モチヅキ 2008/5/12

    ▼該当者がいない場合
    ダイアログ「該当する顧客が見つかりません。名前、フリガナ等で検索してみてください」


    ■名前検索

    抽出したいキーワードがあるセル上でマクロを実行すると全角スペース、半角スペースを取り除き、1文字ごとにワイルドカードの「*」を挿入し、キーワードに該当する行を抽出する。
    例:山本太郎→山*本*太*郎* ※これを*山*本*太*郎*にするにはどうしたらよいでしょうか?


    Sub 名前検索()

    Dim i As Long
    Dim s As String
    Dim myCell As String


    s = ActiveCell.Offset(0, 0).Range("A1").Value
    myCell = ActiveCell.Column

    s = WorksheetFunction.Substitute(WorksheetFunction.Substitute(Trim(s), " ", ""), " ", "")

    For i = 1 To Len(s)
    s = WorksheetFunction.Replace(s, i * 2, 0, "*")
    Next

    Selection.AutoFilter Field:=myCell, Criteria1:=s, Operator:=xlAnd
    ActiveWindow.LargeScroll Down:=-1
  • id:t_shiono
    回答できないので、コメントで。

    VLOOKUPを使えば十分じゃないかなと思います。

    気になったのは、

    そもそも会員番号で引けないのに、フリガナで引ける状態があってよいのか?(入力ミスとかの対策でしょうか?)
    3)の場合でフリガナが同じユーザが存在する場合はどうするのか?
    名前検索は、意図がよく分かりませんでしたが、フリガナと同様の疑問が残ります。

    何かの参考になれば。
  • id:SALINGER
    この検索ルーチンでちょっとだけ気になるのが
    kensaku_retuという変数で顧客名簿の対応する列を更に検索してるのだけど、
    この2つの表ならば転記する会員番号からフリガナまでの列は同じだから検索する必要はないよね。
    2つの表の列が違ってたり変更される可能性があるとすると、
    見つけた値を転記するときにそれぞれがどこに転記するのかをまた検索しなくちゃならなくなりますよね。
  • id:SALINGER
    訂正がありました。
    17行目のElseの後に次のコードを挿入してください。

    If obj.Row = 1 Then
    MsgBox "該当する顧客が見つかりません。名前、フリガナ等で検索してみてください"
    Exit Sub
    End If

    前のコードでは見出しの語句、例えばフリガナで「フリ」などといれるとおかしなことになるので。
  • id:icta
    >VLOOKUPを使えば十分じゃないかなと思います。

    このような回答や疑問がきっとあると思っていました。

    質問時に「データを記録するのはエクセルの知識がほとんどない販売スタッフです。データベースソフトを使えればよいのですが以前導入に失敗しました。現行作業をあまり変えることなく行うのが今回の方針です。」と記載したように、現行の作業をあまり変えないことが原則なのです。
    VLOOKUPでは操作に不慣れなスタッフがコピー&ペーストやドラッグをしたときに不具合が生じる可能性が高いのです。
    しかもファイルを操作するのは1箇所ではなく複数個所になります。
    管理の目が行き届かないのです。

    >会員番号で引けないのに、フリガナで引ける状態があってよいのか?(入力ミスとかの対策でしょうか?

    顧客が来店→購入→記録までの間にはいろいろ事が起きます。
    必ず顧客番号を正確に記録できるとは限らないのです。
    会員証の会員番号が違っていたり、会員証を忘れる顧客もいます。
    いつも決まったルーチンで作業ができるわけではないのです。
    そのためにいろんな検索方法で目的のデータを簡単に取り出せることが必要なのです。

    >3)の場合でフリガナが同じユーザが存在する場合はどうするのか?

    「会員番号で検索して表示されない」かつ「フリガナが同姓同名」の場合の起きる確立はどのくらいでしょうか?
    恐らく同姓同名が1000件に1件ほどなのでそれ以下になるでしょう。
    このようなごくごく稀にしか起き得ないことは無視しても問題になることはありません。

    >名前検索は、意図がよく分かりませんでしたが、フリガナと同様の疑問が残ります。

    上記が起きる確立よりスタッフが顧客名簿に入力時にフリガナ欄に苗字と名前の間にスペースを入れてしまうことの確立の方がはるかに高いです。
    スペースが入っていても対応できるための対策です。

    システム開発者としては以上のような疑問が生じるのは当然だと思いますが、様々な事を想定しなければならないことが重要であることを理解していただければと思います。
    以前、業者に依頼したデータベースはシステム開発側の立場からの設計で使いづらく融通の利かないものでした。
    作業を軽減する目的で導入したものが反って作業を増やすことになり、元に戻した経緯があります。
    システムを実際に利用する人間は変化を嫌います。そうした人間に新しい方法を一から教育するよりも今まで同じ方法で楽になる方が導入が簡単なのです。
    ご理解いただければ幸いです。



    >2つの表の列が違ってたり変更される可能性があるとすると、
    >見つけた値を転記するときにそれぞれがどこに転記するのかをまた検索しなくちゃならなくなりますよね。

    まったくその通りです。
    システム利用者は販売スタッフなのでコロコロ変わります。
    今はシステム設計時点なのでわざと列を同じにしてありますが、何か新しいデータを作らなければならなくなったとき、担当者が変わったときに列を新たに挿入してしまうことも考えられます。
    そのためあえてこのような回りくどい方法を採りました。


  • id:SALINGER
    実感としてはExcelのマクロは作成者以外が実用的に使うのはなかなか難しいと思っています。
    今の例ですと使う人が見出しを変えたり、氏名を名前とか、上に1行追加してタイトルをいれたり
    したらもう意図した動きをしなくなります。
    これはある程度はマクロ側で吸収できますが、限界もあります。
    一番いい方法はシートを保護して、入力するセル以外を変更できなくしてしまうことです。
    シートの保護の仕方は以下。
    http://hamachan.info/excel/si-to.html
  • id:t_shiono
    丁寧にご説明頂きありがとうございます。

    質問とは直接関わりありませんが、

    > システム開発者としては以上のような疑問が生じるのは当然だと思いますが、様々な事を想定しなければならないことが重要であることを理解していただければと思います。

    これは耳が痛い話です。
    使う人が使いやすいものを提供しようとは思いながらも、自分が使いやすいものに引っ張られてしまう傾向はどうしてもありますね。

    今回の件であれば、おおよその話を聞いただけですと、エクセルファイルのセルに追加するのではなく、来店記録登録用のシート(フォームといった方が近いですが)を用意して、そこに入力すると、来店記録シートに1行追加されるというものをまず、提案すると思います。
    ところが、業務自体は変えたくないなどの、見えていなかった要望が理解しきれていないと、一人よがりのシステムになってしまうのですよね。

    今回のマクロの件、使いやすいものができるといいですね。
  • id:icta
    > SALINGERさん
    シートの保護の件ありがとうございました。保護をかけてみようと思います。

    > t_shionoさん

    > 今回の件であれば、おおよその話を聞いただけですと、エクセルファイルのセルに追加するのではなく、来店記録登録用のシート(フォームといった方が近いですが)を用意して、そこに入力すると、来店記録シートに1行追加されるというものをまず、提案すると思います。

    実際の運用は「来店記録シート」という紙に女性スタッフが手書きをしています。
    それをさらにエクセルに取り込む作業をしています。
    ものを書く時すべてPCを利用している私にとっては、手書きは非常に面倒で2重の作業になると思われるのですが、スタッフに言わせると紙の方が「都合がいい」そうです。
    接客中の空いた時間に書き込んでいるため、不便は感じないそうです。仕事以外でPCに触らないスタッフにとっては紙の方が安心でPCは訳の分からない代物なのです。
    アナログな人間には最新鋭の機能は必要ではなく、PCは文字を清書するためのワープロのような存在として考えているようなのです。
    結局のところ、実際に運用するのは彼女たちなので無駄なストレスを与えるよりも、紙とPCを併用した方が導入がスムーズそうです。今回の改善は「新しい作業をできるだけ増やさず、できるだけ今までのやり方で行う」です。
    以前行ったデータベース導入はシステム開発に時間だけ無意味に費やし、できあがったものは作業が煩雑になるものでした。
    複数個所に散らばるスタッフを再教育する手間と労力を考えると、今までのやり方を踏襲する方が効率が悪くても費用対効果では良さそうです。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません