Google Sheets のバックアップ(No.10)

• バックアップ一覧
• 差分 を表示
• 現在との差分 を表示
• ソース を表示
• Google Sheets へ行く。
  • 1 (2017-01-16 (月) 11:14:38)
  • 2 (2019-12-15 (日) 00:46:23)
  • 3 (2019-12-20 (金) 15:29:42)
  • 4 (2020-03-23 (月) 20:19:39)
  • 5 (2020-04-25 (土) 17:23:08)
  • 6 (2023-09-24 (日) 17:37:36)
  • 7 (2023-09-25 (月) 15:49:31)
  • 8 (2023-09-28 (木) 06:45:57)
  • 9 (2023-10-01 (日) 12:46:50)
  • 10 (2023-10-03 (火) 04:55:09)
  • 11 (2023-10-11 (水) 10:52:33)
  • 12 (2023-10-12 (木) 13:17:25)
  • 13 (2023-10-14 (土) 23:56:41)
  • 14 (2023-10-23 (月) 00:17:39)
  • 15 (2023-12-19 (火) 20:11:02)
  • 16 (2023-12-20 (水) 06:31:11)
  • 17 (2023-12-27 (水) 13:49:24)
  • 18 (2024-01-30 (火) 01:57:39)
  • 19 (2024-02-09 (金) 14:27:37)
  • 20 (2024-02-12 (月) 01:13:02)
  • 21 (2024-02-12 (月) 11:40:37)

FrontPage

概要 †

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

ベストプラクティス †

• Excel を使わず Google Sheets を使う（ARRAYFORMULA がない。選択肢をクラウドのみにする。）
• Excel 芸をしない（見た目を気にしない）
• 生データのシートと計算式のシートは完全に分ける
  • どうしても面倒なら、生データは A:Z に押し込めて、計算式は AA:AZ などを利用する。
• 異なる質の計算は AA:AZ や BA:BZ など、26 行ごとにパーティショニングしたところに押し込める。
• ARRAYFORMULA や QUERY や SCAN をなるべく使って、シート変更時の対象を 1 セルに限定する。
• 適切に改行する、コメントアウトするなど読みやすくする。特に QUERY 関数。
• シートのサイズが実用上大きすぎる場合は、不要な行を削る（デフォルトの 1000 行が過大の場合。O(n^2) の計算がある場合は特に、高速になってサクサク動くようになる場合がある）

関数 †

ARRAYFORMULA †

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

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

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&"'")

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 は使わない（面倒なので）

ショートカット †

• 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関数で取得するデータも自動的に更新される

コメントアウト †

• 数値の説明
  • =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") を入力すればよい