ピボットテーブルでコレクションの管理をする

ピボットテーブルで「データベース」を作成する

　レコードやCDなどの、コレクションが増えてくると何らかの「データベース」を作成して管理したくなります（うっかり同じものを買ってしまう）。

　データベースの作成に理想的なのはAccessですが、Accessは最初に決めたフォーマットを後に変更するのが面倒なことや、クラッシュが発生しデータが飛んでしまう（行方不明になる）など、データベースのメンテナンスに時間がかかってしまい個人のデータ管理にはあまり向いていません。

　ピボットテーブルの機能を使ったデータベースは、元が単純なリスト形式の「集計表」なので管理が簡単です。

コレクションから、特定の「アーティスト」のレコードを検索

　ピボットテーブルの機能「スライサー」を使って条件を指定すると、その条件に合う集計表が作成されます。

1. 「jannru」のスライサーで「jazz」を指定
2. 「索引」のスライサーで「 B 」を指定
3. 「jannru」と「索引」で絞り込まれ、対象の「Artist」が３人に絞り込まれます

　※ スライサーで指定する「条件」は複数の指定も可能です。

　　　

「ドリルダウン」で詳細な内容を表示

　集計表の内訳を知りたい「集計値」をクリックすると、ドリルダウンの機能でその内訳の集計表が作成されます。

　　　

コレクションの「リスト」を作成する

 　用意する「リスト」は、単純なリスト形式の表です。

　「リスト」をテーブル化しておくと便利です。

　　　

k-ohmori9616.hatenablog.com

「索引」のフィールドを作成する

　検索時に便利な「索引」のフィールドを作成します。

　「LEFT関数」を使って、「Artist」のフィールド（項目）の左１文字を切り出します。

　　　 関数 「 ＝LEFT(H2) 」

　　　

「入力規則」を設定する

　「type」のフィールド（項目）に入力規則を設定して簡単に入力できるようにします。

　　　

「type」のフィールド（項目）に、入力規則を設定

　あらかじめ用意しておいた、入力規則用の「データ」を選択します。

1. 列番号の部分をクリックして、「type」の列全体を選択状態にする
2. 「データ」タブを選択
3. 「データの入力規則」のラジオボタン「▼」をクリック
4. プルダウンリストから「データの入力規則」を選択

　　　

　　※ 列全体に入力規則を設定しておけば、データが増えても再設定不要です。

表示する「データ」の範囲を選択します

1. 「設定」タブを選択
2. 入力値の種類で「リスト」を選択
3. 元の値で、表示するデータを範囲選択します

　　　

「rank」のように、記号を入力するフィールドでも便利です

　　　

k-ohmori9616.hatenablog.com 

「jannru」で選択した内容で、「artist」の入力規則の表示を変える

　入力規則は便利な機能ですが、たとえばレコードのコレクションが増えてくると、「artist」の入力規則に表示される内容が多くなりすぎて選択するに手間がかかるようになります。

　リスト入力で「jannru」と「Artist」の「２つのフィールドを連動させる」ことで、リストに表示される内容が絞られるので、入力作業の効率が上がります。

　理想的には「Artist」の頭文字をキーにして表示させる方が効率的ですが、元データの作成が大変（※ 26種類のキーが必要）なのであまりお勧めしません。

　　　

k-ohmori9616.hatenablog.com

「jannru」の内容を、「行（横方向）」に入力します。

　　　

「jannru」別にデータを入力します

　jannru別のデータの入力は大変ですが、既に入力しているデータを利用すれば簡単にjannru別のデータを作成することができます。

　テーブル化された「リスト」を「フィルター機能」を使ってジャンルごとに表示させ、それをコピー・ペーストしてjannru別のデータを作成します。

1. 「jannru」の「フィルターボタン」を押します
2. フィルターのダイアログで「すべて選択」のチェックを外します
3. 「jazz」にチェックを入れます
4. 「Artist」のフィールド（項目）を全選択（※ CTRL＋SHIFT＋Enter）します
5. 全選択したまま「コピー」します　　　

　　　

「重複の削除」の機能を使ってデータを作成します

　フィルター機能を使ってJannruのみを表示させ、コピー・ペーストしてもそれには多くの重複データが含まれています。

　「重複の削除」の機能を使って、重複しているデータを削除することができます。

1. 「jazz」のフィールドに、コピーしたデータを貼り付けます
2. 「データタブ」を選択します
3. データツールの「重複の削除」を選択します
4. 重複の削除に関する警告のダイアログで「現在選択されている範囲を並べ替える」を選択
5. 「重複の削除」をクリック
6. 重複の削除のダイアログで「先頭行をデータの見出しとして使用する」のチェックを外す
7. 「OK」をクリック
8. 重複しているデータの内容が表示されるので、「OK」をクリック

　　　

同様の方法で、他の「jannru」も作成します

　　　

それぞれの「jannru」の範囲ごとに「名前」を付けます

　INDIRECT関数を使ってJannruとArtistを関連付けるためには、jannruごとの範囲に「jannru」の名前を付ける必要があります。

　　　

「選択範囲から作成」の機能で、それぞれの範囲に名前を付けます

　一つ一つ範囲に名前を付けるのは大変なので「選択範囲から作成」の機能を使って、それぞれの範囲に一括で名前を付けます。

1. リスト全体を範囲選択します
2. 「数式タブ」を選択します
3. 定義された名前のグループの「選択範囲から作成」を選択
4. 「選択範囲から名前...」のダイアログで「上端行」にチェックを入れます
5. 「OK」をクリック　　　

　　　

リスト入力を設定します

　データの入力規則を設定します。

