Excel大好きプログラミングOLこと、くらげ(@owasa7)です😃
先日Twitterで、Excelについて困っていることはありますか？と質問してみたところ、
お二人の方から「ピボットテーブルがそもそも何なのか、わからない！」という回答をいただきました。
そこで今回は、ピボットテーブルの超基礎を解説していきたいと思います。
超基礎なので、ピボットテーブルって何？一体どんなことができるの？どうやって使うの？などを説明していきます。
ご回答いただきました、
- 青りんごさん(@guuuuutaraol)
- くじらさん(@qOQKjV1wE8plnIa)
ご協力ありがとうございました😍😍
その他のExcelお役立ち関数については、こちらのまとめ記事をご参考いただければ嬉しいです♪
Excelピボットテーブルってなに？どんな事ができる？
ピボットテーブルとはExcel機能の一つで、たくさんあるデータを一目でわかりやすい表に変換するという特徴をもっています。
販売実績の確認や傾向の分析に使ったり、合計や平均などの計算を一瞬でやってくれたりとお仕事の効率化にめちゃめちゃ貢献してくれます。
ピボットテーブルを使った例
たとえば、こんなデータ表があったとします。
これは、フルーツの3か月の売上データです。りんご・パナナ・パイナップルを3つのくらげグループ商店におろしています。
この表のままだと、3か月の合計売上や個数をSUM関数を使って計算することはできますが、
- 八百屋くらげは、9月にどのくらい売り上げたんだろ？
- りんごは、3か月で合計何個売れた？
- 月別の売り上げ金額の傾向は？増えてる？減ってる？
といった、細かい分析を一目で理解することができません💦💦
そこで使うのが、ピボットテーブルです。上記の分析を簡単に表すことができます。
ピボットテーブルを使った表は、この通り。
- 八百屋くらげは、9月にどのくらい売上たんだろ？
- りんごは、3か月で合計何個売れた？
- 月別の売り上げ金額の傾向は？増えてる？減ってる？
ピボットテーブルをつかえば、データからこんなわかりやすい表を作成することができます。
これのすごいところは、一回表を作ってしまえば、行ラベル(縦軸)・列ラベル(横軸)の項目を自由に入れ替えることができるということです。
上の3つの表もわざわざ3つ表を作ったのではなく、1つ作ったあと項目を入れ替え、表を作り変えました。
まあよくわかんないと思うので(笑)ピボットの仕組みを説明していきます。
ピボットテーブルの仕組み
ピボットテーブルは、主に2つのパートに分けることができます。
実際の集計データを表してくれるテーブルと
テーブルの項目(フィールド)を選択できるフィールドリストです。
このフィールドリスト上のフィールドを動かすと、連動してテーブルも動く仕組みです。
フィールドリストは、上記の通りに対応しています。
テーブルの内容を変更したい場合は、フィールドが並んでいる上のリストボックスの中からドラッグして下のボックスの中に移動します。
たとえば、
商店の月別合計表にくだものの種類を追加したい場合、
フィールドリストの上のボックスから種類のフィールドをドラッグして、下の行のボックスに移動させます。
売上金額から、個数に変更したい場合も、
個数を上のリストからドラッグして、下の値のボックスに移動させます。
この時売上は表示したくないので、売上のフィールドをつかんでどこでもいいのでボックス外にポイっと捨てちゃいましょう。
すると、テーブルも個数だけ表示されます。
ピボットテーブルの作成方法
それでは、Excel内でどうやってピボットテーブルを作成していくのでしょうか？
説明していきたいと思います！
ピボットしたいデータ表を開いたら、
どこでもいいので、表内のセルをクリックします。
つぎに、挿入タブを選択するとリボン内にピボットテーブルが現れます。クリックしてください。
表内でクリックしていたので、Excelが勝手にテーブルの範囲を決めてくれました。
(事前にクリックしないと範囲が空白です。自分で選択することができます。)
選択されているテーブル/範囲に問題なければ、何もしなくて大丈夫。
テーブルを設置する場所は他の新しいシートにした方が見やすいので、新規ワークシートを選びます。
全部選択できたら、OKを押します。
テーブルとフィールドリストが新規のシートに作成されました。
ここからは、前述の通り自分の好きなフィールドをボックスに追加してピボットテーブルを完成させていきましょう。
ちなみに、左上のフィルターのボックスにもフィールドをいれることもできます。
文字通り、テーブルにフィルターをかける役割です。選択したもの以外は表示しなくなります。
フィルターに使いたいフィールドをボックスに移動させます。
テーブルの一番上に、フィルターを選択できるプルダウンが現れます。
テーブルに表示させたいフィールドを選択すると、
選択したフィールドの値だけを返してくれます。
今回は八百屋くらげを選んだので、その個数だけが表示されています。それ以外は含まれていません。
複数個選択したい場合は、フィルターで選択の際、複数のアイテムを選択にチェックをいれるとチェックボックスが表示されます。
イカ商店の売り上げ個数は表示したくない。という場合は、そのチェックだけを外せばそれを含まない値が返されます。
ピボットテーブル作成時の注意点
わかりやすい表を超簡単に作成してくれるピボットですが、基本的なことを忘れるとデータが変換できない！となってしまう場合もあります💦
データ表には、ある程度のルールがあります。以下の点には注意してピボットテーブルを作成してみましょう。
タイトルは抜けもれなく
[ピボットテーブルの仕組み]で説明のとおり、ピボットのフィールドはデータ表の一列目(タイトル)を基に自動で生成されます。
なので、タイトルが抜けているとピボットがそこをフィールドと認識することができません。
タイトルが抜けてないか、確認しましょう！
結合セルは使えない・・・
セルを結合すると情報量も少なくなって見やすくはなりますが、これではピボットすることができません。
1つのセルに、1つの値をいれるようにしましょう！
半角と全角をまぜない
ピボットは、半角と全角を別のものとして認識してしまいます。
なので、表記に統一性がないと同じものとしてカウントしてくれません・・。
気をつけましょう！
集計したいデータ以外は取り除く
社内向けデータ表にありがちなのが、上のような小計や総計が挿入された表です。
見た目は美しいのですが、ピボットをかけるためには一列目のタイトル以外のデータは入力してはいけません。
空白の行・列をいれるのもできれば避けた方がいいので、データ表はシンプルに保ちましょう！
まとめ
- ピボットテーブルを使えば、一見してわかりやすい表が作成できる
- 作成は超簡単！フィールドリストを組み合わせて、様々な値を表示させよう
- データ表作成で注意しなければならない点がある、要確認！
今回の記事では、ピボットテーブルの超基礎を解説してきました。
ピボットは結構奥が深いです💦まだまだ知らない機能もたくさん！
おすすめの使い方や応用編についても、これから解説していきたいと思います。
みなさんも、Excelで効率の良い素敵なオフィス生活を♪