[[FrontPage]] *概要 [#k21c72c2] -システム化を待つ間にどうしても管理を Sheets でやらなければならないことがある -[[ベストプラクティスと典型を実践したシート>https://docs.google.com/spreadsheets/d/1JzGDtaDZ07TImm9MzZrGci8zGefUqgyYkpDQEC3QnVs/edit#gid=1885890544]] *ベストプラクティス [#ja7a9bf4] -Excel を使わず Google Sheets を使う(ARRAYFORMULA がない。選択肢をクラウドのみにする。) -Excel 芸をしない(見た目を気にしない) -生データのシートと計算式のシートは完全に分ける --どうしても面倒なら、生データは A:Z に押し込めて、計算式は AA:AZ などを利用する。 -異なる質の計算は AA:AZ や BA:BZ など、26 行ごとにパーティショニングしたところに押し込める。 -ARRAYFORMULA や QUERY や SCAN をなるべく使って、シート変更時の対象を 1 セルに限定する。 -適切に改行する、コメントアウトするなど読みやすくする。特に QUERY 関数。 -シートのサイズが実用上大きすぎる場合は、不要な行を削る(デフォルトの 1000 行が過大の場合。O(n^2) の計算がある場合は特に、高速になってサクサク動くようになる場合がある) *関数 [#dacf2618] **ARRAYFORMULA [#tb6ad105] -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 [#ia666b3b] -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&"'") -QUERY に渡す前に前処理をしたい場合、ARRAYFORMULA を使うと良い =ARRAYFORMULA(QUERY( {data!A2:A/data!B2:B}, "select Col1" ) ) **SCAN [#c6797195] -結合的演算のリダクション --=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) を生成する。 --累積和が簡単にかける *便利な知識 [#l9fe5ff8] **引数は空文字列を受け付ける [#ddb14f8f] -=IF(A1="",,B1) --,,が許される。A1 が空文字列なら空のセルになる。 **文字列結合 [#g4fe3976] -="A"&3 --& 演算子を使い、CONCATENATE, CONCAT は使わない(面倒なので) **ショートカット [#p2ecfdcf] -alt-e d d で現在行削除 -alt-i r で insert row -ctrl-yで繰り返し **便利な関数 [#l1cc3cba] -=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関数で取得するデータも自動的に更新される **コメントアウト [#fe76ddd7] -数値の説明 --=IF(0,"手持ち現金",200000) --その値が何を表しているかが明確になる -クエリの説明 "&IF(0,"コメント","")" =QUERY(A:C," select B * C "&IF(0,"この行は売上 * 利益率を計算している",)&" ") **空セルの削除 [#a010a9a8] -QUERY(raw_data!A2:A,"select * where Col1 is not null") **Join [#x3219435] - =ArrayFormula(SPLIT(FLATTEN(A1:A3 & "|" & TRANSPOSE(B1:B2)), "|")) --ARRAYFORMULA 内部で二次元配列を生成し、それを FLATEN で一次元化した後に、SPLIT で再度表化することで、CROSS JOIN ができる。 --[[公式の例にも乗ってる>https://support.google.com/docs/answer/10307761?hl=en]] --空のセルも JOIN しちゃうので、入力の時点で弾く工夫は必要。A2:A のうち空ではないものを入力したい場合、A2:A の代わりに QUERY(raw_data!A2:A,"select * where Col1 is not null") を入力すればよい **表の縦結合・横結合 [#p958624e] -=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 を二次元表にする一般的なテク [#i8da61db] -デフォルトでは 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" ) |