如何用Excel/Alteryx/Tableau清洗字符串数据并判定指标日期状态

针对你需要将手动录入的Excel字符串数据表转换、判断状态并对接Tableau的需求，以下是三种实用工具及具体实现方法：

工具1：Excel自带功能（适合轻量数据，快速上手） #

• 字符串转日期：
  针对带标识的日期字符串（如2024-05-111），用函数提取日期部分并转换：DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2))；若为x代表本月运行，用EOMONTH(TODAY(),0)生成当月最后一天作为基准日期。
• 结合频率生成到期日：
  用EDATE()函数根据频率计算：
  • 月度：EDATE(转换后日期,1)
  • 季度：EDATE(转换后日期,3)
  • 半年：EDATE(转换后日期,6)
  • 年度：EDATE(转换后日期,12)
• 状态判断：
  先通过CELL("color",A2)识别绿色单元格（返回1代表绿色填充），再结合日期判断：

  =IF(CELL("color",A2)=1,"完成",IF(DATEDIF(到期日,TODAY(),"d")>30,"逾期",IF(DATEDIF(到期日,TODAY(),"d")>=0,"到期","处理中")))
  

• 筛选活跃指标：直接用Excel筛选功能，排除已完成且无需重复执行的任务。
• 对接Tableau：保存为.xlsx或.csv格式，直接导入Tableau即可。

工具2：Power Query（适合中量数据，自动化批量处理） #

• 导入数据：Excel中点击「数据」-「从表格/范围」，进入Power Query编辑器。
• 字符串转日期：添加自定义列，提取日期部分并转换：

  Date.From(Text.BeforeDelimiter([任务标识],"-"))
  

  针对x标识，生成当月起始日期：Date.StartOfMonth(DateTime.LocalNow())
• 生成到期日：添加自定义列，根据频率映射到期时间：

  switch [Frequency],
      "月度", Date.AddMonths([转换后日期], 1),
      "季度", Date.AddMonths([转换后日期], 3),
      "半年", Date.AddMonths([转换后日期], 6),
      "年度", Date.AddMonths([转换后日期], 12),
      [转换后日期]
  

• 状态判断：加载单元格格式信息后，添加自定义列判断状态：

  if [填充颜色] = "#00FF00" then "完成"
  else if Duration.Days(DateTime.LocalNow() - [到期日]) > 30 then "逾期"
  else if Duration.Days(DateTime.LocalNow() - [到期日]) >= 0 then "到期"
  else "处理中"
  

• 筛选活跃指标：添加筛选器，排除状态为「完成」的非重复任务。
• 对接Tableau：将处理后的数据加载回Excel，或直接导出为.csv导入Tableau。

工具3：Python（适合大量数据，可脚本化复用） #

基于pandas和openpyxl库实现：

• 读取数据与转日期：

  import pandas as pd
  from openpyxl import load_workbook
  
  df = pd.read_excel("你的数据表.xlsx", engine="openpyxl")
  # 转换带标识的日期字符串
  df["转换日期"] = df["任务标识"].str.extract(r'(\d{4}-\d{2}-\d{2})').apply(pd.to_datetime)
  # 处理x标识的本月运行日期
  df.loc[df["任务标识"] == "x", "转换日期"] = pd.Timestamp.now().floor('D').replace(day=1)
  

• 生成到期日：

  def get_due_date(date, freq):
      if freq == "月度":
          return date + pd.DateOffset(months=1)
      elif freq == "季度":
          return date + pd.DateOffset(months=3)
      elif freq == "半年":
          return date + pd.DateOffset(months=6)
      elif freq == "年度":
          return date + pd.DateOffset(months=12)
      return date
  
  df["到期日"] = df.apply(lambda x: get_due_date(x["转换日期"], x["Frequency"]), axis=1)
  

• 状态判断：读取单元格颜色并判断状态：

  wb = load_workbook("你的数据表.xlsx")
  ws = wb.active
  color_list = []
  # 假设任务标识在第1列，从第2行开始读取
  for row in ws.iter_rows(min_row=2, max_col=1):
      cell = row[0]
      # 判断是否为绿色填充（RGB格式需匹配实际颜色）
      color_list.append(cell.fill.fgColor.rgb == "00FF0000")
  
  df["已完成"] = color_list
  today = pd.Timestamp.now().floor('D')
  df["状态"] = df.apply(lambda x: "完成" if x["已完成"] 
                      else "逾期" if (today - x["到期日"]).days > 30
                      else "到期" if (today - x["到期日"]).days >= 0
                      else "处理中", axis=1)
  

• 筛选活跃指标：

  df_active = df[df["状态"] != "完成"]  # 根据业务规则调整筛选条件
  

• 对接Tableau：导出为.csv格式：df_active.to_csv("处理后数据.csv", index=False)，再导入Tableau。

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