Google Sheets

FrontPage

概要 †

• システム化を待つ間にどうしても管理を Sheets でやらなければならないことがある
• ベストプラクティスと典型を実践したシート

ベストプラクティス †

• Excel を使わず Google Sheets を使う（ARRAYFORMULA がない。選択肢をクラウドのみにする。）
• Excel 芸をしない（見た目を気にしない）
• 生データのシートと計算式のシートは完全に分ける
  • どうしても面倒なら、生データは A:Z に押し込めて、計算式は AA:AZ などを利用する。
• 異なる質の計算は AA:AZ や BA:BZ など、26 行ごとにパーティショニングしたところに押し込める。
• ARRAYFORMULA や QUERY や SCAN や BYROW をなるべく使って、シート変更時の対象を 1 セルに限定する。
  • 全セルに数式を入れるのに比べて、更新が定数倍高速化される（体感 10 倍）。多分、「セルを実行する」という処理を行う回数が n 回から 1 回に減るから
  • 複数行を更新する式を書く時は、A:A のように終端を開いておかず、A:A1000 のように限定する。また、式の実装中には、A:A10 など小さな範囲でプロトタイプする（そうしないと、10000 行更新されたり挿入されたりして非効率）
  • 参照先が 1 個もしくは全部の単純なリダクション (SUM, MIN, MAX, COUNTIF)なら、BYROW を使う（損がないので）。そうでない場合、BYROW は列の位置を指定しての参照が INDEX(1, 2) などになって可読性が下がるので、ARRAYFORMULA を使うことを検討する。
• 適切に改行する、コメントアウトするなど読みやすくする。特に QUERY 関数。
• シートのサイズが実用上大きすぎる場合は、不要な行を削る（デフォルトの 1000 行が過大の場合。O(n^2) の計算がある場合は特に、高速になってサクサク動くようになる場合がある）
• 上は 2 行以上固定する（ARRAYFORMULA や BYROW は、行の並び替えをしても同じ位置にいなければならないため、固定行に置かなければいけない。そして、固定行が 1 行だけだと、ARRAYFORMULA や BYROW を書く行で埋まってしまい、その列が何を表示しているかを表す説明文が書けなくなるので）

型 †

• 全ての基本、これがわからないと試行錯誤することになる
• text
  • 日本語では「書式なしテキスト」とも呼ばれる。型 ID は 2。デフォルトで左詰め
• boolean
  • 型 ID は 4。デフォルトで真ん中合わせ
• array
  • 型 ID は 64。
  • Sheets の array は 2d array。n x m 行列しか認めずそれ以外は error を返す。空 array は error を返す。
  • {1;2}で縦結合, {1,2}で横結合
  • array は、セルに入れるとそこから展開される。展開後の値は、配列型ではなく展開後の値の型になる。
• error
  • タイプは 16。
• 重要！！！範囲タイプは存在しない（A:B は型ではなく、範囲への参照にすぎない）。
  • array との混同に本当に注意。range はポインタ、array はインスタンスのようなもの。範囲をセルに埋めると、範囲を全て埋めるのではなく、一番始めの値だけが単一のセルに埋まる。よって、=TYPE(A:A) は A1 の型が返り、Type 64 は返らない。
  • 配列を受け取る関数は、範囲が引数に入力された場合、その範囲を実体化した配列を受け取ったものとみなして計算する。
    • したがって、array query(array, text) は入れ子にできる。

関数 †

BYROW †

• 重要: array BYROW(range, lambda)

