選択した項目は表示しないリスト
次のようなことをしたいです。A列に入力されているのが「名前の一覧」です。この「名前の一覧」をF列に"3人分"入力します。このとき、毎回手入力するのではなく「入力規則のリスト」を設定して、リストから選択します。ただし、すでに選んだ名前は「リストに表示されない」ようにします。
これ、今から約20年ほど前に、確かネットの掲示板で質問されました。誰かが「それはマクロを使わないとできない」みたいに回答していたので、私が「いえいえ、関数だけでもできますよ」ってお答えした内容です。ただ、当時はExcel 2002かExcel 2003でした。現在(2022年)の最新版Excelは、さらに進化しています。なので、本コンテンツでは、Excel 2002までの機能で実現するやり方をご紹介するとともに、現在の最新版Excelだったらもっと簡単にできますよ、っていう両方を解説します。
古いExcelでやる方法
すみません、Excel 2002までの機能だけでもできるんですが、「テーブル」だけは使わせてください。テーブルは、Excel 2007で追加された機能なんですけど、テーブルを使うと、見た目的にスッキリするんです。
まずは、下図のようにレイアウトします。セル範囲A1:C8をテーブルにしてあります。
最初に考えるべきことは、A列の名前が「すでに選択されたかどうか」です。これは簡単ですね。A列の名前を、セル範囲F2:F4内で検索して、見つかったかどうかを調べればいいです。使うのはMATCH関数です。
セルF2とセルF3には、動作確認のために、適当な名前を手入力しています。ここにはまだ、入力規則のリストを設定していません。さて、B列に入力したMATCH関数では、見つかったとき"位置を示す数値"が返り、見つからなかったときエラーになります。今回は「見つからなかった名前」を後でリストアップしたいので、「見つからなかったかどうか」を調べます。つまり「MATCH関数がエラーになったかどうか」です。これにはISERROR関数を使います。
もし「MATCH関数がエラーだったら」そのときは、その数式が入力されている「行番号」を返します。使うのはROW関数です。もし「MATCH関数がエラーにならなかったら」つまり、すでに選択された名前だったら空欄を返すようにしました。
B列は、これで終わりです。次はC列の数式です。C列は「もしB列が数値だったら、そのセルを参照する」という仕組みを考えます。今回だったら、次のように参照できればいいです。
このとき、C列で参照する「A列の行番号」は、B列に表示されている数値を、小さい順に並べたものです。
数値を小さい順番で調べるには、SMALL関数を使います。
SMALL関数の引数で指定している「1・2・3…」というのは、それぞれの数式が入力されているセルの「行番号から1を引いた数」です。ここでもROW関数が重宝します。
これで、B列の数値を、小さい順番で取り出せましたので、アルファベットの"A"と結合してアドレスを作ります。さらに、そのアドレスを参照するためにINDIRECT関数を使います。
このあと、セル範囲F2:F4に設定する入力規則で、このC列をリスト表示します。数式がエラーになっていると見苦しいですから、エラーは表示しないようにします。
ごめんなさい！本当はIFERROR関数もExcel 2007で追加されたので、ちょっとルール違反なのですけど、まぁ大目に見てください。やろうと思えば、IF関数とISERROR関数でもできます。ちなみに、B列の数式では、IFERROR関数を使えません。理由は、各自で考えてみてください。
これで、B列とC列の数式が完成しました。最後に、このC列を入力規則のリストに設定します。このとき使うのがOFFSET関数やCOUNTA関数です。OFFSET関数に関しては、下記のページで詳しく解説していますので、自信のない方はご覧ください。特に、動画は超オススメです。
さて、最後に入力規則を設定しましょう。この数式は、パソコン通信時代からの鉄板です。
[元の値]ボックスに指定したのは、次のような数式です。
=OFFSET($C$2,0,0,COUNTA($C$2:$C$8),1)
さて、実際に操作してみましょう。まずは、セルF2のリストです。
ちゃんと、すべての名前がリストアップされていますね。ここでは、試しに"豊崎"を選択してみます。さあ、続いてひとつ下のセルF3でリストを開いてみます。
最初に選択した"豊崎"は、リストに表示されていません。成功です。でも、ちょっと待ってください。リストの一番下に空欄が表示されています。これは、セルC8のことです。もちろん、これでも動作的には問題ありません。でも、できれば、この空欄も表示したくないです。なので、次のようにします。
指定したのは、この数式です。
=OFFSET($C$2,0,0,COUNTA($C$2:$C$8)-COUNTBLANK($C$2:$C$8),1)
OFFSET関数で指定したセル範囲の大きさ(行数)は、COUNTA($C$2:$C$8)ですから「7」です。しかし、最初の1件(今回は"豊崎")を選択した結果、一番下のセルC8がひとつ空欄になりました。このときは、範囲の大きさ(行数)を「6」にしたいです。この「6」というのは、データの件数(COUNTA関数の結果)から「1」を引いた数です。じゃ、この「1」とは何かといえば、これはセル範囲C2:C8の中にある「ブランクセル(空欄セル)の数」です。それを調べるのがCOUNTBLANK関数です。実は、今回テーブルを使いたかった理由は、ここです。もし、テーブルを使っていなかったら、ここでCOUNTBLANK関数が使えません。そうなると、セルF3やセルF4に表示されるリスト内に、空欄が表示されてしまいます。テーブルにしておかないと、なぜCOUNTBLANK関数が使えないのかは、ご自身で考えてみてください。まぁ、本当を言えば方法はあります。ありますけど、難易度が256倍上がりますので、やりたくないです。だって、テーブルにすれば済む話なのですから。
現在のExcelでやる方法
上記のやり方を、パッと思いつくようなら、あなたはかなりワークシート関数に精通していると思います。でも、面倒くさいですｗ。特に最後の方は、もうほとんど"頭の体操"的な発想です。もちろん、いくつかの制約もあります。さて、では最新のExcelではどうでしょう。拍子抜けするほど簡単です。使うのは、たった3つの関数だけです。
まずは、元データなどをテーブルにするのですが、ここがポイントです。
テーブルにするのは、A列とB列だけです。今回も考え方は同じです。B列で「選択されたかどうか」を判定して、C列に「入力規則に設定する名前のリスト」を作成します。このうち、最終的に作成する名前の列は、テーブルに含めません。ちなみに、テーブルの名前は「Data」としました。では、B列に数式を入力しましょう。まずはMATCH関数です。
これで終わりです。今回は、とにかく「すでに選択されているかどうか」が区別できればいいです。エラーになっていてもかまいません。続いてC列の数式です。ここで登場するのがFILTER関数です。要するに今回やりたいことは、A列とB列からなるテーブルのうち「B列がエラーであるA列」だけをフィルタしてやればいいです。オートフィルタみたいにイメージしてください。
FILTER関数の条件は「B列がエラーである」です。セル範囲F2:F4で「まだ選択されていない」名前は、MATCH関数で調べるとエラーになります。エラーかどうかはISERROR関数で判定します。本来であれば「=FILTER(Data[名前],ISERROR(Data[チェック])=TRUE,"")」みたく書くのがセオリーですが、ここは数式を短くしたいので省略形で書きました。なお、FILTER関数は、テーブル内では使えません。だから、C列はテーブルに含めなかったです。
最後に、C列で作成した名前のリストを、条件付き書式に設定します。これも超簡単です。
C列には、何件の名前が表示されるか分かりません。だから、上記の古いExcelでは、CONTA関数で数をカウントしたり、そこからCOUNTBLANK関数で空欄セルを差し引いたり、そうして取得した行数をOFFSET関数に指定して参照範囲を作成しました。でも、FILTER関数だったら、そんな手間は必要ありません。FILTER関数によってスピった範囲というのは「スピル範囲演算子」を使えば自動的に分かります。「=$C$2#」です。一応、動作を確認してみましょう。
OKです。これ実は、B列で判定せず、FILTER関数一発でも可能です。
まぁ、可能ですけど、ここは無理せずB列を作業セルとして使う方がいいでしょう。
いかがですか？FILTER関数って、超絶便利だと思いませんか？今のExcelに「スピル」が実装され、そのおかげでXLOOKUP関数など"スピルありき"の新しいワークシート関数が追加されました。確かにXLOOKUP関数は、おもしろいです。でも、VLOOKUP関数だって、今まで十分"良い仕事"をしてきました。そして、実務では、VLOOKUP関数で事足りるケースも多いです。話題性としては、XLOOKUP関数の方がインパクト強いですけど、FILTER関数のポテンシャルは半端ないです。これからのExcelでは、FILTER関数要チェック！です。