ピボットテーブルでデータの集計を自由にデザインしましょう

シェアする

  • このエントリーをはてなブックマークに追加

ピボットテーブルは、Excelの機能の中でも、少し難しいもののように感じている方が多いものです。ですが、実際に使ってみると、直感的な操作が可能で、使いやすい機能であることが分かります。
イメージに惑わされずに思い切って使ってみましょう。

スポンサーリンク
自動サイズレスボンシブ

ピボットテーブルは、データの集計方法を簡単に変えられる機能です

Excelは数値の集計を得意にするアプリです。
機能や関数を使って膨大な数値データを瞬時に集計してしまいますが、関数について言えば、ひとつひとつ内容を考えながら入力する必要があり、多少の慣れと知識を必要とします。


その点、ピボットテーブルはもっと直感的。
「勘定科目毎に合計したい」
「地域毎の売上を知りたい」
「月毎の経費を知りたい」
など、集計の範囲を指定するだけで、すぐに集計することができます。


つぎのデータを見て下さい。

スクリーンショット 2015 01 17 12 59 17


売上データのサンプルです。
データには売上だけでなく、取引先、取引先のエリア、商品名のデータがあります。


売上の合計を求めるだけなら、簡単です。
ですが、売上分析のために取引先別、エリア別、商品別といった項目毎に集計することを考えるとどうでしょうか。


いちいち項目毎に関数を入力して合計するというのは手間がかかります。
データの量が少なければ、まだいいですが、膨大な量のデータになると、なかなかやっかいです。


その点、ピボットテーブルなら、

スクリーンショット 2015 01 17 13 04 17

スクリーンショット 2015 01 17 13 04 38

スクリーンショット 2015 01 17 13 04 48

さらに、

スクリーンショット 2015 01 17 13 05 33


「商品別+取引先別」といったクロス集計も一瞬のうちにできてしまいます。


今回は基本的なピボットテーブルの使い方を解説します。

ピボットテーブルを使ってみましょう

ピボットテーブルの使い方を説明します。

1.ピボットテーブルの範囲とシートの場所を指定する

まず、集計したいデータにあるセルをアクティブ(セルが緑の枠で囲まれた状態)にします。

スクリーンショット 2015 01 17 13 20 19

Excel上部の「挿入」タブをクリックすると、左端に「ピボットテーブル」のボタンが現れるので、それをクリック

スクリーンショット 2015 01 17 13 14 26


「ピボットテーブルの作成」画面が開きます。

スクリーンショット 2015 01 17 13 14 50


ピボットテーブルで集計する範囲を指定しますが、通常はこの状態で既に、Excelの方で勝手に範囲指定してくれています。


最初に集計したいデータのあるセルをアクティブ(セルが緑の枠で囲まれた状態)にしていると、このセルを中心にして集計範囲の候補をExcelが指定してくれるのです。


ただし、候補はあくまで候補であって、必ず正しい範囲を指定してくれている訳ではないので、念のため確認します。
もし、範囲にずれがある場合は修正します。


次に、ピボットテーブルを作成するシートの指定です。

スクリーンショット 2015 01 17 13 14 50 のコピー


そのままの状態なら、新しくシートが作られることになります。
ですが、それまで使っていたシートの中にピボットテーブルを組み込みたいというケースもあります。


その場合は、「既存のワークシート」をチェック。

スクリーンショット 2015 01 17 13 38 44

ピボットテーブルを配置したいシートにあるセルをクリックしてアクティブにすると、

スクリーンショット 2015 01 17 13 39 44


「既存のワークシート」の指定欄に、ピボットテーブルを配置するシートとセルが入力されます。


これで、アクティブになったセルを左端上部の角として、ピボットテーブルが配置されます。


最後にOKボタンをクリックしてピボットテーブルが作成されます。

2.ピボットテーブルのレイアウトを決める

ピボットテーブルはできましたが、そのままでは何も表示されません。

スクリーンショット 2015 01 17 13 50 27


