Выбор и перенос максимальных значений из столбца с учетом временного интервала.

[Tidus]

Сообщение от SAS888

Посмотрите вложение. В нем предложенный мной макрос с подробными комментариями.

Спасибо, ваш код действительно работает, я проверил, но пока только на вашем листе, сейчас попробую разобраться с ним и переназначить имена ячеек на своём листе и будет видно Сегодня времени не будет, буду в пятницу в 18:00 здесь, отпишусь обязательно.

[Tidus]

Здравствуйте, приветствую всех! Уважаемый SAS888 я вернулся, давайте продолжим. Задачу пока не решил, но пока пытался её решить, снова возникли вопросы в части этого кода.

Private Sub CommandButton1_Click()

'Определяем переменные.
    Dim i As Long, j As Long, k As Long, q As String, w As String, a(), b(), c()
    
'Запрещаем обновление экрана и формируем двумерный массив a из значений ячеек в диапазоне
'от "A1" до последней заполненной ячейки в столбце "B".
    Application.ScreenUpdating = False: a = Range([A1], Cells(Rows.Count, 2).End(xlUp)).Value
    
'Присваиваем переменным q и w текстовое значение границ временного интервала (0:00:00 и 0:20:00).
    q = CStr(TimeSerial(0, 0, 0)): w = CStr(TimeSerial(0, 20, 0))
    
'Определяем размерность массивов b и c равную 1-й размерности массива a (это с запасом)
'также, переменным j и k присваиваем начальные значения.
    ReDim b(1 To UBound(a, 1)): j = 1: k = 1: ReDim c(1 To UBound(a, 1), 1 To 1)
    
'Организовываем цикл по всем элементам 1-й размерности массива a (значения времени).
    For i = 1 To UBound(a, 1)
    
'Сравниваем преобразованное в текст значение.
        Select Case CStr(CDate(a(i, 1)))
        
'Если это "0:00:00", то обнуляем одномерный массив b, устанавливаем j=1 и первому элементу
'массива b присваиваем соответствующее значение 2-й размерности массива a.
        Case q: ReDim b(1 To UBound(a, 1)): j = 1: b(j) = a(i, 2)
        
'Если это не "0", но < чем "0:20:00", то наращиваем указатель j и следующему элементу
'массива b присваиваем соответствующее значение 2-й размерности массива a.
        Case Is < w: j = j + 1: b(j) = a(i, 2)
        
'Если это "0:20:00", то также наращиваем указатель j, следующему элементу
'массива b присваиваем соответствующее значение 2-й размерности массива a,
'присваиваем текущему (по указателю k) элементу массива c максимальное значение массива b
'и инкременируем указатель c.
        Case w: j = j + 1: b(j) = a(i, 2): c(k, 1) = Application.Max(b): k = k + 1
        
'Заканчиваем сравнение. Тут нужно заметить, что оператор Select Case...Case...End Select
'проверяет условия в описанном порядке и при первом выполнении условия выполняет
'соответствующие действия и выходит из проверки, т.е. остальные условия не проверяются.
'Это как раз то, что нам в данном случае требуется.
        End Select
        
'Переходим к следующему значению.
    Next
    
'Вставляем полученный массив c в столбец "C" рабочего листа.
    Range([C1], Cells(UBound(c, 1), 3)).Value = c

End Sub

Вопрос по коду:
1. Здесь вы описываете выборку из диапазона из идущих подряд столбцов А и B. Как изменится код, если мне нужно будет установить взаимодействие например из столбцов А и С, а столбец B вообще не должен участвовать в расчетах,то есть - его не надо включать в диапазон.
2. Уважаемые программисты объясните пожалуста как работает строчка ниже, в частности, интересует функция Cells():
2.1 Что означают передаваемые параметры Rows.Count и цифра 2 в заголовке функции?
2.2 Не совсем понятно .End(xlUp) и Value по отдельности что означают и чем являются в заголовке функции Range. Что означает точка?

a = Range([A1], Cells(Rows.Count, 2).End(xlUp)).Value

[SAS888]

1. Cells(x, y) - ячейка, номер строки которой - x, номер столбца - y.
2. В VBA Excel ячейка имеет "кучу" свойств. Так, в частности, Cells(x, y).Value означает значение ячейки. Cells(x, y).Address - адрес ячейки и т.д. Подробнее читайте в Help-е.
3. Выражение Cells(Rows.Count, 2).End(xlUp) возвращает последнюю заполненную ячейку во 2-м столбце. Это тоже самое, что "вручную" выделить последнюю ячейку в столбце (например, для Excel 2003 это "B65536") и нажать Ctrl+[стрелка вверх].
4. Таким образом, выражение a = Range([A1], Cells(Rows.Count, 2).End(xlUp)).Value означает: определяем двумерный массив "a" (строка, столбец) и присваиваем ему значения ячеек из диапазона с ячейки "A1" до последней заполненной ячейки в столбце "B".
5. После всего сказанного, думаю, что Вы без труда сами разберетесь в следующем коде. Подразумевается (всвязи с Вашими пожеланиями), что время находится в 1-м столбце, а соответствующие ему значения в 3-м. Второй столбец хоть и входит в массив "a" (условие непрерывности диапазона для формирования массива), но в коде никак не обрабатывается и вообще не используется.

