【最新】EXCELピボットテーブル本 – 集計、抽出、分析が学べるおすすめ書籍

コンテンツ 非表示

ピボットテーブルをマスターしよう

基本的な集計の業務からビックデータによる大量データの処理など、これまで以上にエクセルにおける集計から分析までのスキルが必要になっています。ピボットテーブルの使い方を覚え、実行できるようになるとこれまで使っていた関数での作業よりも速く集計表を作ることができたりもします。また、色々あるエクセルの性能の中で、次はピボットテーブルをマスターしたいという方にもおすすめのものとなります。

ランキングを確認する
出典:出版社HP

目次 – Excel最強集計術 〜現場で効率アップできる本当に正しい使い方

もっと効率よくエクセル集計

説明も丁寧で分かりやすく、初心者の方でも理解できるようになっています。他の参考書を読んでもよくわからなかった、またExcel集計の本で悩んでいる方にはこちらをおすすめします。より効率良く集計をすることができるようになります。

不二桜 (著)
出版社: 技術評論社 (2020/2/21)、出典:出版社HP

 

はじめに

そのやり方、本当に Excelの集計を正しく 使いこなせていますか?
Excelで入力したデータや、取り込みしたデータをもとに集計をしなければならなくなった時、まずは合計や平均などの基本的な計算は必須となります。これらの基本的な計算は、数式を作成したことがなくても、Excelでは関数を使えばかんたんに求められます。
関数とはExcelに備わっている集計機能の1つです。たとえば、合計や平均なら、足し算や割り算の数式を入力しなくても、関数を使えば、入力したデータを範囲選択するだけで、驚くべきスピードで求められます。

「そんな基本的な関数さえ知っておけばなんでも集計できる」ならいいのですが、業務内容やほしい分析資料によって、要求される集計表の内容・形はさまざまで、実際はさまざまな集計が必要になります。
たとえば、大量のデータを1件ずつ入力した表をもとに、項目ごとの小計枠を付けた集計表を資料として提出しなければならない場合もあります。そんな時、関数しか知らなかったら、「すべての項目ごとに小計枠を挿入→それぞれの小計行に関数を入力」
こんな作業が必要です。1か月分のデータに日別の小計が必要になったなら、日ごとに小計枠を挿入することになるので、思わぬ時間を費やすはめになってしまいます。

また、1つのシートだけではなく、複数のシートやブックでデータを入力していたなら、そのデータをまとめて集計しなければならない場合もあります。1年分のシートなら、1つのシートにまとめる操作を、コピー&貼り付けしか知らなかったなら、12回コピー&貼り付けをくり返すことになってしまいます。

しかし、Excelは表計算ソフトという名のとおり、さまざまな集計機能が備わっています。そのため、かんたんな関数しか知らなくて対応できない集計や、一刻も早くといったスピードを要求される集計でも、使える集計機能の使い方をマスターしておけば安心です。このあとの本編では、豊富に用意されている集計機能をどんな時にどう使えば、ほしい集計表がスピーディーに作成できるのか、じっくりと解説していきます。

【1つの集計機能に固執してはダメ】
ただ、本書ではこれらの集計機能の使い方を1つ1つ完璧にマスターするところまでを到達点としてはいません。当然のことながら、業務内容 やほしい分析資料によって、要求される集計表の内容、形はさまざまです。
それゆえ、本を読んである集計機能だけをマスターしても、無理にその集計機能を使おうとすると余計な時間がかかってしまうことがあります。
たとえば、以下の図のように、入力した会員の住所をもとに都道府県別の件数を表に求めたいとき、ちょっとExcelの知識がある方であれば、次のように集計する方法が思い浮かぶでしょう。

・項目別集計ができるからピボットテーブルを使う?
→しかし、住所別にしか集計できないからダメ
・都道府県で抽出できるからフィルターを使える
→しかし1つずつ 件数入力が大変……
関数以外にここで出てきた集計機能は、ピボットテーブル、フィルターです。よくあるExcel本を読んでみると、ピボットテーブルやフィルターを使ったほうがかんたんでパッとできそうに見えます。
ですが、実際は、以下のようにCOUNTIF関数1つでできてしまうのです。この数式の詳細は、第4章で解説しています。

誰でもかんたんに集計できるほうがいいに決まっています。しかし、かんたんにできる機能や覚えた機能だけをとりあえず使って集計すると、このように余計に時間がかかってしまう場合もあるのです。
集計表をスピーディーに作成するには、「こんな時はこの集計機能を使えばいい」と目的別に集計機能を頭の中で上手に振り分けられるようになり、1つの方法でできなくても「この集計機能がダメならこっちの集計機能で求められる!」と、臨機応変に対応できるようなることが大 事です。そうなれば、目的の集計表をスピーディーに完成させることができるはずです。本書では、そんな適切な使いこなしを身に付けられるように各集計機能を説明します。
つまり、本書は「この機能を使えばどんな集計でもできる!」という本ではありません。あくまでも臨機応変に使い分け、どんな集計表を頼まれても、「パッと」完成できるように手助けする本です。
ただ、集計どころか、そもそも数式や関数の入力・編集について自信がない場合は、序章をしっかりマスターして本章へ入りましょう。もちろん、数式や関数の基礎知識はばっちりであれば、ここから先を読み飛ばして本章を読み進めてください。

不二桜 (著)
出版社: 技術評論社 (2020/2/21)、出典:出版社HP

CONTENTS

はじめに
そのやり方、本当にExcelの集計を正しく使いこなせていますか?

PROLOGUE 集計するための機能を使う前に
序-1 数式と関数の基本を押さえておこう
1 数式の基本操作
2 関数の基本操作
序-2 データ集計がしやすい表を作成するには

CHAPTER 1 まずはこれだけ押さえよう ~最速集計の「基本のキ」
1-1 ドラッグするだけで計算結果がわかる –
1 数式無しで集計値は把握できる
2 クイック分析ボタンを使えば集計値はプレビューできる
1-2 複数セルの計算を最速にする「コピペ」の基本
1 同じ数値を複数セルに四則演算する最速テク
1-3 基本集計は「合計ボタン」を使って計算をスピードアップしよう
1 関数の入力が面倒だと思ったら「合計ボタン」を使おう
2 5関数+aでできる集計を一緒に覚えよう

CHAPTER 2 フィルターや行の非表示で 隠れた行を除いて集計する
2-1 表の最終行に非表示セルを除く集計値を求めよう
1 表の最終行に非表示セルを除く集計値を求めるには「テーブル」を使おう
2 抽出した集計値が求められるテーブルのしくみ
2-2 表とは違うセルに非表示セルを除く集計値を求めよう
1 表とは違うセルに非表示セルを除く集計値を求めるには
2 SUBTOTAL関数ではできない集計内容なら「AGGREGATE関数」を使う

CHAPTER 3 めんどうな小計処理をスピードアップする
3-1 決められた「小計」枠に計算結果を一発で表示する
1 小計は「合計ボタン」+「Ctrl」キーを使おう
3-2 「小計」枠がなければ、枠作り&計算を同時に処理しよう
1 「集計」を使えば小計枠と計算の追加が同時におこなえる
2 複数の集計方法はその数だけ「集計」を使おう
3 SUBTOTAL関数でできない小計を除く集計はAGGREGATE関数を使う
4 大分類/小分類の小計は階層順に「並べ替え」をしよう
5 追加した小計を活かそう

CHAPTER 4 条件付き集計をマスターする! ~条件付き集計できる関数を正しく使おう
4-1 計算したい条件は1つ?複数?正しく関数を使いこなそう
1 条件付き集計は「もしも~ならば」の英語名の関数を使おう
2 「合計」や「件数」のOR条件は足し算でできる
3 アプリみたいに条件を直接入力して計算しよう
4-2 指定できない条件はこうしよう
1 一部の条件はワイルドカードを使おう
2 指定できない条件は印を入力しよう
4-3 「$」記号を使いこなそう
1 条件ごとにまとめて集計するにはセル参照と絶対参照を組み合わせよう
2 行列見出しの条件に一致する値を条件ごとにまとめて集計するには複合参照を使おう
3 絶対参照と相対参照の組み合わせでできること
4 重複は同じデータの数を条件に集計しよう
4-4 使える条件付き関数がない!でも必要なら配列数式にチャレンジしてみよう
1 配列数式のしくみを知れば作成は難しくない
2 配列数式でAND条件OR条件を満たす集計をおこなうには
4-5 テーブルや名前の長所を活かそう
1 テーブルを使って、データの追加を数式に自動反映させよう
2 どんな内容なのかわかる数式を作成するには
3 テーブルでは不都合な表は名前を使ってみよう

CHAPTER 5 ドラッグ操作でかんたんに集計表を作ろう
5-1 ピボットテーブルで項目別集計をスピードアップ
1 ピボットテーブルを使えばこんなに早い!
5-2 ピボットテーブルを希望の形に変更しよう
1 ピボットテーブルのフィールド名やレイアウトを変更しよう
2 アイテムや集計値の並びを変更しよう
3 ラベル・アイテム・集計値を希望の表示に変えよう
4 小計や総計の表示を希望どおりに変更しよう
5 元のデータの変更や追加が反映できるピボットテーブルにするには
6 ピボットテーブルの既定のレイアウトやスタイルをなんとかしたいときは
7 ピボットテーブルの集計値をリンクさせる
5-3 ピボットテーブルを必要な集計内容に変更しよう
1 集計値を必要な内容に変更しよう
2 集計値をもとに計算したフィールドを追加しよう
3 アイテムを使った集計行を追加しよう
5-4 条件で抽出した集計表にしよう
1 行ラベル・列ラベル・総計値を条件で抽出した集計表にしよう
2 集計対象のデータで抽出した集計表にしよう

CHAPTER 6 グループごとの集計を高速化するコツ
6-1 単位を指定するだけでグループ集計する
1 関連する項目をまとめてグループ集計する
2 数値データを指定の単位でまとめてグループ集計する
3 日付や時刻データをグループ集計する
6-2 こんなグループ集計なら関数の力を借りよう
1 違う単位でグループ集計する
2 ピボットテーブルでできない日付時刻データのグループ集計をおこなう
6-3 ピボットテーブル+関数でできないグループ集計を可能にする
1 互いの長所を活かして望みのグループ集計表を完成させる
2 集計アイテムで利用するならグループ化は関数でおこなおう

CHAPTER 7 シートやブックをまたいでいくつもの表を合体して集計するコツ
7-1 複数のシートやブックの表を集計しよう
1 複数のシートやブックのデータを集計しよう
2 行列見出しが違う表を統合して集計しよう
3 1つに集計したクロス表をシートごとに切り替えられるようにしよう
7-2 共通のフィールドを関連付けて1つの集計表を作成しよう
1 複数の表の共通のフィールドを関連付けて1つの集計表を作成しよう
2 リレーションシップが使えないときはVLOOKUP関数を使おう
7-3 複数のシートやブックの条件集計
1 複数シートのデータそのままで条件集計するなら関数を使おう
2 複数シートやブックを1つのシートにまとめてしまえば条件集計はかんたん!
3 PowerQueryで複数シートやブックを1つのシートにまとめる
4 PowerQueryを使えば複数ブックやシートの共通のフィールドをキーに1つにまとめられる

不二桜 (著)
出版社: 技術評論社 (2020/2/21)、出典:出版社HP

 

目次 – Excel 集計・抽出テクニック大全集 ~あらゆる種類の表に対応、引くだけで一発解決

索引から調べてすぐできる!

Excel VBAがある程度できても、PowerQueryやPowerPivotには手付かずという方は少なくありません。ですが本書は、実際のサンプルデータを用いて解説されており、かなり分かりやすいのでスラスラ読み進めることができます。あっという間にスキルアップできるような1冊です。

