如何在Excel中按日期统计数据列的连续指定区间值最大出现次数？

数据结构说明 #

• 设备每日采集数据包含三列：
  • Date：日期
  • Time：时间
  • Value：采集的数据值
• 数据量：近一年的每日时序数据

统计需求 #

找出Value列中数值落在指定区间的连续出现次数，统计每日该连续次数的最大值。

示例数据 #

预期结果（统计区间：4-8） #

实现方案 #

方案1：Python Pandas（适合本地数据处理） #

通过分组和累计计数实现，处理近一年数据效率足够：

import pandas as pd

# 读取数据（假设数据存储在CSV文件中）
df = pd.read_csv("device_data.csv")

# 1. 标记符合区间的行：Value在4-8之间为1，否则为0
df['in_range'] = df['Value'].between(4, 8).astype(int)

# 2. 按日期分组，计算连续符合条件的计数
# 当in_range为0时，重置计数
df['group_id'] = df.groupby('Date')['in_range'].apply(lambda x: (x == 0).cumsum())

# 3. 统计每个分组的计数，再按日期取最大值
result = df.groupby(['Date', 'group_id'])['in_range'].count()\
           .groupby('Date').max().reset_index(name='Max Count for Day')

# 输出结果
print(result)

方案2：SQL（适合数据库端批量处理） #

利用窗口函数实现，适合大数据量直接在数据库中计算：

WITH marked_data AS (
    SELECT
        Date,
        Value,
        -- 标记是否在目标区间内
        CASE WHEN Value BETWEEN 4 AND 8 THEN 1 ELSE 0 END AS in_range,
        -- 按日期分组，当不在区间时生成新的分组ID
        SUM(CASE WHEN Value BETWEEN 4 AND 8 THEN 0 ELSE 1 END) OVER (
            PARTITION BY Date ORDER BY Time
        ) AS group_id
    FROM device_data
),
group_counts AS (
    SELECT
        Date,
        group_id,
        COUNT(*) AS consecutive_count
    FROM marked_data
    WHERE in_range = 1
    GROUP BY Date, group_id
)
SELECT
    Date,
    MAX(consecutive_count) AS "Max Count for Day"
FROM group_counts
GROUP BY Date
ORDER BY Date;

内容的提问来源于stack exchange，提问作者Adi kul