スプレッドシートQUERY関数の使い方｜抽出・並べ替え・集計を練習する

※本記事にはPRを含む場合があります。

こんにちは。『脱会社員』脱サラを応援し続けるブログ、運営者の『なかびー』です。

Googleスプレッドシートで「完了した案件だけを別の表に出したい」「担当者別の金額を大きい順に見たい」とき、QUERY関数を使うと、元の表を残したまま条件に合う結果表を作れます。ただし、最初から長い式を書こうとすると、列の指定、引用符、見出し、日付のどこで間違えたのか分かりにくくなります。

この記事では、八行の架空の案件明細から、必要な列の選択、行の抽出、並べ替え、担当者別の集計を一段ずつ作ります。画面のフィルタで元表の表示を切り替える方法とは目的を分け、関数によって別の結果表を作る練習に集中します。ファイル間参照の許可設定や外部データの取り込みは、ここでは前提にしません。

根拠は2026年10月4日にGoogleの公式ヘルプとクエリ言語の仕様で確認しています。パソコンのブラウザーで使うGoogleスプレッドシートを想定し、地域や関数の設定により区切り記号などが異なる場合があります。人物・金額・案件はすべて練習用で、画像も作業を示す生成イメージです。

元の表とQUERYの三つの引数を確認する｜型と見出しをそろえる

QUERYは元データから結果表を作る関数

QUERYの基本は、=QUERY(データの範囲,"問い合わせの内容",見出し行数)です。最初にどの範囲を見るか、次にその範囲から何を取り出すか、最後に上部の何行を見出しとして扱うかを指定します。GoogleのQUERY関数の説明で、構文と各引数の意味を確認できます。

元表を直接並べ替えたり削除したりする操作とは異なり、結果を数式のある場所へ展開します。元の明細を入力する人と、集計結果を見る人で使う場所を分けたいときに役立ちます。ただし、QUERYが返した結果の途中へ手入力して管理する方法には向きません。結果が更新されると行の位置や数が変わることがあるためです。

QUERYの問い合わせ文はSQLに似ていますが、一般のデータベースで使うSQLのすべての命令が使えるわけではありません。今回はselect、where、group by、order by、labelなどの基本に絞ります。別のSQLの記事の式をそのまま持ち込まず、Google Visualization APIのクエリ言語として確認してください。

明細と結果の二つのシートを用意する

同じスプレッドシート内に「明細」と「結果」の二つのシートを作ります。明細のA1:E1へ「日付」「担当」「区分」「金額」「状態」を入力し、A2:E9へ次の八行を置きます。結果シートではA1を式の起点に使い、A:Eの下方向を空けます。各練習式は結果のA1へ順に入れ替えて試す想定です。

※表は横にスクロールできます。

金額は単位を付けず数値として入力し、日付は日付として認識される形にします。「12,000円」という文字や、日付らしく見える文章を混ぜないことが重要です。表示形式で見やすく整える操作と、データの型をそろえる操作は区別します。今回のD列は金額ですが、商品コードのように文字として保持すべき列なら同じ扱いにはしません。

この八行の総額は95,000円、完了は六行で71,000円、九月の完了だけなら五行で64,000円です。先にこの答えを控えておくと、作った式の結果が期待どおりか確認できます。実際の案件管理では、金額が請求額か売上計上額かなどの定義も必要ですが、この練習では単純な明細金額として扱います。

見出し行数を推測に任せず指定する

最初の式は=QUERY('明細'!A1:E9,"select *",1)です。アスタリスクはすべての列を選ぶ指定で、最後の1は範囲の先頭一行が見出しという意味です。結果側に五つの列と八行のデータが出ることを確認します。見出しをデータ一件と数えないようにします。

データ範囲をA2:E9から始めるなら、見出しを含まないので最後は0とする考え方になります。範囲だけ変えて1を残すと、最初の明細が見出し扱いになって結果に影響する可能性があります。見出し引数は出力の見出しを自由に増やす数字ではなく、入力範囲の上部の構造を示すものです。

引数を省略した場合や推測を使う設定では、データの内容によって見出しの判断が変わることがあります。練習では、入力表を一行見出しに統一して1を明示します。結果の先頭が抜けた、数字が見出しになったと感じたら、まずデータ範囲の開始行と見出し行数をセットで確認しましょう。

一つの列に異なる型を混ぜない

Googleの公式説明では、一列に異なる種類のデータが混ざる場合、その列で多い型が使われ、少数側の型がNULLとして扱われるとされています。つまり、数字に見える文字が一部混ざると、集計から値が抜けたように見える原因になります。セルの見た目だけでなく、金額が数値として入っているかを確認する必要があります。

