【上級編】スプレッドシートの神関数5選！ARRAYFORMULA・QUERY・独自関数を徹底解説

「何百行ものセルに同じ数式をコピーして回るのに疲れた……」「特定の条件でデータを抽出して、さらに並び替えまで自動でやってほしい」

そんなあなたのスプレッドシート作業を根本から変えるのが、Googleスプレッドシート独自の強力な関数群です。

この記事では、数式1つで100行分の計算を終わらせる「ARRAYFORMULA」や、まるでデータベースのようにデータを操る「QUERY関数」など、まさに「達人」への扉を開く5つの関数を紹介します。これらを使いこなせば、あなたの生産性は文字通り「爆上げ」すること間違いなしです！

ARRAYFORMULA

基本的な使い方

配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。

出典：Googleドキュメント エディタ ヘルプ　ARRAYFORMULA

これを読んだだけでは、何が書いてあるのかさっぱり分からないと思いますが、簡単に言うと、「複数のセルに挿入しないといけない関数を先頭の行だけ記入すること」になります。以下、表と一緒に詳しくご紹介させて頂きます。

IF関数

売上表の2021年度の売り上げが300000円以上であれば、「GOOD」、300000円以下であれば、「BAD」を表示する関数をE列とF列に挿入しています。それぞれ結果は同じですが、

• E列：IF関数
• F列：ARRAYFORMULA関数

を入力しています。分かりやすくするために、数式で表示してみましょう。

E列の「IF関数」は、D2からD13まですべてのセルに数式が記載されています。

F列の「ARRAYFORMULA関数」では、D2のセルだけに数式が記載されています。

このように、本来すべてのセルに関数を入力しなくてはならない場合でも、「ARRAYFORMULA関数」を使うと、1つのセルだけに関数を入力すれば、同じ結果を得ることが出来ます。

この数式でのポイントは、通常の「IF関数」では、セルの指定が1つであるのに対して、「ARRAYFORMULA関数」では、セルの指定が「範囲」になっていることです。

VLOOKUP関数

今度は、販売した商品の金額を自動的に入力する関数をD列とK列に挿入しています。それぞれ結果は同じですが

D列：VLOOKUP関数
K列：ARRAYFORMULA関数

を入力しています。先ほどと同様に以下に数式で表示してみます。

IF関数の時と同様に、左側のD2からD20までは数式が記載されていて、右側には一番上のセル、K2だけに数式が記載されています。両者の違いは、数式に表れています。

IF関数と同様に、「VLOOKUP関数」が特定のセルを指定しているのに対して、「ARRAYFORMULA関数」は、セルの範囲を指定しています。

ARRAYFORMULA関数を使う意味

「IF関数」または「VLOOKUP関数」と「ARRAYFORMULA関数」を使っても同じ結果を得ることが出来るなら、どちらの関数を使っても良いのではないかと思われるかもしれませんが、「ARRAYFORMULA関数」を使うことにより、いくつかのメリットを得ることが出来ます。

• 入力するセルが1つで済む
• シートが軽くなる
• ブラウザの表示が早くなる

そして最も大きなメリットは、「後から行を追加しても、自動で数式が適用される」という点が実務では最大の強みです。アンケートフォームの回答集計など、データがどんどん増える場面でとても役に立ちます。

GOOGLETRANSLATE関数

基本的な使い方

テキストをある言語から別の言語に翻訳します。

出典：Googleドキュメント エディタ ヘルプ　GOOGLETRANSLATE

皆様よくご存知の、Googleの翻訳機能です。

実際の使い方

使い方はとても簡単です。「こんにちは」を様々な国の言語に変換してみます。数式は以下になります。

翻訳したいセルを指定して、「”ja“」で日本語を「”en“」英語に変換しています。言語は「言語コード」で指定します。言語コードはGoogle検索で、「言語コード、調べたい国名」を入れるとすぐに表示されます。

