今日を乗り切るExcel研究所

Excel に働かされていませんか

Excel でタテのデータをヨコにしたい【スピル編】

今回は、スピルを使って表組み換えの問題を解決できないか調べます。

f:id:shego:20201025154813p:plain

スピルで表の組み替え

「表の組み替え」というのは既存の表データの列や行の向きを変えたり、配置やまとめ方等の構造を変更するという、手作業でやると非常にしんどい操作です。

数式を駆使すればなんとかできるようですが、長く複雑な数式を組むのに頭を悩ますのもまた働かされている感じがします。

ところで、最近の Excel で気になる話題に、「スピル」 というのがあり、遅ればせながら勉強中です。

なかなか便利な機能で、使いどころとして表の組み換えにも応用できそうです

そこで本記事ではスピルを理解する練習課題として、この表組み換え問題に挑戦してみます。

スピル?なにそれ美味しいの?

「スピル」とは、一つの数式から複数のセルに値を一括入力するという機能です。

例えば、=A1:B2のようにセル範囲で参照を数式に入力してみましょう。

f:id:shego:20201025155547p:plain

すると隣のセルにも参照先範囲の値が自動で展開されます。

これがスピルです。

複数の値を返す数式の結果が、隣のセルに「漏れ出す(spill)」のです。

もし #VALUE! エラーにったら残念、ご使用のバージョンの Excel はスピルに対応していません。 スピルは今のところ Microsoft 365(旧 Office 365)版の Excel でのみサポートされています。

もともと Excel には複数の値を一括で扱うための「配列」という値形式があります。 ただし配列を扱うには「配列数式」と呼ばれる特殊な入力モードに切り替える必要がありました。

また、配列数式が配列で複数の値を返してくれても、セルのほうは一つの値しか格納できません。 配列の内容をシート上に表示させるには、隣近所の空白セルを間借りする必要があり、ユーザはそのセル範囲の調整もしなければなりませんでした。

配列数式は確かに有用な機能なのですが、その使いづらさのため、一部の上級者にしか活用されていなかったのです。

Microsoft 365 ではその配列数式がリニューアルされ、「動的配列数式」と呼ばれるものに置き換えられました。 そしてそれに伴い、スピル機能と、いくつかの新関数が提供されたのです。

動的配列数式では入力モードの切り替えがなくなり、通常の数式と区別のない使い勝手となりました。

また、スピルによって、配列の各値が自動的に隣接セルへ配置されるようになりました。

配列を返すような関数の結果も全てスピルします。

それらに加え、6個の便利な新関数が提供されました。

 



 

タテのデータを横にスピるには

表組み替えにも色々ありますが、本記事ではタテ向きのデータを横向きにする変換、なかでも以下の3パターンを取り上げます。

1列のデータを複数列にスピる

複数項目からなるデータがタテ1列に並んだリストを、項目ごとの複数列に変換することを考えます。 外部からのデータ取り込みでなどでよく必要になる前処理です。

f:id:shego:20201008170652p:plain

例えば、元のリストに4項目で1組のデータが3件ぶんタテに並んでいるとします。 これを4列3行の表に整理したいと思います。

それには、リストの上のセルから順番に値を横並びで転記していき、4つごとに次の行に改めます。

スピルを使えば SEQUENCE 関数と INDEX 関数を組み合わせた、次のようなシンプルな数式で実現できます。

=INDEX(リスト範囲, SEQUENCE(データ件数, 項目数))

f:id:shego:20201010155630p:plain

もしデータ量が多くてデータ件数もよくわからない場合は、以下のようにその計算も数式に含めることができます。

=INDEX(リスト範囲, SEQUENCE(ROWS(リスト範囲)/項目数) 項目数))

ただし、リスト範囲の行数が項目数の整数倍でないと最後のデータが出ませんので注意してください。

【解説】

