ちょっとした集計をお助けしてくれた関数たち。
なのになぜか毎回引数の入れ方調べたりしてるなと思ったので備忘録としてまとめます。
集計は全部クエリでやらずとも、スプレッドシートに貼って関数で成形、も小技として有効です。
①VLOOKUP関数
構文
VLOOKUP(検索キー, どの範囲から検索するか, 右何列目を返すか,完全一致ならFALSE)
表から、特定のセルの値と一致するレコードの値を抜き出すための関数です。
関数て便利だな～って最初になるやつ。
TRUEかFALSEかこんがらがる人は、集計でTRUE使うことはほぼないのでFALSE固定で覚えておくとよいかなと。(玄人の方、TRUEの活用術あったら教えてください、、)
追記：TRUEの用途、活用の仕方について大変わかりやすくコメントいただきました！ありがとうございます！
②INDIRECT関数
構文
=INDIRECT（参照セルの文字列） =INDIRECT（シート名!セル名）
=VLOOKUP($B3,INDIRECT("'"&D1&"'!"&"A:B"),2,FALSE) ※セルD1に参照したいシートの名前を入れる
セル名を指定して値を取ってこれる関数。
別シートを指定することも可能(別URLのシートを指定することもできる)
①のVLOOKUPと組み合わせると1シート目はスッキリ、2シート目(もしくは別URL)に集計用データを置いた集計データが作成できる。
③COUNTIF関数
構文
=COUNTIF(範囲,条件)
=COUNTIF(B17:B,A3) セルA3の値がB17:Bの範囲にいくつあるか
範囲を指定して条件に当てはまる数を計上する
④COUNTIFS関数
構文
=COUNTIFS(範囲1, 検索条件1, 範囲2, 検索条件2, ...)
カンマで区切って複数条件指定できる
⑤COUNTUNIQUE関数
構文
=COUNTUNIQUE(範囲)
=COUNTUNIQUE(B4:B13)
範囲内のユニークな値の数を計上
②INDIRECT関数と組み合わせることで別シートの表から重複削除して計上することができる
COUNTA(カウントエー)関数は重複削除せず引数の値の個数を計上する関数なのでCOUNTUNIQUEとはちょっと違いますね
⑥COUNTUNIQUEIFS関数
構文
=COUNTUNIQUEIFS関数(重複を省いて計上したい範囲,条件に使う範囲,条件)
最終形態
⑦SWITCH()
GoogleスプレッドシートでもSWITCHが使えるって皆さんご存じでしたか？
私は最近知って感動しました。
特定の値が入ることが分かっているセルの振り分けに便利です。
私は所在地をエリアに分けるときに活用しています。
⑧Arrayformula関数
構文
=Arrayformula(適用させたい条件文)
条件文の範囲を一つのセルではなく適用させたい範囲を指定することでオートフィルしなくても範囲内全てに反映できるすごい関数
Tips
意外と教わる機会がない
ctrl + D オートフィル
太い十字をピーッてしなくてよくなります
F4 「絶対参照」「相対参照」切替してくれるやつ
F4で絶対参照にしておけば、後は↑のオートフィルするだけ
検索条件で使える引数 * ?
* 任意の文字列 "*県" 秋田県 和歌山県
? 任意の1文字
vlookupでなぜか一致が取れないときは
スプレッドシート上での文字列の形式があってないのかもしれません
書式なしテキストでそろえましょう！
おすすめの関数、小技、教えてください