データの中のどの項目について、何を集計するかといった、レイアウトを決めていないからです。
元のデータから何を集計するのかを決めていきます。


もう一度元のデータを見てみましょう。

 

スクリーンショット 2015 01 17 13 49 11


いくつかの項目が並んでいますが、まずは、分かりやすいところから、商品別の売上のデータを集計することにします。


右端にあるピボットテーブルのフィールドでレイアウトを決めます。

スクリーンショット 2015 01 17 13 54 42

使い方は簡単です。


集計したい項目を、ドラッグ&ドロップして”行”に持って行き、

スクリーンショット 2015 01 17 13 54 42 のコピー


集計したい項目を、ドラッグ&ドロップして”値”に持って行き来ます。

スクリーンショット 2015 01 17 13 54 42 のコピー 2


「ピボットテーブルのフィールド」はこのようになります。

スクリーンショット 2015 01 17 14 00 56


その結果できあがったのがこちら。

スクリーンショット 2015 01 17 14 02 20


商品別の売上が集計されました。

3.ピボットテーブルを整える

ピボットテーブルは、1度作成されても固定されるものではありません。
使いたい形にどんどん変更することができます。


やり方も、項目をドラッグ&ドロップするだけですので直感的に変更を加えることができます。


今回は上の商品別の売上に、取引先のデータを加えて集計することにします。

スクリーンショット 2015 01 17 14 07 37


その結果がこちら。

スクリーンショット 2015 01 17 14 09 47


集計はできましたが、見づらいですね。
列と行を代えてみましょう。

スクリーンショット 2015 01 17 14 10 56


その結果、

スクリーンショット 2015 01 17 14 13 20


こうなりました。
これなら見やすいですね。


集計したい項目をドラッグ&ドロップで行、列にレイアウトするだけでどんどん集計できてしまう。
ピボットテーブルの使いやすさは、この直感的な操作ができるところにあります。

ピボットテーブルで重要なのは、ピボットテーブルの使い方ではなく、データの整理

ここまで見てきたように、ピボットテーブルは非常に使いやすいです。
普段必要とされる集計作業については、特に難しい操作を覚える必要はないでしょう。


ですが、それ以外に注意しなければいけないことがあります。
それは、データの整理です。


ピボットテーブルを正しく使うのに重要なのは、「使い方」以上に、データを正しく整理できているかにかかっているのです。

1.リスト形式になっている

集計するデータがリスト形式になっていなければいけません。
「リスト形式」と言うと特別なもののように聞こえますが、普段皆さんが使われている、先頭行に項目があって、その下に、縦にデータが並んでいる形式のことです。

スクリーンショット 2015 01 17 14 21 08

2.空欄の行がない

空欄の行があると、最初のピボットテーブルの範囲指定の時に、空欄行の上の行までを指定されてしまいます。


空欄があっても、集計範囲を正しく指定すれば集計できるのですが、最初から空欄を無くしておいた方が間違いなく範囲指定することができます。


最初の段階で、空欄行のないデータを作成しておきましょう。

3.先頭行の項目に空欄がない

たとえば、次のように先頭行に空欄があるデータでピボットテーブルを作成することにします。

スクリーンショット 2015 01 17 14 26 28

すると、

スクリーンショット 2015 01 17 14 28 41


エラーがでます。


先頭行の項目は、必ず入力された状態でピボットテーブルを作成する必要があります。


以上の3点がピボットテーブルを作成する際には非常に重要ですので、ピボットテーブルを作る前に丁寧に準備するといいでしょう。

まとめ

ピボットテーブルは、レイアウトを直感的に操作して集計することができる便利で使いやすい機能です。
データの整理には注意して、積極的に使っていきましょう。
<おまけ>
SEKAI NO OWARIがフリーライブを開催するというので見に行こうと思ったのですが、風が強くて寒すぎるのでやめることにしました。
1度はどこかのフェスで見るつもりです。


【セミナー情報】
『実務で使えるExcel入門セミナー 基礎編』
2月14日(土) 14時 恵比寿セミナールーム

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする