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

ネット環境さえあれば無料で使えるGoogle スプレッドシート。
マクロなどの高度な機能も使えるので、表計算ソフトはスプレッドシートだけで充分!という人も多いかもしれませんね。
そんな便利なスプレッドシートですが、実はスプレッドシートにしか使えない、便利な関数があることをご存知ですか?
この記事では、本当に作業の効率が上がる、スプレッドシート限定の関数を8つ厳選してご紹介します。
目次
Googleならでは!スプレッドシートがもっと好きになる、便利な関数を使ってみよう
手入力をすると時間がかかる表計算ソフトの入力作業も、関数を使えばあっという間に完了!
関数を使いこなすと作業のスピードが上がり、資料の精度も高くなるので積極的に活用したいですよね。
関数にはSUM関数やIF関数といった定番のものだけでなく、スプレッドシート限定のユニークな関数があるんですよ。
知っておくとスプレッドシートの作業がもっと速く、もっと楽しくなるので、ぜひ今日から活用してみてくださいね。
こんな時に便利!スプレッドシートならではの便利な関数7選
それでは、スプレッドシートだけで使える便利な関数を利用シーンとともにご紹介していきます。
様々な言語に翻訳する関数「GOOGLETRANSLATE」
GOOGLETRANSLATEを利用するとスプレッドシートに入力した単語を、英語、スペイン語、中国語などの多言語に一気に翻訳できます。(逆も可能)
Webサイトや、施設などに掲示するポップを多言語対応させたい時 など
= GOOGLETRANSLATE(対象セル,”翻訳元の言語コード”,”翻訳される言語コード”)
- まずは表を作成します。
1行目に、翻訳元の言語と翻訳をしたい言語を入力していきます。例では日本語、英語、中国語、韓国語、スペイン語と入力しました。
- 1列目(日本語と書かれた列)に翻訳したい言葉を入力します。
- 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 - 対象のセルに関数の構文を入れたら、まとめて下方向にドラッグするとそれぞれ翻訳され単語が表示されます。
セルに画像を挿入する関数「IMAGE」
スプレッドシートで資料を作成する際、画像を挿入することがよくあるかと思います。そんな時はIMAGE関数を使用すると、WEB上に公開されている画像を表示させることが可能です。
WEBに上がっている写真を一度PCにダウンロードすることなくスプレッドシートに入れたい時
=IMAGE(写真のURL)
- 表示させたい画像のアドレスを取得する。
- 構文 =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(“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(A1:F1)
読み込むセルの範囲をして積み重ね棒グラフを指定する場合。横軸の最大値は40を指定
=SPARKLINE(A2:E2,{“charttype”,”bar”;”max”,40})
例ではこちらはIMPORTRANGEでも使用した営業成績のグラフの数値をミニグラフで表示します
- グラフを作成したい表を用意します。こちらはIMPORTRANGEでも使用した営業成績のグラフです。
- ミニグラフを入れたいセルにSPARKLINE構文を書き込みます。
SPARKLINEでは構文にオプションを追加してグラフをカスタマイズすることもできます。(後述)ここでは、合計の数値を棒グラフで表示させます。今回はこの構文を書き込みました。
構文例=SPARKLINE(E3,{“”charttype””,””bar””; “”max””,15000})”
このように、数値が1つのセルの中でグラフ化されました。
XMLなどの構造化データからデータをインポートできる関数「IMPORTXML」複数のスプレッドシートの範囲を読み込める「IMPORTRANGE」
IMPORTXMLという関数を使うと、スクレイピングといってXML、HTML、CSV、TSV、RSS フィード、Atom XML フィードなど、さまざまな種類の構造化データからデータを直接インポートできます。
例えば、あるサイトからタイトルだけまとめて抜き出したい、見出しだけ抜き出したい、いうときにこの関数を使えば自動で欲しい部分を抜き出すことが可能です。
過去に作成したWEBサイトのページタイトルを集めて検証したいとき、競合サイトの情報を取得したいとき など
=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を使用してエブリディG Suiteのタイトルをスクレイピングしてみましょう。
- スクレイピングしたいページのURLリストを作成する
- スクレイピング先のセルに構文を入力します。
今回は各ページのタイトルをスクレイピングしたいので下記の構文を入力しました。=IMPORTXML(A1,”//title”)タイトルを自動で取得することができました。
WEBの表やリストからデータをインポートできる「IMPORTHTML」
こちらもWEB上で情報収集を行う際にかなり便利な関数です。
IMPORTHTMLを使えば、WEBのページに掲載されている表やリストをいちいちコピペせずにインポートできます。
WEBに掲載されている表をスプレッドシートにインポートして、リスト化したいとき。WEBに掲載されている表から情報収集・解析したいとき など
=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)
- 表やリストをインポートしたいWEBページのURLを取得します。
- インポート先のセルに構文を記入します。
今回はhttps://media.rakumo.com/2019_08_28_1691/に掲載されている一つ目の表をインポートしたいので=IMPORTHTML(“https://media.rakumo.com/2019_08_28_1691/”,”table”,1)と入力しました。表が自動でインポートされました。
現在や過去の証券情報を取得できる関数「GOOGLEFINANCE」
「GOOGLEFINANCE」は、株式(米国株限定)に関する資料を作成するときにオススメ。
構文を組み合わせることで様々な株価情報を表示させることが可能です。
自分が知りたい株価情報をスプレッドシートに集約してチェックしたい時、株価のレポートをスプレッドシートで作成したい時 など
シンプルに現在の株価を表示させるにはこちらの構文を入力します。=GOOGLEFINANCE(“ティッカーシンボル”,”price”)
※ティッカーシンボルとは、株式市場で上場企業や商品を識別するため付けられる符丁(記号)のこと。ニューヨーク証券取引所やNASDAQといったアメリカの証券取引所などで使われています。
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月現在)日本株は未対応となっています。アップデートに期待しましょう。
まとめ
今回ご紹介した関数は、目からウロコが落ちるような画期的なものも多かったのではないでしょうか?ご紹介した使い方はほんの一例で、構文を組み合わせればかなり高度な情報の処理もできるようになるんですよ。
スプレッドシートで関数を駆使すれば、普段時間をかけて行なっている作業が何倍もの速さでこなせるかもしれません。
この作業、手間だなー。と感じたらその作業を自動で処理できる関数がないか探してみてはいかがでしょうか?