Office 2019 Excel 2019

住所録テンプレートを使って効率よく入力できるように機能を追加

投稿日:2021年7月22日 更新日:

テンプレートを使用すると、一から作成するより、かなり短時間で目的のファイルを作成できます。

ただ、テンプレートをそのまま利用するのではなく、関数やユーザー定義などを追加すると、さらに便利なものになります。

ここでは、[住所録]テンプレートをダウンロードして、そのテンプレートを効率よく入力できるようにカスタマイズしていきます。

既存の住所録があれば、応用できる内容は適用して、効率よく入力できるように工夫してみてください。

住所録

[住所録]テンプレートをダウンロード

以下のページから[住所録]テンプレートをダウンロードします。

住所録 (はがき作成) - 無料テンプレート公開中 - 楽しもう Office(Microsoft)

[Excelテンプレートのダウンロード]をクリックして、任意の場所に保存します。

テンプレートの住所録(はがき作成)

保存先には、[21621_nengajou_juushoroku.xltx]というブックがあると思います。

この拡張子(.xltx)は、テンプレートです。

ダウンロードしたテンプレート

ダウンロードしたファイルのセキュリティ

ダウンロードしたファイルを開き、[保護ビュー]の[編集を有効にする]をクリックしても編集ができない場合があります。

その場合の解決方法です。(環境:Windows10 バージョン 21H2、Office2019 バージョン 2205)

ファイルで右クリックして、ショートカットメニューから[プロパティ]をクリックします。

プロパティの表示方法は、他にもあります。

ショートカットメニューのプロパティ

そして、[全般]タブのセキュリティにある[許可する]のチェックボックスをオンにして[OK]ボタンをクリックします。

これで使用できるようになります。[保護ビュー]の情報バーも表示されなくなります。

プロパティで一度設定すると、セキュリティ項目は非表示になります。

プロパティのセキュリティ[許可する]

表示倍率の調整

ダブルクリックでテンプレートを開いてみてくだい。

以下のように[保護ビュー]の情報バーが表示されたら、[編集を有効にする]をクリックします。

参考[編集を有効にする]をクリックするとフリーズすることがあります。プロパティで解決できます。

[保護ビュー]の情報バー

ブックを開くと、テーブルで作成されているのが分かります。

テーブルで作成された住所録テンプレート

