GoogleスプレッドシートのExcelには無い便利な関数

はじめに

二回目の投稿のs.abeです。今回は『Googleスプレッドシート』のExcelには無い便利な関数を紹介したいと思います。今回紹介するのはこちらの関数になります。

REGEXEXTRACT

スプレッドシートでは標準で正規表現がサポートされています。
この関数は正規表現を使って、文字列を抽出することができる関数です。
利用には以下のパラメータを指定します。

  • テキスト
  • 正規表現

正規表現で複数抽出したい場合は、No.3,4,5のように関数を利用することで複数セルに結果を取得することも出来ます。
住所から都道府県と区などに分割する際などに利用すると便利。

参考元:https://support.google.com/docs/answer/3098244?hl=ja

JOIN

複数のセルの値を任意の区切り文字で連結できる関数です。
利用には以下のパラメータを指定します。

  • 区切り文字
  • 値または配列1
  • [値または配列2,3,4・・・]※任意

複数のセルを1つのセルにまとめたい場合等に利用できます。

参考元:https://support.google.com/docs/answer/3094077?hl=ja

SPLIT

文字列を任意の文字で区切って並べることができる関数です。
利用には以下のパラメータを指定します。

  • テキスト
  • 区切り文字
  • [各文字での分割]※任意(デフォルトの場合true)
  • [空のテキストを削除]※任意(デフォルトの場合true)

No.1,2は3つ目のパラメータの比較になります。
3つ目のパラメータがtrueの場合は区切り文字を1つの塊として区切り文字として扱い、falseの場合はそれぞれの文字(tとhとe)を区切り文字として扱うようです。4つ目のパラメータに関しては割愛します。
他にもURLからドメインを抽出する時やcsvファイルを分割する時に便利。

参考元:https://support.google.com/docs/answer/3094136?hl=ja

ARRAYFORMULA

『ARRAYFORMULA』は、先頭行のセルに式を設定しただけで残りの全ての行に値が反映されるようになる関数です。行数が多くなると表示速度に大きな差が出てきます。『ROW』関数を使って行番号を採番するような場合、これまでは

= row() -1

という式を全行に設定していましたが、『ARRAYFORMULA』を使うと先頭行に以下の式を設定するだけで全ての行のセルに行番号が表示されるようになります。

= ARRAYFORMULA(ROW(A2:AA) -1)

上のサンプルはB24にだけ関数がはいっており、Formulaで指定している範囲内全てに出力されます。

注意点
自動で出力されるセルに何かしら入力があると全てのセルでエラーになるので注意。

参考元:https://support.google.com/docs/answer/3093275?hl=ja

IMPORTXML

XML、HTML、CSV、TSV、RSS フィード、Atom XML フィードなど、さまざまな種類の構造化データからデータをインポートする関数です。
利用には以下のパラメータを指定します。

  • 取得するページの URL 
  • XPath

↓取得元のページ(https://tech-cci.io/)

↑のようにページのタイトルを取得なども簡単に実施可能。
XPathはブラウザのデベロッパーツールを使って簡単に取得することができます。
取得方法は↓の画像のように開発者ツールを起動し、該当箇所にカーソルを合わせて右クリック→copy→Copy XPathで取得できます。
特定のページのデータ収集等に利用に便利。

参考元:https://support.google.com/docs/answer/3093342?hl=ja

IMPORTRANGE

スプレッドシートの特定の範囲のデータ参照して表示する関数です。
利用には以下のパラメータを指定します。

  • 参照するスプレッドシートのキー
  • 範囲の文字列

↓参照先

↓参照元シート

rangeで指定した参照元シートの背景色が黄色になったセルが参照できます。
参照するスプレッドシートのキーはURLから取得でき、
例えば、スプレッドシートのURLが【https://docs.google.com/spreadsheets/d/ABCD】の場合は、
スプレッドシートのキーは【ABCD】の部分になります。
表示するだけの機能であり値の編集はできませんが、フィルタの機能を使うことはできます。
また、権限があれば別のスプレッドシートの値を読み込むこともできます。

参考元:https://support.google.com/docs/answer/3093340?hl=ja

QUERY

スプレッドシート上の任意の範囲をDBのテーブルとみなし、そこに対しSQLのような記述でデータの検索ができる関数です。
利用には以下のパラメータを指定します。

  • データ・・・Queryを実行した配列などを指定します
  • クエリ
  • [見出し]※任意(省略した場合や -1 と指定した場合は、データの内容に基づいて推測)

↑Queryで利用するデータ

このサンプルでは別のシート内のデータに対してSQLを実行してみました。
『SELECT, WHERE, GROUP BY, ORDER BY, COUNT, MIN, MAX, SUM』など、SQLの基本的な構文が使用でき、範囲の指定に、先述した『IMPORTRANGE』関数が使用できます。
複数の範囲を指定して、UNIONと同じことができます。
外部結合も工夫すればできるようです。
今回は簡単SQLで試してみましたが今後どこまで利用できるのか試していきたいと思います。

参考元:https://support.google.com/docs/answer/3093343?hl=ja

GOOGLETRANSLATE

スプレッドシートの言語を翻訳する関数。
利用には以下のパラメータを指定します。

  • 変換したい文字列
  • 変換元の言語
  • 変換後の言語

日本語から英語や英語から日本語等をspreadsheetの関数を使って行うことができ、一気に文章を翻訳したい時などには便利。
google翻訳と違いスプレッドシートに記録できるのも便利な点。
単語だけでなく文章も問題なく翻訳できます。

参考元:https://support.google.com/docs/answer/3093331?hl=ja

まとめ

今回紹介できたのは、Googleスプレッドシートの一部の関数になるので今後もよく使う関数などがあったら追加していきたいです。
とても便利なものばかりなので使ってみてください!