Excel: как добавить строку за каждый час между двумя разными временами

Question

Я много искал, чтобы выяснить, как добавить строки между двумя строками на основе времени и даты, но, к сожалению, я не смог найти формулу. Я действительно ценю, если кто-нибудь может мне помочь.

Как вы можете видеть на картинке, у меня был столбец J, который является датой и временем, и столбец M, который является результатом.

Чтобы упростить мою работу, я добавил два столбца K и L, которые представляют собой соответственно дату и время

Я хочу посмотреть на первые две строки в столбце L, и если между ними пропущен какой-либо час, то добавьте одну строку для каждого часа и поместите ячейку в столбце Result в "0".

На предыдущем рисунке пропущено два часа между 00:00 и 3:00, поэтому я хочу, чтобы Excel добавил две новые строки между ними и добавил "0" в столбец "M".

Также проверьте, пропущен ли один день между двумя строками, а затем добавьте 24 ряда между ними. например, мне нужно 50 новых строк между 1 января 2010 года 11:00 и 3 января 2010 года 14:00 и нулевым результатом для каждой строки в столбце М.

Конечный результат будет выглядеть так:

Answer 1

Вы должны использовать этот макрос:

Public Sub fillHours()
theEnd = False
i = 2
timecolumn = 7
While theEnd = False:
    firstDate = Me.Cells(i, timecolumn - 1)
    If firstDate <> "" Then
        secondDate = Me.Cells(i + 1, timecolumn - 1)
        If firstDate = secondDate Then
            firstHour = Me.Cells(i, timecolumn)
            secondHour = Me.Cells(i + 1, timecolumn)
            diffHour = ((secondHour * 24) - (firstHour * 24)) - 1
            If diffHour > 0 Then
                For j = 1 To diffHour
                    Rows(i + j).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromAbove
                    newHour = Str(i + j - 1) & ":00"
                    newHour = Right(newHour, Len(newHour) - 1)
                    Me.Cells(i + j, timecolumn) = newHour
                    Me.Cells(i + j, timecolumn - 1) = Me.Cells(i, timecolumn - 1)
                    Me.Cells(i + j, timecolumn + 1) = 0
                Next j
            End If
        ElseIf firstDate < secondDate Then
            diffDate = secondDate - firstDate
            maxInsertedRows = diffDate * 24
            firstHour = Me.Cells(i, timecolumn)
            secondHour = Me.Cells(i + 1, timecolumn)
            diffHour = ((secondHour * 24) - (firstHour * 24)) - 1
            If diffHour < 0 Then
                diffHour = (23 - (firstHour * 24)) + (secondHour * 24)
                realInsertedRows = maxInsertedRows - (24 - diffHour)
            Else
                realInsertedRows = maxInsertedRows - diffHour + 2
            End If
            For j = 1 To realInsertedRows
                Rows(i + j).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromAbove
                basicHour = i + j - 1
                divbasicHour = Int(basicHour / 24)
                basicHour = basicHour - (divbasicHour * 24)
                newHour = Str(basicHour) & ":00"
                newHour = Right(newHour, Len(newHour) - 1)
                Me.Cells(i + j, timecolumn) = newHour
                Me.Cells(i + j, timecolumn + 1) = 0
                If newHour <> "0:00" Then
                    Me.Cells(i + j, timecolumn - 1) = Me.Cells(i + j - 1, timecolumn - 1)
                Else
                    Me.Cells(i + j, timecolumn - 1) = Me.Cells(i + j - 1, timecolumn - 1) + 1
                End If
            Next j

        End If
        i = i + 1
    Else
        theEnd = True
    End If
Wend
End Sub

Откройте Macros с помощью клавиш ALT+F11, дважды щелкните по листу с левой стороны и вставьте код с правой стороны. Убедитесь, что значение в строке timecolumn = 7 совпадает с номером столбца вашего столбца Time .