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

日付をキーにして関数や数式を使うとうまくいかないとき

前回のページでは、意図的に色々な日付を入力し、その書式がどのように変化するのかなどを確認しました。

日付が入力されたセルの書式設定について、曖昧なところもありましたので一度整理したい気持ちもありました。
その上でいくつかの関数を使い、日付をどのようにして扱っているのかを検証してみました。
今回は前回の続編・・というか、やっと本題ですね(笑)。

前回のおさらい

前回のページでは、適当な日付を入力し、それがセルの書式にどのような影響を与えているのかを検証しました。
詳細はそちらを見ていただきたいのですが、「年と月のみ」入力では、「勝手に1日が入力される」などありました(これは書式の域を超えちゃっている感がありますが・・)。
そこで、「セルの書式を文字列にした日付」のとき、「DATE関数を入れた状態で引数にした日付」のとき、こういった値を元データにして、私たちがよく使う関数たちは、期待通りに動いてくれるのか、が今回のテーマです。

試した方法、検証

今回試した方法などです。

実際に試した表は、前回検証した日付の表をそのまま利用します。
以下のD列「日付の入力」を使って試します。

また、以降の説明は画像幅の都合でB、C列は非表示などしています。何の設定か不明になりましたら上記の画像でご確認くださいませ。

ちなみに、上記のNo7についてはご注意です。「入力している値」と「表示している値」をセルの書式設定で変更しています。この例は極端ですが、実際の業務では実に紛らわしいケースも見かけます。

例えば・・
「あ、文字が入っている」と思いきや、実際には「数値が入力されている」のなどケースですね。

なお、Excelでは日付は「シリアル値」というもので管理されています。
シリアル値とは、「日付を数値に変換」した値です。Excelでは(内部的には)日付ではなく、数値として扱っています。※これは時間も同様です。

関数の結果を見る際には、この点もポイントとなると思います。
「あ、数字だ・・なにこれ？」とならぬようにご注意ください。

今回は以下の関数を確認しています。

選抜したのは独断と偏見ですが、それなりに需要がありそうなものを考えました。
また、COUNTIF関数、VLOOKUP関数では、通常複数の範囲を指定しますが、今回は「1つのセルのみを指定」している点にご注意ください。

それではさっそく見ていきましょう。

全体の結果

最初に、ここで使用したすべてのセル書式情報と関数の結果の表です。非常に横広になってしまい縮小しています。
見にくいですがあしからずです、、

YEAR、MONTH、DAY関数で確認する

この3つの関数は、それぞれ年を取得(表示)、月を取得(表示)、日を取得(表示)します。
この関数で先ほどの日付を指定(参照)したとき、果たして期待通りに表示できるのかを見てみます。

No1では、年(YEAR関数)もエラーになっているのは、Excel(関数)が、「日付と見ていない(文字として見ている)」からですね。

注意すべきは、先ほど説明したNo7ですね。見た目の日付と実際に取得できた日付が異なっています。
ただ、私が入力した値は「1987/6/5」ですが、入力直後の値は、シリアル値に自動変換になっています。

No7の結果が「1987」なのは、Excelがこれを内部的に日付(の形)に変えて取得したものと推測します。

また、「DAY関数」でも気をつける点があります。
No2と3、そして10ですね。「1」と表示されています。
けれども、このD列のセルには「日」は入力していません。「年と月」のみの入力です。

しかしExcel側で自動的(勝手)に日付「1日」を入れました。これでExcel上では日付「1987年6月1日(1987/6/1)」として成立されました。DAY関数はその上での「1」を表示です。
もっともこれは関数の結果というよりも、この書式(Excel)の働き方に注意ですね。

COUNTIF関数で確認する

次にCOUNTIF関数で確認したいと思います。COUNTIF関数は、とある範囲(引数で指定した範囲)に「とある値(引数で指定した値)」が何個あるのかをカウントする関数ですね。
今回のCOUNTIFでは、この2番目の引数→「とある値」で、2つの書式にして試しました。

