住所録の作成例：Excel基本講座

スポンサードリンク

更新:2021/1/30;作成:2007/11/26

住所録の構成について

シートの保護

データの抽出

完成例　

住所録の入力用シートを作成する例です。
作成した住所データをWord2007で宛名印刷するように加工を加えています。
- 都道府県名、住所(宛名用)ははがきに印刷する際に利用します。

住所録の構成について　

チェック欄：印刷するデータにはチェックを入れるように入力規則を利用します。
名前：入力規則でIMEをONにし、キーボードから入力します。
フリガナ：入力した名前のふりがな情報をPHONETIC関数で取得します。
郵便番号：入力した住所(入力)のふりがな情報をPHONETIC関数で取得します。
- (注)住所を郵便番号から変換して入力していることが前提です。
住所(入力)：入力規則でIMEをONにし、IMEの機能を利用し、郵便番号から住所に変換して入力します。
都道府県名：入力した住所(入力)からIF関数、MID関数、LEFT関数で取得します。
住所(宛名用)：入力した住所(入力)と都道府県名からSUBSTITUTE関数で取得します。
電話番号：入力規則でIMEをOFFにします。
電子メールアドレス：入力規則でIMEをOFFにします。
備考：入力規則でIMEをONにします。

チェック欄　

印刷するデータにはチェックを入れるように入力規則を利用します
1. A2:A100を選択します。
2. [データ]タブの[データツール]グループで[データの入力規則]→[データの入力規則]を実行します。
3. [データの入力規則]ダイアログの[設定]タブで、入力値の種類を「リスト」、元の値に「レ,　」(レ,　スペース)と入力します。
  なお、この図ではチェックのマークはユニコードを使用しています。
  - Microsoft Office IME 2007での変換例です。
    「ちぇっく」と入力して変換しますが、変換候補に表示されないときは「記号」から選択しています。
4. チェック欄で「レ」と「　」(スペース)が選択して入力できるようになりました。

名前、住所(入力)

入力規則でセルを選択時にIMEをONにします。
1. B2:B100とE2:E100を選択します。
  - 名前ボックスにB2:B100,E2:E100と入力します。
2. [データ]タブの[データツール]グループで[データの入力規則]→[データの入力規則]を実行します。
3. [データの入力規則]ダイアログの[日本語入力]タブで「オン」に設定します。
4. B2:B100とE2:E100のセル範囲のセルを選択すると、IMEの日本語入力がオンになります。
  - 郵便番号「８９２－０００２」と入力して変換している状況です。

フリガナ

入力した名前のふりがな情報をPHONETIC関数で取得します。
- C2セルに=PHONETIC(B2)　と入力し、C100までフィルハンドルをドラッグして数式をコピーします。
名前の入力時にフリガナと異なる読みで変換した場合には、フリガナが適切でなくなります。
名前のフリガナ情報を変更します。
1. [ホーム]タブの[フォント]グループにある[ふりがなの表示/非表示]→[ふりがなの編集]を実行します。
2. ふりがなの編集で適切な読みに修正します。
ふりがなを平仮名にするには？
1. 名前のセル範囲B2:B100を選択します。
2. [ホーム]タブの[フォント]グループにある[ふりがなの表示/非表示]→[ふりがなの設定]を実行します。
3. [ふりがなの設定]ダイアログの[ふりがな]タブで「ひらがな」にチェックを入れます。

郵便番号

住所を郵便番号から変換して入力していることが前提です。
入力した住所(入力)のふりがな情報をPHONETIC関数で取得します。
- D2セルに=ASC(LEFT(PHONETIC(E2),8))　と入力し、D100までフィルハンドルをドラッグして数式をコピーします。

都道府県名

入力した住所(入力)から都道府県名を関数で取得します。
- F2セルに=IF(MID(E2,4,1)="県",LEFT(E2,4),LEFT(E2,3))　と入力し、F100までフィルハンドルをドラッグして数式をコピーします。

住所(宛名用)

入力した住所(入力)と都道府県名からSUBSTITUTE関数で取得します。
- G2セルに=SUBSTITUTE(E2,F2,"")　と入力し、G100までフィルハンドルをドラッグして数式をコピーします

電話番号、電子メール

入力規則でセルを選択時にIMEをOFFにします。
1. セル範囲H2:I100を選択します。
2. [データ]タブの[データツール]グループで[データの入力規則]→[データの入力規則]を実行します。
3. [データの入力規則]ダイアログの[日本語入力]タブで「オフ(英語モード)」に設定します。

備考

入力規則でセルを選択時にIMEをONにします。
1. セル範囲J2:J100を選択します。
2. [データ]タブの[データツール]グループで[データの入力規則]→[データの入力規則]を実行します。
3. [データの入力規則]ダイアログの[日本語入力]タブで「オン」に設定します。

シートを保護する　

データ入力中に数式を消してしまわないようにシートを保護します。
1. 数式が入力されていないセルを選択します。
2. [ホーム]タブの[セル]グループにある[書式]→[セルのロック]を実行して、セルのロックを解除します。
3. [ホーム]タブの[セル]グループにある[書式]→[シートの保護]を実行します。
4. 「シートの保護」ダイアログの設定をします。

データの抽出

「入力用」シートでチェック欄(A列)にチェックのついたデータを印刷用として、シート「印刷用」に抽出して差し込み用データとします。
抽出はVBA(マクロ)でAdvanced Filterを利用しています。(Excel VBA入門編：フィルタオプション(AdvancedFilter)でのデータ抽出で説明しています。)
- シート名は「印刷用」としています。
- 抽出条件は「入力用」シートのM1:M2セルに設定しました。
- 抽出用に使用したコード
  Sub Adfilter10()
  　　Dim dRng As Range, eRng As Range, cRng As Range
  　　　　Set dRng = Worksheets("入力用").Range("A1").CurrentRegion 'データリスト
  　　　　Set cRng = Worksheets("入力用").Range("M1:M2") '条件範囲
  　　　　Set eRng = Worksheets("印刷用").Range("A1") '抽出先
  　　　　　　eRng.CurrentRegion.ClearContents
  　　　　　　　　dRng.AdvancedFilter _
  　　　　　　　　Action:=xlFilterCopy, _
  　　　　　　　　CriteriaRange:=cRng, _
  　　　　　　　　CopyToRange:=eRng, _
  　　　　　　　　Unique:=False
  End Sub

スポンサードリンク

Home｜Excel講座の総目次｜Excel基本講座の目次｜住所録の作成例

PageViewCounter
Counter
Since2006/2/27