Excelピボットテーブル データ集計・分析の「引き出し」が増える本

様々な計算が自由自在

Excelのピボットに関して、初歩から段階的に学べる内容になっています。サンプルデータから実際にデータを操作することができるので、実践的な知識が身につきます。データ作成の時間短縮に繋がるため、業務でExcelを使っている方にはぜひ手に取っていただきたい1冊です。

木村 幸子 (著)
出版社: 翔泳社; 1版 (2018/6/18)、出典:出版社HP

 

はじめに

ピボットテーブルは、売上の一覧表などから商品分類、担当者、顧客といった項目を選んで集計表を作る機能です。計算式を設定しなくても合計や平均を求められる手軽さや、ドラッグするだけで表ができあがる操作性のよさが好まれ、Excelユーザーの間では、使いこなせるようになりたい機能として必ず上位に名前が入ります。

でも、いざ作ってみると、とりあえず合計を求めるところまではできるけれど、そこから先に進めないという人は意外に多いのです。そこで、本書では、「データ集計・分析の『引き出し』が増える本」と題して、ピボットテーブルで集計を自在に行うために知っておきたい、さまざまな操作やテクニックを幅広く解説しました。

まずは目次を見てください。ピボットテーブル作りの基本を見直したい方は、序章と第1章が役立つでしょう。なお、ピボットテーブルでは「リスト」と呼ばれる元表作りが肝心です。そのリスト作りのポイントを第2章で紹介しました。
ピボットテーブルに慣れている方は、知りたい内容に応じて、第3章以降のページをご覧ください。第3章から第5章は、集計の方法や、見出しを階層にするときのルール、集計結果を並べ替えたり抽出したりする方法を紹介しています。 続く第6章では、グラフなどピボットテーブルの内容を視覚化する際に役立つ機能を紹介しています。第7章では、ドリルダウンやダイス分析といったデータ分析の専門手法について解説しました。最後の第8章は、Accessデータと連携させたり、複数の表からピボットテーブルで集計するやや高度なテクニックを載せています。

本書が皆さんのピボットテーブル活用のさまざまな場面においてお役に立てば幸いです。
2018年6月 木村 幸子

木村 幸子 (著)
出版社: 翔泳社; 1版 (2018/6/18)、出典:出版社HP

CONTENTS

はじめに

ダウンロードファイルについて

序 章 これだけは知っておきたいピボットテーブルの仕組み
0-1 ピボットテーブルでできることとは?
0-1-1 大量のデータをすばやく分析できる
0-1-2 数式や関数を使わずに集計表を作成できる
0-2 ピボットテーブルの基本構成
0-2-1 ピボットテーブルの各部の名称
0-2-2 元の表(リスト)の各部の名称

第1章 集計の基本と定番パターン
1-1 ピボットテーブル作成の基本操作
1-1-1 ピボットテーブルの土台を作成する
1-1-2 集計に必要なフィールドを配置する
1-2 ピボットテーブルの基本形① 見出しが1方向の集計表
1-2-1 「単純集計表」を手軽に活用しよう
1-2-2 ピボットテーブルで「単純集計表」を作るには
1-3 ピボットテーブルの基本形② 見出しが2方向の集計表
1-3-1 ピボットテーブルの主流「クロス集計表」を覚えよう
1-3-2 ピボットテーブルで「クロス集計表」を作るには

第2章 データの不備をなくすには
2-1 リスト作りのルールを守っているか
2-1-1 理想的なリストのレイアウトを知る
2-1-2 空行を入れてはいけない場所を知る
2-1-3 セル結合は禁止
2-1-4 フィールド名は必ず入力する
2-2 入力データは統一されているか
2-2-1 商品名などの表現(言葉)を統一する
2-2-2 セル内の余分なスペースを削除する
2-2-3 半角と全角を統一する
2-2-4 リストには空欄のセルを残さない

