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

スポンサーリンク
ARRAYFORMULA関数・QUERY関数 Googleスプレッドシート
記事内に広告が含まれています。
スポンサーリンク

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

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

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

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

スポンサーリンク

ARRAYFORMULA

基本的な使い方

構文:=ARRAYFORMULA(配列数式)

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

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

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

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

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

になります。

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

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

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

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

  • IF関数
  • VLOOKUP関数

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

IF関数

IF関数、ARRAYFORMULA関数

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

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

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

を入力しています。

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

IF関数、ARRAYFORMULA関数

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関数

ARRAYFORMULA関数、VLOOKUUP関数

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

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

D列:VLOOKUP関数
K列:ARRAYFORMULA関数

を入力しています。

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

ARRAYFORMULA関数、VLOOKUUP関数

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関数

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

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

数式は以下になります。

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

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

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

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

GOOGLETRANSLATE関数

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

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

IMAGE関数

基本的な使い方

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

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

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

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

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

実際の使い方

IMAGE関数

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

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

=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

QUERY関数、SELECT

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

数式は以下になります。

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

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

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

where

QUERY関数、WHERE

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

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

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

group by

QUERY関数、group by

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

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

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

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

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

QUERY関数、group by

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

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

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

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

QUERY関数、group by count

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

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

QUERY関数、group by count

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

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

出典:Google Aggregation Functions

pivot

QUERY関数、 pivot

pivot」を使って、「ピボットテーブル」を作成してみましょう。

QUERY関数」、「クエリ言語」、「集計関数」と色々なものを使用していきますので、少し混乱しがちですが、1つ1つゆっくり確実に理解してください。

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

数式は以下になります。

=QUERY(A1:F20, “select sum(F) PIVOT C”)

A1:F20」で範囲を指定し、「select sum(F)」でF列の合計金額を求めます。

PIVOT C」を入れることでC列の項目ごとの合計金額を表示させています。

QUERY関数 、pivot

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

=QUERY(A1:F20, “select B, sum(F) group by B PIVOT C”)

先程の数式に、B列の指定を追加して、「group by B」でB列の項目を表示させています。

参考までに、この数式から「PIVOT C」を抜いてしまうと以下のようになります。

QUERY関数、pivot

=QUERY(A1:F20, “select B, sum(F) group by B”)

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

order by

QUERY関数 、order by

「order by」を使って、データの並び替えを行ってみましょう。

日付と販売数量を大きい順に並び替えます。

数式は以下になります。

=QUERY(A1:F20, “select A,E order by E desc”)

A1:F20」で表の範囲を指定し、「select A,E」で列を指定します。

order by E」でE列の順に並び替えるのですが、「desc」で並び替える順番を指定しています。

  • desc:降順(大きいから小さい)
  • asc:昇順(小さいから大きい)

GOOGLEFINANCE関数

基本的な使い方

構文:GOOGLEFINANCE(銘柄, [属性], [開始日], [終了日|日数], [間隔])

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

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

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

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

実際の使い方(為替レート)

GOOGLEFINANCE関数

「GOOGLEFINANCE関数」を使うと為替レートや株価の情報を取得することが出来ます。

記入方法はとても簡単です。

以下、数式になります。

=GOOGLEFINANCE(“currency:USDJPY”)

currency」とは通貨の意味になります。

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

GOOGLEFINANCE関数

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

実際の使い方(証券情報)

GOOGLEFINANCE関数証券情報

アメリカの株価情報を表示させます。

NASDAQの主要銘柄の一部を表示させる表を作成していきます。

B列にある4桁のアルファベットは銘柄コードと言われるもので、それぞれの銘柄を表しています。

C列に現在価格、D列に前日の終値、E列に前日比(TEXT関数)を返します。

  • 現在価格=GOOGLEFINANCE(“NASDAQ:GOOG”, “price”)
  • 前日終値=GOOGLEFINANCE(“NASDAQ:GOOG”,”closeyest” )
  • 前日比=TEXT(C2/D2,”0.00%”)
GOOGLEFINANCE関数証券情報

無事に表示されました。

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

日本株にはまだ対応していないので、今後の改善が期待されます。

スポンサーリンク

まとめ

いかがでしたか。

Googleスプレッドシートオリジナルの関数の中でも代表的なものをご紹介させて頂きました。

とても単純なものと、使い方を考えていると頭の疲れるものまでありますが、どの関数も大変便利な関数です。

幾つかの関数を組み合わせて利用されることが多いので、1つ1つゆっくりと理解して学習していってください。

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

コメント