入力規則のリストに"重複しないリスト"を表示する
2023年4月から、Office TANAKA主催のセミナーをリニューアルして、新しく「ワークシート関数」のセミナーを3本始めました。詳しくは、下記ページをご覧ください。
さて、先日「ワークシート関数セミナー3 ～スピルと新しい関数たち～」を実施したのですが、前日にあらためて、"スピルありき"で追加された新しい関数の動作確認をしていました。そしたら！なんと驚きの使い方を発見したので解説します。これ、実務では、けっこう求められる仕組みです。
どういう話かというと「入力規則のリスト」に関する内容です。この機能は、とても便利なので、活用している方も多いことでしょう。
リストに表示するデータが、シート上に入力されているのでしたら、こんな感じです。
めっちゃ便利な仕組みなので、実務では多くの場面で利用されています。ただ、そうなると、いろいろと「もっとこうしたい」という要望も増えてきます。入力規則のリストに関して、よく聞く要望は、おおむね次の2つです。
- 元データの数を可変にしたい
- 重複しないリストを表示したい
1.に関しては簡単です。元データの範囲をテーブルにしておくだけで解決です。
さて、問題は2.です。これは少々やっかいですね。次のような結果にしたいです。
リストが二つ並んでいると見にくいので、C列のセルに入力規則のリストを設定しました。見た目の問題です。深い理由はありません。さらに、最初からきちんと想定して書き始めなかった私の"いい加減さ"が原因ですｗ。
こうした「重複しないリスト(ユニークリスト)」って、実務ではけっこう必要になります。項目別の集計を行う際などには必須ですね。「重複しないリスト(ユニークリスト)」を作成する方法はたくさんありますけど、何と言っても便利なのはProPlus(あるいはExcel 2021以降)で追加されたUNIQUE関数です。Excel 2019では使用できませんけど、な～に落胆する必要はありません。時間の問題です。そのうち必ず使えるようになります。
「Data」というのはテーブルの名前です。このUNIQUE関数を、入力規則のリストとして設定してやれば、この問題もクリアです。素敵な関数を追加してくれて、ありがとう！＞Microsoft
UNIQUE関数が実装されたとき、真っ先に思い浮かんだ利用方法はこれです。なので、さっそくやってみました。
[元の値]ボックス内では、構造化参照が使えませんので、アドレスを指定します。それでも、データの量が増減して、テーブルの大きさが変わると、ちゃんとここも自動的に変わってくれます。さて、[OK]ボタンをクリックすると、次のようになります。
ありゃりゃ…。できないんだ、がっかり。この[元の値]ボックスって、任意の数式を入力できるんですけど、昔からときどき「あれ？なんでこの関数は使えないんだろ？」という現象がありました。今までは、あまり深く考えていなかったので、その理由を探ることはしませんでしたが、今回の発見を機に、その理由も判明しました。それは後述します。いずれにしても、[元の値]ボックスに、UNIQUE関数を直接入力することはできないと。なので、これまでは次のように、一度どこかのセルにUNIQUE関数を入力して、[元の値]ボックスでは、そのセルを参照するという二度手間をしてきました。
まぁ、これでも、いいっちゃいいんですけど、なんかシックリきません。どうにか[元の値]ボックスに直接UNIQUE関数を指定できないかと、チャレンジしてみたんですけどダメでした。それが今回、思わぬ関数を使うことで、この仕組みを実現できたんです。それはTAKE関数です。TAKE関数について、詳しくは下記ページをご覧ください。
TAKE関数の引数は「TAKE(範囲, 行数, 列数)」のように指定して、指定した数の行や列を抜き出します。
最初に触ったときは、正直に言って「う～ん...で？」みたいなｗ 使いどころに困るような第一印象でしたが、使っているうちに「あ、便利かも！」って感じてきました。ああ、もちろん単体で使うなんてことはしませんよ。FILTER関数やVSTACK関数の結果から、任意の列を抜き出すようなケースで便利なんです。今回、何気なく「入力規則のリスト」に使ってみました。そしたらびっくりです。まずは、結果をご覧ください。
驚いたことは二つあります。ひとつは「なぜTAKE関数は[元の値]ボックス内で使えるんだろう？」ということ。たとえば、FILTER関数やSORT関数などは、ワークシート上で問題なく使えても、[元の値]ボックスでは使えませんでした。
「スピルありき」で新設された関数のうち、なぜかTAKE関数とDROP関数は[元の値]ボックス内で使用可能です。そして、最も驚いたふたつめは「なぜ重複しないユニークリストになるんだろう？」ということ。これは謎です。もちろん、ワークシート上で使ったときは、ちゃんと重複しているすべてのデータを返します。
どうして、[元の値]ボックス内でTAKE関数を使うと、重複しないユニークリストを返すのかは謎です。Microsoftの情報を探してみましたけど、理由はまったく見当たりません。そもそも、実装された最初からこういう仕様だったかすら分かりません。まさかこんな結果になるなんて想定しませんでしたので、今まで試していなかったからです。理由は謎ですけど、これはバグや不具合とは考えられません。バグで片付けられるような、単純な結果ではないからです。そこには、Microsoftの意図を感じます。つまり、わざとこういう仕様にしていると考えるのが自然です。じゃ、なんでこういう仕様にしたのか。そして、なんでそれを公表しないのか。そこが謎です。謎ですけど、これが意図的な仕様であるのなら、今後も変更はされないはずです。したがって、入力規則のリストで「重複しないリスト(ユニークリスト)」を表示したいときは、TAKE関数を使えば(作業セルを使わずに)実装できるということです。
なぜTAKE関数は使えるのか
「スピルありき」で新設された関数の中で、なぜTAKE関数だけ特別なのかという理由については、察するところがありました。VSTACK関数やTEXTSPLIT関数など14個の画期的な関数が追加されたとき、あれこれと動作確認をしていて気づいたことがあります。TAKE関数とDROP関数だけは「参照型関数」だったんです。ちなみに、これらよりも前の2019年に実装されたSORT関数・FILTER関数・XLOOKUP関数などの中では、唯一XLOOKUP関数だけが「参照型関数」です。
ワークシート関数には、何らかの処理や演算などを行った結果(値)を返す「値型関数」と、指定した条件に一致するセルを参照する「参照型関数」があります。
調べるのは簡単で、引数に"セル"を指定する関数(たとえばOFFSET関数など)に指定してみて、エラーになったら値型関数で、問題なく機能したら参照型関数です。そうして確認したところ、TAKE関数は参照型関数で、ほかの例えばFILTER関数などは値型関数だと判明しました。つまり、両者の結果は根本的に異なっているわけです。
なぜ、入力規則の[元の値]ボックスに、UNIQUE関数やFILTER関数などを指定できなかったのか？もしかすると、それらの関数が「配列を返している」からなのではないか？と。試してみましょう。
「{10,20,30}」は配列です。このように、直接入力する配列のことを"配列定数"などと呼びます。結果は次のとおりです。
予想どおりでした。てか、だったらFILTER関数やUNIQUE関数を直接入力したときに表示されるメッセージも「元の値 はエラーと判断されます。続けますか？」ってゆう曖昧な表現じゃなくて、上図のように「配列は指定できないよ」と言って欲しかったです。そしたら悩まずに済んだのに。
いずれにしても、今回の発見で[元の値]ボックスのモヤモヤが少し晴れました。[元の値]ボックスは、配列を返す「スピルありきの関数たち」とは、相性が悪いということです。でも逆に、[元の値]ボックスには"参照型関数"が有効だと分かったため、次のような使い方も思いつきました。
「入力規則のリストを、状況によって変化させたい」というのも、よく聞くリクエストのひとつです。"参照型関数"であるXLOOKUP関数は、指定した列単位で参照するのが得意ですから、こうしたケースにピッタリです。ただし、この方法の難点は、やってみると分かりますけど、データ件数の増減に対応していません。データが増えても、リストの件数は変化しません。もし、データの増減に対応させたいのでしたら、テーブルの内部に、もうひとつ名前を定義します。
定義する名前は、別に「User」でなくてもいいです。んで、[元の値]ボックス内の数式を、次のように書き換えます。
ただし、これでも注意が必要です。それは「表示されるリストの項目数は、常に最大項目数になる」ということです。
まぁ、簡単にやろうとすれば、それなりに制約はつきものです。ああ、こんなときにFILTER関数を使えれば、空欄セルを除去することなど簡単なのに。残念ですね～
最後に、これと似たような仕組みを、今回の主役であるTAKE関数でやってみましょう。どちらの方法が良い、ということではありません。いろいろな複数の方法を思いつくことが大事です。
これらの仕組みを実務で利用するときは、くれぐれも数式の意味をしっかりと理解してから使ってくださいね。そのためには、何はともあれ"ワークシート関数の基礎"を理解することが重要です。がんばってください！