たった5秒で検索できるエクセル住所録を作る

住所録入力フォーム作成事例

顧客管理ソフトは高価だし、年賀状ソフトじゃ用が足りないからエクセルで住所録を作ろうとして調べていたら、このページにたどり着いたのですね。たぶん。

<span class="fz-12px">とりっぷぼうる</span>
とりっぷぼうる

エクセルで住所録を作るならば、絶対に覚えておかなければならないポイントがあります。

それは名前・住所など、ズバリ「項目」です。

エクセルで作った住所録では、新規登録したり、検索したり、修正したり、並び替えたりと、日々このように使って行くと思います。

その為に検索・修正などがやりやすいような項目を準備しなくてはいけませんので、実際にエクセル住所録で取り入れていることをまとめてみました。

スポンサーリンク

1人1行・1項目1セルで住所録を作る

エクセルに入力した住所録、同じ作るなら簡単便利に素早く検索・修正したいですよね。

言い換えれば、住所録をデータベースとして活用させれば、本当にたった5秒で検索できるようになります。

その為には、1人分の名前・住所・電話番号などのデータは必ず同じ行に入力し、1人1行を必ず実行してください。

そして1項目に1セル(1列)を使い、見栄えなどを優先して結合セルなんて絶対にやってはいけません

結合セルがあると検索の邪魔になるし、並べ替えもできなくなります。

もちろんデータの修正の時も結合セルを一旦解除してやらなくてはいけないので手間が増えるだけです。

最初に作成した時のエクセル住所録は15列でしたが、検索項目が増えたりスタッフの要望を受け付けているうちに、備考欄やこまごました項目が追加して、ついに30列まで増えてしまいました。

<span class="fz-12px">とりっぷぼうる</span>
とりっぷぼうる

私の場合、同じ人のデータならば同じ行にAA列から始まりBD列まで使うようになりました。

ポイント

検索・修正の為に1人1行・1項目1セルで結合しない。見栄えなんて気にしない。

エクセル住所録で作る項目

住所録を作成する時に必要な項目は様々ありますが、今使っているエクセル住所録の項目は抜粋ですが、下記の画像ように「名前」「住所」「電話番号」を含め複数あります。

エクセル住所録データベース
画像クリックで拡大します

もし、このエクセル住所録から宛名印刷をする時に、「連名」「敬称」が必要ならば項目を用意する必要がありますよね。

また、苗字と名前を分けておかないと連名も使えないので、更に項目を用意しなくてはいけません。

ポイント

年賀状ソフト「筆まめ」などでは、エクセルのデータを読み込む機能があり、連名を必要としなければ、敬称も自動付与されるので用意しなくてもいい場合があります。


エクセル住所録を作る時の各項目を全て手入力する必要はありません。

項目によっては、エクセル関数を使って計算式によって自動入力させることも可能ですし、エクセルVBAでプログラムを使って入力する方が、作業時間を短縮できますよ。

各項目の詳細については「住所録入力フォーム」ページと重複するのでここでは省略します。

そして出来上がったエクセル住所録のデータベースを使って検索・修正も簡単にあっという間にできるようにオリジナル入力フォームを使って行えば作業時間短縮になります。

スポンサーリンク

住所録の司令塔・メニュー画面を作る

エクセル住所録メインメニュー
エクセル開くと自動表示

エクセル住所録を素早く正確に入力・検索・修正を行うのに必要なメニュー画面をユーザーフォームで作ります。

ユーザーフォームについては専用ページを作ってありますので、詳細は下記をご覧くださいませ

このメニュー画面は、エクセルファイルを開くと自動的に今日の日付を読み取って表示され、機能としては、それぞれのボタン登録・修正レンタル設定保存終了を押すことで別のユーザーフォームを表示させるだけ、司令塔なのです。

例えば、登録済みの人を検索するならば、登録・修正ボタンを押すと画面が切り替わります。

住所録入力フォーム
切り替わった登録・修正フォーム

切り替わる時間わずか1秒、右上の「顧客番号検索」枠の苗字のフリガナを入力してフリガナ検索ボタンを押すと、左上の空欄の顧客番号が表示され顧客番号確定ボタンを押すと、登録済みの名前以下のデータが数字の「0」の代わりに表示されます。

<span class="fz-12px">とりっぷぼうる</span>
とりっぷぼうる

