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

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

いわゆるこういう表です。「いやデータでちゃんと埋めてよ！」とか「クエリを作って下方向にフィルをかけてくれ！」とかいいたくなる気持ちをぐっとこらえて、このまま数式でなんとかしてみましょう。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))
)

こうすればすっきりします。