- 追加された行はこの色です。
- 削除された行はこの色です。
[[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"
)
**QUERY の結果と QUERY の結果を足し合わせる [#x010d329]
TODO
![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