たとえばD列の大半が数値なのに一行だけ文字列の「18000」になっていると、その行を普通の数値と同じに集計できると期待しない方が安全です。QUERYの式を長くする前に、元表の型と入力の経路を調べます。コピー元の単位、前後の空白、先頭のアポストロフィなども確認材料です。

NULLは、金額がゼロであることとは別です。未入力、型が合わず扱えない値、数値の0を一括でゼロ扱いにすると、未確認の金額を確定したように見せる可能性があります。今回の正常例では数値がそろっている前提で練習し、型の異常は別コピーで試して違いを確認します。

selectとwhereで抽出する｜必要な列と条件を一つずつ増やす

selectで出力する列と順番を決める

すべての列を出す式が動いたら、=QUERY('明細'!A1:E9,"select A,B,D",1)に変えます。結果は日付、担当、金額の三列になります。元表の区分と状態は出力に含まれませんが、元表から消えたわけではありません。必要な情報だけを別表へ見せる操作として理解しましょう。

列を指定するときは、ここではA、B、Dの列記号を使います。見出しに書かれた「日付」「担当」「金額」を、そのままselectの列名として書く形ではありません。出力したい順番に列記号を並べれば、select B,A,Dのように担当を先に出すこともできます。

元表の列を追加したり順序を変えたりした場合、問い合わせ文に書いた列記号が意図した項目を指しているかを再確認します。数式の参照範囲が調整されても、文字列として書いた問い合わせの意味まで常に保たれると決めつけないようにします。列の構成を変えるときは、代表的な結果を検算します。

whereで完了した行だけを取り出す

完了の行だけを出すなら、=QUERY('明細'!A1:E9,"select A,B,D where E = '完了'",1)とします。数式全体の問い合わせ文を半角ダブルクォーテーションで囲み、その中の文字条件は半角シングルクォーテーションで囲んでいます。外側と内側の引用符を分けて読むと、構造が分かりやすくなります。

この例では未完了の行4と取消の行7が除かれ、元の行2、3、5、6、8、9に対応する六行が残ります。条件に使うE列をselectへ含めなくても、入力範囲にE列が含まれていれば条件判定に使えます。どの列を表示するかと、どの列で絞るかは別に指定できます。

結果が予想より少ない場合は、状態の表記が「完了 」や「完了済」になっていないかを確認します。似た意味の言葉を同じと扱いたいなら、元データの表記方針を決める必要があります。式に条件候補を足し続ける前に、正式な状態名が何かを確認しましょう。

数値条件には文字と同じ引用符を付けない

完了かつ15,000円以上の行を見るなら、=QUERY('明細'!A1:E9,"select A,B,D where E = '完了' and D >= 15000",1)です。金額の15000は数値なので、文字列のようにシングルクォーテーションで囲みません。結果は森の20000と青木の18000に対応する二行です。未完了の15000は、金額の条件を満たしても完了条件に合いません。

「以上」と「より大きい」は境界の意味が違います。>=なら基準値そのものを含み、>なら含みません。条件を入力する前に、日本語で基準を一文にします。基準ちょうどの正常な完了行をテスト用コピーに追加すれば、比較記号の意味を確かめられます。

複数の条件をandでつなぐ場合は、すべてに合う行が必要です。orならどれかに合う行を対象にできます。担当が青木または林で、さらに完了という条件なら、where (B = '青木' or B = '林') and E = '完了'のようにまとまりをかっこで示します。条件を混ぜたときは、どの部分を一組にしたいかを明確にします。

空行を除く条件と業務条件を分ける

将来の追記に備えて範囲を広げると、下の空白行も範囲へ含まれます。担当が入った行だけを対象にするならwhere B is not nullのような条件を使う考え方があります。空欄を判定するとき、通常の文字比較のように= nullと書く方法ではありません。

ただし、担当が未入力でも金額や日付のある重要な行を、単に空行だと考えて除外しないようにします。「担当なしの案件は確認待ちとして別に扱う」のか、「完全に空の追記用行だけを除きたい」のかで条件は違います。見た目を整えるために未入力の業務データを見えなくしないことが大切です。

まずはA1:E9の固定範囲で正しい結果を作り、その後に範囲を広げる練習へ進むと原因を切り分けやすくなります。新しい条件と範囲変更を同時に行うと、結果が変わった理由が分かりにくくなるため、一つずつ変えて確認してください。

※表は横にスクロールできます。

