スプレッド シート クエリ。 Googleスプレッドシート ドル円レートを自動取得する関数

Google スプレッドシートからデータをSQLライクに取得してグラフを描く

スプレッド シート クエリ

いまいち具体例がイケていないんですが、いまAさん、Bさんの1年間の昼食、夕食のカテゴリ(和洋中)を元データとし、QUERY関数でデータを統合する例として、複数の同型シートを和結合したうえでピボットで集計というのをやってみたいと思います。 QUERY関数の基本的な使い方はが参考になるかと思います。 また類似の記事としても挙げておきます(こちらはスクリプトを使用した例です)。 具体例ですがスクリーンショットを見てもらった方が早いですね。 こんな感じのシートが2枚(「Aさん」シート、「Bさん」シート)あるということです。 単純和結合 単純に2枚のシートを縦に結合します。 「A2:C」とC列は具体的な番号を指定せず、行の増加に対応できるようにしつつ、where句で空白行は除くという処理をやっています。 これをしないと縦に結合はできるんですがテーブルとテーブルの間に大きな空白ができてしまいます。 また、カラム指定は通常のA,B,Cといった具体的な列番号ではなく、Col1,Col2,Col3のようにします。 なお、全カラム抽出の場合、select句は省略できます。 A2:C,"select A,B" ; QUERY 'Aさん'! SQLでよくFROM句に他のテーブルを加工したもの持ってきたりしますよね。 あれと同じです。 なお、order by句で日付順に並べています。 また、内側(結合前の範囲)のQUERY関数ではselect A,Bのように具体的な列番号をしていますが外側(結合後)のQUERYではCol1,Col2のようにする点にも注意しましょう。 よくよく考えてみれば当然で結合後はそれがアルファベットの何列目かをそもそも定義することが困難です(シート1のA〜C列、シート2のD〜F列を結合する例を思い浮かべれば分かるでしょう)。 結果はこうなります。 ピボット 最後にピボットを織り交ぜてみます。 ここでは縦軸にカテゴリ、横軸に月をとってやってみます。 Aさん、Bさんの昼食と夕食を和結合し、月ごとに合計何食になっているかどうかを集計します。 A2:C,"select 'Aさん',A,B" ; QUERY 'Aさん'! A2:C,"select 'Aさん',A,C" ; QUERY 'Bさん'! A2:C,"select 'Bさん',A,B" ; QUERY 'Bさん'! 12ヶ月ありますが、横に長すぎなのでスクリーンショットでは6月まで収めています。 注意したいのは横軸に1,2,3・・・と月が出力されていますがこれは「文字列」だということです。 えー?っと思うかもしれませんがこれを知らないと後々この表をもとにHLOOKUPなどでこの表から特定の値なり範囲なりを取得するときにハマるかもしれません(かくいう私も2,3回同じことでハマりました)。 他のスプレッドシートのシートも統合できる IMPORTRANGE関数を混ぜるとできます。 すなわち、下記のような形でできるということです。 具体例は割愛します(すみません、正直な話、ちょっと力尽きてきました)。 本当にキリがないので。 ところで私がGASに興味を持つきっかけとなったのも実はこのQUERY関数で、特に今回紹介した結合やピボットを知って「シート上でこんなSQLライクなことができるのか!」と感動したものです。 結局アマゾン電子書籍化(Kindle出版)しました というわけで遂にというタイトルでKindle出版しました。 QUERY関数を自分なりに体系的にまとめてみました。 ここに投稿してから2年半があっという間に過ぎていますがその間いろいろ熟成してきたのでまとめてみたといったところです。 まあその間も出したいなあとは思っていたのですけど。。

次の

Googleスプレッドシートとは? Excelとは何が違う? [Google スプレッドシートの使い方] All About

スプレッド シート クエリ

