これならできる Excel ピボットテーブル作成 超入門~仕事の現場で即使える

仕事の現場ですぐ使える!

ピボットテーブル作成における必要なデータのサンプル、また起こりやすいエラーについても詳しく述べられています。テクニックの具体的な活用の仕方もわかるので、Excel ピボットテーブルの入門書としておすすめです。

木村 幸子 (著)
出版社: 技術評論社 (2017/8/26)、出典:出版社HP

 

はじめに

Excelでマスターしたい機能はなんですかと質問すると、「ピボットテーブル」と答える方は大勢います。業務でExcelを使う人にとって、ピボットテーブルは、昔も今も、使いこなせるようになりたい憧れのツールのようですね。
ピボットテーブルは、日々の売上記録などのデータを元に、さまざまな集計をすばやく行うことができる大変便利な機能です。ただし、見よう見まねでいきなり使えるものではなく、構造や操作のルールをあらかじめ理解しておく必要があります。
そして、ピボットテーブル本体の操作方法と同じくらい重要なのが、その準備段階に当たる表の作り方です。つまり、常日頃から売上一覧表などのデータを正しく集計できる形で作っておくことがポイントで、ここをクリアしておかなければ、ピボットテーブルを作る段階でつまずいてしまうことを知らない方も少なくありません。

本書では、この下準備の部分からしっかり理解していただきたいという思いをこめて、ピボットテーブルの元になる表を整える方法から解説しました。ピボットテーブル作成の操作についても、初めてチャレンジする方のために、操作のコツと意味を丁寧に説明しています。また、うっかりやってしまいがちな間違いの例もできるだけ多く掲載しました。さらに最後のCHAPTER 8では、ピボットテーブルを使って実際に業務で行うデータ分析の例を体験していただけます。
「超入門」というタイトルのとおり、ページを読み進めながらCD-ROMのデータを使って手を動かしていくうちに、ピボットテーブルの使い方を無理なく身に付けていただけるような構成になっています。

あなたもこの機にピボットテーブルの学習を始めてみませんか。本書がピボットテーブルを基礎からマスターしたいと願う方のお役に立てば幸いです。
2017年7月末日
木村 幸子

木村 幸子 (著)
出版社: 技術評論社 (2017/8/26)、出典:出版社HP

本書の構成

CD-ROM の使い方

CHAPTER 1 ピボットテーブルを使うとなにができるのか

1-1 ピボットテーブル=クロス集計表
1-1-1 Excelの表はすべてが集計表ではない
1-1-2 集計表は知りたい数字がすぐにわかる表
1-1-3 クロス集計表は縦と横に見出しがある表

1-2 ピボットテーブルの構造
1-2-1 行見出し、列見出し、値の3つの領域がある
1-2-2 行見出しだけのピボットテーブルもある
1-2-3 列見出しだけのピボットテーブルは作らない

1-3 作成する前に想定しておく ~どういった集計データを求めたいのか
1-3-1 「集計の基準」と「集計したい数値」を決めておく
1-3-2 「集計の基準」と「集計したい数値」を具体例で考える
1-3-3 「集計の基準」、「集計したい数値」に指定できる内容とは

1-4 どんなシートでもピボットテーブルが作成できるわけではない
1-4-1 ピボットテーブルは表を元に作成するもの
1-4-2 ポイント① 同じ属性は1列に入力されているか
1-4-3 ポイント② 「集計の基準」と「集計したい数値」の両方があるか
1-4-4 ポイント③ 集計が行われていない状態の表であるか
1-4-5ポイント④ 項目見出しは列方向に配置されているか
1-4-6 ピボットテーブルの元表に求められるレイアウトとは

CHAPTER 2 Excelシートを整えよう

2-1 ピボットテーブル作成に、理想的なシート・難しいシート
2-1-1 ピボットテーブルのデータとして理想的な表とは
2-1-2 データ以外の内容は削除が必要
2-1-3 表現の統一を徹底する

2-2 1行目は見出しを、データは2行目から配置 ~オブジェクト等の削除
2-2-1 ピボットテーブルに必要なのはデータのみ
2-2-2 1行目にフィールド名、2行目以降にデータ
2-2-3 オブジェクトを削除する
2-2-4 コメントを削除する
2-2-5 1つのシートに1つの表に、仕上げる

