ぴぴ
データの入力規則でリストを使用する場合、後で入力項目を追加した時に、また入力規則のリスト範囲を変更するのが大変なんです。
範囲も間違えたりするので、自動でリストの範囲を変更することができますか？
ピピンプイ
OFFSET関数とCOUNTA関数を使用して、リスト項目を追加しても、データの入力規則を変更することなく、リスト項目を変更することができます。
順を追って説明します。
1.OFFSET関数とは
・OFFSET関数は、セルやセル範囲から指定された行数と列数だけ、シフトした位置にあるセル範囲の参照を返します。
自分で指定したセル範囲を取得できます。
2.COUNTA関数とは
・COUNTA関数は範囲に含まれる、空白ではないセルの個数を返します。
文字を追加したら、空白ではなくなるので、空白ではない個数をCOUNTA関数で数えます。
3.入力規則のリスト作成方法
リストを作成したいセルを選択します。
「データ」タブの「データの入力規則」をクリックします。
「設定」タブの「リスト」を選択し、「元の値」の下の入力フィールドに
=OFFSET(リスト!$A$3,0,0,COUNTA(リスト!$A:$A),1)と入力します。
OFFSET関数で参照したいセルを起点に、今回は行も列も動きたくないので、行数も列数も0にします。
高さは、文字を追加した場合に自動で動かいたいので、COUNTA関数を使い、空白ではないA列全体のセルの個数を数えます。
別のワークシートにリストの一覧を作成し、そのリストを参照しています。
項目を追加すると、リストも自動的に追加されます。
これでA列のセルに新たなリスト項目を追加しても、いちいちセル範囲の参照先を変更しなくても、自動で参照範囲が変更されます。