はじめに

表計算ソフトであるExcelには、数式を作成しなくてもデータを集計したり、抽出したりする機能が備わっています。
しかし、目の前にあるデータを集計したり抽出したりしなければならなくなったとき、使えるはずの機能なのに、上手く使えないとイライラは募るばかりです。

上手く使えないのは、私達が集計・抽出したい表の形が、業務の内容によって異なり、マニュアルのような形でないことが原因です。
そんな業務によって異なる形をした表に、このセルに集計値がほしい、こんな抽出をしたいといっても、使い方の知識だけではどうにもなりません。

そこで本書では、集計や抽出を行う前の表を「いまの状態」とし、「目指す状態」の表にするためのあらゆる集計・抽出テクニックをTips形式でまとめてみました。
それぞれのTipsは、集計や抽出の機能、関数を使ったテクニックです。ただ、もちろん、「目指す状態」の表にするためには、それぞれの機能だけではできない場合もあります。

ですが、ご安心下さい! 機能の限界は、
・集計・抽出の機能 + 関数
・関数+ 違う関数、
といった、組み合わせで超えることができるのです。 本書では、このような組み合わせのTipsをふんだんに盛り込んでいます。

今まで、できなくて諦めていた集計・抽出…諦める前にぜひ、本書を開いて見て下さい。
刻々と迫る資料の提出期限、焦っても進まないとき…パニックになる前にぜひ、本書を開いて見て下さい。
複雑な数式でも、サンプルはダウンロードできますので、そのままコピー&貼り付けで利用できます、つまり、最初から作成する手間は不要です。
最後になりましたが、本書の出版にあたり、大変なご尽力を頂いた、技術評論社 書籍編集部の神山真紀様に、この場をお借りして心より厚くお礼申し上げます。
2017年11月 不二桜

目次

集計編

第1章 数式が苦手なら、集計できる機能を使いこなせ!
1-1大量データでも使い方は簡単!集計できる機能を使いこなせ!
テク001 選択したセル範囲の集計値を今すぐ知りたい
テク002 選択したセル範囲の集計値をクリック操作だけで求めたい
テク003 さまざまな集計方法での集計値をボタン1つで切り替えたい
テク004 項目ごとに小計行を挿入したい
テク005 項目ごとに合計と平均の小計行をまとめて挿入したい
テク006 行・列の数が異なる複数の表のデータを集計し、1つの表に統合したい
テク007 複数のブックに分かれている表のデータを集計し、1つの表に統合したい
テク008 表にデータを貼り付けると同時に四則演算したい

第2章 このセルに 集計値がほしい!基本の集計や小計を完全制覇!
2-1 基本の集計は「合計」ボタンで完全制覇!
テク009 集計範囲を自動的に選択して集計値を求めたい
テク010 ワンクリックで集計値を求めたい
テク011 数式コピーを使わずに、表の縦計と横計をまとめて求めたい
テク012 表の上端や左端に複数の行/列の合計欄がある場合に、手早く集計値を求めたい
テク013 離れたセルや複数の表に分かれているデータの集計値を求めたい
テク014 2列に並ぶデータを行ごとに乗算し、その合計を求めたい
テク015 データが増えても集計範囲を自動的に変更して離れたセルに集計したい
2-2 扱いにくい小計を完全制覇!
テク016 表内にある小計だけを一瞬で合計したい

テク017 小計欄がデータの上の行にある表でも小計だけを一瞬で合計したい
テク018 表内にある小計を除いて平均や最大値を求めたい
テク019 小計と総計を同時に求めたい
テク020 一定の行数ごとにデータの小計を求め、結果は右の列に入力したい
テク021 特定の曜日ごとにデータの小計を求め、結果は右の列に入力したい
テクO22 項目ごとにデータの小計を求め、結果は右の列に入力したい
テク023 項目ごとにデータの最大値を求め、結果は右の列に入力したい
テク024 項目ごとにデータの最小値を求め、結果は右の列に入力したい
テク025 一定の日にちごとにデータの小計を求め、結果は右の列に入力したい
テク026 項目と関連のある特定のルールごとにデータの小計を挿入したい
2-3 複数シートの集計を完全制覇!
テク027 複数のシートの違う行(または列)の表のデータを1つのセルに集計したい
テク028 複数のシートの同じ位置に同じ形式の表がある場合、各データを別表に集計したいテク029 複数のシートの同じ位置に同じ形式の表がある場合、
表内の文字列を別表にカウントしたい
テク030 複数のシートの表のデータを別表に集計するとき、
対象のシートを変更できるようにしたい

第3章 数式が苦手なら、 条件付き集計はピボットテーブル/統合/テーブル機能を使え!
3-1 数式を使わずに条件付き集計を可能にする!
テク031 ドラッグ操作だけで項目ごとに集計したい
テク032 ドラッグ操作だけで項目ごとの合計・平均・件数をまとめて求めたい
テク033 項目ごとの集計表に、独自の計算方法の集計列を追加したい
テク034 ピボットテーブルや数式を使わず、項目ごとに集計したい
テク035 別表の行・列見出しに必要な項目名を入力し、対応するデータだけ集計したい
テク036 オートフィルターで抽出したデータの最終行に集計行を追加したい
3-2 使い方をマスターすれば複数条件での集計も数式を使わずにできる!
テク037 ドラッグ操作だけでクロス集計したい

テク038 大項目と小項目で構成された階層見出しの表に手早く集計したい
テク039 ピボットテーブルを使わず、
大項目と小項目で構成された階層見出しの表に集計したい
テク040 項目ごとの集計表を条件のリストを見ながら切り替えたい
テク041 項目ごとの集計表を日付の期間を見ながら切り替えたい
テク042 項目を指定の単位でグループ化して、グループごとに集計したい
3-3 複数の表/シート/ブックの条件付き集計もこんなに簡単!
テク043 項目ごとの集計表で、別シートにある項目も見出しにしたい
テク044 ピボットテーブルで、複数のシートにある表を項目ごとに集計したい
テク045 一瞬で項目ごとのシートに分割して項目ごとに集計したい
テク046 ピボットテーブルで、複数のシートにある表をクロス集計したい

第4章 どんな条件でも関数を使えば無敵!条件付き集計を完全マスター!
4-1 条件付き集計を完全マスター!
テク047 条件に一致する項目を集計したい(合計/平均/件数)
テク048 条件に一致する項目を集計したい(最大/最小)
テク049 条件に一致する項目を集計したい(関数がない場合)
テク050 条件に一致する項目の集計を、各項目まとめて手早く行いたい
テク051 複数ある条件のすべてに一致する項目を集計したい(合計/平均/件数)
テク052 複数ある条件のすべてに一致する項目を集計したい(最大/最小)
テク053 複数ある条件のすべてに一致する項目を集計したい(関数がない場合)
テク054 複数ある条件のいずれかに一致する項目を集計したい(合計/件数)
テク055 複数ある条件のいずれかに一致する項目を集計したい(最大/最小)
テク056 複数ある条件のいずれかに一致する項目を集計したい(関数がない場合)
テク057 「すべてに一致」と「いずれかに一致」を組み合わせた複数の条件で、
項目を集計したい

テク058 セルに入力した文字列が含まれる項目を集計したい
テク059 セルに入力した数値が演算子と組み合わさって条件になるようにし、集計したい
テク060 「~以上~以下」を条件に集計したい(合計/平均/件数)
テク061 同じ項目名は2行目以降に表示されない表で条件に一致する項目を集計したい
テク062 条件に一致する項目の結合セルを数えたい
テク063 集計範囲が複数列にわたる表で条件に一致する項目を集計したい(合計)
テク064 集計範囲が複数列にわたる表で
条件に一致する項目を集計したい(その他の集計方法)
テク065 集計範囲が複数に分かれているとき、条件に一致する項目を集計したい
4-2 非表示の行/列を除く集計を完全マスター!
テク066 オートフィルターや行の非表示で隠れたデータを除き、集計したい
テク067 列の非表示で隠れたデータを除き、手早く集計したい
テク068 オートフィルターや行の非表示で隠れたデータを除き、累計を求めたい
テク069 オートフィルターや行の非表示で隠れたデータを除き、項目ごとに集計したい

4-3 データの重複を条件にした集計を完全マスター!
テク070 重複データを除いた件数/重複データの件数を数えたい
テク071 データが複数の行・列にある場合に、
重複を除いて数えたい(表に空白を含まない場合)
テク072 データが複数の行・列にある場合に、重複を除いて数えたい(表に空白を含む場合) テク073 複数の条件で重複しているデータを除いた件数を数えたい
テク074 複数の条件で重複しているデータを除いた件数を、項目ごとに数えたい
テク075 オートフィルターや行の非表示で隠れたデータを除き、
重複データを除いた件数を数えたい
テク076 集計範囲に別表を含めるとき、重複データを除く件数を数えたい
テク077 集計範囲に複数のシートを含めるとき、重複データを除く件数を数えたい
テク078 集計範囲に別のブックを含めるとき、重複データを除く件数を数えたい
4-4 その他のあらゆる条件付き集計を完全マスター!
テク079 数式のエラー値を除いて集計したい
テク080 オートフィルターや行の非表示で隠れたデータと、
数式のエラー値を除いて集計したい
テク081 数式のエラー値を除いて手早く集計したい
テク082 色をつけたデータの集計値を、表とは別のセルに求めたい
テク083「~以下」の形式で区切られた別表に、各区間の該当データの件数を集計したい
テク084「~以上」の形式で区切られた別表に、各区間の該当データの件数を集計したい
テク085「~以上」の形式で列並びで区切られた別表に、
各区間の該当データの件数を集計したい
テク086「~以上」の形式で区切られた別表に、各区間の該当データの合計/平均を求めたいテク087「~以上」の形式で区切られた別表に、
各区間の該当データをその他の方法で集計したい
テク088「~以上」「~以下」の2列で区切られた表から、
区間内の各データに該当するデータの件数を集計したい
テク089 別表の行・列見出しに項目名を入力し、対応のデータをクロス集計したい
テク090別表の行・列見出しに項目名を入力し、
対応のデータをデータテーブルでクロス集計したい
テク091 ○行おきや○列おきに集計したい
テク092 データが連続している表で、
1つ前のデータより増加/減少しているデータの件数を数えたい
テク093 前列のデータより増加/減少しているデータの件数を数えたい
テク094 データが増えても集計範囲を自動的に変更して、
条件に一致する項目を集計したい(テーブル変換可能な場合)
テク095 データが増えても集計範囲を自動的に変更して、
条件に一致する項目を集計したい(テーブル変換不可の場合)
テク096 最終データから○件目までなど、直近○件を条件に集計したい
4-5 複数の表/シート/ブックでの条件付き集計を完全マスター!
テク097 別表の行・列見出しに項目名を入力し、
複数のシートからデータをクロス集計したい
テク098 シート名を見出しにした別表に、複数のシートからデータを集計したい
テク099 シート名と項目名を行・列見出しにした別表に、
複数のシートからデータをクロス集計したい
テク100 「~以上」の形式で区切られた別表に、
複数のシートから各区間のデータを集計したい
テク101 別のブックにあるデータから条件に一致する項目を集計したい
テク102 複数のブックにあるデータから条件に一致する項目を集計したい
テク103 ブック名と項目名を行・列見出しにした別表に、
複数のブックからデータをクロス集計したい

