目次
1. GETPIVOTDATA関数が自動で入力される仕様と実務上の不都合
Excelのピボットテーブル内にある数値を、テーブル外の計算式で参照しようとしてセルをクリックすると、自動的に「=GETPIVOTDATA(“売上”,$A$3,…)」といった長い関数が入力されます。これは、ピボットテーブルのレイアウトが変更(並べ替えや項目の追加)されても、目的のデータを正確に追いかけ続けるためのExcel標準の支援機能です。
しかし、この関数には大きな弱点があります。引数が特定の項目名(文字列)で固定されているため、数式を入力した後に下方向へ「オートフィル」でコピーしても、参照先が移動せずすべて同じ計算結果になってしまいます。通常の「=B5」といったシンプルなセル参照に戻したい場合、Excelの裏側で動いている「関数の自動生成スイッチ」を切る必要があります。
2. 手順①:リボンメニューから「関数の生成」を素早くオフにする
現在作業中のExcel画面から、マウス操作だけで最も簡単に設定を変更する手順です。
- 作成済みのピボットテーブル内のどこでも良いので、セルを一つクリックします。
- 画面上部に新しく表示される 「ピボットテーブル分析」 タブをクリックします。
- 左端にある「オプション」という文字のすぐ右側にある 小さな下向き矢印(▼) をクリックします。
- メニューの中にある 「GetPivotData の生成」 をクリックして、チェックが入っていない状態にします。
設定変更後は、ピボットテーブル内のセルをクリックしても「=B5」のような通常のセル参照が入力されるようになります。これにより、数式のコピーが自由に行えるようになります。
3. 手順②:Excelオプションから「恒久的に」機能を無効化する
新しいブックを作るたびに設定を変えるのが面倒な場合は、Excel自体の動作設定を変更して、この機能が最初から動かないように固定します。
- Excelの 「ファイル」 タブをクリックし、左下の 「オプション」 を選択します。
- 左側のメニューから 「数式」 をクリックします。
- 「数式の作業」というセクション内にある 「ピボットテーブルの参照に GetPivotData 関数を使用する」 のチェックを外します。
- 右下の「OK」をクリックして設定を保存します。
4. 比較:通常のセル参照とGETPIVOTDATA関数の違い
| 機能 | 通常のセル参照(=B5) | GETPIVOTDATA関数 |
|---|---|---|
| オートフィル | 可能。コピーすると参照先も移動する。 | 不可。コピーしても同じ項目を参照し続ける。 |
| レイアウト変更 | 弱い。列や行がずれると計算が狂う。 | 強い。項目が移動しても正確に値を追う。 |
| 入力のしやすさ | 数式が短く、理解しやすい。 | 数式が非常に長く、手入力には不向き。 |
5. 豆知識:一時的に通常の参照を使いたい時の回避テクニック
設定を一切変えずに、今回だけは「=B5」形式で入力したいという場合は、マウスを使わない手法が有効です。
- セルの座標を手入力する:目的の数値がB列の5行目にあるなら、セルに直接「=B5」と打ち込みます。Excelはクリックイベントが発生した時のみ関数を生成するため、手入力であれば通常の参照として認識されます。
- ピボット外のセルから矢印キーで移動する:一度ピボットテーブル外のセル(例:A1)をクリックし、そこからキーボードの矢印キーで目的のセルまで移動します。この方法でも、自動生成を回避してセル番地を入力することが可能です。
まとめ:数式コピーの自由度を取り戻すための環境設定
GETPIVOTDATA関数は、データの構造を厳密に守るためには優れた機能ですが、柔軟に計算式をコピーしたい実務の現場では、しばしば作業効率を著しく下げる要因となります。設定一つでこの挙動は制御可能です。
数式のオートフィルを多用するユーザーであれば、Excelオプションからの恒久的な解除をおすすめします。一方で、ピボットテーブルの形が頻繁に変わる高度な分析シートを作成する場合は、この関数の特性を理解して使い分けるのが得策です。ツールが持つ自動化の仕様を正しく把握し、自分の作業スタイルに最適な入力環境を整えてください。