エクセルで使える名簿・住所録の無料テンプレートです。氏名を入れると番号が自動で出て、全体の人数とグループ別の人数を自動で数えます。項目は、氏名・ふりがな・グループ・郵便番号・住所・電話番号・メモの 7 つです。
この記事では、テンプレートの使い方と、番号を自動で出す式・人数を数える式の仕組みを、実際の Excel の画面で説明します。町内会・サークル・クラス・社内の連絡先など、どの名簿にも使える形です。年賀状のあて名の住所録としても使えます。
名簿・住所録のテンプレートの中身
シートは「名簿」の 1 枚です。左（A〜H 列）が名簿、右（J〜K 列）が自動で出る人数です。4 行目が見出しで、5〜34 行目の 30 人ぶんに番号の式が入っています。黄色のセルが入力する所です。
| 場所 | 項目 | 入れ方・式 |
|---|---|---|
| A 列 | 番号 | =IF(B5="","",ROW()-4)（氏名を入れると番号が出る） |
| B・C 列 | 氏名・ふりがな | 手で入力 |
| D 列 | グループ | リストから「1班」「2班」「3班」を選ぶ |
| E 列 | 郵便番号 | 手で入力（文字列の設定なので、先頭の 0 が消えない） |
| F 列 | 住所 | 手で入力 |
| G 列 | 電話番号 | 手で入力（文字列の設定なので、先頭の 0 が消えない） |
| H 列 | メモ | 手で入力 |
| K5〜K7 | グループ別の人数 | =COUNTIF($D$5:$D$34,J5) |
| K8 | 合計 | =SUM(K5:K7) |
| K10 | 全体の人数 | =COUNTA(B5:B34) |
| K11 | グループ未入力 | =K10-K8 |
テンプレートの使い方
- 入力例を消します。B5:H14 を選んで Delete キーを押します（A 列の番号は式なので消しません）。
- 右の J5〜J7 の「1班」「2班」「3班」を、自分のグループの名前に書きかえます。D 列のリストも同じ名前に直します（直し方は下の「自分用に変えるとき」）。
- 5 行目から、氏名・ふりがなを入れ、グループはセルの右の ▼ を押してリストから選びます。
- 郵便番号・住所・電話番号・メモを入れます。番号と人数は自動で出ます。
入力例の氏名・住所・電話番号は、どれも架空のものです。
1. 氏名を入れると番号が自動で出る（IF・ROW）
番号の列（A5）には、次の式が入っています。34 行目まで同じ形でコピーしてあります。
=IF(B5="","",ROW()-4)ROW 関数は、式が入っている行の番号を返します。5 行目なら 5 − 4 ＝ 1、14 行目なら 14 − 4 ＝ 10 です。IF 関数で、氏名が空の行は番号も空白にしています。入力例では、氏名を入れた 10 行に 1〜10 が出て、15 行目から下は空白になりました。
番号は行の位置から計算しているので、名簿を並べ替えても、上から 1・2・3… の順のままです。ほかの連番の作り方は 連番を振る方法で説明しています。
2. 全体の人数とグループ別の人数を数える（COUNTA・COUNTIF）
グループ別の人数（K5）の式は次のとおりです。
=COUNTIF($D$5:$D$34,J5)COUNTIF 関数は、グループの列（D5:D34）の中で、J5 の「1班」と同じセルを数えます。範囲を $ で固定しているので、下へコピーしても範囲がずれません。入力例では、1班が 4人、2班が 4人、3班が 2人、合計が 10人 になりました（COUNTIF関数の使い方）。
全体の人数（K10）は =COUNTA(B5:B34) で、氏名が入っているセルを数えています。こちらも 10人 です。その下の「グループ未入力」は、全体の人数からグループ別の合計を引いた数です。0人 なら、全員にグループが入っています。1人 以上のときは、グループを入れ忘れた人がいるか、リストにない名前が入っています。
3. 人を足すと、番号と人数が自動で変わる
15 行目に 11 人目（3班）を入れると、番号の 11 が自動で出て、3班は 2人 から 3人、全体の人数は 10人 から 11人 に変わりました。式は 34 行目まで入れてあるので、30 人までは入力するだけです。
ふりがなの欄は手で入力する
ふりがなの列は、式ではなく、手で入力する欄にしています。エクセルには、ふりがなを取り出す PHONETIC 関数がありますが、これはキーボードで打ったときの読みを取り出す関数です。ほかの表やメールからコピーして貼った氏名には読みの情報がないので、ふりがなが出ずに漢字のままになります。入力のしかたで結果が変わるので、このテンプレートでは使っていません。
氏名を自分で打って入力する名簿なら、C5 に =PHONETIC(B5) と入れて下へコピーする方法も使えます。くわしくは PHONETIC関数の使い方で説明しています。あいうえお順に並べ替えるときの注意は あいうえお順に並べ替える方法を見てください。
【実機で検証】入力例の人数を別の数え方と照らす
入力例の 10 人と、1 人足したときの結果を、目で数えた数と照らしました。
| 項目 | 入力例（10 人） | 1 人足したとき | 確かめ |
|---|---|---|---|
| 1班 | 4人 | 4人 | 佐藤・高橋・渡辺・小林 |
| 2班 | 4人 | 4人 | 鈴木・伊藤・中村・加藤 |
| 3班 | 2人 | 3人 | 田中・山本（足したのは 3班 の 1 人） |
| グループ別の合計 | 10人 | 11人 | 4 ＋ 4 ＋ 2、4 ＋ 4 ＋ 3 |
| 全体の人数 | 10人 | 11人 | 氏名を入れた行の数 |
| グループ未入力 | 0人 | 0人 | 全体の人数 − グループ別の合計 |
「グループ別の合計」と「全体の人数」は別の式ですが、同じ数になりました。
印刷するとき・自分用に変えるとき
- 印刷: 用紙は横向きで、名簿の A〜H 列（1〜34 行目）だけを印刷範囲にして、横幅が 1 ページに収まる設定にしてあります。右の人数の表は印刷されません。見出しの 4 行目は、2 ページ目以降にも印刷されます。
- グループの名前を変えたいとき: D5:D34 を選び、「データ」タブの「データの入力規則」で「元の値」を
総務,営業,経理のように書きかえます（データの入力規則の使い方）。右の J5〜J7 も同じ名前に直します。 - 31 人以上にしたいとき: 名簿の途中の行（たとえば 30 行目）をコピーして、その下に「コピーしたセルの挿入」で行を足します。番号の式とリストがいっしょに増え、人数の範囲も自動で広がります。
- 郵便番号と電話番号: 文字列の設定にしてあるので、
000-0001や03-0000-0001のように、ハイフン付きでそのまま入力できます（郵便番号の先頭の0が消えるとき、電話番号を扱う方法）。
よくある質問
この住所録から、年賀状のあて名を印刷できますか？
できます。Word の「はがき宛名面印刷」で、この名簿のファイルを住所録として読み込みます。手順は 住所録で年賀状の宛名を印刷する方法で説明しています。1 行目が見出しの表が読み込みやすいので、あて名用にするときは、名簿の部分（見出しの 4 行目から下）を新しいシートの A1 に貼り付けて使うと安心です。
グループ別の人数が、目で数えた数と合いません。
グループの文字が、右の表の名前と同じかを確かめます。「1班」と「１班」（数字が全角）、「1班 」（後ろに空白）は、別の文字として数えられます。リストから選ぶと、文字の違いを防げます。「グループ未入力」が 0人 でないときは、どこかの行がずれています。
2 つの名簿を見比べて、片方にしかいない人を探したいです。
COUNTIF 関数で、もう一方の名簿に同じ氏名があるかを調べます。2つの名簿を照合・比較する方法で説明しています。
