Google スプレッドシートのための効率的なユーティリティ集

SMALLER LARGER

同じサイズの2つの範囲に関して、それぞれのペアのうち、より小さい方のデータからなる範囲が知りたいとしよう。

スプレッドシートでは、かういふゝうに範囲[1]に含まれる各データについて何らかの計算をしたいときは ARRAYFORMULA を使ふことが多い。ARRAYFORMULA は、配列でないデータ(の組)を引数に取る関数によって、配列(の組)を別の配列に移す。ほとんど同じ機能を持つ関数として MAP があり、次の2行は大抵同じ結果になる:

ARRAYFORMULA(FOO(A1:A, B1:B))
MAP(A1:A, B1:B, LAMBDA(a, b, FOO(a, b)))

たゞし、ARRAYFORMULA は配列(の組)を引数に取る関数とは協調しない。もし FOO が引数に配列を取れるなら、ARRAYFORMULA は何もせず、FOO(A1:A, B1:B) が返す値をそのまゝ返す。

冒頭の問題に戻ると、組み込みの MIN は引数に範囲を取れる[2]ので、ARRAYFORMULA(MIN(A1:A, B1:B)) は、A1:AB1:B の最小値を表示してしまふ。これを回避するために、丁度2つの引数を取る MIN の代替物を用意する:

SMALLER(left, right)
=IF(left < right, left, right)

C1 を =ARRAYFORMULA(SMALLER(A1:A, B1:B)) とすれば、C1 には A1 と B1 のうち小さい方が、C2 には A2 と B2 のうち小さい方が……表示される。

LARGER も同様に実装する:

LARGER(left, right)
=IF(left > right, left, right)

CONJ DISJ

同じ理由で、2変数の AND OR もあると嬉しい:

CONJ(left, right)
=IF(NOT(left), false, IF(NOT(right), false, true))
DISJ(left, right)
=IF(NOT(NOT(left)), true, IF(NOT(NOT(right)), true, false))

SLICE

同じヘッダーを持つ2つの表をマージするために、2つ目の表の先頭からヘッダーを取り除きたいとする。

かういふとき、もしこの表がシート上にあるなら、セル参照を直接書き換へるか、OFFSET 関数で参照をシフトさせるかすればよい。たとへば、A1:G100 から先頭2行をヘッダーとして取り除くには、セル参照を直接 A3:G100 に書き換へるか、あるいは OFFSET(A1:G100, 2,, 98) とする。

けれど、扱ふ範囲がいつもセル参照として表せるとは限らない。たとへば、

  • IMPORTHTML で取得した HTML テーブル
  • セル参照の配列。e.g., {C1:C; A1:A}

などはセル参照ではない。

こゝでは、かういふものを受け入れつゝ行のみについて働く OFFSET の簡易版、SLICE を作る:

SLICE(range, offset_rows, _height)
=LET(
  height, IF(ISBLANK(_height), ROWS(range) - offset_rows, _height), 
  CHOOSEROWS(range, SEQUENCE(height, 1, offset_rows + 1))
)

名前付き関数はオプション引数を扱へないので、_height を省略するときは SLICE(range, 2, ) といふゝうに、末尾のカンマを置いて使ふ。

UNION INTERSECTION

範囲や配列を集合のやうに使ひたいことも多い。UNION は割合簡単に実装できる[3]:

UNION(left, right)
=LET(
  left_flat, TOCOL(left, 1),
  right_flat, TOCOL(right, 1),
  VSTACK(left_flat, right_flat)
)

計算量は left right のサイズを 𝑚、𝑛 として、Θ(𝑚 + 𝑛) 程度。left right2次元でも動作する。


INTERSECTION は、FILTERXMATCH を用ゐて、

INTERSECTION(left, right)
=LET(
  left_flat, TOCOL(left, 1),
  right_flat_sorted, SORT(TOCOL(right, 1)),
  TOCOL(IFNA(FILTER(left_flat, IFNA(XMATCH(left_flat, right_flat_sorted,, 2)))), 1)
)

と実装した。FILTER は返すものがないときは #N/A を返すので、IFNATOCOL を使って空の範囲[4]に置き換へてゐる。この実装の計算量は、left right のサイズを 𝑚、𝑛 として、Θ((𝑚 + 𝑛) log 𝑛) 程度となる[5]


配列の共通部分の計算には Θ(𝑚 + 𝑛) の実装がある:

