もーっと！　Microsoft Excelの力を濫用する

悪夢は終わらない。モダンExcel（ここではMicrosoft 365版Excelを指す）の暗黒面へようこそ。

スピル取得

indexを使って不揮発性関数でスピルを取得するやり方は、takeを使えばもっと簡単にできる。そして、xmatchと組み合わせれば、列全体から値が含まれる一番大きな行数までのスピルを取得することができる。

=index(a:a, sequence(counta(a:a)))

=take(A:A, xmatch(false, isblank(A:A),0,-1))

もっと言えば、上のtakeですらこれだけで済んでしまう。

=A:.A

正規表現

正規表現を使って、マッチする文字列があるかどうか、マッチする文字列の抽出、マッチした文字列の置換ができる。標準の検索ダイアログではできないのに！？

regextest、regexextract、regexreplace

使い方については、もはや説明不要だろう。これでもう一度VSCodeにデータを貼り付けて正規表現で置換し、元のシートに戻す、というステップを踏むことはなくなる。

またふたたびのスピル行列

let, lambda, map, filter, reduce, vstack, hstack, choosecols, chooserows, sequence

ここら辺のusual suspectsを組み合わせることで、1セルの中で複数行、複数列を出力に持つ処理を記述し切ることができる。たとえば、スピル配列と複数列の出力を持つ関数を使って以下のような処理が書ける。

=drop(reduce("", sequence(30),lambda(acc, x, vstack(acc,hstack(average(chooserows(A1:Z30, x)), product(chooserows(A1:Z30, x))^(1/columns(chooserows(A1:Z30, x))))))),1)

要するに、各行について相加平均と相乗平均を求める表現だ。本来ならばbyrowを使って書きたいところだが、byrowやbycolは複数の列/行を返すlambdaを使えないので、仕方なしに最初に無をaccumulateするreduceで処理して、最後に無をdropしてる。本当はletで(A1:B30, x)に名前をつける方が楽だし、行数も可変にできるし、処理も早い。

Matlabのequivalent codeはこう。関数型言語ならもっとExcelに寄せて書くことができるだろう。というか、Excelが関数型言語の考え方に沿って記述できるようになった、という方が正しいのだけど。

result = [];
for i = 1:size(data,1)
    [a, b] = some_func(data(i,:));
    result = [result ; a, b];
end

function [a, b] = some_func(x)
    a = mean(x);
    b = prod(x)^(1/numel(x)) ;
end

これくらいはたいしたことない？　じゃあもっと遊んでみよう。簡単のためにヘッダ、空行、空列はないものとする。以下のようなデータがあるとする。

A	B
Alice	Good
Bob	Good
Carol	Neutral
Chuck	Evil
Dan	Neutral
Eve	Evil
Faythe	Good
Mallory	Evil
Walter	Good

A,B列以外のどこかのセルに下式を入力する。

=let(
    data_matrix, A:.B,
    group_name, choosecols(data_matrix,2),
    member_name, choosecols(data_matrix,1),
    unique_groups, unique(group_name),
    mem_of_gr, drop(
        reduce("",unique_groups,
            lambda(acc, x,
                vstack(acc, transpose(filter(member_name, group_name=x)))
            )
        )
    ,1),
    hstack( unique_groups, ifna(mem_of_gr, ""))
)

すると以下の結果が得られるはずだ。

A	B	C	D	E
Good	Alice	Bob	Faythe	Walter
Neutral	Carol	Dan
Evil	Chuck	Eve	Mallory

フハハ、怖かろう！

groupby？　VBA？　PowerQuery/PowerPivot？　私そういう難しい機能よくわからなくて……。