仕事の速さが倍速!?スプレッドシートの効率を加速させる関数はコレ

ネット環境さえあれば無料で使えるGoogle スプレッドシート。
マクロなどの高度な機能も使えるので、表計算ソフトはスプレッドシートだけで充分!という人も多いかもしれませんね。
そんな便利なスプレッドシートですが、実はスプレッドシートにしか使えない、便利な関数があることをご存知ですか?
この記事では、本当に作業の効率が上がる、スプレッドシート限定の関数を8つ厳選してご紹介します。

Googleならでは!スプレッドシートがもっと好きになる、便利な関数を使ってみよう

手入力をすると時間がかかる表計算ソフトの入力作業も、関数を使えばあっという間に完了!
関数を使いこなすと作業のスピードが上がり、資料の精度も高くなるので積極的に活用したいですよね。
関数にはSUM関数やIF関数といった定番のものだけでなく、スプレッドシート限定のユニークな関数があるんですよ。
知っておくとスプレッドシートの作業がもっと速く、もっと楽しくなるので、ぜひ今日から活用してみてくださいね。

こんな時に便利!スプレッドシートならではの便利な関数7選

それでは、スプレッドシートだけで使える便利な関数を利用シーンとともにご紹介していきます。

様々な言語に翻訳する関数「GOOGLETRANSLATE」

GOOGLETRANSLATEを利用するとスプレッドシートに入力した単語を、英語、スペイン語、中国語などの多言語に一気に翻訳できます。(逆も可能)

【こんなシーンで使える!】

Webサイトや、施設などに掲示するポップを多言語対応させたい時 など

【GOOGLETRANSLATE構文例】

= GOOGLETRANSLATE(対象セル,”翻訳元の言語コード”,”翻訳される言語コード”)

【GOOGLETRANSLATEの使い方例】
  1. まずは表を作成します。
    1行目に、翻訳元の言語と翻訳をしたい言語を入力していきます。例では日本語、英語、中国語、韓国語、スペイン語と入力しました。
  2. 1列目(日本語と書かれた列)に翻訳したい言葉を入力します。
  3. 2行目のB列以降の対象セル(この場合はB,C,D,E列)に関数の構文を入力します。
    関数の構文は下記の通りです
    = GOOGLETRANSLATE(翻訳したいセル,”翻訳元の言語コード”,”翻訳される言語コード”)
    各言語コードは””(ダブルクォーテーション)で囲むのがポイントです。
    例えば、日本語を英語に翻訳したいときは
    = GOOGLETRANSLATE(A2,”ja”,”en”)
    のように記入します。
    主要な言語コードは下記の表を参照してください。そのほかの言語はこちらに掲載されています。

    言語 言語コード※利用者が多い言語を抜粋
    中国語(簡体) zh
    中国語(繁体) zh-TW
    英語 en
    ヒンディー語 hi
    スペイン語 es
    アラビア語 ar
    ベンガル文字 bn
    ポルトガル語 pt
    ロシア語 ru
    日本語 ja
    フランス語 fr
    ドイツ語 de
    ジャワ語 jw
    韓国語 ko
    ベトナム語 vi
    イタリア語 it
  4. 対象のセルに関数の構文を入れたら、まとめて下方向にドラッグするとそれぞれ翻訳され単語が表示されます。

セルに画像を挿入する関数「IMAGE」

スプレッドシートで資料を作成する際、画像を挿入することがよくあるかと思います。そんな時はIMAGE関数を使用すると、WEB上に公開されている画像を表示させることが可能です。

【こんなシーンで使える!】

WEBに上がっている写真を一度PCにダウンロードすることなくスプレッドシートに入れたい時

【IMAGEの構文例】

=IMAGE(写真のURL)

【IMAGEの使い方例】
  1. 表示させたい画像のアドレスを取得する。
  2. 構文 =IMAGE(” “) をセルに入力します。
    https://media.rakumo.com/wp-content/uploads/sites/4/2019/07/gphoto_KV1200.jpg を表示させたい時の構文は下記の通りです。
    =IMAGE(“https://media.rakumo.com/wp-content/uploads/sites/4/2019/07/gphoto_KV1200.jpg”)
    画像が表示されました。

    注:)画像は著作権、肖像権などで保護されている場合があります。使用する権利があることを確認した画像のみを選択してください。

複数のスプレッドシートの範囲を読み込める「IMPORTRANGE」

複数のスプレッドシート数値を取り込んで一つのスプレッドシートにまとめたい時は、IMPORTRANGE関数が便利。
この関数を使うと、元のデータを修正した際に全ての表が自動で更新されます。データの管理も今までより簡単になり、工数の削減につながります。

【こんなシーンで使える!】

1つの表作成して、複数のスプレッドシートで使用したい時 など。元の表を更新すれば他の表も自動で更新されます。

【IMPORTRANGEの構文例】

=IMPORTRANGE(“スプレッドシートキー”,”範囲指定した文字列”)