第5章 今日から足し算/引き算は不要!日付や時刻の集計は難しくない!
5-1 日付/時刻関数を使えば期間の計算は難しくない!
テク104 2つの日付の間の月数や年数を求めたい
テク105 年月が小数点で区切られたデータをもとに期間を求めたい
テク106 年・月・日が別々のセルに分かれているデータをもとに
日付や時刻の期間を求めたい
テク107 生年月日をもとに年齢を求めたい
テク108 余分なスペースを含む生年月日データを使う場合でも、正しく年齢を求めたい
テク109 土日祝を除いて指定の期間内の日数を求めたい
テク110 特定の曜日を除いて指定の期間内の日数を求めたい
テク111 複数の特定の曜日を除いて指定の期間内の日数を求めたい
テク112 指定の期間内にある特定の曜日の日数を求めたい
テク113 期間を○年△ヶ月の形式で求めたい
テク114 期間を○年△ヶ月の形式で求め、0なら非表示にしたい
テク115 一定の期間を条件にして、別の指定の期間と一致する日数を求めたい
5-2 日付/時刻関数を使えば、求める日付の計算は難しくない!
テク116 0時までは当日で、〇時以降は次の日になるようにして日付を求めたい
テク117 0ヶ月前/Oヶ月後の日付を求めたい
テク118 土日祝を除く○日後の日付を求めたい
テク119 土日祝を除く○ヶ月後の日付を求めたい
テク120 特定の曜日を除く○日後の日付を求めたい
テク121 複数の特定の曜日を除く○日後の日付を求めたい
テク122 0日後以降で特定の曜日になる最初の日付を求めたい
テク123 指定の日付をもとに、○日締め、△日払いの場合の支払い日を求めたい

5-3 日付/時刻関数を使えば、年/月/日/週/曜日ごとの集計は難しくない!
テク124 年/月/日/週/曜日ごとに集計したい
テク125 別のブックにあるデータを年/月/日/週/曜日ごとに集計したい
テク126 複数の行・列にある日付データを年/月/日/週/曜日ごとに集計したい
テク127 年/月/日/週/曜日ごとに小計行を挿入したい
テク128 日付ごとのデータを年月ごとに集計したい
テク129 日付ごとのデータを年度ごとに集計したい
テク130 2017/1/15~2017/1/25のように期間を指定して集計したい
テク131 0日締めのときに、月ごとに集計したい
テク132 平日と土日祝で別々に集計したい
テク133 2017/1/15~2017/1/25 のような期間が複数あるときに、
合計を○年△ヶ月の形式で求めたい
5-4 勤務時間の計算はこれだけ覚えておけば難しくない!
テク134 勤務時間をもとに給与計算したい
テク135 就業時間内・残業・深夜で時給が変わるため、それぞれの勤務時間を求めたい
テク136 タイムカードの出退時間を切り上げ/切り捨てして勤務時間を求めたい
テク137 時刻が数値の形式で入力されている場合に、勤務時間を求めたい
テク138 シフトによって勤務時間が異なる場合に、勤務時間の合計を求めたい

抽出編

第6章 数式が苦手なら抽出機能を使いこなせ!
6-1 セルからの抽出は2つの分割機能を使いこなせ!
テク139 文字+数値の形式になっている入力データから文字と数値を別々に抽出したい
テク140 入力データがスペースやハイフン等で区切られている場合、別々に抽出したい
テク141 入力データが複数のスペースやハイフン等で区切られている場合、
手早く別々に抽出したい
6-2 表からの抽出はフィルター/アウトライン機能を使いこなせ!
テク142 表から小計だけを抽出したい
テク143 条件に一致するデータを抽出したい
テク144 複数の表それぞれで条件に一致するデータを抽出したい
テク145 表内に結合セルがあっても、オートフィルターで抽出したい
テク146 条件に一致するデータを別表に抽出したい
テク147 条件に部分的に一致するデータを抽出したい
テク148 条件に部分的に一致するデータを別表に抽出したい
テク149 日付データから年/月/日を条件にしてデータを抽出したい
テク150 抽出する列とその並び順を指定して、条件に一致するデータを別表に抽出したい
テク151 1つの表から別々の条件で抽出し、
それらの条件を切り替えて表示できるようにしたい

第7章 必要な値をピックアップ!セルからの抽出テクを完全網羅!
7-1 文字列から抽出!文字の一部や関連情報を抽出するテク!
テク152 1番目の文字を基準にして文字列を抽出したい
テク153 指定の文字を基準にして文字列を抽出したい
テク154 ハイフン等による区切りが複数個所に入っている文字列を自動的に分割したい
テク155 全角と半角を別々に抽出したい
テク156 ハイフン等による区切りがなく字数もバラバラの文字列を、
それぞれ指定の位置で分割したい
テク157 住所を都道府県・市区町村・番地に分割したい
テク158 ふりがなを抽出したい
テク159 法人格を除いてふりがなを抽出したい
テク160 数式を別のセルに表示させておきたい
7-2 日付や時刻から抽出!日時の一部や関連情報を抽出するテク!
テク161 日付データから年・月・日を別々に抽出したい
テク162 日付データから年月を抽出したい
テク163 日付データから4月始まりでの何年度にあたるのかを抽出したい
テク164 日付データから何月締めになるのかを抽出したい
テク165 日付データから曜日を抽出したい
テク166 日付データから曜日を抽出したいが、祝日は別の表記にしたい
テク167 日付データから曜日を抽出したいが曜日によって指定の表記にしたい
テク168 日付データから何週目にあたるのかを抽出したい
テク169 現在の日付や時刻をパソコンの内蔵時計から抽出したい
テク170 時刻データから時・分・秒を別々に抽出したい

第8章 検索してピックアップ!別表からの抽出テクを完全網羅!
8-1 検索ワードがある位置/並び/表の種類で関数を使い分ける!基本テク!
テク171 検索ワードに該当するデータが複数ある表から、
検索対象の列を指定して抽出したい
テク172 五十音順に並ぶデータを検索して該当データを抽出したい
テク173 検索対象の列の左に抽出したいデータがある表から
抽出したい(データが昇順並びの場合)
テク174 検索対象の列の左に抽出したいデータがある表から
抽出したい(データが順不同の場合)
テク175 クロス集計表から、指定の行・列の項目が交差する位置にあるデータを抽出したいテク176 ピボットテーブルから検索するとき、
レイアウト変更しても抽出データがずれないようにしたい
8-2 希望の形式で抽出!抽出したデータの形式を指定するテク!
テク177 別表から検索ワードで抽出するときに、該当データの連続する列を抽出したい
テク178 検索ワードに該当するデータがなければ空白で表示したい
テク179 検索ワードが空白の場合は空白を、見つからなければ「該当なし」等と表示したい
8-3 さまざまな検索ワードで抽出!複雑な検索で抽出するテク!
テク180 期間ごとのデータが入力された別表から日付で該当データを抽出したい
テク181 検索ワードに部分的に一致するデータを抽出したい
テク182 入力データに別表の検索ワードが含まれる場合、該当データを抽出したい
テク183 複数ある検索ワードのすべてに該当するデータを抽出したい
テク184 期間や年代などの項目がある表でも複数ある検索ワードで該当データを抽出したい
8-4 検索ワードに該当するデータが複数ある表でも大丈夫!すべて抽出するテク!
テク185 検索したいデータが連続して入力されている表から、
該当データをすべて抽出したい
テク186 検索したいデータがバラバラに入力されている表から、
該当データをすべて抽出したい
テク187 検索したいデータがバラバラに入力されている表から、
複数条件に該当するデータを抽出したい
テク188 検索したいデータがバラバラに入力されている表から、
検索ワードに部分一致するデータを抽出したい
テク189 検索したいデータがバラバラに入力されている表から、
検索ワードごとに該当データを抽出したい

第9章 目的のデータを導き出す!同じ表からの抽出テクを完全網羅!
9-1 数値の大きさを判定したい!数値の大小をもとに完全抽出
テク190 0番目の順位にある数値を抽出したい
テク191 同じ数値が複数ある表から○番目の順位にある数値を抽出したい
テク192 最も多く出現する数値を抽出したい
テク193 小計やエラー値を除いて○番目の順位にある数値を抽出したい
テク194 指定より大きい/小さい数値は求められないようにして計算結果を求めたい
テク195 50~100のように指定の範囲の数値で計算結果を求めたい
テク196 数値の大きさに対応した順位を付けたい
テク197 同じ数値があっても、数値の大きさに対応した順位を付けたい
テク198 項目ごとに、数値の大きさに対応した順位を付けたい
テク199 複数の条件で順位を付けたい(別の数値が大きい方に上の順位を付ける場合)
テク200 複数の条件で順位を付けたい(別の数値が小さい方に上の順位を付ける場合)
9-2 重複データを何とかしたい!不要なデータを完全抽出!
テク201 重複データを手早く削除して表を作成したい
テク202 重複データを別表に抽出したい
テク203 複数条件で重複になるデータを別表に抽出したい
テク204 複数行列の重複を削除して別の表に1列で抽出したい
テク205 入力するたびに、自動的に重複を除く/重複しているデータの表を作成したい
テク206 入力するたびに、自動的に重複を除く/重複している
データの表を作成したい(複数条件で重複する場合)

9-3 こんなデータを抽出したい!希望のデータを完全抽出!
テク207 データが増えても常に最後の行/列にあるデータを抽出したい
テク208 データが増えても常に最後の行/列にあるデータを
抽出したい(数式による空白データに対応)
テク209 データが増えても常に最後の行/列から○件のデータを抽出したい
テク210 項目ごとに最後の行/列にあるデータを抽出したい
テク211 0番目もしくは○番目以内の順位にあるデータを別表に抽出したい
テク212 日付データから年/月/日を条件にしてデータを別表に抽出したい
テク213 10行ごと、○列ごとなど等間隔にデータを抽出したい
テク214 入力するたびに、データを自動的に昇順で並べ替えて別表に抽出したい
9-4 表の見出しや項目名を抽出したい!入力値や集計結果をもとに完全抽出!
テク215 データがある行/列の見出しを抽出したい(同じ行/列にデータが1つしかない場合)テク216 データがある最終行/列の見出しを抽出したい(同じ行/列にデータが複数ある場合)テク217 データが連続である行/列の見出しを
「最初の見出し~最後の見出し」の形式で抽出したい
テク218 1番目にある数値の見出しを抽出したい
テク219 同じ項目が複数ある表で、項目ごとの集計値が○番目の順位にある
データの見出しを抽出したい
テク220 データの大きさが同じ場合は同じ順位で見出しを抽出したい
9-5 どうしてもフィルター抽出に対応したい!関数を追加して完全抽出!
テク221 オートフィルターや行の非表示で隠れたデータを除いて
○番目の順位にあるデータを抽出したい
テク222 オートフィルターや行の非表示で隠れたデータを除いて順位を付けたい
テク223 複数の列に個別の条件を付け、
条件に一致するかどうかのフィルターを1つ作って抽出したい
テク224 オートフィルターで土日祝や平日のデータを抽出したい
テク225 オートフィルターで指定の曜日のデータを抽出したい