5秒もあれば充分検索可能です

この登録・修正フォームの右下にMENUボタンがあるので、ここを押せばメインメニュー画面に戻るようになっています。

メインメニューを作る準備

先ほどエクセルファイルを開いたら自動的に日付などを読み取ってメインメニューが表示すると書きましたが、読み取る為には日付などを準備しておかなければいけません。

エクセル住所録で使う今日の日付
住所録はAA列から使用し、手前に設定用項目を準備

上の図は実際に使っているエクセル住所録の抜粋で、メインメニューの「本日の日付」「本年度期間開始日」「シニア年齢」はそれぞれ、P2・S2・T2の各セルの値を読み取ってユーザーフォームに表示させています。

誕生日・更新状況・年齢をエクセル関数で設定する為で、今日の日付や更新基準日などが無いと計算できないので準備しましょう。

セルP2が「2019/9/4」と表示していますが、エクセル関数TODAYで現在日時を自動表示させて、セルQ2で「年」を取り出して表示、セルR2で月を取り出している関数はそれぞれ下記の通りです。

セルP2「日付」

=TODAY()

セルQ2「年」

=YEAR(P2)

セルR2「月」

=MONTH(P2)

またセルS2は更新基準日、セルT2はシニア年齢基準を入れて更新状況、年齢を比較できるようにしています。

2か所の変更ボタンの設定

有効期限の基準日となる「本年度期間開始日」と、「シニア年齢」となる基準年齢を変更する時に使うボタン(CommandButton)です。

このそれぞれの変更ボタンを押すことにより基準日や年齢が変更され、住所録全てのデータが差し変わるようになっていて、それぞれVBAコードが書いてあります。

本年度期間開始日変更ボタン

Private Sub CommandButton4_Click()
Range(“S2”).Value = UserForm4.TextBox1.Value
MsgBox “本年度期間開始日が変更されました”
End Sub

VBA解説
  1. コマンドボタン4が押されたら
  2. セルS2の値はユーザーフォーム4のテキストボックス1の値とする
  3. 「本年度期間開始日が変更されました」とメッセージを表示
  4. マクロ終了

次にシニア年齢変更ボタン。

シニア年齢変更ボタン

Private Sub CommandButton5_Click()
Range(“T2”).Value = UserForm4.TextBox2.Value
MsgBox “シニア年齢が変更されました”
End Sub

VBA解説
  1. コマンドボタン5が押されたら
  2. セルT2の値はユーザーフォーム4のテキストボックス2の値とする
  3. 「シニア年齢が変更されました」とメッセージを表示
  4. マクロ終了

これで、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

設定と言うか、どちらかというとリストアップや集計のイメージで、エクセル住所録のデータを「あいうえお順」に並べ替えたり、男性だけ女性だけのリストを抽出したりするようなエクセルの標準機能のフィルター機能みたいなものです。

例えば、こんな感じです作ろうと思っています。

  • 名前順というコマンドボタンを押すと「あいうえお順」並べ替える
  • 男性というボタンを押したら別シートにリストアップしてくれる
  • 女性というボタンを押したら男性リストの横に並べてリストアップする
  • 更新というボタンで更新済み・未更新をそれぞれリストアップする

この他にも、印刷やプリンターの設定なども作りたいですね。

出来上がったら追記します。

VBA解説
  1. コマンドボタン1が押されたら
  2. ユーザーフォーム4を閉じる
  3. ユーザーフォームを開く
  4. マクロ終了

3のユーザーフォームを開くは目的にユーザーフォームの番号により、表示されるフォームが異なります。

とりっぷぼうる
とりっぷぼうる

そして1つだけ違うのが、保存終了ボタンです。

保存終了ボタンとは、その名前の通り上書き保存してエクセルファイルを閉じるという事です。

保存終了ボタン

Private Sub CommandButton3_Click()
Application.Quit
ThisWorkbook.Close savechanges:=True
End Sub

VBA解説
  1. コマンドボタン3が押されたら
  2. エクセルを終了する
  3. このエクセルファイルを閉じる時に上書き保存する
  4. マクロ終了

こうすることにより、エクセルを終了する時には必ず保存終了ボタンを押さない限り終了できないので、保存忘れも防げます。

ユーザーフォームの表示・終了・切り替えやタイマー機能などは詳細は下記を参考にして下さい。

 

タイトルとURLをコピーしました