【IMPORTRANGE使い方例】
  1. データを読み込みたいファイルを開きます。
  2. 呼び出したいスプレッドシートを開き、以下のような構文をセル内に入れます。

    =IMPORTRANGE(“スプレッドシートキー”,”範囲指定した文字列”)

    例:=IMPORTRANGE(“1qVKlsTAEbDXQzzUG05aHp-sDk6go8el4mlUWYxnh-6s”,”A3:E7″)

    ※スプレッドシートキーとは、スプレッドシートのURLの一部です。
    例えばURLが https://docs.google.com/spreadsheets/d/◯◯◯◯◯◯/edit#gid…. となっている場合は「◯」の部分が該当します。

1 つのセル内に含まれるミニ グラフを作成する関数「SPARKLINE」

データを使用した資料作成をするときに使えそうな関数が、こちらのSPARKLINE。指定されたデータ範囲とオプションに基づいて、1つのセル内に棒グラフを作成します。

【こんなシーンで使える!】

データにミニグラフをつけてビジュアライズしたい時 など

SPARKLINE関数では、以下の4種類のミニグラフを描くことができます。

  • 折れ線グラフ(デフォルト)
  • 積み重ね棒グラフ
  • 縦棒グラフ
  • 正と負(コイン投げの表と裏など)の 2 つの結果を表す縦棒グラフ

また、オプションは{}で囲んで追加することができます。(省略化) SPARKLINEにはさまざまなオプションがありますが、よく使われるものをご紹介します。

【主なオプション】

◆””charttype”” でグラフの種類を指定する
“”line”” – 折れ線グラフ(デフォルト)
“”bar”” – 積み重ね棒グラフ
“”column”” – 縦棒グラフ
“”winloss”” – 正と負(コイン投げの表と裏など)の 2 つの結果を表す縦棒グラフ

◆最大値と最小値を指定する
“max”-積み重ね棒グラフ、縦棒グラフの横軸の最大値
“”xmin”” 折れ線グラフの横軸の最小値
“”xmax””折れ線グラフの横軸の最大値
“”ymin”” 折れ線グラフの縦軸の最小値
“”ymax”” 折れ線グラフの縦軸の最大値

他にもグラフの色や太さなどもオプションで選択できます。
詳しいオプションについては、Googleの公式ページも参考にしてみてください。

【SPARKLINEの構文例】

読み込むセルの範囲を指定して折れ線グラフを作成する場合
=SPARKLINE(A1:F1)

読み込むセルの範囲をして積み重ね棒グラフを指定する場合。横軸の最大値は40を指定
=SPARKLINE(A2:E2,{“charttype”,”bar”;”max”,40})

【SPARKLINEの使い方例】

例ではこちらはIMPORTRANGEでも使用した営業成績のグラフの数値をミニグラフで表示します

  1. グラフを作成したい表を用意します。こちらはIMPORTRANGEでも使用した営業成績のグラフです。
  2. ミニグラフを入れたいセルにSPARKLINE構文を書き込みます。
    SPARKLINEでは構文にオプションを追加してグラフをカスタマイズすることもできます。(後述)ここでは、合計の数値を棒グラフで表示させます。今回はこの構文を書き込みました。

    構文例=SPARKLINE(E3,{“”charttype””,””bar””; “”max””,15000})”

    このように、数値が1つのセルの中でグラフ化されました。

XMLなどの構造化データからデータをインポートできる関数「IMPORTXML」複数のスプレッドシートの範囲を読み込める「IMPORTRANGE」

IMPORTXMLという関数を使うと、スクレイピングといってXML、HTML、CSV、TSV、RSS フィード、Atom XML フィードなど、さまざまな種類の構造化データからデータを直接インポートできます。
例えば、あるサイトからタイトルだけまとめて抜き出したい、見出しだけ抜き出したい、いうときにこの関数を使えば自動で欲しい部分を抜き出すことが可能です。

【こんなシーンで使える!】

過去に作成したWEBサイトのページタイトルを集めて検証したいとき、競合サイトの情報を取得したいとき など

【IMPORTXMLの構文例】

=IMPORTXML(“URL”, “XPathクエリ”)

例)=IMPORTXML(“https://media.rakumo.com/”,”//title”)
※指定のURLからタイトルのデータをインポートした場合

例)=IMPORTXML(A1,”//h1″)
※スプレッドシートに記載されているURLから見出しのデータをインポートしたい場合

URLにはスクレイピングしたいサイトのURLを、XPathにはHTMLの特定箇所を指定する文字列を入力します。

※XPathとは、XML文章中の要素や属性値を指定するための言語で、h1やtitleなど、HTMLの要素を指定することができます。
指定の仕方はHTMLをツリー構造として見たときに、上からたどっていく形で指定します。

ex) html/body/h1、html/body/title

なお、URLなどで文字列指定する場合はダブルクォーテーションで囲むようにします。セル指定をする場合はそのままセル番号のみを入れます。

【IMPORTXMLの使い方例】

