思ったより複雑な日付を整理してみる(書式編)

「日付をキーにした集計ができない」ときがありませんか

「関数に日付を使って何かの値を集計する」ケースについて。
私の経験では、それがうまくできないことがあります。これは日付の認識のされ方が(私の判断とExcelの判断で)乖離していることが原因でした。
もっとも、必ずしもそうならないケース・・うまくケースがあるため、そのときは一人混乱状態ですが。

しかし、このようなカオスの状況をくぐり抜けていくうちにいくつかのポイントに気づきました。
今回は私自身の整理も含めて「Excelで日付がどうのように扱われるのか」をまとめてみました。
今回は関数編につながる伏線として日付を入力した時の書式設定についてです。

人とExcelのあり得ない会話

Excelで入力した日付について

おそらく私だけでなく皆さんもセルに日付を入力する時に意識するのは「いつの日付を入力するのか」で、入力した日付が「Excelによってどのように扱われるのか」・・これはほとんど意識することはないと思います。

しかし、集計時にはこの意識が必要になるケースがあるのですね。

そこで、セルに「日付そのもの」、「日付っぽい日付」、「関数、数式で日付を作る」など色々な日付を入力し、その入力前後で実際に「Excelがどのようなセル書式の判断をするのか」、これらの書式設定を確認してみました。

書式設定をした日付のイメージ

色々な日付をExcelではどのように扱うのか試す

試験として色々な日付を入力し、それがExcelでどのように判断されるのか見てみました。
ここで分かったことは、思ったより複雑で混乱したことです(笑)。

※ちなみに本来は日付について「関数がどのように扱うのか」を説明するつもりだったのですが、検証してみると実はそれなりに複雑でした。そのため、まずは当ページでセルの書式設定がどのようになっているのか、から説明したいと思います。

この値・・もしも日付ならばこうなるはず

セルに入力されている値が日付。そしてExcelがちゃんと日付として判断しているならば、期待通りの結果になるはずです。
それを踏まえて確認しました。
トリッキーな結果もありチト重い気持ちになりました(笑)。

日付をキーにした関数が「何かおかしい」と感じたら、今回紹介するこれらのどれかに当てはまるかもしれませんね。

試した方法、検証について

今回の確認、検証では以下の点に注意して行っています。

  • 日付を手入力、または数式など参照した状態で試す
  • 書式設定は「設定していない」と「設定した」ケースで入力の前後を確認する

なお、私の考えでは今回の件を含まずとも書式設定は、集計時のハードルになると思います。
「目に見える値」と「実際に入力されている値」とで乖離が発生するケースがあるからです。
(今回の検証でも嫌な予感が的中の結果もありました・・)

この乖離は色々な作業にも影響することが多いです。みなさんの中でも、もしあまり気にしてない方がいらっしゃれば、意識しておくとよいと思います。

さて、実際に試した表です。

日付を「1987年6月5日」としているのは、後に使う関数で抽出された値が分かるようにするためです。当ページでは日付自体に大きな意味はありません。

日付の入力をした時の書式の結果

最初に表の構成について簡単に説明します。

  • B列:セル(D列)に入力した値の説明
  • C列:入力する前のセルの書式の状態
  • D列:実際に入力した値
  • E列:入力後のセルの書式の状態

と、このような列の構成としています。
例えば、いくつかのNoについてD列に入力した値は次の意味になります。

  • No4、5:通常の方法で日付を入力
  • No10:これは=(イコール)で始まる数式として入力(参照セルがないケース)
  • No12:これは=(イコール)で始まる数式として入力(参照セルがあるケース)

※No12、13は数式の部分のセルアドレスの行位置は「Noではない」です。行番号を画像に入れておりませんのでずれているように見える点にご注意ください(画像幅の関係で行番号を削りました)。

今回、私が特に注目したことは、「セルの書式が入力した値によって書式が勝手に変わってしまうかどうか」です。
通常、新規ブックを開いた状態でのセルの書式設定は「標準」です。
セルの書式設定の初期状態
これが勝手に変わってしまうと何か問題が発生したときに(それに気づかず)確認漏れなどが起こってしまいます。これは時間のロスですし、問題自体に気が付かないのはもっと危険ですね。
ですので、この点にも注目しました。

結果検証:「この勝手変更・・ちょっと困る、、」はあるか

それでは実際に結果を見ています。先に言いましたように混乱させるような結果もあります。
気になる点を中心に見ていきたいと思います。