第10章 複数の表/シート/ブックを扱う抽出を完全制覇!
10-1 複数のシートに分けるならピボット/関数で完全制覇!
テク226 表を項目ごとにデータが抽出されたシートに分割したい(ピボットテーブル編テク227 独自の表で項目ごとにデータが抽出されたシートに分割したい
テク228 独自の表で項目の一部のキーワードごとにデータが抽出されたシートに分割したい
10-2 別表から目的の表に検索、抽出するテクを完全制覇!
テク229 クロス表から、項目の並びを変更したクロス表にデータを抽出したい
テク230 スケジュール表の内容をカレンダーに転記したい
テク231 宛先を入力するだけで、該当データを集計表から抽出して帳票に転記したい
10-3 複数の表/シート/ブックからの検索抽出を完全制覇!
テク232 別のブックの表から検索ワードに該当するデータを抽出したい
テク233 複数の表/シートから検索ワードに該当するデータを抽出したい
テク234 複数のブックから検索ワードに該当するデータを抽出したい
テク235 シート名と検索ワードを入力して該当データを抽出したい
テク236 検索対象のシートがわからなくても、
複数の表/シートから検索ワードに該当するデータを抽出したい
テク237 検索対象のデータが複数の表/シートに含まれる場合、
該当するデータをすべて抽出したい
テク238 複数のクロス表から検索ワードに該当するデータを抽出したい

10-4 複数の表/シート/ブックから重複データを抽出するテクを完全制覇!
テク239 2つの表/シートで重複しているデータを抽出したい
テク240 2つの表/シートで重複しているデータを抽出したい(データの追加に対応)
テク241 2つの表/シートで、複数条件で重複になるデータを
抽出したい(データの追加に対応)
テク242 別の表/シートに存在しないデータを抽出したい
テク243 別の表/シートに複数条件で存在しないデータを抽出したい
テク244 別の表/シートに存在しないデータを抽出したい(データの追加に対応)
テク245 複数のシートのいずれにも存在しないデータを抽出したい
テク246 複数のシートのいずれにも、複数条件で存在しないデータを抽出したい
テク247 複数の表/シート/ブックから重複データを除いて1つの表にしたい
10-5 複数の表/シート/ブックを扱うその他の抽出テクを完全制覇!
テク248 複数の表に分かれているデータについて、数値の大きさに対応した順位を付けたい テク249 複数の表に分かれているデータについて、○番目の順位にある数値を抽出したい
テク250 各シートの同じセル番地のデータを1つの表に抽出したい
テク251 1つの表に入力されたデータを、各対応シートの所定のセル番地に抽出したい
テク252 常に1つ前のシートの所定のセル番地のデータを抽出したい
テク253 シート名のリストを作成し、シート名をクリックしたら
該当シートにジャンプするようにしたい

索引

目次 – いちばんやさしいExcelピボットテーブルの教本 人気講師が教えるデータ集計 が一瞬で終わる方法

講義、実践のワークショップ形式だから分かる!

ピボットテーブルの機能、操作方法、そして利便性など、基礎的な内容から始まりパワーピポットまで全て詳しく解説されています。Excelを使う上で非常に重要な機能であるピボットテーブルを、この1冊でマスターすることができるでしょう。

 

 

はじめに

本書は、ピボットテーブルで集計作業を瞬時に終わらせるノウハウを
まとめた本です。もし次のような状況でお困りなら、本書はきっとお役に立てるでしょう。
・毎月作成しているレポートに、数値を手作業で転記をしている
・レポートを作るときに足し算や、SUM関数をひたすら手入力している
・同僚やお客さんからもらうデータが使いづらく、集計が自動化できない

私が、この本でもっともお伝えしたいことは、ピボットテーブルでの集計・分析において大事なのは「元データを整形するテクニック」だということです。 実は、ピボットテーブルの集計作業自体はさほど難しくありません。1集計 しやすい元データを作る、2ピボットテーブルで集計をする――このパター ンに沿って作業をするだけで、集計作業は一瞬で終わります。注目してほし いのは、「ピボットテーブルで集計をする」前に、「集計しやすい元データを作 る」ステップが必要だということです。

実際の業務では、ピボットテーブルに適した状態でデータが準備されている とは限りません。実際、私が監査法人や会計事務所で仕事をしてきた中で、 使いにくいデータしか入手できないケースが頻繁にありました。こうしたデータでピボットテーブルを使うには、集計の前段階として「集計しやすい元 データ」を自力で作る必要があるのです。そこで本書では、「集計しやすい元 データを作る」方法を詳細に説明していきます。
皆さんは、「ピボットテーブルは難しい」という漠然としたイメージがあるか もしれません。でも、実際には、誰でもできる単純作業を行っているだけです。 本書では、ピボットテーブルの行っている処理を1ステップずつ再現して説明 をしています。それを見れば、ピボットテーブルが何をしているか、すぐに 理解できるでしょう。
難しそうだからと躊躇する必要はありません。本書を手にピボットテーブル に挑戦してみましょう。本書で紹介するパターンに従えば、ほとんどの集計 作業が瞬時に終わります。ぜひ、その世界を、あなた自身で体験してみてく ださい。
2019年8月 羽毛田睦土

 

「いちばんやさしい Excelピボットテーブル の教本」の読み方

「いちばんやさしいExcelピボットテーブルの本」は、はじめての人でも迷わないように、わかりやすい説明と大きな画面で Excelピボットテーブ ルを使ったデータの集計方法を解説しています。
「何のためにやるのか」 がわかる!
薄く色の付いたページでは、Excelピボットテーブ ルでデータを集計する際に必要な考え方を解説 しています。実際のページの作成に入る前に、操 作の目的と意味をしっかり理解してから取り組め ます。

タイトル
レッスンの目的をわかりやすくまとめています。
レッスンのポイント
このレッスンを読むとどうなるのか、 何に役立つのかを解説しています。
解説
データを集計する際の大事な考え方を、画 面や図解をまじえて丁寧に解説しています。
講師によるポイント
特に重要なポイントでは、講師が登場 して確認・念押しします。

「どうやってやるのか」 がわかる!
操作手順は、大きな画面で1つ1つのステップを 丁寧に解説しています。役立つ知識や注意点に ついて補足説明もあるので、つまずきません。
手順
番号順に操作をしていきます。画面のどこを 操作するのかも、赤く囲んで指しています。
ワンポイント
レッスンに関連する知識や知 っておくと役立つ知識を、コラムで解説しています。

いちばんやさしい Excelピボットテーブル
人気講師が教える データ集計が一瞬で終わる方法

Contents

目次
著者プロフィール
はじめに
本書の読み方
索引
本書のサンプルファイルのダウンロードについて

Chapter1 ピボットテーブルの学習を始める前に
[ピボットテーブルとは] ピボットテーブルで何ができるのかを知ろう
[ピボットテーブルのメリット] ピボットテーブルの3つのメリットを知ろう
[ピボットテーブルを使う3ステップ] ピボットテーブルを3つのステップで使いこなす

Chapter2 ピボットテーブルの仕組みを理解しよう
[ピボットテーブルの集計の仕組み]ピボットテーブルが行っている作業を理解しよう
[切り口に沿った集計の仕組み] 同じ元データをさまざまな切り口で集計する仕組みを理解しよう
[ピボットテーブルの基本操作] Excelでピボットテーブルを作ってみよう
[集計の切り口の変更] ピボットテーブルで集計の切り口を変えてみよう
[複数項目での集計] 複雑な表を集計する仕組みを理解しよう
[エリアセクションの使い方] ピボットテーブルで複雑な表を作ろう
[データの更新] ピボットテーブルに元データの修正を反映させよう
[データの個数の集計] ピボットテーブルを使って件数を集計しよう

Chapter3 分析しやすい形の元データを 3準備しよう
[データベースとは] 集計に使う元データをデータベースの形式に整えよう
[データベースの列見出しを整える] 列見出しは1行にまとめ、空欄を作らないようにしよう
[データベースの空欄を埋める] 値の入力を省略しないようにしよう
[データベースを1つの表にまとめる] 複数の表に分割された元データは1つの表にまとめよう
[テーブルとは] 元データをテーブルに変換して扱いやすくしよう
[表記の統一] 取引先名、商品名などの表記ゆれを統一しよう
[数値・文字列の統一]見た目は同じでも中身が違う数値・文字列を統一しよう

Chapter4元データに列を追加して思い通りの集計表を作ろう
[思い通りの集計表を作る]ピボットテーブルで思い通りの表が作れない原因とは?
[集計の切り口を意識する]ピボットテーブルで表を作るときは集計の切り口を意識しよう
[元データに列を追加する] 集計表の軸に合わせて、元データに列を追加しよう
[グループ化] グループ化の機能で切り口を変えて集計をしよう
[数値列を整形する]数値列をピボットテーブルで扱いやすい形に整形しよう

Chapter5 ピボットテーブルの 表示を整えよう
[書式の設定] ピボットテーブルの書式を設定しよう
[小計の非表示、表レイアウトの変更] 行または列に複数の項目を表示するときの見た目を整えよう
[表示順の並べ替え] 項目の並び順を変えて集計表を読みやすくしよう
[並べ替えの工夫] コードを付けて柔軟に表示順を並べ替えよう
[件数・比率・累計の計算] ピボットテーブルで件数・比率・累計を計算しよう
[複数のフィールドの追加] 金額と一緒に件数・比率・累計を集計表に表示しよう
[項目名の書き換え] 項目名を変更して数値の意図を明確にしよう
[フィルター] フィルターをかけて必要なデータだけ集計表に表示しよう
[フィルターの応用] SL集計表にないデータや、総計でフィルターをかけよう

Chapter6 Power Queryで不完全な表をデータベースに整形しよう
[Power Queryエディターの起動] Power Queryの使い方を学ぼう
[Power Queryエディターの基本操作] Power Queryの基本的な操作を確認しよう
[クエリの更新] 作成済みのクエリを更新しよう
[クエリの修正、削除、再出力] 作成済みのクエリを修正しよう

Chapter7さまざまな形式の表を データベースの形に加工しよう
[Power Queryを使ったデータ整形] 日付別に作成された表をデータベースに加工しよう
[ピボット解除、行列の削除] マトリックス型の表をデータベース型に変換しよう
[複数シートの集約] 複数シートのデータを1シートにまとめよう
[1行のデータを複数行に分割する] 拠点間在庫移動データを入庫表と出庫表に分割しよう
[複数ブックの読み込み] 複数ブックを1シートにまとめよう
[CSVファイルの読み込み] CSVファイルを読み込もう

Chapter8ピボットテーブルを 集計や照合に活用しよう
[重複の削除、件数を数える] 重複のない一覧を作成し、件数を数えてみよう
[複数リストの照合、差異把握] 2つのリストを照合して差異を見つけよう
[差異の照合] 売上明細と入金明細を照合して差額を計算しよう
[2つの列の差額を計算する] 経費の年度別の比較表を作ろう
[増減表の作成] 1カ月間の在庫増減表を作ろう
[月末時点での累計を含む推移表の作成] 複数月にわたる在庫推移表を作ろう

Chapter9ピボットテーブルを もっと便利に使おう
[ドリルスルー]ダブルクリックで集計元の明細を確認しよう
[スライサーとタイムライン] スライサーとタイムラインで直感的にデータを絞り込もう
[ピボットグラフ] ピボットテーブルの集計結果をグラフ化しよう
[ピボットテーブルの空欄を0で埋める] ピボットテーブルの空欄は「0」で埋める設定にしておこう
[ピボットテーブルを参照する数式] ピボットテーブルで集計した結果を数式で参照するときの注意点!

Chapter10 今後の学習に向けて
[SUMIFS関数、COUNTIFS関数] 関数とピボットテーブルをうまく使い分けよう
[参考資料] 本書のあとにおすすめする参考書

Chapter1 ピボットテーブルの学習を始める前に
最初に、ピボットテーブルのおおまかなイメージをお伝えします。ピボットテーブルを使うと何ができるのか、使いこなすためにはどういう知識 が必要になるのかを把握しましょう。

著者プロフィール
羽毛田睦土(はけたまこと)
公認会計士・税理士 「経理・会計事務所向けエクセルスピードアップ講座」運営 東京大学理学部数学科を卒業後、デロイトトーマツコンサルティン グ株式会社(現アビームコンサルティング株式会社)、監査法人トーマツ(現有限責任監査法人トーマツ)勤務を経て独立し会計事 務所を設立。データベーススペシャリスト・ネットワークスペシャ リスト資格を保有する異色の公認会計士。 月間アクセス 65万PVのブログ「経理・会計事務所向けエクセルス ピードアップ講座」を運営。手作業で丸1日かかる作業をピボット テーブルで一瞬で終わらせたノウハウをセミナーで紹介。その他、 日常業務を楽にするためのエクセルの使い方を伝えるセミナーを定 期的に開催中。

経理・会計事務所向けエクセルスピードアップ講座:https://www.excelspeedup.com/

本書は、Microsoft Excelについて、2019年8月時点での情報を掲載しています。
また、Microsoft ExcelのバージョンはOffice 365を使用しています。
本文内の製品名およびサービス名は、一般に各開発メーカーおよびサービス提供元の登録商標または商標で。
なお、本文中にはTMおよびRマークは明記していません。

目次 – 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

目次 – Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本

マクロ、VBAいらず?

Excel VBAがある程度できても、PowerQueryやPowerPivotには手付かずという方は少なくありません。ですが本書は、実際のサンプルデータを用いて解説されており、かなり分かりやすいのでスラスラ読み進めることができます。あっという間にスキルアップできるような1冊です。

はじめに

多くの皆様に愛され、使われ続けているExcelは、いま大きな変革を迎えています。この変化はExcel誕生以来の革新と呼ばれており、Excelの使用方法を抜 本から変える可能性をはらんでいます。特に本書のテーマである「データ集計・分 析」といったレポートの作成、そしてレポートの使用方法に関して、絶大な威力を 発揮する変化です。
しかし、残念なことにこれらの機能は日本ではほとんど知られていません。本 書を手にされた皆様は「パワービボット」という言葉を聞いたことがあるでしょう か?おそらく「ピボットテーブル」について知っている方はいても、それに「パ ワー」が付いた「パワービボット」についてはほとんどの方がご存じないことと思 います。「パワービボット」はその変化の中で生まれた強力な機能の1つで、本書 のメインテーマとなる機能です。

私はIT部門で10年以上のキャリアを積んだのち、経理部門へ異動した人間で すが、経理部門へ来て約4年が経った頃、ひょんなことからこのパワーピボットを 知りました。正直なところ初めてこのパワーピボットの機能を知ったとき、私は背 筋が寒くなりました。それまでIT部門の人間として多大な労力を払って作らなければいけなかったレポートが、プログラミングなしで簡単に実現できてしまうからで す。さらに調査を進めてゆき、「パワークエリ」の存在を知った時には恐怖すら感 じました。このパワークエリとパワービボットを組み合わせれば、従来ソフトウェ アとハードウェアの両面からIT部門の支援を得なければ成し遂げられなかったレ ボートが、もはやExcelファイル1つで誰でも作ることができるからです。IT部門 出身の人間として自分の誇ってきたレーゾンデートル=存在意義が揺らぐのを感 じました。当初、私は最初はこの事実を直視できませんでしたが、恐る恐る目を向けてみると、今度はその新技術、アプローチ、そして完成するものの美しさに魅了 され、すっかりExcelの虜となってしまいました。

しかし、さっそく知った新技術を使ってみようと手を動かし始めたところ、すぐに壁にぶつかってしまいました。それらの新技術は大変すばらしいものですが、ある特有のアプローチで使用しなければ、技術の群れの袋小路にはまりこみ、満足 なレポートすら作ることができないのです。それはITの世界では「ビジネス・イン テリジェンス」と呼ばれる分野でのアプローチです。
どんな優れた道具があったとしても、それを使いこなせなければ意味がありま せん。そこで私は自分自身の学習と体験をもとに、「パワーピボット」を中心とし て「パワークエリ」「DAX」「ピボットグラフ」「条件付き書式」といった種々の機 能を、一貫した7つのステップにまとめました。本書を手にされた皆様は、それに よって従来では到底考えられなかった高度なレポーティングおよび自動化を実現 できることでしょう。

本書は、これらのモダンExcelとも呼ばれる新技術群を日本に根付かせるべく、私が強い使命感を持って企画した内容が、翔泳社様のご協力によって実現された本です。本書をきっかけに1人でも多くの皆様が、従来の時間と精神力とお 金を削るアプローチから脱却し、新しいアプローチで次元の異なるレポーティン グを実現できるようになることが私の切なる願いです。
購尾祥

目次

はじめに
本書の使い方
第1部 理論編
第1章 Excelの常識を変えるモダンExcelの登場
1 Excel誕生以来の革新_004
社内に散らばるデータをまとめて取り込むPower Query_004
データの骨格を作るパワービボット_007
横無尽の集計を可能にするDAX_008
KPIを一覧できるダッシュボード_009
2 マクロ・VBAによる自動化の限界_010
習得に時間がかかるプログラミング言語_010
プログラムのブラックボックス化_010
遅い実行速度_011
3 モダンExcelのテクノロジー_011
プログラム不要で誰でも作れる_011
見える化されたプロセス_012
ハイ・パフォーマンス_012
4 テクノロジーの進化が「発想の制約」をなくす_013
インフラ環境の制約がなくなる_013
アプリケーション開発の制約がなくなる_014
発想の制約がなくなる_014

第2章 モダンExcelによる全自動レポートの仕組み
1「集計」と「分析」の違い_016
2 データとロジックの徹底分離で「定点観測」レポートを実現する_017
「定点観測」アプローチ_017
一回きりのレポート=One-Timeレポートの表面_019
「定点観測」のメリット_020
3「骨格」を作ってデータを集める_021
定型レポート-One-Patternレポートの悲劇_021
データの骨格=「データモデル」 _022
骨格を支える骨=「テーブル」 _024
骨と骨をつなぐ関節「リレーションシップ」_028
「データモデル」で何ができるか?_029
4 インタラクティブ・レポートでデータを表現_033
「文脈」に沿って「集計」をするピボットテーブル_033
データモデルで文脈を「 選択」_034
変化する文脈に対応した計算式「メジャー」_036
データを厚く「窓」=インタラクティブ・レポートによる「表現」_038
5 インタラクティブ・レポートがおこす変化_038

第2部 実践編
第1章 実践にあたって
1 動作環境について_044
2「パワーピボット」について_044
3 本書で掲載している画面イメージについて_045
4 Excelのバージョン確認の方法_046
5 レポートを作る際の心構え_048
とりあえず「試作品」を作る_048
どこで何を使うのかを明確に_048
作るときに考えて、緑り返すときは考えない_049
後で忘れても困らないように_049
意識しなくても要点が目に飛び込んでくるレポートを_049

第2章 まずは基本の星型モデルで7つのステップをマスター
1 生きたレポートを作るための7つのステップ_052
1みたてる_053
インプット情報の棚卸し_054
データモデルの下書き_055
アウトプットのラフスケッチ_057
2 とりこむ_058
サンプルファイルの準備_059
「売上明細」をとりこむ_061
「客」をとりこむ_082
「商品」をとりこむ_090
「支店」をとりこむ_092
「カレンダー」を作る_095
作ったクエリはグループ化_104
3つなげる_107)
「F_売上明細」と「顧客」をつなぐ_107
「F_売上明細」と「商品」をつなぐ_111
「F_売上明細」と「支店」をつなぐ_112
「F_売上明細」と「カレンダー」をつなぐ_112
ダイアグラムビューについて_114
4 ならべる_115
ピボットテーブルを呼び出す_116
確認用項目を「値」に_122
「商品」テーブルの項目を「行」に_123
「カレンダー」テーブルの項目を「列」に_126
スライサーを追加_128
仕上げのレイアウト調整_132
5かぞえる_138
「売上合計」メジャーの追加_139
「メジャー」と「値フィールドの設定」の違い_144
「販売数量合計」メジャーの追加_147
「平均単価」メジャーの追加_149
6えがく_152
ピボットテーブルからピボットグラフを作らない_153
ビボットグラフはゼロから作る_157
ピボットグラフとテーブルはスライサーでつなぐ_161
グラフを含めたレイアウト調整_162
7 くりかえす_167
データソースの更新_169
レポートを更新して「定点観測」 169
「数字テーブル」のコンパクト化_172

