BizRobo! ブログRPA関連のお役立ち情報をお届けします

業務効率化

スプレッドシートの集計を自動化！便利な関数やテーブル機能を図解で紹介

2024.11.08

2024.11.07

読み込み中...

Googleスプレッドシートには、データの集計を自動化できる機能が数多く備わっています。とくに関数やテーブル機能を活用すれば、データの集計も格段に効率化できるはずです。

そこで本記事では、Googleスプレッドシートの集計自動化の方法を紹介します。また、集計したデータをより見やすく整理するための便利機能や小技なども解説。

実際の操作画面を用いて分かりやすく図解するので、スプレッドシートに詳しくない人もぜひご覧ください。

RPAお役立ち資料4点セットをダウンロード

1. スプレッドシートの集計自動化には関数が便利
2. 複数のシートからデータを集める「IMPORTRANGE」
3. データの整理や分析・集計自動化には「ピボットテーブル」
4. シンプルな機能による集計の自動化
5. 集計表を見やすくするスプレッドシートの便利機能
6. スプレッドシートの集計自動化が難しい場合は……
7. あらゆる集計自動化はBizRobo!にお任せ
8. スプレッドシートの集計はBizRobo!で自動化

スプレッドシートの集計自動化には関数が便利

スプレッドシートの集計を自動化するには、関数を活用しましょう。関数とは、一定の計算を行うためにあらかじめスプレッドシートに組み込まれている数式です。

単純な合計値や差額の計上といった計算はもちろん、関数を活用すれば複雑な計算も可能になります。関数の一覧は「Google スプレッドシートの関数リスト」で紹介されているため、ぜひご覧ください。

上記サイトの検索窓に「合計」や「平均」といった行いたい処理をキーワードで打ち込むと、活用できる関数が絞り込めます。

ではここから、そのなかでも特に集計の自動化に便利な関数を見ていきましょう。

複数のシートからデータを集める「IMPORTRANGE」

複数のスプレッドシートからデータを集計したい場合は、「IMPORTRANGE（インポートレンジ）」関数を活用しましょう。IMPORTRANGEを使えば、手作業で複数のシートからデータをコピー&ペーストする手間が省けます。

数式は、以下のとおりです。

=IMPORTRANGE（“スプレッドシートの直URL”,“シート名!データを集計したい範囲”）

たとえば①のシートデータを②に転記するとしましょう。

▼①10月の発注数

▼②データ集計用のシート

この場合、②に以下の関数を打ち込みます。

すると以下のとおり、自動的に参照元のデータが転記されました。

このように、IMPORTRANGEを活用すればデータの集計が大幅に効率化できます。データを修正するときも、IMPORTRANGEを使ってシート同士を連携しておけば複数のシートを手作業で修正する必要がありません。

複数データの並びを変える方法

複数のシートからデータを取得し、1枚のシートにまとめるにはIMPORTRANGE関数を並べて記載します。たとえば①のデータと②のデータを、空白のシートにまとめたいとしましょう。

▼①10月の発注数

▼②11月の発注数

このとき、関数の間に「,」と「;」のどちらを入れるかで、表示形式を変えられます。

横の結合：={IMPORTRANGE(), IMPORTRANGE()}縦の結合：={IMPORTRANGE(); IMPORTRANGE()}

まずは横に並べてみましょう。IMPORTRANGE関数同士を「,」でつなぐと、下記のとおり10月と11月のデータが横並びになりました。

次に、縦に並べてみましょう。IMPORTRANGE関数同士を「;」でつなぐと、以下のとおり、10月の下に11月のデータが反映されました。

このように、IMPORTRANGEを使うとデータの集計だけでなく、より見やすく並び変えることも可能です。

条件ごとのデータ抽出や並び替えには「Query関数」

IMPORTRANGEは、指定範囲のデータ全体を反映する関数です。しかし場合によっては、以下のような場合もあるでしょう。

