Google Sheets：如何按Slot start列自动拆分数据集为对照组与测试组

核心逻辑 #

• 按Slot start字段分组，对每个时段的数据均分两组
• 自动将两组数据分左右栏输出（左栏对照组，右栏测试组）
• 适配任意数据量和各时段的配送数变化

完整VBA代码 #

Sub SplitDataBySlot()
    Dim srcWS As Worksheet, destWS As Worksheet
    Dim lastRow As Long, i As Long, slotRowCount As Long
    Dim currentSlot As String, startRow As Long
    Dim halfCount As Integer, rng As Range
    
    ' 配置工作表（按需修改）
    Set srcWS = ThisWorkbook.Worksheets("Sheet1") ' 原数据所在表
    Set destWS = ThisWorkbook.Worksheets("Sheet2") ' 结果输出表
    
    ' 清空输出表并复制表头
    destWS.Cells.Clear
    srcWS.Rows(1).Copy destWS.Cells(1, 1)
    srcWS.Rows(1).Copy destWS.Cells(1, srcWS.UsedRange.Columns.Count + 2) ' 空列分隔两组
    
    lastRow = srcWS.Cells(srcWS.Rows.Count, "A").End(xlUp).Row ' 假设Slot start在A列，需根据实际调整
    startRow = 2 ' 数据起始行
    
    ' 遍历每个Slot时段分组处理
    Do While startRow <= lastRow
        currentSlot = srcWS.Cells(startRow, "A").Value
        ' 统计当前时段的总行数
        slotRowCount = 0
        For i = startRow To lastRow
            If srcWS.Cells(i, "A").Value = currentSlot Then
                slotRowCount = slotRowCount + 1
            Else
                Exit For
            End If
        Next i
        
        halfCount = Int(slotRowCount / 2)
        ' 复制前半部分到对照组（左栏）
        Set rng = srcWS.Rows(startRow & ":" & startRow + halfCount - 1)
        rng.Copy destWS.Cells(destWS.Cells(destWS.Rows.Count, 1).End(xlUp).Row + 1, 1)
        
        ' 复制后半部分到测试组（右栏）
        Set rng = srcWS.Rows(startRow + halfCount & ":" & startRow + slotRowCount - 1)
        rng.Copy destWS.Cells(destWS.Cells(destWS.Rows.Count, srcWS.UsedRange.Columns.Count + 2).End(xlUp).Row + 1, srcWS.UsedRange.Columns.Count + 2)
        
        startRow = startRow + slotRowCount
    Loop
    
    MsgBox "数据拆分完成！"
End Sub

使用步骤 #

1. 打开目标Excel文件，按Alt+F11进入VBA编辑器
2. 右键点击工作簿 → 插入 → 模块
3. 将上述代码粘贴到模块窗口
4. 调整代码中的工作表名称和Slot start所在列（比如原数据在"数据源"表，就把Sheet1改成"数据源"；若Slot start在C列，把"A"改成"C"）
5. 返回Excel，按Alt+F8选择SplitDataBySlot执行宏

补充说明 #

• 若某时段行数为奇数，测试组会比对照组多1行，可自行修改halfCount = Int(slotRowCount / 2)为halfCount = Round(slotRowCount / 2, 0)调整分配逻辑
• 原数据需按Slot start排序，否则分组会混乱。如果未排序，可在代码开头添加排序逻辑：

  srcWS.Range("A1:" & srcWS.Cells(lastRow, srcWS.UsedRange.Columns.Count).Address).Sort _
      Key1:=srcWS.Range("A1"), Order1:=xlAscending, Header:=xlYes
  

• 若要自动创建输出表，可替换Set destWS = ThisWorkbook.Worksheets("Sheet2")为：

  On Error Resume Next
  Set destWS = ThisWorkbook.Worksheets("Sheet2")
  On Error GoTo 0
  If destWS Is Nothing Then
      Set destWS = ThisWorkbook.Worksheets.Add(After:=srcWS)
      destWS.Name = "Sheet2"
  End If
  

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