非連続の列をFILTER関数する
私がときどき、YouTubeでExcel関連の動画を閲覧する方がいます。解説やExcelの操作もわかりやすく、たまにですけど「えっ！？」というようなアイデアも得られます。ああ、もちろん日本人ではありません。お国はどちらだろう？英語で話をされているので、英語圏の方だと思います。ちなみに、チャンネル登録者数は確か280万人くらいだったかと。私も含めて、日本語のユーチューバーとは規模が違いますね。さすがです。
さて、その方の「FILTER関数に関する」動画を、たまたま拝見しました。要するに次のようなTipsです。
テーブルDataの[名前]列を、セルG2の文字列でフィルタします。そして、その結果として「非連続の[日付]列と[数値]列」だけを返したいのです。ちなみに、FILTER関数の最初の引数に、非連続のセル範囲を指定することはできません。
その動画では、次のようなTipsを解説していました。
ふ～む、FILTER関数を2つ使っているのかと。ちょっと解説します。まず、FILTER関数は"行方向"の絞り込みだけでなく、"列方向"を絞り込むことも可能です。たとえば、こんな感じ。
まぁ、こんなことは、ほとんどしませんから、できるってことをご存じない方も多いでしょう。ちなみに、条件に指定した引数「A1:F1=H1」の結果は、次のようになっています。
1行目のタイトルと、セルG2を比較しているのですから、意味は分かりますよね。さて、セルの数式では一般的に、論理値のTRUEは1、FALSEは0として扱われます。つまり、このFILTER関数では、次のように数式内に直接配列定数を記述しても同じ結果になります。
つまり今回のTipsは、1つめのFILTER関数で"行方向"に絞り込み、その結果を2つめのFILTER関数で"列方向"に絞り込んでいるわけです。
ふ～む、なるほどねぇ～と感じましたが、ここまで見て動画を停めました。ん？なんで？という疑問が生じたからです。まぁ、これはこれでおもしろいTipsですけど、だったらHSTACK関数やCHOOSECOLS関数を使った方が、配列定数を直接記述するよりも簡単だし、発展系への応用もできるだろうしって。
なぜだろう？と調べてみたら、理由はすぐに判明しました。この動画の投稿日は、2020年の8月だったんです。HSTACK関数やCHOOSECOLS関数やTAKE関数など第2期生の関数たちが追加されたのは、私の環境で2022年の3月です。つまり、この動画を作成した時点では、まだHSTACK関数などは存在していなかったんですね。納得です。
ちょっと発展させてみる
動画の続きを見て、実際に自分でもやってみて、なるほどこの問題に関しては納得したのですが、じゃぁ、ちょっとこの問題を発展させてみましょう。今回、動画で紹介していたTipsは、FILTER関数の使い方ではありません。FILTER関数の第1引数には、非連続のセル範囲を指定できない。でも、結果は非連続にしたい。非連続の列を表示するには、どうするのかってことです。もちろん「{1,0,0,1}」みたいに配列定数を数式内に記述するのも手ですが、これだと表示される列が固定されます。そこで次のように「表示する列を変更できる」ようにしてみました。
A列からE列のデータはテーブルにしています。名前は「Data」です。G列にもテーブル「Target」があります。テーブル「Target」に指定した"列"だけを自動的に抽出します。動画のように、FILTER関数を合わせてもいいですけど、まずはシンプルに考えましょう。もちろんテーブル「Target」を変更すれば結果も変わります。
まず、テーブル「Target」に入力した対象者の名前を、セルI1から右方向に表示するところですけど、これは超簡単ですね。TOROW関数で一発です。
セルI1から右方向のセルには、見栄えを良くするために、あらかじめ"中央揃え"の表示形式を設定しています。さて、このように指定したタイトルの列を参照するには、XLOOKUP関数が便利です。まずは試しに、1つのセル(ここではセルI1)だけを指定してみましょう。
おお、予想通りうまくいきましたね。あとは、セルI1に入力したスピル範囲に拡張してやればいいです。
ありゃ、失敗です。これは、ちょっと難しいのですが、スピルの仕様です。スピルを使った動的配列数式内では、横(列)方向のスピルと下(行)方向のスピルは、同時に処理されません。なので、ときどきこういう期待外れが起きます。しかたないので、方針を変えましょう。
CHOOSECOLS関数を使うのなら、結局上図のような数式を作れればいいです。何とかして「{5,2,3,1}」という配列を作れないのでしょうか。余談ですが、CHOOSECOLS関数の第2引数には配列を指定できます。たとえばCHOOSECOLS関数で「(左から)4番目の列と1番目の列」を抽出するとき、次のように"2通り"の書き方が可能です。
以前、セミナーを受講された方から「何が違うんですか？」と質問されましたが、まったく違います！「阿藤快」と「加藤あい」くらい違います。たとえば今回の「{5,2,3,1}」で考えてみましょう。これは配列を使わないで表すと「CHOOSECOLS(Data,5,2,3,1)」と書けます。書けますけど、じゃ、これ、抽出する列の数が増減したらどうなりますか？1列減ったときは自動的に「CHOOSECOLS(Data,5,2,3)」と引数を減らし、1列増えたときは自動的に「CHOOSECOLS(Data,5,2,3,1,4)」って引数を増やすなんて不可能です。記述した引数の数は固定されます。でも、何らかの仕組みを使って「{5,2,3,1}」という配列を作るのでしたらどうでしょう。状況によって、要素数の異なる配列を作ってくれる関数なんて、たくさんありますし、そもそもスピッた結果はもれなく配列になります。だから、複数の列位置を配列形式一発でドカンと指定できるのって、メチャクチャ便利なんです。この仕様を見たとき、さすがMicrosoftだな～よく考えてるな～って感心しましたっけ。
さて、自動的に「{5,2,3,1}」を作る方法ですが、まずはセル範囲内を"探す"作業ですし、さらに結果として欲しいのは、見つかった"位置"です。となれば、あいつの出番でしょう。そうMATCH関数です。簡単なケースで試してみます。
うむ、成功ですね。これを、CHOOSECOLS関数と合体させます。
もうちょっと発展させてみる
実務は千差万別です。いろいろなリクエストがあったり、状況も変化します。では、今回のテーブル「Target」に"存在しない名前"が指定されたらどうしましょう。
もちろんエラーですね、これに対応してみましょう。対応のしかたは運用によって変わります。まずは簡単に「存在しない名前は表示しない」とします。
そもそも今回のやり方は、
①テーブル「Target」の名前を1行目に表示し
②1行目の名前を検索してテーブル「Data」を参照する
という流れです。であるなら、テーブル「Target」内の名前をすべて1行目に表示するのではなく「存在している名前」だけが表示されるようにすれば、②の検索もうまくいくはずです。したがって、①だけを考えればいいことになります。
これはFILTER関数で一発ですね。絞り込む元データはテーブル「Target」で、絞り込みの条件は「名前が存在している」です。存在しているかどうかはMATCH関数で判定できます。
FILTER関数の絞り込み条件は、論理値のTRUEかFALSEです。上図MATCH関数の結果を「数値だったらTRUE」に変換してやれば、エラー値は数値ではないのでFALSEになります。
セルI2数式は、何も変わりません。
さらに、もうちょっと発展させてみる
テーブル「Target」に存在しない名前が指定されたとき、その名前を"表示しない"のは、上記のように簡単です。では、存在しない名前も表示するけど、でも結果は空欄にするにはどうしたらいいでしょう。もちろん、存在しない名前はいくつ、どこに指定されるか分からないものとします。
この動作に関して、私は真に驚くべき数式を見つけたが、この余白はそれを書くには狭すぎる...