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

スポンサーリンク

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

筆まめVer.30 オールシーズン 楽天で購入

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

これで、2つの変更ボタンの設定が終わりました。

4つの項目ボタン（登録・修正／レンタル／設定／保存終了）

最初にもお話ししましたが、司令塔の役割をしているメニュー画面なので、これらのボタンは次の画面を呼び出すだけの機能だけのVBAコードが書いてあります。

登録・修正ボタンを押して、住所録に新規登録や検索・修正もあっという間にできるようになります。

顧客の備考欄を改良するために新たに作成予定の項目で、備考欄なので基本的には空白になると新規登録での入力済み最終行が狂ってしまうので、別項目にすることで解決させます。

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

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

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

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

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

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

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

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

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