【QUERY関数③】コピペ可|数値の範囲指定と並び替え(order by)

関数教室
新任
新任

前回の話で、複数の条件を満たす場合部分的に満たす場合の抽出方法は分かったのですが、数値範囲で「ここからここまで」という条件指定もできるんですか?

人事のプロ
人事のプロ

これまでは基本構造を知るために文字列を条件として見てきたけど、もちろん数値で条件指定することも可能よ。

こちらも大枠は変わらないので、覚えていきましょう。

人事業務では、従業員に紐づく様々な数値情報を扱います。

日々の労務管理では勤怠や有給取得状況のチェック、人事考課(評価)の際には業績の収集・分析まで行う企業もあります。

いずれのケースも情報の定期更新が想定されるため、QUERY関数を活用しながらスプレッドシートを仕組み化しておきましょう

この記事を読んだら
  • 行(従業員)を数値で条件指定することで、「ここからここまで」という範囲で抽出できる
  • 数値または文字列で行の並び替えができる
    • 昇順降順を選択できる
    • 複数列を条件指定できる
  • 空白のセルを抽出条件として指定できる
    • 未入力者の洗い出し等に便利
    • 昇順で並び替える場合には必須

数値範囲で条件指定

今回も、読売ジャイアンツを例に見ていきましょう。

読売ジャイアンツの選手データ
プロ野球データFreak2020年データより

数値範囲の指定方法は4種類あります。(=<>の使い方は初回で解説)

  • >(〜より大きい)
  • <(〜より小さい)
  • >=(〜以上)
  • <=(〜以下)

一番下の「<=」を使って「在籍年数10年以下の選手」を抜き出す場合、以下のようになります。

スプレッドシートでQUERY関数を使用して、在籍年数10年以下の選手を抜き出したキャプチャ
新任
新任

等号(=)で条件指定する場合と形式は同じですね。

人事のプロ
人事のプロ

そうそう。
このような選択肢があることだけ覚えておけば、問題なく使えるはずよ。

あと、検索条件が数値であれば、文字列のように前後の「'(シングルコーテーション)」は不要なので注意してね。

新任
新任

はい!

(でもせっかく抽出できたのに並び順がぐちゃぐちゃで使い物にならないなぁ…でも言いづらいなぁ…)

仙人
仙人

並び替えをしたいか?
いいだろう!

次へ進みたまえ!

新任
新任

心を読まれている?!
てか誰?!

以下の文字列をコピーして、早速使ってみましょう。(緑文字を置き換え)


=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where 検索列<=検索値")

Copy
  • >(〜より大きい)
  • <(〜より小さい)
  • >=(〜以上)
  • <=(〜以下)
【補足】シートに関数をコピーする方法
  • 貼り付けたいセルにカーソルを合わせて「fx」欄に貼り付け
    • キーボード「F2」ボタン(セル編集)を押してから貼り付けでも可
スプレッドシートに関数をコピペするには、対象セルにカーソルを合わせて「fx」欄に貼り付けることを説明したキャプチャ

【order by】数値で並び替え

指定した条件で従業員を抽出できても、業務で活用するのに並びを決めておくことは必須ですよね。

QUERY関数では、最後に「order by」を付け加えるだけで、1つの式の中で並び順も指定できます

  • order by 列(指定した列で並び替え)
    • asc(昇順で)
    • desc(降順で)

【desc】降順

