Spreadsheet
テクまとめていく
TODO これ横持ち→縦持ちにできてすごいけどよくわかってない
挿入ショートカット
Ctrl + Option + I(nsert) で開く
R で上に、B で下に1行
行/列 削除
Cmd+Option+-
日付ショートカット
Command+; で現在の日を yyyy/MM/dd で
Command+Shift+; で現在時刻(UTC)を h/m/s で
範囲を参照
={other!E:E} のような数式で展開できる({ もそのまま入れる)
={other!E2:E} のようにカラム名を除いたり、TRANSPOSE で横にして総当たり表を作ったりも
; で ={A:A;B:B} のように縦に繋ぐこともできる、, は ={A:A,B:B} 横に結合
すぐ IMPORT なんとかしてしまいそうになるが外部のスプレッドシートを参照しない場合必要はない
TOCOL
似たケースで地味に便利、雑な範囲を入力して並べられる、そして当然 TOROW もある
VLOOKUP で範囲の右側で検索 / 左側を返す
↑の関連、VLOOKUP(search_key, range, index, [is_sorted]) において index は正の値を取るので、range の左端でしか検索できないが、range に並び替えた範囲を渡せば擬似的に列を入れ替えられる
=VLOOKUP("hoge", {B:B, A:A}, 2, FALSE) などすると、B列で検索してA列の値を取れる
Command + Shift + ↓ でデータのある下まで選択
Command + ↓ で一番下まで移動 & Shift の選択
でもオートフィルは使えない、なんかないのか?
range クエリパラメータ
範囲していの URL を作れる &range=B205 など
右クリックでリンクを取得してもよい
https://gyazo.com/b38aeda159d674a1960f37dc9df64e3e
番号からアルファベット
ADDRESS で参照をゲットして、INDIRECT で座標にして文字列加工するなど複雑な方法はいろいろあるけど
A~Z の範囲なら ascii コードで作る CHAR(64 + {NUM}) が楽
データのあるセルだけ JOIN
JOIN(separator, range) でなく TEXTJOIN(separator, ignore_blank, range) を使う
QUERY の条件をくっつける
MATCH でカラム番号をゲットして、番号からカラムのアルファベットを作って D='ほげ' のような条件文字列を作り
=QUERY(範囲, "SELECT * WHERE " & TEXTJOIN(" AND ", TRUE, 条件のリスト))
名前付き範囲
なんかシートを装飾したりヘッダ部分に情報をごちゃごちゃ書いているタイプだと、データ部分に名前付き範囲を作ると便利
例えば data という名前で sheet!C5:Z (A,B 列が余白で、4行目まで文章が書いてあるとして)のような範囲を作っておけば =QUERY(data, "SELECT ...") のようなのが楽になるし都度選択しなくてよくなる
条件付き書式 & カスタム数式
ROW() などセル参照の命令で塗られうる自身のセルをとれるから、INDIRECT とか ADDRES とか組み合わせると相対位置基準で色塗ったりできる
例: 前日(相対的に左上のセル)が日曜の姉の色を塗る =EXACT(INDIRECT(ADDRESS(ROW() - 1 , COLUMN() - 1)), "日")
https://gyazo.com/2023fd20b8ce38e7e7eaf8272efc6bc4
同じ行や列を対象であれば、適当なセルを対象に設定したものが他の行でもいい感じに動く
例: A列全体を選択して、=XOR($E1,$F1,$G1) などを入れると 2行目移行は E2,F2,G2 を参照してくれる
条件付き書式の対象範囲の先頭を基準にしているので、範囲と参照の行やセルをずらして参照することもできる
上と同様のはこう書けばより簡単、後で見た時にわかりづらいのであまりやりたくはない
(範囲がB2:Bで、$A1 を参照しているので、B3はA2 を参照, ... B10 は A9 を参照して色を塗れる)
https://gyazo.com/7385b6fec8cd19ed9585c562858ba598
条件付き書式で別シートを参照する
=A1<>INDIRECT("'比較用マスタ'!"&CELL("address", A1))
条件付き書式内で A1 のような参照は変数のように扱われて範囲内の全セルに作用する
一方でシート込みの参照 '比較用マスタ'!A1 は変数のように作用しないので、A1 として変数のように作用させつつ INDIRECT & CELL で参照先を組み立てる、という話(におもえる)
INDIRECT とか
INDEX
INDIRECT
ADDRESS
ROW
COLUMN
ISBLANK とか
code:kanren
ISBLANK: 参照セルが空白であるかどうかを検証します。
ISTEXT: 値がテキストであるかどうかを検証します。
ISREF: 値が有効なセル参照であるかどうかを検証します。
ISNUMBER: 値が数値であるかどうかを検証します。
ISNONTEXT: 値がテキストでないかどうかを検証します。
ISNA: 値が「#N/A」のエラー値であるかどうかを検証します。
ISLOGICAL: 値が TRUE であるか FALSE であるかを検証します。
ISERROR: 値がエラーであるかどうかを検証します。
ISERR: 値が「#N/A」以外のエラーであるかどうかを検証します。
改行文字
文字列結合したい際に \n などしてもだめ、CHAR(10) などで作る
=TEXTJOIN(CHAR(10), TRUE, sheet!A1:A10)など、第2引数によって空のセルを除いて改行で結合したりのユースケース
テキストを列に分割
ペーストしたあと : で分割したり、任意の文字列で分割したりできる、対話的にできるのもよい
分割したら展開される右側のカラムは上書きされるのは注意
カンマ だと ,(0x2C) だけでなく 、(0xE3, 0x80, 0x81) で切られたりするので カスタム で , を指定している
https://gyazo.com/0f18039299544b0afe75ef47417487d2
セル幅の自動調整
セルとセルの間をダブルクリック
高さを指定してあると、折返しなどでセルの高さが変わらないが自動調整(デフォルト)に戻すために使う
https://gyazo.com/eeb3b4128feb1e9944acfd1f1ec5aa28
ピボットテーブルで、行/列平均に対する割合を求める
計算フィールド
数式: AVERAGE(...)
ここに元テーブルのカラムの名前を使える
集計: 行/列集計に対する割合
https://gyazo.com/73ce04e30e69d12da7f07b1c041cc5ee
QUERY で日付をクエリできないやつ
"SELECT * WHERE D = date '"& TEXT(TODAY(), "YYYY-MM-DD") &"'"
のように、YYYY-MM-DD 形式にする & date を前につける
空の行を探す, 行のサイズを得る
=MATCH(TRUE,ARRAYFORMULA(ISBLANK(SHEET!A2:A)),0)
関数で範囲の行を並び替える
SORT + RANDARRAY
=SORT(SHEET!A2:A10, RANDARRAY(ROWS(SHEET!A2:A10), FALSE)
範囲の末尾が不定なら、空の行を探すのと組み合わせて
INDIRECT("SHEET!A2:A" & MATCH(TRUE,ARRAYFORMULA(ISBLANK(SHEET!A2:A)),0))
みたいに範囲を参照するけど、めんどい
営業日
日付入力欄
入力規則で日付にすると空のセルもダブルクリックで datepicker が出る
ARRAYFORMULA シリーズ
=ARRAYFORMULA(ROW(A1:A1000))
連番振りたいけどドラッグがダルい時に ARRAYFORMULA で生成する
自身の行番号を取得してまとめて入れる、なんかズラしたり割って余りで map したりしてもよい
https://gyazo.com/1d8922508365905b37ac704b6a8c3d4c
=ARRAYFORMULA(IF(ISBLANK(master!A:A), 0, master!A:A))
master!A 列が空ならデフォルト値(0)を埋めて別のカラムへそのまま移す
=ARRAYFORMULA(IFERROR(VLOOKUP(bigdata!C:C, master!$A:$B, 2, false),0))
bigdata!C 列をキーとして master!A -> master!B のマップを引く、見つからなければ IFERROR で 0 にする
SUM in ARRAYFORMULA
もともとの SUM が Range を取るので、単に SUM を繰り返したい場合は使えない
=ARRAYFORMULA(B2:B+C2:C+D2:D+E2:E+F2:F+G2:G) のようにするのが簡単(簡単か?)
SUMIF や MMULT を使うテクもある
Export
https://docs.google.com/spreadsheets/d/{spreadsheet_id}/export?format=csv にアクセスすることでエクスポートできる