『ピボットテーブルで日付の集計ができません』（かとけん）

お世話になります。
WINDOWS10PROでOFFICE365を使用しています。
データにピボットテーブルをかけて、日付欄を月ごとに集計したいと思っています。
元データの日付欄は"2021/3/1"のように表示されており、ホームの「数値」の上にある表示形式の表示を見ると、「日付」になっています。

なお、オプションのデータにある「ピボットテーブルで日付での自動集計を無効にする」のチェックは入っていません。

この状態でピボットテーブルをかけると、
そもそも日付欄が”20210301"のように表示され、「日付として認識されていない」ように思います。
自動集計もできませんし、日付を選択してグループ化をしようとしても、
「日付に関する集計」のメニューが出ずに「値としての集計」のみができるようになっています。

元データを見ると「日付」になっているのですが。。。

どうすればよろしいでしょうか。
よろしくお願い致します。




＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
----
 >元データの日付欄は"2021/3/1"のように表示されており

 そのセルを選択すると数式バーにはどのように表示されるのでしょうか？
（コナミ） 2022/01/07(金) 19:15
----
ピボットテーブル内の"20210301"と表示されている部分のセルの書式設定が
ユーザー定義になっていませんか？

ちなみに、よくある間違いですが、Excelにおいて日付は「数値」です。
そして表示形式で「数値」を「日付」に変換して表示しているだけです。

＞ホームの「数値」の上にある表示形式の表示を見ると、「日付」になっています。
となっていても、これは日付形式のデータではなく、数値を日付に変換して表示しているにすぎません。
そして、表示形式が「ユーザー定義」になっていて、「yyyymmdd」となっている場合に、
かとけん様の言われている事が再現できたので、
おそらく、元データに、数値データと文字列データなどが混ざっている物と思われます。
間違っていたらすみません。

例えば、csvから読み込んだデータや、Webから落とし込んだデータの場合、
数値データではない場合もあります。


（ヘンリー） 2022/01/07(金) 19:21
----
ありがとうございます。
実は、元のデータが「20210301」という表示になっていました。
それを自分で、「データ・区切り位置」から「2021/3/1」の形式に修正をしたものです。
表示は一見日付になっていても、実は日付ではないということなのですかね。
ピボットで取り込む前のデータで、日付データを選択すると、数式バーには「2021/3/1」のように表示されており、「ホーム・数式」の上部のバーにも日付と表示されています。
これにピボットをかけると、「20210301」という表示に変わり、選択してみると数式バーには「20210301」と表示されます。選択してセルの書式設定を見てみると、「ユーザ形式」とはなっておらず、「標準」と出ます。
ここで表示形式を日付に変えようとすると、表示が「＃＃＃＃＃＃＃＃＃＃＃＃＃」となってしまい、
列幅を広げようが何をしようが、表示が＃＃＃＃のままとなってしまいます。
自分で考えらえることはすべてしてみました。
どうすればよいのかわかりません。

よろしくお願いします。
（かとけん） 2022/01/08(土) 06:50
----
＞実は、元のデータが「20210301」という表示になっていました。
＞それを自分で、「データ・区切り位置」から「2021/3/1」の形式に修正をしたものです。
データタブの区切り位置機能から、1回で「2021/3/1」とできるやり方はわかりませんでしたが…
表示が「＃＃＃＃＃＃＃＃＃＃＃＃＃」となるのは、数値形式(日付のシリアル値)ではなく、
文字列("20210301")または数値(2千21万3百1)になっているからです。

＞選択してセルの書式設定を見てみると、「ユーザ形式」とはなっておらず、「標準」と出ます。
標準ということは、Excelは「20210301」を「2千21万3百1」という数値で入力されているものとみなします。
Excelは日付ではなく、数値(シリアル値)を日付の様に見せかけています。
数値の1を、1900/1/1としています。
従って、2021/3/1は4万4千2百5十6という数値になります。2021年3月でも4万4千ですよ。
それが、2千万というのは、はるか未来の日付になってしまいます。
だから「表示しきれない」という意味の#で表現されるのです。

【解決策(案)】
元データが、「20210301」であれば、これを数値形式(日付のシリアル値)にしてあげる事です。
例えば、「20210301」がA2セルに入力されているとして、ワークシート関数ならば
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
という感じです。
この列をキーとするピボットテーブルを作成すれば解決できると思います。
（ヘンリー） 2022/01/08(土) 10:36
----
ヘンリー様
ありがとうございます。おっしゃっている通りの加工をすると、ピボットテーブルでも日付だと認識してくれました。
助かりました。試行錯誤する中で、私は=left(A2,4)&"/"&mid(A2,5,2)&"/"&right(A2,4))のようなことも試していたのですが、これではDate関数を使っていないので日付と認識されないのですね。

なお、区切り位置については、選択して、区切り位置をクリック、「次へ」を2回押して、メニューから日付を選択して、完了とするのみです。
でもこれでは日付と認識されていないわけですものね。

本当にありがとうございました。

（かとけん） 2022/01/08(土) 11:14
----
 ＞でもこれでは日付と認識されていないわけですものね。　
 そんなことないと思いますけど？
 区切り位置でやれば、「2021/3/1」と手入力したのと全く同じ日付データに変換されるはずです。
「365」では仕様が変わったというのなら別ですけど･･･

 区切り位置かピボットテーブルか、どっちかの操作を間違えているのでは？

 ■ついでに･･･
 データは「20210301」のまま、数式で日付にするのなら

 =TEXT(A2,"0-00-00")*1 
 表示形式「日付」

 これでできますね。

 ＞=left(A2,4)&"/"&mid(A2,5,2)&"/"&right(A2,4))

 これだったら、RIGHT関数の文字数を「2」にして、全体を(  )で囲み
 それに「1」を掛ければ日付データ（シリアル値）になります。

 =(LEFT(A2,4)&"/"&MID(A2,5,2)&"/"&RIGHT(A2,2))*1
 表示形式「日付」

 以上、参考まで
（笑） 2022/01/08(土) 13:30
----
ありがとうございます。

＞区切り位置かピボットテーブルか、どっちかの操作を間違えているのでは？
区切り位置の操作後には間違いなく、「日付」になっているのですが、ピボットをかけると、日付ではなくなってしまうんですよ。たぶん操作の問題ではないように思います。（間違えるような操作はないですし）

＞全体を（　　）で囲み1をかける
すごいですね。これだけのことで大丈夫になるのですね。

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

（かとけん） 2022/01/08(土) 15:02