・指定の行または列だけを抽出したい
・特定の条件に該当する行または列だけを抽出したい

このようなときに役立つのが、「Query（クエリ）関数」です。Query関数は条件を指定し、合致したデータのみを集計できます。

ではここからサンプルデータを用いて、使い方を詳しく見ていきましょう。

指定の行または列だけを集計する

たとえば下記データのうち、年齢以外のデータだけを抽出したいとします。

この場合、任意のセルに以下の関数を入力します。

=Query(A1:D,”Select A,B.D“)

すると以下のとおり、年齢以外のデータが抽出されました。

列ではなく指定の行を抽出したい場合は、「A,B,D…」と記載した箇所を「2,4…」と行数に置き換えて記載します。

特定の条件に合致するデータだけを抽出する

以下のデータから、D列で「りんご」を選んでいる人のデータだけを抽出したいと仮定します。

この場合、任意のセルに以下の関数を入力します。

=QUERY(A1:D, “SELECT * WHERE D CONTAINS ‘りんご'”, 1)

すると以下のとおり、りんごを購入した人のデータだけが抽出されました。

このようにQuery関数を使えば、IMPORTRANGEで集計したデータをさらに見やすくソーティングできます。

データの整理や分析・集計自動化には「ピボットテーブル」

複数の要素を含むデータの整理や分析といった集計の自動化には、ピボットテーブル機能が便利です。今回は例として、以下の顧客データを集計してみましょう。

まずは集計したいデータを選択します。

ツールバーの「挿入」から、ピボットテーブルを選択しましょう。

すると下記のとおり、新たにテーブルを作成する場所を選択できます。「新しいシート」を選択すると新規タブが生成されます。

「既存のシート」を選択すると、同じシート内にテーブルが作成されるため、好きな方を選択しましょう。

今回は「新しいシート」を選択しました。すると、空のテーブルと右側に編集画面（エディタ画面）が表示されます。

ピボットテーブルは行や列を要素ごとに指定できます。たとえば「行」に「氏名」を選択すると、以下のように顧客の氏名だけが並べられます。

このときエディタ画面の「順序」や「並べ替え」を操作すると、データを任意の並び順に変えられます。

続いて、「列」と「値」「商品名」を入れました。すると下記のとおり商品名が横軸に並び、商品を選択した人のセルに「1」とカウントが入ります。これは「値」の集計方法が「COUNT」関数になっているためです。

ほかにも以下のとおり、合計値や最大値・最小値を出す関数などさまざまな集計が可能です。

このように、ピボットテーブルを活用すれば簡単に任意の集計表を作成できます。

データのフィルタリングでより見やすく

エディタ画面下部にある「フィルタ」機能を使えば、特定のデータを省くこともできます。たとえば以下の表で、「りんご」の購入数は不要だとします。

エディタ画面の「フィルタ」の右側にある「追加」をクリックし、「商品名」を選択します。

次に「りんご」のチェックを外しましょう。

すると以下のとおり、りんごのデータが省かれた状態でテーブルが表示されます。

表が見にくい場合は、このようにデータを取捨選択するとスッキリできます。

複数の要素を自動で分析できるクロス集計

ピボットテーブルを活用すれば、複数の要素を分析する「クロス集計」も簡単です。たとえば以下のデータをクロス分析してみましょう。

今回は行に「年齢」と「氏名」を、列に「商品名」を設定しました。さらに値の欄は商品数の合計値を表記するよう、以下のとおりエディタ画面を設定しています。

表示されたテーブルは以下のとおりです。年齢の高い順に並べたことで、年齢が高ければ高いほど商品の購入点数が多いことが分かりました。

さらに商品の購入点数の総計欄を見ると、りんごが最も多く、ぶどうが最も少ないことが分かります。このように、クロス集計を用いればデータをより詳細に分析できます。

「商品がなぜ売れているのか」「特定の消費者行動にはどういった背景があるのか」このように、さまざまなデータ集計・分析の自動化に活用できます。

シンプルな機能による集計の自動化

「複雑な機能は使いこなせない…」このような人には、以下の機能がおすすめです。

関数の名称	用途
転置して貼り付け	データの行と列を入れ替える
SUMIF関数	指定した条件を満たすセルの合計を計算する
COUNTIF関数	指定した条件を満たすセルの数をカウントする

これらを知っているだけでも、手作業の地道なシート作業から解放されます。一つずつ見ていきましょう。

転置して貼り付け

スプレッドシートには、「転置して貼り付け」という機能があります。これはデータの縦軸と横軸を入れ替える機能です。

上記の左側にあるデータをコピーし、「転置して貼り付け」を行います。すると以下のとおり、今まで横軸に並んでいた「顧客番号」「氏名」などの情報が、縦軸に変換されました。

一部の情報だけをコピーし、転置貼り付け機能を使えば短時間で任意の集計表を作成できます。データが膨大で手動選択できない場合は、以下の関数で同じ操作が可能です。

=TRANSPOSE(選択範囲)

条件に合った数値を合計する「SUMIF関数」

特定の条件を満たすデータだけを集計したいときに便利なのが、「SUMIF」関数です。SUMIF関数の数式は、以下のとおり。

=SUMIF(範囲, 条件, [合計範囲])

では実際に、下記のデータをSUMIF関数で集計してみましょう。

今回は商品がそれぞれいくつ売れたのか、集計します。この場合、関数は以下のとおりです。

条件に合った数値をカウントする「COUNTIF関数」

特定の条件を満たす値をカウントしたいときは、「COUNTIF関数」が便利です。関数は以下のとおり。

=COUNTIF(範囲, 条件)

たとえば下記のデータから、りんごを購入した人の数をカウントしたいとしましょう。

任意のセルに式を入れます。今回の場合、対象となるセルはD2〜D11です。

以下のとおり、りんごを買った人は4人と分かりました。

「”りんご”」と記入した部分をアレンジすれば、特定の数値以上、もしくは特定の数値以下といったデータの抽出も可能です。

集計表を見やすくするスプレッドシートの便利機能

「集計表が見にくい…」このような場合は、スプレッドシートの機能でデザインを変えるのもおすすめです。体裁やデザイン、色を変えるだけでも、雑然としていたデータがスッキリします。

ここからは、簡単に集計表を見やすくするスプレッドシートの便利機能を見ていきましょう。

セルの背景色変更

表を見る時、行をたどっていくうちにどこの行のデータか分からなくなることは珍しくありません。このようなときにおすすめなのが、セルの背景色変更機能です。

まず対象とするデータを選択し、メニューバーの「表示形式」を選択します。出てきたメニューから、「交互の背景色」を選びましょう。

すると以下のように、背景色が白とグレーの交互に表示されます。

この機能を使えば、手作業で地道に背景色を変える必要がありません。また、背景色は画面右側に表示されるエディタ画面で変更可能です。

小さなグラフを表示「SPARKLINE」

複数のデータを分かりやすく比較したいときは、「SPARKLINE」が便利です。「SPARKLINE」とは、1つのセルの中にグラフを表示する機能です。

関数は以下のとおりです。

=SPARKLINE(選択範囲)

たとえば以下のとおり、3種類ある商品の発注数推移を比較したいときとします。N列に関数を入れると、データの推移を可視化できます。

またSPARKLINEは、以下のとおりオプションを関数に入れることで折れ線グラフ以外を作成することもできます。

=SPARKLINE（選択範囲, {オプション}）

{ }の内側に入れるオプションには、以下のようなものがあります。

“charttype”,”line”	折れ線グラフ
“bar”,”line”	横棒グラフ
“column”,”line”	縦棒グラフ
“winloss”,”line”	正と負の値を示す縦棒グラフ