【スプレッドシート】複数シートをまとめる!集計する!QUERY関数

Googleスプレッドシートで、2つのシートのデータを1つのシートに集約する方法をご紹介します。データが追加されたときに自動更新する方法や、複数シートのデータを項目別に集計する方法についても解説しているので、ぜひ参考にしてみてください。

スポンサーリンク

複数シートのデータを並べる(簡単!)

【スプレッドシート】複数シートをまとめる!集計する!QUERY関数

配列を表す中括弧{}を使うことで、複数のシートのデータを別の1つのシートにまとめる方法です。

縦方向に連結する場合

【スプレッドシート】複数シートをまとめる!集計する!QUERY関数

複数シートのデータを縦に並べたい場合は、中括弧{}内に、それぞれの「シート名と範囲」のセットをセミコロン(;で区切って入力します。

例:={'シート1'!A2:B4;'シート2'!A2:B4}

横方向に連結する場合

【スプレッドシート】複数シートをまとめる!集計する!QUERY関数

複数シートのデータを横に並べたい場合は、中括弧{}内に、それぞれの「シート名と範囲」のセットをカンマ(,で区切って入力します。

例:={'シート1'!A2:B4,'シート2'!A2:B4}

項目別に集計する(UNIQUE関数・SUMIF関数)

UNIQUE関数で項目をまとめる

【スプレッドシート】複数シートをまとめる!集計する!QUERY関数

まず、UNIQUE関数を使って、集計したい項目の一意の値(重複しない値)を抽出します。

例:=UNIQUE(A2:A7)

UNIQUE関数の引数:(範囲, [列の比較], [回数指定])

SUMIF関数で項目ごとに集計する

【スプレッドシート】複数シートをまとめる!集計する!QUERY関数

次に、SUMIF関数を使って、UNIQUE関数で抽出した一意の値ごとに、対応する行を合計します。

たとえば、「商品名」の範囲から一意の値を検索し、その「価格」を合計したい場合、数式は次のようになります。

例:=SUMIF($A$2:$A$7,C2,$B$2:$B$7)

ポイント:「条件範囲」と「合計範囲」を絶対参照にすることで、数式をオートフィルでコピーしたときに、範囲がずれないようにしています。

SUMIF関数の引数:(範囲, 条件, [合計範囲])

スポンサーリンク

複数シートのデータをまとめる(QUERY関数)

QUERY関数は、特定の「データ範囲」に対し、指定した「クエリ(処理)」を実行します。配列とQUERY関数と組み合わせることで、データの抽出や集計を行います。

将来のデータ追加にも対応する

データ範囲に列全体(例:A:B)を指定することで、将来追加されるデータも自動的に連結されるようにします。

【スプレッドシート】複数シートをまとめる!集計する!QUERY関数

たとえば、「シート1」と「シート2」のA2:Bの範囲をまとめる場合、数式は次のようになります。

例:=QUERY({'シート1'!A2:B;'シート2'!A2:B},"SELECT* WHERE Col1 is not null")

ポイント:この数式では、{'シート1'!A2:B;'シート2'!A2:B}で、QUERY関数が扱う仮想的なデータ範囲を作成します。そこからSELECT句でデータを抽出し、WHERE句で空白行を除外しています。


ここではQUERY関数のデータ範囲として、配列で結合した「仮想的なデータ範囲」を用いるため、列を指定するときに列名(例:A、B)ではなく、カラム番号(col1、col2)を使用しています。

項目別に集計する

【スプレッドシート】複数シートをまとめる!集計する!QUERY関数

たとえば、「シート1」と「シート2」のA2:Bの範囲を、特定の項目ごとに集計したい場合、数式は次のようになります。

例:=QUERY({'シート1'!A2:B;'シート2'!A2:B},"SELECT Col1, sum(Col2) WHERE Col1 is not null GROUP BY Col1")

ポイント:この数式では、仮想的なデータ範囲から1列目の「商品名」をグループ化し、SELECT句で「商品名」と「価格の合計」を抽出しています。

QUERY関数のクエリの種類

  • SELECT: 特定の列を指定した順序で抽出します。(詳細記事はこちら
  • WHERE: 指定した条件を満たすデータのみを抽出します。(詳細記事はこちら
  • GROUP BY: 指定した列の同じ要素ごとにデータを集計します。(詳細記事はこちら
  • PIVOT: GROUP BYで集計したデータに分類要素を追加し、表の形式を変換します。(詳細記事はこちら
  • ORDER BY: データを特定の列の値で並べ替えます。(詳細記事はこちら
  • LIMIT: 抽出する行数を指定した数に制限します。(詳細記事はこちら
  • OFFSET: 指定した数の最初の行をスキップします。(詳細記事はこちら
  • LABEL: 抽出した列のヘッダー名を任意の名前に変更します。(詳細記事はこちら
  • FORMAT: 特定の列の日付や数値などの表示形式を設定します。(詳細記事はこちら
  • OPTIONS: ヘッダーの行数や特定の書式設定を適用しないなどの追加オプションを設定します。
タイトルとURLをコピーしました