エクセル2013、別々の表からピボット集計を行う(リレーションシップ)

リレーショナルデーターベースはアクセスなどで作ります。エクセルは基本的に集計表機能と思っていたのですが、エクセルでもリレーションを設定して異なる複数の表からピボット集計ができるらしいので、試してみました。
しかし「売上記録」シートの「売上数」と「果物詳細」シートの「単価」を乗算する「売上金額」フィールドが作れないので、手動でピボット集計に追加しました。どうも2013では、リレーションシップを設定すると新たなフィールドを追加できない感じです(私の理解が浅いのかも、、、、)。30-エクセルリレーション

(このようなリレーショナルデータベースはエクセルでは望めない感じ)
1-エクセルリレーション

【エクセル2013、リレーショナルデータベールとして使う】
売上シートと商品シートから売上ピボット集計を行ってみました

作業手順としては、以下の順序で行ないました
1.各ワークシートの表をテーブルに設定します
2.各テーブルをリレーションシップで連結します
3.ピボット集計を実行
4.ピボット集計に売上金額フィールドを手動で追加

1.エクセルの表(シート)をテーブルに設定
使用したシートは単純なものです。
「売上集計」シートの項目(フィールド)は「日付」、「商品」と「売上数」です。
またもう一つは「果物詳細」シートで、「商品名」、「産地」と「単価」のフィールドを持っています。
2-エクセルリレーション

1)売上記録シートの表をテ-ブルとして設定
売上記録シートの表を選択して、「ホーム」リボンで「テーブルとして書式設定」をクリックし、表示された表の中から適当な書式をクリック。
「テーブルとして書式設定」画面が表示されるので、先頭行をテーブルの見出しとして使用にチェックを入れ「OK]。
3-エクセルリレーション4-エクセルリレーション

同様に「果物詳細」シートの表もテーブルとして設定
6-エクセルリレーション

2)テーブル名を付ける
テーブル内のセルをクリックするとリボンに「デザイン・テーブルツール」が表示・選択される。
ここでリボン左橋にある「テーブル名」欄の名前を「売上記録」とする。
5-エクセルリレーション

同様に果物詳細シートの表もテーブル名を「果物詳細」とする
7-エクセルリレーション

2.リレーションシップを設定
リボンの「データ」から「リレーションシップ」をクリック
14-エクセルリレーション

・「リレーションシップの管理」画面が開くので、「新規作成」をクリック。
・「リレーションシップの作成」画面となるので、テーブルの右端の下向き▼をクリックし、表示されたテーブルから「売上記録」をクリック。
・そして列の右端の下向き▼をクリックし、表示されたフィールドから「商品」をクリック8-エクセルリレーション

関連テーブルは「果物詳細」を選び、関連列のフィールドは「商品名」とする9-エクセルリレーション

これで二つのテーブルは商品と商品名フィールドでリレーションシップが設定された10-エクセルリレーション

3.ピボット集計
リボンの「挿入」から「ピボットテーブル」をクリック
15-エクセルリレーション

開いた「ピボットテーブルの作成」画面で、(テーブルは「売上記録」であることを確認)、「外部データソースを使用」にチェックを入れ、「接続の選択」をクリック。
開いた「既存の接続」画面で、「テーブル」タブを選択、すべてのテーブルから「果物詳細」を選び、「開く」
11-エクセルリレーション

ピボットテーブルの作成」画面へ戻るので、ピボット作成先を「新規ワークシート」として「OK」
12-エクセルリレーション

新しいワークシート(Sheet3)ができ、ピボット集計の準備が整う
16-エクセルリレーション

「果物詳細」テーブルの「産地」「商品名」「単価」にチェックを入れる。「産地」「商品名」は行に、「単価」は値に表示させる。
「売上記録」テーブルは「売上(数量)」にチェックを入れて、値に表示させる。
18-エクセルリレーション

4.「売上金額」フィールドを手動設定
折角のピボット集計だが「売上金額」が集計されないと意味が少ない。
よく分からないが2013ではリレーションシップを設定すると新たな集計フィールドは作れないようだ。40-エクセルリレーション

仕方がないのでピボット表に「売上金額(円)」フィールドを作成して、一番上のデータセル(オレンジ行)に「=B5×C5」と入力。以下、最後の柿行まで「単価×売上(数量)」を入力する。
追加した列(「売上金額」フィールド)の各セルの書式を見易くする(数字には桁区切り記号を入れるなど)。
13-エクセルリレーション

ピボット集計の中で「新しいフィールド」(売上金額)が作れて、数式「単価×売上(数量)」が設定できると思ってたんだが、できなかった。
17-エクセルリレーション

広告
カテゴリー: エクセル, Office(オフィス) タグ: , , パーマリンク

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中