[[20100508170500]]　『入力規則のリスト取得』（PAN）　ページの最後に飛ぶ

『入力規則のリスト取得』（PAN）
下記のような商品一覧表を元に、別のシートに入力規則を設定したいと考えております。

↓商品一覧表

分類1　分類2　商品名

野菜　キャベツ　新鮮キャベツ

野菜　キャベツ　新鮮キャベツ1/2

果物　リンゴ　紅玉

果物　イチゴ　あまおう

野菜　ハクサイ　1/4カット

↓入力シート

分類1　分類2　商品名　入荷日

上記入力シートの分類1、分類2、商品名を商品一覧表にあるデータをもとに、
分類1を”野菜”と入力すれば、分類2の候補として”キャベツ”および”ハクサイ”が、
更に分類2を”ハクサイ”と入力すれば、商品名の候補として”1/4カット”が出るようにしたいのです。

別の表を作成すれば出来るようですが、
商品の分類が多く、また随時更新される為維持が困難です。
良い方法をご存じの方、教えてください。
よろしくお願いいたします。
尚、OSはWindowsXP、エクセルのバージョンはExcel2007です。

 【商品一覧表シート】
	A	B	C	D	E	F	G
1	分類1	野菜	果物	キャベツ	ハクサイ	リンゴ	イチゴ
2	野菜	キャベツ	リンゴ	新鮮	1/2カット	紅玉	あまおう
3	果物	ハクサイ	イチゴ	新鮮1/2	1/4カット	王林	さちのか
						サンフジ	紅ほっぺ
 ◆それぞれ、2行目以下を1行目の名前をつけます
 ★たとえば、A2:A3の範囲を「分類1」という名前をつけます

 【入力シート】
	A	B	C	D
1	分類1	分類2	商品名	入荷日
2	野菜	ハクサイ	1/4カット	
3				
4

 １）入力シートの「分類1」の下の範囲を指定して、
 ２）リボン「データ」タブ→「データツール」グループ→「データの入力規則」ボタンの▼をクリックして、
 ３）「設定」の「入力値の種類」を「リスト」にして「元の値」に、　分類1　と入力して、ＯＫ
 ４）入力シートのA2:A10の範囲を指定して、
 ５）リボン「データ」タブ→「データツール」グループ→「データの入力規則」ボタンの▼をクリックして、
 ６）「設定」の「入力値の種類」を「リスト」にして「元の値」に、　分類1　と入力して、ＯＫ
 ７）B2:C10の範囲を指定して、
 ８）リボン「データ」タブ→「データツール」グループ→「データの入力規則」ボタンの▼をクリックして、
 ９）「設定」の「入力値の種類」を「リスト」にして「元の値」に、　=INDIRECT(A2)　と入力して、ＯＫ
 ◆これで、ご希望のようになっていると思います
(Maron)
、

Maron様

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

お示しいただいたように、商品一覧表シートを各分類の一覧表にできれば良いのですが、分類1・2、商品名の他にも商品ごとに関連したデータがあるため、商品一覧表のレイアウトを変更することができません。

現在の商品一覧表から自動で分類の一覧表ができるように。。。とも考えたのですが、良い方法が浮かばず、苦慮しております。

せめて入力規則のリストで、指定した範囲の中の重複したデータの削除ができれば良いのですが、何か方法はありますでしょうか。

よろしくお願いいたします。

