FrontPage

概要

ベストプラクティス

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

関数

関数は空文字列を受け付ける

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

文字列結合

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

ショートカット

  • alt-e d d で現在行削除
  • alt-i r で insert row
  • ctrl-yで繰り返し

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) を生成する。
    • 累積和が簡単にかける

便利関数

  • =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,"この行は売上 * 利益率を計算している",)&"
")

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") を入力すればよい

トップ   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS