【スプレッドシート】Query関数のoffsetの使い方と例3つ

Gooleスプレッドシートで、データの上からいくつかスキップして表示したい場合に使える「Query関数のoffsetオプション」。例えばランキング表示しているデータの100位移行のデータを表示したいときにoffsetオプションが役立ちます。

このページでは、Query関数のoffsetの基本的な使い方と、offsetを応用した例3つを解説していきます。データの出し分け方法を知って、見やすいスプレッドシーを作成していきましょう。

Query関数のoffsetの基本的な使い方

GoogleスプレッドシートQuery関数のoffsetを使う基本形を紹介します。次のoffsetを使ったQuery関数は「上から指定した数だけスキップして表示する」という指示になります。

=QUERY(範囲, "offset 数")

より具体的に「A11:D17の範囲の行で、上から3つの行をスキップして表示」という指示を書いたものが次のQuery関数です。

=QUERY(A11:D17, "offset 3",true)

query関数でoffsetを使ってスキップする

Query関数のoffsetの使い方例

①先頭2つの行をスキップするoffsetの使い方

こちらは先程の「offsetの基本的な使い方」で説明したので解説は省略します。

=QUERY(範囲, "offset 数")
=QUERY(A11:D17, "offset 3",true)

query関数でoffsetを使ってスキップする

②最下位2つの行をスキップするoffsetの使い方

Query関数の中で「order by 列 asc」を指定することによって、指定した列を昇順に並べ替えて出力できます。ここにoffsetオプションも合わせると次のようなQuery関数になります。意味は「昇順に並べて上から指定した数だけスキップして表示せよ」です。

=QUERY(範囲, "order by 列 asc offset 数")

「A11:D17の範囲の行の中で、D列を昇順に並べ替えたときの、上3つの行をスキップして表示せよ」という指示の、offsetを使った具体的なquery関数は次のようになります。

=QUERY(A11:D17,"order by D asc offset 3")

下のデータの例だと「PVの少ない順に並べ替えたときの、上から3つの行をスキップして表示」という意味になります。

query関数でorderbyで昇順にしてoffsetを使ってスキップする

③最上位2つの行をスキップするoffsetの使い方

Query関数の中で「order by 列 desc」を指定することによって、指定した列を降順に並べ替えて出力できます。ここにoffsetオプションも合わせると次のようなQuery関数になります。意味は「降順に並べて上から指定した数だけスキップして表示せよ」です。

=QUERY(範囲, "order by 列 desc offset 数")

「A11:D17の範囲の行の中で、D列を降順に並べ替えたときの、上3つの行をスキップして表示せよ」という指示の、offsetを使った具体的なquery関数は次のようになります。

=QUERY(A11:D17,"order by D desc offset 3")

下のデータの例だと「PVの多い順に並べ替えたときの、上から3つの行をスキップして表示」という意味になります。

query関数でorderbyで降順にしてoffsetを使ってスキップする

QUERY関数の使い方」まとめ
QUERY関数の使い方使用頻度使用例
オプションOrder byで昇順・降順を指定する★★QUERY("範囲" "order by 列 asc")
オプションgroup byで同じ値をグルーピングして集計する★★★QUERY(範囲,"select 列A, 集計関数(列B) group by 列A")
オプションpivotを使って、group byをさらにグルーピングして表示するQUERY(範囲, "select 列A, 集計関数(列A) where group by 列A pivot 列B")
オプションlabelを使って、出力される列のラベルを変更するQUERY(範囲, "label 列 '名前' ")
オプションformatを使って、出力される値の表示形式を変更するQUERY(範囲, "format 列 '表示形式' ")
オプションlimitを使って、表示する行数に制限をかけるQUERY(範囲, "limit 数")
オプションoffsetを使って表示データを上からスキップする方法QUERY(範囲, "offset 数")
オプションoptionsを使う
演算子containsを使って、指定文字列を含む行を表示する★★QUERY(範囲, "where 列 contains '検索文字列' "
演算子starts withを使って、行の先頭文字列に条件を指定するQUERY(範囲, "where 列 starts with '検索文字列' "
演算子ends withを使って、行の後尾文字列に条件を指定するQUERY(範囲, "where 列 ends with '検索文字列' "
演算子likeで複雑な条件を指定するQUERY(範囲,"where 列 like '検索文字列' ")
演算子★★★QUERY(範囲,"WHERE 列 matches '正規表現' ")
テクニックwhereの条件で空白を指定・除外する★★QUERY(範囲, "where 列 is not null")
テクニックQUERY関数にSUM関数を使う★★SUM(QUERY(範囲, "where 条件")
テクニックwhereの条件に日付を指定する★★★QUERY(範囲,"where 列 = date '"& TEXT(セル,"YYYY-MM-DD") &"'")
テクニックQUERY関数内でimportrange関数を使って、別シートを参照する★★★QUERY(importrange(シートID,参照範囲), "where 条件")
テクニック複数範囲・複数シートを結合する★★QUERY({IMPORTRANGE(シートURL,シート範囲);IMPORTRANGE(シートURL,シート範囲)},"WHERE 条件")
テクニックwhereの条件を複数指定する★★★QUERY("範囲", "where 条件A or 条件B and 条件C")
[su_note note_color="#00BFFF" text_color="#ffffff"] 人気の記事

icon-check-circle Google検索の変遷から見えてくる「Googleがキュレーション化する日」
[/su_note]
タイトルとURLをコピーしました