はじめに
前回投稿させていただいた運転日報の記事が思った以上に反響があり踊りきました。
読んでいただいた方、コメントくださった方、本当にありがとうございます。
あの記事で少しでもGASの良さを伝えれたら幸いです。
スプレッドシート関数一覧
今回は、個人的にスプレッドシートで使う関数の使い方、またはスプレッドシート独自の関数を備忘録として残していきたいと思います。
IMPORTRANGE関数
別のスプレッドシートのデータを参照することがで出来る関数です。
記述方法
=IMPORTRANGE("[スプレッドシートID]","[シート名]![範囲]")
使用例
まず、スプレッドシート（参照元）を用意します。
もう一つスプレッドシート（参照先）を用意します。
まずは、参照元のスプレッドシートのスプレッドシートIDを控えます。
スプレッドシートIDというのは、URLに記載されています。
「d/」の後から次の「/」までに記載している文字列です。これを控えます。
https://docs.google.com/spreadsheets/d/[スプレッドシートID]/edit#gid=0
その後、参照先のスプレッドシートのA1のセルにIMPORTRANGE関数を設定します。
=IMPORTRANGE("[スプレッドシートID]","シート1!A:Z")
するとA1セルにが「#REF!」と表示されます。
A1セルにカーソルを合わせると、
このような文字が表示されます。
これは、他のスプレッドシートにアクセスしますよ。ということを手動で許可しないといけません。一度許可すると許可したスプレッドシートへのアクセスが可能となり、データが反映されます。
参照範囲の部分のスプレッドシートが更新されたら、リアルタイムで参照先のスプレッドシートも反映されます。
QUERY関数
個人的に一番使用する関数です。
他のいろいろある関数もとりあえずこの関数で代用できるので、重宝しています。
関数の名前のようにクエリを使用することができます。
詳細はこちら
記述方法
=QUERY( [スプレッドシートの範囲] , "[クエリ言語]" )
使用例
上記と同じスプレッドシートを参照元として用意します。
このデータから名前に「スライム」を含むデータのみを抽出したいとします。
セルのD2にQUERY関数を設定します。
=QUERY($A$2:$B,"SELECT * WHERE A LIKE '%スライム%' ")
結果として、「スライム」の名前のみのデータが取得できました。
よく営業の方から、「○○市のデータだけ抜いて欲しいんだけど」って時に使用しています。
設定後は参照元にデータが増えても即時に反映されます。
他にも、項目ごとに集計を行なったり、カウントをとったり、表示順の変更したりと利用用途は様々です。
私はよく、QUERY関数とIMPORTRANGE関数を組み合わせて使用します。
ただし、この組み合わせて関数を使う際には列の参照方法が変わるので注意が必要です。
先ほどはA列に「スライム」を含むデータを抽出したかったので、
「WHERE A LIKE...」とAと指定したのですが、別のスプレッドシートから参照する場合、
「A」と指定せず、「Col1」と指定する必要があります。
これは、A列をだから「Col1」という訳ではなく、参照した範囲の何列目という指定の仕方になります。
なので、指定範囲をD列からF列の範囲を指定した場合、E列を使用したい場合は、
「Col2」となりますので注意してください。
A列からZ列まで参照し、A列に「スライム」を含むデータを参照する場合
B列からZ列まで参照し、B列のレベルが5以上のデータを参照する場合
シートの範囲は変わってますが、WHEREの後はどちらも「Col1」となっています。
SORT関数
データを並び替えて表示してくれる関数です。
記述方法
=SORT( [スプレッドシートの範囲] ,[列番号],[0 or 1],[列番号],[0 or 1], ... )
[0 or 1]は前で指定した列の昇順か降順指定しますよく間違えてしまうのですが、
- 0 : 降順
- 1 : 昇順
です。
使用例は割愛します。
UNIQUE関数
これも結構重宝しています。
重複しているデータをまとめてくれる関数です。
記述方法
=UNIQUE( [スプレッドシートの範囲])
使用例
A列B列に対してUNIQUE関数を使用しています。
複数列を指定した場合、複数の列で同じデータをまとめてくれます。
今回の場合は、
2行目と５行目・３行目と６行目が同じなので、まとめられます。
４行目も「スライム」ですがレベルが違うのでまとめられなかったということです。
FILTER関数
名前の通り、絞りこみを行う関数です。
スプレッドシートの標準にあるじゃないか。
と思う方もいたと思うのですが、スプレッドシートを共有して同時に参照する場合、誰かがこの機能を利用すると、他の人にも影響が出てしまうので、この関数を使用します。
先ほど紹介した「importrange関数」を使用した方が楽かもしれませんけどね。。。。
記述方法
=FILTER( [スプレッドシートの範囲],条件,条件,...)
使用例
A列B列に対してFILTER関数を使用し、レベル（B列）が5より高いデータを抽出しています。
ここで、注意することは、条件の列は範囲で指定しないといけないということです。
今回の例ですと、B列に対して条件を指定していますそのため、列は[B:B]と指定する必要があります。
しかも、列の長さ（行）は参照範囲と同じにする必要があります。ちょっと使いにくい仕様ですね。。。
まとめ
今回紹介した関数の簡単な説明
- IMPORTRANGE
- 別のスプレッドシートを参照可能
- 参照するには手動でアクセス許可する必要
- QUERY
- クエリ言語を使用できて色々な関数はこれで代替可能
- IMPORTRANGEと組み合わせる場合は、列の指定方法に必要
- SORT
- データの並び替えが可能
- 0：降順
- 1：昇順
- UNIQUE
- 同じデータをまとめてくれる
- FILTER
- データの絞り込みが可能
- 列の指定には注意が必要
なにかご質問や、指摘等がありましたらよろしくお願いします。