パソコン教室・IT資格マイクロソフトOffice(オフィス)使い方講座Excelレッスンノート GetPivotData関数

GetPivotData関数

Excel(エクセル)使い方講座のレッスンノート

レッスンノートって?レッスンノートって?

お仕事で実施している集計作業が適切かを見ていただきたいとのご要望でした。
しかし残念ながら、実際の集計ファイルを開くことができず、具体的なアドバイスは断念せざるをえませんでした。

実務の参考になることを願いながら、またご自身のExcel理解のレベルを勘案しながら、通常のレッスンからいくつかのトピックを取り上げて、その内容を理解していただくようにしました。

ピボットテーブルでの更新とデータソースの変更ボタンの使い方を確認しました。
串刺し計算と統合集計を見ていただきました。
Vlookup関数の第3引数、第4引数の使い方を確認していただきました。
ピボットテーブルの成形に使用するGetPivotData関数を紹介しました。
以下は、GetPivotData関数のコピー方法の説明ですので参考にしてください。

Vlookupなど、他表を検索して参照する関数にはいくつかの使い道があります。その一つは表の成形で、最終的に求められている形の表を作っておき、そこにVlookup関数を使って元表から欲しいデータを抽出します。

元表がピボットテーブルの場合は、同様のことをGetPivotData関数を使って実現します。ピボットテーブルは集計表ですから、元表にないデータはピボットテーブルにも出てこないため、ピボットテーブルを成形する必要は比較的多く起こります。

GetPivotData関数をセルに入力するのはいたって簡単で、目的のセルに数式の印である=を入力したあと、そこに表示させたいピボットテーブルのセルをマウスでクリックするだけです。それによってGetPivotDataという関数記号を含めて、式がすべて完成します。(今回は最初、関数記号を手入力して失敗してしまいましたね。)

しかしGetPivotData関数では、引数のほとんどがダブルクォーテーションで囲まれた文字データで入るため、これをコピーして使い回すことができません。成形表は通常、縦・横にたくさんの項目を持っていますから、その一つ一つについて=を入力してピボットテーブルのところに移ってクリックするというのは現実的ではありませんし、そのような操作は間違いの原因にもなります。
ここはやはり式コピーができるように、文字列になっている引数をセル番地に置きかえて適切に$マークをつける作業が必要になります。

GetPivotDataの引数は(データを取り出すピボットテーブルの列名、ピボットテーブルの開始位置、データを検索するピボットテーブルの列名1、検索値1、データを検索するピボットテーブルの列名2、検索値2)という構成になっています。今回扱ったのはこのように、検索値が2個で引数が6個となるものでした。

この中で第2(開始位置)引数は、すでに絶対参照として作成されていますので自動入力されたままでコピーできます。
取出列を指定する第3、第5引数、検索値である第4、第6引数については、文字列になっている引数を適切なセル参照に変更しなければなりません。

$マークをどのようにつけるかということはExcelを扱う上で重要な点ですが、この点はすでに十分理解されていましたね。上の引数をセル番地に変えた上で適切な参照方式を設定してください。

最後に第1引数の問題があります。ここを他の引数のようにセル番地に書き換えるだけでは参照エラーとなってしまいます。これはこの第1引数が「明示的な文字列」でなければならないためで、GetPivotDataのヘルプでは「第1引数は二重引用符で囲まれます」と表現されています。これはセル内容が文字であればよいというのではなく、結果が必ず文字列となるようなセル番地の書き方でなければならないということです。

参照セルを強制的に文字列化する方法は二つです。一つはText関数を使う、もう一つは文字列演算によって空の文字列を加えるということです。
テキスト関数を使う場合は、TEXT(ピポット集計!C$4,"@")などとします。(ここでの第2引数は表示形式の指定で@は文字列としての表示ということです)
後者の方法では第1引数部分を、ピボット集計!C$4 & "" とします。セル番地の前か後ろに""をつけることでその値は強制的に文字列になります。(式の結果を強制的に数値にしたい場合、式の後に+0などをつけることがありますが、それと同様のものと理解してください。)

さて、今回は目的の課題を果たすことができませんでしたので、当レッスンがお役に立てると判断されましたら、またご連絡いただければと思います。体験レッスンを受講していただきありがとうございました。
このレッスンノートを書いたコーチ

データベースシステム開発会社を経営。初心者の壁を乗り越えるお手伝い

ExcelPro大親切スクール
星加弘文 (Excel)

渋谷・新宿・分倍河原・吉祥寺・笹塚・明大前・下高井戸・千歳烏山・調布・府中・下北沢・...

レッスンノート ページ先頭へ

Excel情報

サイタのExcel講師がブログを通して、Excel情報を発信。更新情報のチェックはこちらから!

twitter

Excelの先生

@ExcelCyta