function intersect<T>(xs: T[], ys: T[]): T[] {
  const yy = new Set(ys) // Θ(n)

  return xs.filter( // Θ(m)
    x => yy.has(x) // Θ(1)
  )
}
intersect.ts

Θ(1) の CONTAINS を用意して、XMATCH をそれで置き換へればよい[6]

スプレッドシートの関数だけでこれを実装することはできない[7]が、Apps Script に委譲すればうまくいく。多少のオーバーヘッドはあるが、計算量は変はらないだらう。

FORALL EXISTS

範囲内のデータが全て条件を満たすかどうか、あるいは範囲内に条件を満たすデータが存在するかどうかゞ知りたいこともある。

この操作は ARRAYFORMULA を使ふと効率良く行へる。たとへば、範囲 A2:A に素数が存在することを調べるには、OR(ARRAYFORMULA(ISPRIME(A2:A))) などゝする。計算量は、ISPRIME の計算量を Θ(𝑔(𝑘)) として、常に Θ(Σₖ 𝑔(𝑘)) となる[8]

MAP を使ふ方法もあるが、私の環境では ARRAYFORMULA を使った方法のはうが110倍ほど速く動作した。

もし名前付き関数にするなら、

FORALL(range, pred)
=AND(MAP(range, pred))
EXISTS(range, pred)
=OR(MAP(range, pred))

のやうにする。EXISTS(A2:A, ISPRIME) といふゝうに使ふ。MAPLAMBDA はラムダ関数でなければならないが、名前付き関数を LAMBDA 仮引数として渡すこともできる。ARRAYFORMULA の引数となる数式を範囲と条件に分けることは恐らくできない。

この FORALL EXISTS は名前と実装がほとんど同じで、名前付き関数として定義する意義が少ない。むしろ、専らスプレッドシートに慣れてゐる人にとっては、組み込み関数で書かれてゐるはうが分かりやすいだらう。

ARRAYFORMULAMAP22列以上の範囲も走査できる。便利な仕様だが、この機能性のせゐで、右に挙げた方法で行単位または列単位の条件の全称・存在性を調べることはできない。これを解決させるには BYROW BYCOL を使ふ。たとへば、範囲 A2:B に含まれる全ての行が双子素数のペアであることを調べるには、AND(BYROW(A2:B, ARETWINPRIMES)) とする[9]

REVERSE VREVERSE HREVERSE

範囲の反転は、

REVERSE(range)
=LET(
  empty_rows, TOCOL(, 1),
  REDUCE(empty_rows, range, LAMBDA(total, value, VSTACK(value, total)))
)

のやうに書ける。たゞし、REDUCE2次元の範囲を取れるので、この関数は、どんな形の範囲が渡されても、行優先で平滑化した上で、1列の範囲を返す。

そこで、行方向または列方向の範囲を反転させる方法を考へる。まづ、範囲がセル参照なら、行方向の範囲の反転は

FLIP(rows)
=SORT(rows, ROW(rows), false)

のやうに書ける[10]。この方法は、行番号をキーとして降順にソートすることで、行方向の範囲の反転を実現させてゐる。しかし、ROW はシート上の行番号を返すものなので、セル参照に対してしか動作しない。

また、この方針では列方向の反転も恐らく実装できない。SORT は行方向のソートしか行へないので、上の FLIP と同じやうに実装することはできないし、ROW はセル参照しか取れないので、=TRANSPOSE(FLIP(TRANSPOSE(cols))) みたいな実装もうまくいかない。

VLEN

これを解決させるには、ROW の代はりに SEQUENCE を使ふとよい。範囲のサイズを知る必要があるので、まづは VLEN を作る:

VLEN(rows)
=SUM(BYROW(rows, LAMBDA(_, 1)))

計算量は Θ(𝑛) である[11]


これを使ふと、VREVERSE

VREVERSE(rows)
=SORT(rows, SEQUENCE(VLEN(rows)), false)

と書ける。このバージョンはセル参照でない範囲に対してもうまく動作する。

HREVERSETRANSPOSEVREVERSE で実装できる:

HREVERSE(cols)
=TRANSPOSE(VREVERSE(TRANSPOSE(cols)))

UNMERGE

結合されたセルのデータはその結合の左上[12]のみにあり、そのやうなセルを含む範囲は、左上以外の位置にはデータを持たない。一方、市井では、セルの結合は結合されたセルが全て同じデータを持つことを表すと解釈されることが多い。ナンセンスな例だが、

