エクセルでプルダウンリストを作成した時、リスト項目を追加したい場合があります。リスト項目を追加すると、入力規則の元の値に設定した、リストの範囲を変更しなくてはいけません。それを、エクセルOFFSET関数、COUNTA関数を使って、可変した範囲を入力規則のリストに連動させることができます。この方法で、プルダウンリストの項目を追加したい場合、元の値に設定したリストの範囲を変更せずに、プルダウンリストに連動できます。入力規則のリストの範囲を可変にして、面倒な手間が省けます！
目次
データの入力規則でプルダウンリストを作成する方法
「A2:A10」に、プルダウンリストを作成します。ここでは同じシート、C列にプルダウンリストの「元の値」を入力して作成しましょう。
１．A2を選択して、「データの入力規則(V)」をクリックしましょう。「A2:A10」を選択して、「データの入力規則(V)」をクリックしてもいいです。
「データ」タブ→「データ ツール」グループ→「データの入力規則」▼
２．「データの入力規則」ダイアログボックスが開きます。「入力値の種類(A)」▼から「リスト」を選択して、「元の値(S)」に次の式を入力しましょう。
元の値(S) ： =$C$2:$C$4
元の値(S)ボックスで、範囲「C2:C4」を選択すると、自動で式が入ります。
３．「OK」ボタンをクリックしましょう。すると、A2にドロップダウンリストができます。
４．A2をコピーして、「A3:A10」に貼り付けましょう。最初「A3:A10」を選択して、「データの入力規則(V)」をクリックした場合は、コピーする必要はありません。
５．「A3:A10」に、ドロップダウンリストができます。
次に、可変する範囲を、プルダウンリストに連動させる方法を説明していきましょう。
同シートにプルダウンリストがある場合、可変範囲をリストに連動させる
プルダウンリストに項目を追加したい場合があります。その場合、追加する項目を入力した後、「データの入力規則」の「元の値(S)」のセルの範囲を、変更する必要があります。
ですが、設定し直さなくてもOFFSET関数、COUNTA関数を使用すると、「元の値(S)」のセルの範囲を変更しなくても、可変した範囲をリストに連動させることができます。同シートに、リストと範囲がある場合で、この方法を説明していきましょう。
C列に部署名を入力し、この項目を反映させるプルダウンリストを、「A2:A10」に作成します。
１．A2を選択して、「データの入力規則(V)」をクリックしましょう。
２．「データの入力規則」ダイアログボックスが開きます。「入力値の種類(A)」▼から「リスト」を選択して、「元の値(S)」に次の式を入力します。
元の値(S) ： =OFFSET(C2,0,0,COUNTA(C:C)-1,1)
この式は、OFFSET(基準, 行数, 列数, [高さ], [幅])が基本となっています。
「C2」が基準、 行数と列数は「0」を指定しているので、上下、左右ともシフトせず動きません。
｢幅」は「1」なので、1つの列でC列になります。
「高さ」、「COUNTA(C:C)-1」の式は、C列の値の個数「4」から「1」を引いて「3」を返しています。上の画像を見てください。C列には、部署名が3つ入力されています。この3つの行(3つの部署名)が返されます。
３．「OK」ボタンをクリックしましょう。A2にドロップダウンリストができます。
４．A2をコピーして、「A3:A10」に貼り付けましょう。
５．「A2:A10」に、可変した範囲をリストに連動させる、ドロップダウンリストができました。
これで完成です。
C列に部署名を追加入力すると、自動でプルダウンリストにも追加項目が表示されます！
別シートにプルダウンリストがある場合、可変範囲をリストに連動させる
別シートにリストと範囲がある場合の、可変範囲をリストに連動させる方法を説明します。
① シートA：プルダウンリストを作成するシート
② シートB：リストが入力されているシート
シートA
シートB
シートBに「元の値」となる項目を入力し、シートAの「A2:A10」に、プルダウンリストを作成します。
１．A2を選択して、「データの入力規則(V)」をクリック→「入力値の種類(A)」▼から「リスト」を選択して、「元の値(S)」に次の式を入力しましょう。
元の値(S) ： =OFFSET(シートB!A2,0,0,COUNTA(シートB!A:A)-1,1)
２．「OK」ボタンをクリックすると、A2にドロップダウンリストができます。
３．A2をコピーして、「A3:A10」に貼り付けましょう。
シートB、A列に部署名を追加入力すると、自動でプルダウンリストにも追加項目が表示されます！
名前の定義を入力規則に設定して、可変範囲をリストに連動させる
可変範囲を名前の定義に設定します。
１．「数式」タブから「定義された名前」グループの「名前の定義」コマンドを選択します。
２．名前(N)に「部署」と入力します。
３．参照範囲(R)に次の式を入力しましょう。
=OFFSET(C2,0,0,COUNTA(C:C)-1,1)
別シートの場合は、参照範囲(R)に次の式を入力します。
=OFFSET(シートB!A2,0,0,COUNTA(シートB!A:A)-1,1)
４．入力規則の「元の値(S)」に、次の式を入力しましょう。別シートでも同じ式を入力すればいいです。
元の値(S) ： =部署
C列に部署名を追加入力すると、自動でプルダウンリストにも追加項目が表示されます！
OFFSET関数
セルまたはセル範囲を基準として、基準から指定された行数と列数だけシフトした位置にある、セルまたはセル範囲の参照を返します。返されるセルの参照は、行数と列数を指定することもできます。
OFFSET関数の書式と引数
OFFSET(基準, 行数, 列数, [高さ], [幅])
| 基準 | 基準となるセルまたは隣接するセル範囲を指定します。 |
| 行数 | 基準の左上隅のセルを、上方向または下方向へシフトする行数を指定します。行数が正の場合、下方向へシフトし、負の場合上方向へシフトします。 |
| 列数 | 基準の左上隅のセルを、左方向または右方向へシフトする列数を指定します。行数が正の場合、右方向へシフトし、負の場合左方向へシフトします。 |
| 高さ | 省略できます。参照の行数を指定します。高さは正の数である必要があります。省略した場合、「１」が返されます。セル範囲の参照では「１」を省略、または「１」以外の数を指定するとエラー値「#VALUE! 」が返されます。 |
| 幅 | 省略できます。参照の列数を指定します。幅は正の数である必要があります。省略した場合、「１」が返されます。セル範囲の参照では「１」を省略、または「１」以外の数を指定するとエラー値「#VALUE! 」が返されます。 |
OFFSET関数の使用例
A1に、式を入力しましょう。
A1 =OFFSET(B1,2,3,1,1)
「E3」のセル、「２」が返されます。
A1の式は、セル「B1」を基準として、「B1」から行数「2」列数「3」、シフトしたセルの参照が返されます。
次に、A2に列数に、負の数を指定した式を入力しましょう。
A2 =OFFSET(D1,2,-1,1,1)
「C3」のセル、「6」が返されます。
A2の式は、セル「D1」を基準として、「D1」から行数「2」列数「-1」(左方向に1)、シフトしたセルの参照が返されます。
次に、A3に、基準をセル範囲で指定した式を入力しましょう。
A3 =OFFSET(B2:C3,1,2)
エラー値 「#VALUE! 」になります！
A3の式は、セル範囲「B2:C3」を基準としているので、「B1」から行数「1」列数「2」、シフトしたセルの参照が返されますが、「高さ」と「幅」を省略しているので、セルの範囲と同じ行数、列数であると見なされます。
結果、エラー値「#VALUE! 」が返されます。
次に、A4に、SUM関数とOFFSET関数を組み合わせた式を入力しましょう。
A4 =SUM(OFFSET(B1,1,2,2,2))
セルの範囲「D2:E3」の合計「24」が返されます。
A4の式は、セル「B1」を基準として、「B1」から行数「1」列数「2」、シフトしたセルの範囲「D2:E3」を、SUM関数で合計しています。
COUNTA関数
セル範囲に含まれる空白ではないセルの個数を返します。エラー値や空の文字列 (“”) を含め、すべての種類のデータを含むセルの個数を返します。
COUNTA関数の書式と引数
COUNTA(値 1, [値 2], …)
| 値1 | 1つ目のセル範囲 |
| 値2 | 追加のセル範囲。最大 255 個まで指定できます。 |
COUNTA関数の使用例
B8に式を入力しましょう。B6は空白です
B8 =COUNTA(B2:B7)
B6は空白なので、セルの個数「5」が返されます。
次は、B6には、空の文字列 (“”)が入力されています。この場合、B8に式を入力してみましょう。
B8 =COUNTA(B2:B7)
B6には空の文字列 (“”)が入力されているので、セルの個数「6」が返されます。
今度は、下の表で、B8にセルの範囲を2つ指定します。B8に式を入力しましょう。
B8 =COUNTA(A2:A7,B2:B7)
セルの範囲「A2:A7」と「B2:B7」のセル個数、「12」が返されます。
エクセル2003年では、参照範囲に別シートの式を入力できません。
ここでは、同シートと、別シートにリストの項目が入力されている場合で、可変する範囲をプルダウンリストに連動させる方法を説明しましたが、エクセル2003年では、別シートにプルダウンリストの項目が入力されている場合、入力規則の「元の値(S)」に式は入力できません。同シートの場合のみ、式が入力できます。エクセル2003年で別シートに範囲がある場合、名義の定義をしてから入力規則に名義を設定し、可変する範囲をリストに連動させましょう。
こちらの記事もご覧ください。⇒エクセルVLOOKUP関数で、選択したリスト項目に連動して自動入力させる方法！リストはショートカット選択できます。
