スプレッドシートの効率アップ関数7選

インターネットに接続できる環境さえあれば無料で使える Google スプレッドシート。マクロなどの高度な機能も使えるので、表計算ソフトはスプレッドシートだけで充分、という人も多いかもしれませんね。

そんな便利なスプレッドシートですが、実はスプレッドシートにしか使えない、便利な関数があることをご存知ですか？ この記事では、本当に作業の効率が上がる、スプレッドシート限定の関数を7つ厳選してご紹介します。

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

関数を使いこなすと作業のスピードが上がり、資料の精度も高くなるので積極的に活用したいですよね。

関数には SUM 関数や IF 関数といった定番のものだけでなく、スプレッドシート限定のユニークな関数があります。知っておくとスプレッドシートの作業がもっと速く、もっと楽しくなるので、ぜひ今日から活用してみましょう。

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

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

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

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

【GoogleTranslate 構文例】

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

【GoogleTranslate の使い方例】

1. まずは表を作成します。
   1行目に、翻訳元の言語と翻訳をしたい言語を入力していきます。
   例では日本語、英語、中国語、韓国語、スペイン語と入力しました。
2. 1列目（日本語と書かれた列）に翻訳したい言葉を入力します。
   
3. 2行目のB列以降の対象セル（この場合はB,C,D,E列）に関数の構文を入力します。

   関数の構文は下記の通りです。

   =GoogleTranslate(翻訳したいセル,”翻訳元の言語コード”,”翻訳される言語コード”)

   各言語コードは “”（ダブルクォーテーション）で囲むのがポイントです。

   例えば、日本語を英語に翻訳したいときは

   =GoogleTranslate(A2, "es", "en")

   のように記入します。

   

   主要な言語コードは下記の表を参照してください。そのほかの言語はこちらに掲載されています。

4. 対象のセルに関数の構文を入れたら、まとめて下方向にドラッグするとそれぞれ翻訳され単語が表示されます。
   

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

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

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

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

【IMAGE の構文例】

=IMAGE(写真のURL)

【IMAGE の使い方例】

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

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

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

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

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

1つの表作成して、複数のスプレッドシートで使用したい時など

元の表を更新すれば他の表も自動で更新されます。

【IMPORTRANGE の構文例】

=IMPORTRANGE(“スプレッドシートのURL”, “シートの名前!A1:C10”)

【IMPORTRANGE 使い方例】

1. データの呼び込み先のスプレッドシートを開き、下記のような構文をセル内に入れておきます。 IMPORTRANGE(“https://docs.google.com/spreadsheets/d/abcd123abcd123”, “シート1!A1:C10”)
   
2. データの読み込み元のファイルを開き、スプレッドシートのURLをコピーします。
   
3. 読み込み先のスプレッドシートの A1 に入力した関数を、下記のように赤字部分を書き換えます。
   読み込み先のスプレッドシートに 「これらのシートをリンクする必要があります」 と表示されたら 「アクセスを許可」 をクリックしてください。
   
   

   正しくデータが読み込まれました。
   一度データをインポートすれば、参照元のスプレッドシートが更新された際には参照先のスプレッドシートも自動で更新されます。

   

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」

IMPORTXML という関数を使うと、スクレイピングといって XML、 HTML、 CSV、 TSV、 RSS フィード、 Atom XML フィードなど、さまざまな種類の構造化データからデータを直接インポートできます。

例えば、あるサイトからタイトルだけまとめて抜き出したい、見出しだけ抜き出したい、いうときにこの関数を使えば自動で欲しい部分を抜き出すことが可能です。

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

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

【IMPORTXML の構文例】

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

指定のURLからタイトルのデータをインポートした場合

IMPORTXML(“https://media.rakumo.com/”,”//title”)

スプレッドシートに記載されている URL から見出しのデータをインポートしたい場合

IMPORTXML(A1,"//h1")

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

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

例) html/body/h1、 html/body/title

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

【IMPORTXML の使い方例】

例として、 IMPORTXML を使用して「活用のヒント」のタイトルをスクレイピングしてみましょう。

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

   タイトルを自動で取得することができました。

　

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

こちらも WEB 上で情報収集を行う際にかなり便利な関数です。

IMPORTHTML を使えば、 WEB のページに掲載されている表やリストをいちいちコピペせずにインポートできます。

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

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

【IMPORTHTML の構文例】

=IMPORTRANGE("URL", "クエリ", "指数")

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

1つ目の表またはリスト

IMPORTHTML("https://media.rakumo.com/2019_12_26_1967/","table",1)

2つ目の表またはリスト

=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」 は、株式（米国株限定）に関する資料を作成するときにオススメ。構文を組み合わせることで様々な株価情報を表示させることが可能です。

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

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

【GOOGLEFINANCE の構文例】

シンプルに現在の株価を表示させるにはこちらの構文を入力します。

=GOOGLEFINANCE("ティッカーシンボル","price")

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

【GOOGLEFINANCE の使い方例】

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	ファンドの経費率。

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

まとめ

スプレッドシートで関数を駆使すれば、普段時間をかけて行なっている作業が何倍もの速さでこなせるかもしれません。

スプレッドシートで行っている作業を手間に感じたら。と感じたらその作業を自動で処理できる関数がないか探してみてはいかがでしょうか？