2-5.Excel・スプレッドシート・ピボットテーブルの使い方 PR

【エクセル:エラー事例】ExcelのピボットテーブルをGoogleスプレッドシートに上手く読み込めない原因と対処方法①表の大きさの問題

【エクセル:エラー事例】ExcelのピボットテーブルをGoogleスプレッドシートに上手く読み込めない原因と対処方法①表の大きさの問題
記事内に商品プロモーションを含む場合があります
じゃなさん

 ExcelのピボットテーブルをGoogleスプレッドシートで読み込んだら、うまくいかない、困った…………(>.<)

 ExcelのピボットテーブルをGoogleスプレッドシートで読み込んだらピボットテーブルがうまくいかない、と悩んでいる方に向けた記事です。

こんな人にオススメ
  • ExcelのピボットテーブルをGoogleスプレッドシートで読み込んだらピボットテーブルがうまく開かない、と悩んでいる方
  • Excelデータベースをピボットテーブルで集計後、そのままGoogleスプレッドシートで保存したいが、ピボットテーブルの表示はうまくいくのか、と悩んでいる方

 私の経験(株式投資Excelテンプレートのブログ作成)から、ExcelをGoogleスプレッドシートにコンバートしたときピボットテーブルの問題点は、現在までに2点わかっており、それを解決してきました。

 その1点は、(今回の記事)

  • ピボットテーブルの行ラベル階層的に下方向に増やした場合
    1. Excel(ピボットテーブル)では、行方向(下)にのみ表が拡大していくが、Googleスプレッドシート(ピボットテーブル)では、行方向(下)と列方向(右)へ同時に表が拡大していく。
    2. したがって、Excel(ピボットテーブル)で、ピボットテーブルの右側のセルに別表を作成していた場合、そのExcelデータをGoogleスプレッドシート(ピボットテーブル)で読み込んだとき、ピボットテーブルと別表が重なる部分があればピボットテーブルが表示されず、エラー表示となる。

という点です。

 もう1点は、(※別記事・参照)

  • ExcelのピボットテーブルをGoogleスプレッドシートで読み込んだら、数値はそのまま維持するが「単位」が変わる

という点です。(※別記事【Excelとスプレッドシートの違い】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだが上手く表示できない②単位の問題参照)

 今回の記事では、1点目のピボットテーブルの行ラベルを階層的に下方向に増やした場合について説明します。

 結論を言うと、その解決方法は、

  • 行ラベルを階層的に下方向に増やした」その数だけ、右側に空白の列を増やす。
  • 「フィルタ」欄は、ピボットテーブル・エディタの中にあり、選択する。(項目は引き継がれているが、選択は引き継がれてないため)

ことでした。

 わかれば簡単なことなんですが、わかるまでに意外と時間を要することがあります。しばらくしないと再度おなじ間違いをするExcelあるあるです。

【エクセル:エラー事例】ExcelのピボットテーブルをGoogleスプレッドシートに上手く読み込めない事例①表の大きさの問題

 当ブログの株式投資Excelテンプレートを例に説明します。(※数値はアレンジしています)

Excelのピボットテーブルを表示

 下の図は、Excelのピボットテーブルです。

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

Excel・ピボットテーブルのフィールドの内容表示

 下の図は、上記Excelのピボットテーブルのフィールドの内容です。

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

 行ラベルは「3区分・大」「3区分・中」「セクター・1」「通貨」の4つを選んでいます。

