Excel入力規則リストの参照範囲指定方法について

質問

バージョンは2010です。

添付のように、D１セルに会社名を入力するとD３セルのリストが
その会社名の人だけになるように設定したいのですが
上手く出来ません。

宜しくお願いします。

回答No.4

bunjii · ベストアンサー

E1セルに次の式を入力し、必要数を右にコピーします。
=IFERROR(INDEX($B$1:$B$100,SUMPRODUCT(SMALL(($A$2:$A$100=$D$1)*ROW($A$2:$A$100)+($A$2:$A$100<>$D$1)*1000,COLUMN(A1))),1),"")
A列とB列の最大行を100としましたが実際の行数に合わせて変更してください。
D3のデータの入力規則でリストを選択し、元の値に上記の範囲を指定します。
D1の会社名に対応する氏名がE1から右に列記されますので、データの入力規則のリストが可変になります。

回答No.5

nishi6

縦に会社名、その横にその会社名所属の氏名を入力すれば、回答No.2さんが答えられている「エクセル技道場」に同じ事例があります。

ここでは、質問で示されている表をそのまま使って入力規則を定義してみます。会社名の入力が面倒なので、会社名、氏名とも入力規則を使ってみます。

この方法で定義してみると、会社別の表を定義するよりもデータのメンテナンスが楽に思えました。一番の利点は入力規則を知らなくても（定義しなくても）算式のコピーで済む点です。一回範囲名と入力規則を定義すれば、あとはデータの追加に伴い算式のコピーをするだけで手間がかかりません。考えてみると、会社別の表を作るのも案外大変かもしれません。

添付図を元に説明します。最終的にH4に会社名、H10に氏名を入力規則で定義します。

　セルC2:=IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")
　セルD2:=IF(COUNT(C:C)<ROW(A1),"",INDEX(A:A,SMALL(C:C,ROW(A1))))
　セルE2:=IF(A2=$H$4,ROW(),"")
　セルF2:=IF(COUNT(E:E)<ROW(A1),"",INDEX(B:B,SMALL(E:E,ROW(A1))))

　　４つのセルに入力したらデータ分下にコピーします。
　　C列、E列について、算式以外は入力しないでください。有効なデータ数を計算しています。

範囲名を定義します。まず、「会社名2」を定義します。
　数式タブ＞名前の管理＞新規作成で名前を「会社名2」、
　参照範囲を「=INDIRECT("$D2:$D"&COUNTIF($D:$D,"?*")+1)」

次に「氏名2」を定義します。
　数式タブ＞名前の管理＞新規作成で名前を「氏名2」、
　参照範囲を「=INDIRECT("$F2:$F"&COUNTIF($F:$F,"?*")+1)」

最後に入力規則を定義します。
H4の会社名は、入力値の種類を「リスト」、元の値を「=会社名2」、
H10の氏名は、入力値の種類を「リスト」、元の値を「=氏名2」
とします。

お礼

ご協力ありがとうございます！
私の力不足で即実行できませんでした。。
添付の画像を見るとムダが無く綺麗にまとまっていて
理想のリストになっているので、次にチャレンジしたいと思います！

回答No.3

tom04

こんばんは！
一例です。

↓の画像のように別Sheetにリスト候補を表示させます。
画像では右側がSheet2とします。

Sheet2のA1セルに
=IF(COUNTIF(Sheet1!A$1:A$1000,Sheet1!D$1)<ROW(A1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!A$1:A$1000=Sheet1!D$1,ROW(A$1:A$1000)),ROW(A1))))
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のA1セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
このA1セルをフィルハンドルで下へずぃ～～～！っとコピーしておきます。

そしてSheet1のD3セルを選択　→　データの入力規則　→　「リスト」を選択　→　「元の値」の欄に
　=OFFSET(Sheet2!A1,,,COUNTIF(Sheet2!A:A,"?*"))
という数式を入れOK

これで何とかご希望通りにならないでしょうか？m(_ _)m

お礼

ありがとうございます！
上手くいきました！
なのですがシートを増やしたくなかったのでベストアンサーできず申し訳ないです。。
ご協力有難うございました！

回答No.2

mshr1962

会社名毎の従業員リストを作れば可能です。

下記のサイトが参考になります。
・エクセル技道場
http://www2.odn.ne.jp/excel/waza/validation.html#SEC2

お礼

有難うございます！
私もこのHPを見たのですが実際の一覧では数社、1000名以上の名簿があり追加や削除が有る為、データ管理が難しく断念しました。。

回答No.1

aokii

その会社名の人の表に名前を付けてください。

お礼

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