Excel。Pivot table。ピボットテーブル。構成比だけじゃなかった、順位も瞬間芸で算出しちゃいます

Excel。ピボットテーブル。構成比だけじゃなかった、順位も瞬間芸で算出しちゃいます。

＜ピボットテーブル＞

ピボットテーブルネタが続いておりますが、奥が深いピボットテーブル。
今回は、前回が構成比の算出をパパッとできちゃった応用編として、なんと順位も瞬間芸で、
算出させちゃうことが出来るのです。

通常、Excelで順位を算出しようとしたら、【RANK.EQ関数】を使うわけですが、
このRANK.EQ関数を作るのが苦手な方も多くて、それに参照を絶対参照にしておかないと、
参照範囲がずれてしまってうまく算出できない。なんてこともあります。

ところが、ピボットテーブルだと、本当に瞬間で算出出来ますので、ご紹介します。

毎度のことながら、データがありまして、

これを基に、ピボットテーブルを使って、下記の表を作成しました。

前回はC列の構成比を求めましたので、D列に順位を算出させます。
まず、フィールドリストにある売上金額を値ボックスに移動しましょう。

D列に、合計/売上金額2　というフィールドが登場しましたね。これを順位に変換していきます。
D列の合計/売上金額2のデータのセルをアクティブにしておきます。

ピボットテーブルツールのオプションタブにある、計算の種類をクリックして、その中にある、
【降順での順位】を選択します。

計算の種類（合計/売上金額2）ダイアログボックスが表示されますので、
基準フィールドは店舗名しか今回はありませんので、店舗名のままで大丈夫ですので、
OKボタンをクリックしましょう。

すると、アッという間に、順位が算出されました。驚きですね。

RANK.EQ関数ではこうはいきませんよね。

ただ、これでは、見栄えが悪いので、D3の合計/売上金額2というフィールド名を変えていきます。

D3をクリックして、ピボットテーブルツールのオプションタブにある、
アクティブなフィールドを順位に入力しなおします。

これで、D3は順位になりましたね。
あとデータも○位という位という単位を表示したいので、表示形式を変更します。

先程のアクティブなフィールドの下にある、フィールドの設定をクリックします。
値フィールドの設定ダイアログボックスが表示されますので、左下にあります、
表示形式のボタンをクリックしましょう。

セルの書式設定ダイアログボックスが表示されます。

分類を【ユーザー定義】にして、種類は、G/標準になっていますので、
その後ろに【位】を入力します。

種類は、G/標準位
あとは、OKボタンをクリックします。

値フィールドの設定ダイアログボックスに戻りますので、ここもOKボタンをクリックしましょう。

これで、完成しましたね。構成比だけじゃなくて、
順位も簡単に算出することが出来るピボットテーブル。なかなかすごいでしょう。

Excel。Pivot table。ピボットテーブルを使うと瞬間芸で構成比を算出出来ちゃいます。

Excel。ピボットテーブルを使うと瞬間芸で構成比を算出出来ちゃいます。

＜ピボットテーブル＞

ピボットテーブルを使うと、簡単に集計できますよ。
簡単にクロス集計が算出出来ますとかまでは、よくテキストなどに書かれておりますが、
実は、構成比も瞬間芸？
というぐらい簡単に算出することが出来てしまうことが意外と知られていないので、
今回はそれをご紹介します。

まずは、下記の大きなデータをピボットテーブルを使って算出させました。

そして、この集計表を使って、構成比を算出していきます。

列ラベルから商品名を削除して、
行ラベルが店舗名で値には合計/売上金額の表を作成しましょう。

そうしましたら、再度、フィールドリストから売上金額を値ボックスに移動します。

合計/売上金額の隣に合計/売上金額2が登場しました。
この合計/売上金額2を構成比に変えていくわけです。

合計/売上金額2のデータのセルをアクティブにしておきましょう。

ピボットテーブルツールのオプションタブにある計算の種類をクリックして、
その中にある、【列集計に対する比率】を選択しましょう。

ピボットテーブルはどうなったでしょうか？

なんと、これだけで、構成比が算出出来ちゃいました。
構成比といえば、Excelでは絶対参照を使って算出しなければいけないとか、
どっちからどっちを除算するのか？と考えてしまったりすることがあるのですが、
ピボットテーブルは、選んで終了なんですね。

あまりにも、あっけないので、本当に合っているのか？
と疑心暗鬼になる方もいらっしゃいますので、
6,884,650を31,164,850で除算してみましょう。

