STUDY

オンラインで使える!便利なスプレッドシートの関数

こんにちは!エンジニアの服部です。
自粛期間中のGWとなりましたが、みなさんはどう過ごしましたか??
僕は、毎年GW期間は帰省するのですが、今年は神奈川の自宅で過ごしました。Netflixで映画を見たり、地元の友人とオンライン飲み会をしたりと、家に居ながらも楽しく過ごせました!

今回は、スプレッドシートシートの関数について、いくつかご紹介したいと思います。

スプレッドシートとは?

スプレッドシートはGoogleが開発した表計算ソフトです。
Excelとの互換性もあり、常にオンラインで使用できるという性質があります。
今回はその特性を活かしたスプレッドシートの関数をいくつか紹介したいと思います。

GOOGLETRANSLATE

指定の言語を翻訳することができる関数です。

使用例)
=GOOGLETRANSLATE(セル,” [翻訳したい言語]”,” [翻訳後の言語]”)

英語から日本語に翻訳したいときは、
=GOOGLETRANSLATE(セル, “en”,”ja”)と指定します。

他にも、
・中国語(zh)
・韓国語(ko)
・ドイツ語(de)
・フランス語(fr)
・イタリア語(it)
に翻訳が可能です。

UNIQUE

重複する行を削除して表示できる関数です。ソートされた行は範囲内の先頭から順に表示されます。

使用例)
=UNIQUE(重複を確認する範囲)

A1~A9の重複していたデータが削除されてC1のセルから順に表示されています。

FILTER

指定した範囲内で条件に合うデータを表示する関数です。

使用例)
=FILTER(範囲,”[条件1]”,”[条件2]”)

今回は、指定した範囲内で「10頭」のデータをフィルタリングして表示しています。
Filter関数は指定範囲と同じ列数でない場合、エラーとなるので注意が必要です。

QUERY

指定範囲を配列集計するときに便利な関数です。
様々な条件を指定して独自の集計データを作成することができます。

使用例)
=QUERY(範囲, “[クエリ],[見出し]”)

例の関数では、範囲(A2:C10)で、C列の値がAかつBの値が1以上のデータ抽出して、B、A列を基準に昇順で表示しています。

QUERY関数は様々なオプションがありますので、組み合わせて見やすい集計データを作成しましょう。

IMAGE

Web上の画像をスプレッドシートのセルに表示する関数です。

使用例)
=IMAGE(“URL”,[モード],[高さ],[幅])

こちらはARCHETYPblogのロゴをセルに表示させています。
モード1(デフォルト)はセルの大きさに合わせて、画像サイズを調節して表示します。
モード4は、画像のサイズをピクセル数で指定できますが、セルの横幅や高さが足りない場合はトリミングされます。

ARRAYFORMULA

ARRAYFORMULA関数は複数のセルに指定の関数を反映することができます。

使用例)
=ARRAYFORMULA(複数反映する関数)

例の画像では、if関数をARRAYFORMULAで表示しています。

if関数の記述は、B2~B10のセルの値が10以上であれば、「10以上」、そうでなければ「10未満」と表示しています。

E2のセルにのみ関数を記述していますが、E10のセルまで結果が表示されています。

IMPORTHTML

IMPORTHTML関数は、Webページにある表やリスト(箇条書き)の内容を抽出して、表示することのできる関数です。

使用例)
=IMPORTHTML(“URL”,”[クエリ]”,”[指数]”)

IMPORTHTMLで弊社Webサイトの会社情報ページのtable要素を抽出して表示しています。

IMPORTRANGE

別のスプレッドシートの値を参照できる関数です。リアルタイムで値を取得することができます。つまり、参照元の値が変更された時に、読み込んでいる側のシートの値も変更されます。

使用例)
=IMPORTRANGE(“スプレッドシートキー”,”[範囲]”)

こちらは、先ほどIMPORTHTMLで取得した値を、別のスプレッドシート(右)で表示しています。

スプレッドシートキーはURLの下図の部分です。

別シートから読み込んだ値でプルダウンリストを作成しよう!

前述したIMPORTRANGEで別のシートの値を参照できることがわかりました。
そして、1つ実践的な使い方として、その値をプルダウンリストにしてみましょう。

①プルダウンリストを作成するスプレッドシートにIMPORTRANGEで値を読み込みます。

②そのスプレッドシート内でシートを追加します。

③追加をしたシートにプルダウンリストを作成します。

プルダウンリストの作成
→データ>データの入力規則

入力する値

セル範囲:プルダウンリストを表示したいシート名とセルの範囲を指定
条件:リストを範囲で指定:”IMPORTRANGEで読み込んだ値が表示されているシート名”!”プルダウンリストの内容のセル範囲”

このように設定すると、画像のA列のように、プルダウンリストが出来上がります。

今回紹介した関数の他にも便利な関数がたくさんあります。
スプレッドシートは独自の関数も用意されており、様々なオプションもありますので、是非、調べてみてください!

それではまた!