第3章 商品別収益性分析 1「クエリのマージ」で2つのテーブルを結合_178
接続用「F商品」クエリの用意_178
「F_商品」を「F_売上明細」にマージ_180
「商品」まとめテーブルの簡素化_189
追加項目の確認_190
2 利益率のドリルアップ・ダウン_191
「商品別収益性分析」ビボットテーブルの用意_191
「割増引合計」「利益合計」「利益率」メジャーの追加_193
「利益率」メジャーをドリルアップ・ダウンに対応_196
3 条件付き書式はメジャーに設定_200
4 割合の比較にはレーダーチャート_204

第4章 商品カテゴリー・商品別の売上割合
1 総計に対する割合_210
「商品カテゴリー別売上割合」ピボットテーブルの用意_211
「顔選択」の流れ_212
「商品」テーブルの選択条件を解除して「総計」を出す_216
「総計」に対する「商品カテゴリー」の割合を出す_220
スライサーと選択条件の解除_221
2 小計に対する割合_235
ピボットテーブルに「商品名」を追加_235
「商品名」項目の選択条件を解除して小計を出す_227
「小計」に対する「商品」の割合_230
3 階層ごとの条件判断_232
有効なフィルターを知る_232
条件に応じて処理を分ける_235
データバーには最大値と最小値をセット_237

第5章 当期累計売上
1 当期累計売上その1詳細パターン_242
「当期累計売上」ピボットテーブルの用意_242
「当期累計」フィルターの作り方_243
2 当期累計売上その2タイムインテリジェンス関数_255
タイムインテリジェンス関数で当期累計_256
当四半期累計と当月累計_258

第6章 売上前年比較
1 一年前の数字を持ってくる_262
「前年同期比」ピボットテーブルの用意_262
前年度の半期売上を取得する_263
2 前の年と比較する_2654
売上の前年同期比(単期)_265
売上の前年同期比(累計)_268
3 条件付き書式で前年比較_272

第7章 予算vs実績比較
1 予算vs実績比較の見立て_280
予算ファイルの構造_280
「ダイヤ型データモデル」について_281
2 複数のExcelファイルを一括取り込み_283
「フォルダーから」予算ファイル一覧を取得する_283
「カスタム列」でデータを一括取得_286
ピボット解除で横に並んだデータを縦に_290
テキストを日付データに変換_296
仕上げと予算データのとりこみ_301
3「商品カテゴリー」まとめテーブルの作成_307
4 ダイヤ型データモデルを作る_309
「F_予算」と「支店」をつなぐ_309
「F_予算」と「カレンダー」をつなぐ_310
「F_予算」と「商品カテゴリー」をつなぐ_310
「商品」と「商品カテゴリー」をつなぐ_311
5 予算vs実績比較_312
「予算実績対比」ビポットテーブルの用意_312
予算の合計と累計のメジャーを作る_314
予算vs実績比較のメジャーを作る_317
6 一人当たりの生産性分析_321

第8章 ダッシュボード
1 ピボットテーブル・グラフの用意_326
2 ダッシュボード向けの共通設定_331
目盛線の非表示_331
スライサーの設定_332
ピボットテーブルの設定_335
ピボットグラフの設定_337

本書の使い方

1.本書の構成
本書は、第1部:理箱編と、第2部:実践編で構成しています。 第1部:理論編では、パワーピボットをはじめとしたモダンExcelの新機能に ついて紹介します。また、それらモダンExcelの新機能を応用してレポート作成 を自動化するための仕組みについて説明しています。
第2部:実践編では、実際に手を動かしてExcelのレポートを作ります。この 中で、基本の7つのステップをマスターし、レポート自動化の基本を身に付けま す。様々なシナリオを通じて、新技術の応用方法を学んでいきます。
第2部:実践編から先に読み始めても構いませんが、初めて聞く言葉や考え方 が見つかった場合は、第1部:理論編に立ち戻って確認することをお勧めします。

