【Excel】日時の完全一致で突合できない原因とキー列の設計

【Excel】日時の完全一致で突合できない原因とキー列の設計のアイキャッチ画像 実務設計

処理済みかどうかを、受付日時の完全一致で判定していました。画面では同じ時刻に見えるのに、再取得したデータでは「未処理」と判定されてしまう。日時を条件にした突合で、似た場面に当たった方もいるのではないでしょうか。

この記事では、Excelの日時で「=」が一致しなくなる場面を、手元のMicrosoft 365(日本語版)で実測して並べます。そのうえで、日時で突合するのをやめ、元のシステムが振るIDで突き合わせる設計を整理します。時刻をキーにできず連番で紐付けた話は【Power Query】時刻が9時間ズレる原因とタイムゾーン変換でも触れています。この記事は、Excelの中で「=」が一致しなくなる仕組みと、キー列の設計に絞ります。

困った場面:同じに見える日時が一致しない

実務で起きていたのは、次のような流れです。

  • 取り込んだ一覧の受付日時と、処理済みの一覧に残した受付日時を比べる
  • 一致したら処理済みとみなす
  • 再取得したあとで、同じ受付なのに一致しない行が出る

日時の差は、画面ではわかりません。原因は、日時の中身にあります。

日時は小数で、1秒は1/86400

Excelの日時はシリアル値という数値です。2026/10/29 9:00:00は46324.375で、1秒は1/86400にあたります。表示が秒までなら、秒未満の小数は画面に出ません。仕組みの全体は【Excel】日付が数字になる仕組みとVBA・PQでの扱いにまとめています。

数値は2進数の浮動小数点で保存され、有効桁数は15桁までです。詳しくは、浮動小数点演算を使用すると、Excel で不正確な結果が得られます(Microsoft Learn)に書かれています。

「=」が一致しない5つのケースを実測

次の結果は、今回の環境(Microsoft 365、日本語、Windows)で測ったものです。Excel 2021未満・Mac・他のバージョンは未検証です。

1. 積み上げ計算。1秒ずつ3,600回足した時刻は、直接作った時刻と3,583行で一致しませんでした。ROUND(×86400,0)で秒に丸めて比べると、不一致は0件でした。一方、1日分の86,400秒を日付+n/86400と日付+TIME(h,m,s)の2通りで作り分けて比べたときは、不一致が0件でした。ずれるのは、同じ時刻でも計算の経路が違うときです。

2. 秒未満の小数。9:00:00と、0.4秒だけ進めた値は、どちらも「2026/10/29 09:00:00」と表示されます。それでも=はFALSEです。

3. 文字列の日時。文字列の「2026/10/29 09:00:00」と数値の日時の=はFALSEでした。VALUEや--で数値にすればTRUEです。

4. CSV経由。秒までの書式で保存したCSVを開き直すと、0.4秒は落ち、0.6秒は次の秒に繰り上がりました。どちらも、元の値とは一致しません。

5. Power Query経由。シートの日時をPower Queryの日時型で読み込んでシートに戻すと、603行中597行で値が変わりました(差は最大0.00005秒)。

秒に丸めて比べる方法と、その限界

下の表は、同じ条件の4行を並べたものです。丸めの考え方は【Excel関数】時間の丸めは順序で結果が変わる理由と対処にあります。

日時の「=」比較の結果を並べたExcelの表。同じ表示の日時でも、積み上げ計算・0.4秒・0.6秒・文字列の日時はすべて「X=Y」がFALSE。秒に丸めた比較は0.6秒だけFALSE

ROUNDやTEXTで秒にそろえると、積み上げ計算の差は消えました。ただし、秒の境目をまたぐ値は救えません。0.4秒と0.6秒は、TEXTで09:00:00と09:00:01に分かれ、同じ秒として比べられません。丸めは「計算の差を消す」ための道具で、「同じ受付を同じとみなす」ための道具ではありません。

VBAのDate型とDateDiffでも同じ

VBAで測ると、Date型の「=」も秒未満でFALSEでした。0.4秒進めた値は、元の値と一致しません。一方、CDate("2026/10/29 09:00:00")は、数値の日時と一致しました。

DateDiff("s", …)は、0.4秒の差で0、0.6秒の差で1、0.9秒と1.1秒の差で0でした。秒の差を数える関数も、小数をそのまま見ているのではなく、秒に丸めて数えているように見えます。また、DateAdd("s", 1, …)を3,600回繰り返した結果は、DateAdd("h", 1, …)と「=」で一致しませんでした。DateDiff("s")で差を数えると、3,600秒でした。

日時での突合をやめ、元IDで突き合わせる

日時を「同じかどうか」の根拠にすると、再取得や、経由する道具で壊れます。判定は、元のシステムが振るIDの一致にします。

3つの方式を、同じ5行の例で比べました。処理済みは、BabaとDoiの2人です。表のセルは、処理済みと判定された人です。

場面日時の一致連番(ROW()-1)の一致元IDの一致
取得直後Baba, DoiBaba, DoiBaba, Doi
再取得で全行の日時が+0.4秒なしBaba, DoiBaba, Doi
並べ替えBaba, DoiDoi, EndoBaba, Doi
先頭に新規行を挿入Baba, DoiAoki, ChibaBaba, Doi
先頭行を削除Baba, DoiChiba, EndoBaba, Doi

日時の一致は、再取得で日時が0.4秒ずれた場面で、処理済みが1人も見つかりませんでした。連番は、並べ替え・挿入・削除で、別の人を処理済みと判定しました。=ROW()-1の式で振った連番は、並べ替えると位置の番号に変わるためです。値に固定してから並べ替えると、連番は人についていきました。ただし、固定した連番でも、再取得のたびに先頭から振り直すなら、位置で決まる番号と同じです。

元IDで突き合わせた場合は、どの場面でもBabaとDoiだけが処理済みでした。

キー列に必要な3つの条件

  • 元のシステムが振り、再取得しても変わらない値であること
  • 重複しないこと。COUNTIFで2以上の行がないか確認します。重複していると、MATCHは最初の行だけを黙って返しました(今回の例では、1001のAokiでした)
  • 並べ替えや行の削除のあとでも、値が変わらないこと(式でなく値にしておく)

重複の確認は、【Power Query】結合で行が増える原因と重複の検知方法でも扱っています。手入力の値と自動計算の値を同じ表に置くときのキーの考え方は、【Excel】手入力と自動計算を共存させる上書き優先列の設計が参考になります。

まとめ

  • 日時の「=」は、積み上げ計算・秒未満の小数・文字列・CSV・Power Queryで一致しなくなる
  • 秒への丸めは計算の差を消せるが、秒の境目をまたぐ値は別の秒になる
  • 突合は、元のシステムが振る、重複のないIDで行う。連番は位置で決まるため、並べ替え・挿入・削除に耐えない

コメント

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