Googleスプレッドシートのデータ抽出に便利なQUERY関数について、前回はGROUP BY句を使用して値の個別の組み合わせごとに1つの行を作成する方法をご紹介しました。
QUERY関数No3
- GROUP BY句の使い方
- PIVOT句の使い方
- クロス集計
今回はデータの並べ替え(ソート)ができる「ORDER BY」句についてご紹介します。抽出する行数を操作できる「LIMIT」句と「OFFSET」句についても解説しているので、ぜひ参考にしてみてください。
QUERY関数のORDER BY句の使い方
ORDER-BY句を使用して、昇順や降順に並べ替えたデータを抽出する方法をご紹介します。ORDER-BY句が理解できたら、LIMIT句やOFFSET句と組み合わせてデータ抽出してみましょう。
ORDER BY句とは?
ORDER-BY句は指定した列の値を基準として、行全体を昇順または降順に並べ替えます。
- asc(昇順):五十音順(あ→ん)、数値の小さい順
- desc(降順):五十音順(ん→あ)、数値の大きい順
例えば「=QUERY(A1:D7,”SELECT * ORDER BY D asc”)」のように、D列を基準として対象データの行全体を昇順に並べ替えて見ましょう。
ORDER-BY句では「ひらがな・漢字・カタカナ」が混合している列は正しく並び替えできません。
価格が小さい順に、対象データの行全体が並べ替えられました。
なおデータ範囲を「A:D」のように列全体とする場合、WHERE句に「is not null」を指定することで空白を除外できます。WHERE句の詳細については、WHERE句の使い方を参照下さい。
ORDER-BY:QUERY(データ範囲,”SELECT 抽出列
ORDER BY 基準列
ascまたはdesc”)
ORDER BY句に複数条件を指定する方法
ORDER-BY句には、並び替えの基準となる列を複数指定することも可能です。
複数の列を指定した場合、左から順に並べ替えの優先度が高くなります。
例えば「=QUERY(A1:D7,”ORDER BY A desc, D asc”)」のように、ORDER-BY句にA列とD列の2つの列を指定してみましょう。
例ではSELECT句を省略しています。SELECT句を省略(またはアスタリスクを指定)した場合、データ範囲のすべての列を抽出対象とします。
先にA列を降順に並べ替え、その範囲内でD列が昇順に並べ替えられました。
抽出後のデータと元データを比較してみると、日付が降順に並べ替えられ、同じ日付内で価格が昇順に並べ替えられているのが分かります。
QUERY(データ範囲,”ORDER BY 基準列1
ascまたはdesc, 基準列2
ascまたはdesc”)」
QUERY関数のLIMIT句とOFFSET句(行数制限)
抽出するデータの行数を操作する、LIMIT句とOFFSET句について解説します。ORDER-BY句とセットで使用すると便利なクエリです。
LIMIT句の使い方
LIMIT句は、抽出するデータの「行数」を制限するのに使用します。
例えばORDER-BY句と組み合わせて「=QUERY(A1:D7,”ORDER BY D asc limit 2″)」のように指定すれば、並び替えたデータの上位2行だけを抽出可能です。
LIMIT:QUERY(データ範囲,”ORDER BY 列
ascまたはdesc limit 行数
“)
OFFSET句の使い方
OFFSET句は、抽出するデータを指定した「行数」分スキップするのに使用します。
例えばORDER-BY句と組み合わせて「=QUERY(A1:D7,”ORDER BY D asc offset 2″)」のように指定すれば、並び替えたデータを上位2行だけ除外して抽出できます。
OFFSET:QUERY(データ範囲,”ORDER BY 列
ascまたはdesc offset 行数
“)
LIMIT句とOFFSET句を組み合わせる方法
LIMIT句とOFFSET句を組み合わせれば、中間のデータだけを抽出できます。
例えばORDER-BY句と組み合わせて「=QUERY(A1:D7,”limit 3 offset 1″)」のように指定すると、対象データの最初の1行を除いた合計3行を抽出します。
QUERY関数のクエリの種類
今回はデータの並び替え抽出に便利な「ORDER-BY句」、「LIMIT句・OFFSET句」の3つのクエリについてご紹介しました。QUERY関数で使えるクエリには他に以下のようなものがあります。
- select :特定の列を指定した順序で抽出します。
- where :条件を満たすセルだけを抽出します。
- group by: 列内の同じ要素の値を集計します。
- pivot :group byで抽出したデータに分類要素を追加します。
- order by 行を列の値で並べ替えます。(ピックアップ)
- limit :抽出する行数を指定します。(ピックアップ)
- offset:指定した数の最初の行をスキップします。(ピックアップ)
- label:列ラベルを設定します。
- format:特定の列の値を書式設定します。
- options :追加オプションを設定します(no_formatまたはno_values)
QUERY関数No5
- FORMAT句の使い方
- LABEL句の使い方
- 表示形式の設定