2.動作環境および画面イメージについて
Microsoft Excelは、バージョンによって画面のデザインや機能面に違いがあります。本書は、その中でもExcel 2016以降のバージョンを主な対象としています。
Excel 2013やExcel 2010をご利用の場合は、アドインを追加することで、本 書で紹介している内容の大部分を試すことは可能ですが、画面やメニューが大 きく異なるため、動作の保証はできません。
また、本書に掲載している画面イメージは、筆者が本書執筆時に利用していたOffice365環境のExcel(2019年4月頃)によるものです。そのため、プレイン ストール版およびパッケージ版のExcel 2016の画面とは、イメージが一部異なる場合があります。いくつかの大きく異なる画面についてはExcel 2016の画面イ メージ(図番号の後に[2016]と記しています)を併記する形で対応しています。 また、わずかな違いについては、文章で説明しています。
動作環境については、第2部:実践編 第1章で詳しく説明していますので、ご 利用前に必ずご確認ください。

3・読者特典について
本書では、読者特典として翔泳社のWebサイトから、第2部:実践編で使用 する練習用のサンプルファイルと、特別付録のPDFファイルをダウンロードすることができます。
サンプルファイルには、デモで使用する「データソース」ファイルのほか、 各章の開始・終了時点のExcelファイルを用意していますので、自分が興味を 持っている章からデモを開始することもできます。ただし、PowerQueryのデー タソースを指定したフォルダーの場所(C:¥データソース)は変更できないので ご注意ください。また、Officeの更新プログラムが適用されていない環境の場合、 Power Queryの関数に互換性が無く、サンプルファイルが使用できない場合が ありますのでご了承ください。
特別付録のPDFをファイルには、ページ数の都合で本書に掲載しきれなかったシナリオ「顧客別売上分析」や、ビジネスインテリジェンス(BI) 用語についての解説、およびPower Pivotアドインを有効にする手順についての解説を掲載しています。

4・読者特典のダウンロード
本書の読者特典として、以下のサイトからサンプルファイルおよびPDFファ イルをダウンロードできます。
http://www.shoeisha.co.jp/book/present/9784798161181/
※会員特典データのダウンロードには、SHOEISHA iD(翔泳社が運営する無料の会員制度)への会員登録が必要です。詳しくは、Webサイトをご覧ください。
※ファイルをダウンロードするには、本書に掲載されているアクセスキーが必要になります。 該当するアクセスキーが掲載されているページ番号はWebサイトに表示されますので、そちらを参照してください。
※会員特典データに関する権利は著者および株式会社翔泳社が所有しています。許可なく配布したり、Webサイトに転載することはできません。
※会員特典データの提供は予告なく終了することがあります。あらかじめご了承ください。

[第1部] 理論編
第1部では、近年のExcelにおける「表計算システム型アプローチか らデータベース型アプローチへの変化」とその仕組み、および、それが業務ユーザーにもたらすメリットについて解説します。

[第1章] Excelの常識を変える モダンExcelの登場
ここでは、近年のExcelの進化の概要を、 旧来型のアプローチと比較しながら説明していきます。

目次 – 何万件ものデータやピボットテーブルで苦しんでいる人のための Excel多量データ整形テクニック

大量データを思い通りに集計!

実際のビジネスの現場で使われている表は、ピボットテーブルに向いていないものも多いです。しかし本書は、そういった表をピボットテーブルの処理に適したものに変換するための技術が多く載っています。使っている関数も絞られており、実務に役立つ有益な知識が身につくこと間違い無いでしょう。

はじめに

ピボットテーブルは、多量のデータを様々な角度から瞬時に集計できるExcelの強力な機能の一つです。これは、スーパー機能と呼んでもよいほどのパワーを秘めています。ピボットテーブルは、それを使いこなせるかどうかでデータを活用できるかどうかを左右するほど重要です。

しかし、このピボットテーブルでデータを集計した場合、どうも思うような集計結果を得ることができない、と感じることはないでしょうか。また、ある表では満足できる集計ができたのに、別の表ではうまく集計できない、ということはないでしょうか。
筆者も、このことを長年感じてきた一人です。そして、もしかすると表の構造に原因があるのではないかと感じ、表の構造とピボットテーブルとの関係を探ってきました。

書籍やウェブサイトには、「ピボットテーブルで集計する表はリスト表にする」とサラッとだけ記述されています。リスト表の定義や構造のことを詳しく説明した書籍やウェブサイトは、筆者の知る限り見当たりません。
そして、最初からリスト表ありきで、オフィスで日常的に使用している表がまるですべてリスト表であるかのような説明に、非常に違和感がありました。

筆者は、長年企業のオフィス現場にいますが、そこで目にする表の大部分は、決してリスト表ではありません。リスト表以外の様々なパターンの表が使用されています。しかし、これらの表がリスト表でないために、ピボットテーブルで思うように集計できないのが現状です。
オフィス現場で日常的に使用されている表には、非常に有用なデータが入力されています。もし、この表をピボットテーブルで簡単にしかも自在に集計できるとすると、既存のデータがさらなる価値を生み出すようになります。

本書は、このことを実現するためのものです。つまり、オフィス現場の表をリスト表に変更する方法を、表のパターン別に解説した本です。本書によって、今まで埋もれていた既存のデータの価値を発掘して貰えれば幸いです。

ピボットテーブルの名称
本書では、ピボットテーブルの各部分の名称を、次のように記載しています。
●ピボットテーブル
フィルターフィールド
行フィールド
列フィールド
値フィールド
●ピボットテーブルのフィールドリスト
フィールドセクション
レイアウトセクション
フィルターエリア
行エリア
列エリア
値エリア

ピボットテーブルの名称

①フィルターフィールド
②行フィールド
③列フィールド
④値フィールド
⑤フィールドセクション
⑥レイアウトセクション
⑦フィルターエリア
⑧行エリア
⑨列エリア
⑩値エリア

CONTENTS

1章
表とピボットテーブルの関係
ピボットテーブルでは、うまく集計ができない場合がよくあります。その理由は、ユーザーの技量不足ではなく、表の形にあります。ピボットテーブルで自在に集計するには、表を「リスト表」にする必要があります。この章では、リスト表ではない表のパターンを説明するので、表のピボットテーブル集計の向き不向きが分かります。

1.1▶︎ピボットテーブルでうまく集計できない理由
一般表はピボットテーブルでうまく集計できない
全く集計できない一般表もある
技量不足ではなく表の形が問題なのだ

1.2▶︎ピボットテーブル集計に向く表とは
リスト表に変更すればうまく集計できる
リスト表と一般表の集計を比べてみよう
人には一般表、ピボットテーブルにはリスト表

1.3▶︎リスト表の内容
リスト表とは同種データを同一列にしたもの
リスト表とはテーブルを非正規化したもの
掛け算して意味のあるのがリスト表
リスト表に見やすさを求めるな

