ピポットテーブルのデーターを別のブックで参照する

質問

ピポットテーブルのデーターを別のブックで参照する

エクセルの質問です。

例えばＡというブックがあり、シート名が「本部入金まとめ」とします。
このシートにはピポットテーブルのデータが貼り付けられております。
シート名「本部入金まとめ」のセルB5の値を別のブック「入金一覧表」のシート名「本部」のセルU4に反映させるにどうしたらよいのでしょうか？

「=」にてリンクしてみましが、エクセルを閉じて再度開くとエラーになってしまいます。

どのようにすればピポットのデーターを他のブックに反映させることができるのでしょうか？

ご教授をお願い致します。

ちなみにエクセルは2016、OSはWin7　32bitとなります。

回答No.3

nishi6 · ベストアンサー

エラーが出たということで、確認です。

まず、環境ですが、Excel2016ということですが、ピボットを含むブックが「45期受注a.xls」となっているということは、Excel97-2003ブックで、20年近く前の形式です。
もしかしたら、Excel2016で作っていても、「xls形式」で保存していると、いろいろな（新しい）機能が自動的に削除されている可能性があります。あまり好ましいとは言えません。

また、使用しているExcelはExcel2016ということですが、確か、Excel2013からExcelもシングルタスクからマルチタスクに変わっています。シングルタスクはExcelという１つマンションがあってその中に各ブックがあります。マルチタスクは各ブックが一軒家と思えばいいです。WordもPoewpointもExcelもマルチタスクになりました。
実は、私はExcel2010を使っていますが、シングルタスクが嫌で、少しいじってマルチタスクにしています。クラッシュすると全部ダメになるのが嫌だからです。
そのために、少し確認してください。

（１）前置きはこのくらいで、２つのブックが開けているかどうか確認してください。
　　　添付図１のように、表示タブ＞ウインドウの切り替え　で２つのブックが表示されるか確かめます。
（２）エラーが出た算式を見る限り、ピボットの右下セルを参照しようとしていて、ピボットの左上はセルC6と思われます。添付図２
（３）入金一覧表ブックのシート名「本部」のセルU4に半角イコール「=」を入力
　　　表示＞ウインドウの切り替えで「45期受注a.xls」を選択。添付図３
（４）ピボットの右下セルをクリックします。添付図４
　　Enterして登録します。これで終わりです。

エラーが出た式は、
　=GETPIVOTDATA("入金額（6）",[45期受注a.xls]本部入金まとめ (工事)!$C$6)
上記手順の式は、
　=GETPIVOTDATA("入金額（6）",'[45期受注a.xls]本部入金まとめ (工事)'!$C$6)

よく見ると、「[」の前と[!]の前に「'」シングルクォーテーションが入っています。手入力されたのでしょうか。

上手くいけばいいんですが。。。

お礼

nishi6　さま

お忙しい中、幾度もアドバイスを頂きありがとうございました。
ご教授頂きました方法にて上手くいきそうです。
本当に助かりました。
感謝致します。
ありがとうございます！

回答No.2

nishi6

前回の回答は、「ピボットを含むブックを開かないとエラーになる」ので、それへの対応と思い、開かないでデータを取得する方法を回答しました。
補足に「２つのブックが開いていれば参照できるのですね」とあったので、誤解がありそうなので補足させてもらいます。前回のマクロは関係なさそうですね。

まず、添付図上図のようなピボットを考えます。これの右端の「200」と「600」を参照します。
参照方法には２つあって、
・ピボットの内容を、「GetPivotData関数」で参照する方法で、算式で、２つのブックを開いておいて、ウィンドウの切り替えをおこない登録します。ピボットテーブルのセルをクリックするだけで算式が登録されます。
・２つ目はリンク貼り付けで、ピボットのセルをコピーして、参照するブックにリンク貼り付けします。
結果が添付図中図です。

この貼り付け結果をピボットを含むブック（少し内容を変更）を開かないまま立ち上げると、添付図下図のように、「GetPivotData関数」を使った方は更新してもエラーです。リンク貼り付けは正しく計算しています。リンク貼り付けの方が優秀なようです。

しかし、ピボットテーブルはそのデータ内容によって表のかたちが変わってきます。参照したいセル座標が特定されることはあまりないでしょう。当然、ピボットの内容を取得する場合は「GetPivotData関数」を使うのが正しいと思います。

従って、「GetPivotData関数」を使って参照し、ピボットを含むブックを先に開き、参照するブックを開けば、更新するかどうかのメッセージが出るので分かりやすいでしょう。
「ピボットを含むブックを先に開く」手順が面倒ならば、参照するブックのThisWorkbookコードウインドウに「Workbook_Openイベント」を書いて、「ピボットを含むブックを自動的に開く」方法もあります。ネットでググってみてください。

お礼

nishi6さま

再度のご教授ありがとうございます。
「GetPivotData関数」を使って参照して、ピボットを含むブックを先に開き、参照するブックを開けばよいということですね。

まずは御礼を申し上げます。

ご教授頂きました内容で再度エクセルを修正してみます。
結果を報告申し上げます。

ありがとうございました！

補足

こんにちは。

教えて頂きました関数を元に下記のように記入しましたら、エラーメッセージが出てきました。
おかしな所が判別できません。
お力を拝借できますでしょうか？

=GETPIVOTDATA("入金額（6）",[45期受注a.xls]本部入金まとめ (工事)!$C$6)

以下がエラーメッセージです。

—————————————————————
この数式には問題があります。

数式を入力していない場合は次の点に注意します。
最初の文字に等号 (=) やマイナス記号 (-) を使用している場合は、数式として認識されます。

入力内容: =1+1、セルの表示: 2

これを回避するには、先頭に単一引用符 ( ' ) を入力します。

入力内容: '=1+1、セルの表示: =1+1

—————————————————————

どうぞ宜しくお願いします。

回答No.1

nishi6

２つのブックが開いていれば参照できるはずです。しかし、ピボットのあるブックを閉じればエラーが出ます。

これはピボット云々ではなく、閉じたブックの参照の問題でしょう。

閉じたブックを参照するには、Macro4.0のExecuteExcel4Macroを使います。
古いマクロなので、ブックのフルパスを下記のように記述し、カッコ付きブック名、シート名、R1C1方式のセル座標を指定します。

下記では、参照元のセルB5を参照し、セルU4に書いています。
ブックのフルパス：C:\Users\Hoooo\Documents（ご自分のフルパスにしてください）
ブック名：[OK_Pivot.xlsx]　（勝手にこんな名前にしてテストしました）
シート名：本部入金まとめ
セル座標：R5C2（R：行、5行目、C：列、2列目でB列・・・B5）

Sub getData()
　Range("U4") = ExecuteExcel4Macro( _
　　"'C:\Users\Hoooo\Documents\[OK_Pivot.xlsx]本部入金まとめ'!R5C2")
End Sub

これで１行で１セルの参照が書けます。

多量のセル範囲の参照の場合は面倒なので、参照したいブックを見えないように（裏で）開いて、処理をして閉じるようなことをしています。当方、Win10、Excel2010です。ご参考に。

お礼

nishi6さん

さっそくのご返答ありがとうございます。
２つのブックが開いていれば参照できるのですね。
両方を開いてもリンクした値がエラーになっていたので再度確認をしてみます。

またマクロの提供ありがとうございます。
いちど試してみたいと思います。
ただ、ピポットの多数のセルの値を参照するので、このマクロをどのように書き直して活用するのかが課題です。
まずはトライしてみます。
ありがとうございました。