2-3 書式はすべて削除する~データ以外は不要
2-3-1 書式は余計な飾りにすぎない
2-3-2 全ての書式を一括で削除する
2-3-3 日付のセルに書式を再設定する
2-3-4 結果を確認する

2-4 計算で求めるフィールドを作っておく
2-4-1 集計に使う数値は元表にも必要
2-4-2 元表に「売上金額」フィールドを作る
2-4-3 フィールドどうしの「横の計算」は元表に追加が必要
2-4-4 理想の表ができたことを確認する

CHAPTER 3 データを統一しよう

3-1 並べ替えとフィルターで状況を把握する
3-1-1 なぜデータの統一が必要なのか?
3-1-2 「並べ替え」の前に連番フィールドを作っておく
3-1-3 「並べ替え」を使ってデータの不統一を見つける
3-1-4 「フィルター」を使う準備をする
3-1-5 「フィルター」で効率よくデータを統一する

3-2 置換機能でデータを統一する
3-2-1 置換機能でデータを統一する
3-2-2 置換を実行する
3-2-3 セル内の余分なスペースを削除する
3-2-4 特定の文字の全角と半角を変換する

3-3 関数でデータを統一する
3-3-1 表現の統一に役立つ関数を知っておく
3-3-2 全角と半角を変換するJIS関数・ASC関数
3-3-3 入力した関数の式を下のセルにコピーする
3-3-4 関数で求めた結果をフィールドとして使う
3-3-5 大文字・小文字を変換するUPPER関数・LOWER関数
3-3-6 特定の言葉を置き換えるSUBSTITUTE関数
3-3-7 余分な文字を一括削除するTRIM関数

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 データが統一されたことを確認する

CHAPTER 4 ピボットテーブルを作成しよう