1.4▶︎ピボットテーブル集計に向かない一般表パターン
オフィス現場にはリスト表は少ない
パターン1:単純並列表…非常に多くある(店舗別売上表など)
パターン2:階層並列表…統計データに多い(統計白書など)
パターン3:繰返しリスト表…伝票データの管理用(請求一覧表など)
パターン4:並列リスト混合表…意外と多い(見積管理表など)
パターン5:縦列型並列表…整理用に使われる(データ整理メモなし
パターン6:縦列型リスト表…名簿でお馴染み(会員リストなど)
パターン7:帳票…1件が1枚の書類(請求書など)

2章
リスト表への変更の基本操作
一般表のリスト表への変更方法は、表のパターンによって異なります。しかし、共通した処理も多くあるので、この章ではその共通操作に関する基本的な操作を説明します。また、リスト表への変更のポイントとなる点も説明します。一般表をリスト表に変更すれば、データの集計や分析で絶大な効果をもたらします。

2.1▶︎リスト表への変更のルールと手順
リスト表への変更ルールを覚えよう
リスト表への変更手順を理解しよう

2.2▶︎リスト表への変更の主役INDEX関数の使い方
INDEX関数で表からデータを取り出す
多量のINDEX関数の手入力には問題がある
INDEX関数の引数はセルに入力する
INDEX関数はワンパターン化して入力する
INDEX関数の参照方法を使い分ける
ワンパターン化したINDEX関数を複写する

2.3▶︎結合セルやセル内複数データの対応
結合セルには数式でデータを埋め込む
セル内複数データを他のセルに取り出す
セル内改行データを他のセルに取り出す

3章
単純並列表のリスト表への変更
単純並列表とは、同じ種類のデータ列が横に並んでいる表のことです。例えば、次のような表が挙げられます。
▶︎営業員別の月別売上表
▶︎商品別の店舗別売上表
▶︎エ事施工の進捗管理表

3.1▶︎単純並列表の特徴
単純並列表ってどんな表?
「営業員別の月別売上表」の単純並列表
「商品別の店舗別売上表」の単純並列表
「工事施工の進捗管理表」の単純並列表

3.2▶︎単純並列表のピボットテーブル集計の限界
列数の多い場合は現実的ではない
日付集計やデシル集計ができない
横計と統計は一方しか集計できない
的確な小計値が集計できない
集計項目の先頭に文字列が付加される
空白セルが無駄に表示される

3.3▶︎単純並列表のリスト表への変更の仕組み
事例の単純並列表とそのリスト表
変更手順をイメージで理解する
パラメータの値を理解する

3.4▶︎単純並列表のリスト表への変更手順
手順1:共通リスト値のパラメータを入力する
手順2:共通リスト値用のINDEX関数を入力する
手順3:項目名用のパラメータを入力する
手順4:項目名用のINDEX関数を入力する
手順5:単純並列値用のINDEX関数を入力する
手順6:空白以外の行を抽出する

3.5▶︎単純並列表のリスト表への実戦的な変更
リスト表への実戦的な変更とは
多量のパラメータを数式で入力する
単純並列表をテーブル化する
INDEX関数をメンテナンスフリー化する

4章
階層並列表のリスト表への変更
階層並列表とは、並列値の横目が分類別に階層構造になっている表のことです。例えば、次のような表が挙げられます。
▶︎地形面積の統計表
▶︎見積プロセス管理表
▶︎項目別の販売表

4.1▶︎階層並列表の特徴
階層並列表ってどんな表?
「地形面積の統計表」の階層並列表
「見積プロセス管理表」の階層並列表
「項目別の販売表」の階層並列表

4.2▶︎階層並列表のピボットテーブル集計の限界
階層別の集計ができない
横計と縦計は一方しか集計できない
的確な小計値が集計できない
集計項目の先頭に文字列が付加される
空白セルが無駄に表示される

4.3▶︎階層並列表のリスト表への変更の仕組み
事例の階層並列表とそのリスト表
変更手順をイメージで理解する
パラメータの値を理解する

4.4▶︎階層並列表のリスト表への変更手順
手順1:共通リスト値のパラメータを入力する
手順2:共通リスト値用のINDEX関数を入力する
手順3:項目名のパラメータを入力する
手順4:項目名用のINDEX関数を入力する
手順5:階層並列値のパラメータを入力する
手順6:階層並列値用のINDEX関数を入力する
手順7:空白以外の行を抽出する

4.5▶︎階層並列表のリスト表への実戦的な変更
リスト表への実戦的な変更とは
多量のパラメータを数式で入力する
階層並列表をテーブル化する
INDEX関数をメンテナンスフリー化する

5章
繰返しリスト表のリスト表への変更
「繰返しリスト表」とは、同じパターンのリスト値が横方向に繰り返し入力されている表のことです。例えば、次のような表が挙げられます。
▶︎見積の1件1行管理表
▶︎社員の1名1行管理表
▶︎個人スキルの1名1行管理表
5.1▶︎繰返しリスト表の特徴
繰返しリスト表ってどんな表?
「見積の1件1行管理表」の繰返しリスト表
「社員の1名1行管理表」の繰返しリスト表
「社員スキルの1名1行管理表」の繰返しリスト表

5.2▶︎繰返しリスト表のピボットテーブル集計の限界
繰返しリスト表のまま集計するとどうなる?
意味のある集計ができない

5.3▶︎繰返しリスト表のリスト表への変更の仕組み
事例の繰返しリスト表とそのリスト表
変更手順をイメージで理解する
パラメータの値を理解する

5.4▶︎繰返しリスト表のリスト表への変更手順
手順1:共通リスト値のパラメータを入力する。
手順2:共通リスト値用のINDEX関数を入力する
手順3:繰返しリスト用のパラメータを入力する
手順4:繰返しリスト用のINDEX関数を入力する
手順5:空白以外の行を抽出する

5.5▶︎繰返しリスト表のリスト表への実戦的な変更
リスト表への実戦的な変更とは
多量のパラメータを数式で入力する
繰返しリスト表をテーブル化する
INDEX関数をメンテナンスフリー化する

6章
並列リスト混合表のリスト表への変更
「並列リスト混合表」とは、並列値とリスト値が混合していて、しかもそれが複数組あるという複雑で大規模な表のことです。例えば、次のような表が挙げられます。
▶︎見積プロセスの統合管理表
▶︎社員研修の一覧管理表
▶︎面積と地価の統計表

6.1▶︎並列リスト混合表の特徴
並列リスト混合表ってどんな表?
「見積プロセスの統合管理表」の並列リスト混合表
「社員研修の一覧管理表」の並列リスト混合表
「面積と地価の統計表」の並列リスト混合表

6.2▶︎並列リスト混合表のピボットテーブル集計の限界
並列リスト混合表のまま集計するとどうなる?
意味のある集計ができない

6.3▶︎並列リスト混合表のリスト表への変更の仕組み
事例の並列リスト混合表とそのリスト表
変更手順をイメージで理解する
INDEX関数のパラメータを理解する

6.4▶︎並列リスト混合表のリスト表への変更手順
手順1:共通リスト値のパラメータを入力する
手順2:共通リスト値のINDEX関数を入力する
手順3:リスト値のパラメータを入力する
手順4:リスト値のINDEX関数を入力する
手順5:項目名のパラメータを入力する
手順6:項目名のINDEX関数を入力する
手順7:並列値のINDEX関数を入力する
手順8:空白以外の行を抽出する

6.5▶︎並列リスト混合表のリスト表への実戦的な変更
リスト表への実戦的な変更とは
多量のパラメータを数式で入力する
並列リスト混合表をテーブル化する
INDEX関数をメンテナンスフリー化する

7章
縦列型並列表のリスト表への変更
「縦列型並列表」とは、縦方向に異なる種類のデータが入力され、横方向には同じ種類のデータが入力されている表です。例えば、次のような表が挙げられます。
▶︎商品別・月別の得意先売上表
▶︎店舗別・日別の人員シフト表
▶︎商品別・月別の目標達成管理表

7.1▶︎縦列型並列表の特徴
縦列型並列表ってどんな表?
「商品別・月別の得意先売上表」の縦列型並列表
「店舗別・日別の人員シフト表」の縦列型並列表
「商品別・月別の目標達成管理表」の縦列型並列表

7.2▶︎縦列型並列表のピボットテーブル集計の限界
縦列型並列表のまま集計するとどうなる?
意味のある集計は二つしかできない

7.3▶︎縦列型並列表のリスト表への変更の仕組み
事例の縦列型並列表とそのリスト表
変更手順をイメージで理解する
INDEX関数のパラメータを理解する

7.4▶︎縦列型並列表のリスト表への変更手順
手順1:共通リスト値のパラメータを入力する
手順2:共通リスト値のINDEX関数を入力する
手順3:項目名のパラメータを入力する
手順4:項目名のINDEX関数を入力する
手順5:上段値のINDEX関数を入力する
手順6:下段値のパラメータを入力する
手順7:下段値のINDEX関数を入力する
手順8:空白以外の行を抽出する

7.5▶︎縦列型並列表のリスト表への実戦的な変更
リスト表への実戦的な変更とは
多量のパラメータを数式で入力する
縦列型並列表をテーブル化する
INDEX関数をメンテナンスフリー化する

8章
縦列型リスト表のリスト表への変更
「縦列型リスト表」とは、1行で入力されるべきリスト値が、見やすくするために途中の列で折り返されて複数行に入力されている表です。例えば、次のような表が挙げられます。
▶︎住所録
▶︎社員スキル管理表
▶︎社員台帳

8.1▶︎縦列型リスト表の特徴
縦列型リスト表ってどんな表?
「住所録」の縦列型リスト表
「社員スキル管理表」の縦列型リスト表
「社員台帳」の縦列型リスト表

8.2▶︎縦列型リスト表のピボットテーブル集計の限界
縦列型リスト表のまま集計するとどうなる?
集計できない項目がある

8.3▶︎縦列型リスト表のリスト表への変更の仕組み
事例の縦列型リスト表とそのリスト表
変更手順をイメージで理解する
INDEX関数のパラメータを理解する

8.4▶︎縦列型リスト表のリスト表への変更手順
手順1:共通リスト値のパラメータを入力する
手順2:共通リスト値のINDEX関数を入力する
手順3:上段値のパラメータを入力する
手順4:上段値のINDEX関数を入力する
手順5:下段値のパラメータを入力する
手順6:下段値のINDEX関数を入力する
手順7:列を並べ替えて行を抽出する

8.5▶︎縦列型リスト表のリスト表への実戦的な変更
リスト表への実戦的な変更とは
多量のパラメータを数式で入力する
縦列型リスト表をテーブル化する
INDEX関数をメンテナンスフリー化する

9章
帳票のリスト表への変更
「帳票」とは、1件のデータが1枚の書類や1画面に見やすくレイアウトされているものです。例えば、次のようなものが挙げられます。
▶︎見積書
▶︎社員情報入力表
▶︎社員スキル入力表

9.1▶︎帳票とは何か
帳票ってなに?
「見積書」の帳票
「社員情報入力表」の帳票
「社員スキル入力表」の帳票
帳票のままでは電卓で集計する羽目になる

9.2▶︎少量の帳票データのリスト表への変更
事例の帳票とそのリスト表
リスト化の仕組みを理解する
手順1:作業帳票と作業リスト表をリンクする
手順2:帳票データをリスト表に蓄積する

9.3▶︎同一ファイルの帳票データのリスト表への変更
事例の帳票とそのリスト表
リスト化の仕組みを理解する
手順1:帳票データをセル番地で間接参照する
手順2:作業帳票と作業リスト表をリンクする
手順3:帳票データをリスト表に蓄積する

9.4▶︎別ファイルの帳票データのリスト表への変更
事例の帳票とそのリスト表
リスト化の仕組みを理解する
手順1:帳票ファイル情報を出力する
手順2:帳票データをセル番地で間接参照する
手順3:作業帳票と作業リスト表をリンクする
手順4:帳票データをリスト表に蓄積する

10章
より簡単・軽快に
この章では、INDEX関数ではなくピボットテーブルを利用して一般表をリスト表に変更する方法と、INDEX関数を大量に入力したファイルのファイルサイズを小さくする方法を説明します。

10.1▶︎ピボットテーブルによるリスト表への変更
ピボットテーブルによるリスト表への変更とは
単純並列表の数値データの場合だけの特典
手順1:元表にIDを入力する
手順2:空のピボットテーブルを作成する
手順3:ピボットテーブルの項目を設定する
手順4:ピボットテーブルを編集する
手順5:ピボットテーブルをレイアウトする
手順6:ピボットテーブルを値に変換する
手順7:表を編集する。

10.2▶︎多列表のピボットテーブルによる
リスト表への変更
多列表のピボットテーブルは手間がかかる
隠し機能でピボットテーブルを作成する
共通リスト値は1列だけにする
手順1:隠し機能でピボットテーブルを作成する
手順2:ピボットテーブルをレイアウトする
手順3:ピボットテーブルの集計値を日付に変更する
手順4:ピボットテーブルを編集する
手順5:ピボットテーブルを値に変換する

10.3▶︎多量データへの対応
小さな練習表で操作に慣れておく
リスト表の数式を値に変換する
ブックファイルをバイナリ形式で保存する

COLUMNピボットテーブルがすばらしい点:その1
ピボットテーブルは、表計算機能と並びExcelの最強機能と呼べるものです。このピボットテーブルには、すばらしい点が幾つもあります。本書では、それを五つのコラムに分けて説明したいと思います。
先ずここでは、ピボットテーブルがすばらしい点その1として、関数入力が不要で集計値が正確だということを述べたいと思います。
Excelでデータを集計するには、大きく分けると三つの方法があります。「関数」、「集計機能」、「ピボットテーブル」です。集計関数には、SUM、SUMIF、SUMIFS、COUNT、COUNTIF、COUNTIFS、DSUM、DCOUNT、SUMPRODUCTなど、多くの種類があります。これらを入力するには、書式を理解して引数を正確に記述しなければなりません。それを行えなければ、正しい集計値を得ることができません。
集計機能とは、「データ」リボンの「統合」と「小計」です。これらは、ある特定の場面に、はまると便利ですが、その頻度はあまり高くないのが現状です。
最後のピボットテーブルですが、これはそもそも関数の入力やダイアログボックスの指定が不要です。しかも、集計値は、常に正しい値になります。関数のように間違えることはありません。少量の集計値を求めるには、関数が便利です。しかし、多量の集計値を求める場合は、圧倒的にピボットテーブルの方に分があります。

 

目次 – これならできる 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

目次 – できるポケット Excelピボットテーブル 基本&活用マスターブック Office 365/2019/2016/2013対応

無料電話で確認もできる!

Excelにおける業務を自動化するためのテクニックが満載です。本書のピボットテーブルをマスターすることで、多角的なデータ分析が可能になります。

本書の読み方

レッスン
見開き完結を基本に、やりたいことを簡潔に解説しています。 各レッスンには、操作の目的を記すレッスンタイトルと機能名で引けるサンタ イトルが付いているので、すぐ調べられます。
練習用ファイル
手順をすぐに試せる練習用 ファイルをレッスンごとに用意しています。
動画で見る
レッスンで解説している操 作を動画で見られます。詳しくは3ページを参照してください。
左ページのつめでは、章タイトルでページを探せます。
解說
操作の要点やレッスンの概 要を解説します。解説を読 むだけでレッスンの目的と 内容が分かります。

手順
必要な手順を、すべての 画面と操作を掲載して解説
(手順見出し)
おおまかな操作の流れが理解できます。
解説
操作の前提や意味、操作結 果に関して解説しています。
操作説明
「○○をクリック」など、それぞれの手順での実際の操作で す。番号順に操作してください。
Hint!
レッスンに関連したさまざまな機能や一歩進んだテク ニックを紹介しています。
右ページのつめでは、知りたい機能でページを探せます。
図解
練習用ファイルの「Before] (操作前)と「After」(操作 後)の画面です。レッスンで 学ぶ操作や機能の概要がひと目で分かります。
※ここに掲載している紙面はイメー ジです。実際のレッスンページと は異なります。

