【めざせExcelマスターへの道】Excelの配列系新関数について

こんにちは。ヒトツメです。
先日、Microsoftは配列に関する新関数を今後Excelに実装していくことを発表しました。
ついているアニメーションを見る限り、なんとなく各配列の動きは予想がつきますが、まだまだこれらの関数がどのように使われるべきかは、あまりピンときません。

Announcing New Text and Array Functions

We are excited to announce fourteen new Excel functions that will allow you to easily manipulate text and arrays.

techcommunity.microsoft.com

そこで今日は、2019年よりExcelが実装している配列機能について少し解説をしながら、今後これらの関数をどのように使うべきなのか、考えていきたいと思います。

配列について

基本的な考え方

Excelに限らず、配列という考え方は、広く様々なプログラミング言語で採用されています。
2019年以降、Excelの関数やシートの方でも採用されるようになっていますが、一般に「マクロ」と呼ばれるVBAでは、それ以前から実装されており、多次元配列やジャグ配列と呼ばれる特殊な配列に関しても、扱うことが可能でした。

Excelの関数やシートにおいては、二次元配列しか扱うことができないので、ここでは、二次元配列に絞って話を進めますが、かなり大雑把に言ってしまうと、シートの一部分を、長方形に切り取ったものを、そのまま扱えるということです。
例えばA1から横に4列、縦に2行、データが入っているとして、A3に「=A1:D2」と入れると、A1からD2に入っている情報がA3からD4にそのまま入ります。A1からD2の情報をそのまま扱う機能が、Excelのシート上での配列の機能と言えます。

上の例では、シートの情報をそのまま使いましたが、配列はセルに直接入力することで作成することもできます。例えばA1に「={1,2,3,4;5,6,7,8}」と入れると、次のように表示されます。

VLOOKUPが一つで良くなる

これだけ聞くと、「え、それだけ？」と思うと思いますが、配列の優れているところは、ほかの関数の引数としても扱うことができるという点です。
VLOOKUP関数の引数として、第二引数に使うことはもちろんですが、何よりすごいのが、第一引数として使うことができるということです。言葉だけで説明するよりも、実際の動きを見ていただいた方がいいと思うので、次のような表を例に説明します。

この時、例えばA株式会社と有限会社Cの担当エリアをそれぞれ、VLOOKUP関数を使って検索したい場合、例えばA8に「A株式会社」、A9に「有限会社C」と入れて、B8とB9に、それぞれ次のような関数を入れると思います。

この時、配列を使えば、B8に下記のように入れるだけで実現することが可能になります。

この時、もちろん、「=VLOOKUP({“A株式会社”;”有限会社C”},$A$1:$E$6,3,0)」と入れることで、同じ結果を実現することも可能です。

引数と戻り値の関係性

この時、重要なのは、引数と戻り値の関係性は、配列を使った場合でも使わなかった場合でも変わらないということです。
一般的に、Excel関数の多くは戻り値が単なる「値」になるように設定されていることが多く、SUMやAVERAGEなどの計算系の関数を除けば、第一引数も同じく「値」になるように設定されていることが多いです。この時、第一引数に配列を投入すれば、同じように戻り値も配列になって返却されます。

先ほどのVLOOKUP関数のケースもそうですが、IF関数を使う場合でも、第一引数を配列とした場合、戻り値も同じ形の配列が返却されます。

新関数を見ていく

中間テーブルが要らなくなる

さて、以上が配列に関する解説ですが、今後実装が予定されている新関数は、いずれもこういった配列を直接操作することを目的としているものです。

例えば、TEXTSPRIT関数は、「Splits text into rows or columns using delimiters（区切り文字を使って、テキストを行または列に分割する）」という説明がなされていますが、例えば、ハイフンで区切られた電話番号を三つに分割し、それぞれの要素を持つ配列を返却することができます。
あるいは、VSTACK関数およびHSTACK関数は、それぞれ、「Stacks arrays vertically（垂直方向に配列を結合する）」「Stacks arrays horizontally（水平方向に配列を結合する）」という説明がなされており、二つの配列を縦か横につなげたものを返却することができます。

これらの関数を使うことによって、中間テーブルを作って、それを手動で加工するといったことを行ったうえで実施していた作業が、関数上で自動化することができます。
具体的な実装例は少しややこしくなりそうなので割愛しますが、「関数によって表現できる」ということは、Excel上の計算で再現できるということなので、作業の省力化が実現できます。

カレンダーが扱いやすくなる

あるいは、TOROW関数とTOCOL関数、WRAPROWS関数とWRAPCOLS関数は、それぞれ、配列の形を変えて、二次元配列を一次元配列に、一次元配列を二次元配列に変更することができるというものです。
例えば、TOROW関数を使えば、横2×縦4の配列を、横8×縦1の配列に変換することができます。

こういった関数を使うことで、7日おきに改行するように作られているカレンダーを、一列もしくは一行に変換したり、逆に一列に表現されている日付ごとのデータを、7日おきに改行するように見やすく作り変えることが可能になります。
縦に日付ごとにまとめて記載したデータを、7日おきに改行するようなデータに作り変えるときに、手作業をしなくてよくなるので、非常に便利です。

さいごに

ただ、配列で処理をするということは、データをExcelが一時的に保持するということを意味します。それはつまり、計算する側からしても、いまExcelがどんな配列を保持しているのかを記憶しながら作業を進める必要があります。
使う側においても、記憶力を駆使して、工夫しながら設計していく必要があるので、使い慣れるまでには少々時間がかかりそうです。