wrote :: 2006.04.06

トップページ > Excel > 機能と操作 > 選択した項目は表示しないリスト

選択した項目は表示しないリスト

某Q&Aで質問されていたネタです。
関数を使ったやり方をアップしたのですが、よく見たらそこはVBAの掲示板でした(^^; で、せっかくなのでこちらに掲載しておきます。

図のように、5人の名前を入力しておき、この名前をリストで選択します。リストを使って、5人の中から4人を選ぶわけですが、すでに「山田」が選ばれているときは、それ以降のリストに「山田」を表示したくありません。すでに選んだ名前は以降のリストから消したいんです。下の図のように。



この機能を実現するには次のステップが必要です。
非表示になっているB列とC列でこの処理を行い、その結果をセル範囲E2:E5にリストとして表示します。リストは「入力規則」を使います。

■すでに選択された名前かどうか判定する
たとえば「山田」がすでに選択されているかどうかは、「山田」(セルA3)がセル範囲E2:E5内に存在するかどうかで判定できます。これにはMATCH関数を使います。セルE2に「山田」が入力されていると、

=MATCH(A3,E2:E5)

は「1」を返します。セル範囲E2:E5に「山田」が入力されていないときはエラーになります。ここでは「入力されていなかったらリストに表示」したいのですから、MATCH関数がエラーかどうかを判定します。そうISERROR関数です。

=IF(ISERROR(MATCH(A3,E2:E5,0)),"入力されていない","")

入力されていなかったら、「山田」(セルA3)の行数を返すようにします。行数を返すのはROW関数ですね。ここでは、この作業をB列で行いましょう。そうすれば、ROW関数の引数を省略できます。

=IF(ISERROR(MATCH(A3,E2:E5,0)),ROW(),"")

セル範囲E2:E5のアドレスを絶対参照にしてから、上記の式をセル範囲B1:B5に入力します。どれか1つに入力してから他のセルにコピーしてください。



■選択されていない名前だけのリストを作る
すでに選択された名前はB列に行数が表示されません。B列に行数が表示されているセルが「まだ選択されていない」＝「リストに表示する」名前になります。

どの名前を表示するかというと、A列の「1行目」「2行目」「4行目」「5行目」にあるセルです。アドレスでいえば「A1」「A2」「A4」「A5」になりますね。ここで注目したいのは、「1行目」の「1」はセル範囲B1:B5内で「1番小さい数字」ということです。同様に「2行目」の「2」は「2番目に小さい数字」、「4行目」の「4」は「3番目に小さい数字」、「5行目」の「5」は「4番目に小さい数字」です。わかりますか？この理屈こそが今回行う処理で最大のポイントになります。

数字を小さい順に取り出すのはSMALL関数です。セル範囲C1:C5に次のような式を入力してみましょう。

=SMALL($B$1:$B$5,ROW())



これで、空欄を除いた連続したセルに変換できました。実際に参照したいセルはA列なので、文字列の「A」と合体させてやります。

="A"&SMALL($B$1:$B$5,ROW())



文字列として作成したアドレスを実際に参照できるようにするには、必殺技INDIRECT関数の出番です。INDIRECT関数に関してはこちら(INDIRECT関数)をご覧ください。

=INDIRECT("A"&SMALL($B$1:$B$5,ROW()))



すでに選択されている名前は式がエラーになります。これでもいいのですが、次の処理に備えて「エラーだったら空欄を返す」ようにします。式が長くなりますけどがんばってくださいね。

=IF(ISERROR(INDIRECT("A"&SMALL($B$1:$B$5,ROW()))),"",INDIRECT("A"&SMALL($B$1:$B$5,ROW())))



■データに応じてリストに表示する名前を変更する
最後のステップです。
セル範囲E2:E5のリストには何を表示すればいいのでしょう。上図のように、1つの名前が使われていたときは「C1:C4」をリストとして表示したいんです。もし2つの名前が使われていたら、C列に名前が3つ表示されているはずですから「C1:C3」をリストに表示します。以上のことからリストに表示する範囲は次のように決められます。
最後はいきなり式にまとめちゃいましたけど(^^; 要するに、セル範囲を可変にするOFFSET関数を使うわけです。OFFSET関数についてはこちら(OFFSET関数)をご覧ください。

で、この式をどこで使うかというと、入力規則でリストに表示するセル範囲を指定する「元の値」ボックスです。セル範囲E2:E5を選択して[データ]-[入力規則]を実行します。表示される[データの入力規則]ダイアログボックスの[設定]タブで、[入力値の種類]→「リスト」を選択し、[元の値]ボックスに上記の式「=OFFSET($C$1,0,0,5-COUNTBLANK($C$1:$C$5),1)」を入力します。[OK]ボタンをクリックすれば完成です。



最後に、作業セルとして使ったB列とC列を非表示にすれば完了です。実際に設定したブックを用意しましたのでご覧ください。こちらをクリックするとダウンロードできます。→Sample27.xls リンクを右クリックして[対象をファイルに保存する]を実行してください。

[目次]へ戻る