Excel 入力規則・3段階で連動して変化するリスト

●Sheet1に元データ[対応表]があったとします。

品物	種類	産地
なし	20世紀	島根
なし	20世紀	鳥取
なし	20世紀	山梨
なし	21世紀	青森
なし	22世紀	鳥取
なし	22世紀	和歌山
なし	22世紀	山梨
みかん	温州	鳥取
みかん	温州	高知
みかん	ハウス	和歌山
みかん	ハウス	高知
みかん	ハウス	静岡
みかん	ハウス	愛媛
りんご	ジョナ	山口
りんご	ジョナ	山梨
りんご	ふじ	高知
りんご	ふじ	山口
りんご	ふじ	秋田
りんご	ふじ	青森

●Sheet2で...
A2をクリックすると

A2でりんごを選択した場合,B2は

A2がりんご,B2がふじの場合,C2は

上のように、3段階で動的に変化する入力規則：リスト

の作り方です。2段階ならあちこちのサイトに方法が書かれていますが、

3段階となると難易度が上がります。

Q&Aサイトのmougでの質問に回答したもので、

ピボットテーブルを使う方法です。

赤字の部分に注意していただくと

わかりやすいかもしれません。

《手順１》
Sheet1のデータ範囲を選択
[2003の場合]

データ→リストの作成→先頭行を～にチェック→OK
[2007,2010の場合]

テーブルとして書式設定→先頭行を～にチェック→OK
　※今後のデータ増加に備えるためです。

《手順2》
Sheet1のデータ範囲を選択したまま

→データ→ピボットテーブル～→完了[OK]

→できたシートの名をPivoに変更

→以下のような配置に。具体的には...

ピボットテーブル1つ目。

行ラベルに最初の「品物」をドラッグします。

左上の[品物]がA4にくるようにしてください。

　※[2007,2010の場合]デザイン→レイアウト→必ず表形式に変更

品物

なし

みかん

りんご

総計

ピボットテーブル1をコピーしてピボットテーブル2つ目に。

左上の[品物]がH4にくるようにしてください。

行ラベルに2番目の「種類」をドラッグして追加します。

品物	種類
なし	20世紀
	21世紀
	22世紀
なし 集計
みかん	ハウス
	温州
みかん 集計
りんご	ジョナ
	ふじ
りんご 集計
総計

ピボットテーブル2をコピーしてピボットテーブル3つ目に。

左上の[品物]がP4にくるようにしてください。

行ラベルに3番目の「産地」をドラッグして追加します。

品物	種類	産地
なし	20世紀	山梨
		鳥取
		島根
	20世紀 集計
	21世紀	青森
	21世紀 集計
	22世紀	山梨
		鳥取
		和歌山
	22世紀 集計
なし 集計
みかん	ハウス	高知
		山梨
		秋田
		和歌山
	ハウス 集計
	温州	高知
		鳥取
	温州 集計
みかん 集計
りんご	ジョナ	山口
		山梨
	ジョナ 集計
	ふじ	高知
		山口
		秋田
		青森
	ふじ 集計
りんご 集計
総計

《手順3》

Sheet2のC2を選択した状態で
[2003の場合]挿入→名前→定義
[2007,2010の場合]数式→名前の定義
名前：　品物
参照範囲：　=Pivo!$A$5:INDEX(Pivo!$A:$A,MATCH("総計",Pivo!$A:$A,0)-1)

名前：　種類
参照範囲：　=INDEX(Pivo!$I:$I,MATCH(!B2,Pivo!$H:$H,0)):INDEX(Pivo!$I:$I,MATCH(!B2&" 集計",Pivo!$H:$H,0)-1)

名前：　産地
参照範囲： =INDEX(Pivo!$R:$R,MATCH(!A2,Pivo!$P:$P,0)+MATCH(!B2,INDEX(Pivo!$Q:$Q,MATCH(!A2,Pivo!$P:$P,0)):Pivo!$Q$2000,0)-1):INDEX(Pivo!$R:$R,MATCH(!A2,Pivo!$P:$P,0)+MATCH(!B2&" 集計",INDEX(Pivo!$Q:$Q,MATCH(!A2,Pivo!$P:$P,0)):Pivo!$Q$2000,0)-2)

《手順4》
Sheet2で
A2を選択した状態で
データ→入力規則:リスト▼:元の値：
=品物
B2を選択した状態で
データ→入力規則:リスト▼:元の値：
=種類
C2を選択した状態で
データ→入力規則:リスト▼:元の値：
=産地

　※ふう。これで完成です。

《手順5》

Sheet1のデータが追加されたら

Pivoシートのどれかひとつのピボットテーブルを選択してデータの更新。