数式/関数：値が省略された表から計算する

表に見やすさを求める人にしばしば見られるのだが、入力表なのにセル結合を入れてしまったり、同じデータが下に並んでいる時に先頭行以外を省略してしまったりする。こういう表を作ってしまってから「集計どうやったらいいの？」と悩む人も多い。

いわゆるこういう表。「いやデータでちゃんと埋めてよ！」とか「クエリを作って下方向にフィルをかけてくれ！」とかいいたくなるところをぐっとこらえて、このまま数式でなんとかしてみよう。Microsoft365ならなんとかなる。

SCAN関数を使えば、作業列でやっていたような「上のセル（あるいは下のセル）と比較する」計算が配列で簡単に作れる。

F2：=SCAN(,テーブル1[社名],LAMBDA(x,y,IF(y="",x,y)))

G2：=SCAN(,テーブル1[商品],LAMBDA(x,y,IF(y="",x,y)))

D・F・G列があれば、普通に集計ができる。では集計を含めてこれを一つの数式にまとめてみよう。

=SUM(
    FILTER(
        テーブルA[売上],
        (SCAN(,テーブルA[社名],LAMBDA(x,y,IF(y="",x,y)))=[@社名])
        *(SCAN(,テーブルA[商品],LAMBDA(x,y,IF(y="",x,y)))=[@商品]),
        0
    )
)

とりあえずはこれで計算する。ただ参照元が違うだけの SCAN関数を２個並べるのは無駄じゃないだろうか。

それなら異なるところだけを変数にした「X関数」を作ってしまえばいい。

=LET(
    X,LAMBDA(tbl,SCAN(,tbl,LAMBDA(x,y,IF(y="",x,y)))),
    _x1,X(テーブル1[社名])=[@社名],
    _x2,X(テーブル1[商品])=[@商品],
    SUM(FILTER(テーブル1[売上],_x1*_x2,0))
)

こうすればすっきりする。