可変リスト 2009.02.06 更新：2016.04.20
ここでは関数と入力規則の「リスト」を組み合わせ、データに応じてリスト項目が増減する「可変リスト」を作っていきます。尚、Excel2007でリストを作成した場合、下記「可変リスト（１）」の前半部と類似の機能（余分な空白を表示しない）が追加されています。
サンプルファイル「可変リスト」 ダウンロード
2016/04/20に更新 【重要】
Excelの仕様の変更により、以前紹介した、入力規則に数式を入力する方法では警告が出て、数式を設定できなくなりました。それを回避する方法を新しく記載しました。
書籍紹介本を執筆しました
好評販売中！
「教師のExcel ～校務（個人業務＋チーム業務）カイゼンのためのデジタルリテラシー～」
基本の学びなおしから校務をサポートするシステム作りまで（技術評論社）
教員みんなができる「チーム業務」のポイントを丁寧にわかりやすく解説しています。また「個人業務」を更に効率化するための学び直しと応用テクニックを解説しています。
この書籍の活用してもらうことで、先生方が児童・生徒に向き合う時間を、更に確保できることを願っています！
■ 詳細（技術評論社） ■ 内容紹介：note ■ 購入：amazon
入力規則のリスト
入力規則の「リスト」は、値を設定したり、セル・セル範囲参照することにより、ドロップダウンリストからデータを選択する機能です。データの誤入力予防に役立ちます。
セル範囲を参照の場合、対象範囲内のデータ量が固定されておれば、その範囲を指定すればよいのですが、データ量が増減する場合はどうしたらよいでしょうか？
「最大データ量にあわせセル範囲を指定するのでは？」確かにこれなら、データ量が増減してもリストに全てが反映されます。
しかし、課題も残ります。それは、指定したセル範囲に空白があると、その空白もそのままリストに反映されてしまうことです。
※空白が反映されたリスト例
その空白をなくし、データの増減に対応しリスト項目も増減するリストを、ここでは「可変リスト」と呼び、作成していきます。
可変リスト（１）
下図のセル範囲[B3:B12]を可変リストの対象にします。
「数式」タブ→「名前の管理」より、名前を新規に定義します。（今回は「List1」と定義）
「参照範囲」に次の数式を設定します。
=$B$3:INDEX($B$3:$B$12,COUNTA($B$3:$B$12)) （シート名は自動で追記されます）
次に「データ」→「入力規則」→「入力値の種類」をリストに設定し、「元の値」に先ほど定義した名前を設定します。
【数式の解説】
１．COUNTA関数で空白以外のセルをカウントしデータ数を数える。
２．カウントした数をINDEX関数「セル範囲形式」の「セルの参照を返す」性質と
組み合わせる。
この仕組みで、データの個数に合わせリスト内の値を増減させます。
この方法は簡単ですが、課題もあります。それは、データ間に空白がある場合にリストが上手く反映されない事です。
それを防ぐため、データ入力範囲（セル範囲[B3:B12]）に入力規則を設定し、「空白をまたいでの入力を禁止」にします。
１．セル[B3]を選択し「データ」→「入力規則」で「データの入力規則」ダイアログボックスを開く。
２．「入力値の種類」を「ユーザー設定」にする。
３．「数式」を指定。=COUNTIF($B$2:B2,"")=0
４．セル[B3]をコピーし、セル範囲[B3:B12]を選択し「右クリック」→
「形式を選択して貼り付け」→「入力規則」チェック→「OK」
このように設定することで、入力対象セルより上に空白がある場合は入力を禁止にできます。
しかし、既に存在するデータの削除によりデータ間に空白セルが出来た場合には、リストの値はうまく反映されなくなります。次はその課題への対応です。
可変リスト（２）
ここでは、可変リスト（１）での課題である「データ間の空白対応」を考えていきます。
解決には「データの個数のカウント」と「（空白があった場合）データの間を詰める」の組み合わせが考えられますので、新たに作業セル範囲を使用しそれらの作成していきます。
準備
１．氏名欄の隣に作業列１と２を作ります。
２．氏名欄に「Per_Data」、作業列１のセル範囲[C3:C12]に「Num_Data」と
名前を定義します。（名前は自由で構いません。ここでは上記の名前で行います）
データの個数のカウント
下の数式は、氏名欄（セル範囲[Per_Data]）の空白以外のセルをカウントするものです。
=COUNTA(Per_Data)
データ間を詰める（１）
データに空白がある場合、間を詰めるための第１段階として、作業列１に次の数式を挿入します。
=IF(B3="","",A3)
これは、「氏名が入力されていたら作業列１に対応番号（通番）を表示」するものです。
データ間を詰める（２）
第２段階として、作業列２に数式を挿入します。
=IF(COUNTA(Per_Data)< A3,"",INDEX(Per_Data,SMALL(Num_Data,A3)))
【数式の解説】
１．SMALL関数で、セル範囲[Num_Data]の中の△番目に小さい値を取り出す
２．上記の値を元にINDEX関数で該当の人物を表示する
３．△番目の値がデータ個数を超えるとエラーになるため、IF関数とCOUNTA関数で
エラーを回避
名前を定義
「数式」→「名前の管理」より、名前を新規に定義します。（今回は「List2」と定義）
「参照範囲」に次の数式を設定します。
=$D$3:INDEX($D$3:$D$12,COUNTA($B$3:$B$12)) （シート名は自動で追記されます）
可変リスト（１）と異なり、作業列２のセル[D3:D12]が範囲になります。
入力規則の設定
最後に入力規則です。「データ」→「入力規則」→「入力値の種類」をリストに設定し、「元の値」に先ほど定義した名前を設定します。
可変リスト完成
これでデータ間の空白に対応した可変リストが完成しました。
サンプルファイルでは、更に重複データの入力を禁止しています。興味があれば見て下さい。
手軽さでは「可変リスト（１）」ですが、確実性を増したい場合は「可変リスト（２）」が良いでしょう。状況に応じて使い分けましょう。
サンプルファイル
これまで説明したサンプルファイルを用意しました。サンプルファイルは「仕組みの理解」に重点を置いているため実用的な作りにはなっていません。自分なりに改変してご使用下さい。尚、ファイルによる問題が生じないよう気をつけておりますが、万が一、本ファイルの使用によって損害等が生じたとしても、責任は取りませんので、悪しからずご了承下さい。
サンプルファイル「可変リスト」 ダウンロード