第3章 集計の応用テクニックいろいろ
3-1 集計方法を「合計」以外に変更する
3-1-1 合計を「平均」や「最大値」に変更する
3-2 比率や累計、順位を求める
3-2-1 商品分類、販売エリアなどの構成比を求める
3-2-2 特定の商品分類などを基準にした売上比率を求める
3-2-3 前月比や前年比を求める
3-2-4 金額などを上から足して累計を求める
3-2-5 金額の高い順に順位を付ける
3-3 ピボットテーブルで計算式を作る
3-3-1 「消費税額」や「税込合計」を求める計算をする
3-3-2 行や列に集計用の項目を追加する
3-4 集計結果を見やすく表示するには
3-4-1 集計値に桁区切りのカンマを表示する
3-4-2 桁の大きな数値に単位を設定する(小さな桁を省略する)
3-4-3 項目見出しが簡潔になるように変更する
3-4-4 統計を省略してピボットテーブルをすっきり見せる
3-4-5 複数の集計方法の結果を表示する
3-5 集計値を最新の状態に更新する
3-5-1 リストの一部を変更したピボットテーブルを更新する
3-5-2 リストにレコードを追加後、ピボットテーブルを更新する
3-6 集計に関する便利機能を使う
3-6-1 集計元になった明細を別シートで確認する
3-6-2 一部の集計結果を別のセルに転記する

第 4 章 「階層」を使いこなして活用の幅を広げる
4-1 階層を理解する
4-1-1 階層とは
4-1-2 分析の主となるフィールドを階層の上位にしよう
4-2 行ラベル・列ラベルの見出しを階層構造にする
4-2-1 上位・下位にフィールドを追加する
4-2-2 上位と下位を入れ替える
4-2-3 日付フィールドは最下位レベルに追加する
4-3 階層構造のピボットテーブルを見やすく表示する
4-3-1 下位フィールドの合計を表の下にまとめて表示する
4-3-2 見出しや小計のレイアウトを変更する
4-3-3 小計の表示方法を変更する

第5章 ピボットテーブル分析の基本
5-1 データを並べ替える
5-1-1 合計金額の高い順に並べ替える
5-1-2 昇順や降順ではない順序で項目を並べ替える
5-1-3 名称ではなくコード順に並べ替える
5-1-4 独自に登録した順序で並べ替える
5-2 特定の行や列のデータを表示する
5-2-1 特定の文字を含むものを抽出する
5-2-2 特定の金額以上のものだけを抽出する
5-2-3 上位5位までを表示する
5-3 リストから特定のレコードを選んで集計する
5-3-1 特定の支社などの売上データだけを集計する
5-3-2 複数の条件を組み合わせてピボットテーブルを絞り込む
5-3-3 シートを分けて集計表を表示する
5-4 データをグループ化して表示する
5-4-1 日付を年、四半期、月単位でグループ化する
5-4-2 日数を指定してグループ化する
5-4-3 一定の件数にまとめてグループ化する
5-4-4 独自の分類名を付けてグループ化する
5-5 ピボットテーブルをわかりやすく印刷する
5-5-1 項目見出しをすべてのページに印刷する
5-5-2 項目が変わる位置で改ページする

第6章 分析に役立つ視覚化テクニック
6-1 ピボットテーブルをグラフにする
6-1-1 ピボットグラフとは
6-1-2 商品別の売上状況をピボットグラフで表す
6-1-3 ピボットグラフのレイアウトを変更して分析の視点を変える
6-1-4 グラフに表示する項目を絞り込む
6-1-5 横軸の階層表示をボタンで切り替える
6-1-6 グラフの種類を変更する
6-1-7 強調点が伝わるように色や数値を表示する
6-2 注目したいデータが一目でわかるように工夫する
6-2-1 指定した数値以上の場合に色を付ける
6-2-2 1位から3位までに色を付ける
6-2-3 平均値を下回ったものに色を付ける
6-3 集計値の傾向を簡易グラフやアイコンで表現する
6-3-1 数値の変化を簡易的な折れ線グラフで表示する
6-3-2 数値の大きさを簡易的な棒グラフで比較する
6-3-3 数値の大きさを横棒グラフで表示する
6-3-4 数値の大きさをアイコンでランク分けする
6-3-5 金額の大きさに応じてセルを塗り分ける
6-4 集計表のデザインを見やすく工夫する
6-4-1 ピボットテーブルのデザインを変更する
6-4-2 行を見間違えないように1行おきに縞模様を追加する
6-4-3 集計値の空欄に「0」を表示する

