【QUERY関数】order by句で抽出結果を昇順・降順ソートする

QUERY関数8アイキャッチ

みなさんこんにちは!
もり(@moripro3)です。

GoogleスプレッドシートQUERY関数の使い方をシリーズでお届けしています!

前回はwhere句とand,orを使用して複数条件を指定する方法を紹介しました。

【QUERY関数】where句でand,orを使用して複数条件を指定する方法
GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第七回目は、where句とand,orを使用した複数条件での抽出です。andとorを組み合わせた3つ以上の条件指定の方法も紹介しています。

これまでシリーズを通して紹介してきたselect句・where句は、データ抽出をする句です。

QUERY関数で抽出したデータに対して、なんらかの加工ができたらもっと便利ですよね。
たとえば、抽出結果を並び替える、特定の列で集計する、などです。

今回は、抽出結果の並び替えができる order by句の使い方を紹介します。それではみていきましょう!

スポンサーリンク

QUERY関数で並び替えをするメリット

「データの並び替え」と聞くと、スプレッドシートの並び替え機能を思い浮かべる方もいるでしょう。

スプレッドシートの並び替え機能を使うと、元データの並び順そのものが変わってしまいます。さらに、データの値が変わったり、行が追加された場合、再度、手作業で並び替えをしなおす必要があります。

それに対して、QUERY関数で並び替えをするメリットは2つあります。

  • 元データとは別シートに並び替え結果を表示できるため、元データの並び順を維持できる
  • 元データに変更・追加が発生した場合も、自動で並び替えされる

データの並び替えの方法には昇順・降順の2通りがあります。2つの違いを詳しくみていきましょう。

昇順ソート・降順ソートとは

データの並び替えをする方法は下記の2通りあります。

ソート方法 英語表記 説明
降順ソート descending(ディセンディング) 大きい順に並べる 9,7,5,3,1
昇順ソート ascending(アセンディング) 小さい順に並べる 1,3,5,7,9

今回使用するのはこちらの備品購入リストです。D列の「単価」の数字を基準にして、行の並び替えをしてみます。

query8-1

行を並び替えるorder by句

order by句とは、指定された列の値で行を並び替える句です。

order by 列 [ソート方法]

[ソート方法]の部分には下記のいずれかを指定します。

  1. desc(降順)
  2. asc(昇順)

それでは、descとascを使って、次の項から2通りのソートをしていきます。

大きい順に並べる降順ソート(desc)

order by句のソート方法に desc を指定すると降順ソートができます。

order by 列 desc

select句に続けて、order by句を書きます。D列を大きい順に並べる書き方がこちらです。

=query('備品購入リスト'!A:E,"select * order by D desc",1)

query8-2

D列が大きい順、つまり、単価が高い順にソートすることができました。

小さい順に並べる昇順ソート(asc)

order by 句のソート方法に asc を指定すると昇順ソートができます。

order by 列 asc

D列価格の小さい順(安い順)にソートしてみると・・・

ヘッダー行だけは正しく取得できていますが、肝心のデータが表示されません。さて、いったい何がおきているのでしょうか。

=query('備品購入リスト'!A:E,"select * order by D asc",1)

query8-3

スプレッドシートの最終行までスクロールすると、シートの末尾に昇順ソートの結果が並んでいます。

query8-4

元データの範囲をA列~E列の「列全体」としているため、データなしの空白行も含めて、昇順で並び替えられているためです。つまり、数字の100よりも、データなしの方が小さいと判定されるため、空白行が上に寄っている状態です。

これでは困ってしまいますね。対処方法を2つ紹介します。

方法1 元データの範囲を絞る(非推奨)

QUERY関数・第1引数のデータ範囲を絞る方法です。データ範囲を、列全体ではなく、A1セル ~ E12セルと指定すれば、(データが存在する行のみを指定すれば)、空白行が混在することはありません。

query8-5

 

ただ、この方法はオススメしません。非推奨の方法をあえて紹介しているのは、この方法のデメリットをお伝えするためです。

「A1セル ~ E12セル」と範囲を固定すると、元データの13行目以降に行を追加しても、並び替え結果に反映されません。

