以下の一覧表をピボットテーブルで日付ごと集計すれば、年ごとにグループ化されて2021年から2023年に分かれるはずです。
ところが、ピボットテーブルには状況によってこの年が2020年から2022年とずれてグループ化されるということを確認しました。
どうやら以前からこの不具合は発生していたようで Microsoft の質問サイトにも同様の報告があるのですが、現段階で対処されていないようです。
原因もわかったので説明します。
まず原因ですが、 このように元データの中に日付シリアル値で0の値、1900年の1月0日の日付が入っている場合に起きるようです。
このデータを行ごと削除したことにより正常に表示するようになりました。
上記のピボットテーブルの結果ではこの1900年のデータを抜きたかったので今日ラベルのフィルターに1900年のデータを除くフィルターをかけていました。
ピボットテーブルの日付データ内の0値は削除したほうがいいのでしょう。
しかし、0の値を単純に空白にしてしまうと、空白文字の含まれている項目ということで認識されてしまい、日付ではなく文字データとして集計されます。これでは四半期や月ごとのグループ化もできません。
そもそも日付に0が入る原因は、日付データをVLOOKUP関数などで他の表から引っ張ってくる時にその元データが空白だった場合は0になってしまうからです。
以下の表では、セルB2に「=VLOOKUP(A2,$E$2:$F$4,2,FALSE)」という計算式が入っており右側の平から納期を参照しているのですが、計算式はそのまま下にコピーされていて、案件番号A2の納期が空欄になっているため、VLOOKUP関数の結果が0になっているのです。
このようなデータを日付で集計した場合に1年ずれるピボットテーブルが出来上がってしまうということらしいのです。
では、どんな対処すればいいかというとVLOOKUP関数のような他のセル値を参照する場合その元のセルが空白の場合は0になるのはExcelの仕様上、仕方ありません。
一旦計算しその結果が0ならばどうにかするという計算式を使えば良いのでしょう。
次のような書式の計算式にするのが最も素直な方法になるかと思います。
=IF(VLOOKUP(検索値,範囲,2,FALSE)=0,代替値,VLOOKUP(検索値,範囲,2,FALSE))
ここで問題になってくるのは代替値を何にするかです。空白を表す「””」にしてしまえばその項目は日付ではなく文字列として判断されてしまいます。 まず動作としては0でなければいいので、何らかの整数を入れれば良いのではないかと思いました。
さらに集計のことを考えると後からフィルターすればいいと思うので絶対にありえない日付を設定すれば良いのではないかと思います。1であれば1900年1月1日として認識され、年もずれずに集計されました。または極端に未来の日付にするために999999という整数を入力すれば 4637年11月25日に設定されるので、これでも正しくピボットテーブルが動作します。
Excelが認識できる最後の日付は「9999/12/31」で日付シリアル値としては2958465なので、この値を代替値にしたところ、日付が文字列データとして認識されてしまったのであまり大きな値にすると認識できないようです。
DATEVALUE関数の結果がエラーになる日付なのかなとも思ったのですが、どうやらピボットテーブルはピボットテーブルだけの日付の認識できる範囲があるような気がしてなりません。おそらく日付に0が入るようなユースケースを想定していなかったのかと思いますが、VLOOKUP関数で日付を参照するということは普通に行うことなので早く対処して欲しいと思います。
コメント