Googleスプレッドシート QUERY関数 超応用例 18（skipping, limit, offset）
Googleスプレッドシートの最強集計関数 QUERY関数シリーズの第18回です。
👇これまでのQUERY関数シリーズを全て無料マガジンにまとめています。QUERY関数の限界を超える（プルス ウルトラ）をしたい人は是非！
前回はQUERY関数で「並べ替え」をする句 order by を解説しました。
QUERY関数の名脇役 skipping, limit, offset
今回はクエリ文の
第6の句 skipping、第7の句 limit、第8の句 offset
の3つをセットで学んでいきましょう。
$$
\begin{array}{lll}
\text{No}&\text{句}&\text{使用量}\\ \hline
\text{1}&\text{select}&\text{出力する列を指定 （類似関数 CHOOSECOLS）}\\ \hline
\text{\text2}&\text{where}&\text{条件でデータをフィルタ （類似関数 FILTER）}\\ \hline
\text{3}&\text{group by}&\text{行方向（縦）にグループ化（類似関数 UNIQUE）}\\ \hline
\text{4}&\text{pivot}&\text{列方向（横）にグループ化（類似関数 UNIQUE）}\\ \hline
\text{5}&\text{order by}&\text{データを並び替え（類似関数 SORT）}\\ \hline
\textbf{6}&\textbf{skipping}&\textbf{〇行おきにデータを出力する}\\ \hline
\textbf{7}&\textbf{limit}&\textbf{〇行目までデータを出力}\\ \hline
\textbf{8}&\textbf{offset}&\textbf{〇行目からデータを出力}\\ \hline
\text{9}&\text{label}&\text{列のラベル名を変更する}\\ \hline
\text{10}&\text{format}&\text{特定の列の値を書式設定}\\ \hline
\text{11}&\text{options}&\text{追加オプションを設定。（使わない）}\\ \hline
\end{array}
$$
この3つはいずれもQUERY関数の出力結果を「絞り込む」句です。
主役級とは言えませんが、ちょっとQUERY関数の出力をアレンジしたい時に活躍する名脇役のような句と言えます。
まずは個々に見ていきましょう。
👇サンプルデータとして以下をコピペして利用ください。
No 日付 営業担当 商品 売上金額
1 2024/01/05 山田 A ¥1,500,000
2 2024/01/12 田中 B ¥2,200,000
3 2024/01/18 佐藤 C ¥800,000
4 2024/02/03 佐藤 B ¥1,800,000
5 2024/02/10 山田 C ¥700,000
6 2024/02/15 田中 A ¥2,500,000
7 2024/03/12 佐藤 A ¥2,000,000
8 2024/03/18 山田 B ¥1,100,000
9 2024/03/25 田中 C ¥2,800,000
10 2024/04/03 山田 A ¥1,900,000
11 2024/04/15 佐藤 C ¥2,400,000
12 2024/05/18 田中 A ¥780,000
13 2024/05/30 山田 C ¥550,000
14 2024/06/03 田中 A ¥1,400,000
15 2024/06/15 山田 C ¥2,300,000
16 2024/06/28 佐藤 A ¥1,850,000
17 2024/07/05 山田 B ¥900,000
18 2024/07/18 佐藤 A ¥500,000
19 2024/08/03 佐藤 A ¥2,050,000
20 2024/08/10 山田 B ¥700,000
21 2024/08/20 佐藤 B ¥600,000
22 2024/08/28 山田 A ¥1,750,000
23 2024/09/12 佐藤 C ¥2,250,000
24 2024/09/18 山田 B ¥800,000
25 2024/09/25 田中 A ¥2,650,000
26 2024/10/10 田中 B ¥750,000
27 2024/11/13 山田 B ¥580,000
28 2024/11/21 田中 A ¥1,650,000
29 2024/12/12 山田 B ¥940,000
30 2024/12/17 佐藤 C ¥1,230,000
31 2024/12/27 田中 A ¥1,600,000
32 2025/01/25 山田 B ¥3,000,000
33 2025/01/30 田中 A ¥1,200,000
34 2025/02/20 佐藤 B ¥900,000
35 2025/02/28 山田 A ¥1,600,000
36 2025/03/05 田中 C ¥600,000
37 2025/03/30 佐藤 B ¥750,000
38 2025/04/10 田中 B ¥950,000
39 2025/04/20 山田 B ¥650,000
40 2025/04/28 田中 A ¥1,700,000
41 2025/05/05 佐藤 B ¥1,000,000
42 2025/05/12 山田 C ¥2,100,000
43 2025/05/25 佐藤 B ¥2,600,000
44 2025/06/10 佐藤 B ¥850,000
45 2025/06/20 田中 B ¥600,000
46 2025/07/12 田中 C ¥2,700,000
47 2025/07/25 山田 B ¥1,550,000
48 2025/07/30 田中 C ¥1,250,000
49 2025/08/15 田中 C ¥2,900,000
50 2025/09/05 田中 B ¥1,150,000
51 2025/09/30 佐藤 C ¥900,000
52 2025/10/03 山田 A ¥1,950,000
53 2025/10/15 佐藤 C ¥2,550,000指定がない場合は、👆こちらをテーブルにしたものをQUERY関数の第1引数に使用します。
〇行ごとの出力を実現する QUERY関数の skipping （隠れ句）
まずは第6の句 skipping です。
skipping は、公式リファレンスには掲載されていない隠れ句となっています。
QUERY関数シリーズの第3回で、クエリ文全体を紹介する際にも軽く触れています。
公式に掲載されていないので、今後急に使えなくなる可能性があります。
その点には注意です。
skippingの使い方
=QUERY(表_1[#ALL],"skipping 10")
skipping N（数値）で、間のN－1行をスキップしてN行毎のデータ（N行おきにデータ）を出力します。
出力は見出しがある場合は、まず見出し、そして必ず1行目を出力した上で、間のＮ-1 行を除いた（飛ばした）行を出力します。
行番号に着目するとわかりやすいですが、行番号の差分が Ｎになるってことです。
skipping N で指定できる数値 Nは、0以上の整数となります。マイナスの数値や小数を指定した場合はエラーとなります。
skipping 0 または skipping 1 とした場合は、間の行をスキップしないそのままのデータ（全行）が出力されます。
データの行数より大きい数値を指定した場合は、見出しと1行目だけを出力します。
これまで登場した select, where, group by, pivot, order byと組み合わせることも出来ます。
使えそうなケースである 2行で1データ や3行で1データ みたいな変則的な表は、そもそも列の型が統一されてない可能性があるので、QUERY関数で扱うべきではないですし、活用シーンが思いつかない句です。
ただ、CHOOSEROWSやFILTER関数で代替は出来るものの、〇行毎に出力と直接同様のことが出来る関数は存在しないので、このような処理が必要な時には有用な句と言えます。
Q1.skipping N の Nをセル参照にしたい
簡単なお題で理解を深めましょう。
👆のように表_1を対象として QUERY関数 の skipping N のNをセル参照（K1セル）としたい場合、どのような式を組めばよいでしょうか？
ただし K1セルが空白の場合は全データを返すものとします。
考えてみましょう！
↓↓
回答はここから。
↓↓
A1. skipping N の Nをセル参照にする
回答です。
=QUERY(表_1[#ALL],"skipping "&K1*1)
まずskipping の後ろは 数値 なので、QUERY関数でセル参照と言えばコレ！と何も考えずに
✖ skipping '"&K1&"'"
シングルクォートを付けてしまうのはNGです。
また、単純に skipping "&K1 としてしまうと、K1 が空白の場合 skippingの後ろに何もないのでエラーとなります。
上に書いた通り skipping 0 は 0行飛ばし（そのままデータを返す）なので、空白を 0にする 為に K1*1 もしくは N(K1) とN関数を組み合わせればOK。
これは簡単ですね。
Q2. 売上の集計1位と最下位を出力したい
skippingが使えそうなケースをひねり出してみましたｗ
もう1つお題にチャレンジしてみましょう。
このお題は、営業担当を増やした 以下のデータをA1に貼り付けて「売上表」とテーブル名を設定して利用ください。
営業担当 商品 売上金額
山田 A ¥1,500,000
田中 B ¥2,200,000
佐藤 C ¥800,000
鈴木 B ¥1,800,000
青木 C ¥700,000
田中 A ¥2,500,000
佐藤 A ¥2,000,000
山田 B ¥1,100,000
田中 C ¥2,800,000
山田 A ¥1,900,000
佐藤 C ¥2,400,000
青木 A ¥780,000
鈴木 C ¥550,000
田中 A ¥1,400,000
鈴木 C ¥2,300,000
佐藤 A ¥1,850,000ここから営業担当毎の売上金額合計が1位の人と最下位の人を 👇このように出力したい。
この時、どんな式を組めばよいでしょうか？
考えてみましょう！
↓↓
回答はここから。
↓↓
A2. 売上の集計1位と最下位を出力する
回答です。
=QUERY(売上表[#ALL],"select Col1,sum(Col3) group by Col1 order by sum(Col3) desc skipping "&COUNTUNIQUE(売上表[営業担当])-1)
解説していきます。
まず、担当者毎に集計して売上が多い順（売上金額の降順）で並び替えるには、QUERY関数で前回登場した order by句を group byと組み合わせる式を作ればよいですね。
=QUERY(売上表[#ALL],"select Col1,sum(Col3) group by Col1 order by sum(Col3) desc")
ここから見出しを除いて一番上となる 田中 と 一番下の青木だけに絞り込めばいいわけですが、ここで skipping が使えます。
1行目と最終行を取得するには、skipping N の Nを データの行数 -1 とすればよいので、データの行数 = ユニークな営業担当の数を COUNTUNIQU関数で取得します。
COUNTUNIQUE(売上表[営業担当])-1
COUNTUNIQUE関数はExcelには無い関数です。
QUERY関数一発で、1位と最下位を抽出できました～。
一応別解として
=LET(x,QUERY(売上表[#ALL],"select Col1,sum(Col3) group by Col1 order by sum(Col3) desc"),
QUERY(x,"skipping "&ROWS(x)-2))
1回目のグループ集計の結果をLET関数で xと置いて、もう1回 skippingだけのQUERY関数を使う方法もあります。
※この時は見出し行分も含めて ROWS(x) - 2 と指定する必要があります。
〇行目まで出力を実現する QUERY関数の limit
続いて第7句 limitを見ていきましょう。
limitは 出力結果を上から〇行目までに絞り込んで出力したい。こんな時に使う句です。
limitの使い方
=QUERY(表_5[#ALL],"limit 5")
limt N（数値）で、出力は見出しがある場合はまず見出し、そして上からデータを N行出力します。
👆画像の例では、 limit 5 として 見出し以外のデータ部分の出力を上から5行に制限しています。
limit で指定できる数値 Nは 0以上の整数です。skippingと同じくマイナスの数値や小数を指定した場合はエラーとなります。
第1引数のデータの行数を超える数をNに指定した場合は、全データを出力。
※出力行数の上限はあくまでも元データの行数です。元の行数を超えて拡張はされません。
limit 0 とした場合は、データ部分なしで見出しだけを出力することが出来ます。
skippingよりもわかりやすいですね。
limit句の類似関数
limit 5 （見出しあり）と同じことを実現するには FILTER関数で行番号を条件にするか、
=ARRAY_CONSTRAIN(表_5[#ALL],6,5)
※列数も指定する必要あり
=CHOOSEROWS(表_5[#ALL],SEQUENCE(6))
※数が多い場合は重い？
=OFFSET(表_5[#ALL],0,0,6)
※配列には使えない
この辺りの関数で代替が可能です。
Excelだったら TAKE関数が非常に優秀なんですが・・・。
Googleスプレッドシートの ARRAY_CONSTRAIN関数は、範囲や配列を指定したサイズに縮小する関数です。
ただ左上から〇行、〇列という指定以外出来ませんし、どちらも必ず数値指定が必要（引数を省略できない）のが難点です。
劣化版 TAKE関数って感じかも。
limitの活用シーン
QUERY関数の limit句は order byと組み合わせて使うことが多いです。
たとえば
=QUERY(表_6[#ALL],"order by Col5 desc limit 3")
として、売上金額が大きい上位3位までを出力したり。
=QUERY(売上表_2[#ALL],"select Col1,sum(Col3) group by Col1 order by sum(Col3) desc limit 2")
グループ集計した上で 売上合計の上位2位を出力。
このような使い方をすることが多いです。
Excelには存在しない Googleスプレッドシートシートオンリーの関数、 並び替えて上位〇個を出力する SORTN関数 に近い処理と言えます。
さすがにSORTN関数のように 同順位だった時にどうするか？を設定するオプションはありませんが
見出しと上から〇個を出力したい
条件で絞り込んだ上で（where句） 上から〇個を出力したい
グループ集計した上で 上位〇個を出力したい
こんな時はQUERY関数の limit句が便利でしょう。
limit句単体の応用例は思いつきませんが、後ほど他の句と合わせたちょい応用例にチャレンジしましょう。
先頭から〇行を除外して出力を実現する QUERY関数の offset
最後は第8句 offset です。
offset は出力結果を上から〇行目までを除外して出力したい。こんな時に使う句です。
offsetの使い方
=QUERY(表_8[#ALL],"offset 5")
offset N（数値）で、出力は見出しがある場合はまず見出し、そして上からデータを N行飛ばして（除外して）、それ以降を出力します。
👆画像の例では、 offset 5 として 見出し以外のデータ部分の出力を上から5行（1～5行目）を除外して6行目以降を出力しています。
offset で指定できる数値 Nは 0以上の整数です。マイナスの数値や小数を指定した場合はエラーとなります。
N に第1引数のデータの行数を超える数値を指定した場合は、データ部分なしで見出しのみを出力。
offset 0 とした場合は 除外する行数0なので、全データを出力します。
offset句の類似関数
このoffset句の機能「データの上から〇行を除外する」をそのまま実現できるシート関数はありません。
少し数式を工夫すれば
=OFFSET(表_8[#ALL],51,0,ROWS(表_8[#ALL])-51)
※配列には使えない。出力行数の指定が必要。揮発性関数なので多用は避けた方がよい
=CHOOSEROWS(表_8[#ALL],SEQUENCE(ROWS(表_8[#ALL])-51,1,52))
※出力の行数指定、開始行番号の指定が必要
=FILTER(表_8[#ALL],SEQUENCE(ROWS(表_8[#ALL]))>51)
※SEQUENCEで行数の連番を生成する必要あり
見出し行を考慮しなければ対応できますが、いずれもROWS関数でデータの行数を取得する必要があり、シンプルとは言えません。
一番近いのは ExcelのDROP関数かもしれません。
※DROP関数は上から〇行だけでなくマイナス指定で下から〇行を除外や列方向の除外も可能で、より多機能な関数です）
=DROP(表_8[#すべて],51)
※見出しの1行分を考慮して51としています
ExcelのTAKE関数とDROP関数がGoogleスプレッドシートに輸入されなかったのは非常に残念です。
offsetの活用シーン（offsetで見出しを除いたデータ部分だけを出力する）
QUERY関数のoffset句の使い方としては変化球なんですが、mirが多用しているoffset句の使い方が、表データから1行目の見出し行を除外する処理です。
見出し1行を含めた表を参照した際、見出し行のみを取得する場合は
=INDEX(表_9[#ALL],1)
このようにINDEX関数で割とシンプルに取得できます。
しかし、見出し行を除くデータ部分の取得はちょうどいい関数がありません。（Excelなら DROP関数で瞬殺ですが）
=QUERY(表_9[#ALL],"offset 1",0)
これをQUERY関数で見出しなし offset 1 と指定することで、割とシンプルな式で見出しを除くデータ部分のみを取得することが出来ます。
👆 「見出しなし」を指定するのが、第3引数の 0 です。（まだQUERY関数シリーズでは取り上げてませんが）
見出し行が0 = 見出しなし とすることで、本来の見出し行をデータ部分の1行目として扱い、 offset 1 で除外しています。
この見出し除去が最も活用できるのが、QUERY関数で集計や演算子を使った時に勝手に見出しが生成されるケースです。
QUERY関数の見出し除去は、この後登場する label句でも対応出来るんですが、1列ごとに記述が必要となる為、selectした列が複雑だったり、複数列の見出しを除去しようとすると面倒です。
しかしもう1回QUERY関数をネストして offet 1 、見出し行数 0と指定すれば
=QUERY(QUERY(A2:D12,"select Col1+Col2,Col1-Col2"),"offset 1",0)
さくっと見出し行を除去してデータ部分のみを取得することができます！
つまり
【Excel】DROP(data,1)
▼
【Googleスプレッドシート】 QUERY(data,"offset 1",0)
このように置き換えできるってことです。
これは便利。
=LET(
q,QUERY(表_9,"select Col3,sum(Col5) group by Col3"),
header,INDEX(q,1),
data,QUERY(q,"offset 1",0),
VSTACK(header,data)
)QUERY関数の結果をさらに他の関数で処理する際、データ部分だけを使いたい場合は offset 1 による見出し除去テクニックが活躍します。（今後の超応用例で登場します）
第6、第7、第8の句の個別の仕様、そして使い方をまずは紹介しました。
skipping, limit, offset を組み合わせて使う
それぞれの使い方、活用シーンは理解できたでしょうか？
この3つの句は単体でも使えますが、組み合わせて使うことで真価を発揮します。
組み合わせて使う際のルールと挙動を学んでいきましょう。
skipping, limit, offset を組み合わせる時は、この順番で記述する
=QUERY(表_12[#ALL],"skipping 5 limit 4 offset 3")
他の句と同じように記述の順番が決まっています。
skipping, limit, offset を組み合わせて使う場合は、必ずこの順番で記述する必要があります。
たとえば
✖ =QUERY(表_12[#ALL],"offset 3 skipping 5 limit 4 ")
こんな感じで offsetを先に書いてしまうと
記述順ミスでお馴染みの エラー表示で Encountered が登場します。
offset 3 までは間違いではないので、「offsetの後ろに skippingが登場するのがありえない」というエラー表示になります。
一番下に記載がある通り、offsetの後ろに記述できる句は label, format, options のみってことですね。
処理の順番は skipping ▶ offset ▶ limit となっている
わかりにくいのが、
記述順は skipping ▶ limit ▶ offset なのに
処理順は skipping ▶ offset ▶ limit という点です。
たとえば skipping 5 limit 4 とすると 👆このようになります。
これは先に 5行置きにデータを絞り込んでから 上から4行目までを取得しているとわかりますね。
同様に skipping 5 offset 3 とした場合は 👆のようになります。
先に skipping 5を実行した結果の 上から3行（1,6,11 行目）をoffsetで除外した結果となっています。
しかし limit と offset の処理順序を比較する為に limit 3 offset 5 とした場合は、👆このようになります。
これは 先に offset 5で 1～5行目を除外し6行目以降のデータにしてから limit 3で上から3行を出力しています。
もし処理順が逆だったら 上から3行に絞り込んで 5行除外となるので、データ部分は空、見出しのみの出力となるはずです。
というわけで記述順と違って 処理順は
skipping ▶ offset ▶ limit
このような順番となります。
また、条件でフィルタする where 句 や group by 、pivot で集計、order by による並び替えも組み合わせた場合は、これらの処理をした結果に対して まず skipping を行います。
つまり以下のような順番で処理されているってことです。
【QUERY関数の処理の順番】
where ▶ （group by や pivot） ▶ order by ▶ skipping ▶ offset ▶ limit
3つの句の組み合わせルールが理解できたので、最後に簡単なお題2問にチャレンジしてみましょう！
Q3. データ部分の行番号の1の位が4のものだけを出力したい
👆画像のように 1列目 Noの一の位が4のものだけ（データ部分の4行目、14行目、24行目…) を出力したい場合、QUERY関数でどのような式を組めばよいでしょうか？
なおNoの列 Col1は条件に使わないものとします。（Col1で連番を振ってるのは結果の出力をわかりやすくするためです）
考えてみましょう！
↓↓
回答はここから。
↓↓
A3. データ部分の行番号の1の位が4のものだけを出力する
回答です。
=QUERY(QUERY(表_13[#ALL],"offset 3"),"skipping 10")
まず、先ほど学んだ通り1回のQUERYで記述しようとすると
先に skippingが処理されてしまうのでNGです。
ここは一度 QUERY関数で offset 3だけを実行して4行目スタートとしてから
もう1回QUERY関数をネストして skipping 10 とします。
QUERY関数をネストするテクニックは、これまでも何回か登場しましたね。
もう1問いってみましょう。
Q4. 金額の大きいデータの 11位～15位を出力したい
左のテーブルの 5列目 売上金額が最も大きいものを1位とした時、金額の大きさが 11位（11番目）～15位（15番目）のデータだけを出力したい場合は、どのような式を組めばよいでしょうか？
これはQUERY関数1回だけで出来る問題です。
考えてみましょう！
↓↓
回答はここから。
↓↓
A4. 金額の大きいデータの 11位～15位を出力する
回答です。
=QUERY(表_14[#ALL],"order by Col5 desc limit 5 offset 10")
このような式になります。
今回学んだ3つの句を組み合わせて利用するケースで、もっとも便利なものが、order by に limit と offset を組み合わせたパターンです。
これは並べ替えた上で、〇位～△位までを出力したい というケースで大活躍します。
クエリの記述に落とし込む際は、
売上金額の大きさが11番目～15番目のデータを出力
▼
5列目（売上金額）をキーとして降順で並び替えて、
上から10行を除外して5行分のデータを出力
▼
order by Col5 desc limit 5 offset 10
と考えます。
ここでポイントは
11番目から ➡ 10行を除外する （飛ばす行数10を offsetで指定）
11～15行目 ➡ 15 - 11 +1 = 5行を出力（出力する行数5を limitで指定）
記述順に注意 order by Col5 desc limit 5 offset 10
この3点です。
SORTN関数は1位から〇個という出力は出来ますが、〇位から△個出力は出来ません。他の関数も単体でこの処理は出来ません。
一応、見出しを考慮しなければ
=FILTER(SORT(表_14,5,0),ISBETWEEN(SEQUENCE(ROWS(表_14)),11,15))
SORTとFILTERを組み合わせて対応できますが、SEQUENCEでバーチャルな行番号を生成したり、シンプルに記述するには ISBETWEEN関数を使ったりと、それなりに工夫が必要です。
QUERY関数なら クエリ文で 句は3つ使いますが、QUEYR関数だけで完結しますし、見出しも出力してくれます！
便利ですね。
QUERY関数 skipping, limit, offset に続き次回は labelを。組み合わせ超応用例も！
今回はQUERY関数の
第6の句 skipping、第7の句 limit、第8の句 offset
の3つをセットで紹介しました。
これまで登場した select, where, group by, pivot, order に比べれば重要度は低いですが、構文はシンプルなんで簡単に使えるかと思います。
たまに「これ使えるんじゃね？」というケースがあるので、是非頭の片隅に入れておいてください！（そしてこのnoteのスキをポチっと）
次回もQUERY関数シリーズです。
第9の句 label の理解、そして今回の句と label句を組み合わせた 超応用例にチャンレジしましょう！
いいなと思ったら応援しよう！
チップ大歓迎です。やる気がアップしますｗ