Excel使いこなしの基礎と応用


データの入力規則「リスト」の項目を追加・削除しても自動で修正されるように設定

入力規則のリストは入力値の選択肢が限られている場合、ドロップダウンリストで選べるように設定できる便利な機能です。住所録でも「区分(親戚・友人などの属性分け)」「敬称(様・先生など)」で使います。リストの設定自体は簡単ですが、ここではリストの選択肢を途中で変更したくなった場合に備えて、項目を追加・削除しても設定をやり直さずに自動修正されるようにしておきます。

目次


リストの項目(選択肢)を別シートに用意

住所録と同じシート上にリスト項目を置くことも可能ですが、データ入力時に予期しない変更が加わったり一部が消去されたりする事態を引き起こしかねないため、別シートに作ります。

スポンサーリンク

シートの最下部にあるシート名の右横の「」をクリックしてシートを増やします。
シートを増やす
「Sheet2」が追加されて開きました。シート名を「選択リスト」にしておきます。
シート名をつける
シートの追加・削除・名前の変更の詳細はこちらの記事を参照して下さい。

シート「選択リスト」のA列に「区分」の項目を、C列に「敬称1」の項目を並べます。それぞれの1行目に表題を入れています。
区分リストと敬称リストを作る
このリスト項目に名前を定義します。A列の「区分リスト」を選択して、「数式」タブの「定義された名前」グループにある「選択範囲から作成」ボタンをクリック。
作ったリストの名前を定義する
ダイアログボックスが開いたら、「上端行」にチェックを入れて「OK」。
名前の定義ダイアログで上端行にチェック

C列の「敬称リスト」も同じ操作で名前を定義しておきます。

OFFSET・COUNTA関数で参照範囲の増減に対応する

この時点で「区分」の最初のセルを選択し、「データ」タブの「データの入力規則」をクリック。
Excel2016データの入力規則ボタン
ダイアログで以下のように設定すれば、
入力規則のリストの元の値に区分リストを指定
ドロップダウンリストは機能します。
ドロップダウンリストが機能した
ですが、このままだとリストの項目を追加・削除する度に範囲を指定し直さなければなりません。その手間を省き、リストに変更を加えると自動で範囲が修正されるようにしたいわけです。

スポンサーリンク

OFFSET関数がリスト項目を列記する範囲を指定

OFFSETはある場所から指定された距離にある参照範囲を求める関数です。
OFFSETの基礎
この例だと、基準となるA1セルから3行下がり、2列右へ移動した高さ3行・幅2列の範囲(C4:D6)を求めるということです。▶OFFSET関数の基本を知る

「区分リスト」が並ぶA2セルからA列の可変するセルまでを参照範囲として求める式を上記例に当てはめて考えてみましょう。
区分リストをOFFSETの式に当てはめる
A2」を基準として、上下左右の移動は無しで「」、求める参照範囲の幅はA列のみで「」となります。・・・わかりにくいですか?

もっと解りやすくするために、シート「住所録」の任意のセルを選択して、OFFSETのダイアログを開き(「関数ライブラリ」の「検索/行列▼」⇨「OFFSET」)引数を入力してみます。
OFFSETのダイアログボックスを開く

最初の引数「参照」:シート「選択リスト」に切り替えて、A2セルをクリック。ここは可変しないようにF4キーを押して絶対参照にしておきます。
基準となるA2セルをクリックし、F4キーで絶対参照にする
絶対参照・相対参照を徹底的にわかりやすく

2番目と3番目の引数「行数」と「列数」:移動距離はありません。A2セルは基準点であると同時に求める参照範囲の始点でもあります。だから、ここは両方とも「
基準セルからの移動距離はないので行数・列数ともに0

最後の引数「幅」:参照するのはA列だけなので「」。
求める参照範囲の幅は1
そして、問題は4番目の引数の「高さ」。ここがポイント。ここにCOUNTA関数を入れ子して、リストの項目(=文字列)が入ったセル数を数え、「高さ」として指定するわけです。

COUNTA関数が範囲内にあるリスト項目を数える

COUNTA(カウント・エー)関数はセルまたはセル範囲の中に文字列などのデータが幾つ入っているかを数えます。

スポンサーリンク

前章のOFFSETのダイアログで、「高さ」のボックスにカーソルを置いて、画面左上の「名前ボックス」からCOUNTAをクリック。※見つからない時は「その他の関数」をクリック。
数式の挿入ダイアログで統計のCOUNTAをクリック
「関数の検索」ダイアログボックスで分類を「統計」にして「COUNTA」をクリック。

