2014年04月26日

累計を集計する/Excel/ピボットテーブル

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

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

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

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

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

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


posted by haku1569 at 14:14| Comment(0) | Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

構成比を集計する(売上構成比とか)/Excel/ピボットテーブル

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

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

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

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

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

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

因みに、売り上げ金額と構成比の両方を集計表示したい場合は、フィールドリストの値の欄にもう一つ金額をドラッグドロップすれば出来ます。
20140426-09.JPG
しかし、一番の構成比が10%未満とは、ABC分析等により重点管理はかなり難しそうです。。。(^_^;)
posted by haku1569 at 14:02| Comment(0) | Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2014年04月20日

列や行の総計を非表示にする/Excel/ピボットテーブル

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

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

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

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

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

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

また、もう一つの方法は、「ピボットテーブルツール」の「デザイン」から「総計」を選択すると、このように行、列の集計を選ぶことができます。
20140420_56.JPG
posted by haku1569 at 15:49| Comment(0) | Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

小計を非表示にする/Excel/ピボットテーブル

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

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

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

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

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

また、この場合の様に全ての小計を非表示にするばあいは、「ピボットテーブルツール」の「デザイン」から「小計」の「小計を表示しない」を選択すればOKです。
20140420-45.JPG
posted by haku1569 at 15:35| Comment(0) | Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

クロス集計表を簡単に作成する/Excel/ピボットテーブル

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

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

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

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

関連ページ
データの作り方
クロス集計で構成比を求める
posted by haku1569 at 14:52| Comment(0) | Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

2014年04月19日

データソース(元データ)の範囲を変える/ピボットテーブル/Excel

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

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

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

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

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

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

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

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

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

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

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



バックナンバー

関連ページ
ピボットテーブルを作ってみた(更新や、用語の説目も)
posted by haku1569 at 22:05| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

更新しても列幅を固定する/Excel/ピボットテーブル

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

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

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

以上で、完了です!


関連ページ
ピボットテーブルのデザインを変更してみた
集計フィールドの名前を変更する
セルを結合してラベルを中央に表示してみた(ピボットテーブルオプション)Excel2007
posted by haku1569 at 21:22| Comment(0) | Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

フィールドの展開と折りたたみ(ボタンの非表示とかも)/Excel/ピボットテーブル

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

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

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

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

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

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

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

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

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

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

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

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

すると、プラス・マイナスのボタンが表示されなくなりました。
20140418_311.JPG
posted by haku1569 at 18:35| Comment(0) | Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

値の書式を設定する(値フィールドの設定)/Excel/ピボットテーブル

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

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

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

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

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

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

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


関連ページ
posted by haku1569 at 16:45| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする

フィールド(項目)毎の集計順序を変更する/Excel/ピボットテーブル

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

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

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

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

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

すると、この様に商品別の得意先の金額集計が完成します。
20140419_05.JPG
posted by haku1569 at 16:15| Comment(0) | Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする