☰

Excel 2013：将一行转换为两行并编辑两行

tag-icon • tag-icon microsoft-excel vba microsoft-excel-2013
Excel 2013：将一行转换为两行并编辑两行

我想编写（复制）一个模块，将某些行转换为两行并编辑原始行。例如，我想转换此行：

6/1/2015    16:25    1:00

更改为：

6/1/2015    16:25    23:59
6/2/2015    0:00     1:00

我已经在另一列中确定了要执行此操作的行。行中还有其他列，我想直接复制下来。

我将非常感激您推荐的任何帮助和/或 VBA 教程，使我可以自己编写此程序。

图像

答案1

此代码应该有帮助：

Public Sub splittime()
    Application.ScreenUpdating = False
    firstrow = 2
    firstcopycolumn = 4
    lastcopycolumn = 7
    sheetname = "Sheet1"
    Dim wkb As Workbook
    Dim wks As Worksheet
    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets(sheetname)
    therow = firstrow
    usedrow = True
    affrows = 0
    While usedrow
        thedate = wks.Cells(therow, 1)
        If thedate <> "" Then
            firstdate = wks.Cells(therow, 2)
            lastdate = wks.Cells(therow, 3)
            If lastdate < firstdate Then
                Rows(therow + 1).Insert shift:=xlShiftDown
                wks.Cells(therow + 1, 1) = thedate + 1
                wks.Cells(therow + 1, 2) = "0:00"
                wks.Cells(therow + 1, 3) = lastdate
                wks.Range(Cells(therow + 1, firstcopycolumn), Cells(therow + 1, lastcopycolumn)).Value = wks.Range(Cells(therow, firstcopycolumn), Cells(therow, lastcopycolumn)).Value
                wks.Cells(therow, 3) = "23:59"
                affrows = affrows + 1
            End If
            therow = therow + 1
        Else
            usedrow = False
        End If
    Wend
    Application.ScreenUpdating = True
    themessage = MsgBox("Finished" & vbCrLf & "Affected rows: " & affrows, vbInformation)
End Sub

要使用宏，请按照以下步骤操作指导或者直接用 ALT+F11 打开 VBA/宏，在本工作簿插入一个新模块并粘贴代码。

该变量firstrow可以根据您的需要进行调整。

另外，借助变量firstcopycolumn，lastcopycolumn您可以定义要复制哪些范围的列。

答案2

你是否有特定的原因想要在 VBA 中执行此操作？我解决了类似的问题几个月前使用公式；我已经将该解决方案调整为您的问题。

但首先，让我确保我理解了您的问题。据我了解，您有一张如下所示的工作表：

       A          B       C       D       E         F
1   Date        Start    End    User    Color   Overnight
2   5/28/2015   15:00   21:00   Fred    Green
3   6/1/2015    16:25    1:00   Henry   Red     1
4   6/4/2015     9:00   13:00   Mary    Blue

其中每行代表一个事件，包含日期、开始时间、结束时间和其他数据。有些事件发生在夜间（即，它们在午夜前开始并在午夜后结束）。您已1在 Column中用 标记这些事件F （尽管无需手动执行此操作；您可以简单地设置F2为=B2>C2并向下拖动）。没有事件持续超过 24 小时，因此不可能有事件从星期一 10:00 开始并在星期二 11:00 结束（或者，如果有，您有某种方式来识别您未描述的那些事件，并且您已F正确设置了 Column）。无论如何，没有事件跨越超过两天（例如，您永远不会有事件从星期一 23:00 开始并持续到星期三 1:00）。您希望将每个多日（即夜间）事件分成两行：第一天一行，从开始时间到午夜（或 23:59），第二天另一行，从午夜到结束时间。您希望与该事件相关的所有其他数据都复制到两行上。因此，对于上述数据，您需要

       A          B       C       D       E
1   Date        Start    End    User    Color
2   5/28/2015   15:00   21:00   Fred    Green
3   6/1/2015    16:25   23:59   Henry   Red
4   6/2/2015     0:00    1:00   Henry   Red
5   6/4/2015     9:00   13:00   Mary    Blue

因此。

我的解决方案：

我假设您使用的列不超过 23 列，因此列X、Y和Z可用作“辅助列”。

  • 创建新工作表。假设现有工作表为Sheet1，新工作表为Sheet2。以下步骤将数据从Sheet1复制到Sheet2，并拆分行。
  • 将列标题从Sheet1第 1 行 复制到Sheet2第 1 行。
  • 进入。=INDEX(Sheet1!A:A, $X2)+$Y2​Sheet2!A2
  • 进入。=IF($Y2=0, INDEX(Sheet1!B:B, $X2), 0)​Sheet2!B2
  • 进入。=IF(AND($Y2=0,$Z2>0), TIME(23,59,59), INDEX(Sheet1!C:C, $X2))​Sheet2!C2
  • 进入=INDEX(Sheet1!D:D, $X2)并Sheet2!D2 拖动/填充到右侧以覆盖所有数据（即，E在我的示例中，到列）。
  • 复制Sheet1:A2:E2并粘贴格式（以及列宽，如果需要）到Sheet2:A2:E2。
  • 输入。这指定2了该Sheet2!X2行Sheet1这行（在 上Sheet2）将从中提取数据。
  • 输入。0​Sheet2!Y2
  • 输入=INDEX(Sheet1!F:F, $X2)。Sheet2!Z2这将创建“隔夜”指标的本地副本。
  • 选择Sheet2!A2:Z2并向下拖动/填充至第 3 行。
  • 改成。Sheet2!X3​=IF(AND(Y2=0,Z2>0), X2, X2+1)
  • 改成。Sheet2!Y3​=IF(AND(Y2=0,Z2>0), Y2+1, 0)
  • 选择Sheet2!A3:Z3并向下拖动/填充，以获取您需要的所有数据。

它看起来应该是这样的：

       A          B       C       D       E                          X   Y   Z
1   Date        Start    End    User    Color
2   5/28/2015   15:00   21:00   Fred    Green                        2   0   0
3   6/1/2015    16:25   23:59   Henry   Red                          3   0   1
4   6/2/2015     0:00    1:00   Henry   Red                          3   1   1
5   6/4/2015     9:00   13:00   Mary    Blue                         4   0   0

笔记：

  • 按照说明，指定行就该行Sheet2!XnSheet1n（开启Sheet2）将从中提取数据。
  • Sheet2!Yn是值内的上一个数字；即在一行内；即在事件内。对于隔夜事件，午夜前部分为 0，午夜后部分为 1。例如，由于第 3 行和第 4 行从第 3 行提取数据，因此我们有= =3，且, = 0, 1。Sheet2!XnSheet1YSheet2Sheet1X3X4Y3Y4

为了使其永久生效，您可以复制并粘贴值，并删除列X、Y和Z。

答案3

我会先创建 3 个新列。使用 Excel 公式根据原始列计算新数据。然后将 3 个新列剪切并粘贴到前 3 个列的底部。

相关内容