1つの数式で縦横にスピらせる方法

  1. Home
  2. Excel
  3. Excel Tips

先日、古い知人から久しぶりで連絡がありました。その方は、かなりExcelができて、特にVBAのスキルは相当に高い方です。メールの内容はと言えば「1つの数式で縦横にスピらせる方法が分からない。詳しく解説してくれ」とのこと。たとえば、こういうやつですね。

=DROP(REDUCE("",Data,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"-")))),1)

ほかのコンテンツでも普通に使っていましたが、そういえば詳しく書いたことはありませんでした。なので、今回はメッチャ詳しく解説します。

まず前提です。横方向にスピるような数式を、縦方向にスピらせることはできません。

逆も同じです。縦方向にスピる数式を、横方向にスピらせることはできません。

こんなときも、次のようにすると実現できます。

=DROP(REDUCE("",{1,3},LAMBDA(a,b,HSTACK(a,INDEX(A2:C4,,b)))),,1)

なぜ、縦横を同時にスピれないのかという件については、下記のページをご覧ください。

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

基本的な考え方

一気にやることはできないので、次のように分割して考えます。

  1. 各行ごとの処理をする

  2. 処理した複数の結果を結合する

TEXTSPLIT(A2:A4,"-")とは書けないので、縦(下)方向にスピりたい配列(ここではA2:A4)を、別々に処理します。

これにはLAMBDA関数を使います。まず、何らかの方法(後述します)で、セル範囲A2:A4をLAMBDA関数に渡します。LAMBDA関数は、A2:A4の各セルを「A2→A3→A4」のように、1つずつ受け取ります。受け取ったセルは、LAMBDA関数の引数に入ります。下図では引数「b」です。

TEXTSPLIT関数が分割した各結果を、縦方向に結合します。使うのはVSTACK関数です。下図みたいなイメージです。

さて、LAMBDA関数に引数を渡すには、いくつかの方法がありますけど、便利なのは"(LAMBDA関数の)ヘルパー関数"を使うやり方です。ヘルパー関数は、LAMBDA関数に何らかの値を引数として渡すことができる関数で、2025年7月現在は、MAKEARRAY関数, MAP関数,BYROW関数, BYCOL関数,SCAN関数, REDUCE関数, ISOMITTED関数が用意されています。今回はREDUCE関数を使います。REDUCE関数はLAMBDA関数に引数を渡して、LAMBDA関数が複数回行った最終結果だけを返す関数です。LAMBDA関数に渡す引数は2つです。1つめの引数は、LAMBDA関数が前回までに行った結果を入れておく引数で、2つめの引数は、LAMBDA関数へ順番に渡す値です。下図では、LAMBDA関数側で受け取る引数を「a」「b」としました。引数「a」には前回のLAMBDA関数が行ったVSTACK関数の結果が格納されます。最初はブランクが望ましいので、初期値には空欄("")を指定します。

まず、LAMBDA関数の引数「b」に「A2」が渡されます。LAMBDA関数は、受け取った「A2」を使ってTEXTSPLIT関数を実行します。その結果は「{ABC,123,DEF}」という配列です。その配列と"前回までの結果"をVSTACK関数で縦結合します。とはいえ、これは初回の処理なので、"前回までの結果"は初期値の空欄("")です。このVSTACK関数の結果が、引数「a」に入って、次回以降使われます。

次に、2つめの値である「A3」が、引数「b」に入ります。TEXTSPLIT関数で分割した結果は「{ABC,234,EFG}」です。この配列と、前回VSTACK関数した結果(引数「a」)を縦結合します。結合された結果が引数「a」に入ります。これは、VBAの「A = A + 1」みたいなイメージです。

最後に、3つめの値「A4」がLAMBDA関数に渡されて処理されます。最後のVSTACK関数によって、3つの配列が縦に結合されます。そして、REDUCE関数は、この"最終結果"を返します。

ここまでを実行すると、下図の結果になります。

結果の1行目がエラーになっています。これはREDUCE関数の仕様です。全体の動作(流れ)は上述したとおりなのですが、引数に「A2」を渡した1回目のVSTACK関数で、引数「a」の初期値である空欄("")と「{ABC,123,DEF}」が結合されたためです。この仕様を利用すれば、下図のように見出し(ヘッダ)を指定することも可能です。

今回は、見出し(ヘッダ)を表示する予定はありませんので、エラーになっている結果の1行目を除去します。使うのはDROP関数です。DROP関数の詳細については、下記のページをご覧ください。

DROP関数の解説

=DROP(REDUCE("",Data,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"-")))),1)

今さらですが、元データはテーブル「Data」です。テーブルにしておけば、新しいデータが追加されても、心配はいりません。

解説は以上です。ちょっと難しいところもありますけど、実際に何度も入力して、何度もエラーになって、何度も悩んで修正を繰り返して、全体の流れや動作をイメージしてください。このやり方って、ある種の"鉄板パターン"なので、理解するといろいろな場面で活用できます。がんばってくださいね。