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

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

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

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

=IMAGE(写真のURL)

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”)
   
   画像が表示されました。

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

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

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

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

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

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

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

   

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

また、オプションは{}で囲んで追加することができます。（省略化） 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でも使用した営業成績のグラフの数値をミニグラフで表示します

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

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

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

過去に作成した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のタイトルをスクレイピングしてみましょう。

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

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)

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

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

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

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

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

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

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

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