group byとorder byで集計する｜金額と件数を別々に検算する

order byで金額の大きい順に並べる

前章の高額な完了行を大きい順に出すなら、問い合わせ文の後ろへorder by D descを加えます。完成式は=QUERY('明細'!A1:E9,"select A,B,D where E = '完了' and D >= 15000 order by D desc",1)です。森の20000が先、青木の18000が後という順序を期待します。

descは降順、ascは昇順です。並び順が業務上重要なら、明示しておくと意図を伝えやすくなります。同額の行も一定の順にしたい場合は、日付などの第二の並べ替え条件を検討します。値が同じなのに表示の順が変わったとしても、金額の集計誤りとは限りません。

order byは結果表の並び方を指定するもので、明細シートの元の行を並べ替える操作ではありません。結果の一番上に森が出ても、元表の一番上が森へ変わったわけではない点を確認します。入力側の作業順を保ったまま、閲覧側の順番を変えられることがQUERYの使いどころです。

担当者別に完了金額を合計する

担当者ごとの集計へ進むなら、=QUERY('明細'!A1:E9,"select B,sum(D) where E = '完了' group by B",1)です。担当Bでまとめ、Dの金額を合計します。元の六行の完了明細から、担当者ごとの三行の結果へ変わります。個別の日付を並べる表から、集計の単位が変わったことを意識してください。

結果の期待値は青木38000、林13000、森20000です。青木は12000＋8000＋18000、林は6000＋7000、森は20000です。未完了15000と取消9000を加えないことを確認します。三人の合計を足すと71000となり、前に出した完了六行の合計と一致します。

集計では、selectへ出す非集計列とgroup byのまとまりをそろえる必要があります。担当別合計を出しているのに、個々の明細の日付もそのまま表示したいという要求は、結果の一行が何を表すかを曖昧にします。担当別集計と明細一覧を別の結果表にするなど、出力の単位を決めましょう。

集計金額で並べ、見出しを分かりやすくする

担当別の金額を大きい順にしたいなら、order by sum(D) descを指定します。元のDの一件の金額でなく、担当ごとに合計した値で並べるという意味です。さらにlabelで表示用の見出しを付けると、関数由来の名前のままより読みやすくできます。

完成例は=QUERY('明細'!A1:E9,"select B,sum(D) where E = '完了' group by B order by sum(D) desc label B '担当', sum(D) '完了金額'",1)です。青木38000、森20000、林13000の順になります。labelは結果の見出しを整える指定で、元表の列名や集計対象の条件を変更するものではありません。

構文の詳細はGoogleのクエリ言語リファレンスで確認できます。select、where、group by、order by、labelには書く順序があるため、日本語の説明文のように自由な順へ並べ替えないようにします。エラーになったら、最後に足した句を外して一段前の状態が動くかを確認します。

countで何を数えているかを明示する

金額と件数を一緒に見たい場合は、select B,sum(D),count(D)へ変更します。今回の正常データではD列に金額が入っている完了行を数えるため、青木3、森1、林2です。金額の合計が大きい順に並べた結果を、次のように検算できます。

※表は横にスクロールできます。

この表の合計行は説明用の検算で、前述のQUERYが自動で最後に付ける行ではありません。結果へ追加したい場合は、別の確認セルで計算するなど、担当者の行と混ぜない設計にします。またcount(D)は、DがNULLでない値を数える考え方です。Dに未入力や型の問題があると、業務上の行数と一致しない可能性があります。

件数の定義を「案件数」にしたいなら、一案件を見分けるIDや明細の単位も確認します。同じ案件が商品別に複数行になっている表では、行数を案件数と呼ぶと誤解を招きます。今回の練習は一行一明細として、金額のある行数という名前を使います。指標名は、実際に数えているものに合わせてください。

小計を出す前に、除外された行を確認する

合計が合わないときは、集計式を眺めるだけでなく、同じwhere条件で明細を出してみます。担当ごとの三行へ圧縮されると、どの明細が含まれたか見えにくくなります。先に六行の完了明細を確認し、そのあとでgroup byを付けると、抽出と合計のどちらに問題があるかを分けられます。

区分別にまとめるなら、担当Bの代わりに区分Cを使い、完了したデザイン50000、事務21000を期待値にできます。担当別の合計71000と区分別の合計71000が一致することも確認材料です。ただし、二つの式が同じ誤った元データを参照していれば両方同じ誤りになるので、元の明細も残して見られるようにします。

日付と文字の条件を組み立てる｜引用符とデータ型のずれを防ぐ

