【Excel】リストの追加項目関数式でエラーばかり

質問

セル範囲に名前を付けたグループを指定し、入力規則でリストを使って、コンボボックスから選択できるように設定する。

たとえばシート2のA1セルに「練習」という名前を付けた項目すべてをコンボボックスから指定したい。
しかし、このリストは追加される可能性があるが、範囲指定を繰り返すのは面倒なので関数にしたい。というケースです。

質問(1)シート1のリスト一覧に項目が増えた場合でオフセット関数を使う場合、以下の式でよいらしいのですが…
まずは式の読み方についてです。

=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)

OFFSETは
=OFFSET(基準,行数,列数 [,高さ,幅]）

『基点のセルから○行△列移動し、そこから高さと幅の分だけ範囲選択します。
基点のセルを動かす必要がない場合は、行と列は0を指定します。』

上の例の場合ですとこの式は

起点のセル→$A$1（A1を絶対参照とする）
行数→0
列数→0

しかし

COUNTA($A:$A), 1

については、($A:$A)はA列を絶対参照ということなのでしょうか？
またこの１はどのような意味なのでしょうか？

質問(2)
実際この関数を定義する場合「データ」の「名前の定義」の「参照範囲」に入力するそうですが…エラーとなってしまいます。

質問(3)
また、(2)の「名前」についても、元のリスト候補となるグループと同じ名前にした場合「別の名前を指定してください」とエラーがでます。
このエラーにより「データの入力規則」から指定する範囲も、「名前の定義」から指定する関数も「名前の管理」一覧に登録されることがわかりますが、テキストによっては同じ名前で登録できている説明もあるのです。

何が間違っているのでしょうか？
いろいろ調べたりしましたが、いろいろな例示があるので、逆に混乱してきます（お手上げ状態）。
関数は学び始めたばかりですので、アバウトな解説よりも具体的にご指導いただるご回答をお願いいたします。

回答No.1

MackyNo1

＞=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)

「A1」セルを基準に下方向に「0」行、右方向に「０」列移動した位置(すなわちA1セル）から、COUNTA関数の値（A列に入力されているデータ数）分だけの行数で、「1」列の範囲、すなわち、A列でセルに入力されているセル範囲を指定していることになります。

ちなみにCOUNTA($A:$A)はA列全体の空白以外のセルの数を計算しています。

＞実際この関数を定義する場合「データ」の「名前の定義」の「参照範囲」に入力するそうですが…エラーとなってしまいます。

おそらく数式の入力間違いだと思うのですが、どのようなエラーが出るのでしょうか？

＞「名前」についても、元のリスト候補となるグループと同じ名前にした場合「別の名前を指定してください」とエラーがでます。

名前定義はシート単位でもできますので、見かけ上同じ名前の定義も可能ですが、基本的に、混乱を避けるために同じ名前の定義しないようにしたほうが良いと思います。

補足

ご回答ありがとうございます。

いろいろ試してみて一応できました。
原因がわかりましたけれど、このリンク先の説明文の不足でした。
http://www2.odn.ne.jp/excel/waza/validation.html#SEC3

自動的に範囲が拡張されない場合の例
→「名前ボックス」で「部署」という名前を付ける

拡張される場合
→「名前の定義」で「部署2」という名前を付ける

という手順でしたが、いよいよ可変範囲をしていする式を定義する際の範囲指定では「部署」を選択しろと言われています。この説明違いが「追加できない」原因でした。

試しにここを「部署2」で範囲指定したところ無事に追加、削除できたわけです。

たとえば名前の重複についてもすでにある名前は定義できないのに、既存の名前のグループを用いた別の目的のための説明のため、混乱してしまったためです。

いずれにしろ、「名前の定義」「名前の管理」「データの入力規則」で登録される名前とその範囲や式は同じグループとして登録されることから、重複になるわけですね。