列を抽出するいくつかの方法
他のコンテンツで何気なくやっていますけど。本稿ではあらためて「列を抽出する」いくつかの方法をご紹介します。SORT関数やFILTER関数など、スピルが実装されて以降、複数行×複数列を返す関数が増えました。確かに強力で便利なのですけど、実務はそんなに簡単じゃありません。そうした複数行×複数列の結果から、ある特定の列だけを抽出する必要が多いです。むしろ逆に多いです。列を抽出するには複数の関数を使えますが、どれも一長一短です。それぞれの特徴を理解して、うまいこと使い分けてください。
CHOOSECOLS関数
まずはCHOOSECOLS関数です。これはまさに、列を抽出するために作られた関数ですね。ここでは、下図のような元データで解説します。元データはテーブル「Data」です。
上図では、抽出する列の位置を「{2,3,1}」のように配列で指定しました。別の書き方として「CHOOSECOLS(Data,2,3,1)」のようにも指定できます。とはいえ、複数の列を動的に指定するケースでは、配列を使った方が便利です。このへんの詳しい話は、下記のページなどをご覧ください。
CHOOSECOLS関数の特徴は、抽出する列の位置を数値で指定できるところです。数値の指定ですが、左から数えるときは「1・2・3…」で、右からだったら「-1・-2・-3…」って数えます。
このへんは、TAKE関数やDROP関数と同じですね。CHOOSECOLS関数のメリットは、抽出する列を数値で指定できるところと、抽出した列の並び順も同時に指定できることかな。逆に欠点は、単一の列指定しかできないことでしょうか。○列目から○列目までをまとめて抽出する、みたいな使い方はできないです。まぁ、そんなことができる関数は、ほとんどありませんけど。また、抽出したい列数が少ないのでしたら「{2,3,1}」程度で済みますが、もっと大量の列を指定するときは、配列を書くのがちょっと面倒くさいかも。そのへんは、MATCH関数やSEQUENCE関数あたりと組み合わせてください。たとえば「CHOOSECOLS(Data,SEQUENCE(3,,2,3))」は「2列目, 5列目, 8列目」を抽出します。
TAKE関数
TAKE関数も列を抽出できます。TAKE関数の引数は「TAKE(範囲,行,列)」です。引数「範囲」は元データ(今回はテーブルData)です。引数「行」には"何行分"を抜き出すかを指定します。たとえば"3"だったら「上から3行分」で、"-2"は「下から2行分」となります。
引数「列」には"何列分"を抜き出すか指定します。こちらも「1・2・3…」は右からで、左からだったら「-1・-2・-3…」とします。
引数「行」と引数「列」は、少なくともどちらか一方を指定します。列を抜き出すときは、上図のように引数「行」を省略して引数「列」だけを指定します。
TAKE関数のメリットは「右端の1列」や「左端の1列」を簡単に抽出できることですね。
何かの集計をするとき「左端の1列」がキーになることが多いですし、集計する数値などが「右端の1列」に入力されているケースも多いです。特に「右端の1列」だったら、元データが何列あるかによらず、とにかく"－1"を指定すればよいので簡単です。そのように「左端の1列」や「右端の1列」は、もちろんCHOOSECOLS関数でも抽出可能ですが、どちらでもよいのであれば、私はよくTAKE関数を使います。理由は、関数名が短くて入力するのが楽だからですｗ 細かいことを言えば、CHOOSECOLS関数を入力する場合、関数オートコンプリートを使っても"CHOOSEC"まで入力しないと確定されないのに対して、TAKE関数だったら"TA"の2文字でいけます。
XLOOKUP関数
CHOOSECOLS関数とTAKE関数は汎用的に活用できますが、思わぬところで活躍してくれるのがXLOOKUP関数です。XLOOKUP関数は、次のような使い方が一般的に知られています。
これは、いわゆるVLOOKUP関数的な使い方ですね。しかしXLOOKUP関数は、HLOOKUP関数としても使えます。もちろん結果はスピりますので、結果的に列全体を抽出できます。
XLOOKUP関数での列抽出は、CHOOSECOLS関数やTAKE関数みたいな「何列目」という数値指定ではなく、「何という見出し(ヘッダ)の列」と指定できるので便利です。抽出後のリストにも、元データと同じ"見出し(ヘッダ)"を表記したい場合、あらかじめ入力した"見出し(ヘッダ)"を検索語として利用できますし、入力規則のリストなどを使って、抽出後の"見出し(ヘッダ)"を変更すれば、該当する列を簡単に切り替えられますね。
INDEX関数
最後にご紹介するのはINDEX関数です。Excelにスピルが実装されるより昔から、実はINDEX関数で列全体や行全体を抽出することができました。ちょっと、古いExcelで試してみましょう。
この仕様はヘルプにも記載されていました。
ただし、上記のように(スピルが実装される前の)古いExcelでは、配列が返されるセル範囲全体に対して"配列数式"として入力する必要があり、お世辞にも使い勝手が良いとは言えませんでした。スピルを使った数式は"動的配列数式"です。つまり"配列数式"の上位互換版ですね。なので(スピルが実装されている)今のExcelだったら、INDEX関数で普通に列全体や行全体を抽出できます。なお、列を抽出するときは「INDEX(Data,0,2)」のように引数「行」に0を指定すると書かれていますが、上図のように引数「行」を省略しても同じ結果を得られます。
INDEX関数での指定は、結局のところ「抽出する列の位置」を数値で指定することになります。であるなら、CHOOSECOLS関数やTAKE関数でも同じ処理はできそうです。そう考えると、INDEX関数だけの特別な使い方というのは、案外と少ないのかもしれません。
こんなところですかね。ほかにも、FILTER関数でも列単位で抽出できますし、何ならBYROW関数とLAMBDA関数の組み合わせでも可能です。いずれにしても、スピルありきで実装された強力な関数たちを活用するには、列の抽出が不可欠です。ぜひ、選択肢を広く持って、自在に操作してください。