の続き。
機能の記事のキモはこのとおり。
JavaScript使ってピボットしたり集計したり、あんまり得意じゃないらしいし、コードいじれない人がスプレッドシートから動的に集計の変数いじれるようにしてあげてほしいよね～。
って。
よろしい、ならばやろう。
スプレッドシートにクエリ関数を同的に運用する仕掛けは可能です。
たとえばこんなシチュエーション。
BOSSからの緊急指令。
- ちょっとさぁ、GOOGLEファイナンスでアップルの株価をチャート描画してほしいのよ。
- うーん、開始日付は利用者がきめらえれるようにしてほしいなぁ。
- 例えば、2000年くらいからのチャートも見たいんだけどさ、年平均みたいし、年平均のグラフも長期スパンだったり中期だったり、年単位で選びたいのよね。
かしこまりっ！
じゃあ以下の手順でやってみう。
- GoogleFinance()関数でアップルの株価を取得するよ。
- 開始日を任意にしたいよ。
- 集計関数で年ごとの平均に慣らしたいよ。
- なんなら、年月ごとの平均に慣らしたいよ。
これでA1セルのNASDAQ上場銘柄の終値を一日ずつとってきてくれます。
株価や銘柄の指定方法は公式のヘルプをみてくださいな。
GASでとってこなくても、基本関数でここまでの動的な疑似スクレイピングは可能です。
んじゃあ、もうちょい長期で2010から取得して、年平均に慣らしてみましょっか。
まずは、D列の横に年と月をarrayformula関数で出します。
死ぬほど便利なarrayformula関数の威力が炸裂。
E2セルにこの式入れたらC列の年をダダダっと出してくれます。
=arrayformula(year((C2:C)))
同様に月も出すよ。
=arrayformula(month((C2:C)))
んだらあとは、まずはクエリ関数のベタ打ちで年集計してみます。
=query(C:F,"select E, avg(D) where C is not null group by (E) ")
月平均だとこんな感じ。
=query(C:F,"select F, avg(D) where C is not null group by F ")
年月平均だとこれ。
=query(C:F,"select E,F, avg(D) where C is not null group by E,F ")
じゃあ、これをGASに書くには？になるよね。
GASで書く時には、
銘柄のコード
開始日
平均グループ化の単位（年、月、年月）
これをスプレッドシートに入力した値から取得して
GASで書き込む関数に読み込ませてあれげればよろしい。
すると、スプレッドシートのセルでプルダウンで開始年を選ぶ、
銘柄を選ぶといった動的な株価チャートが描画されるようになる。
適当なセルで年、月、年月をプルダウン選択して、適当なセルにifs関数で
それぞれに合った列を表示させます。
今回なら、こんな感じ。
A3セルにプルダウンを指定して、
A6セルに関数書いてます。
=iferror(ifs(A3="年","E",A3="月","F",A3="年月","E,F"),"")
A2セルにはデータの入力規則で日付型のみを入力するようにしておきます。
で、最後の関数はこうなる。
=query(C:F,"select "&A6&", avg(D) where C is not null group by "&A6&" ")
クエリ関数でセル参照するときはselect句が始まるダブルクォーテーションで一度閉じて、&でセル参照先を指定して、さらに&のあとにダブルクォーテーションで囲う式に続きを書きます。
このあたり、慣れないうちはA6セルを&で囲うように見えてしまって、式を作る法則性が分からず混乱するから気を付けましょう。
あとは、GASでこれをいい感じにコントロールする。
昨日の記事でお伝えしたように、取得したデータの集計用に、このクエリ関数を文字列としてGASエディタに書けばオッケーです。
この例でいう、A6セルや平均化する列としてD列などを変数としてあれこれいじることもできますし、頭の体操代わりに書いてみてください。
つづく。回答編はまた後日。
GAS書かんのかい！