EXCELの入力規則

質問

EXCEL2000で入力規則を使いたいのですが、

たとえば、sheet１の値をsheet２の表でプルダウンメニューにするとき、

sheet１の値が増減したときに対応できるやり方がわかりません。

http://www2.odn.ne.jp/excel/waza/validation.html#SEC3

を参考にしているのですが、うまくいきません。

さらに分かりやすく説明していただけますか。

回答No.5

BlueRay · ベストアンサー

>sheet１には、県名と言う名前を付けた県名のリストがあります。
どの列かはわかりませんが、とにかく対象列全てを県名用のリストにしているのですよね？
（※１　…　ここでは、仮にB列全体とします。
　　　　　　B1は、見出し部分とするのでリストデータはB2からとします。）

>このリストを、sheet２に作成した表の県名の各々のセルにプルダウンメニューが出るよう設定します。
プルダウンメニューを使用したいセルや列全体を入力規則で設定されていますよね？
（※２　…　ここでは、仮にE列全体とします。）

>ご案内いただいた
>=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1$A:$A)-1,1)では、
>sheetを丸ごと指しているので正しくないと思われます。
丸ごとは指していませんよ。
引き数
１．Sheet1!$A$2　…　Sheet1のセル(A2)を開始位置に指定しています。
２．0　…　１．で指定しているので必要ないので0。
３．0　…　１．で指定しているので必要ないので0。
４．COUNTA(Sheet1$A:$A)-1　…　Sheet1のA列全体から、空白になっていないセルの数。更に、開始位置を2としているので-1。
５．1　…　A列のみが対象なので1。

>また、sheet１で設定した範囲はA２から始まりますが、
>sheet２の表の該当のセルはＡ列とは限らないので、
その通りです、どのセルに指定しても同じリストを表示します。

上部で※１、※２を前提にCtrl+F3でリスト化すると
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)
となります。これを「県名」と名前を定義します。
そして、Sheet2を選択しE列全体を選択して入力規則を設定します。

以上のようになります。如何でしょうか？

これで、解決できないのであればもう少し具体的に、リストにする表(Sheet1)の図、リストを使用する表(Sheet2)の図らしき物を補足していただければ、回答いたしますよ。

お礼

お返事が遅くなり申し訳ありません。

確かに、おっしゃる通りのやり方で新規に表を作成すると、問題ないように見えます。

しかし自分の表に当てはめると、例えばリストに１０個入っていたとしても、

表が３行しかないときには、プルダウンメニューに３個しか出てこず、

５行になると、５個出てくるといった按配で、うまく表示されません。

文章で表のつくりを説明するのも限界があるので、もう少し自分で研究してみます。

どうもありがとうございました。

次にもし私の質問を見かけたら、またお願い致します。

回答No.4

BlueRay

>～、「項番１」の行しかないときには、
>sheet１に全県名のリストを作成してあるにもかかわらず、
>プルダウンメニューのリストにはひとつしか県名が出てきません。

OFFSET(基準,行数,列数,高さ,幅)
基準…開始位置の指定
行数…基準から指定した行数ずらして開始位置の指定
列数…基準から指定した列数ずらして開始位置の指定
高さ…対象とする範囲（行数）
幅　…対象とする範囲（列数）

Sheet1にリストがあるということなので、以下の様に記述されていれば問題ないと思いますよ。

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1$A:$A)-1,1)

COUNTAのところがCOUNTA(Sheet2$A:$A)-1とかになっていませんか？
その様な感じを受けましたが、解釈を間違えてたら補足をお願いします。

補足

ありがとうございます。

私の説明が分かり難くてすみません。

sheet１には、県名と言う名前を付けた県名のリストがあります。

このリストを、sheet２に作成した表の県名の各々のセルにプルダウンメニューが出るよう設定します。

sheet１の県名は追加や削除する事があります。

普通に設定すると、sheet１で定義した県名リストは、

追加でその定義範囲を超えるとプルダウンメニューに表示されなくなる事を避ける為、

参考資料に従い設定しましたが、うまく行きません。

ご案内いただいた
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1$A:$A)-1,1)では、

sheetを丸ごと指しているので正しくないと思われます。

また、sheet１で設定した範囲はA２から始まりますが、

sheet２の表の該当のセルはＡ列とは限らないので、

リストと対比するセルをOFFSETの記述でシフトさせなければいけないのでしょうか。

その辺が、参考資料に詳しく書いてないので分からないところです。

回答No.3

comv

こんばんは

>Sheet1の値が増減したときに対応できる
の意味が微妙ですが

リスト範囲の名前の定義をする範囲を
例えば元の値がＡ列にあるなら

Ａ列全体を選択（列記号をクリック）
挿入　名前　定義　適当な名前
（範囲には　=Sheet1!$A:$A　となっている筈です
　実際にリストに表示される範囲は、使用している
　行範囲です）

お礼

ありがとうございます。

Excelの「入力規則」を使ってセルにプルダウンメニューの機能を持たせたとき、

メニューの中のリストに追加項目や削除項目があったときに、

自動でメニューリストの増減に対応できる機能があると説明にあったので、

その設定をしているときにうまく機能しないので質問を挙げさせて頂きました。

comv様の設定は通常の設定で、リストの範囲を列で指定する方法ですね。

列を指定してもセルに入力された部分しか選択されませんし、

仮に、セル指定で大きく指定したとしても、

これだとリストの項目が少ない場合、プルダウンメニューを出したときに、

無駄な空白がダラッと表示されませんか。

回答No.2

BlueRay

いまいち状況が良くわからないですが、こちらで確認してみた結果
リスト全体が表示されない現象は、リスト中で空白の行があると
一覧の中身がずれていきますね。

以下の様にSheet2がなっている場合、リストにはAしか表示されません。

リスト
A

B

C

>「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」
この条件を修正すれば、納得のいくように出来るかと思います。
あまり詳しくないので、すいません。

お礼

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

項番｜県名　｜
－－－－－－－
１　｜東京　｜
－－－－－－－
２　｜埼玉　｜

例えば、このような表がsheet２にあって、

県名を入力する際に、県名のセルにプルダウンメニューが出るようにする入力規則を設定しました。

BlueRay様のやり方ですと、「項番１」の行しかないときには、

sheet１に全県名のリストを作成してあるにもかかわらず、

プルダウンメニューのリストにはひとつしか県名が出てきません。

そこで、「=OFFSET($A$2,0,0,・・・」の使い方を教えていただきたかったのです。

回答No.1

BlueRay

>http://www2.odn.ne.jp/excel/waza/validation.html#SEC3
「可変範囲の名前定義で、リストへの追加／削除に自動的に対応」
「部署2」を作成する所まではわかりますか？
わかっている前提とします。

>参考にしているのですが、うまくいきません。
そうですね。うまくいかないと思います。若干記述ミスがあると思います。

>>5.「部署」を選択してEnterを押す
ここで、選択するのは「部署」ではなく「部署2」を選択して下さい。

これで、目的のことが出来るようになると思います。如何でしょうか。

補足

ありがとうございます。

質問したあとでそのことに気が付きました。

それだと一見うまく行ったように見えるのですが、

たとえば、SHEET2の表が最初１行で時間を追うごとに

行が増えていく表だと、最初の方の少ない行数では、

プルダウンメニューの中のリストが全て表示されないのです。

引数の使い方が悪いのでしょうか。