IMPORTRANGE "スプレッドシートキー", "範囲の文字列" この 『範囲の文字列』のところに、 "持ってきたいシートの名前! 持ってきたいセル"を入力します。 例えば、 データベースという名前のシートの A1セルを参照したい場合は、 "データベース! A1"と入力すれば大丈夫です。 表(範囲セル)を参照する 表を参照したい場合は、 『範囲の文字列』のところに、 "持ってきたいシートの名前! 持ってきたい範囲セル"を入力します。 例えば、 データベースという名前のシートの A1からG9を参照したい場合は、 "データベース! A1:G9"と入力すれば大丈夫です。 集計したデータや表引きを参照する方法 ただ、値を引っ張ってをくるだけじゃ面白くないですよね! IMPORTRANGE関数は、他の関数と組み合わせることでその真価を発揮します。 例1:合計金額を参照する(SUM関数) 合計金額だけを参照したいときは、SUM関数とIMPORTRANGE関数を組み合わせます。 例えば、 別のシートのA列の合計金額を参照したい場合は、次のようにSUM関数と組み合わせます。 例えば、次のような表があったとします。 ここで『1002』をキーとして、商品名である『ぶどう』を参照したい場合は、次のように記述します。 ほら、こんな感じで参照することができました。 IMPORTRANGE関数とVLOOKUP関数を組み合わせれば、このように他のスプレッドシートであっても表引きを実現できるんです。 スプレッドシートすごすぎません?Excelには出来ない芸当ですよね。 例3:スプレッドシートを簡易的なデータベースとして使う 集計したデータや表引きしたデータだけをIMPORTRANGEで持ってくるということは、スプレッドシートをデータベースのように扱うことができます。 スプレッドシートは200万個のセルにしか値を入力できませんが、IMPORTRANGEを使えば実質無限のセルを使うことができるんですよ。 詳しくは割愛しますが、気になる方はこの記事をどうぞ!.

次の

QUERY関数を使って条件付き集計|Googleスプレッドシート応用

スプレッド シート クエリ

スプレッドシート関数の便利な使い方_第四弾として、 上級者向けの スプレッドシート専用の関数、 Query関数 について、ご紹介させて頂きます。 クエリ言語と呼ばれる、クエリ関数付属の数式と組み合わせることで、 例えば、コールリスト中の「アポイントを取得した企業」を「訪問日時順」にして反映させる、といったような使い方が可能です。 これらの関数を使っていない方は、ぜひご活用頂き、業務効率化に繋げて頂ければ幸いです。 <読了目安:10分(作業時間別)> 目次 ・Query関数 ・クエリ言語 ーーSelect ーーWhere ーーorder by ・まとめ Query関数 Query関数とは、 選択した範囲の情報を別のシートに出力できる関数です。 エクセルでも、セルを「=」で結べば、同じことは出来ますが、対象範囲の全てのセルに数式を反映させなければなりません。 しかし、このクエリ関数は、ひとつのセルに関数を入力をすれば、シート全体に反映されます。 文字だけでは伝わりづらいので、図を交えてご説明いたします。 下の図を確認ください。 「左側の赤い線で囲まれている範囲 A1:F23 」の情報を 「右側の青で囲まれている範囲 H1:M26 」へ反映させたいとします。 もちろん、シートをまたいでの反映も可能です。 A1:F23 」のように選択範囲でシート名を指定しましょう。 クエリ言語 Query関数は、クエリ言語(実行命令)と呼ばれる、Query関数専用のサブ関数を使用することによって、 指定した条件の抽出や、条件や並び替えの指示をかけた情報を出力することができます。 例えば、コールリスト中の「アポイントを取得した企業」を「訪問日時順」にして反映させる、といったことが出来ます。 複数あるQuery言語のうち、今回は良く使う3つをご紹介いたします。 下図を確認いただくと、Selectで指定している『A列、C列、E列、F列』の情報のみが抽出されているのがお分かりいただけると思います。 以下の図は、「C列にみかん」の入った情報を抜き出した場合の数式です。 < A1からFの範囲のうち、 C列がみかん>のものを抜き出す。 今回は見本として、以下2つの情報を抽出いたしました。 ・「C列にみかん or ぶどう」の入った情報 ・「C列にみかん」and「E列に3」の入った情報 文章だけだと分かりにくいので、画像と併せて、ご確認ください。 「order by」で列を指定することにより、その列を基準に並び替えすることが出来ます。 今回は、C列の商品がみかんの場合、B列の名前を基準に昇順へ並べ替えて表示させる式をつくります。

次の