XLOOKUP関数など横にスピる関数を縦にスピらせようとすると失敗する件(配列の配列 ～Array of Array～)

  1. Home
  2. Excel
  3. Excel Tips

上図はXLOOKUP関数です。普通です。でも、XLOOKUP関数で検索しているセル(ここではF2)を、次のようにスピらせようとすると失敗します。

XLOOKUP関数だけじゃありません。横方向にスピるといえば、TEXTSPLIT関数ですね。

こちらも、次のようにすると上手くいきません。

これ、「へ？なんで？」って思った方が多いのでは。私もです。私もスピルが実装された最初の頃に「へ？なんで？」ってなりました。だって、考え方は正しいのですから。

配列の配列(Array of Array)

一般的に、スピル系の関数が返すのは配列です。配列の中でも、下図のように"横方向"だけの配列を"一次元配列"といいます。

たとえるなら「平屋(1階建て)のアパート」みたいなイメージでしょうか。対して「高層階(2階建て以上)のアパート」を二次元配列と呼びます。

注意していただきたいのは、下図も"二次元配列"です。

たとえ、各階に1部屋しかなくても、立派な"高層階アパート"です。

さて、一般的に配列の各要素内には、文字列や数値などの"値"が入っています。しかし、配列の各要素内に"別の配列"が格納されているような配列も存在します。

なんでしょう？平屋建てアパートの各部屋が"メゾネット"になっていて、そのメゾネットの各層に、それぞれ別の人がいるような状態かな？(ちょっと、たとえに無理がありますかｗ)このようなデータを"配列の配列(Array of Array)"といいます。プログラミングの世界では、このように「配列の要素が配列になっている配列」のことを"ジャグ配列"と呼びますが、上図のデータもジャグ配列の一種です。そして、Excelのスピルは、"配列の配列"に対応していません。これは仕様です。まぁ、ちょっと考えたら、これをワークシート上に展開するのって、相当に無理がある気がしませんか。

これを、どーーーしても一つの数式だけで縦横にスピらせたいのなら、難易度が爆上がりします。たとえば、こんな感じです。

ね、超難しいでしょ。こんなの、後任者に引き継げませんよね。だから、こんな無茶をしないでください。実務は"腕自慢の場"じゃないです。こんなときは、普通に数式をコピーしてください。

これなら、関数初心者さんも安心ですね。