ここで、SEQUENCE 関数は連番を生成し、それを配列として返す関数です。 その結果をスピルさせると、隣のセル範囲に指定個数の連番が展開されます。

f:id:shego:20201010154319p:plain

SEQUENCE 関数の引数に行数と列数の両方を指定すると、結果は2次元の配列として連番が折り込まれます。 その連番の並び順をみると、まさにリストからコピーされるべきセルの位置に相当します。

セル範囲内の位置を指定してその値を取得するには INDEX 関数を使います。

f:id:shego:20201025214053p:plain

INDEX 関数を使い SEQUENCE 関数から得た連番で1列リスト内の位置を指定すれば、そのまま複数列への変換になります。

ところで上記数式では、INDEX 関数に SEQUENCE 関数の結果の配列をまるごと渡しているように見えますが、どうなっているのでしょうか。

関数の引数には、数値や文字列のような値を1個しか受け取れないものと、配列やセル範囲による複数の値をまとめて受け取れるものがあります。

INDEX 関数の最初の引数は複数の値を受け取れて、上記数式ではリストのセル範囲を指定しています。

INDEX 関数の2番目の引数は、その範囲内の位置を示す行番号を指定するもので、本来は1個の数値しか受け付けないはずです。

上記数式のように、1個の値しか受け取らない引数に配列やセル範囲を指定すると、関数の処理はその中の要素に個別に適用され、関数の結果もそのまま複数の値、すなわち配列として返されるのです。

f:id:shego:20201025214742p:plain

その結果もまたそのままスピルします。

リスト表からグループ表にスピる

次に項目列でグルーピングした値列の値を横並びにすることを考えます。 ここでは1列目が項目名で2列目が値になっているようなリストを想定します。

f:id:shego:20201008170622p:plain

それには、2つの数式を使います

項目列
=UNIQUE(1列目の範囲)
値の並び
=TRANSPOSE(FILTER(2列目の範囲の絶対参照, 項目列の範囲の絶対参照=項目列の先頭, ""))

例えば以下のようなデータの組み換えをしたいとします。

f:id:shego:20201026005531p:plain

まず、リストの1列目からグループとなる項目名のリストを作成します。 これには UNIQUE 関数を使います。

=UNIQUE(B2:B10)

f:id:shego:20201026004005p:plain

次に最初の項目名を参照して2列目の値をグルーピングし、それを横向きに並べます。 これには FILTER 関数と TRANSPOSE 関数 を組み合わせます。

=TRANSPOSE(FILTER($C$2:$C$10,$B$2:$B$10=E2, ""))

f:id:shego:20201026004402p:plain

元リストへのセル参照は、コピーでずれないように、$をつけた絶対参照に固定しています。 相対参照を絶対参照に切り替えるにはF4キーを使うと早いです。

あとはこの数式をオートフィルなどを使って列方向にコピーします。

f:id:shego:20201026005028p:plain

本当なら数式のコピーもなしで、スピルで解決したかったところですが、一つの数式で2回スピルさせるのは難しいようでうまくいきません。

【解説】

ここで、UNIQUE 関数はセル範囲の値を重複なしの配列にして返す関数です。

f:id:shego:20201025160900p:plain

FILTER 関数はセル範囲内で条件に一致する行を絞り込んだ配列を返します。

f:id:shego:20201025161441p:plain

TRANSPOSE 関数は引数に渡されたセル範囲や配列の行と列を入れ替えた配列を返します。

f:id:shego:20201026005819p:plain

どれも配列を返す関数なので、スピルします。

FILTER 関数で項目別に絞り込まれた値はタテ方向の配列になりますが、TRANSPOSE 関数でそれを横方向に並び換えてグループ行とします。

ところで、QNIQUE 関数 と FILTER 関数はどちらも、結果の配列の要素が何個になるのかは、データの内容によるので固定はできません。

つまり、どこまでスピルするのかは実行してみるまで分からず、データの内容が変更されればその個数も変わるかもしれません。

