EXCELで質問です。

質問

以下のように作成したいのですが、重複するコードがあって入力規則やVLOOKUP関数が使いこなせません。
良きアドバイスをよろしくお願いします。

【表示】の部署コードを入力すると、
　(1)部署名が表示される
　(2)部署コードに該当する担当コードを
　　ドロップダウンリストで表示する
　(3)(2)に該当するコードの担当名を表示する

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿
【リスト】
　部署コード 　部署名 　担当コード 　担当名
　10000　　　　AAA　　　100　　　　　A
　10000　　　　AAA　　　200　　　　　B
　10000　　　　AAA　　　300　　　　　C
　20000　　　　BBB　　　100　　　　　D
　20000　　　　BBB　　　200　　　　　E
　30000　　　　CCC　　　100　　　　　F

【表示】
　部署コード_______ 部署名_______
　担当コード_______ 担当名_______
＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

　※(1)はVLOOKUP関数でできたのですが、
　　(2)と(3)がわかりません。
　　入力規則を使用しての表示は無理でしょうか？

回答No.2

maruru01 · ベストアンサー

こんにちは。maruru01です。

まず、まったく関係のない場所（別のシートでも可）に、以下のように部署コードのみの表を作成します。

部署コード
10000
20000
30000

そうしたら、タイトルを除いて範囲選択し、メニューの[挿入]→[名前]→[定義]で名前を付けます。
（仮に「部署コード」とします。）

質問の[リスト]範囲をA1:D7（1行目は項目名）とし、[表示]を、
H1：部署コード
H2：部署名
H3：担当コード
H4：担当名
とします。

さらに、特に範囲選択をせずに、メニューの[挿入]→[名前]→[定義]で以下のように名前を定義します。
参照範囲：「=OFFSET(Sheet1!$C$1,MATCH(Sheet1!$H$1,Sheet1!$A$2:$A$7,0),0,COUNTIF(Sheet1!$A$2:$A$7,Sheet1!$H$1),1)」
名前：「担当コード」
（名前は自由に付けて構いません）

H1（部署コード）の入力規則を、
入力値の種類：「リスト」
元の値：「=部署コード」
にします。

H2（部署名）のセルに、

=IF(COUNTIF($A$2:$A$7,$H$1)=0,"",VLOOKUP($H$1,$A$2:$B$7,2,FALSE))

と入力します。

H3（担当コード）の入力規則を、
入力値の種類：「リスト」
元の値：「=担当コード」
にします。

H4（担当名）のセルの数式バーに、

=VLOOKUP($H$3,IF($B$2:$B$7=$H$2,$C$2:$D$7,""),2,FALSE)

と入力し、まだ数式バーにカーソルがある状態で、[Ctrl]と[Shift]を押しながら[Enter]を押して決定します。
（数式の両端に「{}」が付いて配列数式になります。）

名前定義やセルの数式のリストの範囲は、実際の範囲に直して下さい。

また、名前や入力規則に関するページと、配列数式に関するページを紹介しておきます。

http://www2.odn.ne.jp/excel/waza/validation.html#SEC5
http://pc21.nikkeibp.co.jp/pc21/pc_10/hr_top.htm

参考URL：

http://www2.odn.ne.jp/excel/waza/validation.html#SEC5,http://pc21.nikkeibp.co.jp/pc21/pc_10/hr_top.htm

お礼

お礼が遅くなりましてごめんなさい。
やっとできました！
あの後追加項目があって時間がかかってしまいました。
ありがとうございました。
丁寧な説明と参考URLも記載して頂いて助かりました。

回答No.3

oresama

1;担当コードの表示セルは、
　入力規則で「リストより入力」にします。
2：リストの範囲を任意のセルに指定します。
3：リストの範囲に、VLOOKUPで
　　部署名を参照して、担当コードを引き当てる
　　関数を入力します。
　　その際、部署名-1、部署名-2・・の
　　ように、枝番をつけて、ユニークにします。
4：～部の○担当は必ず一人という前提であれば、
　部署と担当コードを＆でくっつけたものを参照して
　VLOOKUPで引き当てるようにします。
以上が入力規則とVLOOKUPでやるやり方です。

具体的には、
既存の表の範囲をA1～D6とすると
部署名と担当コードの間に列を挿入
=B2&"-"&COUNTIF($B$2:B2,B2)
下までコピー
担当コードと担当者名の間に列を挿入
=B2&D2 （部署名と担当コードを結合）

2で&、設定したリストをH列とすると
H2に
=VLOOKUP(C2,$C$2:$D$6,2,0)
H3以下にコピー　・・・

担当名の表示セルには、
=VLOOKUP(部署名表示セル&担当コード表示セル,E2:F6,2,0)

お礼

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

回答No.1

kumikoara

確か、入力規則にif関数は使えなかったと思うので、入力規則での表示は、出来ないのではないでしょうか？

担当者コードが重複しているということですよね。
部署ごとに担当者コードと担当名のリストを作り、
担当名を返すセルでif関数とVLookUp関数を組み合わせて使用すれば、
部署名と担当者コードの合致したデータ（担当者名）が返ると思うのですが。。。
かなり長い式になるので、どうかな？？と思います。

本当は、担当者コードは担当名ごとに変えたほうが、式もシンプルになって良いですよね。
A001、B001というコード付けじゃダメなんでしょうか？？

補足

>本当は、担当者コードは担当名ごとに変えたほうが、
>式もシンプルになって良いですよね。
そうなんですよね。会社で決まってしまってるので
もうどうしようもないのですけど（T_T）。。

>かなり長い式になるので、どうかな？？と思います。
組み合わせパターンが200もあるのでつ、つらい。。
なせばなる、かしら。。。
気合入れてチャレンジしてみます。

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