Zenn
👏

スプレッドシートチートシート

2024/07/10に公開
チートシート
スプレッドシート
関数
tech

日付関連

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を両方満たす行

  1. I列が2023/01/01以降 または 空 (3行目)
  2. 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)
)

例だと$B2:Bでデータが入力されているセル数をカウントしている
条件は
$B2:Bの中で2023年4月1日～2023年4月末のデータ

※同じデータに複数の条件を設定できる
COUNTIFSの公式解説

B列が空白以外の例

=COUNTIFS(
$B$2:$B,
"<>"
)

SUMIFS

複数条件付きの合計

=SUMIFS('別シート'!A:A, '別シート'!B:B, "<>", '別シート'!C:C, ">0")
例だと範囲が別シートのA列を合計している
条件は 1、2を両方満たす行

  1. B列が空ではない
  2. C列が0より大きい

SUMIFSの公式解説

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行目)

ARRAYFORMULAの公式解説

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))
)

MAPの公式解説
LAMBDAの公式解説

Discussion