1．引数の部分に「文字列」として入力
2．引数の部分に「日付(DATE関数を使う)」として入力

検索の対象範囲は「1セルのみ(上記ではセルD2)」です。
つまり指定する値が存在すれば、どのセルも必ず「1」を返します。

では、全体の結果です。

日付を文字列としても、DATE関数でも同じ結果になりました。
No8のように書式が文字列にも関わらず、J列のDATE関数を使ったCOUNTIF関数でカウントできるのは、意外で混乱しそうです。。

VLOOKUP関数で確認する

みなさんお馴染みのVLOOKUPでも確認します。
VLOOKUPでもCOUNTIF同様に第1引数に2つの書式で試しました。

1．引数の部分に「文字列」として入力
2．引数の部分に「日付(DATE関数を使う)」として入力

そして、全体の結果です。COUNTIFとは異なり、同じVLOOKUPでも結果が割れているところが出ましたね。

COUNTIFとは違い、VLOOKUPでは検索値に使用する「引数の書式を変えた」ことにより、結果が異なりました。

みなさんは、あまりやられてないかもしれませんが、「VLOOKUPを使って指定範囲に値の存在有無を確認する」場合で、かつ「日付を検索値に使う」とき、COUNTIFと結果が異なることは注意です。
(私は気をつけなければです・・笑)

結果を眺めると、日付を文字列として検索した方(K列)では、見つかった値を文字として返しているのに対して、DATE関数を使ったL列では、シリアル値で返しています。
値を返すとき「関数自体のあるセルの書式が標準」なので、シリアル値で表示しているのだと思います。

あと、No8、9，12では、日付を文字列として引数に設定した場合は見つかりますが、DATE関数を使った引数では見つからないことも先のCOUNTIF、YEAR関数などと異なりますね。

(ここだけの結果だけでは結論付けられませんが)VLOOKUPは、参照先で入力されている値や書式を判定して見ているとも言えそうです。

LEFT、RIGHT、ISNUMBER関数で確認する

最後に文字列を扱う関数と、セルの値が数値か確認するISNUMBER関数を使ってどのような値を返すのか見ていきます。

ここでは、文字列関数のLEFT、RIGHT関数を使って「年の部分」と「日の部分」を指定した時にどのような結果になるのか、そしてISNUMBER関数を使って日付の入っているセルの値を数値として判定するのか確認しました。

以下、結果です。

LEFT関数とRIGHT関数の結果から分かるのは、2つの種類の表示があることです。

これは、それぞれの関数が参照しているセルをどのように判断したかで結果が変わっているのだと思います。
文字として抽出した値は視覚的にも分かりやすいですね。
一方、シリアル値として抽出した値は、シリアル値の中で先頭4文字などを取得しているため、一見すると「結果が間違えているように見えてしまう」点には注意ですね。

LEFT関数で言えば、
Excelが日付として認識している日付：「シリアル値：3193」となります。
日付と認識していない日付：「1987」で、つまり単なる文字列として認識していると思います。

ISNUMBER関数については、指定(参照)したセルが数値ならばTRUE、数値でなければFALSEを返す関数ですね。

Excelでの日付の管理は「シリアル値という数値」です。この部分を利用してISNUMBER関数でどのような結果になるのか確認しました。
これは「D列の日付」の入力列で右寄せになっているセルがTRUE(数値)という扱いになっています。
セルの書式で意図的に位置を変えなければ分かりやすいですが。。

具体的な解決策・・その都度変わる

いかがでしょうか？
関数自身やVLOOKUPのように引数の書式によっても扱われ方が違うということは、非常に管理がしづらいところです。

今回の結果から思うに、日付が関連する関数(と表)について、どのように対処するのかは「表に入力されている日付によってその都度変えていく」必要がありそうです。
つまり、私ならば「参照しているセルを確認しながら関数を使う」ですが・・シンドいですね。
(すごく大量のデータで一部のセルだけ書式などが変わっていると見逃しそうだし・・)
関数などで「日付を使わない」方法・・これで検討する余地も十分有りです。

以上、みなさんの参考になれば幸いです。