Practical Excel
応用:可変リストの作成

【当サイト人気ソフト】
ナンプレ無双:問題作成・解析・印刷・プレイができる無料ナンプレソフト。雑誌掲載多数

可変リスト

 ここでは関数と入力規則の「リスト」を組み合わせ、データに応じてリスト項目が増減する「可変リスト」を作っていきます。尚、Excel2007でリストを作成した場合、下記「可変リスト(1)」の前半部と類似の機能(余分な空白を表示しない)が追加されています。

 ここで紹介する可変リストのサンプルファイルはダウンロード出来ます。

 2016/04/20に更新 【重要】
 Excelの仕様の変更により、以前紹介した、入力規則に数式を入力する方法では警告が出て、数式を設定できなくなりました。それを回避する方法を新しく記載しました。

入力規則のリスト

 入力規則の「リスト」は、値を設定したり、セル・セル範囲参照することにより、ドロップダウンリストからデータを選択する機能です。データの誤入力予防に役立ちます。

 セル範囲を参照の場合、対象範囲内のデータ量が固定されておれば、その範囲を指定すればよいのですが、データ量が増減する場合はどうしたらよいでしょうか?
 「最大データ量にあわせセル範囲を指定するのでは?」確かにこれなら、データ量が増減してもリストに全てが反映されます。

 しかし、課題も残ります。それは、指定したセル範囲に空白があると、その空白もそのままリストに反映されてしまうことです。

※空白が反映されたリスト例

 その空白をなくし、データの増減に対応しリスト項目も増減するリストを、ここでは「可変リスト」と呼び、作成していきます。

可変リスト(1)

 下図のセル範囲[B3:B12]を可変リストの対象にします。


 「数式」→「名前の管理」より、名前を新規に定義します。(今回は「List1」と定義)
 「参照範囲」に次の数式を設定します。
 =$B$3:INDEX($B$3:$B$12,COUNTA($B$3:$B$12)) (シート名は自動で追記されます)


 次に「データ」→「入力規則」→「入力値の種類」をリストに設定し、「元の値」に先ほど定義した名前を設定します。


 【数式の解説】
  1.COUNTA関数で空白以外のセルをカウントしデータ数を数える。
  2.カウントした数をINDEX関数「セル範囲形式」の「セルの参照を返す」性質と
    組み合わせる。
 この仕組みで、データの個数に合わせリスト内の値を増減させます。

 この方法は簡単ですが、課題もあります。それは、データ間に空白がある場合にリストが上手く反映されない事です。

 それを防ぐため、データ入力範囲(セル範囲[B3:B12])に入力規則を設定し、「空白をまたいでの入力を禁止」にします。

 1.セル[B3]を選択し「データ」→「入力規則」で「データの入力規則」ダイアログ
   ボックスを開く。
 2.「入力値の種類」を「ユーザー設定」にする。
 3.「数式」を指定。=COUNTIF($B$2:B2,"")=0
 4.セル[B3]をコピーし、セル範囲[B3:B12]を選択し「右クリック」→
       「形式を選択して貼り付け」→「入力規則」チェック→「OK」



 このように設定することで、入力対象セルより上に空白がある場合は入力を禁止にできます。

 しかし、既に存在するデータの削除によりデータ間に空白セルが出来た場合には、リストの値はうまく反映されなくなります。次はその課題への対応です。


可変リスト(2)

 ここでは、可変リスト(1)での課題である「データ間の空白対応」を考えていきます。
 解決には「データの個数のカウント」と「(空白があった場合)データの間を詰める」の組み合わせが考えられますので、新たに作業セル範囲を使用しそれらの作成していきます。

◆準備
 1.氏名欄の隣に作業列1と2を作ります。
 2.氏名欄に「Per_Data」、作業列1のセル範囲[C3:C12]に「Num_Data」と
   名前を定義します。(名前は自由で構いません。ここでは上記の名前で行います)


◆データの個数のカウント
 下の数式は、氏名欄(セル範囲[Per_Data])の空白以外のセルをカウントするものです。
 =COUNTA(Per_Data)

◆データ間を詰める(1)
 データに空白がある場合、間を詰めるための第1段階として、作業列1に次の数式を挿入します。
 =IF(B3="","",A3)

 これは、「氏名が入力されていたら作業列1に対応番号(通番)を表示」するものです。


◆データ間を詰める(2)
 第2段階として、作業列2に数式を挿入します。
 =IF(COUNTA(Per_Data)< A3,"",INDEX(Per_Data,SMALL(Num_Data,A3)))

【数式の解説】
 1.SMALL関数で、セル範囲[Num_Data]の中の△番目に小さい値を取り出す
 2.上記の値を元にINDEX関数で該当の人物を表示する
 3.△番目の値がデータ個数を超えるとエラーになるため、IF関数とCOUNTA関数で
   エラーを回避


◆名前を定義
 「数式」→「名前の管理」より、名前を新規に定義します。(今回は「List2」と定義)
 「参照範囲」に次の数式を設定します。
 =$D$3:INDEX($D$3:$D$12,COUNTA($B$3:$B$12)) (シート名は自動で追記されます)
 可変リスト(1)と異なり、作業列2のセル[D3:D12]が範囲になります。


◆入力規則の設定
 最後に入力規則です。「データ」→「入力規則」→「入力値の種類」をリストに設定し、「元の値」に先ほど定義した名前を設定します。


◆可変リスト完成
 これでデータ間の空白に対応した可変リストが完成しました。
 サンプルファイルでは、更に重複データの入力を禁止しています。興味があれば見て下さい。



 手軽さでは「可変リスト(1)」ですが、確実性を増したい場合は「可変リスト(2)」が良いでしょう。状況に応じて使い分けましょう。


サンプルファイル

 これまで説明したサンプルファイルを用意しました。サンプルファイルは「仕組みの理解」に重点を置いているため実用的な作りにはなっていません。自分なりに改変してご使用下さい。尚、ファイルによる問題が生じないよう気をつけておりますが、万が一、本ファイルの使用によって損害等が生じたとしても、責任は取りませんので、悪しからずご了承下さい。

サンプルファイル「可変リスト」・・・[sample_list.xls] 24kb

Excel Tips for Teachers

Copyright (C) 2009 坂江 保 All Rights Reserved.