スプレッドシートチートシート
日付関連
1カ月前
EDATE(A1, -1)
EDATEの公式解説
1カ月後
EDATE(A1, 1)
月間日数
DAY(EOMONTH(A1, 0))
A1セルの日付の月末を取得し、その日を取得することで月間日数を取得している
EOMONTHの公式解説
DAYの公式解説
正規表現
正規表現と一致するか
REGEXMATCH(B1, "チェック")
第1引数がチェック対象
第2引数が正規表現
js だと /チェック/.test(b1)
となる
[REGEXMATCHの公式解説](https://support.google.com/docs/answer/3098292?hl=ja&sjid=13861782736637561237-AP
範囲操作関数
FILTER
フィルタ関数
範囲から条件に一致する部分を取得し展開する
サンプル
=FILTER(
'別シート'!A2:K,
('別シート'!I2:I>=DATE(2023,1,1))+('別シート'!I2:I=""),
('別シート'!A2:A<>"")
)
説明
別シートのA2:Kを展開する (2行目)
条件は 1、2を両方満たす行
- I列が2023/01/01以降 または 空 (3行目)
- A列が空ではない (4行目)
FILTERの公式解説
SORT
範囲の順番を並び替える
=SORT(
'別シート'!A2:K,
'別シート'!E2:E,
TRUE)
例だと別シートのA2:K
を別シートのE列
で昇順並び替えている
降順で並び替えたい場合は最後のTRUEをFALSEにする
SORTの公式解説
集約関数
COUNTIFS
複数条件のカウント
=COUNTIFS(
$B$2:$B,
">="&DATE(2023, 4, 1),
$B$2:$B,
"<"&DATE(2023, 5, 1)
)
例だと$B
条件は
$B
※同じデータに複数の条件を設定できる
COUNTIFSの公式解説
SUMIFS
複数条件付きの合計
=SUMIFS('別シート'!A:A, '別シート'!B:B, "<>", '別シート'!C:C, ">0")
例だと範囲が別シートのA列を合計している
条件は 1、2を両方満たす行
- B列が空ではない
- C列が0より大きい
QUERY
範囲を縦方向に結合する
SQLでいうところのUNIONに相当する関数
=QUERY({
{'シート1'!A:C};
{'シート2'!A2:C}
},
"where Col1 is not null"
)
サンプル説明
シート1
はヘッダ行を含む全行が対象 (2行目)
シート2
は2行目から下が対象 (3行目)
a列がNULLの行は削除される (5行目)
イメージ図
シート1
a列 | b列 | c列 |
---|---|---|
1 | 2 | 0.5 |
3 | ||
0 | 4 |
シート2
a列 | b列 | c列 |
---|---|---|
4 | 5 | 7 |
実行結果
a列 | b列 | c列 |
---|---|---|
1 | 2 | 0.5 |
0 | 4 | |
4 | 5 | 7 |
範囲を展開しつつ各セルに計算結果を表示する関数
ARRAYFORMULA
使用例
=ARRAYFORMULA(IFS(
A:A="","",
ROW(B:B)=1, "[タイトル]",
true, IFERROR(VLOOKUP($A:$A,'別シート'!$A:$D,4,false))
))
サンプル説明
行番号が1の場合、[タイトル]を表示してます (2行目)
A列が空の行はVLOOKUPを実行しないようにしてます (3行目)
A列の値を元に別シートのA列を検索して一致した行のD列の値を返しています (4行目)
MAP, LAMBDA
使用例
=MAP(
B2:B, C2:C,
LAMBDA(b, c, IF(
b <> "", "",
b <> 0, "",
c / b
)))
実行結果
MAP関数とLAMBDA関数を合わせてB列とC列の該当行の数値で C列 / B列 の数値を計算する
b列 | c列 | d列 | 説明 |
---|---|---|---|
1 | 2 | 0.5 | c / b の計算結果 |
3 | b列が空なのでd列も空 | ||
0 | 4 | b列が0なのでd列も空 |
=MAP(
B2:B, C2:C, D2:D, E2:E
LAMBDA(b, c, d, e, AVERAGE(b, c, d, e))
)
Discussion