何万件ものデータやピボットテーブルで苦しんでいる人のための 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など、多くの種類があります。これらを入力するには、書式を理解して引数を正確に記述しなければなりません。それを行えなければ、正しい集計値を得ることができません。
集計機能とは、「データ」リボンの「統合」と「小計」です。これらは、ある特定の場面に、はまると便利ですが、その頻度はあまり高くないのが現状です。
最後のピボットテーブルですが、これはそもそも関数の入力やダイアログボックスの指定が不要です。しかも、集計値は、常に正しい値になります。関数のように間違えることはありません。少量の集計値を求めるには、関数が便利です。しかし、多量の集計値を求める場合は、圧倒的にピボットテーブルの方に分があります。