4-1 空のピボットテーブルを作成する ~クリック3回で作成
4-1-1 元表をもう一度確認しよう
4-1-2 ピボットテーブルの土台を作成する
4-1-3 元表とは別のシートに作られる
4-1-4 ピボットテーブルが新規シートに作成されるメリット(参考
4-1-5 ピボットテーブルの土台は2つのエリアに分かれる

4-2 「ピボットテーブルのフィールド」 作業ウィンドウを理解する
4-2-1 「ピボットテーブルのフィールド」作業ウィンドウを表示する
4-2-2 「フィールドセクション」とは?
4-2-3 「エリアセクション」とは?
4-2-4 エリアセクションにはフィールドを配置する

4-3 集計したい要素を洗い出す
4-3-1 学習用サンプルデータの内容について
4-3-2 作成する集計表のレイアウトを決める
4-3-3 「なにを集計したいのか」を考える
4-3-4 2つの領域に指定するフィールドを考える

4-4 行のラベルを設定する ~「行」ボックスへドラッグ&ドロップ
4-4-1 ピボットテーブルに商品名の一覧を表示する
4-4-2 行見出しをピボットテーブルに表示する
4-4-3 行見出しに商品名が表示された
4-4-4 行ラベルとは行見出しのこと
4-4-5 なぜ「行」ボックスにドラッグするのか?
4-4-6 ピボットテーブルに指定したフィールドにはチェックが付く

4-5 クロス集計する ~「ど値」ボックスへドラッグ&ドロップ
4-5-1 集計結果の欄に売上金額の合計を求める
4-5-2 集計結果をピボットテーブルに表示する
4-5-3 集計結果の欄に売上金額の合計が表示された
4-5-4 「差値」ボックスへドラッグするだけで合計が表示
4-5-5 集計表が完成した

4-6 失敗したら ~フィールドの削除方法
4-6-1 追加するフィールドを間違えた場合
4-6-2 フィールドの追加先ボックスを間違えた場合

4-7 「分析」「デザイン」タブの設定を行う
4-7-1 完成したピボットテーブルを確認する
4-7-2 ピボットテーブルの編集に使うタブを表示する
4-7-3 「分析」タブのボタンを使ってみる
4-7-4 「デザイン」タブのボタンを使ってみる

CHAPTER 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-2-4 上位、下位を間違えたら
5-2-5 一部の下位フィールドを個別に折りたたむ
5-2-6 下位フィールド全体を折りたたむ

5-3 日付フィールドを行に追加する ~「グループ化」
5-3-1 日付のデータを行見出しに追加する
5-3-2 「日付」フィールドを行ラベルに追加する
5-3-3 バラバラの日付をまとめる「グループ化」とは
5-3-4 自動的に「年」「四半期」「月」でグループ化される
5-3-5 四半期や月単位の見出しを表示する
5-3-6 日付のグループ化を解除してみる
5-3-7 手動で日付をグループ化する
5-3-8 完成した行見出しを確認する

5-4 階層の上下を入れ替える ~集計元の理解
5-4-1 現在の階層を確認する
5-4-2 階層の上下を入れ替える
5-4-3 行ラベルの見出しを入れ替える
5-4-4 日付は最下位に置くのが望ましい(参考)

5-5 見出しや集計された数値を変更する ~「値フィールドの設定」
5-5-1 ピボットテーブルの集計値を見やすく設定する
5-5-2 「値フィールドの設定」ウィンドウを表示する
5-5-3 小見出しを変更する
5-5-4 表示形式で数値の桁をわかりやすく表示する

CHAPTER 6 「列」と「差値」を理解しよう

6-1 作成したいテーブルの構造を再考する ~行・列双方に見出し
6-1-1 行見出しが3階層だと縦長になる
6-1-2 クロス集計表でも、集計の結果は変わらない
6-1-3 複数の集計結果を表示したい
6-1-4 フィールドを使ってオリジナルの計算式を作ってみる

6-2 Σ値を増やす ~原価と売上を集計する
6-2-1 現在のピボットテーブルを確認する
6-2-2 「売上原価」の合計を求める流れを確認する
6-2-3 元表に「売上原価」のフィールドを作る
6-2-4 ピボットテーブルは自動で更新されない
6-2-5 ピボットテーブルを最新状態に更新する
6-2-6 「売上原価」を「差値」ボックスに追加する
6-2-7 値フィールドの小見出しや数値の表示を変更する

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 列見出しも階層構造になる
6-4-3 列ラベルの階層を入れ替える
6-4-4 列ラベルの階層を元に戻す
6-4-5 集計が1つだけの場合は階層にならない(参考)

6-5 集計方法を変更する ~「値フィールド」の「計算の種類」
6-5-1 「合計」以外の集計も設定できる
6-5-2 「売上金額」フィールドの「個数」を求める
6-5-3 集計の種類を変更する
6-5-4 「数量」の平均を追加する
6-5-5 平均の小数部分を切り捨てて整数で表示する
6-5-6 属性のフィールドを「差値」に追加した場合(参考)

6-6 オリジナルの計算式を用いる ~「集計フィールドの挿入」
6-6-1 計算式で求める集計をピボットテーブルに追加する
6-6-2 「税込金額」を計算するための集計フィールドを追加する
6-6-3 「粗利益」を求める集計フィールドを追加する
6-6-4 小見出しを簡潔にする

6-7 ピボットテーブルを再計算する
6-7-1 基本は右クリックで「更新」
6-7-2 元表にデータを追加した場合は、データソースを拡張する

CHAPTER 7 条件を付けてデータを抽出しよう

7-1 フィルターを使って抽出する ~「ラベルフィルター」
7-1-1 ピボットテーブルの集計結果を抽出する
7-1-2 特定の商品の集計結果を抽出する
7-1-3 「商品名」フィールドのフィルターを解除する
7-1-4 「2016年」と「2017年」のデータを抽出する
7-1-5 列ラベルでも「ラベルフィルター」を設定できる
7-1-6 すべてのフィルターを一括でクリアする
7-1-7 「ワイルドカード」を使って商品名の一部などで抽出する

7-2 集計結果の数値を元に抽出する ~「値フィルター」
7-2-1 粗利益が150万円以上の年を抽出する
7-2-2 列ラベルから「担当者」を削除しておく
7-2-3 フィルターを設定するフィールドと比較の方法を選ぶ
7-2-4 条件判定に使う集計を選び、基準となる数値を指定する
7-2-5 「値フィルター」の抽出結果が表示された

7-3 集計対象となる範囲を抽出する ~「レポートフィルター」
7-3-1 ピボットテーブルで集計する元データを抽出する
7-3-2 レポートフィルターを設定する
7-3-3 レポートフィルターで抽出する
7-3-4 レポートフィルターの抽出を解除する
7-3-5 複数の項目で抽出する

7-4 スライサーを使って抽出する
7-4-1 「スライサー」はピボットテーブルから独立した抽出ツール
7-4-2 レポートフィルターを削除する
7-4-3 スライサーを表示する
7-4-4 スライサーを使って抽出する
7-4-5 スライサーの抽出を解除する
7-4-6 複数の項目で抽出する

7-5 複数のスライサーを同時に使う
7-5-1 複数のスライサーで抽出する
7-5-2 「分類」のスライサーを追加する
7-5-3 「分類」が「ボールペン」である商品を抽出する
7-5-4 スライサーを削除する

7-6 タイムラインを使って抽出する
7-6-1 「タイムライン」で集計対象の期間を抽出する
7-6-2 タイムラインをシートに追加する
7-6-3 タイムラインを使って抽出する
7-6-4 年や四半期単位で抽出する
7-6-5 抽出を解除して、タイムラインを削除する
7-6-6 行ラベルのフィルターで期間を抽出する(参考)

CHAPTER 8 ピボットテーブルを使って実践的なデータ分析をしよう

8-1 問題点を把握して仮説を立てる
8-1-1 ピボットテーブルを使った分析を体験する
8-1-2 「売上金額」の高い順に商品を並べ替える
8-1-3 「数量」の合計をピボットテーブルに追加する
8-1-4 「定価」の平均をピボットテーブルに追加する
8-1-5 販売数や定価を確認する
8-1-6 機能性ボールペンと「ボールペン(黒)」の相関関係を考える

8-2 得意先別に販売数を比較 ~「レポートフィルターページ」の表示
8-2-1 販売数の多い順にピボットテーブルを並べ替える
8-2-2 レポートフィルターに「得意先」を設定する
8-2-3 「レポートフィルターページ」を作成する
8-2-4 得意先ごとに集計結果を比較する

8-3 気になるデータの詳細を次々に展開 ~「ドリルダウン」の活用
8-3-1 ダブルクリックで詳細を展開する「ドリルダウン」
8-3-2 最も販売数の多い顧客「F社」をさらに展開
8-3-3 ドリルダウンで「ボールペン(黒)」の得意先を展開する
8-3-4 集計の元データを別シートに展開する(参考)

8-4 売上推移を視覚的に検証 ~「ピボットグラフ」の利用
8-4-1 ピボットグラフとは
8-4-2 グラフの元になるピボットテーブルを作成する
8-4-3 「商品名」でボールペン関連の商品を抽出する
8-4-4 任意の「得意先」フィールドで抽出する
8-4-5 列ラベルを「年」と「月」の2段表示にする
8-4-6 ピボットグラフを作成する
8-4-7 「年」と「月」をグラフの横軸に表示する
8-4-8 縦軸の最大値と目盛り間隔を固定にする
8-4-9 「A社」のデータが仮説を満たすかどうかを確認する
8-4-10 ほかの得意先の折れ線グラフを表示する

索引

《本書の構成》
本書は Excelのピボットテーブルを自在に使いこなすことを目的としています。ピボットテーブルを作成する前にExcelのデータを整理することから、実際の作成、さらにはピボットテーブルを使ったデータ分析およびピボットグラフの活用まで、1冊すべてがピボットテーブルに関する書籍です。

CHAPTER 1
ピボットテーブルを作成するために必要なデータはどういったものか、など前提となる知識を解説
CHAPTER 2
ピボットテーブルを作成することができるExcelシートについて解説
CHAPTER 3
Excelシート内のデータを統一しておくことでピボットテーブルによる正しい集計ができることを解説
CHAPTER 4
ピボットテーブルの作成を確実に行うための方法を解説
CHAPTER 5
「担当者」別、「商品名」別のように複数の階層で集計する方法について解説
CHAPTER 6
単純な合計以外のさまざまな集計方法について解説
CHAPTER 7
ピボットテーブルから必要なデータを抽出する方法を解説
CHAPTER 8
仮説の構築からピボットグラフを使った分析まで、ピボットテーブルによる分析方法を解説

確実にピボットテーブルを作成したいという目的ならば、CHAPTER4までを学習してください。 ピボットテーブルの機能全般を利用したいという目的ならば、CHAPTER 7までを学習してください。さらに一歩進んで分析をしてみたいという目的ならば、CHAPTER8までを学習してください。
なお、解説の都合上、本書内に掲載している画面は、紹介した操作をすべて順番ごとに行った結果でないこともあります。そのため、ご自身の操作によっては、ご自身の結果画面と本書内に掲載している画面が微妙に異なることがあります。

木村 幸子 (著)
出版社: 技術評論社 (2017/8/26)、出典:出版社HP