- ベストアンサー
エクセルファイルでピボットテーブルの年月日がズレる問題について
- エクセルファイルでピボットテーブルを使用して月毎の進捗を管理していますが、数か月に一度年月が-1された表になってしまう問題が発生しています。
- この問題が発生すると、シートの再編集やピボットの作り直しにも関わらず解決せず、更新データの差分チェックも手間となります。
- この問題に対する解決策をご存知の方がいれば、教えていただけると助かります。
- okarinamama
- お礼率100% (2/2)
- Excel（エクセル）
- 回答数2
- ありがとう数4
- みんなの回答 （2）
- 専門家の回答
質問者が選んだベストアンサー
「excel ピボットテーブル 年月日 ズレ」でググると、最初に「pivot 集計 年数 が表示のみずれている」というマイクロソフトコミュニティへの質問があり、「pivotの集計で日付をグループ化したときの年数が１年前で表示されてしまう」というのがあります。質問内容とよく似ています。結果は、実際のデータの提示がなく、解決に至ってないようです。 他にも「ピボットが言うこと聞かない！日付で並び替えしてくれない時の対処法」などがあり、日付に関して何か問題がありそうです。 質問に、 ＞一度なってしまうと、シートを値貼り付けして、再度ピボットを作り直して ＞も治りません。現状は、大丈夫だった数週間前のファイルにさかのぼって ＞元データを再編集するなどしています と書かれており、この症状になった原因が分かっているような気がしますが。（再編集できているわけなので） ここで、データに日付以外のデータを入力してピボットを作ってみます。 ＜１＞最初から文字列を含む ピボットを作成するデータ内の日付列に、日付以外の文字列が入っているときのピボット例です。４番目のデータは「'2019/7/1」です。この時当然ですが、ピボットは上手くいっていません。年月日のグループ化ができません。 ＜２＞日付にして更新 「'2019/7/1」を「2019/7/1」に修正して、ピボットを更新しても正しく表示してくれません。この状態は、質問にある「一度なってしまうと、シートを値貼り付けして、再度ピボットを作り直しても治りません。」に似ています。ここでも、年月日のグループ化ができません。 ＜３＞ もう一回更新（ピボットは変わらないがグループ化可） その状態のまま、ピボットを再度更新してみます。ピボットはおかしいものの、年月日のグループ化ができるようになりました。 ＜４＞グループ化を行い、グループ解除をするとピボットが正しくなる グループ化できるようになったので、グループ化を行い、すぐにグループ解除をするとピボットが正しくなります。 おかしなデータがあるときの対応として、 ア．おかしなデータを修正 イ．ピボットの更新を２回行いグループ化できるようにする ウ．グループ化を行い、すぐグループ解除する が考えられます。 「ア．おかしなデータを修正」法は、ピボットのおかしなセルをダブルクリックすると他シートにおかしなデータ内容が表示されるので発見は簡単です。 その他、質問で気になる点として、 ＞期日のタイミングで(1)を編集、年月日の報告を2019/10/1、等VLOOKUPで当てて入力して値貼り付けした後、念のため区切り位置で「日付」を選択します。 の意味がよく分かりません。 「VLOOKUPで当てて入力して値貼り付け」とはどんな内容？ 区切り位置機能で８桁数値やyyyy/m/d表示を分解しDate関数で復元すると異なる日付になったりします。この機能で日付の妥当性をチェックするのは結構大変です。 最後に、ピボットを使う時は、通常の範囲としてデータを扱うのではなく、「テーブル」としてデータを設定しています。誤入力対策や更新のしやすさがあるので「テーブル」を使っています。 ご参考に。
その他の回答 (1)
- kon555
- ベストアンサー率51% (1842/3559)
軽く検索してみましたが、年月日についての類型事例や注意書きは引っかかりませんでした。 エクセルもピボットテーブルも非常に一般的なものなので、検索しても類似事例が引っかからない=貴方自身の環境や作業方法に依存するところが大きい、と考えられます。 なので一般的なエラー切り分けの方法として再現性テストを行い、原因を明らかにするのが解決への近道だと思います。 記述の中では「数か月に一度くらいの割合で」と書かれていますが、この異常は特定の元データを読ませると必ず発生しますか？ それとも元データに依存せず、純粋にランダムに発生しますか？ もしも元データに依存するなら、次は「どんなデータの時に発生するか」を突き止めましょう。例えば発生した元データ・ピボットの双方を別フォルダなどにコピーしておいて、元データ側を半分削除してテスト。これで発生しなければ削除した半分だけでテスト。 もしこの半分ずつのテストのどちらかで再現したなら、再現した方のデータに何かしらの原因が潜んでいるわけです。なので残ったデータをさらに半分ずつテスト・・・とやっていくと、原因となりうるデータの範囲が絞り込まれていきます。 例えばですが、もしかすると特定の日付けのデータだけがピボットの暴発を招いているかもしれません。あるいは何かの誤入力や、データ形式の問題かもしれません。 一定以上まで原因候補が絞れて行けば、そうした具体的な部分も見つけやすくなります。 また半分半分のテストデータで再現しないなら、今度はデータの「量」ではないかと仮説が立てられます。 これは俗に「切り分け」と言われるエラー解析の初歩的手法ですが、このようにして少しずつ原因を絞り込んでいけば、きっと解決すると思いますよ。
お礼
使い込んだ？エクセルファイルでないと発生しないのと、会社のコンプライアンスに抵触するので、画像貼付けもできない中、文章で説明するのは難しいと思いつつ、発生する都度手戻りがあって困っていたので、質問させていただきました。 私のつたない説明から、解決の糸口を教えていただきありがとうございます。 時間を見つけて、原因を切り分けてみたいと思います。
お礼
使い込んだ？エクセルファイルでないと発生しないのと、会社のコンプライアンスに抵触するので、画像貼付けもできない中、文章で説明するのは難しいと思いつつ、発生する都度手戻りがあって困っていたので、質問させていただきました。 私のつたない説明から、解決の糸口を教えていただきありがとうございます。 時間を見つけて、原因を切り分けてみたいと思います。
補足
最終形としては、ツールにインポートして運用するデータですので日付での報告を求めていますが、報告者の一存で「調整中」「未定」「-」等がユニークに入ってきますので、VLOOKUPで当てた時にセルの書式が変わってしまうのでしょう。ですので、値貼り付けをした後に、日付以外をカットして念のためデータ→区切り位置で列のデータ形式を日付で再認識させています。