4月にはエクセルの使い方についての記事を4本アップしましたが、参考になりましたか?今回はエクセルではなく、「Google スプレッドシート」をご紹介します!
「名前は知ってるけどエクセルと同じでしょ?」という方や、「使ってるよ」という方でもエクセルと同じ機能しか利用していない人が大半かと思います。実は、Google スプレッドシートでしか使えない機能がたくさんあるんです!
まずは、利用可能な関数の一覧をみてみてください。
普段エクセルを使う人ほど見たことのない関数が多くあることに気が付きましたよね。さらにその機能の内容に驚くのではないでしょうか。Web上で処理ができるからこそ、エクセルでは実現できないような関数が多数搭載されています。Google スプレッドシートとエクセルの大きな違いは、Web上で動かしているかどうかです。特にGoogle スプレッドシートで搭載されている関数にはインターネット上の情報を使用して使う関数も多いのでこの機会に覚えておくといざというときに楽になります。
他にも次のようなことができるのがGoogle スプレッドシートの特徴です。
- 誰でも無料で利用できる
- オンラインでのデータ共有が可能
- 複数ユーザーが同時に編集作業を行うことができる
データ編集の場面などで非常に強力な関数もあるので、ぜひ使ってみていただきたく「Google スプレッドシートでしか使えない」おすすめの関数とデータ集計に役立つ機能をご紹介します。
目次
IMPORTRANGE関数
IMPORTRANGE(インポートレンジ)関数は対象の範囲を別スプレッドシートに参照させる事のできる関数です。
使用例
IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "シート1!A1:C10")
IMPORTRANGE(A2,B2)
構文
IMPORTRANGE(スプレッドシートキー, 範囲の文字列)
スプレッドシートキーの箇所にはデータの読み込み元となるスプレッドシートのURLを指定することで情報の取得ができます。使用例では「範囲の文字列」にシート名も入っていますがシート名は省略も可能です。その場合は最初のシートの指定範囲から読み込みを開始します。もちろんこのデータ単体では何か計算ができるわけではないですが、他の関数と組み合わせることで様々なメリットにつながるのでスプレッドシートでファイルまたぎの計算式を動かしたい場合には必須な関数です。
ちなみにエクセルの場合は関数ではなく、ファイルパスを入力することで参照が可能です。
Tips - ファイルパスとは?
「ファイルパス」とはパソコン上のどこにファイルが保存されているのかを示す文字列で、PC上の住所のようなものです。エクセルでは、ファイルパス+シート名+範囲を指定することで参照データを読み込むことができますが、ファイルパスが入っているデータを他のPCへ渡してしまった場合には、受け取った側が参照できないファイルパスを入力していたなど参照できないケースも発生してしまいますので気をつけましょう。
参考例
・対象のファイルを開いているとき
・対象のファイルを閉じているとき
IMPORTRANGE関数の注意点
エクセルの場合は計算のタイミングを手動または自動から選択したり、再計算などの機能もありましたが、このIMPORTRANGE関数に関してはデータ取得頻度が30分ごとなので、参照元データを変更した場合に参照先の計算結果が変わるまでに最大で30分かかることになります。データが更新されないなと思ったときには、少し時間を置いてから再度確認してみましょう。
IMAGE関数
IMAGE関数はセルの中に画像を表示する関数ですが、Web上だからこそ実現する関数で非常に強力です。表示したい画像がWeb上にアップロードされていれば、IMAGE関数内にそのURLを打ち込むだけで表示することができるので、クリエイティブの集計表を作るときに大いに役立ちます。
使用例
IMAGE("https://www.google.com/images/srpr/logo3w.png")
IMAGE(A2,2)
IMAGE(A2,4,120,200)
構文
IMAGE(URL, [モード], [高さ], [幅])
モードなどの変数の内容に関してはヘルプページをご参照ください。
Googleドライブ上に画像をアップロードして、IMAGE関数で引用してしまえば良いのでスプレッドシート内に画像を挿入する必要もありません。エクセルの場合は画像を挿入してセル幅を調整して…といった作業が必要でしたが、こちらは関数でURLを指定しているので数値をまとめた後に表示回数やコンバージョン数で表を降順に変えてもレイアウトが崩れる心配もありません。
ただし、IMAGE関数を利用する場合の注意点があります。指定する画像は「パブリックにURLを知っていれば閲覧できるもの」という条件です。そのため限定されたメンバーしか見れない画像ではIMAGE関数にURLを入力しても、画像は出力されません。そのため、Googleドライブ上に画像をアップロードしている場合には「全員が閲覧可能」な設定にするなど共有設定に気をつける必要があります。
↓
https://drive.google.com/uc?export=view&id=画像ID
また、Googleドライブ上の画像をIMAGE関数関数に使用する場合は、そのまま共有リンク入力しても関数で出力がされないのでパラメータを変更することで表示がされるようになります。
ARRAYFORMULA関数
配列数式とは、配列(複数セル)を対象に、1つの数式を作成する式です。 エクセルで配列数式をよく使っている方には馴染み深いと思います。エクセルの配列数式と機能はほぼおなじなのですが、違いはすべてのセルに配列数式を入力しなくても良いという点です。
使用例
ARRAYFORMULA(SUM(IF(A1:A10>5, A1:A10, 0)))
ARRAYFORMULA(A1:C1+A2:C2)
構文
ARRAYFORMULA(配列数式)
サンプルのスプレッドシートE3とG3にARRAYFORMULA関数を入れており、関数の入っているセルより下には配列数式の要件を満たす範囲で自動的に数値が出力されています。もしARRAYFORMULA関数で設定した数式が間違っていて修正する場合でも、ARRAYFORMULA関数内の配列数式を編集するだけで完了するため、修正した数式を対象のセルすべてに上書きする必要もありません。特に、複雑な数式をたくさん組んでいるスプレッドシートの場合、修正作業だけで非常に動作が重くなってしまうこともありますがARRAYFORMULAを利用することで、スプレッドシート上の数式を簡潔にすることができるため、管理上のミスを減らすことにも役立ちます。配列数式なのでもちろん二次元的な指定も可能です。
小技ですが、セル入力中に「Ctrl+Shift+Enter」を押すことで自動的にARRAYFORMULA関数が挿入されます。
QUERY関数
QUERY関数は選択した範囲の情報を別のシートに出力できる関数です。データベース操作などで使われるような指定をすることのできる関数で、SQLなどを使ったことがある方なら馴染み深いかもしれません。
使用例
QUERY(A2:E6,"select avg(A) pivot B")
QUERY(A2:E6,F2,FALSE)
構文
QUERY(データ, クエリ, [見出し])
仮のテストデータを用意し、スプレッドシートを用意してみました。
まずは確認してみてください。ここで入力されている関数はA1セルだけです。
※ランダムに出力されている架空のデータを使用しています。
A1以外は参照元のデータから数式ではなく数値や文字列で表示されています。しかし、ここで表示されている文字列などを変更した場合出力されているデータが全てエラーとなってしまうため変更することはできません。
サンプルのように必要なデータだけを条件をつけて取り出すこともできるので、「女性だけの顧客データリスト」「東京のユーザーにダイレクトメールを送付するため、東京在住のユーザーリスト」など任意のデータリストを作成することが可能です。また、検索広告の検索語句のデータがあれば、QUERY関数を使って「ページ上部インプレッションの割合」が90%以上である検索語句の実績を抽出といった条件の設定も可能です。
クエリにて条件を指定するのですが、本記事ですべてを説明するとかなりの量になりますので下記を参照ください。
クエリ言語 | 使用法 |
---|---|
select | どの列をどの順序で返すかを選択します。省略すると、テーブルのすべての列がデフォルトの順序で返されます。 |
where | 条件に一致する行のみを返します。省略すると、すべての行が返されます。 |
group by | 行をまたいで値を集計します。 |
pivot | 列内の個別の値を新しい列に変換します。 |
order by | 行を列の値でソートします。 |
limit | 返される行数を制限します。 |
offset | 指定された数の最初の行をスキップします。 |
label | 列ラベルを設定します。 |
format | 与えられたフォーマットパターンを使って特定の列の値をフォーマットします。 |
options | 追加オプションを設定します。 |
引用元: Google Visualization API のクエリ言語
ちなみに、最初にご紹介したIMPORTRANGE関数をデータの箇所に使用することも可能です。
追加できる広告運用で便利なアドオン
Google スプレッドシートはアドオンという追加機能があり、インストールすることでより便利にカスタマイズすることが可能になります。便利なアドオンを2種類ご紹介します。
Google Analytics アドオン
使うのに多少の慣れは必要ですが、Google アナリティクスのデータをスプレッドシート上に表示させることができます。また、スケジュール設定で定期的に数値を更新することができますので、広告数値とGoogle アナリティクスの数値をかけ合わせてレポーティングすることが可能になります。特に広告管理画面のコンバージョン数を使用せずにGoogle アナリティクス上の目標完了数をKPIに設定している場合には非常に役立つすぐれものです。
インストールはこちら:Google Analytics - Google スプレッドシート アドオン
Google Adsアドオン
こちらもGoogle アナリティクスと同様にデータをスプレッドシート上に取り込むことが可能です。もし現在の配信がGoogleだけであればGoogle アナリティクスのデータとGoogle 広告のデータを掛け合わせるだけでレポートに十分なデータが自動で取得できるようになります。(もちろんYahoo!プロモーション広告やFacebook広告を利用していた場合は、レポート作成にそれぞれのデータも追加で必要になりますが…)
また、一つのスプレッドシートにGoogle Analytics アドオンとGoogle Ads アドオンを使用するとエラーになるケースがありますので、別のシートへ分けて作成するのをおすすめします。
インストールはこちら:Google Ads - Google スプレッドシート アドオン
Google スプレッドシートではできないこと
※先日までできなかったLENB()関数が、いつの間にかできるようになっていました!
参考:LENB function - ドキュメント エディタ ヘルプ
実際にできないことというのはほぼありませんが…エクセルにはあってGoogle スプレッドシートにはない関数がいくつかあります。例えば、エクセルの関数編でお伝えしていたLENB()関数はGoogle スプレッドシートでは搭載されていません。しかし、Google スプレッドシートはGoogle Apps Scriptというサービスを使用することで自分で自作の関数を作成することができます。なので、無い関数は自分で作成することで利用することができます!
例えば、下記はLENB()関数を実際に使用しているGoogle スプレッドシートです。
こちらを参考にさせていただいてます。
参考:2バイト文字は2として文字列の長さをカウントするGoogle Apps Script関数
スクリプト欄をご覧いただければ、LENB関数用に用意したプログラムがあります。
このようにスプレッドシートには用意されていない関数があった場合には、自作することで実現することができます!これには少々の専門的知識も必要なので近くにエンジニアの方がいれば相談したり、エンジニア向けのSNSやWebサービスにて公開されているオープンソースなどがあればそれを参考にするのも良いかと思います。
Google スプレッドシートの魅力
スプレッドシートは昔は計算が遅い、関数が少ないなど不満を聞くこともありましたが、PCスペックの向上やGoogle スプレッドシート自体のアップデートにより非常に魅力的な機能が多く増えています。かゆいところにも手が届くような拡張性の高いスクリプト機能や文章や文字列を自動で翻訳してくれる「GOOGLETRANSLATE関数」、祝日などを判断して就業日数を計算する「NETWORKDAYS.INTL関数」などインターネット上で処理するからこそ実現している関数が多くあり、エクセルでは難しいリアルタイムでの同時編集などもクラウド化されているスプレッドシートならではのメリットです。
ユーザー権限の編集で限定的なユーザーにのみファイルの内容を見てもらうこともできますし、アプリを使えば出先などでスマートフォンから編集閲覧することもできます。ここまで機能が充実していて無料なのが一番ありがたいです!今後のアップデートも考えるとより便利になりそうですね!
エクセルとはまた違った機能や魅力を備えたスプレッドシートも用途に応じて是非使いこなしてください!