九月分は月初以上・翌月初未満で指定する

九月の完了だけを抽出する式は、=QUERY('明細'!A1:E9,"select A,B,D where E = '完了' and A >= date '2026-09-01' and A < date '2026-10-01'",1)です。日付を表すdateと、年・月・日の形式を使っています。セルの表示が2026/09/01でも、問い合わせ文の中は日付リテラルの書き方に合わせます。

この式では元の行2、3、5、6、8に対応する五行が残り、金額は64000です。十月一日の7000は含まれません。月末の日付以下という条件を作るより、翌月の始まりより前という境界で表すと、期間の意図を説明しやすくなります。今回のA列は時刻を含まない日付として扱っています。

日時を含む別のデータへ応用する場合は、日付だけの列と同じと思い込まず、元列の型と比較するリテラルを確認してください。時刻やタイムゾーンの意味が関わる一覧では、集計の締め時刻も業務ルールになります。見た目の日付が同じだから同じ期間へ入ると判断せず、小さい境界テストを用意します。

条件セルを使うときも文字列の構造を読む

毎回担当名を式の中で書き換えたくないなら、結果シートのH1を「対象担当」、H2を「青木」として、=QUERY('明細'!A1:E9,"select A,C,D where B = '"&H2&"'",1)のように条件をつなげる練習ができます。H2は出力のA:Eから離れた場所に置き、結果が展開する範囲を邪魔しないようにします。

式の中では、H2の前後に担当名を囲むシングルクォーテーションを付けています。H2の文字が青木なら、問い合わせ文の条件部分はwhere B = '青木'になります。まずこの完成した文字列を頭の中で読めるようにし、引用符を何となく増減させて直そうとしない方が確実です。

この短い式は、今回の青木・林・森のように引用符を含まない候補で練習する前提です。任意の文章やアポストロフィを含む名前をそのままつなげると、問い合わせ文の構造が壊れる場合があります。自由入力の条件に広げるなら、扱う文字と入力規則を決め、特殊な文字を含む例も試してください。

完全一致と部分一致を使い分ける

状態が完了である行だけを選ぶなら、E = '完了'の完全一致が分かりやすい指定です。もし「完了」という文字を含む条件へ変えると、「未完了」にも同じ文字が含まれるため、意図せず対象になる可能性があります。部分一致は便利ですが、業務状態の判定へ安易に使わないようにします。

たとえば文字列を含むかを見るcontainsなどの条件は、商品名や説明文を探す用途で検討できます。ただし、大文字・小文字の扱いや、似た語が含まれる場合の意味を確認します。今回のように候補が限られた状態列では、正式な値との一致を出発点にする方が、何を含めるか説明しやすくなります。

「完了が見つからない」からといって部分一致へ変える前に、末尾の空白や別表記がないかを調べてください。データの揺れを条件の広さで隠すと、未完了まで含むなど別の問題を作ります。元表の入力ルールを整えることが、QUERYの式を短く保つ助けになります。

A・B表記とCol表記を混ぜない

この練習は明細シートの直接のセル範囲をデータにしているため、問い合わせ文ではA、B、C、D、Eを使っています。一方、ほかの式が返す配列などをデータにする場面では、Col1、Col2のような表記を使う構成があります。検索で見つけた式の列指定だけを混ぜると、どの列を示しているのか分からなくなります。

GoogleのQUERYヘルプにはCol表記とA・B表記の案内があります。応用するときは、まず入力データとして渡しているものの列順を確かめます。元のシートのD列だった値が、組み替えた配列では二列目になっている可能性もあるので、元シート上の位置とQUERYへ渡した順序を混同しないことが重要です。

初心者のうちは、直接範囲の短いQUERYを完成させてから、外部参照や配列との組み合わせへ進みます。IMPORTRANGEを組み合わせる場合は、参照元へのアクセスや更新の問題も加わります。QUERYの抽出条件が間違っているのか、そもそもデータが届いていないのかを分けて調べられる構成にしてください。

結果を検算して更新へ備える｜元表を残し、出力を直接直さない

エラーは短い式へ戻して切り分ける

構文のエラーが出たら、まずselect *だけの式で同じ範囲を読めるかを確認します。そこへ列選択、where、group by、order by、labelの順に一つずつ追加すれば、どの指定で問題が出たかを絞れます。長い式を全部書き直すより、動く最小の状態を保ちながら進める方が原因を見つけやすくなります。

