Excel のピボットテーブル 簡単でいろんな角度から集計や分析が行えます。

ピボットテーブルは、既に Execl が持っている凄い機能です。

このピボットテーブルは、相当古くからある Excel の中の機能です。BB は、むかーし「住中先生」のピボットテーブルというサイトで勉強させて頂きましたが、随分古くから Excel を使っている人でもこのピボットテーブルをご存じでない方や難しそうと言って活用して無い方も、まだまだいらっしゃいますので BB なりに紹介したいと思います。

受託ソフト開発を行っている頃、納品先の担当者の方には、サポートの合間にピボットの簡単な使い方を教えていました。すると教えた方すべてが良いですねーと言って、皆さん重宝して色々な使い方で活用しておられました。ホント教えて良かったなーと思う機能です。

インターネットでも検索するとたくさんの方がピボットについてのサイトをアップしてあります。今更、とは思いましたが、BB ファン(どこに?)の方の為にあえて作成する事にしました。宜しくお願いします。

ピボットテーブルとは?

ピボットテーブルとは、膨大なエクセルデータを年、年月、担当者、得意先、商品など任意の形式で分類したり、集計したり、又、グラフにしていろんな角度で分析しやすいようにしたものです。

BB は、受託ソフト開発を始めた頃は、得意先別や仕入先別、担当別、又商品分類別や商品別など、開発時間を掛けてプログラミングし高い開発費をお客様に請求していました。
しかし、簡単な項目の集計でいい帳票なら、このピボットでも十分使えます。そしたら開発費の削減も出来るのです。ですから今、使ってあるシステムでは簡単な集計分はピボットで作成出来るようにメニューの中に「ピボットテーブルデータの作成」という項目を作り、お客様が自由に活用できるようにしています。

早い話、ピボットテーブルの完成形は、こんな形です。凄いでしょう。データさえあれば作るのは簡単なのです。

 
ビジネスExcel 完全版
 

簡単なピボットテーブルを作成前の準備

使用するデータには下記の条件があります。

  1. データには「項目名」が必ず必要です。きちんとわかりやすい短めの項目名を付けて下さい。
  2. 空白セルが無いように、又、数値欄の空白は、0 を入力して下さい。
  3. 商品名に漢字/かな/英字や全 角/半角などが混在すると別の商品として集計されます。
  4. 得意コードや商品コードの状態では、それぞれの名前がわからなくなってしまいます。前もってコードに対する名前の設定が必要です。

出来るだけ、売上年、売上年月、売上日、担当者名、得意先名、地区名、商品分類名、商品名、数量、金額などのデータ加工が出来れば最高です。出来るだけ、販売管理からCSVで落とす場合、このような型になるようにして作成されて下さい、売上年や売上年月などは、エクセルに落としてからでも加工できると思います。

商品名がバラバラの時、一つに統一する方法

上記の 3. の件ですが、商品名などで、名前付が統一されているかどうかです。例えば、商品名としてパソコンに「デスクトップパソコン」「デスクトップ パソコン」「デスクトップPC」「デスクトップ パソコン」など、色々な形で使われている場合、これを統一する必要があります。そうしないと「デスクトップパソコン」一つに集計されないからです。

このような場合は、 下記のようにピボットテーブルを作成する前に、Excel の状態で、「ホーム」-「検索と選択」-「置き換え」を使って、文字列の設定を行い、「すべて置き換え」ボタンをクリックして商品名の統一化を行って下さい。慣れれば簡単ですよ。

ピボットテーブルの作成

データの加工がすべて完了したら、一旦、データすべてを範囲指定して「挿入」-「ピボットテーブル」をクリックします。
全てを範囲指定の仕方は、ABC と 123 の交差する 1 の上の空白欄をクリックする

ここでは、データ全体が範囲していになっている事を確認して「OK」をクリックします。

下記の画面が、ピボットテーブルの項目を設定する画面です。間違っても「戻る」ボタンで戻れますので安心して試してください。

商品グループをクリックして下記の場所にドラッグします。次に店舗売上をドラッグします。

氏名欄も下記の場所にドラッグします。凄いでしょう!面白いでしょう。
後は、自分なりに考えて上段の項目を下段の4つの場所にドラッグして試してみましょう。

今回は、ここ迄です。
これで、ピボットテーブルの大体の使い方がわかって頂けたと思います。一つ、データ加工の問題もありますが、後が簡単ですので是非、データ加工も頑張って下さい。