haku1569　Excel でらくらく データ分析！

ピボットテーブルで一発で売上金額の累計を集計してみましょう。

ピボットテーブルでこんな、2013年の得意先別の売上集計があります。


フィールドリストの値の「金額」を左クリックして「値フィールドの設定」を選択します。


値フィールの設定画面が開きます。
ここで、「計算の種類」タブを開き、「計算の種類（A）」のドロップダウンリストから「累計」を選択し「基準フィールド（F）」で「得意先名」を選択し「OK」します。


すると、この様に売上金額の累計の集計ができました。


ここまで来たら、累積の構成比も集計して、ABC分析をしたくなりますが、、、
残念ながらExcel2007のピボットテーブルではできません。2010以降になると「比率の累計」という集計があるので、それで出来ます。。

ピボットテーブルで一発で売上構成比を集計してみましょう。

ピボットテーブルでこんな、2013年の得意先別の売上集計があります。


フィールドリストの値の「金額」を左クリックして「値フィールドの設定」を選択します。


値フィールの設定画面が開きます。
ここで、「計算の種類」タブを開き、「計算の種類（A）」のドロップダウンリストから「行方向の比率」を選択し「OK」します。


ついでに見易いように降順で並べ替えもしちゃいましょう。
集計された構成比の上にカーソルを置いて右クリして「並べ替え」から「降順」を選択すると、、


と、このように売上構成比の高い得意先から並んだ集計が完成しました。


因みに、売り上げ金額と構成比の両方を集計表示したい場合は、フィールドリストの値の欄にもう一つ金額をドラッグドロップすれば出来ます。

しかし、一番の構成比が10%未満とは、ABC分析等により重点管理はかなり難しそうです。。。(^_^;)

例えば、クロス集計テーブル等を作成すると、一番下の最終行、一番右の最終列にそれぞれの総計が表示されます。邪魔な時は非表示に設定することが可能です。

クロス集計表等ではこのように最終行、最終列に総計が表示されます。


「ピボットテーブルツール」の「オプション」から「オプション」を選択します。


すると、「ピボットテーブルオプション」画面が開きます。
ここで、「総計」の「行の総計・・・」、「列の総計・・・」のチェックを外し、「OK」します。


これで、設定は終了。最終行、最終列の総計が非表示になりました。


他の方法もあります。
非表示にしたい総計のセルを選択して右クリします。
そこで「総計の削除」を選択します。


また、もう一つの方法は、「ピボットテーブルツール」の「デザイン」から「総計」を選択すると、このように行、列の集計を選ぶことができます。

例えば、行ラベルに複数のフィールドを持ってきて集計するとフィールド毎の小計が集計されます。意外とこれが邪魔な場合があるので非表示にしてしまいましょう。

では、行ラベルに「氏名」「得意先名」「商品名」を持ってきて「金額」を集計するピボットテーブルを作ります。


すると、このように、「氏名」、「得意先名」毎に「金額」の小計が表示されます。


では、まず「氏名」の小計を非表示にします。
「稲垣友子集計」の上にカーソルを置いて右クリします。
そこで、「”氏名”の小計」のチェックを外します。
同様に「小料理なんごく集計」で右クリして、「”得意先名”の小計」のチェックを外します。


すると、小計が表示されないすっきりした表になりました。


また、この場合の様に全ての小計を非表示にするばあいは、「ピボットテーブルツール」の「デザイン」から「小計」の「小計を表示しない」を選択すればOKです。

クロス集計と言うとなんだか難しそうですが、どちらかと言うと普通にエクセルで何か集計してまとめの表を手作りする時ってクロス集計をしているんじゃないかと思います。Access等のデータベースを使い慣れていない人にとってピボットテーブルでデータ集計を行うためのデータを作るほうが作り辛いのではないでしょうか？
で、クロス集計と言うのは行と列に項目があるマトリックス表の様なものです。

では、受注金額を得意先と売れた商品でクロス集計表をピボットテーブルで作ってみましょう。
フィールドリストを表示させて行ラベルに「得意先名」、列ラベルに「商品名」、値に「金額」を持ってきます。


すると、このように、どこに（得意先名）と何が（商品毎）のどのくらい（金額）のクロス集計表が出来ました。最終行、最終列にはその合計も集計されます。


もちろんフィールドリストで、今度は「商品名」を行ラベルに、「得意先名」を列ラベルに変えてやれば、商品毎に何処に売れているかの集計にアッと言う間に変更できる。
ワークシートだったら、「形式を選択して貼付け」で「行列を入れ替える」とかで編集しなければならないのがこんなにも簡単にできてしまう。これが、ピボットテーブルの優れた所だと思う。。。

関連ページ
データの作り方
クロス集計で構成比を求める

既に作成してあるピボットテーブルのデータソース（元データ）のデータの追加した場合、追加したレコードがデータソースの範囲に入っていないと更新を行っても集計に反映されません。

ここで、仮に1973行から1982行の受注コード"3057"のデータ（レコード）を追加したとします。因みに得意先名は「小料理なんごく」です。（オキナワの料理屋さんでしょうか？）


既に、データ範囲1972行までで出来上がっているピボットテーブルをみると、「小料理なんごく」の受注金額は280200円と集計されています。


ここで、「ピボットテーブルツール」の「オプション」から「データソースの変更」を選択します。


