【Googleスプレッドシート】SUBTOTALを数値以外へ無理やり適用し、非表示行を除外して処理する【GAS無し】

この記事は以下の記事の改善版です。

TL;DR

• 非表示行に対するSUBTOTAL(103, ...（COUNTA）の結果は0になる
• 「SUBTOTAL結果が0かをまず判定し、非0ならやりたい処理」とすれば、非表示行を除外して処理できる

前置き

スプレッドシートで、非表示範囲を無視して処理を行いたい場合、SUBTOTAL関数を使うしかありません¹。
一方、SUBTOTAL関数は数値以外へ適用できないため、柔軟性に欠けるという問題があります。

この記事では、工夫でこの制約を突破し、制御列1列 + 最小限の補助セルという縛りの中で、SUBTOTALを用いた汎用的な処理を実現する方法を紹介します。

ただし、この記事で紹介する方法は、処理対象データの範囲が48列以下の場合のみ適用可能です。
これはBITOR関数の制約によるものです。
もっと工夫すればより列数の大きな表にも適用可能と思われますが、ここではシンプルな方法を紹介します。

サンプルシートは以下で公開しています。

今回紹介する題材

「その列中で、表示されていて、空文字列でないセルが有ればNGを表示」という集計を作ります。
これは2つの巨大なシートを比較する際、非表示行を無視した当該列中に差分が含まれるか判定するという場面を想定しています。

具体的には、以下の画像のような表を作ります。

1行目はヘッダー行です。

A列は結果表示及び制御列です。
A2セルは、各所で使いまわすため、この表の列数を格納しています。
A3:Aの範囲には行毎の判定結果を格納しています。
上の画像では、3行目には非空セルが無いため空、4, 5行目は非空セルが含まれるため、制御値（後述）が表示されています。

2行目は結果表示列です。
B列は全て空白なのでセルが空に、C・D列は非空行が含まれるためNGと出ています。

B3以降の範囲には、処理対象データが格納されます。

表を操作した様子

B3を以下のように編集すると、非空セルが含まれるようになったことへ対応してB2がNGへ変化します。

更に4行目を非表示にした場合、表示中のC列には非空セルが無くなるため、NG表示が消えます。

手法の説明

以下のような流れで各列への判定を行います。

• 各行毎の判定結果の制御値を制御列（A列）へ格納
  1. SUBTOTAL(103, ...（COUNTA）を取る
  2. 1.の結果が0でなければ、当該行の各列について、空なら0、非空なら1のビット列に変換
  3. 2.の結果が0なら空文字列、非0なら2.の結果を制御列へ格納
• 各行毎の判定結果全体のBITORを取り、1つ以上非空セルの有る列にはNGを表示

行毎の集計方法

行毎の集計は以下の関数で行なっています。

=BYROW(
  INDIRECT("B3:" & SUBSTITUTE(ADDRESS(1, A2, 4), "1", "")),
  LAMBDA(row, LET(
    count, SUBTOTAL(103, row),
    result, IF(count = 0, 0, SUM(ARRAYFORMULA(IF(row<>"", POW(2, (COLUMN(row) - 2)), 0)))),
    IF(result = 0, "", result)
  ))
)

処理対象範囲の取得

以下で、B3以降に格納されたデータ範囲を取得しています。

INDIRECT("B3:" & SUBSTITUTE(ADDRESS(1, A2, 4), "1", ""))

これで取得した範囲について、BYROWで処理しています。

BYROW(
  INDIRECT("B3:" & SUBSTITUTE(ADDRESS(1, A2, 4), "1", "")),
  LAMBDA(row, ...)
)

行毎の集計

行毎の集計は以下のように行なっています。

LAMBDA(row, LET(
  count, SUBTOTAL(103, row),
  result, IF(count = 0, 0, SUM(ARRAYFORMULA(IF(row<>"", POW(2, (COLUMN(row) - 2)), 0)))),
  IF(result = 0, "", result)
))

SUBTOTAL(103, row)は、行の表示・非表示を判定しています。

IF(count = 0, 0, SUM(ARRAYFORMULA(IF(row<>"", POW(2, (COLUMN(row) - 2)), 0))))は、行毎の判定結果から制御値への変換です。
まず、処理対象行が非表示なら0を返します。
表示されている場合、セルの値が空なら0、非空なら1のビット列に変換します。
サンプル表の4行目を例にすると、{0, 10, 100}の総和を取って、110（10進数で6）という値が得られます。

最後にIF(result = 0, "", result)として、制御値が0だった場合は空文字列にしています。

列毎の集計結果判定

列毎の集計結果判定は、以下をB2へ設定して行なっています。

=LET(
  result, REDUCE(0, A3:A, LAMBDA(acc, cur, BITOR(acc, cur))),
  ARRAYFORMULA(
    IF(
      BITAND(result, POW(2, (COLUMN(B2:2) - 2))) <> 0,
      "NG",
      ""
    )
  )
)

REDUCE(0, A3:A, LAMBDA(acc, cur, BITOR(acc, cur)))は、A列に格納された制御値全体の論理和計算です。
これによって、1度以上非空セルの登場した列が1のビット列が得られます。

各列の判定結果への変換は以下のように行なっています。

ARRAYFORMULA(
  IF(
    BITAND(result, POW(2, (COLUMN(B2:2) - 2))) <> 0,
    "NG",
    ""
  )
)

この手法の限界について

最後に、この手法の限界についてです。

まず、冒頭で述べた通り、今回は判定にBITORを利用している関係で、処理対象データの範囲が48列以下の場合のみ適用可能となっています。
この制約に関しては、制御値の生成・判定方法を工夫することで拡張可能です。
例えば、制御値全体を文字列と捉え、48桁毎に改行区切りで格納し、判定時は改行で分割し直して処理する方法が思いつきます。

次に、非表示列の無視についてです。
行毎の集計方法と同じような手順で非表示になっている列を判定し、それを行毎の集計結果へ反映できないものかと試みましたが、残念ながらこれはできないようでした。
BYROWとBYCOLで何か違いが有るのか、それとも何か処理方法を誤っているのか不明ですが、何かご存知の方いらっしゃいましたらコメント頂けると嬉しいです。

1. 少なくとも執筆時点では、調べてもどのAIに聞いてもこの回答でした。 ↩