【今さら聞けない】Excelのピボットテーブルとは?

  • ガイド ( How to )
  • 閲覧数:2475
  • ありがとう数:0

はじめに

Photo by 素材辞典

ピボットテーブルというのがあるのは知ってるけどどんなものなのか、

何に使うのか、どの程度できればピボットテーブル使えます、といえるのかをまとめてあります。

STEP1どんな時に使うのか

ピボットテーブル

エクセルのピボットテーブルはどんな時に使うんですか?その有用性を教えてください。

例えば
地域別+男女別
商品別+価格別
日別+担当者別
・・
などのように
A2,A3,A4・・方向に地域別名が並び。B2、C2、d2・・方向に男女
の項目が並ぶ、2次元の表が簡単に作れます。
もちろん地域を表すコードや地域名、男女を表すコードや男女の別がデータとしてリスト形式(WEB照会のこと)必要です。
http://www11.plala.or.jp/koma_Excel/contents3/mame3020/mame302004.html
行と列に項目をドラッグ&ドロップするだけです。
計数は合計や個数や他数種選べます。

全国に10箇所の支社を持つ会社で、それぞれの支社から毎月の売り上げデータが本社に集まってくるところを想像してみてください。
あなたはそのデータの集計・分析を担当していると仮定します。

そのデータは、「10社×12ヶ月×部門数」個数のデータが集まっていることになります。
これを表にまとめる時、あなたはどのような表を作りますか?
例えば、以下のような表を作り…
   1月 2月 3月 ・・・
A支社
B支社
C支社
D・・

これに、さらに部門別に整理したい場合、[方法A]1月の列を部門で区切る方法もあるでしょうし、[方法B]A支社の行を部門で区切る方法もあります。
仮に、この両者[方法AとB]を切り替えて見比べたい場合、二つの表を作る必要がありますよね?

これをピボットテーブルを使って作成しておくと、「部門」をドラッグ&ドロップするだけで、方法AとBを簡単に切り替えることができます。

これはほんの一例ですが、いろいろな切り口でデータを集計したい時などにピボットテーブルを利用します。

言葉での説明ではわかりにくいので、一度、サンプルを見た方がわかりやすいと思います。

STEP2日別のデータを月別に集計する

Excelで日別に入力されたデータを月別に集計するにはどうしたらいいのでしょうか。

Excelで月別の集計をしたいのですが
例えば
  日付    数量
10月25日  200
10月27日  150
10月30日  120
11月 1日  200
11月 3日  150
のように、日付が飛び飛びで連続していなくい日々の数量を、上の行から順番に入力していった場合に(月が替わる行が決まっていない)月別の集計を取る方法はないでしょうか。


 

どのように集計する月を指定するのかが書かれていませんが、例えば11月分の合計を出したいなら

1)11月までの合計から10月までの合計を引く方法
 =SUMIF(A:A,"<=11/30",B:B)-SUMIF(A:A,"<=10/31",B:B)

2)月が「11」のB列を合計する方法
 =SUMPRODUCT((MONTH(A2:A100)=11)*(B2:B100))

なども考えられます。

でもエクセルなら一番オーソドックスな方法はピボットテーブルでしょう。C列に
 =TEXT(A1,"YYYY/MM")
で「2008/11」のような文字列を生成しておいてピボットテーブルで集計すれば、シート上にある全ての月の合計が一発で求められます。
ピボットテーブルの使い方はGoogleなどで検索したらたくさん見つかります

別シートに
 年月 合計数量
 08/01
 08/02
・・・
 09/01
年月には2008/1/1 とシリアル値で入れて、書式、表示形式で年と月を表示させておきます。
合計のセルには 
=SUM(INDEX(データ!B:B,MATCH(A2,データ!A:A)):INDEX(データ!B:B,MATCH(A3,データ1!A:A)))
で下までコピィしてください。
合計(SUM)関数ですが範囲をA列の年月の当月と次月の日付から探して指定しています。
データの最初には
  日付    数量
 1月 1日    0
最初の月の集計の為にダミーデータ入れる必要あったと思います。
式は少し複雑ですが SUMPRODUCT関数など配列関数を多用すると重くなりますが、この式では軽くてすみます。
データが飛びでもかまいませんが日付がシリアル値で入力されている。
日付順に並んでいることが条件です。

ピボットテーブルはどうでしょうか

日付 A列
数量 B列
の場合
C列を「月」とし
C2 =MONTH(A2)
として、下にコピーします
B列とC列を選択し、ピボットテーブルを設定します
「月」を行のフィールドに、「数量」をデーターアイテムにドラグします