A B C
1 BIG CELL meow
2 croak
3 coo buzz neigh

のやうなシート片に対して

=JOIN("
", BYROW(A1:C3, LAMBDA(row, JOIN(",", row))))

とすると、

BIG CELL,,meow
,,croak
coo,buzz,neigh

と表示され、B1 A2 B2 は空白であることが分かる。

かういふ表を結合のない範囲として扱ふために、結合されたセルを含む範囲に関して、結合されたセルを最も左上のデータで埋める関数 UNMERGE を作る。

スプレッドシートにはセルが結合されてゐるかどうかを知る方法が無いので、Apps Script を使用した。sueka/google-sheets-unmerge に置いてゐる。

この Apps Script をインストールして、

=UNMERGE("A1:C3")

とすると、

BIG CELL BIG CELL meow
BIG CELL BIG CELL croak
coo buzz neigh

のやうに表示される。

この関数の計算量は、範囲に含まれる結合されたセルのサイズの和を 𝑛 として、Θ(𝑛) 程度となる。

なほ、スプレッドシートにおける範囲は、Apps Script に渡されると2次元配列となり、結合に関する情報を失ってしまふため、UNMERGE(A1:C3) のやうにセル参照を渡して実行されるものは作れない[7:1]

TIMESPENT

数式の実行時間を計測するための関数 TIMESPENT を作った:

TIMESPENT(proc)
=LET(
  start_time, NOW(),
  _result, proc(),
  end_time, NOW(),
  TIMEDIF(start_time, end_time, "Ms")
)

proc は引数を取らない関数。LET は、value_expression の中でより左で宣言された name が使へるやうにするために、name value_expression ペアを左から順に逐次的に処理する。

多く LAMBDA を使って、TIMESPENT(LAMBDA(MATCH("Pikachu", B2:B))) といふゝうに使ふ。

TIMEDIF

TIMEDIF は、組み込みの DATEDIF に似せて、

TIMEDIF(start_time, end_time, unit)
=LET(
  diff, end_time - start_time,
  IFS(
    unit = "S", FLOOR(86400 * diff),
    unit = "Ms", FLOOR(86400 * 1000 * diff)
  )
)

のやうに実装した。ミリ秒のための unit"MS" ではなく "Ms" にしたのは、Seconds in Minute と誤解されないやうにするため[13]


  1. スプレッドシートでは「範囲」と「配列」はほゞ同じものを指す。 ↩︎

  2. 組み込みの関数はほゞ全て、可変長引数を取る部分には配列も渡せるやうになってゐる。 ↩︎

  3. UNIQUE は使はない。UNIQUE の計算量は Θ(𝑛 log 𝑛) もあるが、その割に嬉しいことが少ない。 ↩︎

  4. 空の範囲は、セルに直接出力すると #REF参照が存在しません。Reference does not exist)を返すが、セルに出力されない限り、サイズ0の範囲として振る舞ふ。 ↩︎

  5. SORT が最悪時間計算量が Θ(𝑛 log 𝑛) で済むアルゴリズムで実装されてゐることを期待してゐる。 ↩︎

  6. これは嘘だった。CONTAINS のみを Apps Script に委譲する場合、new Set(ys) 相当の処理が 𝑚 回ほど実行されることになる。配列から集合への変換は Θ(𝑛) を要するので、XMATCHCONTAINS で置き換へた実装の計算量は、良くて Θ(𝑚𝑛) となる。 ↩︎

  7. 多分。 ↩︎ ↩︎

  8. 𝑔(𝑘) がどういふ形をしてゐるかは分からないが、とにかく全て足し合はせた程度の時間を要し、途中で打ち切られない。念のために補足すると、仕様ではないので、タイミング攻撃への対策として利用しないこと。 ↩︎

  9. 範囲が分割できるなら、AND(ARRAYFORMULA(ARETWINPRIMES(A2:A, B2:B))) のやうにしても同じ結果が得られる。 ↩︎

  10. Flip Data Vertically - Google Docs Editors Community ↩︎

  11. 遅さうに見えるが、サイズのためのフィールドがない場合は妥当だと思ふ。 ↩︎

  12. 表示言語が RtL 言語でも同じ。 ↩︎

  13. DATEDIFunit"YM" "MD" "YD" といふ値を取ることがあり、これらはそれぞれ Months in YearDays in MonthDays in Year を意味する。 ↩︎