勝手に初心者講座、ピボットテーブル編。
ピボットテーブル４連投の４回目、最終回です。
今回は便利な機能編です。
- ■日付のデータはいろいろ気の利いたことをしてくれる
- ■分析ツール：スライサー
- ■分析ツール：タイムライン
- ■グループ化
- ■行ラベルの空白を埋めたいとき：方法１ピボットテーブルの段階で変更
- ■行ラベルの空白を埋めたいとき：方法２ピボットテーブルを値貼り付けした後変更
- ■単に重複を削除したデータを取りたいとき
■日付のデータはいろいろ気の利いたことをしてくれる
購入日で分析しよう～と列ラベルに「購入日」をもってくると
なんと！「月」という項目が勝手にできちゃってます！
で、まずは月ごとにどうよ、というのを見せてくれる。
月のところの「+」マークをクリックすると購入日毎の集計が現れます。「＋」「－」で切り替えができます。月はいらん！というときはフィールドリストから「月」を取り払って「購入日」だけにすれば月の表示はなくなります。
これはExcel2013からの機能のようです。
それ以前のエクセルでは「グループ化」というのをやると、日付データを月ごと、週ごと、四半期ごと等にまとめることができたとのこと。（知らなかった～使ってない～）
■分析ツール：スライサー
スライサーとは・・・フィルターみたいに絞込表示ができる機能で、フィルターとおんなじ気がするけどより簡単にサッサッと切り替えができる。なので、データを眺めつつ、この商品の場合の傾向はどうかな？とか分析するのに最適なツール、だと思います（私自身、あんまり分析しないので実感こもらず。すみません）
①リボンの「ピボットテーブル分析」タグの「 フィルター」カテゴリーの「スライサー」をクリック
②フィルタリングしたい項目にチェックを入れます（複数選択可）
③絞込表示させたいアイテムをクリックすると、抽出結果が表示されます。
※複数のアイテムで絞込したいときは
・Ctrlキーを押しながらアイテムをクリックすると複数のアイテムが選択できます。
・または、スライサーボックスの右上のボタンを押すとクリックしたアイテムが複数選択されるように切り替わります。
ちなみに複数選択チェックボックスの右のボタンはフィルター解除ボタンです。
■分析ツール：タイムライン
タイムラインは期間を指定して絞込表示できる機能です。この月はどうだったかな～とか、月末～月初にかけてのこの期間はどうだったかな～とか見たいときに便利です。
リボンの「ピボットテーブル分析」タグの「 フィルター」カテゴリーの「タイムライン」をクリックします。タイムラインに使いたい項目を選択します（日付関連の項目が選択可能です）。
タイムラインウインドウの青いバーになっている部分が絞込表示される期間です。
最初と最後をドラッグして青いバーを調節し、表示したい期間を変更します。
右上の▼ボタンで「年」「月」「四半期」「日」の目盛りの切り替えができます。
■グループ化
前日の日付データのところでちらっと出てきたグループ化。
日付データのグループ化や、任意のグループを作ることができます。
購入日と購入者で購入個数をピボットしたもの。
〇日付データのグループ化
購入日のところを右クリックし「グループ化」をクリック。
「月」を選択すると、月ごとの集計結果になります。
「年」や「四半期」、時間のあるデータであれば時間単位でのグループ化もできるんですね。
〇項目のグループ化
購入者を法人か個人かで分類しようと思います。
まず、法人グループを作ります。
Ctrlキーを押しながら「23世紀カンパニー」「ゆめコーポレーション」「未来社」をクリックして選択します。その状態で右クリックして「グループ化」をクリック。
選択した三社がグループ１としてまとまります。
つぎは「剛田」から「野比」を選択し、右クリック、「グループ化」をクリック。
個人がグループ２としてまとまりました。
〇グループ名の変更
グループ１、グループ２だとわかりにくいのでグループ名を変更したいです。そんなときは普通に「ｸﾞﾙｰﾌﾟ1」のセルを選択してぐりぐり普通に入力、「法人」と打ち込みます。「ｸﾞﾙｰﾌﾟ1」は「個人」と打ち直します。
〇フィールド名の変更
ピボットテーブルのフィールドリストを見ると「購入者2」というフィールドができています。先ほど「購入者」を「法人」「個人」にグループ化したものがフィールドとして追加されたようです。
そのフィールを名を変えたいときは、「法人」か「個人」のところを選択した状態で右クリック → 「フィールドの設定」をクリックします。
「名前の設定」のところで変更したい名前を入力します。
なお、グループ化をやめたいときは、右クリックで「グループ解除」をクリックすると解除できます。
■行ラベルの空白を埋めたいとき：方法１ピボットテーブルの段階で変更
下の例はピボットテーブルを「デザイン」タグで「表形式」にしたものです。
みやすいんだけど・・・後で利用するのに項目行の空白をなくしたい、というとき。
リボン、ピボットテーブルの「デザイン」タグ「レイアウト」カテゴリーの「アイテムのラベルをすべて繰り返す」をクリック
こうなります。
「アイテムのラベルを繰り返さない」をクリックすると前の状態に戻ります。
■行ラベルの空白を埋めたいとき：方法２ピボットテーブルを値貼り付けした後変更
ピボットテーブルで集計して、それを利用してあれこれすることが私は多いです。
ピボットテーブルのままだと扱いにくいので、私はすぐにピボットテーブルをコピー、値貼り付けしてピボットの機能を奪い去り、普通の表にしてしまいます。
普通の表にしてしまってから「はっ。項目の空白を埋めたい」と思ったときは。
今回はA・Ｂ列の「購入ＩＤ」「購入者」の空白を埋めたいです。
①Ａ・Ｂ列の隣にＣ・Ｄ列を挿入します。
②Ａ・Ｂ列の3行目の項目をコピーして貼り付けします。
③セルＣ４に式を入力します。
＝IF ( A4=””, C3 , A4 )
もし、Ａ４セルが空白ならＣ列の上のセルの値を、空白でなければＡ４の値を持ってくる、という式です。
セルＡ４は空白でないのでA4の値「1001」が入ります。
次の行のセルA5は空白なので、セルC５にはひとつ上のセルC4の値「1001」が入ります。
④この式をコピーして、C・D列の最後の行まで貼り付けします。
これでC・D列には空白なしの行ラベルができます。
A・B列があっても見栄えが悪いので、C・D列をコピーして値貼り付けし、A・B列は削除します。
■単に重複を削除したデータを取りたいとき
単に、商品ってなにがあるの？と知りたいとき、ピボット機能を使ってしまいます。
「商品」を行項目に指定して、ほ～う、こんな商品があるんだ～ってお手軽に一覧化できる。。。分析とは程遠いけど、重複を排除してユニークな値を取り出したいときなどに便利です。
これでピボットシリーズは終わりです。
最後までお読みいただきありがとうございました！
ピボットテーブルシリーズ
コメント