Private Sub CommandButton1_Click()
    Dim i As Long, j As Long, k As Long, q As String, w As String, a(), b(), c()
    Application.ScreenUpdating = False: a = Range([A1], Cells(Rows.Count, 3).End(xlUp)).Value
    q = CStr(TimeSerial(0, 0, 0)): w = CStr(TimeSerial(0, 20, 0))
    ReDim b(1 To UBound(a, 1)): j = 1: k = 1: ReDim c(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        Select Case CStr(CDate(a(i, 1)))
        Case q: ReDim b(1 To UBound(a, 1)): j = 1: b(j) = a(i, 3)
        Case Is < w: j = j + 1: b(j) = a(i, 3)
        Case w: j = j + 1: b(j) = a(i, 3): c(k, 1) = Application.Max(b): k = k + 1
        End Select
    Next
    Range([D1], Cells(UBound(c, 1), 4)).Value = c 'Вставляем в 4-й столбец
End Sub

[Tidus]

Сообщение от SAS888

1. Cells(x, y) - ячейка, номер строки которой - x, номер столбца - y.
2. В VBA Excel ячейка имеет "кучу" свойств. Так, в частности, Cells(x, y).Value означает значение ячейки. Cells(x, y).Address - адрес ячейки и т.д. Подробнее читайте в Help-е.
3. Выражение Cells(Rows.Count, 2).End(xlUp) возвращает последнюю заполненную ячейку во 2-м столбце. Это тоже самое, что "вручную" выделить последнюю ячейку в столбце (например, для Excel 2003 это "B65536") и нажать Ctrl+[стрелка вверх].

Спасибо.
1. А у вас есть Help на русском языке?Поделитесь пожалуйста ссылочкой.
2. А почему вы в заголовок функции передаёте параметр Rows.Count , а не например "единичку" для строки ? Я с Rows.Count не разобрался, что он означает...

[SAS888]

1. На русском у меня нет справки. Поищите в I-нете. Наверняка что-нибудь найдете. Да и на нашем форуме есть соответствующие ссылки. Я не буду их за Вас искать.
2. Rows.Count - это константа (не функция), которую Excel хранит в модуле класса, равная максимальному количеству строк на рабочем листе. В применении к вышесказанному, нам нужна именно последняя строка в столбце (Rows.Count), а не первая ("единичка").

[Tidus]

Вот 1 пример на рисунке, когда из диапазона от А1 до E65536 необходимые данные вставляются в столбец по порядку от I1 до I65536.
1 пример расположения столбцов:



Ниже я выделил красным цветом, что требуется поправить в коде, что бы изменить конец диапазона ячеек в двухмерном массиве для 1 примера расположения столбцов на рисунке.
1 пример кода:

Private Sub CommandButton1_Click()
    Dim i As Long, j As Long, k As Long, q As String, w As String, a(), b(), c()
    Application.ScreenUpdating = False: a = Range([A1], Cells(Rows.Count, 5).End(xlUp)).Value
    q = CStr(TimeSerial(0, 0, 0)): w = CStr(TimeSerial(0, 20, 0))
    ReDim b(1 To UBound(a, 1)): j = 1: k = 1: ReDim c(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        Select Case CStr(CDate(a(i, 1)))
        Case q: ReDim b(1 To UBound(a, 1)): j = 1: b(j) = a(i, 5)
        Case Is < w: j = j + 1: b(j) = a(i, 5)
        Case w: j = j + 1: b(j) = a(i, 5): c(k, 1) = Application.Max(b): k = k + 1
        End Select
    Next
    Range([I1], Cells(UBound(c, 1), 9)).Value = c 'Вставляем в 9-й столбец
End Sub

[Tidus]

Уважаемый SAS888, а что поправить в коде, что бы формировался двумерный массив из B3 до E65536 и вставка начиналась с I5 (рисунок ниже), то есть нужно реализовать код при таком расположении столбцов. Я пытался и так вставлял и эдак, не получается, постоянно вылетает ошибка....

2 пример расположения столбцов:

[SAS888]

Пример во вложении. Что не понятно - спрашивайте.

[Tidus]

Сообщение от SAS888

Пример во вложении. Что не понятно - спрашивайте.

Здравствуйте SAS888, спасибо за код, представленный ниже в файле test5, который выполнен для 2 примера отображения столбцов в посте #47. Выделил основные изменения, внесенные в код:
2 пример кода:

   
Private Sub CommandButton1_Click() 
Dim i As Long, j As Long, k As Long, q As String, w As String, a(), b(), c()
    Application.ScreenUpdating = False: a = Range([B3], Cells(Rows.Count, "E").End(xlUp)).Value
    q = CStr(TimeSerial(0, 0, 0)): w = CStr(TimeSerial(0, 20, 0))
    ReDim b(1 To UBound(a, 1)): j = 1: k = 1: ReDim c(1 To UBound(a, 1), 1 To 1)
    For i = 1 To UBound(a, 1)
        Select Case CStr(CDate(a(i, 1)))
        Case q: ReDim b(1 To UBound(a, 1)): j = 1: b(j) = a(i, 4)
        Case Is < w: j = j + 1: b(j) = a(i, 4)
        Case w: j = j + 1: b(j) = a(i, 4): c(k, 1) = Application.Max(b): k = k + 1
        End Select
    Next
    Range([I5], Cells(UBound(c, 1) + 4, "I")).Value = c

End Sub

На ум пришли такие вопросы:
1. Когда в столбце указывается интервал от 00:00 до 19:00, то этот интервал ваш макрос не учитывает в своих расчётах. Почему так происходит?И как это поправить, ведь время должно лежать в диапазоне от 00:00 до 20:00.
2. А почему вы выбрали именно "E" ,а не цифру 5 например?Имеет ли цифра или буква принципиальное значение?
3. Что такое + 4 ? (Я не нашел отличий в работе кода, если убрать +4)
4. Если мне нужен диапазон времени от 1:10 до 12:15, как правильно его поменять в коде.
5. Перенес ваш код в свой файл, запустил макрос, и у меня вылезла ошибка run time error 13 - type mismatch. С чем это может быть связано?

[Teslenko_EA]

Приветсвую участников обсуждения темы.
возможный вариант решения, конечно требуется "доработка".
Евгений.