【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列を大きい順に並べる書き方がこちらです。

query8-2

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

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

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

order by 列 asc

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

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

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関数のメリットを活かすことができます。

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

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

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

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

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

query8-8

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

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

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

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関数で列のデータ数をカウントする

      投稿者プロフィール

    もり
    もりシステムエンジニア・Webライター
    「ラクするために全力を尽くす」をモットーに日々勉強。退屈なことはプログラミングで片づけよう。
    事務作業をとことんラクにできるITネタを発信していきます。

    お気軽にフォローしてくださいね!

    もりの詳しいプロフィールはこちら
    ▽▽▽

    コメント

    タイトルとURLをコピーしました