（PAN）

 分類一覧表が膨大になるので、おすすめではありませんが、途中まで作ってみました。
 商品一覧表シート 
  A        B       C                                            G        H        I
 野菜	キャベツ	新鮮キャベツ				1	1	1
 野菜	キャベツ	新鮮キャベツ1/2						2
 果物	リンゴ	紅玉				         3	3	3
 果物	イチゴ	あまおう					         4	4
 野菜	ハクサイ	1/4カット					         5	5
 魚	いわし	1/2				         6	6	6
 G:Iは作業列(実際には離れた所でよい)
 G1:=IF($A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",ROW(A1)))
 右へI1までコピー
 下へ500行目までコピー
 入力シート
　「分類の一覧表(リスト)」は同じシートにないと面倒なので、入力シート内に作ります。
　A         B      C                                    G        H       I
 果物	イチゴ					野菜	果物	魚
 魚	いわし					キャベツ	リンゴ	いわし
 魚	いわし					ハクサイ	イチゴ	
 野菜	ハクサイ

 第１分類はG1:Z1(２０件)
 第２分類はG2:Z52(５０件×２０)

 A1:データ、入力規則、リストとして、元の値「=$G$1:INDEX($G$1:$Z$1,20-COUNTBLANK($G$1:$Z$1))」
 B1:データ、入力規則、リストとして、元の値
 「=INDEX($G$2:$Z$2,MATCH($A1,$G$1:$Z$1,0)):INDEX(INDEX($G$2:$Z$2,MATCH($A1,$G$1:$Z$1,0)):INDEX($G$51:$Z$51,MATCH($A1,$G$1:$Z$1,0)),50-COUNTBLANK(INDEX($G$2:$Z$2,MATCH($A1,$G$1:$Z$1,0)):INDEX($G$51:$Z$51,MATCH($A1,$G$1:$Z$1,0))))」

 第２分類までですが、第３分類までやろうとすると、膨大なセル範囲を用意する必要があります。

 重大な忘れもの
 入力シートの一覧表部分
 G1:=IF(COUNT(一覧表!$G$1:$G$500)<COLUMN(A1),"",INDEX(一覧表!$A$1:$A$500,SMALL(一覧表!$G$1:$G$500,COLUMN(A1))))
 Z1までコピーします。
 G2:{=IF(COUNT(IF(一覧表!$A$1:$A$500=入力シート!G$1,一覧表!$H$1:$H$500,""))<ROW(G1),"",INDEX(一覧表!$B$1:$B$500,SMALL(IF(一覧表!$A$1:$A$500=入力シート!G$1,一覧表!$H$1:$H$500,""),ROW(G1))))}
 「SHIFT]+[Ctrl]+[Enter]で確定。
 Z2まで右にコピー
 51行まで下にコピー

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

第2分類まででも十分です。

組まれている内容をよく理解し、自分でも工夫できたら。。。

と思っております。

またの機会がありましたら、よろしくお願いいたします。
（PAN)

 こんなソフトがピッタリかも。
http://www.vector.co.jp/soft/win95/business/se385991.html

 第２分類までうまく作動していますか。
 第３分類まで作りましたので提示します。
 リストの範囲G61:Q560(第２分類５００件、第３分類１０件×５００)
 G61:=IF(COUNT(一覧表!$H$1:$H$500,"")<ROW(G1),"",INDEX(一覧表!$B$1:$B$500,SMALL(一覧表!$H$1:$H$500,ROW(G1))))
 G560までコピー
 H61:{=IF(COUNT(IF(一覧表!$B$1:$B$500=入力シート!$G61,ROW(一覧表!$B$1:$B$500),""))<COLUMN(A1),"",INDEX(一覧表!$C$1:$C$500,SMALL(IF(一覧表!$B$1:$B$500=入力シート!$G61,ROW(一覧表!$B$1:$B$500),""),COLUMN(A1))))}
 「SHIFT]+[Ctrl]+[Enter]で確定。
 Q61まで右にコピー
 560行まで下にコピー
 C1:データ、入力規則、リストとして、元の値
 =INDEX($H$61:$H$560,MATCH($B1,$G$61:$G$560,0)):INDEX(INDEX($H$61:$H$560,MATCH($B1,$G$61:$G$560,0)):INDEX($Q$61:$Q$560,MATCH($B1,$G$61:$G$560,0)),10-COUNTBLANK(INDEX($H$61:$H$560,MATCH($B1,$G$61:$G$560,0)):INDEX($Q$61:$Q$560,MATCH($B1,$G$61:$G$560,0))))

 補足　　一覧表作業列I列は不要クリアしてください。

 注意1(重要)
 一覧表の元の入力データの修正について
 入力シートに展開しているリストデータは一覧表の元のデータに基づいています。
 元データは修正追加があります、ある項目がなくなってその分上に詰めるということがありますが、
 この場合「削除上方向にシフト 」としますと、削除したセルがなくなって、「エラー」となります。
 これを避けるためには、本当の元の入力データシートは別に作り(これは削除修正自由)修正が終わったら、コピーして入力シートに貼り付けます。

コメント返信：

[ 一覧(最新更新順) ]

[[20100508170500]] 『入力規則のリスト取得』（PAN） ページの最後に飛ぶ

[[20100508170500]]　『入力規則のリスト取得』（PAN）　ページの最後に飛ぶ