このような配列の変更にも動的に対応できること、これが動的配列数式と呼ばれる理由です。

 



 

リスト表をマトリクス表にスピる

以前本ブロク記事でマトリクス→リスト変換の方法を取り上げました。

今度はその逆の変換です。 近頃は縦持ち横持ち変換とも言うようです。

f:id:shego:20201008170704p:plain

次の図のような、項目2列と値1列からなる3列のデータリストから、マトリクス表を作成することを考えます。

f:id:shego:20201026011524p:plain

それには3つの数式を使います。

行見出し
=UNIQUE(1列目の範囲))
列見出し
=TRANSPOSE(UNIQUE(2列目の範囲))
マトリクスデータ
=XLOOKUP(列見出しの範囲 & 行見出しの範囲, リストの1列目 & リストの2列目, リストの3列目, "")

まず、UNIQUE 関数と TRANSPOSE 関数を使い、項目列から列見出しと行見出しを抽出し、そのスピルをマトリクス表の見出しとして配置します。

=UNIQUE(B2:B13)
=TRANSPOSE(UNIQUE(C2:C13))

f:id:shego:20201026012408p:plain

次はマトリクスの各セルの値を列と行の見出しをキーにしてリストから検索します。

これには XLOOKUP 関数を使います。 マトリクス内の左上のセルに以下の数式を入力します。

=XLOOKUP(F3#&G2#,B2:B13&C2:C13,D2:D13, "")

f:id:shego:20201026013717p:plain

マトリクス内に値がスピルされました。

【解説】

XLOOKUP 関数も動的配列数式と同時期に追加された関数で、従来の VLOOKUP 関数を置き換えるものです。 VLOOKUP 関数に比べてだいぶ使い勝手が改善されているので要チェックです。

ところで、XLOOKUP 関数は1個の値を返す関数です。 それなのに上記数式でスピルしているのはなぜでしょう。

まず、タテと横の配列やセル範囲を組み合わせた計算式は、交差する値の組みの2次元配列として展開されるという性質があります。

f:id:shego:20201018140231p:plain

これを利用すると、マトリクスの各セルで交差するキーの組み合わせを参照することができます。 以下の数式でのキーの組み合わせがスピルでどう展開されるのが確認できます。

=F3# & "," & G2#

f:id:shego:20201026014501p:plain

ここでG3#というのは見慣れない記法です。

本来セル範囲の参照はG3:G5のように表記するところですが、スピルのサイズはその時々で異なるので固定で指定できません。 スピルされたセル範囲は「#」をつけてG3#とすることで終了アドレスを明示せずに参照できるようになります。

XLOOKUP 関数の最初の引数は検索キーで、本来1個の値しか受け取れないところに、キーの組み合わせの配列を渡しているので、その戻りも2次元の配列となってスピルします。

スピルされた各セルでは、配分された2つのキーの組を XLOOKUP 関数に渡してリストの値を検索します。 ただ、XLOOKUP 関数に複数キーの指定はできないので、ちょっと安直ですが、2つのキーを&で連結した文字列を代わりに検索対象としています。

まとめ

動的配列関数とスピルを活用して、表の組み換えを行ってみました。

同じことを配列やスピルを使わない従来の数式で実現するには複雑な数式を組み立てる必要があります。 スピルをうまく活用すると、これまでの数式を使った解法と比べて、かなり単純で見通しの良い表現にできることが分かりました。

これまで配列数式と言えば、かなりの上級者だけが使える機能でしたが、スピルのおかげで、広く一般にも活用される基本スキルになっていくと思われます。

ただし、Microsoft 365 版以外のバージョンの Excel でスピルがサポートされる見込みはないので、旧来の買い切り版 Excel のユーザがその恩恵にあずかることはありません。 Excel 2019 の次(2022?)のバージョンまで待てば使えるようになるかもしれません。

関連記事