知っていたらドヤれる！スプレッドシート独自の関数を10個まとめてみた

4分

SHARE

こんにちは、色んな効率化が大好きなたぬ（@tanuhack）です！

今回紹介する10個の関数は全て、スプレッドシートのオリジナル関数です。

スプレッドシートには『ネットに繋がっていないと使えない』というデメリットがありますが、『常にオンラインである』という性質を最大限活かしたグレイトな関数がたくさん用意されています。

どれも強力すぎて、普段Excelを使っている人から見れば、カルチャーショックを受けるかもしれません！

効率化が捗ること間違いなしなので、1つでも持ち帰って積極的に使ってみて下さい。

IMPORTRANGE：別のスプレッドシートから値を参照

upscale-functions1

構文
=IMPORTRANGE("スプレッドシートキー","範囲指定した文字列")
例：IMPORTRANGE("1-qd48UQf8EJMnEFELTCxZJ5KoTplq--BqeBwlJLBXto","挿入される元データ!A1:J30")

個人的に一番気に入っている関数です。

Excelと違って、別のスプレッドシートの値をリアルタイムで引っ張ってこれるのは、やっぱり便利ですね。

IMPORTRANGE関数を使うとスプレッドシートのクラウド管理の恩恵が、いかに素晴らしいか思い知らされてます…！

応用技：データベースのように使う

通常スプレッドシートには、200万セルしか文字を入力できませんが、IMPORTRANGEを使えばその制約を無理矢理解除することが出来ます。

スプレッドシートの『IMPORTRANGE』関数で200万の制約が無くなるって本当？

ARRAYFORMULA：複数セルへ関数を一気に反映

upscale-functions2
例：TEXT関数を最終行まで反映させる
構文
=ARRAYFORMULA(配列数式または、非配列関数で配列を使用)
例：ARRAYFORMULA(TEXT(B3:B12,"ddd"))

ARRAYFORMULA関数には、専用のショートカットキーがある

なんと入力補完用のショートカットキーが用意されています。『ARRAYFORMULA』って入力するの大変ですもんねｗ

セルに『=』を入力して、以下のショートカットキーを試してみてください！

