すぐマク　YNxv9822 Home | Search | Contents | Gallery | Introduction | Service | Support | What's New!
お客さまIDで検索する受付システムを作るには？
Ｑuestion 28.1　値の操作 Previous Next
受付業務でExcelを使いたいのですが、次の要件を簡単かつ確実にこなすことが、VBAなどで可能でしょうか。

「名前や住所、会社名などが並んだ名簿があります。全てのデータには便宜上のIDが振ってあり、お客様がいらした際に、 そのIDを伺ってそのデータベース上で検索をかけ、日付と現在時刻をワンタッチで挿入します」
要は受付システムなのですが、オートフィルタやフォームを駆使しようとしても、手数が多くなってしまい、Excel初心者が対応 した場合、ミスが出そうなのです。
望ましいのは、画面上に常に入力ボックスが出ていて、IDを入力した後にワンアクションで タイムスタンプが押せる仕様なのですが、私自身が初心者で、苦戦しております。

何かアドバイスがおありでしたら、ご回答を よろしくお願い致します。
Ａnswer　　Excel 97以上 Copyright (C) 2000.5.9 Yoshioh Nagai
発想が楽しいですね。
フリー客が殆んどない受付では、あなたがイメージされたシステムで、十分に機能発揮できそうですね。
実際に使い始めると、新しいお客さまを簡単にデータベースへ追加したい、不要になったお客さまを消したいなどの欲求が出てくるでしょうが、 一度にあれこれ考えるとシステム作りが難しくなって、結局なにも実現できなくなりかねません。
あなたがイメージされた範囲に絞って、初心者でも使いやすいシステム、そして、受付で使うのですから、素早く応対できるシステムを考えてみましょう。

　【データベース】
データベース
「名前や住所、会社名などが並んだ名簿」を左図のとおりレイアウトしてみました。 シート名は「受付」です。

F列へ「日付と現在時刻をワンタッチで挿入」することにしましょう。

　【入力ボックス】
入力ボックス
「画面上に常に出ている入力ボックス」は ユーザーフォームに、左図のレイアウトで作りましょう。


操作方法と流れ

1. 「お客様に伺ったID」をテキストボックスへ入力し、 [Enter]キーを押す

2. お名前、住所、会社名とスタンプ（日付と現在時刻）が表示される

3. [記録]ボタンを押すと、データベースの F列へスタンプが記入される

　【抽出シート】
組み合わせ
左図は中間作業用のシートで、名前は「抽出」です。データベースから、IDが一致する氏名、住所、会社名を抽出して格納します。

あなたの希望である「手間のかかるオートフィルタやフォームを使わず、Excel初心者が対応してもミスを出さない」に沿って、 VLOOKUP（Vルックアップ）関数だけで作りました。
VLOOKUP関数による検索スピードは、とても早いので、受付でお客さまをお待たせする心配は、ほとんど、ありません。 しかし、データベースの データを、IDの昇順に維持しておくことが必要です。従って、お客さまを追加、抹消した場合には、必ずソート（並べ替え）しておきましょう。 抽出シートにセットしてある数式は、次の 4つだけです。

　B2セル　　　=VLOOKUP($A2,受付!$A1:$E6,2,FALSE)　　　氏名を取り出す（案内シートから）

　C2セル　　　=VLOOKUP($A2,受付!$A1:$E6,3,FALSE)　　　住所を　　　〃

　D2セル　　　=VLOOKUP($A2,受付!$A1:$E6,4,FALSE)　　　会社名を　　〃

　E2セル　　　=VLOOKUP($A2,受付!$A1:$E6,5,FALSE)　　　行を　　　　　〃

VLOOKUP関数については、くわしく説明しませんが、下記のページに掲載していますので、必要でしたら見てください。
　・ 指定された値から別表を検索して目的の値を取り出すには？
　・ セルに入力したブック名でそのブックを開くには？
マクロは、ブックの 2カ所に分けて入力します。
1つ目のマクロは、標準モジュールに作成します。 このマクロは、ブックが開かれると同時に自動的に実行されて、入力ボックスを画面に表示します。 
'-----------------------------------------------------------------------------------------
' ブックが開かれたときに自動的に実行されるマクロ
'-----------------------------------------------------------------------------------------
Sub Auto_Open()
    Worksheets("受付").Activate                 '受付シートをアクティブにする
    UserForm1.Show                              'ユーザーフォームを表示する
