Excel2010 入力規則のリスト自動対応

質問

こんにちは。
EXCEL2010 入力規則のリストについて質問です。

名前の定義の設定とINＤＩＲＥＣＴを使ってリストを連動させる方法は、
理解できています。
選択リスト選択する内容が毎月追加・削除され一つずつ参照範囲を
直すのが大変です。

リスト選択する内容を自動で更新できる方法はないでしょうか？

リストデータシート
・部署名→1行目（現在Ａ1～Ｇ1にあり以下式で名前「部署名」と付けました。
=OFFSET('リストデータ'!$A$1,0,0,1,COUNTA('リストデータ'!$1:$1))
氏名→それぞれ50名程あります。Ａ2～Ｂ56

入力シート
Ａ6～Ａ56まで部署を選択します。（部署の選択設定は出来ています。）
Ｂ6～Ｂ56にそれぞれのＡ列で選択した部署の名前リストが出るようにしたい。

希望通りの設定が可能でしょうか？
どうぞ宜しくお願い致します。

回答No.3

tsubuyuki · ベストアンサー

・・・と投げっ放しもアレなので、コッソリ試してみました。
添付図をご覧くださいませ・・・と言いたいところなのですが、
お希望の処理から外れている可能性が高いなぁ、
と、やりながら思ってしまいました＾＾；
なので、参考までにどうぞ＾＾；；

ひとまず、右脇に部署コード表を用意して、
Ａ列Ｂ列にVLOOKUP関数を使って部署を出しています。
Ｃ列は氏名のつもり。
Ｄ列にフラグを（行番号）を入れています。
　　ちなみに、=IF(B2=$I$9,ROW(D2),"")　とし、
　　Ｉ９セルに選択した部署と合致したものだけにフラグを立てるようにします。
Ｆ列にはINDEX関数とSMALL関数、COUNT関数を使って、
名簿からフラグが立ったものだけ抜き取ります。
　　=IF(COUNT(D:D)<ROW(F2)-1,"",INDEX(C:C,SMALL(D:D,ROW(F2)-1)))
　　Ｄ列に入力されている数値を数え、データの個数より多い行は空白、
　　個数以下の場合はINDEX以下を返すようにします。
　　ひとまず、SMALL関数を使ってＤ列から「ROW(F列)-1番目」のデータがある行番号を見、
　　INDEX関数でＣ列から該当行を抜き出してきます。
　　これで、所属している人の名前（いろは）を抜き出してきます。

この先は画面（図）では見えていませんが、範囲名「名前リスト」を設定してやります。
図の場合、Ｆ２：Ｆ６に名前を付けてやれば問題ないのですが、なんせこの範囲は可変です。
で、ちょっとだけ工夫します。私の場合、
　　=Sheet1!$F$2:INDEX(Sheet1!$F:$F,COUNT(Sheet1!$D:$D)+1)
としてやりました。
ちょっと無駄があるかもしれませんが、ご勘弁くださいませ。
つまり、範囲の開始セルはＦ２、終了セルにINDEX関数を使って、
Ｆ列の「Ｄ列の数値が入力されているセルの数＋１」セルを指定してやりました。

あとは、入力規制→入力値の種類＝リスト→元の値「=名前リスト」としてやると、
部署に対応した名前リストをコンボボックスに表示してくれました。

ご希望の処理っぽかったらお試しくださいませ。
違ったら・・スルーなさってください＾＾；

お礼

説明とサンプルありがとうございます!!

今回は、INDIRECTを使うやり方を希望していましたが
このやり方も使えそうです。

とても、勉強になりました。
EXCEL、奥が深いですね。

回答No.2

tsubuyuki

他さまのサイトへのリンクで恐縮ですが、
http://excel.syogyoumujou.com/function/list.html
この辺りが参考になるのではないでしょうか。

例えばＡ列に部署、Ｂ列に名前、Ｃ列に選択された部署だったらフラグを立てるＩＦ式、
Ｄ列にフラグが立ったデータを集めてきて、範囲名を付ける・・
でお望みの動きが可能なのではないかと思います。

試していないので可能かどうかわかりませんが＾＾；

お礼

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

すごく高度なテクニックな感じです。。。
試してみます。

回答No.1

keithin

こんにちは。

「名前の定義を利用して可変範囲を取得する」「名前定義を経由して，計算させた範囲を入力規則の元ネタにする」などの，基本テクニックは理解されているということですね。

さて。
いま「部署名」を関数を使って名前定義なさっていますが，そもそも部署名のセル範囲（Ａ１：Ｇ１）を可変で計算させなきゃならないほど「部署数の増減（や部署名の変更）が頻繁にある」のですか？

ごく一般的に考えると，次のように作成します。
Ａ１「総務部」，Ｂ１「経理部」…Ｇ１「営業第二部」まで７部体制となっている。各部にぶら下がるメンバー（及び人数）には増減入れ替えがあるので，自動追従させたい。

名前の定義：
名前　　部署名
参照範囲　='リストデータ'!$A$1:$G$1　　（固定範囲に対して定義）

名前　　総務部
参照範囲　=INDIRECT("'リストデータ'!$A$2:$A$"&(COUNTA('リストデータ'!$A:$A)-1))

名前　　経理部
参照範囲　=INDIRECT("'リストデータ'!$B$2:$B$"&(COUNTA('リストデータ'!$B:$B)-1))
以下同文で「営業第二部」までそれぞれ個別部署名で名前定義を作成します

データシートのＡ列には入力規則のリストで　=部署名　とします
データシートのＢ列には入力規則のリストで　=INDIRECT(A6)　のようにします。

お礼

早速の回答、ありがとうございます。

はい、おっしゃるとおり部署名は可変させるほどありませんので
回答者様のとおり変更しました。

各部署の名前の定義の参照範囲ですが「入力した数式は正しくありません。」と表示され
OKをクリックすると、=INDIRECT("'リストデータ'!$A$2:$A$"の$A$の部分が反転しました。
$Aにしてみてもやはりエラーになってしまいました。。。

補足

NO.1の回答者様

名前の定義の参照範囲の設定出来ました。
しかし、リスト選択の表示がでなくなってしまいました。