COUNTAのダイアログが開いたら、「値1」にA列全体を指定し、これもF4キーを押して絶対参照にしておきます。
COUNTA関数のダイアログで値1にA列を指定する
数式バーのOFFSETの文字をクリックしてOFFSETのダイアログに戻ります。COUNTA関数の式が「高さ」の引数に入力されていますね。これだと表題の「区分リスト」もカウントされているので、式の末尾に「-1」を付け足します。
OFFSETに戻り、高さに入ったCOUNTA関数の式に-1を付け足す
「OK」して数式バーを確認すると、「=OFFSET(選択リスト!$A$2,0,0,COUNTA(選択リスト!$A:$A)-1,1)」となっています。この式が「=次の範囲を参照します(シート「選択リスト」のA2セルを始点に、A列の項目が入力された最後のセルまで。但し、表題は除外します」という宣言になっていることが解りましたね?

名前の管理で範囲に数式を指定し、ドロップダウンリストの動作を確認

シート「選択リスト」に切り替えて、「数式」タブの「定義された名前」グループにある「名前の管理」をクリック。
数式タブの名前の管理をクリック
定義された名前の中から「区分リスト」を選び、「参照範囲」に前章で組み立てた数式を入れます。
定義された名前の参照範囲にOFFSETとCOUNTA関数で組み立てた数式を指定
同じく、「敬称リスト」の参照範囲も以下のように変更します。「A」を「C」に置き換えるだけなので簡単。
敬称リストの参照範囲にも数式を入れる

これで完了。最後に入力規則のリスト項目を追加してみて、動作確認しましょう。

大事なポイントが一つ。項目をリストの途中で追加する時は必ずセルを追加して下さい。追加したい場所のひとつ下の項目セルを選択して右クリック ⇒「挿入」⇒「下方向にシフト」で「OK」。
セルの挿入⇒下方向にシフト
行の追加を行うと、隣の「敬称リスト」に空欄が追加され、ドロップダウンリストが正常に機能しなくなります。

「区分リスト」に「同好会」という項目を追加して、ドロップダウンリストに反映されるかどうか見てみましょう。
区分リストに項目を追加してみる
ちゃんと反映されていますね。
ドロップダウンリストに追加項目が反映された
「敬称リスト」にも「君」と「ちゃん」を追加してみました。
敬称リストに項目を追加してみる
こちらも問題なさそうです。
ドロップダウンリストに追加した2つの項目が反映された

ついでに「区分」リストの選択と同時に通し番号が自動で振られるよう設定

宛名データの入力は左端A列から右へ順を追ってリズミカルに作業するのがベストです。この住所録のA列には「No.」の項目を設けてありますが、ここは手入力せず、B列の「区分」のリスト選択と同時に連番が自動で振られるように関数を入れておきます。

スポンサーリンク

通し番号を振るのに使うのは行番号を返してくれるROW関数です。この関数を実装すると、途中で行を追加しても番号を振り直す必要がありません。設定の詳細は行を追加・削除しても自動で通し番号が振り直されるの記事に詳述しています。

No.」の最初のセルを選択してROW関数のダイアログを開くと、「=ROW()」と数式が入り、そのままEnterを押すと「」が入ります。「このセルの行番号は?」と求めたのだから当然「2行目ですよ」という答え。ここから、項目が入った先頭行分の1を引けば「1」が返されます。式はこうです→「=ROW()-1
No.のセルを選択しROW関数のダイアログを開く
これを「区分」に入力値が入った時点で表示させるためには、IF関数の中にネストします。「No.」の最初のセルを選択して「関数ライブラリ」→「論理」→「IF」をクリック。
論理式のIF関数をクリック
IF関数のダイアログが開いたら、「論理式」に「[区分]=""」、「真の場合」に「""」と入れます。「もし、「区分」のセルが空欄だったら」「空白を返します」という意味ですね。最後の引数「偽の場合」にカーソルを置いて名前ボックス▼からROW関数を呼び出します。
IF関数のダイアログで「論理式」「真の場合」の引数を入力
ダイアログが開いたら、そのまま数式バーの「IF」をクリックしてIF関数のダイアログに戻ります。上記で説明した通り、項目が入った先頭行分の1を引くため「ROW()」の末尾に「-1」と付け足して「OK」。
IF関数の「偽の場合」に入った数式に-1を付け足す
数式は「=IF([区分]="","",ROW()-1)」「もし「区分」のセルが空だったら空欄のままにしそうでなかったら行番号から1を引いた値を返す

区分」のドロップダウンリストを選ぶと、自動で「No.」の列に番号が振られました。
区分のセルに入力値が入るとNo.のセルに自動で通し番号が振られた

▶次は、氏名を入力すると自動で「姓」「名」を分割し、フリガナを表示

Officeで作れるもの