各国の「こんにちは」が表示されました。翻訳する文章が長いと、翻訳に時間がかかる場合があります。

応用編

GOOGLETRANSLATE は ARRAYFORMULA との相性が抜群です。この組み合わせは、まさに「スプレッドシートを使っていて良かった！」と思える瞬間のひとつです。

通常なら、100個の単語があれば100回関数をコピーするか、翻訳サイトと行ったり来たりしなければなりませんが、この組み合わせなら「数式たった1つ」で済みます。

例えば、以下のような表を準備したとします。

• A列（日本語）: 翻訳したい単語（例：A2に「りんご」、A3に「みかん」…）
• B列（英語に翻訳）: ここに数式を1つだけ入れます。

B2セルに以下の数式を入力してみてください。

=ARRAYFORMULA(GOOGLETRANSLATE(A2:A10, “ja”, “en”))

• A2:A10: ここがポイントです。本来は「A2」と1つのセルを指定するところを、「範囲」で指定します。
• “ja”, “en”: 日本語から英語へ、という意味です。
• ARRAYFORMULA: これが外側にあることで、「A2からA10まで、全員分翻訳してね！」という命令に変わります。

これだけで、A列に新しい単語を追加するたび、B列に自動的に英語が流し込まれます。また列を増やしてその他の言語にも同時に翻訳することが出来ます。

IMAGE関数

基本的な使い方

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

出典：Googleドキュメント エディタ ヘルプ　IMAGE

説明通り、画像を挿入する関数です。

実際の使い方

セルB3に画像のURLを記入します。セルC３に以下の数式を挿入します。

数式のB3はURLに置き換えても構いません。画像のサイズを変更する時は、以下のモードから選択します。

• =IMAGE(B3,1)：「1」を挿入すると、アスペクト比を維持しながらセル内に収まるようにサイズを変更します。
• =IMAGE(B3,2)：「2」を挿入すると、アスペクト比を無視してセル内に収めます。
• =IMAGE(B3,3)：「3」を挿入すると、画像をもとのまま挿入します。
• =IMAGE(B3,4)：「4」を挿入すると、画像サイズをカスタムで挿入することが出来ます。「例=IMAGE(B3,1,300,500)」

QUERY関数

基本的な使い方

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

出典：Googleドキュメント エディタ ヘルプ　QUERY

「クエリ言語」という聞き慣れない言葉が書かれていると思いますが、英語の「Query（問い合わせる）」にあたる、あらかじめ決められた英単語を入力することです。クエリ言語については以下をご参照ください。なお、クエリ言語を使用する時は、必ず「“（ダブルクオーテーション）」で囲む必要があります。

クエリ言語

クエリ言語	意味
select	どの列をどのような順序で返すのか選択します。 順序の指示がない場合は、デフォルトの順序で返します。
where	条件に合致する行のみを返します。
group by	行全体の値を集計します。
pivot	列内の異なる値を新しい列に変換します。
order by	列の値で行を並べ替えます。
limit	返される行の数を制限します。
offset	最初の行を指定された数だけスキップします。
label	列ラベルを設定します。
format	指定された書式パターンを使用して、特定の列の値を整えます。
options	追加オプションを設定します。

出典：Google Query Language Reference

実際の使い方

select

クエリ言語「select」を使って、表からA列とD列のみを取り出します。数式は以下になります。

「 A1:F20 」で表の範囲を選択して、「 select A,D 」で列を指定しています。「 select 」は小文字で書いてありますが、大文字でも問題ありません。

where

次はクエリ言語、「where」を使って、販売金額（D列）が10000円を超えるものだけを表示させています。

範囲は先程と同じで、「 where D>=10000 」で10000円以上と指定しています。

group by

「group by」を使ってD列の合計金額を求めます。なお、「group by」を使う時は、データを処理するための「集計関数」を一緒に使用します。ここではSUM関数を一緒に使用します。

「A1:F20」の範囲から「“select sum(D)”」でD列の合計金額を返しています。