なお、Excelでは、日付は数値として扱われます。「シリアル値」というもので、正しい日付を入力したとき、Excelでは数値として見ています。
セルの中の文字位置が左寄せは文字、右寄せは数値です。これでExcelがどのように判断しているのか一つの見方になります。

日付は数値(シリアル値)に表示(変換)できます。逆に日付も数値に表示できます。
シリアル値を表示

もっとも、表示位置は自由に変えられるため、(Excel利用者が)意図的に変えていることもありますので、その点は注意です。
また、これらの「寄せ位置」は、セルの書式設定にある「文字列」とは意味が異なるためご注意です。私も混乱してしまいました、、

まずは手始めに予想外ではないですが、そのまま扱われたのがNo1のケースです。
年だけ入力したケース
D列の値の位置を見ると、左寄せになっていますね。これは文字扱いです。
「1987年」のみ入力しましたので、これは「Excelが日付ではないと判断している」いうことですね。

以降、このような形で見ていきます。

「年と月」を入力すると書式が自動変換される
No2、3のケースでは、同じ「年と月のみ」の入力ですが、E列の入力後の書式が「ユーザー定義」になっています。
つまり、日付の入力前と後で書式が「自動的に」変換されています。

これは、入力後のユーザー定義の書式設定を見ると「年月の形」となっていますね。このことから「日付として扱われている」ことが分かります。

なお、この書式の変換は大きな問題があります。
それは、「入力後」の値です。
「年と月」のみの入力は「1日」が勝手につく
このように「1日」が勝手に付与されます。これは集計時に大きな影響を与えるものと思います。例えば・・関数などで、「年と月だけ」で探しても見つからないなどです。
先ほどお話した「目に見える値」と「実際に入力されている値」の違いがこれですね。

Excel側としては日付として見るためには「年月日のセット」でないと「日付として扱えない」ことが理由と思われます。
ただ、逆にこのことからもExcelが日付として見ていることが分かりますが、、チト皮肉ですね。

次のNo7、8は書式を事前に変更した状態から入力しました。
事前に書式設定を変更して日付を入力 
No7 については、ちょっと異色に見えますが、書式設定ではこのようなやり方もできます。
(これも、上記でお話した「目に見える値」と「実際に入力されている値」の違いがあります)
この詳細については割愛しますが、セル内の文字の位置が右寄せであることから数値であり、日付として扱われていると予測できます。が、セルの表示結果を見てもそれが「見た目上の日付」なのか「実際に入力した日付」なのかまでは分かりません。

これは数式バーで確認すれば分かりますが、ここではシリアル値(=1987年6月5日)の表記になっています。うーん、、不思議ですね。
書式設定を変えて数式バーで見るとシリアル値の表示になっている

No8の方は、文字列のままですね。

最後に関数と数式についてです。
関数と数式で引数に日付を指定する
No9のTEXT関数では、引数に入れた「年月日」が正しく日付として認識されているならば、表記が「/(スラッシュ)」に変わるか、を確認しました。日付として認識されているようですね。ただし表示位置は左寄せで文字列として扱っているようです。
※「書式設定が文字列」になっているわけではないのでご注意です。

全体としては書式設定が「標準」が多く、結果としては、No13だけ、日付として扱われるということになりました。

今回挙げている色々な日付・・これらの値を別の関数を使う(参照する)と、それなりにバラバラな結果になります。。こちらは改めて別ページでご紹介します。

いやー、書式設定は奥が深いです。日付の入力一つでこれですから。今後、実際にこれらの値から関数を使って更に検証を進めていますのでこちらについては改めてご紹介します。

おまけ・・オートフィルタでこの異色の日付列を見るとどうなる?

最後の最後にオートフィルタでも見てみます。
普通に考えて、1つの列にこんな色々な種類の日付がある表は存在しないですよね(笑)。
これは私も意識したことありません。従いオートフィルタで見ると、どんな値がリストに表示されるのか興味がありました。

みなさんはどんな一覧になっていると思いますか?

ここの部分ですね。
オートフィルで色々な日付の列のリストには何が表示されるか

そしてこちらが結果です・・

オートフィルタのリストに表示されている内容
※矢印の「6」というのは、表の一番下に参照先にしていたセル値を拾っているので無視してください。外しておけばよかったですね・・

私は今のところ規則性を拾えておりませんが、みなさんは当たってましたか(笑)。

タイトルとURLをコピーしました