【Googleスプレッドシート】QUERY関数のFORMAT句!数値・日付の表示形式設定

QUERY関数を使っていると、日付がシリアル値で表示されたり、数値が桁区切りなしで並んだりして困った経験はありませんか。実はQUERY関数には「FORMAT句」という便利な機能が備わっています。このFORMAT句を使えば、日付や数値の表示形式を自由に指定できるのです。本記事では、FORMAT句の基本的な使い方から、日付や数値の具体的な書式設定方法までをわかりやすく解説します。

この記事を読み終えると、QUERY関数の結果を見やすく整形できるようになります。データ集計やレポート作成の際に、そのまま使える表示形式を手に入れてください。

QUERY関数のFORMAT句とは

QUERY関数はGoogleスプレッドシートのデータベース操作を担う強力な関数です。通常のQUERYはデータの取得・絞り込み・並べ替えなどを行います。しかし、日付列はシリアル値(数値)、数値列も生の値で返ってくるため、そのままでは見にくいことがあります。そこで登場するのがFORMAT句です。FORMAT句をQUERY文の末尾に追加することで、結果の表示形式を自由に指定できます。この句を使えば、日付を「2024年01月15日」、数値を「¥1,234.56」のように整形できるのです。FORMAT句はGoogleスプレッドシート独自の拡張機能であり、SQLのFORMATとは少し異なります。書式文字列はGoogleスプレッドシートのカスタム日付・数値書式に準拠しています。

FORMAT句で数値の表示形式を設定する手順

最初に、数値列に対してFORMAT句を使う方法を解説します。基本的な構文は以下の通りです。

=QUERY(A1:C100, "SELECT B, C FORMAT C '#,##0'", 1)

上記の例では、C列の数値が千桁区切りで表示されます。では、実際の手順を見ていきましょう。

1. QUERY関数の基本形を作成します
   まずは通常のQUERY式を入力します。例えば、範囲A2:C10からB列とC列を取得する場合、=QUERY(A2:C10, "SELECT B, C")とします。ここまでは通常と同じです。
2. SELECT句の後にFORMAT句を追加します
   SQL文の末尾(ORDER BYより前)にFORMAT C '#,##0'と書き加えます。Cは列ラベル(A、B、Cなど)です。書式文字列はシングルクォーテーションで囲みます。
3. 書式文字列を目的に合わせて変更します
   例えば、小数点以下2桁を表示するなら'#,##0.00'、パーセント表示なら'0.00%'、通貨記号付きなら'$#,##0.00'とします。日本円の場合は'¥#,##0'のように直接記述できます。

なお、FORMAT句は複数列を同時に指定できます。その場合はFORMAT C '#,##0', D '0.0%'のようにカンマで区切ります。

数値の代表的な書式例

書式文字列	表示例(元の値 1234.567)
‘#,###’	1,235(四捨五入)
‘#,##0.00’	1,234.57
‘0.00%’	1234.57%(元の値を百分率に変換)
‘¥#,##0’	¥1,235

FORMAT句で日付の表示形式を設定する手順

日付列を扱う場合も、FORMAT句で簡単に整形できます。日付の書式は、年・月・日・曜日などを組み合わせた文字列を指定します。

=QUERY(A1:C100, "SELECT A, B FORMAT A 'yyyy-mm-dd'", 1)

A列に日付データがある場合、上記の式で「2024-01-15」のように表示されます。手順は数値の場合と同様です。

1. QUERY文を用意します
   例えば=QUERY(A2:C10, "SELECT A, B, C")とします。
2. FORMAT句を追加し、日付列を指定します
   日付列がA列ならFORMAT A 'yyyy/mm/dd'とします。
3. 書式を好みに変更します
   日本語の年月日表示には'yyyy年mm月dd日'、曜日を付けたい場合は'yyyy/mm/dd (ddd)'とします。dddは英字略称(Mon, Tue…)、ddddは英字フルスペルです。日本語の曜日を表示する方法はありませんが、TEXT関数と組み合わせることで実現可能です。

日付の代表的な書式例

書式文字列	表示例(2024年1月15日)
‘yyyy-mm-dd’	2024-01-15
‘yyyy/mm/dd’	2024/01/15
‘yyyy年mm月dd日’	2024年01月15日
‘yyyy/mm/dd (ddd)’	2024/01/15 (Mon)
‘mm/dd/yy’	01/15/24

FORMAT句を使う際の注意点

書式文字列はシングルクォーテーションで囲む必要があります

FORMAT句内で使用する書式文字列は、必ずシングルクォーテーション(‘)で囲まなければなりません。ダブルクォーテーション(“)ではエラーになります。また、QUERY全体の文字列はダブルクォーテーションで囲む必要がありますので、区別して使い分けてください。

FORMAT句は列ラベルで指定します

FORMAT句では、SELECTで指定した列のラベル(A、B、Cといったアルファベット)を使います。列見出しではありません。もしSELECTで計算列やラベルを変更した場合は、その新しいラベルを使用します。例えばSELECT A, B*1.1 AS Cとした場合、FORMAT句ではC列をFORMAT C '#,##0'のように指定します。

QUERY内の文字列と区切りに注意します

書式文字列の中にシングルクォーテーションを含める必要は通常ありませんが、もし含める場合はエスケープ(”)します。また、複数列のFORMATはカンマで区切りますが、カンマの前後にスペースを入れても構いません。

FORMAT句は表示のみを変更し、元のデータの値は変わりません

QUERY結果はスプレッドシート上で見た目が変わりますが、セルに格納されている実際の値は変わりません。日付はシリアル値、数値は数値のままです。そのため、他の関数で参照する場合は元の値が使われます。表示だけを変更したい場合に適しています。

QUERY関数のFORMAT句と他の書式設定の比較

方法	特徴	適用タイミング	元データへの影響
QUERYのFORMAT句	QUERY結果の表示形式を直接指定可能なため、抽出と整形を一度に行えます。	QUERY関数の計算時	なし(表示のみ)
スプレッドシートの表示形式(書式設定)	セル範囲を選択してメニューから書式を適用します。QUERY結果にも後から適用できます。	QUERY結果が出力された後	なし(表示のみ)
TEXT関数で変換	TEXT関数を使って別セルに整形結果を出力します。QUERY内では使えません。	QUERY結果を参照した後	TEXT関数の結果は文字列になります

FORMAT句の利点は、QUERY関数の計算と同時に整形が完了するため、後から書式を設定する手間が省けることです。特にQUERY結果をそのまま別の関数やグラフで使う場合、表示形式が保たれるわけではない点に注意が必要です。

以上、QUERY関数のFORMAT句について、数値と日付の表示形式設定方法を解説しました。FORMAT句を使えば、QUERYの結果を思い通りに整形できるようになります。最初は書式の指定に戸惑うかもしれませんが、表にまとめた例を参考に試してみてください。特に日付の’yyyy年mm月dd日’や数値の’#,##0.00’は頻繁に使う書式です。ぜひ自分のデータに合わせてカスタマイズし、見やすいレポート作成に役立ててください。