ちゃんと22.09%になるはずです。

あとは、C3の見出しが合計/売上金額2となっていますので、これを構成比に変えていきます。
直接入力しなおしてもいいのですが、ちゃんと修正していきましょう。

ピボットテーブルツールのオプションタブにある、アクティブなフィールドが、
合計/売上金額2となっていますので、ここを、【構成比】と入力しなおしましょう。

これで、構成比が完成しましたね。

どうしても、疑心暗鬼の方は、
ピボットグラフで円グラフを作ってみて確認してみてはどうでしょうか？

ピボットテーブル内のセルをアクティブにしておいて、
ピボットテーブルツールのピボットグラフをクリックします。

グラフの挿入ダイアログボックスが表示されますので、

2-Dの円グラフを選択してOKボタンをクリックしましょう。

円グラフが挿入されましたら、グラフのレイアウトのレイアウト1をクリックしましょう。

レイアウト1が適用されてパーセントが表示されましたね。構成比。
やっぱりあっているのが確認できましたね。

Excel。Pivot table。ピボットテーブルで値に三桁区切りカンマと平均を求めてみたい

Excel。ピボットテーブルで値に三桁区切りカンマと平均を求めてみたい

＜ピボットテーブル＞

前回ピボットテーブルの基本的な使い方として、クロス集計表を作ってみましたが、
三桁区切りのカンマが設定されていなかったり、合計じゃなくて、
平均を知りたいって場合はどうしたらいいのかを説明していきます。

簡単ですが、ピボットテーブルを作っただけでは資料としてはいただけませんので、
最低限でも三桁区切りのカンマぐらいは設定しておきたいものです。

まず、このような大きなデータをピボットテーブルを使ってクロス集計表にしました。

行ラベルに店舗名で、列ラベルには、商品名、値には売上金額でしたね。

この集計されたデータには、三桁区切りのカンマがありませんので、
設定しましょうということをやっていきます。

確かに通常通りに、範囲選択をして三桁区切りのカンマを設定してもOKなのですが、
表が大きい時など、範囲選択だと大変という時もありますので、
値の数値内のセルをアクティブにしておきます。

ピボットテーブルのオプションタブにある、
アクティブなフィールドが【合計/売上金額】になっていることを確認したら、
フィールドの設定ボタンをクリックしましょう。

値フィールドの設定ダイアログボックスが表示されてきますので、

表示形式のボタンをクリックします。

セルの書式設定ダイアログボックスが表示されます。

通常のセルの書式設定ダイアログボックスと異なり、表示形式しかありませんが、
この表示形式の分類を【数値】にして、桁区切りを使用するに、チェックマークをいれます。

そして、OKボタンをクリックします。

値フィールドの設定ダイアログボックスに戻りますので、改めてOKボタンをクリックしましょう。

すると、三桁区切りのカンマが設定されたのが確認できますね。

では、続いて、今算出されている数値は、合計金額ですが、
平均値を求めたい場合はどうしたいいでしょうか？というのが次の質問。

これも、簡単に合計から平均へと変えることができますよ。
また、値のセル。たとえばB5とかをクリックしておきましょう。

ピボットテーブルツールのオプションタブにある集計方法をクリックして、
合計にチェックマークがついているので、合計をしているわけです。

この一覧にある平均をクリックしてみましょう。

こうすることで、アッという間に、算出方法が合計から平均へとかわり、
平均で算出された値が表示されたわけです。

次回は、各店舗ごとの構成比（シェア）をピボットテーブルを使って算出してみます。

Excel。Pivot table。ピボットテーブルを使うと集計表が簡単に作れる

Excel。ピボットテーブルを使うと集計表が簡単に作れる

＜ピボットテーブル＞

最近の企業研修さんでは、必ずと言っていいほどオーダーがある、【ピボットテーブル】。

確かに、ビックデータ花盛りの昨今では、データがあっても、
それをいろんな角度から見るための資料を作らないと、何も出来ませんからね。

そこで、今回は、下記のようなデータを、ピボットテーブルを使って、クロス集計。
つまり、集計表を作ってみたいと思います。

このようなデータがあります。

この大量のデータを、このような集計表にしたいわけです。

ピボットテーブルを知らないで、このような表を作ろうとしたら大変ですよね。

SUMIF関数を使いまくるか、それとも集計を使って算出するのか。
一日作業になってしまいます。

そこで、

【ピボットテーブル】

の登場となります。

使い方は、簡単です。

