如何用Excel函数验证月度测量日期是否符合合规要求？

Excel 分步实现 #

1. 计算每月合规日期 #

假设数据中A列为实际测量日期，新增两列处理合规日期：

• B列（基准日期）：公式 =DATE(YEAR(A2), MONTH(A2), 7)，生成当月7日的基准日期
• C列（合规日期）：公式 =IF(WEEKDAY(B2,2)=6, B2-1, IF(WEEKDAY(B2,2)=7, B2+1, B2))
  （WEEKDAY(...,2) 定义周一=1、周日=7，周六（6）往前调1天到周五，周日（7）往后调1天到周一）

若使用Excel 365/2021，可简化为单公式：
=LET(d,A2,base,DATE(YEAR(d),MONTH(d),7),IF(WEEKDAY(base,2)=6,base-1,IF(WEEKDAY(base,2)=7,base+1,base)))

2. 判断单次测量是否合规 #

新增D列（合规判断）：公式 =IF(A2=C2, "合规", "不合规")
若需判断当月是否完成合规测量（只要有1次合规即算当月达标），新增E列（当月达标）：
=MAX(IF((YEAR($A$2:$A$10000)=YEAR(A2))*(MONTH($A$2:$A$10000)=MONTH(A2)),IF($D$2:$D$10000="合规",1,0),0))
（Excel 365直接回车，旧版本按Ctrl+Shift+Enter作为数组公式输入）

3. 生成合规性汇总表 #

• 方式1：优化数据透视表
  选中全量数据插入透视表：

  • 行区域：拖入「地点」
  • 列区域：新增辅助列F列（年月）（公式=TEXT(A2,"YYYY-MM")），拖入该字段
  • 值区域：拖入「合规判断」设为「计数」，或拖入「当月达标」设为「最大值」（1=达标，0=未达标）
  • 优化可读性：右键列标签→「分组」按年份合并月份，用条件格式标记未达标月份

• 方式2：函数手动汇总
  新建汇总表，行填所有地点、列填所有年月，用以下公式计算：
  合规次数：=COUNTIFS($地点列,$F2,$年月列,G$1,$合规判断列,"合规")
  总测量次数：=COUNTIFS($地点列,$F2,$年月列,G$1)
  合规率：=H2/I2（设置为百分比格式）

Python（Pandas）高效实现 #

适合大数量级数据或自动化处理场景：

1. 数据预处理与合规日期计算 #

import pandas as pd
import numpy as np

# 读取数据（假设CSV文件，日期列名为"测量日期"，地点列名为"地点"）
df = pd.read_csv("现场数据.csv", parse_dates=["测量日期"])

# 生成年月分组字段
df["年月"] = df["测量日期"].dt.to_period("M")

# 计算当月基准日期（7日）
df["基准日期"] = df["测量日期"].apply(lambda x: pd.Timestamp(x.year, x.month, 7))

# 判断基准日期星期几（Monday=0, Sunday=6）
df["星期几"] = df["基准日期"].dt.weekday

# 调整为合规日期：周六（5）减1天，周日（6）加1天
df["合规日期"] = np.where(
    df["星期几"] == 5,
    df["基准日期"] - pd.Timedelta(days=1),
    np.where(df["星期几"] == 6, df["基准日期"] + pd.Timedelta(days=1), df["基准日期"])
)

# 判断单次测量是否合规
df["合规"] = df["测量日期"].dt.date == df["合规日期"].dt.date

2. 生成合规性汇总表 #

# 按地点+年月分组，统计核心指标
summary = df.groupby(["地点", "年月"])["合规"].agg(
    总测量次数="count",
    合规次数="sum"
).reset_index()
summary["合规率"] = (summary["合规次数"] / summary["总测量次数"]).round(4) * 100

# 转成宽表（地点为行，年月为列）提升可读性
wide_summary = summary.pivot(index="地点", columns="年月", values="合规率")

# 保存结果到Excel
wide_summary.to_excel("合规性汇总表.xlsx")

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