目新しい情報は無いと思いますが、自分への備忘録も兼ねて業務でよく使うスプレッドシート関数(一部Excel可)のまとめを簡潔に。詳細な情報は他で沢山まとまっているのでそちらをご参考に。随時更新。
IFS
(2019/2/5)
複数の条件を順に調べた結果に応じて異なる値を返す。従来のIF関数のネストを1つの関数で表せる。
=IFS(論理式1, 真の場合1, 論理式2, 真の場合2, ...)
SUMIF
条件に合う値の合計。
=SUMIF(範囲, 条件, 合計範囲)
SUMIFS
条件が複数ある場合。順番が異なる。
=SUMIFS(合計範囲, 範囲1, 条件1, 範囲2, 条件2, ...)
COUNTIF
条件に合う要素の個数。
=COUNTIF(範囲, 条件)
COUNTIFS
条件が複数ある場合。範囲と条件を並べる。
=COUNTIFS(範囲1, 条件1, 範囲2, 条件2, ...)
COUNTA
範囲内の値の個数。
=COUNTA(範囲)
VLOOKUP
QUERY関数を知ってからは頻度は下がったけどたまに使う。
=VLOOKUP(検索値, 検索対象範囲, 戻り列, 検索一致条件)
LEFT, RIGHT
(2018/10/23)
文字列の左端 （右端）から指定した文字数分の文字列を抽出する。
=LEFT(文字列, 文字数)
=RIGHT(文字列, 文字数)
FIND
(2018/10/23)
文字列から指定した検索文字列の一列を返す。
=FIND(検索文字列, 対象, 開始位置)
LEN
(2018/10/23)
文字列の長さを返す。
=LEN(文字列)
特定の文字列から前を抽出する
(2018/10/23)
セルA1にqiita.com/skouno25/items/0199871d21b20c84684cと記載されているとする。
=LEFT(A1,FIND("skouno25",A1)-1)
# 結果： qiita.com/
特定の文字列から後を抽出する
(2018/10/23)
=RIGHT(A1,LEN(A1)-FIND("skouno25",A1)-LEN("skouno25"))
# 結果： items/0199871d21b20c84684c
文字列から特定の文字列を抽出する
(2018/10/23)
文字列のパターンによって臨機応変に。
=RIGHT(LEFT(A1,FIND("/items",A1)-1),8)
# 結果： skouno25
=LEFT(RIGHT(A1,LEN(A1)-(FIND("qiita.com/",A1)+LEN("qiita.com/")-1)),8)
# 結果： skouno25
IFERROR
#N/A、#VALUE!みたいなエラーの場合に違う値を使うようにする。
=IFERROR(関数等, エラーの場合の値)
IMPORTRANGE
スプレッドシートのみ。他のスプレッドシートのデータをインポートできる。
=IMPORTRANGE("スプレッドシートURL", "シート名!範囲")
ARRAYFORMULA
スプレッドシートのみ。複数セル（配列）を対象に、1つの式を作成する（配列数式）。
同じ関数を繰り返し入力しなくて済むようになるので負担が減り動作が軽くなる。
=ARRAYFORMULA(配列数式)
(2019/2/5)
- IMPORTRANGEとは同時に使えない
- 複数条件を設定する際にANDやORは使えない
ANDやORを使いたい場合
(2019/2/5)
- AND：条件を ( ) に入力して * で連結させる
=ARRAYFORMULA(IF(($A1:$A<B$1)*($C1:$C>=D$1)),1)
- OR：条件を ( ) に入力して + で連結させる
=ARRAYFORMULA(IF(($A1:$A<B$1)+($C1:$C>=D$1)),1)
QUERY
スプレッドシートのみ。スプレッドシート上で気軽にクエリが使える。Excelでもクエリは使えるみたいだけど詳しく知らないのでパス。
これを知るまではVLOOKUPと他の関数組み合わせたりして無理やりやってたけど知ってからは超絶捗っている。ただちょっと癖があるので取扱注意。
=QUERY('データ範囲', "クエリ")
クエリの基本形
SELECT A WHERE 条件
-
SELECT *の場合は省略可 - 文字列を条件にする場合はシングルクォーテーションで囲む
- 列は
A, B, C, ...で指定する
条件にセルを参照する
-
"&で囲む
条件に日付を使う
date '2018-10-05'
セルを参照する
date '"& TEXT(A1, "YYYY-MM-DD") &"'
昇順と降順の並び替え
(2018/10/23)
=QUERY(A1:C6, "ORDER BY A ASC") # A列で昇順
=QUERY(A1:C6, "ORDER BY B DESC") # B列で降順
グルーピングして集計する
(2018/11/26)
集計関数 (avg, sum, max, min, sum)と使うことで集計をすることができる。
=QUERY(A1:C6, "SELECT A, 集計関数(B) GROUP BY A")
条件を指定する
WHERE句の位置に注意する
=QUERY(A1:C6, "SELECT A, 集計関数(B) WHERE B = 2018 GROUP BY A")
テーブルを結合して使う
=QUERY({A1:C6;E1:G6}, "WHERE Col1 > 1")
- データ範囲をセミコロンでつなげて中括弧で囲む
- 列は
Col1, Col2, ...で指定する
IMPORTRANGEと組み合わせる
=QUERY(IMPORTRANGE("スプレッドシートUR","シート名!範囲"), "WHERE Col1 > 1")