End Sub
'-----------------------------------------------------------------------------------------
' ＜コメント＞
' このAuto_Openプロシージャーに代えて、ThisWorkbookのコードとして記入してもよい
(注) ユーザーフォームをモードレスで表示すると、いちいちユーザーフォームを閉じなくても、他の操作が可能になります。
 例えば、受付シートに新しいお客さまを追加、削除したり、並べ替えたりできます。この機能は、Excel2000で新設されました。 いずれ、Excel97から 2000にバージョンアップされたら、ぜひ使ってみてください。マクロコードは次のとおりです。 
    UserForm1.Show vbModeless                   'ユーザーフォームをモードレス表示する
2つ目のマクロは、ユーザーフォームのコード画面で作成します。
このマクロの機能は、
1) ユーザーフォームのテキストボックスへ ID が入力されると、対応する氏名、住所、会社名を 受付シートから抽出し、さらに、コンピュータのシステムの日付と時刻を取得して、ユーザーフォームに表示します。
2) ユーザーフォームの「記録」ボタンがクリックされると、日付と時刻を受付シートの対応する行の F列のセルへ記入します。
3) ユーザーフォームの「×」ボタンがクリックされると、ユーザーフォームを閉じます。 
'=========================================================================================
Option Explicit
Dim スタンプ As Variant                         '現在の日付と時刻を記憶
Dim 行 As Long                                  '受付シートの何行目かを記憶
'-----------------------------------------------------------------------------------------
' テキストボックスから別のコントロールにフォーカスを移す直前に発生するイベントで実行
'-----------------------------------------------------------------------------------------
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If TextBox1.Text <> "" Then                 'テキストボックスに何か入力されていれば
        Worksheets("抽出").Range("A2").Value = TextBox1.Text  '抽出シートのA2セルへ
        Label2.Caption = Worksheets("抽出").Range("B2").Value '名前を表示する
        Label3.Caption = Worksheets("抽出").Range("C2").Value '住所を　　〃
        Label7.Caption = Worksheets("抽出").Range("D2").Value '会社名を　〃
        スタンプ = Now                          '現在日付と時刻を取得する
        Label8.Caption = スタンプ               '　　　〃　　　　表示する
    End If
End Sub
'-----------------------------------------------------------------------------------------
' 「記録」ボタンがクリックされたときに実行するマクロ
'-----------------------------------------------------------------------------------------
Private Sub CommandButton1_Click()
    行 = Worksheets("抽出").Range("E2").Value   '抽出シートの何行目かを取得する
    Worksheets("受付").Range(Cells(行, 6), Cells(行, 6)).Value = スタンプ
'                                                日付と時刻を受付シートのF列の対応する行へ
    TextBox1.Text = ""                          'テキストボックスをクリアする
End Sub
'-----------------------------------------------------------------------------------------
' ユーザーフォームの「×」ボタンがクリックされたときに実行するマクロ
'-----------------------------------------------------------------------------------------
Private Sub UserForm_Deactivate()
    Unload Me                                   'ユーザーフォームをメモリから削除する
End Sub
'=========================================================================================
以上で作成したブックを一旦保存して、改めて開いてみましょう。
自動的に入力ボックスが表示されたら、早速キーボードから「ID」を入力して、[Enter]キーを押してみましょう　．．．

※ 参考
回答のマクロは、説明をわかりやすくするために不可欠な機能を、シンプルにコーディングしています。
実務で使うためには、シートの改良とマクロの機能強化が必要になるでしょう。一例をあげておきますので、チャレンジしてみてください。
1) 抽出シートの数式 VLOOKUP で、範囲を '受付!$A1:$E6' と固定しているが、お客さま数の増減に対応可能に
　　　　・ [Macro] - [範囲選択] が参考になるでしょう
2) お客さまを追加、削除したときに、ワンタッチで並べ替えするためのコマンドボタン
3) スタンプを前回、前々回と記録を残すならば、そのコーディング etc.
　　　　・ [Macro] - [コピー] が参考になるでしょう
サンプルブックのダウンロードは ここをクリック 　（YNxv9822_TimeStamp.xls　60KB）
※ 一旦、ブックをハードディスクに保存し、後で改めて開いてから実行してください。
http://www.geocities.jp/happy_ngi/ Home | Contents | Gallery | Introduction | Service | Support | What's New!