まず、データの中のどこでもいいので、アクティブにしておきます。
範囲は自動的に選択してくれますので、データ内ならどこでもOKです。

挿入タブのピボットテーブルをクリックしましょう。

ピボットテーブルの作成ダイアログボックスが表示されます。

今回は特に変更する必要はありませんので、そのままOKボタンをクリックしましょう。

ピボットテーブルを設定できるシートが登場しました。

フィールドリストにある、各フィールド名をチェックマークをオンにする、
あるいは、直接ドラックアンドドロップで下部にある、
それぞれのレポートフィルター・列ラベル・行ラベル・値に移動させてあげます。

なお、チェックマークをオンにしますと、下部のボックスにフィールド名が表示されますので、
その後、希望する各ボックスに移動させます。

では、店舗名のチェックマークをオンしてみます。すると行ラベルに店舗名が入りました。

次は、列ラベルに、商品名を移動しましょう。

これで、フレーム部分が完成しました。
あとは、売上金額のチェックマークをオンにするか、下のボックスの値に移動させましょう。

これで、クロス集計は完成しましたね。

ピボットテーブルの特長は、簡単に行と列を入れ替えることが出来るというのも、
その一つだといえます。

行ラベルにある店舗名と列ラベルにある商品名を入れ替えてあげるだけで、
行と列を入れ替えることが出来ます。

このように、とても簡単に見方を変えることが可能になっていますので、
アプローチしたい角度での資料を作ることも容易になっております。

ピボットテーブルは、使い勝手の良いツールですので、
ビジネスシーンで使ってみると色々効率が改善するかもしれませんね。

Excel。Sparklines。スパークラインってどんな時に使うの？

Excel。スパークラインってどんな時に使うの？

＜スパークライン＞

Excel2010からスパークラインという機能が追加されまして、そのスパークライン。
作り方はわかるのですが、どういう時に使うの？
というご質問をよくいただきますので、
今回はその一つのケースとして、スパークラインの作り方となんで、
スパークラインなのか？ということについて、書いてみたいと思います。

まずは、下記のようなデータ。表があります。

このデータを使って、商品ごとの各月の売上推移を見てみたいと思ったとします。

すると、このデータを使って、折れ線グラフにすると、
商品ごとの各月の売上推移がわかるわけですね。

例えば、デラックスチョコレートをマーカー付き折れ線グラフにしてみると、

このようなマーカー付折れ線グラフになりますので、2月が一番売れているんだよね。
6月が一番落ち込んでいるんだよね。とわかるわけですね。

ところが、今回は、デラックスチョコレートというワンアイテムでしたので、
折れ線グラフも一つだったので、傾向はわかりやすかったのですが、
今回のアイテムを全部まとめて動向や傾向を確認したいとした場合、
どのようになるでしょうか？

A1:M23までを範囲選択して、挿入タブの折れ線グラフから、
マーカー付折れ線グラフをクリックしましょう。

すると、次のようなマーカー付折れ線グラフが完成しましたが、
これでは、全くわからないので、修正していきます。

行と列を切り替えますので、デザインタブの行/列の切り替えボタンをクリックしましょう。

グラフのデータが、行と列が切り替わりましたので、横軸が月になりました。

このグラフからですと、生クリームロールが一番人気があって、
他はその他大勢って感じしか読み取れませんよね。プロットされていますが、ぐちゃぐちゃ。

折れ線グラフは、時系列で経過した推移を見ることが出来るグラフではありますが、
このようにアイテム件数が多い場合、全く役に立ちません。

そこで、最初作成しました、デラックスチョコレートのように、
ワンアイテムごとにグラフを作ってもいいかもしれませんが、このアイテム数、
しかも、縦軸の値がアイテムごとによって変わってしまうので、統一した見方が出来ませんから、
すべてのグラフの縦軸も統一した数値に変えていく必要が生じてしまいます。

そこで、スパークラインの折れ線グラフが登場するわけです。

O2をクリックして、挿入タブのスパークラインの折れ線をクリックしましょう。

そうすると、スパークラインの作成ダイアログボックスが表示されますので、

データ範囲には、B2:M2と入力してOKボタンをクリックしましょう。
スパークラインが挿入されますので、
オートフィルハンドルを使ってスパークラインをコピーしましょう。

これで、スパークラインの折れ線が出来ました。このようにすると、
アイテムごとに動向や傾向を見ることが出来ます。

このようにアイテム数が多い時などに、スパークラインを使ってみるといいかもしれませんね。