Googleスプレッドシートグーグルオリジナル関数ぜひマスターしたいARRAYFORMULA関数・QUERY関数

今回ご紹介させて頂くのは、「Googleスプレッドシート・オリジナルの関数」になります。

その中でも特に、「ARRAYFORMULA関数」と「QUERY関数」は他の関数と組み合わせて利用すると、大変便利な関数ですので、ぜひマスターして頂きたいのですが、他の関数と比べて、やや難易度の高い関数となっています。

（但し、複数の関数が組み合わされた数式だけを見ると難しく感じますが、1つ1つを分けて考えると理解しやすくなると思います。）

なるべく分かりやすく解説させて頂いていますので、ぜひ一緒に学習していきましょう。

ARRAYFORMULA

基本的な使い方

構文：＝ARRAYFORMULA(配列数式)

Googleのヘルプには以下のように書かれています。

配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。
出典：Googleドキュメントエディタヘルプ　ARRAYFORMULA

これを読んだだけでは、何が書いてあるのかさっぱり分からないと思いますが、簡単に言うと、

「複数のセルに挿入しないといけない関数を先頭の行だけ記入すること」

になります。

以下、表と一緒に詳しくご紹介させて頂きます。

「ARRAYFORMULA関数」はどんな関数に対してでも利用出来る訳ではありません。

組み合わせる関数によっては、魅力が低い場合もあります。

その中でも最も組み合わせの良い代表的な関数が以下の2つになります。

IF関数
VLOOKUP関数

ARRAYFORMULA関数をセルに入力する時は、＝の後に、Ctrl+Shift+Enterを押すと数式を入力することが出来ます。

IF関数

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

それぞれ結果は同じですが、

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

を入力しています。

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

IF関数の使い方については、以下の記事をご参照ください。

Googleスプレッドシート論理の関数の使い方 IF関数・IFS関数・IFERROR関数を使いこなす

今回は様々な状況で活躍する「論理」の関数についてご紹介させて頂きます。「論理」と聞くと何だか難しそうですが、実際は凄くシンプルな関数が多く、「特定の条件の時にどのように表示させるか」という関数だと考えて頂いて良いかと思います。「論理」の...

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

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

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

IF関数：=IF(D2>=300000,”GOOD”,”BAD”)

ARRAYFORMULA関数：=ARRAYFORMULA(IF(D2:D13>=300000,”GOOD”,”BAD”))

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

VLOOKUP関数

今度は、販売した商品の金額を自動的に入力する関数をD列とK列に挿入しています。

それぞれ結果は同じですが

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

を入力しています。

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

VLOOKUP関数の使い方については、以下の記事でご紹介させて頂いております。

Googleスプレッドシート関数の使い方参照データから指定した値を検索する VLOOKUP関数

今回は「参照」の関数の使い方についてご紹介させて頂きます。中でも「VLOOKUP関数」はとても便利な関数であり、他の関数と組み合わせて使うことの多い関数ですので、ぜひこの機会にしっかりと身につけてください。 ROW関数基本的な使い方構...

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

両者の違いは、数式に表れています。

VLOOKUP関数：=VLOOKUP(C2,$D$23:$E$27,2,0)

ARRAYFORMULA関数：=ArrayFormula(VLOOKUP(J2:J20,$K$23:$L$27,2,0))

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

ARRAYFORMULA関数を使う意味

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

メリット

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

GOOGLETRANSLATE関数

基本的な使い方

構文：GOOGLETRANSLATE(テキスト, [ソース言語, ターゲット言語])

Googleのヘルプには以下のように書かれています。

テキストをある言語から別の言語に翻訳します。
出典：Googleドキュメントエディタヘルプ　GOOGLETRANSLATE

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

実際の使い方

使い方はとても簡単です。

「こんにちは」を様々な国の言語に変換してみます。

数式は以下になります。

=GOOGLETRANSLATE(C3,”ja”,”en”)

翻訳したいセルを指定して、「”ja“」で日本語を「”en“」英語に変換しています。

言語は「言語コード」で指定します。

言語コードはGoogle検索で、「言語コード、調べたい国名」を入れるとすぐに表示されます。

各国の「こんにちは」が表示されました。

翻訳する文章が長いと、翻訳に時間がかかる場合があります。

IMAGE関数

基本的な使い方

構文：IMAGE(URL, [モード], [高さ], [幅])

Googleのヘルプには以下のように書かれています。

セルに画像を挿入します。
出典：Googleドキュメントエディタヘルプ　IMAGE

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

実際の使い方

セルB3に画像のURLを記入します。

セルC３に以下の数式を挿入します。

=IMAGE(B3)

数式のB3はURLに置き換えても構いません。

画像のサイズを変更する時は、以下のモードから選択します。

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

QUERY関数

基本的な使い方

構文：QUERY(データ, クエリ, [見出し])

Googleのヘルプには以下のように書かれています。

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列のみを取り出します。

数式は以下になります。

=QUERY(A1:F20,”select A,D”)

「 A1:F20 」で表の範囲を選択して、「 select A,D 」で列を指定しています。

「 select 」は小文字で書いてありますが、大文字でも問題ありません。

where

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

=QUERY(A1:F20,”where D>=10000″)

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

group by

「group by」を使ってD列の合計金額を求めます。

なお、「group by」を使う時は、データを処理するための「集計関数」を一緒に使用します。

ここではSUM関数を一緒に使用します。

=QUERY(A1:F20,”select sum(D)”)

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

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

=QUERY(A1:F20,”select count(C)”)

先程の「sum」を「count」に変更しただけになります。

C列にある商品名の数を数えています。

「group by」を追加して商品名ごとの数を表示させてみました。

しかしこれだとせっかく集計したのに、何の商品かわかりませんので、商品名も表示させてみます。

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

集計関数

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