引用符の閉じ忘れ、半角かっこの数、句の順番、列の指定は、最初に確認したい点です。列を追加した直後なら、問い合わせ文のDがまだ金額かを確認します。日付条件を足した直後なら、A列の日付の型とdateの書き方を見ます。エラーメッセージと最後に変更した箇所を一緒に記録すると、質問もしやすくなります。

結果を展開する場所に既存のデータがある場合は、式の文法が正しくても表示できないことがあります。結果シートのA1から右と下を確認し、別の空いた場所へ式を置いて試します。必要なメモを削って空ける前に、そのメモがどこへ残るべきかを決めましょう。

該当なしと計算失敗を同じ空欄で隠さない

条件に合う行がない場合と、構文が間違って結果を出せない場合は、意味が異なります。どちらも空表示へまとめると、読者は「ゼロ件で問題なし」と受け取るかもしれません。まずエラーや空の結果の原因を確認し、意図していない条件、範囲漏れ、型の問題を解消します。

出力に「該当なし」と表示したい場合も、正常に問い合わせが実行され、条件に合うデータがないと確認できる設計を考えます。IFERRORであらゆる問題を一括で空欄にするより、作成中はエラーを見える状態にしておく方が点検しやすくなります。利用者へ出す表示と、作成者が確認する情報を分ける方法もあります。

集計が0のケースも、入力がないケースと区別します。金額がプラスとマイナスで相殺したのか、本当にゼロの明細なのか、金額がNULL扱いになったのかは、結果の一つの数字だけでは分かりません。必要なら抽出した明細を併せて確認できるようにします。

範囲へ新しい行が入るかを試す

A1:E9という固定範囲のまま、明細の十行目へ新しい案件を入力しても、その行は今回のQUERYに含まれません。更新する表では、想定する入力範囲を決めて式へ反映します。たとえば十分な行数まで広げる方法や、列全体を参照する方法がありますが、空行の扱いと処理量も考える必要があります。

練習では、原本のコピーに十行目として完了の1000円を追加し、範囲をE10まで広げたときに完了合計が72000、件数が七へ変わるかを確認できます。元の八行へ戻すと71000・六件へ戻ることも確かめます。追加した値が結果へ入ったことを、見た目だけでなく合計の変化で確認しましょう。

列全体を参照するときも、表の下に説明用の文字や合計行を置けば入力データへ混ざる可能性があります。入力範囲には明細だけを置き、集計や説明を別の領域へ分けます。QUERYへ渡す表の構造を安定させることが、更新のたびに式を直す負担を減らします。

抽出と集計の期待値を照合表へ残す

完成前には、結果が出るかだけでなく、含まれる元の行、件数、合計、並び順を確認します。今回の練習なら次のチェック表を使えます。合計が一致しても、別の行を誤って含み、ほかの行を落としている可能性があるため、元の行番号や担当と日付も合わせて見ます。

※表は横にスクロールできます。

掲載した期待値は、同じ架空データに対する独立した抽出・並べ替え・集計計算で照合しています。Googleスプレッドシートの計算エンジンやブラウザー画面をすべて実操作して確認したものではありません。ご自身の環境では、型の確認、式の入力、元表の変更、保存後の再表示を試してください。地域設定による引数区切りなどが異なる場合は、関数入力の案内に合わせます。

入力欄・条件欄・結果欄を分けて引き継ぐ

引き継ぎでは、明細シートが入力元、結果シートのA1が式の起点、H2が担当の条件セル、といった役割を書きます。QUERYの結果の途中へ手入力して直すのではなく、元の明細か条件を変更することを説明します。式が返す行の数は増減するため、出力のすぐ下に固定のメモを置かない方が管理しやすくなります。

問い合わせ文だけを渡すより、「完了の定義はE列が完了に完全一致」「金額は数値」「日付は日付型」「見出しは一行」「範囲の末尾は九行目」という前提も残すと、更新時に何を確認すべきか伝わります。将来範囲を広げる予定なら、誰がどの条件を点検して変更するかも決めます。

表のひな形から整えたい場合は、当ブログのbizoceanのテンプレート活用を紹介した記事も参考になります。ひな形を使う場合も、入力列と結果列の配置やデータ型がQUERYの前提に合うかを確認してください。元データを持つファイルを共有するときは、必要な情報と相手の権限も別に点検します。

まずは全件表示、完了の抽出、担当別集計の三つを順に作り、八行から六行、さらに三行へ変わる意味を説明してみましょう。条件を一つずつ加え、そのたびに元の明細で答えを確かめる。この流れを身に付けると、QUERYの長い式をただ貼り付けるのではなく、確認しながら組み立てられるようになります。