表を利用した名前の定義・入力規則設定について

質問

はじめまして。

エクセルの入力規則・名前の定義でどうしても分からない事が
ありましたので、教えて頂けますでしょうか？

【前提】以下のような表が作成されているとします。
Ａ列　　　Ｂ列
果物　　　りんご
果物　　　みかん
果物　　　いちご
野菜　　　ピーマン
野菜　　　にんじん
野菜　　　なす
その他　　肉
その他　　魚

【要望】
セルＣ１に、果物のみ（「りんご」「みかん」「いちご」）が表示
されたドロップダウンリストを作成します。

同様に、セルＤ１に、その他のみ（「肉」「魚」）が表示
されたドロップダウンリストを作成します。

【質問】
要望を満たす為に、入力規則を設定したいのですが、
　＞前提にある表の中から、
　＞Ａ列が果物であるデータだけを取り出し、
　＞そのＢ列をリストの元の値とする
とする方法が分かりません。

「りんご」「みかん」「いちご」だけを選択し、名前定義をする、
という方法もあると思いますが、
そうではなく、あくまで「果物」というキーを指定して
名前を定義したいのです。

以上、よろしくお願いします。

回答No.1

ham_kamo · ベストアンサー

ちょっとややこしくなってしまいましたが、以下の通りに試してみてください。

まず、作業用としてSheet2を使います。
Sheet2のA1に、
=IF(Sheet1!A1="果物",ROW(),"")
と入力して、Sheet1の行数分下にコピーしてください。

次にSheet2のB1に
=IF(COUNT($A:$A)<ROW(A1),"",INDEX(Sheet1!$B:$B,SMALL($A:$A,ROW(A1)),COLUMN(A1)))
という式を入力し、下にコピーしてください。果物一覧ができるはずです。

次に「挿入」＞「名前」＞「定義」から、名前に「果物」と入力し、参照範囲に以下の式を入力してください。
=OFFSET(Sheet2!$B$1,0,0,COUNT(Sheet2!$A:$A),1)

Sheet1のＣ列全体を選択し、「データ」＞「入力規則」で「入力値の種類」をリストにし、「元の値」に　=果物 と入力してください。

これでＣ列に果物だけをドロップダウンで入力できます。

同様に「その他」の場合です。（野菜はいらないのかな？）

Sheet2のC1に、
=IF(Sheet1!A1="その他",ROW(),"")
と入力して、Sheet1の行数分下にコピー

Sheet2のD1に
=IF(COUNT($C:$C)<ROW(A1),"",INDEX(Sheet1!$B:$B,SMALL($C:$C,ROW(A1)),COLUMN(A1)))
を入力し、下にコピー。

次に「その他」という名前を以下の参照範囲で登録
=OFFSET(Sheet2!$D$1,0,0,COUNT(Sheet2!$C:$C),1)

Ｄ列の入力規則をリストにして =その他 に指定。

回答No.2

maron--5

◆こんな方法もありますよ
　　　A　　　B　　　　　C　　　　D　　　　　E
1　　区分　　品名　　　果物　　　野菜　　　その他
2　　果物　　りんご　　りんご　　ピーマン　肉
3　　果物　　みかん　　みかん　　にんじん　魚
4　　果物　　いちご　　いちご　　なす
5　　野菜　　ピーマン
6　　野菜　　にんじん
7　　野菜　　なす
8　　その他　肉
9　　その他　魚

C2=IF(ROW(A1)>COUNTIF($A$2:$A$10,C$1),"",INDEX($B$1:$B$10,SMALL(INDEX(SUBSTITUTE(($A$2:$A$10=C$1)*1,0,10^5)*ROW($A$2:$A$10),),ROW(A1))))

★右・下にコピー