すると、元データのシートが表示され、その上に「ピボットテーブルのデータソースの変更」画面が開きます。
そして、テーブル範囲の窓が反転表示されていて、同時に元データのシート上にはデータ範囲が破線で囲まれています。
ここで、範囲を1982行まで増やしてあげればいいわけです。。

で、オーソドックスですが、「テーブル/範囲」の窓で行番号のところを"1982"と書き換えて「OK」します。


で、ピボットテーブルに戻ってみると「更新」をかけなくて「小料理なんごく」の金額が"367800"円に更新されています。


例えば、日々にデータが増えていく様な場合に、毎日こんなことをするのは手間なので、そんな場合は予め空白行を含めて、データの範囲を大きくしておけば、いちいちデータソースの変更を行わなくてすみますが、ピボットテーブルには「得意先名」が「空白」と言う集計結果が表示されてしまいます。


それが、邪魔な場合は、見出しの「得意先名」の右端の矢印ボタンをクリックして「値フィルタ」から「空白」のチェックを外します。


そうすれば、「空白」が表示されないテーブルになります。


さて、データソースの範囲変更のやり方を分かったところで、、、
次回は、"テーブル"の設定で範囲をいちいち変更しなくてもいい方法を説明します！



バックナンバー

関連ページ
ピボットテーブルを作ってみた（更新や、用語の説目も）

ピボットテーブルでせっかく体裁よく列幅を調整したのに、テーブルの更新をかけたら列幅が勝手に元に戻ってしまってまた一からやり直しって経験した人、以外に多いんじゃないでしょうか。
それは列幅の自動調整が設定されているからなんです。解除してしまえば、更新前の列幅が維持されますよ！
これ、必須！

ピボットテーブルの上にカーソルを置いて、右クリし、ピボットテーブルオプションをクリックします。


すると、ピボットテーブルオプション画面が開きます。
ここで、「更新時に列幅を自動調整する」のチェックを外し「OK」します。


以上で、完了です！


関連ページ
ピボットテーブルのデザインを変更してみた
集計フィールドの名前を変更する
セルを結合してラベルを中央に表示してみた（ピボットテーブルオプション）Excel2007

複数のフィールド（項目）を使って集計している時、フィールドを折りたたみ非表示にして表をコンパクトにして全体を見易くしたり出来ます。
ここでは、2通りの方法と、おまけに展開・折りたたみのボタンを非表示にする方法を紹介します。

フィールドの展開・折りたたみの方法１
仕入先と商品名の２つのフィールドを使ったピボットテーブルを作ると、仕入先名の先頭にマイナスボタンが表示されます。これをクリックすると。。


今、「アリス亭」の商品名が折りたたまれて非表示になりました。


全ての商品名のフィールドを折りたたむには、「ピボットテーブルツール」の「オプション」から「アクティブなフィールド」をみると、赤いマイナスマークと緑のプラスマークアイコンがあります。
マイナスマークのアイコンをクリックします。


すると、全ての商品名が折りたたまれました。


緑のプラスマークをクリックすると展開されます。


注）これを行う時は、カーソルは折りたたむフィールドに置かれていないとできません。例えば「集計」のフィールドに置かれているとプラス・マイナスのボタンはアクティブになりません。

フィールドの展開・折りたたみの方法２
他の方法もあります。
カーソルを「行ラベル」のフィールドに置いて右クリし「展開/折りたたみ」を選択すると次のようなメニューが現れ、個別のフィールドの展開・折りたたみ、フィールド全体の展開・折りたたみが出来ます。


また任意のフィールドを折りたたみたい時は折りたたみたいフィールドを選択し右クリから「折りたたみ」を実行します。


すると、選択したフィールドのみ折りたたまれました。


展開・折りたたみボタンを非表示にする
フィールドの先頭に表示されるプラス・マイナスのボタンを非表示にしたい時は、「ピボットテーブルツール」の「オプション」から「オプション」を選択します。


「ピボットテーブルオプション」画面でタブ「表示」を開きます。
ここで、一番最初の「展開/折りたたみボタンを表示する」のチェックを外し、「OK」します。


すると、プラス・マイナスのボタンが表示されなくなりました。

Excelのピボットテーブルでフィールドのセルの書式を設定してみましょう。

こんな集計表があります。
“金額”の値の書式を通貨（\マーク付き）に変更してみます。


フィールドリストの値の「金額」を左クリックするとメニューが表示されます。
（右クリではなく左クリックです）


そこから、「値フィールドの設定」をクリックし「値フィールドの設定画面」を表示させます。


左下の「表示形式の設定」ボタンを押します。
すると、Excelでお馴染の「セルの書式設定」画面が表示されます。


ここで「通貨」から表示を設定したら、「OK」で戻っていきます。
すると、値の書式が変更されました。


さて次は、値に“単価”と“金額”の2つのフィールドが設定してあって、2つのフィールドの書式をセルの範囲を選択して一括で変更してみましょう。


関連ページ

例えばフィールドの項目、「得意先」と「商品名」で「金額」の合計を集計する時に、得意先別の商品の金額か、商品別の得意先の金額か、集計順序を変更してみましょう。

まず得意先別の商品金額の集計があります。


この時、フィールドリストの行ラベルを見ると「得意先名」が上に「商品名」がその下にあるます。


この順番を変えると集計順序を変更できます。
マウスでドラッグするのが手っ取り早い方法です。


この様に上に「商品名」を持っていきます。
　

すると、この様に商品別の得意先の金額集計が完成します。