上記で抜き出した「在籍年数10年以下の選手」を「ベテラン順(年数が長い順」に並び替えると、以下のようになります。

スプレッドシートでQUERY関数を使用して、「在籍年数10年以下の選手」を「ベテラン順(年数が長い順)」に並び替えたキャプチャ
新任
新任

おおー!

リストが綺麗に整いましたね!

人事のプロ
人事のプロ

order byはこのようにwhere句の後ろにつけるのが原則なので気をつけて

ちなみに並び替えだけならwhere句なしで使用することも可能よ。

新任
新任

ちなみになぜ降順から教えてくれたのでしょうか?

パターンとして普通は昇順からでは…?

人事のプロ
人事のプロ

鋭いわね。

すぐに理由が分かるわ…。

【asc】昇順

失敗事例(読み飛ばしてもOK)

先程のdesc(降順)をasc(昇順)に変えて「在籍年数10年以下の選手」を「若手順(年数が短い順」に並び替えると、以下のようになります。

スプレッドシートでQUERY関数を使用して、「在籍年数10年以下の選手」を「若手順(年数が短い順)」に並び替えたが、シートが空白ばかりのキャプチャ
新任
新任

ちゃんとやって下さいよ。

人事のプロ
人事のプロ

まだ状況が分かっていないようね。

下へスクロールすると…。

スプレッドシートでQUERY関数を使用して、「在籍年数10年以下の選手」を「若手順(年数が短い順)」に並び替えたが、シートが空白ばかりのキャプチャ
新任
新任

ぎょえーーー!!

911行目って…どういうことですか?!

スプレッドシートでQUERY関数を使用して、「在籍年数10年以下の選手」を「若手順(年数が短い順)」に並び替える数式のキャプチャ
参照先データを「列まるごと」指定している
スプレッドシートでQUERY関数を使用する時の参照先データのキャプチャ
参照先データ
人事のプロ
人事のプロ

数式の前半に注目してみて。

参照先データをセルの範囲ではなく「列まるごと」指定しているでしょ?

それをasc(昇順)で並び替えているから、データ下部にある空白セル最小とみなされて優先的に抽出されてしまったわけ

G列の在任年数も空白だから「10年以下」という条件も満たしてしまっているのよ。

新任
新任

な、なるほど…。

ではセルの範囲で指定するしかないんですか?

スプレッドシートでQUERY関数を使用して、「在籍年数10年以下の選手」を「若手順(年数が短い順)」にセルの範囲指定で並び替えたキャプチャ
セルの範囲で指定した場合
人事のプロ
人事のプロ

んー。

確かに空白セルが除かれて一時的には大丈夫なんだけど、今後参照先データに情報(行)が追加されていった時、数式の参照範囲も伸ばさないといけなくなるのよ

新任
新任

数式内の行数を都度変更するのはしんどいですね。

特に人事関連だと対象者の漏れは致命的なので他の関数を使った方が良いのでは…。

人事のプロ
人事のプロ

そうね、でも安心して!

実は、少し工夫するだけでこの問題は解決できるのよ。

「バックデータからの自動反映」というQUERY関数の強みを活かせるように覚えていきましょう!

成功事例(【is null】空欄を攻略)

昇順で並び替える際の問題点
  • 大量の空白セルが(最小値として)抽出されてしまい、本来必要なデータが埋もれてしまう
    • 関数で参照先「列まるごと」指定しているため、データ下部の空白セルも参照対象になっていることが原因

上記の問題点を解決するために、空白セルを除外するように条件指定します

ここで登場するのが、空白を表す「is null」です。

先程の失敗事例と同じ条件で、「在籍年数10年以下の選手を若手順(年数が短い順に並び替える」ためにis nullを活用すると、以下のようになります。

スプレッドシートでQUERY関数を使用して、「在籍年数10年以下の選手」を「若手順(年数が短い順)」に空白セルを除いて並び替えたキャプチャ

本記事で解説している「数値の範囲指定(<=)」「並び替え(order by)」に加えて、以前解説した「文否定(not)」「複数条件(and)」も含まれているフルコースになります。

復習(※)も兼ねて、この機会に是非セットで身につけて、実務にお役立て下さい。
notの解説andの解説

新任
新任

過去に倒した敵たちが再登場してくるパターンですね(笑)

各要素を切り分けて考えると、「G列(年数)が空白ではないという条件を追加していることが分かります!

人事のプロ
人事のプロ

is nullもあくまでwhere句にある条件指定の1つに過ぎないから、andで繋いだりnotを前に置いたり同じように使ってみてね。

新任
新任

でもこんなに長い数式、自分でイチから作れる気がしないです…。

人事のプロ
人事のプロ

そんなに不安がらなくて大丈夫よ。

最初からこの数式が頭に浮かんで、その通りにセルに打ち込んで作れるのはかなりの熟練者

まずは参照先の範囲と列(select句)を指定して大枠を作り、後から行の抽出条件(where句)を追加していくイメージかしら

並びなんて最後でOKよ。

複数条件による並び替え

並び替える順番(asc/desc)について見ていきましたが、最後に複数の並び替え条件を指定する方法について紹介します

先程、在籍年数10年以下の選手を若手順(年数が短い順に並び替えることができましたが、同じ年数の中で守備位置がバラバラなのは揃えたいですよね。

スプレッドシートでQUERY関数を使用して、「在籍年数10年以下の選手」を「若手順(年数が短い順)」に空白セルを除いて並び替えたキャプチャ
並び替え条件は年数(D列)昇順のみ

実は数値だけでなく、文字列でも並び替えが可能なので、あえて「①年数(数値)を昇順、②守備(文字列)を降順」という条件で並び替えてみます。

スプレッドシートでQUERY関数を使用して、「在籍年数10年以下の選手」を「①年数(数値)を昇順、②守備(文字列)を降順」で空白セルを除いて並び替えたキャプチャ
並び替え条件は年数(D列)昇順・守備(G列)降順

すると、同じ「年数」の中では捕手→投手→外野手→内野手という順番で整理することができました。

ちなみに、漢字の場合は「代表的な音訓」の順序で配列され、多くの場合は使用頻度の多い音読みが適用されますが、「内」のように「うち」という訓読みが適用されることもあります

新任
新任

並び替え条件の列を追加する時はカンマで区切るんですね。

andとか半角スペースにしちゃいそう…。

人事のプロ
人事のプロ

そこはselect句と同じよ!

複数の列を並べる時はカンマで区切ると覚えてね

desc(降順)」で並び替える場合

=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where 検索列<=検索値 order by 検索列 desc")

Copy

asc(降順)」で並び替える場合

=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where 検索列<=検索値 and not 検索列 is null  order by 検索列 asc")

Copy

複数条件で並び替える場合

=query('シート名'!$この列から:$この列まで,"select 列①,列②,列③ where 検索列<=検索値 and not 検索列① is null  order by 検索列① asc,検索列② desc")

Copy

以上、数値による範囲指定並び替えについて解説しました。

今回で連載3回目になりますが、ここまで読んで頂ければ、スプレッドシート上で発生する手作業の大半は自動化できたのではないでしょうか

次回は、人事などバックオフィス業務には欠かせない「日付ベースの情報管理」について、QUERY関数自動化する方法を解説するので是非ご覧下さい。

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