• 範囲を与えて値を返す関数を LAMBDA として与えると、各行ごとに LAMBDA を適用してくれる
  • 各行の合計: =BYROW(A1:Z100, LAMBDA(r, SUM(r)))
  • それぞれの行で左から 3 番目の要素に 2 をかけたものを抽出する。=BYROW(A1:Z100, LAMBDA(r, INDEX(r, 1, 3) * 2))
  • array 型を返却するので、ARRAYFORMULA との相性もよい。ARRAYFORMULA(IF(A1:A100="",,BYROW(A1:Z100, LAMBDA(r, SUM(r)))) で A 列が空だったら空白セルとして埋める
• LAMBDA(r, max(r)) などとしたときの r が変数ではなく関数として自動補完が効いてしまうので、数式入力が地味に面倒。
• 後述の ARRAYFORMULA の機能的な上位互換。ただ、ARRAYFORMULA は内部に SUM, MAX などを使うことはできず、内部でのクエリ実行や GAS での自前関数の実行も不安定だが、ARRAYFORMULA では列の参照を列名で行うことができて可読性が上がるというメリットがある。

• BYROW と BYCOL と一緒に使って、内部でクエリ可能な pivot table を作成することもできる。
  • 下記は、auto r : A1:A6 を縦に、auto c : B1:B4 を横にして、それぞれの r, c についての func(r, c) = join(",",{r,c}) を二次元の表として記述する式。func(r, c) = r * c などにすれば、テンソル積みたいになる。
  • この表記で重要なのは、r, c を使った QUERY を内部に入れることができること。ARRAYFORMULA や QUERY の group by A pivot B では実現できない柔軟性を持っている。

    =BYROW(A1:A6, LAMBDA(r, BYCOL(TRANSPOSE(B1:B4), LAMBDA(c,
      join(",",{r,c})
    ))))

ARRAYFORMULA †

• 重要: array ARRAYFORMULA(function(array1, array2, ...))
• 列の参照が簡単な BYROW
  • BYROW ではなくこちらを使うと可読性が高くなる。例えば BYROW での A:B 列範囲を元とした BYROW の LAMBDA(r, ...) 内での B 列の参照は、INDEX(1, 2) とかしないといけなくて面倒。
• ARRAYFORMULA は BYROW の機能的な下位互換であることには注意が必要。ARRAYFORMULA でつらくなったときに、ARRAYFOMULA で頑張ろうとするのではなく、まず初手で BYROW への切り替えを検討すること
  • ARRAYFORMULA にできないこと
    • 各行で、ある行範囲の SUM, MAX, MIN をとる
    • 各行で、行の一番左の列をキーとして、そのキーが別の範囲に何個含まれるかを判定する（要するに ARRAYFORMULA 内部でクエリを実行できないことがある）
    • 簡単に作った GAS の自前関数を列ごとに呼び出し（シグネチャが云々と言われてエラーになる。BYROW なら何も考えずにできる。）

• ARRAYFORMULA は、それぞれの配列の要素ごとに演算を行う。ARRAYFORMULA 内に含まれる配列サイズ（n x m 配列の形）は全て同じでない場合、あぶれた範囲はエラーになる。
• ARRAYFORMULA(function(A:A,B:B))
  • array[i] = function(A[i], B[i]) を生成する。

• ARRAYFORMULA(function(A:A,TRANSPOSE(B:B)))
  • array[i][j] = function(A[i], B[j]) を生成する。

QUERY †

• 重要: array ARRAYFORMULA(array, "query")
• QUERY内参照
  • "&A1&"
    • 一旦クエリを切って、& 演算子で concat している。
    • "&A1&"が文字列の場合には、QUREY文内部では'で囲う必要がある。'"&A1&"'とするとそこにクオーテーションなしで展開される
    • =QUERY('仕訳表'!\( A \)3:$Z, "select D, month(A)+1 where month(A)+1="&A3&"", true)
    • =QUERY(\( A: \)H, "select sum(C) where F='"&\( I \)1&"'")
  • CONCATENATE("select A '", A1, "'")
    • クエリが複雑になった場合、上記だと可読性が悪くなるのでこっちを使うほうがよい場合も。

• A, B などの絶対指定の他、Col1, Col2 (左から i 列目) で相対参照することができる。
  • 混用不可、Col1 は case-sensitive であることに注意！(col1 や COL1 は動作しない)
  • QUERY の結果などは相対参照しかできない

• where A is not null や where Col1 is not null はほぼどんなときでも入れたほうがいい
  • A:Z のような指定をした場合、そのシートの空列も含めた全行を取ってきてしまい、計算量が実データではなくシートサイズに依存してしまい、計算時間が悪くなる

• QUERY に渡す前に前処理をしたい場合、ARRAYFORMULA を使うと良い

=QUERY(
    ARRAYFORMULA({data!A2:A/data!B2:B}),
    "select Col1"
)

SCAN †

• 結合的演算のリダクション
  • =SCAN(0, B2:B, LAMBDA(accumulated_value, cell_value, accumulated_value + cell_value))
  • table[0] = initial_value, table[i+1] = function(table[i], value[i]) (i >= 0) を生成する。
  • 累積和が簡単にかける

便利な知識 †

引数は空文字列を受け付ける †

• =IF(A1="",,B1)
  • ,,が許される。A1 が空文字列なら空のセルになる。

文字列結合 †

• ="A"&3
  • & 演算子を使い、CONCATENATE, CONCAT は使わない（面倒なので）

ショートカット †

• enter enter で下のセルに移動
• shift enter enter で左のセルに移動
• tab で右のセルに移動
• shift tab で左のセルに移動
• alt-e d d で現在行削除
• alt-i r で insert row
• ctrl-yで繰り返し

便利な関数 †

• =ADDRESS(ROW(), COLUMN(), 4)
  • 現在のセルのアドレスが相対参照で得られる。F20みたいな（4は相対参照で、1とかだと固定がはいる）
  • なので、=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")でカラムのアルファベットが取れる。

• =INDEX(QUERY(A:A, "select count(*)"), 2, 1)
  • クエリ結果をワンラインで取得
  • COUNT などのクエリの agg 関数と相性がいい

• IMPORTRANGE
  • 最終手段（なるべく一つのシートに収め、極力使わない）
  • Importrange関数は、他のシートからデータを持ってくるときに使う。元のデータのあるシートが更新されると、Importrange関数で取得するデータも自動的に更新される

• ADDRESS, INDIRECT
  • ADDRESS: 行・列表記を入力して、セル番地を返す。=ADDRESS(1,1) で「\( A \)1」を文字列で返します
  • INDIRECT: セル範囲を入力して、その範囲の値の array を返す。=INDIRECT(ADDRESS(1, 1)), =INDIRECT(ADDRESS(1, 1)&":"&ADDRESS(7, 1) など
    • INDIRECT は関数を入れられない。できるのは単セルもしくはセル範囲のみなので、関数を使いたいなら関数の中にINDIRECT を入れる必要がある

コメントアウト †

• 数値の説明
  • =IF(0,"手持ち現金",200000)
  • その値が何を表しているかが明確になる
• クエリの説明 "&IF(0,"コメント","")"

=QUERY(A:C,"
  select B * C "&IF(0,"この行は売上 * 利益率を計算している",)&"
")

空セルの削除 †

• QUERY(raw_data!A2:A,"select * where Col1 is not null")

Join †

• =ArrayFormula?(SPLIT(FLATTEN(A1:A3 & "|" & TRANSPOSE(B1:B2)), "|"))
  • ARRAYFORMULA 内部で二次元配列を生成し、それを FLATEN で一次元化した後に、SPLIT で再度表化することで、CROSS JOIN ができる。
  • 公式の例にも乗ってる
  • 空のセルも JOIN しちゃうので、入力の時点で弾く工夫は必要。A2:A のうち空ではないものを入力したい場合、A2:A の代わりに QUERY(raw_data!A2:A,"select * where Col1 is not null") を入力すればよい

表の縦結合・横結合 †

• =QUERY({'シートA'!A:F;'シートB'!A:F})
• =QUERY({'シートA'!A:F;'シートB'!A:F},"SELECT Col1,Col2 WHERE Col6 ='対象'")
• 縦に結合したい場合のカンマは「;」横に結合したい場合のカンマは「,」
• クエリの条件指定はA、BではなくCol1,Col2....
• トラブルシューティング
  • ARRAY_LITERAL の配列リテラルで、1 つ以上の行の値が見つかりませんでした。→縦結合の場合の列数が違う（例：QUERY({'シートA'!A:F;'シートB'!A:E})）
  • 関数 ARRAY_ROW のパラメータ 2 に一致しない行サイズがあります（XXXX となるべきところが XXXX になっています）。→横結合の場合の行数が違う（例：QUERY({'シートA'!A100:F100,'シートB'!A50:F50})）
  • 関数 QUERY のパラメータ 2 のクエリ文字列を解析できません: NO_COLUMN: A→条件指定がColの記載になってない（例：QUERY({'シートA'!A:F;'シートB'!A:F},"SELECT A"）

AGG(C) GROUP BY A, B を二次元表にする一般的なテク †

• デフォルトでは FLATTEN されてしまうが、QUERY を使うと二次元表に AGG できる。
• 基本

=QUERY(
    {data!A2:A, data!B2:B, data!C2:C},
    "select max(Col3) where Col1 is not null group by Col1 pivot Col2"
  )

QUERY の結果と QUERY の結果を足し合わせる †

• ARRAYFORMULA(QUERY(...) + QUERY(...)) で array 同士を足し合わせることができる
• QUERY({QUERY(...), QUERY(...)}, "select Col3 + Col6") などでも OK

TODO †

• BYROW の二次元版というか、配列 a, b が与えられて、pivot table func(a[i], b[j]) を生成する一般的なテクってないのだろうか。
  • BYROW の中に BYCOL を入れる？

• 変数化
  • variable!\( A \)2 などは可読性が悪い。getVariable("max_number_of_contractor") などにしたい

Google Apps Scripts †

• https://gas.excelspeedup.com/gas-spreadsheet-speedup/