Excelで「日付＋時間」の範囲をCOUNTIFSで集計し、ドラッグコピーで時間帯や日付をずらしながら連続集計したい——ところが文字列の連結で条件を作ると正しく数えられない。この記事はその原因を数式エンジンの仕組みから解説し、オートフィルに強い“壊れない”書き方と運用のコツ、境界条件や深夜跨ぎへの実践的対処まで網羅的にまとめます。
前提とゴール
対象ブックの構成は次のとおりです。
- シート名：
Engineer Issue Data - 列N：対象日時（例：
2025/01/10 13:45） - 列H：地域（例：
Cebu） - 列AA：ステータス（例：
Open）
基準日はセル A264、時間帯は V1（開始）と W1（終了）。この条件で件数を数え、式を下方向へドラッグコピー（オートフィル）しても常に正しい集計を返すことがゴールです。
なぜ「&」でつないだ条件だと崩れるのか
COUNTIFS の条件引数は 文字列を受け取ります。たとえば ">=" & A264 は「>=44977」のような文字列（Excelの日付シリアル）に展開され、これ自体は問題ありません。ところが「日付＋時間」を ">=" & A264 & $V$1 のように「文字列連結」で作ると、Excelは日付と時間を結合した“見た目の文字列”を比較しようとします。結果として
- 「
>=44977」と「0.5（=12:00）」が単純連結され「>=449770.5」のような意図しない閾値になる - 開始と終了を別々に連結しても、日時の加算が起きていないため比較値がズレる
- オートフィル時に一部参照が相対・絶対の混在で崩れやすい
根本原因は「比較すべきは文字列ではなく、Excelの日時シリアル値（数値）」である点にあります。Excelでは「日付＝整数部」「時刻＝小数部」の同一数直線上にあります。したがって、A264 に V1, W1 を加算して数値にし、その数値を比較に使えばブレません。
壊れない基本形：数値で比較するCOUNTIFS
まずは最短で正しく動く“基本形”です。A264の日付にV1/W1の時刻を加算し、その合計（日時シリアル）を条件に使います。
=COUNTIFS(
'Engineer Issue Data'!$N:$N, ">=" & (A264 + $V$1),
'Engineer Issue Data'!$N:$N, "<=" & (A264 + $W$1),
'Engineer Issue Data'!$H:$H, "Cebu",
'Engineer Issue Data'!$AA:$AA, "Open"
)ポイント：
- (A264 + $V$1) のように丸かっこで先に加算して「数値」を作る
- 列参照は
$を付けて固定（例：$N:$N）。条件値のV1/W1は絶対参照（$V$1/$W$1）にし、A264は相対行のままにしてオートフィルで基準日だけが1行ずつ動くようにする
オートフィル設定の「当たり前」を表で確認
| 参照種類 | 例 | 動作 |
|---|---|---|
| 相対参照 | A264 | 下にコピーで A265, A266…に変化（基準日が行ごとに進む） |
| 絶対参照 | $V$1 / $W$1 | どこにコピーしても不変（全行で同一の開始・終了時刻） |
| 列固定 | 'Engineer Issue Data'!$N:$N | 列を固定（範囲はデータに応じて限定すると高速） |
半開区間にして境界を安全に：<= を < に見直す
時間帯を1時間刻み等でオートフィルする場合、終了条件を "<" で指定する「半開区間」がおすすめです。これにより隣接区間の重複カウントを防げます。
=COUNTIFS(
'Engineer Issue Data'!$N:$N, ">=" & (A264 + $V$1),
'Engineer Issue Data'!$N:$N, "<" & (A264 + $W$1),
'Engineer Issue Data'!$H:$H, "Cebu",
'Engineer Issue Data'!$AA:$AA, "Open"
)たとえば「09:00–10:00」「10:00–11:00」を順に数えるとき、<= 10:00 と >= 10:00 が同一時刻を重複させます。< 10:00 にすれば重複しません。
深夜跨ぎ（22:00–翌6:00）も1本の式で
終了時刻が開始時刻より早い場合は翌日に跨いでいます。W1<V1 をフラグにして「終了時刻へ+1日」するだけで、OR条件を使わず1本のCOUNTIFSで表現できます。終端は半開区間にします。
=COUNTIFS(
'Engineer Issue Data'!$N:$N, ">=" & (A264 + $V$1),
'Engineer Issue Data'!$N:$N, "<" & (A264 + $W$1 + ($W$1 < $V$1)),
'Engineer Issue Data'!$H:$H, "Cebu",
'Engineer Issue Data'!$AA:$AA, "Open"
)($W$1<$V$1) は TRUE=1/FALSE=0 として評価されるため、深夜跨ぎのときだけ +1 日され、範囲が [A264+V1, A264+1+W1) となります。開始側に+1日は不要です（>= A264+V1 が翌日の早朝も含むため）。
Microsoft 365なら LET で読みやすく保守しやすく
同じ計算を何度も書かないほうがヒューマンエラーを防げます。Microsoft 365/Excel 2021 以降なら LET で変数化しましょう。
=LET(
start, A264 + $V$1,
end, A264 + $W$1 + ($W$1 < $V$1),
COUNTIFS(
'Engineer Issue Data'!$N:$N, ">=" & start,
'Engineer Issue Data'!$N:$N, "<" & end,
'Engineer Issue Data'!$H:$H, "Cebu",
'Engineer Issue Data'!$AA:$AA, "Open"
)
)セル参照で条件を可変に（地域・ステータスを外出し）
地域やステータスを数式中の固定文字列にせず、シート上のセルに外出しすると運用がラクです（データの入力規則でプルダウン化も可能）。
地域: B1（例: Cebu）
状態: B2（例: Open）
=LET(
start, A264 + $V$1,
end, A264 + $W$1 + ($W$1 < $V$1),
COUNTIFS(
'Engineer Issue Data'!$N:$N, ">=" & start,
'Engineer Issue Data'!$N:$N, "<" & end,
'Engineer Issue Data'!$H:$H, $B$1,
'Engineer Issue Data'!$AA:$AA, $B$2
)
)計算を軽くするコツ（大規模データ向け）
- 列全体参照を避ける：行数が多いほど計算コストが増えます。実データ範囲（例：
$N$2:$N$50000）に限定しましょう。 - テーブル化（Ctrl+T）：データをテーブルにしておくと、増えた行分だけ自動で範囲が伸びます。構造化参照で式も読みやすくなります。
- 再計算制御：大量のオートフィルを行う前に自動計算を一時的に手動へ切替えると操作が快適になります（作業後に自動へ戻す）。
- 条件の外出し：文字列（地域・状態）をセル参照化すると、式の再解析負荷が下がる場合があります。
構造化参照の例（テーブル名：IssueTbl）
=LET(
start, A264 + $V$1,
end, A264 + $W$1 + ($W$1 < $V$1),
COUNTIFS(
IssueTbl[対象日時], ">=" & start,
IssueTbl[対象日時], "<" & end,
IssueTbl[地域], $B$1,
IssueTbl[ステータス], $B$2
)
)「値が文字列だった」ケースのリカバリ
列Nの日時が見た目は日時でも、内部は「文字列」というケースがあります。COUNTIFS は文字列比較になるため意図通りに判定できません。対処は次のとおりです。
- 区切り位置指定（データ > テキストを列に分割）：日付・時刻を一度「日付」「時刻」型に変換する（最も確実）。
- VALUE/DATEVALUE/TIMEVALUEで数値化：補助列に
=VALUE(N2)、または=DATEVALUE(LEFT(N2,10))+TIMEVALUE(RIGHT(N2,8))などでシリアル化し、その列をCOUNTIFSに参照させる。 - 二重負号：
=--N2として数値化できる場合もあります（文字列がExcelの日時書式と一致していることが条件）。
1時間刻み・15分刻みのオートフィル実例
行方向に「基準日＋時間帯」をずらしながら集計する例を示します。基準日の縦リスト（A264:A287）と、時間帯の開始・終了ペアを横に持つ場合です。
| セル | 意味 | 例 |
|---|---|---|
| V1 | 開始時刻 | 9:00 |
| W1 | 終了時刻 | 10:00 |
| V2 | 次の開始 | =V1+TIME(1,0,0) |
| W2 | 次の終了 | =W1+TIME(1,0,0) |
| … | 以降オートフィル | 刻み幅を TIME(0,15,0) にすれば15分刻み |
集計セル（例：D264）には次式を置き、行方向にコピーします。
=COUNTIFS(
'Engineer Issue Data'!$N:$N, ">=" & (A264 + V$1),
'Engineer Issue Data'!$N:$N, "<" & (A264 + W$1),
'Engineer Issue Data'!$H:$H, $B$1,
'Engineer Issue Data'!$AA:$AA, $B$2
)V$1/W$1 のように行だけ固定することで、横方向にコピーしても時間帯の列が自動追従します。
FILTERで数えてもOK（Microsoft 365）
動的配列関数が使えるなら、レコードを絞り込んで ROWS で数える書き方も直感的です。比較の考え方（数値で判定）はまったく同じです。
=LET(
start, A264 + $V$1,
end, A264 + $W$1 + ($W$1 < $V$1),
data, FILTER('Engineer Issue Data'!$A:$AA,
('Engineer Issue Data'!$N:$N >= start) *
('Engineer Issue Data'!$N:$N < end) *
('Engineer Issue Data'!$H:$H = $B$1) *
('Engineer Issue Data'!$AA:$AA = $B$2)),
ROWS(data)
)COUNTIFSに比べると表示用にも流用しやすい（抽出結果がそのまま見える）のが利点です。
境界と仕様の豆知識
- 1900/1904日付システム：Excel for Windows は通常 1900 日付システム、Mac の一部は 1904 を使う設定が残っていることがあります。混在するとシリアル値が4年強ずれます。ブックの「計算方法」設定を統一してください。
- 秒精度：Excelの時刻は1日=1の実数。1秒は
1/86400です。秒まで条件に含めるときは小数の丸め（ROUND/MROUND）に注意。 - 表示書式と実体の違い：列N、A264、V1、W1はいずれも「表示書式」を日付/時刻にしておくと確認しやすいですが、計算は書式ではなく内部の数値で行われます。トラブル時は
=TYPE(N2)で型（1=数値、2=文字列）を確認すると早いです。
デバッグチェックリスト（現場で役立つ最短ルート）
- 列Nの1セルを選び、編集バーに
=N2と入れて Enter。右寄せ＋数式バーで小数が見えるなら数値、左寄せで見た目だけ日時なら文字列です。 - 問題のCOUNTIFSを一時的に次のように分解して確認：
=A264+$V$1と=A264+$W$1+($W$1<$V$1)を別セルで可視化。意図した日時になっているか、表示書式を「yyyy/mm/dd h:mm:ss」に変えて目視で検証。 - 終了側の不等号を
<=から<にし、隣接区間の二重カウントが消えるかを確認。 - 範囲の固定（
$）が崩れていないか。キーボードのF4で切替えるのが最速です。
高速運用テンプレート（丸ごとコピペOK）
地域・ステータスをセル参照、深夜跨ぎ対応、半開区間・限定範囲・テーブル非依存の実用テンプレです。範囲は実データに合わせて調整してください。
=LET(
nRange, 'Engineer Issue Data'!$N$2:$N$50000,
hRange, 'Engineer Issue Data'!$H$2:$H$50000,
sRange, 'Engineer Issue Data'!$AA$2:$AA$50000,
start, A264 + $V$1,
end, A264 + $W$1 + ($W$1 < $V$1),
COUNTIFS(nRange, ">=" & start,
nRange, "<" & end,
hRange, $B$1,
sRange, $B$2)
)よくあるNG例と改善の観点
| NGパターン | 問題点 | 改善 |
|---|---|---|
">=" & A264 & $V$1 | 日付と時刻の連結であり加算ではない。しばしば桁違いの閾値に。 | ">=" & (A264 + $V$1) のように丸かっこで先に加算して数値比較。 |
終了側を <= で書く | 隣接区間で境界が二重計上。 | 終了は < にして半開区間に。 |
列全体参照（N:N）のまま | データ量が増えると計算が極端に遅くなる。 | 実データ範囲に限定、またはテーブル化で自動拡張。 |
| 深夜跨ぎをORで分岐 | 式が長く保守が難しい。 | + ($W$1<$V$1) を活用して1本に集約。 |
表示整備で「見える化」も同時に
- セル書式：
yyyy/mm/dd h:mmやh:mm:ssにして境界の確認を容易に。 - 条件付き書式：集計対象の時間帯に入る行に色を付けると、COUNT結果と視覚が一致して検証が速くなります。数式例：
=AND($N2 >= A$264+$V$1, $N2 < A$264+$W$1+($W$1<$V$1))
まとめ
COUNTIFSで「日付＋時間」を正しくオートフィルさせる鍵は、日時を文字列ではなく数値（シリアル）で比較すること、そして開始は「>=」・終了は「<」の半開区間に統一することです。さらに、深夜跨ぎは終了側にだけ+1日を加える小技で、式を短く頑丈にできます。範囲固定・テーブル化・表示整備までセットで押さえれば、行方向にも列方向にも崩れない堅牢な集計基盤になります。
実運用ショートレシピ（完成版）
=LET(
// 基準と範囲
start, A264 + $V$1,
end, A264 + $W$1 + ($W$1 < $V$1),
Nrng, 'Engineer Issue Data'!$N$2:$N$200000,
Hrng, 'Engineer Issue Data'!$H$2:$H$200000,
Arng, 'Engineer Issue Data'!$AA$2:$AA$200000,
// 集計
COUNTIFS(Nrng, ">=" & start,
Nrng, "<" & end,
Hrng, $B$1,
Arng, $B$2)
)この形にしておけば、基準日（A列）をオートフィルで動かすだけで、時間帯が固定された日次集計が縦方向に並びます。時間帯も横方向に動かすなら V$1/W$1 のように行だけ固定するのがコツです。ぜひ現場シートへそのまま貼り付けて試してください。
コメント