勝手にエクセル初心者講座 (*’▽’)
前回ピボットテーブルの基本的な使い方を書きました。
今回はちょっと応用編。いろいろピボットテーブル操作編。
- ■複数の項目の集計をしたいとき
- ■時刻・日付など、表示書式を変えたいときの例
- ■絞込をして集計をしたいとき（一つだけ）
- ■絞込をして集計をしたいとき（複数の値で）
- ■並べ替えをしたい
- ■列・行の項目で一部の項目を外したい、とか項目を限定したいときとか
- ■複数項目を行列に指定したいとき
- ■複数項目を行列に指定したいとき②従来のピボットテーブル
- ■複数項目を行列に指定したいとき②表形式で表示
- ■小計はいちいちいらないんだけど・・・という場合
- ■小計はいちいちいらないんだけど・・・という場合②「デザイン」タグを使う
この表を例に使います。
■複数の項目の集計をしたいとき
購入者IDで集計、何回購入して、合計で何個買ったかを集計したい。
①挿入→ピボットテーブル作成
②行：購入者IDをドラッグして指定
③値：購入者IDをドラックして指定→値フィールドの設定で「個数」を指定
④値：購入個数をドラックして指定→「合計」となっていることを確認（合計になっていなければ「合計」を指定）
こちらが結果。購入者ID1022のゆめコーポレーションさんは5回購入、計350個の商品を購入しています。
■時刻・日付など、表示書式を変えたいときの例
購入者の一番最新の購入日を知りたい。
①挿入→ピボットテーブル作成
②行：購入者ＩＤをドラッグして指定
③値：購入日をドラッグして指定
→右クリック：値フィールドの設定から「最大値」を選択
→値フィールドの設定の左下のボタン「表示形式」をクリック
→おなじみの書式設定の画面が出てくるので日付の書式を選択
結果はこうなります。
■絞込をして集計をしたいとき（一つだけ）
「地域」が「北海道」の購入者がどんな商品をいくつ購入しているか知りたい。
①挿入→ピボットテーブル作成
②行：購入者をドラッグして指定
③列：商品をドラッグして指定
④値：購入個数の合計を指定
⑤フィルタ：「地域」をドラッグして指定
これでピボットテーブルの基本はできました。
⑤ピボットテーブルのフィルタの部分「地域」の▼印をクリック
北海道をクリックして選択。ＯＫクリック
これで北海道地域のみに絞込表示されます。
北海道地域の購入者の購入商品の分析ができます！
フィルタの地域の選択を別の地域にすると、別の地域の分析ができます。
切り替えもさっと簡単！
■絞込をして集計をしたいとき（複数の値で）
こんどは大都市圏の関東と関西の分析をしたくなりました。
フィルタで関東と関西の両方を選択します。
①~⑤までは前項と同じ
①地域のフィルタの▼をクリック
②「複数のアイテム」をクリック
③チェックボックスが現れるのでチェックボックスを選択(一つでも複数でも選択可）
関東と関西の購入者と購入商品の合計個数が集計されます。
■並べ替えをしたい
上の集計結果を購入個数の総計を降順に並べ替えてみたくなりました。
そんなときは
①総計のところで右クリック
②並べ替えを選択→「降順」を選択
並べ替えができました！
ゆめコーポレーションさんが大口購入先ですね。
■列・行の項目で一部の項目を外したい、とか項目を限定したいときとか
購入者毎に購入個数をピボットした結果です。
なんと「未来社」が倒産してしまいました。なので「未来社」を分析対象から外したくなりました。
そんなときは
①「行ラベル」の▼をクリック
②外したい対象のチェックを外す
■複数項目を行列に指定したいとき
地域ごとの購入者を行項目に、商品を列項目にして、購入個数の集計をしたいとき。
地域・購入者ＩＤ、ＩＤだけではわかりにくいので購入者を行項目に。
商品ＩＤだけではわかりにくいので商品ＩＤ・商品名を列項目にします。
やってみましょう！
行：「地域」「購入者ＩＤ」「購入者」をシュッシュッシュッとドラッグ
列：「商品ＩＤ」「商品」をシュッシュッとドラッグ
値：「購入個数」をドラッグ、計算方法は合計
以下のようになります。
■複数項目を行列に指定したいとき②従来のピボットテーブル
上の結果を見て・・・いやー見やすいんだけどねー。行ラベルの「地域」「購入者ＩＤ」「購入者が」Ａ列に全部入っている。後で利用するのにそれぞれの列に分かれてほしいんだけどー。という場合は！
ピボットテーブルの中のどこかで右クリック → 「ピボットテーブルオプション」をクリック → 「表示」タグの「従来のピボットテーブルレイアウトを使用する」にチェック → 「ＯＫ」クリック
すると
分かれてくれます！
前のバージョンのエクセルだとこうゆう風になってくれたんですよね。前のピボットテーブルの方が使いやすかったな・・・と私は思います。。。
■複数項目を行列に指定したいとき②表形式で表示
いつもは上の「従来のピボットテーブル」で変換していたのですが、もっと良い方法がありました。
ピボットテーブル内のどこかを選択した状態で
①リボンの「デザイン」をクリック
②「レポートのレイアウト」を選択
③「表形式で表示」を選択
すると項目が列に分かれた状態に変わります。
ちなみに「コンパクト形式」が最初の形。「アウトライン形式」は「コンパクト形式」と「表形式」の折衷形式みたいな感じです。
なんでか、私は表形式が一番しっくりきます。前のピボットに慣れすぎているからでしょうか。。。
■小計はいちいちいらないんだけど・・・という場合
上の結果で、商品ＩＤ毎の小計、購入者毎ごとの小計はいちいちいらないんだけど・・・という場合は！
商品ＩＤが表示されている行のどこかで右クリック → ”商品ＩＤ”の小計 にチェックがついています。このチェックが外れれば小計が表示されなくなるのでここをクリックします。→ 商品ＩＤの小計が非表示になります。
同様に、購入者ＩＤが表示されている列のどこかで右クリック → ”購入者ＩＤ”の小計をクリック → 購入者ＩＤの小計が非表示になります。
地域ごとの小計も不要の場合は同様に 地域の列で右クリックして “地域”の小計 のチェックを外します。
その結果すっきりした表になりました！
総計もいらないよ！という場合は、総計のところで右クリックして「総計の削除」をクリックします。
■小計はいちいちいらないんだけど・・・という場合②「デザイン」タグを使う
これももっと良い方法が・・・。あったことに今日気が付いた！
ピボットテーブル内のどこかを選択した状態で
①リボンの「デザイン」をクリック
②「小計」を選択
③「小計を表示しない」を選択
となりの「総計」をクリックすると総計を入れるとか入れないとか各種選べます。
便利～
今日はこんなところで。
最後までお読みいただきありがとうございました。
次回もピボットテーブルを予定しています。
ピボットテーブルシリーズ
コメント