ピボットテーブルがベスト! Excelで複数のシートから集計する方法の比較(サンプルあり)

Excelで複数のシートから数字を集計する方法には、いろいろなものがあります。
やはりベストはピボットテーブルと言えるでしょう。
私はこれしか使っていません。

串刺し集計 完成

3D集計(串刺し集計)

まず、1つめは3D集計(串刺し集計)といわれるものです。

事例では、店舗・商品・月別の売上高を合計して、トータルの商品・月別売上高を計算します。
事例 複数のシートから集計

このように数式を入力すれば、各シートの数値を合計できます。

串刺し集計 数式

1つのセルに数式を入れれば、後はコピーするだけで完成です。
串刺し集計 完成

ただし、欠点があります。
各シートのフォームが統一され、変更されないことが条件です。
商品が増えると、それぞれのシートで入力しなければいけません。
シートの数が多くなると、数式を作るのも大変です。

統合

Excelには[統合]という機能があります。
その名のとおり、複数のシートの数値を統合(合計)してくれます。

Excel2007以降では、[データ]タブに[統合]があります。
結合 ボタン


------※この記事は、投稿日現在の状況、心境、法律に基づいて書いています。---------

アイコンをクリックすると、次のようなボックスが表示されますので、統合したい範囲を選択し、[追加]で追加していきます。

結合 開始

事例の場合、新宿、渋谷、池袋の各シートを設定しなければいけません。

結合 設定完了

[統合元データをリンクする]にチェックを入れると、各シートのデータを変更した場合、合計にも連動します。

結合 完成形

この方法にも欠点があります。
やはりフォームを統一させる必要がありますし、データの追加時には一手間かかります。
※統合にはもう1つ方法がありますが、割愛します。

SUMIF関数

もし、1枚のシートに各店舗の表があれば、SUMIF関数を使う方法もあります。
合計の欄を作り、
SUMIF加工前

SUMIF関数を入力します。
B列の各店舗の商品名から、「ノートPC」を検索し、ヒットしたら、数値を合計する関数です。
SUMIF

この場合も、フォームを統一させる必要がありますし、項目を追加したときに一手間かかります。

ピボットテーブル

私は、3D集計、統合は一切使わず、SUMIFも特定の場合にしか使いません。
この事例ならば、ピボットテーブルを使います。

複数のシートのデータが分散している場合は、加工します。
(加工の際にマクロを使うことも多いです。)
ピボット用データへ加工

最初からこういう形式のデータであれば、ピボットテーブルで集計できるのです。
入力時にこのように入力するか、この形式で準備していただくよう依頼します。
ピボット用データ

ピボットテーブルなら、一瞬で数値を集計し、表を作れます。
ピボット集計

表の形式をかえて、分析することも可能ですし、項目を増やしても対応できます。
日別、年別、担当者別や商品名別などでの集計も楽です。
ピボット完成形

ーーーまとめーーー
2つ以上の項目を集計をするなら、SUMではなくピボットテーブルを使うのが便利です。
ピボットテーブルで集計するためには、元データの形式が重要となります。
最初から表を作ると、原則として、ピボットテーブルでは集計できません。

ピボットテーブルを使うようになると、データの作り方から変わってきます。

サンプルで違いを確認してみてください(^^)





【編集後記】

■スポンサードリンク

今週末の横浜トライアスロンに向けて、いろいろと準備しています。
前日に会場にて受付があるので移動・バイク持込の手間もあります。
当日は雨予報なのが心配です。
山下公園側の海を泳ぐのも心配ですが、去年も泳いだのでなんとかなるでしょう(^^;)

■スポンサードリンク



■ブログEX-ITの購読 →feedlyを使って無料で読む
→Twtterで読む
Facebookで読む

■1日1新 Instagram
@1day1new
井ノ上陽一のVALU
■著書
ひとり税理士のIT仕事術―ITに強くなれば、ひとり税理士の真価を発揮できる!!
フリーランスとひとり社長のための 経理をエクセルでトコトン楽にする本
新版 ひとり社長の経理の基本
毎日定時で帰っても給料が上がる時間のつかい方をお金のプロに聞いてみた!
『ひとり税理士の仕事術』
『フリーランスのための一生仕事に困らない本』
『社長!「経理」がわからないと、あなたの会社潰れますよ』
『そのまま使える経理&会計のためのExcel入門』