行を追加するたびにQUERY関数のデータ範囲を設定しなおすのでは、QUERY関数のメリットが台無しですね。データ範囲を固定するのはやめておきましょう。

query8-6

方法2 データが存在する行のみをソート対象にする(推奨)

A列~E列のうち、データが存在する行のみをソート対象とする方法です。

  • 【変更前】A列~E列の、すべての行を並び替える
  • 【変更後】A列~E列の、データが存在する行を並び替える

order by句は、シリーズで紹介してきたwhere句と合わせて使うことが可能です。そこで、where句を使用して「データが存在する行」を抽出します。

「データが存在する行」を抽出する書き方がこちらです。

where 列 is not null

null(ヌル)とは「データがない・空っぽである」という意味です。nullをnotで反転させると、nullではない行、つまり「なんらかのデータがある行」が抽出できます。

「なんらかのデータがある行」に対して、order by句で昇順ソートします。

=query('備品購入リスト'!A:E,"select * where D is not null order by D asc",1)

このようにしておけば、元データの行が増えても、データ範囲を変更する必要がないので、QUERY関数のメリットを活かすことができます。

order by句に複数条件を指定する

order by句は、複数列を指定することが可能です。カンマ区切りで列挙します。

order by 列 [ソート方法], 列 [ソート方法], 列 [ソート方法] …

下記は、前述の項で紹介した降順ソート(desc)の結果です。D列単価が同じ金額の行があります(1,000円と300円がそれぞれ2行ずつ)

同じ値が存在する場合は、元データの並び順がそのまま保たれます。

query8-8

ここで、「D列が同じ金額の場合は、日付の古い順に並べる」というルールを設定します。

日付はシリアル値なので、日付が古い=日付が小さい、となります。つまり、日付を昇順ソートすれば、古い順に並びます。

D列の降順ソートのあとに、カンマ区切りで、A列を昇順ソートする条件を記述します。

=query('備品購入リスト'!A:E,"select * order by D desc, A asc",1)

query8-9

まとめ

今回の記事では、order by 句を使用して、指定の列を条件にして行を並び替える方法を紹介しました。

  • 大きい順に並べる降順ソート(desc)
  • 小さい順に並べる昇順ソート(asc)

また、列全体をソートするときは「空白行」に注意が必要です。where句と is not null で空白行を除外する方法も覚えておきましょう!

スプレッドシートの並び替え機能よりも、QUERY関数の方が、元データの並び順を維持できたり、元データが追加された場合も自動でソートされる、などのメリットが大きいので、ぜひ使ってみてください。

    さて次回は、QUERY関数でデータをグループ化・集計する方法を紹介します。

    【QUERY関数】group by句とcount関数で列のデータ数をカウントする
    GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第九回目は、group by句を使用して値を集計する方法の紹介です。group by句は集計関数と併せて使用します。count関数データの個数を数えます。

    連載目次:GoogleスプレッドシートQUERY関数をマスターしよう

    スプレッドシートのQUERY関数を使って、データ抽出・集計を効率化する方法を紹介しています。

    1. スプレッドシートのQUERY関数を使う最初の一歩!クエリを理解する
    2. QUERY関数の基本!別シートのデータからselect句で列を取得する方法
    3. 【QUERY関数】where句と比較演算子を使って単一条件に一致した行を抽出する
    4. 【QUERY関数】where句とlike演算子を使用して指定の文字を含む行を抽出する
    5. 【QUERY関数】where句で日付データを条件にして行を抽出する
    6. 【QUERY関数】where句で時刻データを条件にして行を抽出する
    7. 【QUERY関数】where句でand,orを使用して複数条件を指定する
    8. 【QUERY関数】order by句で抽出結果を昇順・降順ソートする
    9. 【QUERY関数】group by句とcount関数で列のデータ数をカウントする
    10. 【QUERY関数】group by句とsum関数で列の値の合計値を求める
    11. 【QUERY関数】group by句とavg関数で列の値の平均値を求める
    12. 【QUERY関数】group by句とmax・min関数で列の最大値・最小値を求める
    13. 【QUERY関数】group by句で複数の列をグループ化して集計する
    タイトルとURLをコピーしました