続いてはもう少し使い方を理解するために、「count」を使って見ていきましょう。

先程の「sum」を「count」に変更しただけになります。C列にある商品名の数を数えています。

「group by」を追加して商品名ごとの数を表示させてみました。しかしこれだとせっかく集計したのに、何の商品かわかりませんので、商品名も表示させてみます。

「select」の後に「C」列を追加して商品名も表示させました。

集計関数

集計関数	内容
avg()	列内のすべての値の平均値を返します。
count()	指定された列の要素数を返します。 （空白の時はカウントされません）
max()	列の最大値を返します。
min()	列の最小値を返します。
sum()	列内のすべての値の合計を返します。

出典：Google Aggregation Functions

pivot

「pivot」を使って、「ピボットテーブル」を作成してみましょう。「QUERY関数」、「クエリ言語」、「集計関数」と色々なものを使用していきますので、少し混乱しがちですが、1つ1つゆっくり確実に理解してください。

まずは商品ごとの売り上げ金額の合計を、「pivot」を使って表示させます。数式は以下になります。

「A1:F20」で範囲を指定し、「select sum(F)」でF列の合計金額を求めます。「PIVOT C」を入れることでC列の項目ごとの合計金額を表示させています。

次に、販売者ごとにどの商品をいくら販売したのかを表示させます。

先程の数式に、B列の指定を追加して、「group by B」でB列の項目を表示させています。参考までに、この数式から「PIVOT C」を抜いてしまうと以下のようになります。

「pivot」を入れることにより、C列が見出しとなって表示されているのが分かると思います。

order by

「order by」を使って、データの並び替えを行ってみましょう。日付と販売数量を大きい順に並び替えます。数式は以下になります。

「A1:F20」で表の範囲を指定し、「select A,E」で列を指定します。「order by E」でE列の順に並び替えるのですが、「desc」で並び替える順番を指定しています。

GOOGLEFINANCE関数

基本的な使い方

Google Finance から現在や過去の証券情報を取得します。

出典：Googleドキュメント エディタ ヘルプ　GOOGLEFINANCE

説明通りの関数で、証券情報や為替情報を返します。

実際の使い方（為替レート）

「GOOGLEFINANCE関数」を使うと為替レートや株価の情報を取得することが出来ます。記入方法はとても簡単です。以下、数式になります。

「currency」とは通貨の意味になります。その後に、表示したい国の通貨コードを2つ並べて記入するだけになります。

為替レートは都度更新されますが、リアルタイムのレートではない場合がありますので、利用される場合は注意してください。

実際の使い方（証券情報）

アメリカの株価情報を表示させます。NASDAQの主要銘柄の一部を表示させる表を作成していきます。B列にある4桁のアルファベットは銘柄コードと言われるもので、それぞれの銘柄を表しています。C列に現在価格、D列に前日の終値、E列に前日比（TEXT関数）を返します。

無事に表示されました。なお、表示できる項目はかなりの数がありますので、詳細は、Googleドキュメントエディタヘルプを御覧ください。

まとめ

お疲れ様でした！Googleスプレッドシートの真髄とも言える「独自関数」の世界、いかがでしたか？

• コピペを卒業し、1つの数式で全てを制御するなら、ARRAYFORMULA
• 自由自在にデータを抽出し、集計・並べ替えまで自動化するなら、QUERY
• 世界中の言語や最新の株価情報をシートに取り込むなら、Google独自の連携関数

これらの関数は、最初は少し難しく感じるかもしれません。しかし、一度その仕組み（ロジック）を理解してしまえば、今まで手作業で何時間もかけていた作業が、嘘のように一瞬で終わるようになります。

まずは、身近な管理表の一部を ARRAYFORMULA に置き換えることから始めてみてください。その便利さを知ったとき、あなたはもう、普通の表計算には戻れなくなっているはずです！

今回も最後までお読みいただき、ありがとうございました。