よりシンプルかつ巨大な表へも適用可能な改良版を別途投稿しておりますので、そちらをご覧ください。
この記事はアイデアの備忘用に残しています。
TL;DR
- 制約は強めだが、すごく頑張れば非表示行を除外した任意の判定が可能
- （
SUBTOTAL関数は論理和・論理積に対応して下さい！！！）
前置き
スプレッドシートで、非表示範囲を無視して処理を行いたい場合、SUBTOTAL関数を使うしかありません1。
ここで、SUBTOTAL関数は数値以外へ適用できないため、基本的には、処理対象から数値への変換結果を格納する集計範囲を作って処理する必要があります。
この記事では、工夫でこの制約を突破し、制御列1列 + 最小限の補助セルという縛りの中で、SUBTOTAL用いた汎用的な処理を実現する方法を紹介します。
ただし、この手法には、前述の通り難解かつ、それほど大規模に適用し切れないという問題が有ります（改善案など頂けると非常に嬉しいです）。
サンプルシートは以下で公開しています。
今回紹介する題材
「その列中で、表示されていて、空文字列でないセルが有ればNGを表示」という集計を作ります。
これは2つの巨大なシートを比較する際、非表示行を無視した当該列中に差分が含まれるか判定するという場面を想定しています。
具体的には、以下の画像のような表を作ります。
1行目はヘッダー行です。
A列は結果表示及び制御列です。
2行目は、各所で使いまわすため、この表の列数を格納しています。
3行目以降は、行毎の判定結果を格納しています。
3行目は非空セルが無いため空、4, 5行目は非空セルが含まれるため、制御値（後述）が表示されています。
2行目は結果表示列です。
この部分について、A列に対するSUBTOTAL関数の適用で実現しています。
B列は全て空白なのでセルが空に、C・D列は非空行が含まれるためNGと出ています。
B3以降の範囲には、処理対象データが格納されます。
表を操作した様子
B3を以下のように編集すると、非空セルが含まれるようになったことへ対応してNGと出ます。
更に4行目を非表示にした場合、表示中のC列には非空セルが無くなるため、NG表示が消えます。
手法の説明
基本的には、「SUBTOTAL関数が処理可能な数値形式で行毎の判定結果をA列へ格納 -> A列をSUBTOTAL関数で処理した結果を2行目に表示」という流れで処理を行います。
例として、先ほど紹介した表の4行目に着目すると、判定結果はB列から順に「空、非空、非空」という並びになっています。
この情報を何らかの形でA列へ格納する必要があります。
プログラミングにおいてはこういった場合に2進数の論理演算を使いがちですが、執筆時点では、SUBTOTAL関数で論理和・論理積を取ることはできないようでした。
そこで、自分は素数の積を使った方法を考えました。
素数の性質について
素数は1または自分自身でしか割り切れないという性質を持ちます。
つまり、列毎に素数を割り当て、集計結果をその総乗とすれば、集計結果をその列に対応する素数で割り切れるかでその列に対する判定状況を読み取れるようになります。
素数シートについて
後続の手順では、事前に取得しておいた素数を格納したシートを用います。
このシートの名前はprimesです。
中身は以下のようになっています。
2
3
5
7
...
行毎の集計方法
行毎の集計は、以下をA3へ設定して行なっています。
=LET(
primes,TRANSPOSE(INDIRECT("primes!A1:A"&(A2 - 1))),
BYROW(
INDIRECT("B3:" & SUBSTITUTE(ADDRESS(1, A2, 4), "1", "")),
LAMBDA(row, LET(
tmp,ARRAYFORMULA(IF(row<>"", 1, 0) * primes),
IFNA(PRODUCT(FILTER(tmp, tmp>0)), ""))
)
)
)
これは、「非空セルの列に割り当てられた素数の総積を取る」という処理になっています。
primesの取得
変数primesへは、列数に対応する範囲の素数列を設定しています。
TRANSPOSE(INDIRECT("primes!A1:A"&(A2 - 1)))
これによって、列数に応じ、以下のような素数配列が得られます。
{2, 3, 5}
処理対象範囲の取得
以下で、B3以降に格納されたデータ範囲を取得しています。
INDIRECT("B3:" & SUBSTITUTE(ADDRESS(1, A2, 4), "1", ""))
これで取得した範囲について、BYROWで処理しています。
BYROW(
INDIRECT("B3:" & SUBSTITUTE(ADDRESS(1, A2, 4), "1", "")),
LAMBDA(row, ...)
)
行毎の集計
業毎の集計は以下のように行なっています。
LAMBDA(row, LET(
tmp,ARRAYFORMULA(IF(row<>"", 1, 0) * primes),
IFNA(PRODUCT(FILTER(tmp, tmp>0)), ""))
)
まず、「非空なら1、空なら0」という配列を作り、素数配列との積を取っています。
サンプル表の4行目を例にすると、{0, 3, 5}という配列が得られます。
ARRAYFORMULA(IF(row<>"", 1, 0) * primes)
この配列について、0でないものの総乗を結果として取っています。
サンプル表の4行目では15が得られます。
IFNA(PRODUCT(FILTER(tmp, tmp>0)), ""))
列毎の集計結果判定
列毎の集計結果判定は、以下をB2へ設定して行なっています。
=LET(
s,SUBTOTAL(106, A3:A),
IF(
s = 0,
"",
LET(
ps,INDIRECT("primes!A1:A"&(A2-1)),
TRANSPOSE(ARRAYFORMULA(IF(INT(s/ps) <> s/ps, "", "NG")))
)
)
)
表示されている行毎の集計結果の総乗の取得
この記事のコアです。
106は、範囲から非表示行を無視した総乗を取っています。
SUBTOTAL(106, A3:A)
集計結果が0件の場合のエッジケース処理
集計結果が0だった場合、後述する判定結果がおかしくなってしまうため、以下のようにして先に処理しています。
IF(
s = 0,
"",
...
)
列毎の集計結果判定
最後に、列毎の集計結果判定です。
LET(
ps,INDIRECT("primes!A1:A"&(A2-1)),
TRANSPOSE(ARRAYFORMULA(IF(INT(s/ps) <> s/ps, "", "NG")))
)
INDIRECT("primes!A1:A"&(A2-1))は他でも利用した素数配列取得です。
ARRAYFORMULA(IF(INT(s/ps) <> s/ps, "", "NG"))は、一括で列毎の剰余を取って判定しています。
MOD関数でなくINT(s/ps) <> s/psを使っているのは、MOD関数だと集計結果の総乗が大きい場合に機能しなかったためです。
この手法の限界について
最後に、この手法の限界についてです。
まず、総乗の総乗が出てきてしまう関係で、判定に引っかかるセルが少しでも増えるとすぐにエラーが出ます。
具体的に、最悪のケースだと、データ範囲が15列の時、20箇所程度判定に引っ掛かるだけでエラーになってしまうようでした。
判定処理自体はそれなりに低コストで実現できており、大規模シートにも適用できるのですが、数値の上限だけがネックでした。
また、剰余の取り方に関しても、今回利用した方法は誤差によって判定結果が変になる可能性があります（検証はできていません）。
記事内でも言及しましたが、SUBTOTALで論理和・論理積を取れればと何度も思いました。
これさえ有れば、厳密かつ軽量に実現できるのですが……。
正直、後1列 or 1行制御列を追加して、より低コストな方法を模索した方がよいと感じました。
-
少なくとも執筆時点では、調べてもどの
AIに聞いてもこの回答でした。 ↩