　INDIRECT関数を使って、「jannru」と「Artist」のフィールド（項目）を連携させます。

　※ INDIRECT関数は「セルの範囲」を、文字列で指定できる関数です。

1. 「CTRL＋SHIFT＋Enter」の機能を使って、「Artist」のフィールド（項目）全体を選択します
2. 「データタブ」を選択します
3. データツールのグループで、「入力規則」を選択します
4. データの入力規則で「設定」タブを選択し、入力の種類を「リスト」にします
5. 元の値にINDIRECT関数を使った数式を入力します
6. 「OK」をクリック
7. １行目が「見出し」になっているのでエラーになりますが、そのまま続けます。

　　　

「jannru」に対応した「Artist」が表示されます

　INDIRECT関数で「jannru」と「Artist」のフィールド（項目）が関連付けられ、リスト入力用のラジオボタン「▼」をクリックすると、「jannru」に対応した「Artist」だけがドロップダウンリストに表示されます。

　　　

 ピボットテーブルを作成します

　元データとなる「リスト」からピボットテーブルを作成します。

　ピボットテーブルの作成はとても簡単です。

1. 「リスト」のどこか１カ所を選択します。
2. 「挿入タブ」を選択します。
3. 「ピボットテーブル」をクリック
4. ピボットテーブルの作成のダイアログで、テーブル範囲を確認します（※ 基本的にそのままでよい）
5. 「新規ワークシート」を選択
6. 「OK」をクリック

　　　

※ ピボットテーブルの作成方法はこちらも参照してください。

k-ohmori9616.hatenablog.com

ピボットテーブルの設定をします

　別シートに「空白」のピボットテーブルが作成されるので、フィールド（項目）をドラッグして配置していきます。

別シートにピボットテーブルが作成されます

　　　

「ボックス」に、フィールド（項目）をドラッグします

1. 「media」を「列」のボックスにドラッグ
2. 「jannru」を「行」のボックスにドラッグ
3. 「sauce」を「∑ 値」のボックスにドラッグ
4. 「Artist」を「行」のボックスの「jannru」の下にドラッグ
5. 「Title」を「行」のボックスの「Artist」の下にドラッグ

　　※ 「ボックス」内の上下の位置で、集計方法が変わるので注意

　　　

「jannru」と「Artist」と「Title」の複合集計の集計表ができました

　「jannru」と「Artist」と「Title」の３つの複合条件で集計した表ができました。

　単純なリスト形式の「表」が、簡単な設定をするだけで「データベース」になります。

　　　

「リスト」に検索機能（スライサー）を付加する

　ピボットテーブルの機能「スライサー」を使えば、簡単に検索することができます。

1. ピボットテーブル（集計表）のどこか１カ所を選択します。
2. 「ピボットテーブル分析タブ」を選択します（※ ピボットテーブルを選択していないと表示されません）
3. スライサーの挿入で「索引」にチェックを入れます
4. 「jannru」にチェックを入れます
5. 「Artist」にチェックを入れます

　　　

スライサーが作成されました

　「索引」「jannru」「Artist」の３つのスライサーが作成されました。

　　　

スライサーの「位置」と「色」を変更します

　操作しやすいように「位置」を変更し、「色」を変えて分かりやすくします。

1. 「スライサー」をドラッグして位置を変更します
2. 「色」を変更するスライサーを選択します
3. スライサーを選択すると「スライサーのタブ」が表示されるので選択します
4. スライサースタイルの中から「色」を選びます

　　　

ピボットテーブルで「レコード」を検索する

　ピボットテーブルで作成した「集計表」は、スライサーを使えば簡単に「集計の条件」を変えることができます。

　スライサーを使えば、データベースのように複数の条件を設定して目的の情報を検索し表示することができます。

「Bill Evans」を検索する

1. jannruのスライサーで「jazz」を選択
2. 索引のスライサーで「B」を選択

　　　

Artistで「Bill Evans」を選択する

　「jazz」と「B」の条件で5つに絞り込まれたArtistの中から「Bill Evans」を選択すると、集計表の表示が「Bill Evans」の情報のみになる。

1. Artistで「Bill Evans」を選択する
2. 集計表（ピボットテーブル）が、「Bill Evans」に関するものだけになる

　　　

「ドリルダウン」で詳細な情報を表示する

　ピボットテーブルでは「集計値」をダブルクリックすると、その「元になるデータ」が表示される「ドリルダウン」の機能が使えます。

　ドリルダウンで表示されるデータは、「別シート」に作成され集計表（ピボットテーブル）とは関連付けされていません。

　※ ドリルダウンで作成された「シート」を修正・変更しても元のデータは変更されないので注意が必要です。

　　　

　このように「ピボットテーブル」を使えば、簡単に検索可能なデータベースを作成することができます。

　今回は「スライサー」を紹介しましたが、ピボットテーブルにはその他にも「タイムライン」の機能があります。

　タイムラインを使えば「購入年月日」を使って情報を絞り込むこともできます。

　　　

「スライサー」の表示を変更する

　スライサーの初期設定では、条件に該当しない部分は「グレーアウト」で表示されますが、設定により「条件に該当しないものを非表示」にすることができます。

　　　

スライサーの「表示設定」で、データのないアイテムを「非表示」にする

1. SHIFTキーを押しながら「３つのスライサー」をクリックして選択状態にします（※ 個別に設定することもできます）
2. 「スライサータブ」を選択します（※ スライサーを選択しないと表示されません）
3. 「スライサーの表示設定」をクリックします
4. 「データのないアイテムを非表示にする」にチェックを入れます

　　　

 データのない「アイテム」が非表示になりました

　　　

k-ohmori9616.hatenablog.com