目次

できるシリーズ読者サービスのご案内
本書の読み方
練習用ファイルについて
第1章ピボットテーブルとリスト作成の基本を知ろう
1ピボットテーブルの特徴を知ろう
ピボットテーブルの特徴
2ピボットグラフの特徴を知ろう 2
ピボットグラフ
3ピボットテーブルの各部の名称を知ろう 3
各部の名称
4集計データを集めるときのルールを知ろう
リストの入力
5レッスンテキストファイルをExcelで開くには
テキストファイルウィザード
6データの入力ミスや表記揺れを統一するには
テーブル
7特定の文字列を別の文字に置き換えるには7
検索と置換
ステップアップ!
クイック分析ツールを使ってテーブルに変換できる

第2章 売り上げのリストを集計表に変身させよう
8ピボットテーブルの作成手順を知ろう
ピボットテーブルの作成
9集計元のデータを修正するには一
データの更新
10集計元のデータを後から追加するには
データソースの変更
11集計値の明細データを一覧表形式で確認するには
明細データの表示
12指定した商品のみの集計結果を表示するには
ドロップダウンリスト
ステップアップ! おすすめピボットテーブルを使ってピボットテーブルを作成できる

第3章 データをあらゆる視点で分析しよう
13「顧客別」ではなく「商品別」に集計するには
フィールドエリアの変更
14「商品分類」を掘り下げて「商品別」に集計するには
中分類の追加
15データの項目を掘り下げて集計するには
ドリルダウン
16大分類ごとにデータを集計するには
ドリルアップ
17売上金額の高い順に地区を並べ替えるには並べ替え
18指定した順番に商品を並べ替えるには
項目の移動
19特定のリストを元に項目を並べ替えるには19
ユーザー設定リスト
20売上金額の上位5位までの商品を集計するには
トップテンフィルター
21指定したキーワードに一致する商品を集計するには
ラベルフィルター
22日付をまとめて集計するには
日付のグループ化
23レッスンいくつかの商品をまとめて集計するには
文字のグループ化
24集計表の項目名を変更するにはフィールド名の変更
ステップアップ! クリックやドラッグ操作で[レイアウトセクション」の動きをマスターしよう

第4章 集計方法を変更してデータを分析しよう
25「月別」の注文明細件数を求めるには
データの個数、
26「商品別」の売上の割合を求めるには26 行方向の比率
レッスン売上金額の前月比を求めるには
27集計値の累計を求めるには
比率
29複数の集計結果を並べて表示するには
複数のフィールドの追加
30数式のフィールドを挿入して手数料を計算するには
集計フィールドの挿入
31項目をグループ化して構成比を求めるには
集計アイテムの挿入
32レッスン小計や総計行を非表示にするには 32
小計、総計
33すべての分類に含まれる商品を合計して表の下に表示するには
詳細項目の小計
34指定した「地区」や「商品分類」のみの集計結果を表示するには
レポートフィルター
35「地区別」の集計表を別のワークシートに作成するには
レポートフィルターページの表示
36集計値の項目の後に空白行を入れて見やすくするには
空白行の挿入
ステップアップ! レポートの種類を理解する

第5章 集計表のデータを読み取りやすくしよう
37分類や商品名のレイアウトを変更するには
レポートのレイアウト
38集計表のデザインを簡単に変更するには
ピボットテーブルスタイル
39数値にけた区切りのカンマを付けるには
セルの表示形式
40指定した値を上回ったデータのみ色を付けるには
セルの強調表示ルール
41平均値を上回ったデータのみ色を付けるには
上位/下位ルール
42条件に応じて数値をマークで目立たせるには
アイコンセット
43集計値の大きさによってセルの色を塗り分けるには
カラースケール
44集計値の大きさによって色付きのバーを表示するには
データバー
45集計方法を変更しても書式が保持されるようにするには
レイアウトと書式
ステップアップ! 複数の条件付き書式を設定する

第6章集計表をさまざまな角度から「グラフ化」しよう
46ピボットグラフの各部の名称を知ろう
グラフ要素
47ピボットグラフを作成しよう
ピボットグラフ
48グラフの種類を変更するには
グラフの種類の変更
49グラフに表示する項目を入れ替えるには
フィールドの入れ替え
50グラフ全体のデザインを変更するには
グラフスタイル
51円グラフでデータの割合を見るには
円グラフ
ステップアップ! おすすめグラフを使ってみよう

第7章 集計対象をワンクリックで切り替えよう
52レビスマスライサーで特定の地区の集計結果を表示するには
スライサー
53特定の地区から顧客別に売上金額を表示するには
複数のスライサー
54スライサーのデザインを変更するには
スライサースタイル
55タイムラインで特定の期間の集計結果を表示するには
タイムライン
ステップアップ! – データがないアイテムの表示方法を指定する

索引
できるサポートのご案内

練習用ファイルについて
本書で使用する練習用ファイルは、弊社Webサイトからダウンロードできます。 練習用ファイルと書籍を併用することで、より理解が深まります。
▼練習用ファイルのダウンロードページ https://book.impress.co.jp/books/1119101142
●本書に掲載されている情報について
・本書で紹介する操作はすべて、2019年11月現在の情報です。
・本書では、「Windows 10」と「Office Home & Business 2019」がインストールされているパソコンで、インターネットに常時接続されている環境を前提に画面を再現しています。
・本書は2019年11月発刊の「できるExcelピボットテーブル Office 365/2019/2016/ 2013対応 データ集計・分析に役立つ本」の一部を再編集し構成しています。重複する内 容があることを、あらかじめご了承ください。
「できる」「できるシリーズ」は、株式会社インプレスの登録商標です。 本書に記載されている会社名、製品名、サービス名は、一般に各開発メーカーおよびサービス提供元の 登録商標または商標です。なお、本文中には様およびRマークは明記していません。

第1章
ピボットテーブルとリスト作成の基本を知ろう
ピボットテーブルの集計表は、売り上げ のデータやアンケート結果などのリスト から作成します。したがって、正しく集 計を行うためには、集計表の元になるリ ストをきちんと整理しておくことが重要 です。この章では、ピボットテーブルや ピボットグラフの特徴のほか、リストや テーブル作成の基本を紹介します。

目次 – スピードマスター 1時間でわかる エクセルピボットテーブル 上級職の必須ツールを最短でマスター

まずはササッと理解する!

本書は、ピボットテーブルでの根幹となるところを、まずは1時間で理解することを念頭においております。本書は具体的な操作などは極力おさえ、まずは大まかなコツからはいることで、理解を促します。もちろん少しかじったことがある方は更にピボットテーブルの理解を助けます。

本書について

・「新感覚」のパソコン解説書
本書は「1時間で読める・わかる」をコンセプトに制作された、まったく新しいパソコン解説書です。「1時間でなにができる?」と疑問を感じているかもしれませんが、ビジネスの現場で必要とされるパソコンの操作はそれほど多くはありません。
ビジネスの現場で必要とされる操作に絞ることで、1時間で読んで理解することができるのです。
また、従来のパソコン書は具体的な操作解説が中心ですが、本書はコツやしくみの解説に重点を置いています。コツやしくみを理解しない場合、ほんの少しでも状況が異なると、とたんに操作がおぼつかなくなってしまいます。
移動時間でもサッと読めるように、縦書きスタイルの読んで・わかる新感覚なパソコン解説書です。

・コツさえわかれば、ピボットテーブルは自由自在
ピボットテーブルは非常に高機能かつ有効なツールです。また、上級職のビジネスマンにとってはなくてはならない分析ツールでもあります。しかし非常に高機能であるために、ピボットテーブルを使いこなすことは難しいと敬遠されがちです。
実際はそんなことはありません! ただし、ピボットテーブルは誰でもどこでも使えるというツールではありません。ピボットテーブルには向き不向きがあるのです。「ピボットテーブルのキモとなる「コツやしくみ」を正しく理解すれば、何が向いていて何が向いていないかすぐにわかるようになります。そして、そのキモとなる「コツやしくみ」は、1時間で読んで理解できるのです。本書を読んで、ピボットテーブルを駆使してライバルに差を付けましょう!
本書はエクセル2016/2013/2010を対象としています。

目次

1章 期待通りのピボットテーブルを 作成するための条件
01 「ピボットテーブルはとても便利!」といわれる理由
02 クロス集計と通常の集計は何が違う?
03 ピボットテーブルは万人のための魔法のテーブルにあらず
04 ピボットテーブルの作成前にエクセルシートを確認しよう
05 こんなデータがあると集計できない! 元の表のNGを徹底チェック
06 集計に必要のない要素は省いておく
コラム:こんなデータがピボットテーブルの元の表にはべスト

2章 ピボットテーブルの 原理と構造を知ろう
07 作成するだけなら3秒程度
08 これからの作業はドラッグ&ドロップだけ
09 最終的に何が集計されるのか
10 フィールドの選択 これですべてが決まる
11 行に設定するフィールドは1つだけではない
12 列に指定したフィールドの値が集計される
13 計算式をピボットテーブルに作成する際の注意点
コラム:ピボットテーブルを二次利用するには

3章 クロス集計してみよう! 実践ピボットテーブル
14 集計の元となるフィールドを設定する
15 行フィールドの階層レベルを設定する
16 日付での集計は非常に重要にも関わらずとてもかんたん!
17 クロス集計の結果を表示させる
18 自分で作った計算式で知りたい値を表示する
19 出来上がったピボットテーブルを見やすくカスタマイズする
コラム:完成したピボットテーブルを再度確認

4章 ピボットテーブルからさらに情報を引き出す5つのツール
20 指定した条件に合うものだけを集計・表示させる
21 「行ラベルのフィルター」を活用する
22 範囲があるフィールドは指定した単位に区切って集計できる
23 数値で区切る「グループ化」
24 日付で区切る「グループ化」
25 作成した集計表に組み込んでいない属性を利用する。
26 「スライサー」を使ってみると
27 直感的に一定期間の集計を把握できる「タイムライン」
28 「タイムライン」を使ってみると
コラム:列方向のグループ化

索引

【Excel 2016/2013/2010 各バージョン間の主な違い】
ピボットテーブルはExcelのバージョンによって、差異があります。以下の表 に各バージョンの主な違いを示します。

機能/名称 2016 2013 2010
分析タブの名称 分析タブ 分析タブ オプションタブ
日付のグループ化 自動で行われる 操作が必要 操作が必要
タイムライン 利用できる 利用できる 利用できない

【免責】
本書に記載された内容は、情報の提供のみを目的としています。したがって、本書を用いた運用は、必ずお客様自身の責任と判断によって行ってください。 これらの情報の運用の結果について、技術評論社および著者はいかなる責任も負いません。本書記載の情報は、2016年6月末日現在のものを掲載していますので、ご利用時には、変更されている場合もあります。また、本書はWindows 10とExcel 2016を使って作成されており、2016 年6月末日現在での最新バージョンをもとにしています。ソフトウェアはバージョンアップされる場合があり、本書での説明とは機能内容や画面図などが異なってしまうこともあり得ます。本書ご購入の前に、必ずバージョン番号をご確認ください。OSやソフトウェアのバージョンが異なることを理由とする、本書の返本、 交換および返金には応じられませんので、あらかじめご了承ください。以上の注意事項をご承諾いただいた上で、本書をご利用願います。これらの注意事項に関わる理由に基づく、返金、返本を含む、あらゆる対処を、技術 評論社および著者は行いません。あらかじめ、ご承知おきください。
【商標・登録商標について】
本書に記載した会社名、プログラム名、システム名などは、米国およびその他の国における登録商標または商標です。本文中ではTM、Rマークは明記しておりません。