ショートカットキー
  • Mac：Command+Shift+Enter
  • Windows：Ctrl+Shift+Enter
    • 【保存版】実務の効率化が加速するスプレッドシートのショートカットキー18選！

    読み込み速度を速くしたいならARRAYFORMULA一択！

    Googleスプレッドシートは、関数を数行に渡って入力するより、ARRAYFORMULAを使ったほうが断然速いので、積極的に使っていきましょう。

    スプレッドシートの『ARRAYFORMULA』関数を使って表示速度を高速化させよう！

    UNIQUE：参照範囲をリアルタイムで重複削除

    upscale-functions4

    構文
    =UNIQUE(範囲)
    例：UNIQUE(A2:B21)

    重複する行を破棄して、指定したソース範囲内の一意の行を返します。行はソース範囲内の先頭から順に返されます。

    重複を削除した表を作りたいなら、この関数で決まりですね！

    【知ってると便利】重複データを削除するスプレッドシートのUNIQUE関数の紹介

    FILTER：参照範囲をリアルタイムでフィルタリング

    upscale-functions5
    例：『商品名=A、個数２以上』でフィルタリング
    構文
    =FILTER(範囲, 条件1, [条件2, ...])
    例：FILTER(A3:C21,B3:B21="A",C3:C21>1)

    ソース範囲をフィルタ処理して、指定した条件を満たす行または列のみを返します。

    FILTER関数を使うメリットは、元データを変更しないフィルタリングなので、データの破損の恐れがないことですね^^

    ただ、一つだけデメリットを挙げるならば、関数の性質上、条件にはAND検索しか使えないのがネックです…。

    OR検索を行いたい場合は、後述しているQUERY関数を使えば解決出来ます。

    SORT：選択範囲をリアルタイムで並べ替え

    upscale-functions6
    例：『品番を昇順でソート』
    構文
    =SORT(範囲, 並べ替える列, 昇順, [並べ替える列2, ...], [昇順2, ...])
    例：SORT(A3:B6,A3:A6,1)

    指定した配列または範囲の行を、1列または複数の列の値に従って並べ替えます。

    こちらもFLITER関数と同様に、元のデータを変更しないソートなので、データの破損の恐れがないのがいい感じです。

    ソートの優先順位は前にあればあるほど強くなります。

    QUERY：参照範囲をリアルタイムで配列集計

    upscale-functions3
    例：『商品名=A、個数２以上、個数を昇順でソート』
    構文
    =QUERY(データ, クエリ, [見出し])
    例：QUERY(A3:C21,"WHERE B='A' and C>1 ORDER BY C,A asc")

    『Google Visualization API』のクエリ言語を使用して、データ全体に対するクエリを実行します。

    少しだけ敷居が高そうに見えますが、実はそんなことはありません。

    上手く使いこなせるようになれば、QUERY関数だけで前述したFLITERやSORT関数を組み合わせたような数式を作ることが出来ます。

    クエリ言語

    クエリは、SQLチックな書き方で記述します。

    SELECT：表示させたい列だけ表示させる

    構文
    =QUERY(選択範囲,"SELECT 列1,列2,列3")

    WHERE：条件を満たした行のみ表示させる

    構文
    =QUERY(選択範囲,"WHERE 条件範囲列=条件")
    QUERY関数の６個のWHEREオプションで、データ抽出を捗らせる方法！

    ORDER BY：ソートさせる

    構文
    =QUERY(選択範囲,"ORDER BY 並び替えの基準列 asc（昇順）または、desk（降順）")

    実践編：クエリ関数の使い方

    upscale-functions3
    例：『商品名=A、個数２以上、個数を昇順でソート』
    例題
    =QUERY(A3:C21,"WHERE B='A' and C>1 ORDER BY C,A asc")

    この場合だと、『B列の値がA』かつ『C列の値が2以上』を抽出して、C、A列を基準に昇順でソートしています。

    スプレッドシートのQUERY関数で、条件に一致するデータをまとめて抽出する方法

    IMPORTHTML：HTMLページの表やリストを参照

    upscale-functions7

    構文
    =IMPORTHTML(URL, クエリ, 指数)
    例：IMPORTHTML("https://support.google.com/docs/answer/181110?co=GENIE.Platform%3DDesktop&hl=ja","table",4)

    HTMLページ内の表やリストからデータをインポートします。

    Webサイトにあるテーブルをいちいち入力しなくて良いのは物凄く便利な機能だと思いませんか？

    クエリに指定する値
    テーブル table
    リスト list

    IMAGE：HTMLページの画像を参照

    upscale-functions10

    構文
    =IMAGE(URL, [モード], [高さ], [幅])
    例：IMAGE("https://tanuhack.com/wp-content/uploads/2018/03/upscale-functions.jpg",4,200,300)

    セルに画像を挿入します。

    文字だや数字だらけになりがちなスプレッドシートだからこそ、画像があったほうが分かりやすくなりますよね。

    GOOGLETRANSLATE：テキストをGoogle翻訳

    upscale-functions8

    構文
    =GOOGLETRANSLATE(テキスト, [ソース言語], [ターゲット言語])
    例：GOOGLETRANSLATE(A2,"en","ja")

    テキストをソース言語からターゲット言語に翻訳します。

    Google翻訳では、複数まとめて翻訳できないので、スプレッドシートで一気に翻訳できるのはありがたいですね。

    日本語 ja
    英語 en
    中国語 zh
    韓国語 ko
    ドイツ語 de
    フランス語 fr
    イタリア語 it

    REGEXEXTRACT：正規表現で文字列を置換

    upscale-functions9
    例：正規表現を使って、住所から都道府県を抜き出す
    構文
    =REGEXEXTRACT(テキスト, 正規表現)
    例：REGEXEXTRACT(A2,"..+?[都道府県]")

    正規表現に従って、一致する部分文字列を取り出します。

    「正規表現？何それ、おいしいの？」という方は過去記事をどうぞ！

    知らないと損！正規表現を使ってスプレッドシートの文字列を便利に抽出する方法

    まとめ

    今回は、知っていたらドヤれる、スプレッドシート独自の関数を10個ほど紹介しました。

    他にも独自の関数は山ほどあるんですけど、あまり実用性がないので省略してます。

    この記事をきっかけにExcelからスプレッドシートに乗り換えてくれる人が増えると嬉しいなあ…。

    前の記事

    【1分で設定完了】スプレッドシートをオフラインで使う方法

    次の記事

    【知ってると便利】重複データを削除するスプレッドシートのUNI…