Googleスプレッドシートで読み込んだときのピボットテーブルの表示状態

 上記Excelデータ(ピボットテーブル含む)をGoogleスプレッドシートで読み込んだときのピボットテーブルの表示状態です。

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

 上記のとおり、右側の別表は表示されていますが、肝心のピボットテーブルは表示されていません。

 そこには「エラー表示(#REF!)」があるだけです。

かん

こんな表示だとがっかりしますね。でも、原因がわかれば対策は取れ解決します。 

じゃなさん

 Excelのピボットテーブルの表示形式を変えたらどうなんじゃ?

かん

Excelのピボットテーブルのレイアウトの変更もできますが、まあ、それは別の話にしましょう。 

ピボットテーブルのレイアウトと書式のデザインマイクロソフト・サポートより

【エクセル:エラー事例】ExcelのピボットテーブルをGoogleスプレッドシートに上手く読み込めない原因①表の大きさの問題

 Excelデータ(ピボットテーブル含む)をGoogleスプレッドシートで読み込んだとき、ピボットテーブルが表示されない原因について、私の経験を通して説明します。

「エラー表示とその内容」の確認

 まず、「エラー表示とその内容」の確認です。

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ
【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

 「エラー」の内容には、「配列結果はG14のデータを上書きするため、展開されませんでした。」と表示があり、その意味は「ピボットテーブルを表示すると右側の別表を上書きすることになるから、ピボットテーブルを表示できない」という内容であることがわかりました。

【エクセル:エラー事例】ExcelのピボットテーブルをGoogleスプレッドシートに上手く読み込めない場合の対処方法①表の大きさの問題

 次に、私が上記エラー表示を解消し、Googleスプレッドシートでピボットテーブルを表示させた方法です。

「別表」の「左に7列挿入」し、別表を右へ移動

 表示されない原因は、「ピボットテーブルを表示すると右側の別表を上書きすることになるから、ピボットテーブルを表示できない」ということがわかりました。

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

 その原因がわかり、別表の位置を右側へ移動させるため、上記のとおり、別表の「左に7列挿入」をクリックすることにしました。

「エラー」は解消されたが「数値」の表示なし

 別表を右へ移動したためピボットテーブルの右側に空白ができ、エラー表示は解消されましたが、下の状態でした。

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

 「エラー」は解消されましたが、Googleスプレッドシートのピボットテーブルにエクセルで選んでいた行ラベルの「3区分・大」「3区分・中」「セクター・1」「通貨」の項目がなく、「数値」の表示もない状態でした。

かん

これでも、まだがっかりしますね。もう一つ対策が必要です。 

Googleスプレッドシートの「ピボットテーブル」の編集

 ただ、ここまで来れば、あとは、編集作業をすればいいだけだけだろうと考えました。Excelでいう「ピボットテーブルのフィールド」での編集です。

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

 上記のピボットテーブル部分にある「編集」をクリックする。

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

 Googleスプレッドシートのピボットテーブルの編集用の「ピボットテーブル・エディタ」を表示させる。(※Excelでは、「ピボットテーブルのフィールド」と言われている部分)

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

 上記のピボットテーブルエディタの中にある「フィルタ」部分「年月日」の「すべて選択」(実質、このデータの場合「2022/2/6」のみである)を選択して「OK」をクリックする。

【エクセル失敗談】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだら上手く表示できない①表の大きさ

 上記の結果、ピボットテーブル・エディタでフィルタを編集し、ピボットテーブルの全体を表示させることができました。

かん

エクセルのピボットテーブルのフィルターに慣れている人は、ピボットテーブルの表の上部にフィルターが表示されないのもがっかりしますね。Googleスプレッドシートでは、このフィルタは、ピボットテーブル・エディタの中だけしか表示されない、ということですね。 

【似た事例】【NISA枠・拡大拡充に備えて】株式投資信託・資産運用管理ポートフォリオ作成用Excelテンプレートの使い方(老後資金の親子2世代運用版)【SBI証券・楽天証券】※スプレッドシート対応から抜粋した図面(下図)

【NISA枠・拡大拡充に備えて】株式投資信託・資産運用管理ポートフォリオ作成用Excelテンプレートの使い方(老後資金の親子2世代運用版)【SBI証券・楽天証券】※スプレッドシート対応
かん

上の図は、エクセルのピボットテーブルをGoogleスプレッドシートのピボットテーブルにコンバートしたため、図の左上のピボットテーブルの表の上部のExcelのフィルター部分が空白になっている状態です。

そのため、Googleスプレッドシートピボットテーブル・エディタの中にある「フィルタ」を開いて選択をし直す必要がありました。赤丸はその手順です。 

じゃなさん

 Excelではフィルター、スプレッドシートではフィルタ、ちょっと語尾が違う(>.<) まっ、どーでもいいか

※当記事のGoogleスプレッドシートのピボットテーブルの表示結果は単位が違っていますが、数値は表示されました。その単位の修正は別記事【Excelとスプレッドシートの違い】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだが上手く表示できない②単位の問題参照して下さい。

まとめ:【エクセル:エラー事例】ExcelのピボットテーブルをGoogleスプレッドシートに上手く読み込めない原因と対処方法①表の大きさの問題

 今回は、

  • ExcelのピボットテーブルをGoogleスプレッドシートで読み込んだらピボットテーブルがうまく開かない、と悩んでいる方
  • Excelデータベースをピボットテーブルで集計後、そのままGoogleスプレッドシートで保存したいが、ピボットテーブルの表示はうまくいくのか、と悩んでいる方

に向けた記事です。

 私の経験(株式投資Excelテンプレートのブログ作成)から、ExcelをGoogleスプレッドシートにコンバートしたときピボットテーブルの問題点は、現在までに2点ありました。

 その1点は、ピボットテーブルの表の大きさの問題で

  • ピボットテーブルの行ラベル階層的に下方向に増やした場合
    1. Excel(ピボットテーブル)では、行方向(下)にのみ表が拡大していくが、Googleスプレッドシート(ピボットテーブル)では、行方向(下)と列方向(右)へ同時に表が拡大していく。
    2. したがって、Excel(ピボットテーブル)で、ピボットテーブルの右側のセルに別表を作成していた場合、Googleスプレッドシート(ピボットテーブル)で読み込んだとき、ピボットテーブルと別表が重なる部分があればピボットテーブル自体が表示されない。(エラー表示となる)

 もう1点は、数値の単位の問題で

  • ExcelのピボットテーブルをGoogleスプレッドシートで読み込んだら、数値はそのまま維持するが「単位」が変わる(※別記事で紹介)

という点です。(※単位の修正記事【Excelとスプレッドシートの違い】ExcelのピボットテーブルをGoogleスプレッドシートに読み込んだが上手く表示できない②単位の問題参照)

 今回は、前者の「ピボットテーブルの表の大きさの問題」の原因と対策をこの記事で紹介しました。

 その解決方法は、

  • 行ラベルを階層的に下方向に増やした」その数だけ、右側に空白の列を増やす。
  • 「フィルタ」欄は、ピボットテーブル・エディタの中にあり、選択する。(項目は引き継がれているが、選択は引き継がれてないため)

ことでした。

 どちらも原因がわかって対策をすれば簡単なことなんですが、わかるまでに意外と時間を要することがあります。しばらくしないと再度おなじ間違いをするExcelあるあるですから、しっかりと覚えておいて下さいね。

【関連記事】

【要注意!】エクセル・スプレッドシート及びピボットテーブルの操作方法で注意する点

要注意!【エクセル:エラー事例】株管理ポートフォリオ(円グラフ)作成用のエクセル・スプレッドシート及びピボットテーブルの使い方で注意する点
要注意!【エクセル:エラー事例】株管理ポートフォリオ(円グラフ)作成用のエクセル・スプレッドシート及びピボットテーブルの使い方で注意する点 今回の記事は、ExcelのピボットテーブルをGoogleスプレッドシートにコンバートすることに関する悩みや、ピボットテーブルの参照元データの範囲指定の失敗等に対し回答するものです。  当ブログの株式投資ExcelテンプレートをGoogleスプレッドシートにコンバートする際、ピボットテーブルの集計で同じようなミスが何度も出てくると思われます。  私がそのようなときに解決した方法、その原因と対策を記事にしました。参考になれば幸いです。...
【エクセル:エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす3つの方法
【エクセル:エラー事例】ピボットテーブルで参照元データの範囲指定の失敗をなくす3つの方法 今回の記事は、Excelのピボットテーブルの範囲指定に関する悩みに対し回答するものです。  当ブログの株式投資Excelテンプレートのような不揃いなデータを使った場合、ピボットテーブルの範囲指定で同じようなミスが何度も出てくると思われます。  ただ、その原因はおおよそ2つに絞られる思われるのでその点を詳細に記事にしました。参考になれば幸いです。...
【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない、集計できない場合の原因と修正方法(事例②:「範囲の更新忘れ」or「範囲指定の間違い」)
【エクセル:エラー事例】ピボットテーブルの集計(合計・総計)が参照元データの集計と合わない、集計できない場合の原因と修正方法(事例②:「範囲の更新忘れ」or「範囲指定の間違い」) 今回の記事は、Excelのピボットテーブルの集計(合計)に関するエラーの悩みに対し図解付きで回答するものです。  当ブログの株式投資Excelテンプレートのような不揃いなデータを使った場合、ピボットテーブルの集計(合計)で同じようなミスが何度も出てくると思われます。  ただ、その原因はおおよそ2つに絞られると思われるのでその点を詳細に記事にしました。参考になれば幸いです。...

 株管理ポートフォリオ作成の基本記事

【いまさら聞けない基本の「き」】エクセルで株管理ポートフォリオ(円グラフ)の作り方とExcelテンプレートの使い方の基本の記事5本<全て無料>(SBI・楽天証券・2024年の新NISA移行対応予定)※Googleスプレッドシート版あり
【2024年の新NISA移行対応】【いまさら聞けない基本の「き」】エクセルで株管理ポートフォリオ(円グラフ)の作り方とExcelテンプレートの使い方の基本の記事4本<全て無料>(SBI・楽天証券)※Googleスプレッドシート版あり 今回の記事は、いまさら聞けない基本の「き」として、エクセルによるSBI証券と楽天証券とSBIネオモバイル証券の株管理ポートフォリオの作り方とテンプレートの使い方の基本記事を集めました。途中からブログ記事を読んだ方で、基本に戻って作り方や使い方を知りたいと悩んでいる方へ向けたものです。  一度作成してしまえば次回からは意外と簡単にポートフォリオができてしまいます。  全て無料配信です。  私が現に使っているExcelテンプレートを多数公開していますので、ぜひお使いください。ピボットテーブルで集計をしており、使いやすさ、分かりやすさを追求して作成しております。  参考になれば幸いです。...

親子2世代運用のポートフォリオ作成テンプレート使い方の記事一覧

【おすすめ】老後資金は親子2世代で資産運用・Excelテンプレートで株管理ポートフォリオ(円グラフ)作成」記事一覧【SBI・楽天証券・NISA対応】Googleスプレッドシート版も有<無料>
【おすすめ】老後資金は親子2世代で資産運用・Excelテンプレートで株管理ポートフォリオ(円グラフ)作成」記事一覧【SBI・楽天証券・NISA対応】Googleスプレッドシート版も有<無料> 今回の記事は、老後資金の親子2世代運用を目指している定年後のシニアの方やシニアを親に持つ方へむけての記事の一覧表です。  2世代運用での複数口座や複数名義など集計に悩んでいる方は、私が現に使っているExcelテンプレートでポートフォリオ(円グラフ)を作る方法をぜひお使いください。  ピボットテーブルでの集計で、使いやすさ、分かりやすさを追求して作成しております。参考になれば幸いです。  全て無料です。...
【NISA枠・拡大拡充に備えて】株式投資信託・資産運用管理ポートフォリオ作成用Excelテンプレートの使い方(老後資金の親子2世代運用版)【SBI証券・楽天証券】<無料>※スプレッドシート対応
【NISA枠・拡大拡充に備えて】株式投資信託・資産運用管理ポートフォリオ作成用Excelテンプレートの使い方(老後資金の親子2世代運用版)【SBI証券・楽天証券】<無料>※スプレッドシート対応今回の記事は、NISAに特化していますが、基本は老後資金の親子2世代運用を目指している定年後のシニアの方やシニアを親に持つ方へむけての記事です。複数口座や複数名義など集計し、その中にNISAが混在しており、NISAの集計や分析で悩んでいる方向けです。私がNISA確認用に使っているExcelテンプレートを無料で公開していますので、ぜひお使いください。ピボットテーブルで集計をしており、使いやすさ、分かりやすさを追求して作成しております。参考になれば幸いです。...

 これまでに公開したテンプレートに関する記事一覧はコチラです。(リスク別テンプレート一覧はコチラです。)

※【楽天市場】楽天市場で「エクセル・ピボットテーブル」関連の商品購入はこちらから↗

※【メルカリ】メルカリで「エクセル・ピボットテーブル」関連の商品購入はこちらから↗

ABOUT ME
CAN(かん)
●60歳でサラリーマン定年退職後、10年目。「今日が一番若い日」と言いながらブログを始めたブログ初心者のオヤジです。●3つの証券会社分&家族全員分&100銘柄分をまとめて見える化できるExcelテンプレート無料配信中!
こちらの記事もおすすめ!
【図解エクセル】<無料>明日の急激な円高を想定した株管理ポートフォリオの作り方とテンプレートの使い方【為替リスク:実質上の円建てドル建てで区分】【SBI証券・親子2世代運用・積立NISA等対応】 2-2-2.為替リスク

【図解エクセル】<無料>明日の急激な円高を想定した株管理ポートフォリオの作り方とテンプレートの使い方【為替リスク:実質上の円建てドル建てで区分】【SBI証券・親子2世代運用・積立NISA等対応】

2022年7月6日
らくぶる
じゃなさん 次は 「為替リスク(円高リスク)対策用テンプレートの使い方。 「投資信託や東証米国ETFなど表面上は円建ての商品 …