コピロボ
Excelの集計機能を使ってデータを集計しよう!基本的なポイントを解説
Excelには集計機能が備わっており、どういったデータをどのように集計したいのかによって使用するべき機能は変わります。
また、集計機能を使用する際には、押さえておきたいポイントがいくつか存在します。
今回はエクセルの集計機能の関数やピボットテーブルについて紹介します。
Excelの集計機能について
Excelのデータ集計機能ではExcel関数(目的の計算をするためにExcelに用意されている数式)が使われます。単純にデータを合計するSUM関数、条件を絞って合計するSUMIF関数などが代表的です。その他、AVERAGE関数、COUNTIF関数などもよく使われます。
・集計機能はどんな機能?
Excelの集計機能とは、特定の項目ごとの小計(合計)を簡単に算出できる計算機能のことです。単に合計値を出したい場合から、「商品ごとの売上が知りたい」「顧客別の売上高を集計したい」など、条件を指定して合計値を出したい場合まで幅広く活用できます。
・SUM関数
SUM関数とは、指定したセルの数値を合計できる関数です。以下のようなExcelデータ(売上管理表)があるとしましょう。この表を用いて、集計機能(関数)の特徴について説明します。
A | B | C | D | E | F | G | H | |
1 | 日付 | 商品 | 単価 | 個数 | 売上 | 合計 | ||
2 | 10/1 | A | 120 | 10 | 1,200 | 平均 | ||
3 | 10/2 | B | 350 | 7 | 2,450 | |||
4 | 10/3 | C | 780 | 6 | 4,680 | 商品 | A | |
5 | 10/4 | B | 350 | 3 | 1,050 | 個数 | ||
6 | 10/5 | A | 120 | 9 | 1,080 | |||
7 | 10/6 | C | 780 | 4 | 3,120 | 5個以上 | ||
8 | 10/7 | A | 120 | 28 | 3,360 | |||
9 | 10/8 | A | 120 | 54 | 6,480 |
SUM関数は、たとえば「10/1〜10/8までの売上合計額を知りたい」などの場合に使います。
- カーソルをH1に合わせて、関数の挿入ボタン(fxボタン)で「SUM」を選択
- 合計したいセルの範囲(E2〜E9)を指定
以上で、10/1〜10/8の合計売上を簡単に求めることができます。
・SUMIF関数
SUMIF関数は条件を絞って合計値を出したいときに使用する関数です。たとえば、上記データの中から「10/1〜10/8で売れたAの合計数を知りたい」などの場合に使います。
- カーソルをH5に合わせて、fxボタンでSUMIFを選択
- 表示されたダイアログボックス内の「範囲」にB2〜B9、「検索条件」にH4、「合計範囲」にD2〜D9を指定
以上で、10/1〜10/8で売れたAの合計数を抽出できます。
・AVERAGE関数
指定範囲の数値の平均値を出せるのがAVERAGE関数です。たとえば、「10/1〜10/8の平均売上額を知りたい」などの場合に使用します。
- カーソルをH2に合わせて、fxボタンでAVERAGEを選択
- セルの範囲(E2〜E9)を指定
以上で、10/1〜10/8の平均売上額を出すことができます。
・COUNTIF関数
COUNTIF(カウントイフ)関数とは、指定範囲の中で1つの検索条件に一致するセルの数を求める関数です。たとえば、上記データの中から「商品が5個以上売れた日が何日あるか知りたい」などの場合に使用できます。
- H7にカーソルを合わせて、fxボタンからCOUNTIFを選択
- 表示されたダイアログボックス内の「範囲」にD2〜D9、「検索条件」に「>=5」を入力
以上で、商品が5個以上売れた日数を知ることができます。COUNTIFは隠れた名関数ともいわれるほど、重宝されている集計機能の1つです。
Excelの集計機能の基本的なポイント
Excelの集計機能を使いこなすうえで、押さえておくべき基本ポイントがあります。
・名前の定義はしっかりと
表やフィールドには名前定義をしっかりと行っておきましょう。名前の定義を行っていれば、数式(集計機能の計算)を設定する際に範囲指定がスムーズになるだけでなく、誰が見ても数式が分かりやすくなるメリットがあります。
たとえば、あるデータのセルA1〜A10に記入された売上額の合計を出したいとしましょう。通常であれば数式は「=SUM(A1:A10)」となります。A1〜A10の範囲を「売上」と名前定義しておけば、数式を組む際に名前BOXから選択するだけで範囲指定が可能です。その場合、数式も「=SUM(売上)」と分かりやすくなります。
上記は簡単な例ですが、集計が複数シートにまたがる場合や複雑な数式になる場合のことを考えて、あらかじめ表やフィールドにしっかりと名前を定義しておくようにしましょう。
・集計は解除できる
集計は以下の手順で解除できます。
- 小計を含む範囲内のセルを選択
- 「データ」タブ→小計をクリックし、「集計の設定」ダイアログボックスを表示
- すべて削除ボタンをクリック
■ピボットテーブルについて
Excelのデータ集計機能には、数式・関数のほかにも便利な「ピボットテーブル」があります。
ピボットテーブルとは?
ピボットテーブルとは、大量のデータを集計・分析できる機能のことです。元データを変更せずに集計方法を切り替えられ、数式・関数などを使わずにデータ集計できるメリットもあります。使い方はシンプルで、元データの範囲を指定→ピボットテーブルを呼び出し、抽出したい条件(名前)を指定するだけです。マウス操作のみで簡単にデータ抽出できるので、数式や関数の設定が苦手な人におすすめの方法といえます。
膨大なデータから必要な数値を瞬時に抽出したい時は、ピボットテーブルを使うと良いでしょう。たとえば、以下の項目をまとめた「販売データ」があるとします。
- 販売日
- 販売店舗
- 商品
- 単価
- 販売数
- 売上金額
ピボットテーブルを使えば、このデータから販売日と販売店舗ごとの売上金額や、販売日と販売店舗ごとの販売数などを瞬時に抽出できるようになります。このように、1つのデータを様々な切り口で集計・分析できるのがピボットテーブルの特徴です。
また、急な変更にも対応できるのがピボットテーブルの強みでもあります。たとえば、「30分後の会議に使うので、商品別の月間売上ランキングを作って!」と頼まれて作成したのに、「ごめん!やっぱり週ごとの売上ランキングに変更!」と指示されるようなこともあるでしょう。そんな時でもピボットテーブルなら、テーブルの設定を変更するだけでデータ抽出を完了できるので瞬時に対応が可能です。
注意点
簡単な操作で集計データを抽出できるピボットテーブルですが、自由度が高いだけに最初は戸惑うかもしれません。集計項目の絞り込みや表示方法の変更など、自分が知りたい情報を抽出できるようにカスタマイズが必要です。ピボットテーブルで欲しいデータを自由に抽出できるようになるまでには、それなりの時間がかかるでしょう。
まとめ
関数やピボットテーブルを使ったデータ集計は、Excelに慣れていない人にはなかなか難しいものです。Excelの作業を自動化する機能「マクロ」を使う手もありますが、習得に時間がかかるうえに、専任者を立てたとしても人的コストがかかります。
そこでおすすめなのが、専用のソフトウェア(RPA:Robotic Process Automation)の導入です。Excel専用RPAソリューション「コピロボ」なら、Excelの基本操作さえできればデータ集計の自動化も可能なうえに、作成者以外でも簡単に追加変更・メンテナンスができます。
Excelデータ集計の面倒な設定を避けたいなら、ぜひ「コピロボ」の導入をご検討ください。