【スプレッドシート｜QUERY関数】今すぐコピペで使えるテンプレ集

これを見てくれたバックオフィスの方が煩雑な管理業務を自動化することで、「今日残業せずに帰れるように」「創造的な仕事にフォーカスして価値を生み出せるように」と思い、まとめ記事を書きました。

もし皆さんの会社がスプレッドシートを導入していたら、これから紹介するQUERY関数であらゆる手作業を自動化することが出来ます。

詳しい内容は各記事で解説していますが、

という時間のない方のためにコピペで問題解決できるようにしています。

読売ジャイアンツの2020年データを例に進めていきますが、在籍している企業の情報に置き換えて考えて頂ければと思います。

なお、QUERY関数の構造がまだよく理解できていない場合には図解も用意しているので、

という場合は参考にしてみて下さい。

それでは、難易度の低いものから順に掲載していくので、数式をコピペしてどんどん使っていきましょう！

【基本形】１つの条件で抽出

条件一致（〜と一致するもの）

データベースから「投手」だけを抜き出し、項目を守備・選手名・年俸（推定）の順番で並べたい時は、以下のようになります。

• 前半の赤下線部分で参照先を指定
  • シートとセルの範囲を指定するだけ（VLOOKUP等と同様）
• 後半の黄下線部分で条件を指定
  • selectは列指定
    • 「▲▲と■■の項目だけ持ってきたい！」
      • 今回のケースでは、［D列］守備・［C列］選手名・［M列］年俸（推定）
        • 列は指定した順番で抽出される（前後してもOK）
    • select*にすると、前半の赤下線部分で指定した全ての列を抽出できる
    • 始まりと終わりに「"（ダブルコーテーション）」をつける 忘れがち！
  • whereは行指定
    • 「●●の人たちだけを抜き出したい！」
      • 今回のケースでは、［D列］守備＝投手
    • 検索条件（文字）の始まりと終わりに「'（シングルコーテーション）」をつける 忘れがち！
• 見出し行番号は省略可
  • 参照先データの内容に基づいて推測される
    • 先程の「投手」を抽出した例では、見出し行番号は「2」になるが、基本的には省略しても問題ない

条件不一致（〜以外のもの全て）

先程とは反対に、「野手（投手以外）」だけを抜き出し、項目を守備・選手名・年俸（推定）の順番で並べたい時は、以下のようになります。

「<>（不等号）」を使用する場合

「not（文否定）」を使用する場合

QUERY関数の基本、「条件の一致・不一致」による抽出について、詳しい解説はこちらをご覧下さい。

【and/or】複数の条件で抽出

条件一致（当てはまるもの全て）

and（かつ）

「北海道出身の投手（守備が「投手」かつ出身地が「北海道」の選手）」を抜き出したい時は、以下のようになります。

or（または）

「東京・神奈川出身の投手（守備が「投手」かつ出身が「東京」または「神奈川」の選手）」を抜き出したい時は、以下のようになります。

条件不一致（当てはまらないもの全て）

and（かつ）［否定］

「北海道出身ではない投手」を抜き出したい時は、以下のようになります。

or（または）［否定］

「野手のうち、高卒ルーキー（１年目・１９歳）と在籍２年目以上」（※）を抜き出したい時は、以下のようになります。
※１年目のうち大卒や外国人選手を除くという意図

【like＋%】条件の部分一致で抽出

基本的な使い方として、数式内での条件指定を紹介しています。

部分一致の条件をセル参照したい場合は、こちらをご覧下さい。

部分的な条件一致（含むもの全て）

「左投げの投手」を抜き出したい時は、以下のようになります。（補足：K列に利き腕の情報はあるが、投打が１つのセルにまとめられている）

• 〜%（後に%）
  • 〜から始まる文字列
• %〜（前に%）
  • 〜で終わる文字列
• %〜%（前後に%）
  • 〜を含む文字列 おすすめ

部分的な条件不一致（含まないもの全て）

「投手と捕手」を消去法で抜き出したい時は、以下のようになります。

複数条件で絞り込む「or」「and」、 部分一致でキーワード検索する「like」について、詳しい解説はこちらをご覧下さい。

数値範囲で条件指定

「在籍年数１０年以下の選手」を抜き出したい時は、以下のようになります。

• >（〜より大きい）
• <（〜より小さい）
• >=（〜以上）
• <=（〜以下）

【order by】数値で並び替え

【desc】降順

「在籍年数１０年以下の選手」を抜き出して「ベテラン順（年数が長い順）」に並び替えたい時は、以下のようになります。

【asc】昇順

「在籍年数１０年以下の選手」を抜き出して「若手順（年数が短い順）」に並び替えたい時は、以下のようになります。（空欄を除外するために「not 列 is null」を使用）

複数条件による並び替え

「在籍年数１０年以下の選手」を抜き出して「①年数（数値）を昇順、②守備（文字列）を降順」に並び替えたい時は、以下のようになります。

数値セルの条件指定、並び替え「order by」、空欄「null」の除外方法ついて、詳しい解説はこちらをご覧下さい。

【date】日付指定の方法

QUERY関数で日付指定する際のルールは以下の通りです。

なお、日付条件をセル参照したい場合は、こちらをご覧下さい。

指定した日付以降・以前

「2000年以降に生まれた選手」を抜き出す場合、以下のようになります。

• >（〜より後）
• <（〜より前）
• >=（〜以降）
• <=（〜以前）

【not】一部を除いた期間

「2000年以降に生まれた選手」を消去法で抜き出す場合、以下のようになります。

【order by】日付による並び替え

「2000年以降に生まれた選手」を「生まれ順（生年月日が早い順）」に並び替えると、以下のようになります。

• order by 列（指定した列で並び替え）
  • ＋ asc（昇順で）
  • ＋ desc（降順で）

【and】指定した期間「いつからいつまで」

「2000年に生まれた選手」を抜き出して「生まれ順（生年月日が早い順）」に並び替えると、以下のようになります。

• 特定の月を期間指定する場合は「検索列>=当月初 and 検索列<翌月初」
  • 「<翌月初」にすれば、自動的に「前月末日まで」が指定される
    • 「<=当月末」にすると、月毎に日付を修正する手間が発生する

日付指定の方法ついて、詳しい解説はこちらをご覧下さい。

以上、QUERY関数の条件指定について超効率重視で紹介しました。

参照範囲の指定方法に関するまとめは、こちらをご覧下さい。