顧客管理ソフトは高価だし、年賀状ソフトじゃ用が足りないからエクセルで住所録を作ろうとして調べていたら、このページにたどり着いたのですね。たぶん。
エクセルで住所録を作るならば、絶対に覚えておかなければならないポイントがあります。
それは名前・住所など、ズバリ「項目」です。
エクセルで作った住所録では、新規登録したり、検索したり、修正したり、並び替えたりと、日々このように使って行くと思います。
その為に検索・修正などがやりやすいような項目を準備しなくてはいけませんので、実際にエクセル住所録で取り入れていることをまとめてみました。
1人1行・1項目1セルで住所録を作る
エクセルに入力した住所録、同じ作るなら簡単便利に素早く検索・修正したいですよね。
言い換えれば、住所録をデータベースとして活用させれば、本当にたった5秒で検索できるようになります。
その為には、1人分の名前・住所・電話番号などのデータは必ず同じ行に入力し、1人1行を必ず実行してください。
そして1項目に1セル（1列）を使い、見栄えなどを優先して結合セルなんて絶対にやってはいけません。
結合セルがあると検索の邪魔になるし、並べ替えもできなくなります。
もちろんデータの修正の時も結合セルを一旦解除してやらなくてはいけないので手間が増えるだけです。
最初に作成した時のエクセル住所録は15列でしたが、検索項目が増えたりスタッフの要望を受け付けているうちに、備考欄やこまごました項目が追加して、ついに30列まで増えてしまいました。
私の場合、同じ人のデータならば同じ行にAA列から始まりBD列まで使うようになりました。
検索・修正の為に1人1行・1項目1セルで結合しない。見栄えなんて気にしない。
エクセル住所録で作る項目
住所録を作成する時に必要な項目は様々ありますが、今使っているエクセル住所録の項目は抜粋ですが、下記の画像ように「名前」「住所」「電話番号」を含め複数あります。
もし、このエクセル住所録から宛名印刷をする時に、「連名」「敬称」が必要ならば項目を用意する必要がありますよね。
また、苗字と名前を分けておかないと連名も使えないので、更に項目を用意しなくてはいけません。
年賀状ソフト「筆まめ」などでは、エクセルのデータを読み込む機能があり、連名を必要としなければ、敬称も自動付与されるので用意しなくてもいい場合があります。
エクセル住所録を作る時の各項目を全て手入力する必要はありません。
項目によっては、エクセル関数を使って計算式によって自動入力させることも可能ですし、エクセルVBAでプログラムを使って入力する方が、作業時間を短縮できますよ。
各項目の詳細については「住所録入力フォーム」ページと重複するのでここでは省略します。
そして出来上がったエクセル住所録のデータベースを使って検索・修正も簡単にあっという間にできるようにオリジナル入力フォームを使って行えば作業時間短縮になります。
住所録の司令塔・メニュー画面を作る
エクセル住所録を素早く正確に入力・検索・修正を行うのに必要なメニュー画面をユーザーフォームで作ります。
ユーザーフォームについては専用ページを作ってありますので、詳細は下記をご覧くださいませ
このメニュー画面は、エクセルファイルを開くと自動的に今日の日付を読み取って表示され、機能としては、それぞれのボタン登録・修正、レンタル、設定、保存終了を押すことで別のユーザーフォームを表示させるだけ、司令塔なのです。
例えば、登録済みの人を検索するならば、登録・修正ボタンを押すと画面が切り替わります。
切り替わる時間わずか1秒、右上の「顧客番号検索」枠の苗字のフリガナを入力してフリガナ検索ボタンを押すと、左上の空欄の顧客番号が表示され顧客番号確定ボタンを押すと、登録済みの名前以下のデータが数字の「0」の代わりに表示されます。
5秒もあれば充分検索可能です
この登録・修正フォームの右下にMENUボタンがあるので、ここを押せばメインメニュー画面に戻るようになっています。
メインメニューを作る準備
先ほどエクセルファイルを開いたら自動的に日付などを読み取ってメインメニューが表示すると書きましたが、読み取る為には日付などを準備しておかなければいけません。
上の図は実際に使っているエクセル住所録の抜粋で、メインメニューの「本日の日付」「本年度期間開始日」「シニア年齢」はそれぞれ、Ｐ2・Ｓ2・Ｔ2の各セルの値を読み取ってユーザーフォームに表示させています。
誕生日・更新状況・年齢をエクセル関数で設定する為で、今日の日付や更新基準日などが無いと計算できないので準備しましょう。
セルP2が「2019/9/4」と表示していますが、エクセル関数TODAYで現在日時を自動表示させて、セルQ2で「年」を取り出して表示、セルR2で月を取り出している関数はそれぞれ下記の通りです。
=TODAY()
=YEAR(P2)
=MONTH(P2)
またセルS2は更新基準日、セルT2はシニア年齢基準を入れて更新状況、年齢を比較できるようにしています。
2か所の変更ボタンの設定
有効期限の基準日となる「本年度期間開始日」と、「シニア年齢」となる基準年齢を変更する時に使うボタン（CommandButton）です。
このそれぞれの変更ボタンを押すことにより基準日や年齢が変更され、住所録全てのデータが差し変わるようになっていて、それぞれVBAコードが書いてあります。
Private Sub CommandButton4_Click()
Range(“S2”).Value = UserForm4.TextBox1.Value
MsgBox “本年度期間開始日が変更されました”
End Sub
- コマンドボタン4が押されたら
- セルＳ2の値はユーザーフォーム4のテキストボックス1の値とする
- 「本年度期間開始日が変更されました」とメッセージを表示
- マクロ終了
次にシニア年齢変更ボタン。
Private Sub CommandButton5_Click()
Range(“T2”).Value = UserForm4.TextBox2.Value
MsgBox “シニア年齢が変更されました”
End Sub
- コマンドボタン5が押されたら
- セルT2の値はユーザーフォーム4のテキストボックス2の値とする
- 「シニア年齢が変更されました」とメッセージを表示
- マクロ終了
これで、2つの変更ボタンの設定が終わりました。
4つの項目ボタン（登録・修正／レンタル／設定／保存終了）
最初にもお話ししましたが、司令塔の役割をしているメニュー画面なので、これらのボタンは次の画面を呼び出すだけの機能だけのVBAコードが書いてあります。
Private Sub CommandButton1_Click()
Unload UserForm4
UserForm1.Show
End Sub
登録・修正ボタンを押して、住所録に新規登録や検索・修正もあっという間にできるようになります。
Private Sub CommandButton2_Click()
Unload UserForm4
UserForm3.Show
End Sub
顧客の備考欄を改良するために新たに作成予定の項目で、備考欄なので基本的には空白になると新規登録での入力済み最終行が狂ってしまうので、別項目にすることで解決させます。
準備中！
Private Sub CommandButton6_Click()
Unload UserForm4
UserForm5.Show
End Sub
設定と言うか、どちらかというとリストアップや集計のイメージで、エクセル住所録のデータを「あいうえお順」に並べ替えたり、男性だけ女性だけのリストを抽出したりするようなエクセルの標準機能のフィルター機能みたいなものです。
例えば、こんな感じです作ろうと思っています。
- 名前順というコマンドボタンを押すと「あいうえお順」並べ替える
- 男性というボタンを押したら別シートにリストアップしてくれる
- 女性というボタンを押したら男性リストの横に並べてリストアップする
- 更新というボタンで更新済み・未更新をそれぞれリストアップする
この他にも、印刷やプリンターの設定なども作りたいですね。
出来上がったら追記します。
- コマンドボタン1が押されたら
- ユーザーフォーム4を閉じる
- ユーザーフォームを開く
- マクロ終了
3のユーザーフォームを開くは目的にユーザーフォームの番号により、表示されるフォームが異なります。
そして1つだけ違うのが、保存終了ボタンです。
保存終了ボタンとは、その名前の通り上書き保存してエクセルファイルを閉じるという事です。
Private Sub CommandButton3_Click()
Application.Quit
ThisWorkbook.Close savechanges:=True
End Sub
- コマンドボタン3が押されたら
- エクセルを終了する
- このエクセルファイルを閉じる時に上書き保存する
- マクロ終了
こうすることにより、エクセルを終了する時には必ず保存終了ボタンを押さない限り終了できないので、保存忘れも防げます。
ユーザーフォームの表示・終了・切り替えやタイマー機能などは詳細は下記を参考にして下さい。
