エクセル住所録の作成ー３「プルダウンの項目を自動設定」
使用関数：OFFSET、COUNTA
入力規則のリストは入力値の選択肢が限られている場合、ドロップダウンリストで選べるように設定できる便利な機能です。住所録でも「区分（親戚・友人などの属性分け）」「敬称（様・先生など）」で使います。リストの設定自体は簡単ですが、ここではリストの選択肢を途中で変更したくなった場合に備えて、項目を追加・削除しても設定をやり直さずに自動修正されるようにしておきます。
リストの項目（選択肢）を別シートに用意
住所録と同じシート上にリスト項目を置くことも可能ですが、データ入力時に予期しない変更が加わったり一部が消去されたりする事態を引き起こしかねないため、別シートに作ります。
シートの最下部にあるシート名の右横の「＋」をクリックしてシートを増やします。
「Sheet2」が追加されて開きました。シート名を「選択リスト」にしておきます。
シートの追加・削除・名前の変更の詳細はこちらの記事を参照
シート「選択リスト」のA列に「区分」の項目を、C列に「敬称１」の項目を並べます。それぞれの１行目に表題を入れています。
このリスト項目に名前を定義します。A列の「区分リスト」を選択して、「数式」タブの「定義された名前」グループにある「選択範囲から作成」ボタンをクリック。
ダイアログボックスが開いたら、「上端行」にチェックを入れて「OK」。
C列の「敬称リスト」も同じ操作で名前を定義しておきます。
OFFSET・COUNTA関数で参照範囲の増減に対応する
この時点で「区分」の最初のセルを選択し、「データ」タブの「データの入力規則」をクリック。
ダイアログで以下のように設定すれば、
ドロップダウンリストは機能します。
ですが、このままだとリストの項目を追加・削除する度に範囲を指定し直さなければなりません。その手間を省き、リストに変更を加えると自動で範囲が修正されるようにしたいわけです。
OFFSET関数がリスト項目を列記する範囲を指定
OFFSETはある場所から指定された距離にある参照範囲を求める関数です。
この例だと、基準となるA1セルから３行下がり、２列右へ移動した高さ３行・幅２列の範囲（C4:D6）を求めるということです。
OFFSET関数の基本を知る
「区分リスト」が並ぶA2セルからA列の可変するセルまでを参照範囲として求める式を上記例に当てはめて考えてみましょう。
「A2」を基準として、上下左右の移動は無しで「０」、求める参照範囲の幅はA列のみで「１」となります。・・・わかりにくいですか？
もっと解りやすくするために、シート「住所録」の任意のセルを選択して、OFFSETのダイアログを開き（「関数ライブラリ」の「検索／行列▼」⇨「OFFSET」）引数を入力してみます。
最初の引数「参照」：シート「選択リスト」に切り替えて、A２セルをクリック。ここは可変しないようにF4キーを押して絶対参照にしておきます。
絶対参照・相対参照を徹底的にわかりやすく
２番目と３番目の引数「行数」と「列数」：移動距離はありません。A2セルは基準点であると同時に求める参照範囲の始点でもあります。だから、ここは両方とも「０」
最後の引数「幅」：参照するのはA列だけなので「１」。
そして、問題は４番目の引数の「高さ」。ここがポイント。ここにCOUNTA関数を入れ子して、リストの項目（＝文字列）が入ったセル数を数え、「高さ」として指定するわけです。
COUNTA関数が範囲内にあるリスト項目を数える
COUNTA（カウント・エー）関数はセルまたはセル範囲の中に文字列などのデータが幾つ入っているかを数えます。
前章のOFFSETのダイアログで、「高さ」のボックスにカーソルを置いて、画面左上の「名前ボックス」からCOUNTAをクリック。※見つからない時は「その他の関数」をクリック。
「関数の検索」ダイアログボックスで分類を「統計」にして「COUNTA」をクリック。
COUNTAのダイアログが開いたら、「値１」にA列全体を指定し、これもF4キーを押して絶対参照にしておきます。
数式バーのOFFSETの文字をクリックしてOFFSETのダイアログに戻ります。COUNTA関数の式が「高さ」の引数に入力されていますね。これだと表題の「区分リスト」もカウントされているので、式の末尾に「-1」を付け足します。
「OK」して数式バーを確認すると、「=OFFSET(選択リスト!$A$2,0,0,COUNTA(選択リスト!$A:$A)-1,1)」となっています。この式が「＝次の範囲を参照します（シート「選択リスト」のA2セルを始点に、A列の項目が入力された最後のセルまで。但し、表題は除外します）」という宣言になっていることが解りましたね？
名前の管理で範囲に数式を指定し、ドロップダウンリストの動作を確認
シート「選択リスト」に切り替えて、「数式」タブの「定義された名前」グループにある「名前の管理」をクリック。
定義された名前の中から「区分リスト」を選び、「参照範囲」に前章で組み立てた数式を入れます。
同じく、「敬称リスト」の参照範囲も以下のように変更します。「A」を「C」に置き換えるだけなので簡単。
これで完了。最後に入力規則のリスト項目を追加してみて、動作確認しましょう。
大事なポイントが一つ。項目をリストの途中で追加する時は必ずセルを追加して下さい。追加したい場所のひとつ下の項目セルを選択して右クリック ⇒「挿入」⇒「下方向にシフト」で「OK」。
行の追加を行うと、隣の「敬称リスト」に空欄が追加され、ドロップダウンリストが正常に機能しなくなります。
「区分リスト」に「同好会」という項目を追加して、ドロップダウンリストに反映されるかどうか見てみましょう。
ちゃんと反映されていますね。
「敬称リスト」にも「君」と「ちゃん」を追加してみました。
こちらも問題なさそうです。
ついでに「区分」リストの選択と同時に通し番号が自動で振られるよう設定
宛名データの入力は左端A列から右へ順を追ってリズミカルに作業するのがベストです。この住所録のA列には「No.」の項目を設けてありますが、ここは手入力せず、B列の「区分」のリスト選択と同時に連番が自動で振られるように関数を入れておきます。
通し番号を振るのに使うのは行番号を返してくれるROW関数です。この関数を実装すると、途中で行を追加しても番号を振り直す必要がありません。設定の詳細は以下の記事に詳述しています。
行を追加・削除しても自動で通し番号が振り直される
「No.」の最初のセルを選択してROW関数のダイアログを開くと、「=ROW()」と数式が入り、そのままEnterを押すと「２」が入ります。「このセルの行番号は？」と求めたのだから当然「２行目ですよ」という答え。ここから、項目が入った先頭行分の１を引けば「１」が返されます。式はこうです→「=ROW()-1」
これを「区分」に入力値が入った時点で表示させるためには、IF関数の中にネストします。「No.」の最初のセルを選択して「関数ライブラリ」→「論理」→「IF」をクリック。
IF関数のダイアログが開いたら、「論理式」に「[区分]=""」、「真の場合」に「""」と入れます。「もし、「区分」のセルが空欄だったら」「空白を返します」という意味ですね。最後の引数「偽の場合」にカーソルを置いて名前ボックス▼からROW関数を呼び出します。
ダイアログが開いたら、そのまま数式バーの「IF」をクリックしてIF関数のダイアログに戻ります。上記で説明した通り、項目が入った先頭行分の１を引くため「ROW()」の末尾に「-1」と付け足して「OK」。
数式は「=IF([区分]="","",ROW()-1)」「もし「区分」のセルが空だったら空欄のままにし、そうでなかったら行番号から１を引いた値を返す」
「区分」のドロップダウンリストを選ぶと、自動で「No.」の列に番号が振られました。
もう一つ、これはケースバイケースですが、連名の敬称が「様」オンリーの場合は「連名」フィールドに入力されたら自動で「敬称２」に「様」が入るようにした方がいいと思います。（入力値が複数あるなら、「敬称１」と同じくドロップダウンリストで）
「敬称２」の最初のセルを選択して、「No.」と同様「IF関数」のダイアログで「論理式」に「[連名]=""」、「真の場合」に「""」、「偽の場合」に「"様"」と入れます。
数式は「=IF([連名]="","","様")」「もし「連名」のセルが空だったら空欄のままにし、空でなかったら「様」を入力する」
もっとついでに入力ミス回避のため最適な日本語入力を設定
一つのフィールド（列）から次のフィールドへ移動した時、その度に入力値に合わせて日本語の入力モードを変更するのは煩わしいですね。そこで、あらかじめ日本語入力の入力規則を設定しておいて、セル移動すると適正な入力モードに自動で切り替わるようにしておきましょう。
「郵便番号」「住所２」「自宅電話番号」「携帯電話番号」「E-Mail」は半角英数字の入るフィールドなので、日本語入力をオフにしておきます。各フィールドの最初のセルをContrlキーを押しながら選択し、「データの入力規則」→「日本語入力」で「オフ（英語モード）」を選択します。
「氏名」「印刷用住所２」「連名」「備考」は、かなモードで入力するフィールドなので日本語入力を「ひらがな」にしておきます。各フィールドの最初のセルをContrlキーを押しながら選択し、「データの入力規則」→「日本語入力」で「ひらがな」を選択します。