例として、IMPORTXMLを使用してエブリディG Suiteのタイトルをスクレイピングしてみましょう。

  1. スクレイピングしたいページのURLリストを作成する
  2. スクレイピング先のセルに構文を入力します。
    今回は各ページのタイトルをスクレイピングしたいので下記の構文を入力しました。=IMPORTXML(A1,”//title”)
    タイトルを自動で取得することができました。

WEBの表やリストからデータをインポートできる「IMPORTHTML」

こちらもWEB上で情報収集を行う際にかなり便利な関数です。
IMPORTHTMLを使えば、WEBのページに掲載されている表やリストをいちいちコピペせずにインポートできます。

【こんなシーンで使える!】

WEBに掲載されている表をスプレッドシートにインポートして、リスト化したいとき。WEBに掲載されている表から情報収集・解析したいとき など

【IMPORTHTMLの構文例】

=IMPORTRANGE(“URL”, “クエリ”, “指数”)クエリは、表の場合は”table”、リストの場合は”list”を指定します
指数は、指定のURLに載っている1つ目の表またはリストならば”1″、2つ目の表ならば”2″のように入れます。

例)=IMPORTHTML(“https://media.rakumo.com/2019_12_26_1967/”,”table”,1)

例)=IMPORTHTML(“https://media.rakumo.com/2019_12_26_1967/”,”list”,2)

【IMPORTHTMLの使い方例】
  1. 表やリストをインポートしたいWEBページのURLを取得します。
  2. インポート先のセルに構文を記入します。
    今回はhttps://media.rakumo.com/2019_08_28_1691/に掲載されている一つ目の表をインポートしたいので=IMPORTHTML(“https://media.rakumo.com/2019_08_28_1691/”,”table”,1)と入力しました。
    表が自動でインポートされました。

現在や過去の証券情報を取得できる関数「GOOGLEFINANCE」

「GOOGLEFINANCE」は、株式(米国株限定)に関する資料を作成するときにオススメ。
構文を組み合わせることで様々な株価情報を表示させることが可能です。

【こんなシーンで使える!】

自分が知りたい株価情報をスプレッドシートに集約してチェックしたい時、株価のレポートをスプレッドシートで作成したい時 など

【GOOGLE FINANCEの構文例】

シンプルに現在の株価を表示させるにはこちらの構文を入力します。=GOOGLEFINANCE(“ティッカーシンボル”,”price”)

※ティッカーシンボルとは、株式市場で上場企業や商品を識別するため付けられる符丁(記号)のこと。ニューヨーク証券取引所やNASDAQといったアメリカの証券取引所などで使われています。

【GOOGLE FINANCEの使い方例】

Googleを運営しているアルファベット社の株価(ティッカーシンボル:GOOG)を表示させるには下記のコードをセルに入力します。=GOOGLEFINANCE(“GOOG”,”price”)

アルファベット社の現在の株価が表示されました。
構文に入れる属性(priceなどは)他にも当日の始値、当日の高値など様々な数値を指定できます。

属性の一覧は下記になります。

セルの書式設定
属性 説明
price リアルタイムの見積価格。最大20分ほど遅延する場合があります。
priceopen 当日の始値。
high 当日の高値。
low 当日の安値。
volume 当日の取引量。
marketcap 株式の時価総額。
tradetime 最終取引の時刻。
datadelay リアルタイムデータの遅延度。
volumeavg 1日の平均取引量。
pe 株価収益率。
eps 1株当たりの収益。
high52 52週高値。
low52 52週安値。
change 前取引日の終値からの株価の変動。
beta ベータ値。
changepct 前取引日の終値からの株価の変動率。
closeyest 前日の終値。
shares 発行済み株式数。
currency 証券の販売通貨。
過去のデータの属性
属性 説明
open 指定した日付の始値。
close 指定した日付の終値。
high 指定した日付の高値。
low 指定した日付の安値。
volume 指定した日付の取引量。
all 上記のすべて。
投資信託データの属性
属性 説明
closeyest 純資産価値。
date 純資産価値が報告された日付。
returnytd 年初からの収益。
netassets 純資産。
change 直近に報告された純資産価値とその前回の純資産価値の変動。
changepct 純資産価値の変動率。
yieldpct 流通利回り(過去12か月の所得分布(株式配当や債券利息の支払いなど)と純資産価値の増分の和を前月の純資産価値の数値で割った値)。
returnday 1日の総収益。
return1 1週間の総収益。
return4 4週間の総収益。
return13 13週間の総収益。
return52 52週間(年間)の総収益。
return156 156週間(3年間)の総収益。
return260 260週間(5年間)の総収益。
incomedividend 直近の現金分配額。
incomedividenddate 直近の現金分配の日付。
capitalgain 直近のキャピタルゲイン分配額。
morningstarrating モーニングスターの「スター」評価。
expenseratio ファンドの経費率。

こちらの機能ですが現在(2020年2月現在)日本株は未対応となっています。アップデートに期待しましょう。

まとめ

今回ご紹介した関数は、目からウロコが落ちるような画期的なものも多かったのではないでしょうか?ご紹介した使い方はほんの一例で、構文を組み合わせればかなり高度な情報の処理もできるようになるんですよ。

スプレッドシートで関数を駆使すれば、普段時間をかけて行なっている作業が何倍もの速さでこなせるかもしれません。
この作業、手間だなー。と感じたらその作業を自動で処理できる関数がないか探してみてはいかがでしょうか?

関連キーワード

関連記事