かもメモ

自分の落ちた落とし穴に何度も落ちる人のメモ帳

Googleスプレッドシート QUERY で取ってきたデータの複数のセルをそれぞれフォーマットしたい

Spreadsheet Google Drive Tips

数値で計算しているシートから QUERY や IMPORTRANGE を使ってデータを取ってきて、SELECT文で必要な絡むだけにした上で単位を付けたりフォーマットして表示したい。

元のデータ例 シート名 販売目標

A B C D E
1 エリア 🍺消費量 (kl, 2016) 目標売上量 (kl) 350ml缶換算 (本) 目標販売人数 (人)
2 イバラキスタン 85192 708.57 2024493 34947
3 トチギニア共和国 60654 506.85 1448131 24997
4 グンマー帝国 57577 478.92 1368350 23620
5 サイタマニア共和国 208964 1729.53 4941527 85300
6 チバラキスタン 202300 1685.24 4814960 83115
7 帝都 736655 6591.23 18832076 325078
8 ヨコハマ=カワサキ二重帝国 297639 2478.30 7080871 122229
9 ヒョーゴスラビア連邦 199758 1669.90 814488 82359

このシートからデータを取ってきて、A列、C列、E列だけを表示し、C, E 列は3桁ごとにコンマ区切りにして単位を付けて表示したい

QUERY でフォーマットする

The syntax of the query language is composed of the following clauses. Each clause starts with one or two keywords. All clauses are optional. Clauses are separated by spaces. The order of the clauses must be as follows:
format Formats the values in certain columns using given formatting patterns.
cf. Query Language Reference (Version 0.7)  |  Charts  |  Google Developers

QUERY関数の第2引数は SELECT XXX〜 なSQL文だけでなく format 句も使用することができる。

QUERY関数の第2引数で format 句を使用するには format {対象} '{フォーマット形式}' のように書く。 例えば、「B列を3桁区切りで小数点第二位まで表示して kl を付けてヘッダーも含めて表示」なら次のような感じ

QUERY(B:B, "format B '#,##0.00 kl'", true)

フォーマット形式はシングルクォート ' で加工必要があるので注意！ (シングルクォートで囲わないとエラーになる)

今膜区切りで複数のセルのフォーマットをすることができる

format 句はフォーマットをコンマ区切りで複数設定することが可能

QUERY(A:E, "format B '#,##0.00 kl', E '#,## 人'", true)

B列は 3桁区切り小数点第二位まで表示して最後に klを付け、E列は 3桁区切りで最後に 人 を付けた形式で表示される

QUERY の第2引数の SELECT 文 に続けて format 句を付けることが可能

A、C、E列だけ、表示してフォーマットするならこんな感じ (SQLっぽく大文字にしてます)

QUERY(A:E, "SELECT A, C, E FORMAT C '#,##0.00 kl', E '#,## 人'", true)

👇

A C E
1 エリア 目標売上量 (kl) 目標販売人数 (人)
2 イバラキスタン 708.57 kl 34,947 人
3 トチギニア共和国 506.85 kl 24,997 人
4 グンマー帝国 478.92 kl 23,620 人
5 サイタマニア共和国 1,729.53 kl 85,300 人
6 チバラキスタン 1,685.24 kl 83,115 人
7 帝都 6,591.23 kl 325,078 人
8 ヨコハマ=カワサキ二重帝国 2,478.30 kl 122,229 人
9 ヒョーゴスラビア連邦 1,669.90 kl 82,359 人

✧＼\ ٩( 'ω' )و /／✧ ﾖｼ!

SELECT と FORMAT を一緒に使う時の注意

次のようなものはエラーになる

  1. select で選択されてないカラムをフォーマットしよとしたらエラー 
    QUERY(A:E, "SELECT A, B, E FORMAT C '#,##0.00'", true)
    => #VALUE! SELECT していない C列をフォーマットしようとしている
  2. select より前に format があるとエラー 
    QUERY(A:E, "FORMAT B '#,##0.00' SELECT A, B, E", true)
    => #VALUE! どの列が SELECT されるかわからない状態で FORMAT しようとしてるからエラーになるのだと思う

他のスプレッドシートからデータを取ってくる場合

他のスプレッドシートからデータを取ってくるには IMPORTRANGE() を使う
詳しい使い方は👇を参考にしてください。 [https://chaika.hatenablog.com/entry/2015/11/26/143853:embed]

IMPORTRANGE() で取ってきたデータを同じように QUERY で必要なカラムだけをSELECTしてフォーマットをすればOK。IMPORTRANGE() で取ってくる場合は、列の指定がA, B, C ... ではなく、選択した範囲の先頭からCol1, Col2, Col3 ... のようになるので注意 (B:Cのように選択した場合は B列がCol1になる)

別のスプレッドーシートで先の販売目標シートからA列、C列、E列だけを取ってきて、C, E 列は3桁ごとにコンマ区切りにして単位を付けて表示するなら次のような感じ

QUERY(
  IMPORTRANGE("{SHEET-KEY}", "販売目標!A:E"),
  "SELECT Col1, Col3, Col5 format Col3 '#,##0.00 kl', Col5 '#,## 人' ", true
)

ポエム

QUERY() の SELECT 文に FORMAT くっつけて使える、それも複数のカラムのフォーマットもできるの知らなかった。
今まで QUERY で取ってきたのを ARRAYFORMULA() でフォーマットしたりしてたﾖ…
まぁこのタイミングで知れてよかったです。

[参考]

第２話

第２話

👆アマプラにあったメタノールって2012年にチェコで実際に起こった密造酒事件を元にしたドラマ映画面白かった。

よなよなエール [ 350ml×24本 ] クラフトビール ペールエール エールビール

よなよなエール [ 350ml×24本 ] クラフトビール ペールエール エールビール