STEP3ピボットテーブルでデータの分析と抽出

エクセルで、販売数量と売上金額の管理をしています。

エクセルで、販売数量と売上金額の管理をしています。

26個の小分類の管理と、11個の中分類、5個の大分類の管理で、毎日同じものを販売しています。

これをピボットテーブルを使って、いろいろな分析(前年比や商品単価の推移など)や、必要なデータのみの抽出なんかができればと思いましたが、なかなか思うようにできません。

現在、同じものを毎日販売しているので、

シート1(数量のみ入力)
日 商品A 商品B 商品C 商品D・・・

シート2(売上のみ入力)
日 商品A 商品B 商品C 商品D・・・

としています。そして、それぞれをテーブルにしています。


エクセルは2007です。


わかりにくい説明で、申し訳ありません。
みなさん、なにかよい方法をおしえていただけたら、幸いです。

シートのデータを、
日,売り先,商品名,担当者,数量,単価,売上額
としてみてはいかがでしょう。

ピボットでやりやすい形になります。
日に追加して月・四半期・半期・年度
売り先に追加して地域・業種
商品名に追加して商品大分類・中分類
担当者に追加して部門・事業所
などの属性をつけてあげるとより詳細な分析が可能になります。

特に「推移」というのはクロス集計をかけた際の一軸に時間軸を
持ってきたものですので簡単に出せます。

たとえば、縦軸に商品名、横軸に月をもってきてデータ欄に
数量と売上額をセットすれば商品別月別の売上推移表になります。

STEP4クロス集計クエリとピボットテーブルの違い

クロス集計クエリとピボットテーブルの違い

アクセスのクロス集計クエリとピボットテーブルの違いがよくわからないのですが、
どういうことを基準に使い分ければいいのでしょうか?
デザインでしょうか?

ピボットテーブルはExcelの機能ですけどね。Accessの機能じゃなく。

ですので、使い分けの基準は「Accessを使うならクロス集計クエリ、
Excelを使うならピボットテーブル」です。

まあ、AccessからExcelのピボットテーブルを呼ぶことも、Excelから
Accessのクロス集計クエリを呼ぶこともできますが、やってることは
ほぼ同じですから、わざわざ別ソフトの機能を呼びなさなくても・・・。

ピボットテーブル
クリック、ドラッグ&ドロップでグラフィカルに簡単に作成できる。
ただ、手の込んだ改良は出来ない。注1
出来上がったフォームを他のフォームと関連付けて処理させるのが難しい。注2
取りあえずチャチャッと作らねばならない場合に向いている。
注1・2は私の不明のためかもしれません。

クロス集計
ピボットテーブルよりはハードルが少し高い。
作成したものを元にして再利用がしやすい。
見栄えの良いものを作成しようとなると、フォームやレポートを作ることになる。

STEP5履歴書に書けるピボットテーブル使えますとはどの程度?

ピボットテーブル

仕事で「ピボットテーブルが使えます」というのはどの程度のレベルまで出来ないといけないのでしょうか? 私は作成できてグループ化、並び替え程度ですが実際仕事で使ってらっしゃる方は、関数とかも合わせて使ったりされていますか?

> 仕事で「ピボットテーブルが使えます」というのは
> どの程度のレベルまで出来ないといけないのでしょうか?

会社によってまちまちだと思います。

ピボットはグラフも連動できて応用範囲が広いので、「どこまで」、というのは正直範囲が広すぎて、なかなか定義するのは難しい気がします。

>私は作成できてグループ化、並び替え程度
基本的には、ピボット専用書籍程度の操作ができればよいのではないでしょうか?
たとえばちょっとまえの書籍だとこんなの
http://www.bk1.jp/product/02769950

具体的には、グループ化、並び替え以外だと「集計フィールド」「集計アイテム」「ピボットグラフ」「ドリルダウン」「ドリルアップ」「ドリルスルー」、「TOP××」、「構成比」、「累計」、「平均」、応用で「RFM」分析など。

関数も使うことはあります。
でも関数を使う場面はそれほど多くないのではないでしょうか?
ソースの表のほうでは色々と使う方も居るかもしれませんが、私はMicrosoftQueryとセットで使うことが多いので、関数はせいぜいIntとかIIF、IF関数あたりくらいです。

そのほか、ピボットをVBAで生成するようなケースも結構あると思います。
こちらもそれなりに使うような気がします。
定型表示のピボットではいちいちフィールドを入れ替えるのが面倒な場合もありますので・・・。

特集


感謝指数をマイページで確認!

ピックアップ

ページ先頭へ