如何高效基于QUALIFIER_CD为散点图系列设置差异化标记样式？

场景适配：Excel 快速批量处理（适合办公报告场景） #

• 数据准备：确保数据集为规范表格，列包含STATION_ID、COLLECTION_DT、PLOTVALUE、QUALIFIER_CD，无合并单元格。
• 一键生成多系列图表：
  1. 选中全量数据，插入「散点图（带平滑线和标记）」，默认生成单个系列。
  2. 用UNIQUE函数提取所有唯一STATION_ID（例：=UNIQUE(Sheet1!$A:$A)），再通过名称管理器+OFFSET函数批量创建每个站点的动态数据源，快速拆分出所有STATION_ID对应的系列。
• 批量设置标记样式：
  直接用VBA宏一次性完成所有数据点的标记设置，无需手动调整：

  Sub SetMarkerByQualifier()
      Dim cht As Chart
      Dim ser As Series
      Dim xVals As Variant
      Dim yVals As Variant
      Dim qualVals As Variant
      Dim i As Integer
      
      Set cht = ActiveChart
      qualVals = Sheet1.Range("D:D").Value 'D列为QUALIFIER_CD
      
      For Each ser In cht.SeriesCollection
          xVals = ser.XValues
          yVals = ser.Values
          ser.MarkerStyle = xlMarkerStyleNone
          
          For i = 1 To UBound(xVals)
              Dim rowIdx As Integer
              rowIdx = Application.Match(xVals(i), Sheet1.Range("B:B"), 0) 'B列为COLLECTION_DT
              Select Case qualVals(rowIdx, 1)
                  Case "U"
                      ser.Points(i).MarkerStyle = xlMarkerStyleSquare
                      ser.Points(i).MarkerForegroundColorIndex = 46
                      ser.Points(i).MarkerBackgroundColorIndex = xlColorIndexNone
                  Case "J"
                      ser.Points(i).MarkerStyle = xlMarkerStyleCircle
                      ser.Points(i).MarkerForegroundColorIndex = 46
                      ser.Points(i).MarkerBackgroundColorIndex = xlColorIndexNone
                  Case ""
                      ser.Points(i).MarkerStyle = xlMarkerStyleCircle
                      ser.Points(i).MarkerForegroundColorIndex = 46
                      ser.Points(i).MarkerBackgroundColorIndex = 46
              End Select
          Next i
      Next ser
  End Sub
  

  运行前选中目标图表，确保数据列对应正确，一次执行即可完成所有系列的标记配置。

自动化进阶：Python 脚本批量生成（适合大规模数据集） #

如果数据集量级大、分组多，用Python实现完全自动化，代码可重复复用：

• 安装依赖：

  pip install pandas matplotlib
  

• 核心代码：

  import pandas as pd
  import matplotlib.pyplot as plt
  from matplotlib.lines import Line2D
  
  # 读取数据集
  df = pd.read_csv("your_dataset.csv")
  # 提取所有站点
  unique_stations = df["STATION_ID"].unique()
  
  # 定义标记规则
  marker_config = {
      "U": {"marker": "s", "color": "#ff9900", "fillstyle": "none"},
      "J": {"marker": "o", "color": "#ff9900", "fillstyle": "none"},
      "": {"marker": "o", "color": "#ff9900", "fillstyle": "full"}
  }
  
  # 创建图表
  fig, ax = plt.subplots(figsize=(12, 6))
  
  # 遍历每个站点绘制线条和标记
  for station in unique_stations:
      station_df = df[df["STATION_ID"] == station]
      # 绘制基础线条
      ax.plot(station_df["COLLECTION_DT"], station_df["PLOTVALUE"],
              color="#ff9900", linestyle="-", linewidth=1)
      # 按QUALIFIER_CD绘制对应标记
      for qual_code, group in station_df.groupby("QUALIFIER_CD"):
          config = marker_config.get(qual_code, marker_config[""])
          ax.scatter(group["COLLECTION_DT"], group["PLOTVALUE"],
                     marker=config["marker"], color=config["color"],
                     fillstyle=config["fillstyle"], s=50)
  
  # 自定义图例
  legend_elements = [
      Line2D([0], [0], color='#ff9900', lw=2, label='Series Line'),
      Line2D([0], [0], marker='s', color='#ff9900', linestyle='None',
             markerfacecolor='none', markersize=10, label='U (Hollow Square)'),
      Line2D([0], [0], marker='o', color='#ff9900', linestyle='None',
             markerfacecolor='none', markersize=10, label='J (Hollow Circle)'),
      Line2D([0], [0], marker='o', color='#ff9900', linestyle='None',
             markerfacecolor='#ff9900', markersize=10, label='Empty (Solid Circle)')
  ]
  ax.legend(handles=legend_elements)
  
  # 配置轴标签与布局
  ax.set_xlabel("COLLECTION_DT")
  ax.set_ylabel("PLOTVALUE")
  plt.xticks(rotation=45)
  plt.tight_layout()
  # 保存或展示图表
  plt.savefig("station_trend_chart.png")
  plt.show()
  

  每次仅需替换数据集路径，运行脚本即可自动生成符合要求的图表，适合季度重复性任务。

内容的提问来源于stack exchange，提问作者A.will