Google spreadsheetの関数リスト
spreadsheetをよく使う一年だったので振り返ってよく使っていた関数をまとめておきます。
ちょっとずつ更新していきます。
一覧
- importrange
- today
- eomonth
- text
- if
- iferror
- countif, countifs
- sumif, sumifs
- countunique
- vlook
- query
- lambda
- arrayformula
- map
IMPORTRANGE(spreadsheet_url, range_string)
URLがhttps://docs.google.com/spreadsheets/d/abc/edit#gid=xyzのスプレッドシートにおいてシート名がexapmleのA1からD5までのデータを得るときには下の関数を使う
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc/edit#gid=xyz","example!A1:D5")
TODAY()
今日の日付が得られる
=TODAY() //`yyyy/mm/dd`
EOMONTH(開始日, 月数)
今月末の日付を得るには下の関数を使う
=EOMONTH(today(), 0)
TEXT(数値, 表示形式)
指定した表示形式に従って、数値をテキストに変換する。
=TEXT(1.23, "$0.00") // $1.23
QUERY(データ, クエリ, 見出し)
カスタムしてデータを抽出、出力することができる。
下で出てくるmapやarrayformulaの内部で使用したり、importrangeで撮ってきたデータを好きな順序で表示するのに使っていた。
データシートにおいてB列がB1と一致する行のH, I, N, L列の値を出力する
=query('データ'!A:Z, "select H, I, N, L where B='"&B1&"'", 0)
LAMBDA(名前, 数式)
カスタム関数を作成する。下のmapと一緒に使っていた。
salaryに0.3をかける関数を作成し、C5をsalaryに代入している。
=LAMBDA(salary, salary*0.3)(C5)
ARRAYFORMULA(配列数式)
ある列に対して同じ処理をなん度もしたいときに使っていた。
下の例では、B列の値が空欄でないときにcountifsの結果が出力される。
A列の値を見ていって、それぞれの値と会員データのL列の値が一致する回数をそれぞれ求めて、配列として出力する。
=ArrayFormula(IF(B2:B="","", countifs('会員データ'!$L$3:$L,$A2:A)))
MAP(配列1, [配列2, ...], LAMBDA)
配列をlambda関数の引数として処理を行い、結果を出力する。
arrayformulaを使えないときに使用していた。
下の例では、A2から順に値を読み込んでlambda内の処理を行って出力することをA13まで繰り返していく。
lambda内でA列から読み込んだ値はvalueとしてEXAMPLEのD2:Dが'value'と等しい時のC列の値の総和を返す。
=map(A2:A13, lambda(value,sumifs('EXAMPLE'!$C2:C, 'EXAMPLE'!$D2:D, "="&value)))
参考