[使い方」と[住所録]の2つのシートがあります。[使い方]シートは、読んでおいてください。

[住所録]シートを表示すると、少し小さく感じるのは、表示倍率が[70%]になっているためです。

大きくしたい場合は、ステータスバーのズームで任意の表示にしてください。

ここでは、100%にします。

ズームの[70%]の箇所をクリックすると、[ズーム]ダイアログボックスが表示されますので、[100%]を選択して[OK]ボタンをクリックします。

[ズーム]ダイアログボックスで[100%]を選択

テーブルの空白行の削除

テーブルは、セル[A1]からセル[O83]まで設定されていますが、ここでは説明しやすいように空白の領域は削除しておきます。

セル[A11]を選択して、キーボードから[Ctrl]+[Shift]+[↓]を使用すると、セル[A83]まで選択できます。

キーボード[Ctrl]+[Shift]+[↓]

右クリックしてショートカットメニューから[削除]-[テーブルの行]をクリックします。

ショートカットメニューから[削除]-[テーブルの行]

行全体を選択して、行の削除を行っても構いません。

行の削除

テーブルの範囲は、セル[A1]から、セル[O10]までになります。

フリガナについて

A列の[氏名]のセルに入力すると、PHONETIC関数によって自動で表示されるようになっていますが、コピー&ペーストしたものはフリガナは表示されません。

コピー&ペーストして住所録を作成したい場合は、簡単なマクロを使って表示することができます。データが少なければ、再変換すると表示できます。

ふりがなを別のセルに表示したい(PHONETIC関数とマクロ)

Excelには、ふりがなを表示できる便利な機能があります。 そして、PHONETIC(フォネティック)関数を使って、ふりがなを別のセルに表示 ...

続きを見る

フリガナを使用しないのであれば、フリガナの列は削除して構いません。

ただ、五十音順に並べ替えたいという場合は、フリガナの列はあったほうが便利です。

また、フォントサイズは、[6]になっていますので、小さいようであれば、大きくしてください。

フリガナのフォントサイズ

[姓]と[名]は関数を使用して自動表示できるように変更

[氏名]のセルに入力したら、[姓]と[名]のセルには自動的に入力されるようにします。

まず、[姓]と[名]に入力されている文字列は削除します。

セル[C2]に以下の数式を入力します。数式を確定すると、自動的にテーブルの最終行のセルまでコピーされます。

=IFERROR(LEFT(A2,SEARCH(" ",A2,1)-1),"")

姓のセルに関数を入力

セル[D2]には、以下の数式を入力します。数式を確定すると、自動的にテーブルの最終行のセルまでコピーされます。

=TRIM(SUBSTITUTE(A2,C2,""))

名のセルに関数を入力

ここで使用した数式については、以下の記事で解説しています。

関数を使って氏名の列を[姓]と[名]に分割

Excelで氏名の列を姓と名の列に分割したい場合がありますね。 区切り位置指定ウィザードを使用して分割する方法は、以下の記事で解説しています ...

続きを見る

A列のセルが空白の時は、エラーになるので、IFERROR関数でエラーの場合は空白にするようにしています。

VLOOKUP関数のエラー回避はIFERROR関数で

Excelでデータを参照する場合、VLOOKUP関数を使います。 下の左の表、セル[C2]からセル[C7]には、VLOOKUP関数が入力され ...

続きを見る

セル[A2]やセル[C2]をクリックすると、数式は以下のようになりますが、これでもOKです。

=IFERROR(LEFT([@氏名],SEARCH(" ",[@氏名],1)-1),"")

=TRIM(SUBSTITUTE([@氏名],[@姓],""))

[郵便番号]が自動表示されるように関数に変更

[郵便番号]も自動で表示されるようにします。

住所を入力したら自動で郵便番号は表示されるので、[郵便番号]のセルには何も入力しません。

入力されている郵便番号を削除します。

セル[I2]に以下の数式を入力します。数式を確定すると、自動的にテーブルの最終行のセルまでコピーされます。

=ASC(LEFT(PHONETIC(J2),8))

郵便番号のセルに関数を入力

この数式については、以下で解説しています。

郵便番号と住所の表示

注意住所を貼り付けしたような場合は、郵便番号が表示されない場合があります。住所を範囲選択して[変換]キーで再変換すると、表示されるようになります。

住所の入力について

[住所1]列のセルに住所を入力するときは、郵便番号を入力して変換すると早いです。

変換できるように、ひらがな入力モードで入力します。

郵便番号の列に関数を使用して郵便番号を表示するようにしているので、郵便番号から変換して表示するようにしてください。

このとき必ずハイフンを含めて変換します。

郵便番号を変換して住所を入力

郵便番号で変換できる範囲の住所を入力した後は、続けて番地を入力します。

住所の番地を入力

IMEの郵便番号辞書の情報(いつのデータなのか)を確認するには

IMEには、郵便番号辞書が標準で組み込まれています。 とても便利ですし、重宝しています。 ですが、時々変換されないこともあって、いったいこの ...

続きを見る

[電話番号]をハイフンなしで入力できるようにユーザー定義で設定

電話番号は、テンプレートのように[3桁-4桁-4桁]で表示されるようにするなら、ユーザー定義で以下のようにします。

入力済みの電話番号を削除します。

L列のセル[L2]からセル[L10]を選択して、右クリックしてショートカットメニューから[セルの書式設定]をクリックします。

ショートカットメニューから[セルの書式設定]

[セルの書式設定]を表示するショートカットキーは、[Ctrl]+[1]です。

キーボード[Ctrl]+[1]

[表示形式]タブの[ユーザー定義]をクリックして、[種類]のテキストボックスに、以下のように入力します。

000-0000-0000

[セルの書式設定]の[ユーザー定義]

これでハイフンなしで、数字を入力するだけでOKです。試しに携帯電話番号などを入力してみてください。

先頭の0は入力しなくても表示されます。

電話番号の入力

参考電話番号を、括弧付きで表示したい場合は、以下の記事を参照してください。東京の電話番号表示は、選択するだけです。

郵便番号と電話番号の簡単入力(東京都内の電話番号は超便利)

セルに郵便番号を入力する場合、7桁の番号を連続して入力しても、自動で3桁目の後ろに[-](ハイフン)を表示させることができます。 また、電話 ...

続きを見る

入力規則のリストの編集

M列の[昨年出した]とN列の[昨年もらった]は、データの入力規則が設定されていて、ドロップダウンリストから選択できるようになっています。

ドロップダウンリスト

このドロップダウンリストの内容を変更したい場合の方法を記しておきます。

セル[M2]を選択して、[データ]タブの[データツール]グループにある[データの入力規則]をクリックします。

[データの入力規則]の[設定]タブで[元の値]で編集します。リストの区切りは、[半角カンマ](,)です。

そして、[同じ入力規則が設定されたすべてのセルに変更を適用する]のチェックボックスをオンにして[OK]ボタンをクリックします。

[データの入力規則]の[設定]タブ-元の値

注意このテンプレートのM列とN列は、テーブルで自動拡張(新しいデータを追加したときに自動的にテーブルの範囲が拡張される)すると、入力規則がコピーされません。

コピーされるように修正します。

テーブル外のセル[M11]とセル[N11]を選択して、キーボードから[Ctrl]+[Shift]+[↓]を押して、最後の行(1048576行)のセルまで選択します。

キーボード[Ctrl]+[Shift]+[↓]

そして、[ホーム]タブの[編集]グループにある[すべてクリア]をクリックします。この操作を実行しておくと、自動拡張で入力規則はコピーされるようになります。

[ホーム]タブの[編集]グループにある[すべてクリア]

IMEの設定

また、ひらがな入力となる列(A列~I列とJ列とK列)は、[データの入力規則]で既に[日本語入力]が[オン]の設定にしてあります。

[データの入力規則]の[日本語入力]の設定-[オン]

I列の[郵便番号]とL列の[電話番号]の列は、[オフ(英語モード)]に設定してあります。

なので、このテーブルでIMEの半角とひらがなの切り替えは不要です。

[データの入力規則]の[日本語入力]の設定-[オフ]

参考入力規則のIMEの設定については、以下で解説しています。

データの入力規則を使ってIMEの入力モードを自動切り替え

Excelでは、[入力規則]を使って入力時の日本語入力をオンにしたり、オフにしたりすることができます。 この設定を行っておくと、入力時にIM ...

続きを見る

住所録テンプレートの書式設定について

このMicrosoftのテンプレート[21621_nengajou_juushoroku.xltx]の書式設定は、K列(住所2)とL列(電話番号)は[文字列]になっています。列に設定してあります。

テーブルは、K列(住所2)、L列(電話番号)、M列(昨年出した)、N列(昨年もらった)、O列(備考)が[文字列]になっています。

新規データを入力して自動拡張した場合、M列(昨年出した)、N列(昨年もらった)、O列(備考)は書式がコピーされません。[標準]になります。

[標準]になっても別に問題はないと思いますが、もし、テンプレートの既定のまま[文字列]にしたいのであれば、再度、テーブル内のM列からO列までを選択して、[文字列]の設定をしてください。

そうすると、自動拡張しても書式がコピーされるようになります。

参考テーブルであれば、自動拡張した場合、通常は書式もコピーされます。

文字列の書式設定

新規入力でテーブルの自動拡張を確認

新しいデータを入力する場合は、そのままA列最後のセルに続けて入力します。

姓と名の間は、全角スペースを入力します。

自動的に書式や数式、入力規則などもいっしょに拡張されます。テーブルの機能の一つです。

新規入力

もし、自動拡張されない場合は、オプションの設定を確認してみてください。

テーブルのサイズ変更と解除(自動拡張の設定はオートコレクトで)

Excelでテーブルを作成した後、テーブルのサイズを変更する方法です。 また、テーブルを通常の表に変換する方法と、自動拡張の設定についても解 ...

続きを見る

また、テーブルを使用せずに条件付き書式で罫線を自動設定する方法も解説しています。

新規データを追加したら自動的に罫線が追加される表にするには

Excelで表を作成する時、罫線を引いたりして書式を整えますね。 データが増えることがない表であれば、それで終わりかもしれませんが、新規デー ...

続きを見る

先頭列を固定表示

横へスクロールしても[氏名]列は常に見えるように固定しておいたほうがいいと思います。

[表示]タブの[ウィンドウ]グループにある[ウィンドウ枠の固定]から[先頭列の固定]をクリックします。

先頭列の固定

ワークシートの最初の列を表示したままで、ほかの部分をスクロールできます。

[ウィンドウ枠の固定]の[先頭列の固定]

印刷範囲とタイトル行の指定

テンプレートには、印刷範囲と、タイトル行が設定されています。

[ページレイアウト]タブの[ページ設定]グループにある[印刷タイトル]をクリックします。

以下のように設定されています。必要であれば、変更してください。

[ページ設定]ダイアログボックスの[シート]タブ

すべてのページに項目行(印刷タイトル)を設定

縦に長い表を作成して、その表を印刷プレビューで確認すると、1ページには項目行が表示されますが、2ページ以降には項目行が表示されていません。 ...

続きを見る

名前を付けて保存(新しい住所録テンプレートの作成)

項目名の変更や、不要な列の削除、列の追加などの編集が終わったら、テンプレートを別名を付けて保存します。

[連名]や[敬称]の列が不要であれば、削除しても構いませんし、入力データを削除して空白データにしても構いません。

ただし、数式や書式などは削除しないように気をつけてください。

[使い方]シートは、削除してもいいですし、必要であればメモとして追記しておいてもいいですね。

数式などを入力したので、間違って編集されても、すぐ修正できるようにテンプレートを1つ作成しておきましょう。

キーボードから[F12]を押して、[名前を付けて保存]ダイアログボックスを表示します。

キーボード[F12]

[ファイルの種類]で[Excelテンプレート(*.xltx)]を選択して、分りやすい名前を付けて[保存]ボタンをクリックします。

保存先は、既定は以下のようになっています。

C:\Users\(ユーザー名)\Documents\Office のカスタム テンプレート

テンプレートについては、以下の記事で詳細に解説しています。

原本(テンプレート)として保存して利用する

毎回、データを入力して完成させるようなファイルは、原本(テンプレート)を保存しておくと、そのファイルを開くたびに原本のコピーが自動的に作成さ ...

続きを見る

[名前を付けて保存]ダイアログボックスで[Excelテンプレート]を選択

参考数式が入力されたセルをプロテクトしたい場合は、テーブルを解除して行ってください。

テーブルを解除しても、数式が消えることはありません。

テーブルのまま、保護をかけると自動拡張の機能は使用できなくなります。

テーブルのサイズ変更と解除(自動拡張の設定はオートコレクトで)

Excelでテーブルを作成した後、テーブルのサイズを変更する方法です。 また、テーブルを通常の表に変換する方法と、自動拡張の設定についても解 ...

続きを見る

数式を壊されたくない、数式が入力されたセルのみロック(保護)するには

Excelのブックを複数人と共有すると「えっ?」と思うことはよくあります。 「せっかく苦労して作成した数式が消えてる!」なんてこともあります ...

続きを見る

テンプレートを起動して新規住所録ブックを作成

テンプレートを開いて、実際に入力していきます。

Excelを起動して、Backstageビューの[新規]から[個人用]を選択すると、先ほどテンプレートとして保存したブックが表示されていると思いますので、クリックして起動します。

テンプレートとして保存したブックが新しいブックにコピーされて、開きます。

Backstageビューの[新規]-[個人用]

編集後は、名前を付けて、既定のExcelブック(*.xlsx)として保存します。

次回からは、このファイルを起動してデータを追加していきます。

[名前と付けて保存]ダイアログボックスで[Excelブック]として保存

テンプレート編集する場合は、[開く]ダイアログボックスから[Officeカスタムテンプレート]フォルダーを開いて、テンプレートとして保存したファイルを選択して起動し、編集後、上書き保存します。

テンプレートの編集と削除

参考テーブルの機能については、以下で解説しています。

テーブルの便利機能(テーブルに変換して作業効率アップ)

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

Wordのはがきウィザードは、以下を参照してください。

はがき宛名面印刷ウィザードを使って宛名作成(Excelの住所録から)

Wordの差し込み印刷機能を使って、Excelで作成した既存の住所録をもとに葉書のあて名面を作成することができます。Excelの住所録を使用 ...

続きを見る

Microsoftでも、同じ住所録テンプレートを使用した宛名印刷の解説があります。

カンタン宛名面印刷 - 年賀状 特集 - 楽しもう Office(Microsoft)

また、住所録などの2つのシートを比較して、異なるデータを見つける方法も解説しています。

データが修正されたセルが分かるようにする条件付き書式設定

Excelでセルの値が修正されたことが一目で分かるようにしておくと、便利なことはよくあります。 たとえば、各部署に既存の住所一覧を配布して、 ...

続きを見る

検索

ほかの記事も検索してみませんか?

Profile

執筆者/はま

-Office 2019 Excel 2019

Copyright © 2001-2022 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2022 AllRights Reserved Powered by AFFINGER4.