第7章 ここで差がつく!応用的な分析手法 (ケーススタディ)
7-1 データ分析の方法を理解しよう
7-1-1 データから問題の原因を探るポイント
7-2 ドリルダウンを活用する
7-2-1 特定のデータを掘り下げてゆく「ドリルダウン」
7-2-2 「商品分類」から「商品名」へと集計表を掘り下げる
7-3 ダイス分析を活用する
7-3-1 さまざまな視点から傾向を探る「ダイス分析」
7-3-2 「販売エリア」を軸にピボットテーブルを変形する
7-4 スライス分析を活用する
7-4-1 特定の部分に着目して集計表を切り出す「スライス分析」
7-4-2 特定の「支社」でピボットテーブルを切り出す
7-4-3 複数のフィールドを指定してさらに細かく分析する
7-4-4 時間軸で分析する「タイムライン」

第8章 データベースを作成してピボットテーブルを高度に活用する
8-1 Accessデータからピボットテーブルを作成する
8-1-1 Accessデータを取り込んでピボットテーブルを作成する
8-1-2 Accessデータとリンクしたピボットテーブルを作成する
8-2 複数の表に分かれたデータを集計する
8-2-1 複数の表からピボットテーブルを作成するには
8-2-2 リレーションシップについて理解する
8-2-3 テーブルを準備する
8-2-4 「リレーションシップ」でテーブル同士を関連付ける
8-2-5 複数テーブルからピボットテーブルを作成する

INDEX

《ダウンロードファイルについて》
・練習用Excelファイルをプレゼント
本書では、ピボットテーブルのさまざまな操作方法を解説しています。練習用のExcelファイルを無料で配布しているので、ぜひご活用ください。
・ダウンロード方法
①以下のサイトにアクセスしてください。
URL https://www.shoeisha.co.jp/book/present/9784798155784
②画面に従って必要事項を入力してください 。(無料の会員登録が必要です)
③表示されるリンクをクリックし、ダウンロードしてください。
・ファイルについて
上記の手順でダウンロードしたデータは、章ごとにフォルダが分けられています。書籍の見出し番号(1-1、2-1-1など)と共通のファイル名がついているので、操作を試してみたいファイルを選択し、利用してください。
※各ファイルは、Microsoft Excel 2016および2013で動作を確認しています。以前のバージョンでも利用できますが、一部機能が失われる可能性があります。
※各ファイルの著作権は著者が所有しています。許可なく配布したり、Webサイトに転載することはできません。

― 序 章 これだけは知っておきたいピボットテーブルの仕組み ―

0-1 ピボットテーブルでできることとは?
0-1-1 大量のデータをすばやく分析できる
「ピボットテーブル」とは、売上一覧表などのデータをもとに、集計表をすばやく作成できる機能です。ピボットテーブルを使えば、何千行もあるような膨大なデータであっても手間をかけずにすばやく集計表が作成できます。
【瞬時に大量データを集計できる】
ピボットテーブルを使うと、毎日蓄積される売上記録や販売データといった大量の情報をもとにして、傾向を把握したり、集計を求めたりする作業ができます。元データとなる表が数十行であっても、何千行を超えるような大きな表であっても、ピボットテーブルなら集計や作表にかかる時間は変わりません。手作業でまとめることを考えれば、あっという間に集計表を作ることができるのです。
図0-1では、日付、顧客名、担当支社や販売エリアなどを入力した日々の注文データをもとにして、2通りの集計表を作っています。このようなさまざまな集計表を、必要に応じてすばやく作成できるのがピボットテーブルです。
◎図0-1 大量データから複数の集計表をすばやく作成できる

【作成後の変更も瞬時にできる】
ピボットテーブルでは、一度作成した集計表の内容変更も瞬時に行うことができます。
【作成後の変更も瞬時にできる】
ピボットテーブルでは、一度作成した集計表の内容変更も瞬時に行うことができます。
たとえば、顧客名ごとに売上金額を集計していた表を、商品名ごとに集計するように内容を変更したい場合、図0-2のように、縦軸に配置する項目を顧客名から商品名へと入れ替えるだけで完了します。
◎図0-2 集計表のレイアウト変更も瞬時にできる

もちろん、縦軸の項目を変更すれば、対応する金額の合計もちゃんと正しい数値に変更されます。集計表を最初から作りなおす必要はありません。

木村 幸子 (著)
出版社: 翔泳社; 1版 (2018/6/18)、出典:出版社HP