関数の結果に連番の列を追加する
要するに、こういうことをしたいんです。
これ、関数のセミナーに参加された受講者さんから質問されました。まぁ、この手の「結果に連番を振りたい」ってのは、大昔から鉄板の話題です。今回は、そのやり方を詳しく解説します。
複数の表を横に並べる
まず知っておくべき基本は「複数の表を横方向に並べる」テクニックです。使うのはHSTACK関数。STACKとは"積み重ねる"とか"並べる"という意味です。Hは"水平"や"横方向"などを表すHorizontalの頭文字で、HSTACK関数は、複数の表やリストや配列を、横方向に並べて一つの表やリストを作成する働きをします。簡単なケースで試してみましょう。
セルD1に入力したHSTACK関数の結果であるセル範囲D1:E4が、一つの表(リスト)になっている点に留意してください。もちろん、並べ順は自由ですし、複数列の表やリストも結合できます。
今回は、このHSTACK関数を使います。冒頭でご紹介した"望む結果"のうち、簡単なUNIQUE関数を例にしましょう。大まかな流れは、下図のように、連番部分とUNIQUE関数の結果を別々に作ってHSTACK関数で結合します。
連番を作る
上図の②はUNIQUE関数(の結果)です。さて、問題は①の連番ですね。これには、SEQUENCE関数を使います。SEQUENCE関数の引数は次のとおりです。
SEQUENCE(行,列,開始,目盛り)
引数「行」は必須です。必ず指定します。引数「列」、引数「開始」、引数「目盛り」は省略可能です。引数「行」は、"行(下)方向に作る連番の数"を指定します。
引数「列」は、"列(右)方向に作る連番の数"を指定します。引数「列」を省略すると、1を指定したとみなされます。
引数「開始」は、作成する連番の"最初(始まり)の数"を指定します。引数「開始」を省略すると、1を指定したとみなされます。
引数「目盛り」には、作成する連番の"間隔"を指定します。たとえば、開始が1だったとして、引数「目盛り」に3を指定すると「1→4→7→10」という連番を作成できます。引数「目盛り」を省略すると、1を指定したとみなされます。
さて、UNIQUE関数の結果に連番を追加するのですが、
①の連番は、先に紹介したSEQUENCE関数を使うと下図のように考えられます。
しかし、ここで問題があります。これ、毎回「4」とは限りませんよね。UNIQUE関数の結果が何件になるかは、やってみなければ分からないからです。
行数を自動的に取得する
これは、次のように考えます。今回の「4」とは何でしょう。この数は「UNIQUE関数の結果」です。もっと正確に言うなら「UNIQUE関数の結果の行数」ですね。だったら、行数を調べてあげればいいんです。あるセル範囲の行数を調べるにはROWS関数を使います。ROWS関数は実務でめったに使いませんが、今回は重宝します。
これで完成です。まぁ、このままでもいいですけど、同じUNIQUE関数が2回出てきて、数式全体が長くなっています。こんなときはLET関数を使うと、数式を短く記述できます。LET関数に関しては、下記のページをご覧ください。
解説は以上です。より深く理解するには、読むだけではなく、実際にデータや数式を入力し確認してください。一応、動作を確かめてみます。
FILTER関数も、考え方は一緒です。
ポイントは、HSTACK関数で表(リスト)を拡張するという発想ですね。新設された当初は「こんなの、何に使うんだろう？」って感じていたHSTACK関数ですが、最近では、メチャクチャ便利に使っています。もはや、HSTACK関数なしでは仕事になりません。てゆーか、HSTACK関数で任意のリストや配列を結合しちゃえば、なんだってできる、みたいな気分ですね。みなさんも、ぜひ活用してください。