わえなび ワード&エクセル問題集

当サイトにリンクを張るのは自由です。WordとExcelの練習問題の動画を「Youtube」で無料公開しています。みんなで勉強しましょう!

【Excel】ピボットテーブルの中のセルを数式で参照してもよいか

Excelのピボットテーブルとは、リスト形式の表を元にして、分類ごとに集約したり集計したりする機能です(※参考:ピボットテーブルは集計の前の基礎トレーニングが大事!ピボットテーブル総まとめ)。

ピボットテーブルの機能を用いて集計した後で、このピボットテーブルの計算結果を数式の中で使いたい場合があります。ピボットテーブルのセルを数式で利用する場合の問題が2つあります。

  • この使い方は正しいのか?
  • リスクを承知した上でどうしても数式を使いたい場合はどうすればよいか?

f:id:waenavi:20190108235855j:plain

 

そこで、今回はピボットテーブルの中のセルを数式で使うときの注意点について出題します。

 

「ピボットテーブル」シリーズ(この記事は第6回です)
  1. ピボットテーブルは集計より「集約」の仕方を理解せよ
  2. ピボットテーブルのクロス集計の基本は、行集計である
  3. ピボットテーブル集計の練習問題サンプルデータ動画解説付き
  4. ピボットテーブルで度数分布表(ヒストグラム)を作成する方法
  5. ピボットテーブルにはミスを発見するという使い方がある
  6. ピボットテーブルの中のセルを数式で参照してもよいか
  7. 【まとめ】ピボットテーブルは集計の前の基礎トレーニングが大事!ピボットテーブル総まとめ
  8. 【応用事例】ルー大柴さんが過去11年間にブログで使用した頻出ルー語TOP100を発表!カッ!(ピボットテーブル)

 

目次

1.ピボットテーブルを編集してはいけない

問題

このリストからピボットテーブルを作って、受験者別の平均点を求めました。

f:id:waenavi:20190108233337j:plain

f:id:waenavi:20190108233339j:plain

 

この平均点に10点を加算するため数式を入力すると、長い計算式が表示されますが、正しく10点加算されます。

f:id:waenavi:20190108233343j:plain

 

しかし、オートフィルをすると同じ値が表示されてしまいます。この現象について以下の各設問に答えなさい。

f:id:waenavi:20190108233346j:plain

 

ピボットテーブルのセルの色を変えたり、外側に文字を入力してもよいか述べなさい。

f:id:waenavi:20190108233620j:plain

 

解説

ピボットテーブルは元の表と連動するものであり、集計方法を変えることによって、形が大きく変わります。

f:id:waenavi:20190108233950j:plain

 

原則として、元のデータを変更することは許されますが、それと連動してできるピボットテーブルのデータを書き換えることは許されません。

f:id:waenavi:20190108233953j:plain

 

文字の色を変えたり、塗りつぶしをしたりすることも許されません。

f:id:waenavi:20190108233956j:plain

 

ピボットテーブルは計算するだけであって、見た目をよくしようと思ってはいけません。

f:id:waenavi:20190108233959j:plain

 

また、ピボットテーブルの大きさも大きく変わりますから、外側であってもできるだけ文字入力や数式の入力は控えるべきです。

f:id:waenavi:20190108234002j:plain

 

2.ピボットテーブルの再利用

問題

さきほどのピボットテーブルで、ピボットテーブルのデータを再利用をするにはどうすればよいか述べなさい。

f:id:waenavi:20190108233633j:plain

 

解説

ピボットテーブルで求められたデータから資料を作ることを「再利用」といいます。

f:id:waenavi:20190108234210j:plain

 

ピボットテーブルを再利用するには、コピーをして、別のシートに値の貼り付けをして利用します。

f:id:waenavi:20190108234213j:plain

f:id:waenavi:20190108234218j:plain

 

これによって元の表との連動を切り、独立して資料を作ることができます。

f:id:waenavi:20190108234229j:plain

 

3.GETPIVOTDATA関数の生成

問題

さきほどのピボットテーブルで、ピボットテーブルのセルをやむを得ず使いたい場合はどうすればよいか述べなさい。

f:id:waenavi:20190108233645j:plain

 

解説

資料を作ることなく、ピボットテーブルの外側のセルで、一時的に計算をしたい場合があります。

f:id:waenavi:20190108234609j:plain

 

例えば、B列の数値に10点を加算します。このとき、GETPIVOTDATA(ゲットピボットデータ)と表示されます。

f:id:waenavi:20190108234612j:plain

 

かっこの中は絶対参照になっているか、または文字列だけであり、相対参照はありません。

f:id:waenavi:20190108234615j:plain

 

相対参照が無いので、オートフィルをしてもデータが変わることはありません。

f:id:waenavi:20190108234618j:plain

 

このGETPIVOTDATAは、おもに、ピボットテーブルの中で1か所のデータを取り出すときに使います。

f:id:waenavi:20190108234621j:plain

 

相対参照が無いので、原則としてオートフィルをしてはいけません。

f:id:waenavi:20190108234624j:plain

 

キーボードで直接計算式を入力します。

f:id:waenavi:20190108234627j:plain

 

これは相対参照なので、オートフィルをすると正しく計算できます。

f:id:waenavi:20190108234632j:plain

 

別解

ピボットテーブルツール、分析のピボットテーブルオプションの中の、「GETPIVOTDATAの生成」にチェックが入っています。

f:id:waenavi:20190108234859j:plain

 

このチェックがあると、クリックしたときにGETPIVOTDATAが表示されます。チェックを外します。

f:id:waenavi:20190108234903j:plain

 

これで、クリックすると相対参照の式になります。

f:id:waenavi:20190108234906j:plain

f:id:waenavi:20190108234911j:plain

 


解説は以上です。


4.動画版はこちら(無料)

この記事は、わえなびファンダメンタルExcel Program7-25-3 の動画の内容を書き起こし、加筆修正したものです。

わえなび

 

なお、データベース関連の動画(完全版)は、Youtubeにすべて無料で公開しております。ぜひ、ご覧ください。

ファンダメンタルExcel 7 データベース(全28回)【わえなび】 - YouTube
https://www.youtube.com/playlist?list=PLRaY8kd5CoxOnpijKZLSbMRv37GQXv1Ko

 

「ピボットテーブル」シリーズ(この記事は第6回です)
  1. ピボットテーブルは集計より「集約」の仕方を理解せよ
  2. ピボットテーブルのクロス集計の基本は、行集計である
  3. ピボットテーブル集計の練習問題サンプルデータ動画解説付き
  4. ピボットテーブルで度数分布表(ヒストグラム)を作成する方法
  5. ピボットテーブルにはミスを発見するという使い方がある
  6. ピボットテーブルの中のセルを数式で参照してもよいか
  7. 【まとめ】ピボットテーブルは集計の前の基礎トレーニングが大事!ピボットテーブル総まとめ
  8. 【応用事例】ルー大柴さんが過去11年間にブログで使用した頻出ルー語TOP100を発表!カッ!(ピボットテーブル)

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]