Автоматизации сортировки данных. Какие варианты?

[Nomercy]

Добрый день!
Сам я не программист, только начал изучать VB (в связи с обстоятельствами на работе). Однако у меня есть одна гигантская проблема - необходимо автоматизировать сортировку и получение результата по определенной группе данных. На одном листе экселя имеются порядка 70000-100000 записей (каждый день варьируются):
Столбец А содержит в себе повторяющиеся значения, которым соотвествуют по 2 значения (в столбцах B и С)
Требуется:
Для каждого уникального значения в столбце А подсчитать
сумму всех его значений в столбцах B и С.
В примере на листе я показал как я решаю эту проблему вручную (увы, макросы мне тут не помощники: так как данные каждый день новые и колличество уникальных значений каждый раз разное)

Понимаю, что все это вероятно делается через массивы, но пока еще не горазд написать код, так как не особо представляю даже как данные в массив из ячеек загнать

[Dophin]

то есть для наглядности необходимо было выдавать файл на 80 тыщ строк?

Что надо в итоге получить я так и не понял.

[EducatedFool]

увы, макросы мне тут не помощники: так как данные каждый день новые и колличество уникальных значений каждый раз разное

Наоборот, в таких случаях (тем более, при таких объемах данных) только макросы вам и помогут.

Посмотрите пример макроса в файле:



Вот код основной функции:

Sub ПримерИспользования()
    ' отключаем обновление экрана
    Application.ScreenUpdating = False
    ' считываем массив с листа - в него попадут все заполненные строки
    Массив = Range([A1], Range("A" & Rows.Count).End(xlUp)).Resize(, 3).Value

    ' объединяем уникальные, суммируя данные в столбцах 2 и 3
    arr = JoinedArray(Массив, 1, "2,3")

    Range("e:g").ClearContents    ' очистка содержимого столбцов E F G
    ' заносим массив на лист, начиная с ячейки e1
    Range("e1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr

    '  пишем формулу в столбец H (сразу во все ячейки)
    Range("h1").Resize(UBound(arr, 1)) = "=rc[-1]+rc[-2]"
    ' заменяем формулы значениями
    Range("h1").Resize(UBound(arr, 1)).Value = Range("h1").Resize(UBound(arr, 1)).Value
    ' удаляем лишние столбцы - F и G
    Range("f:g").EntireColumn.Delete
    ' автоподбор ширины столбцов
    Range("e:g").EntireColumn.AutoFit
End Sub

[EducatedFool]

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

Function JoinedArray(ByVal arr As Variant, ByVal ComparedColumn As Long, _
                     Optional ByVal ColumnsForSum As String, Optional ByVal ColumnsForJoin As String, _
                     Optional ByVal JoinSeparator As String = ", ") As Variant
    ' осуществляет объединение строк в массиве
    ' получает в качестве параметров исходный массив, и номер столбца ComparedColumn,
    ' по которому осуществляется сравнение строк
    ' ---------------------------------------------
    ' для совпадающих строк:
    ' - суммируются значения в столбцах, перечисленных через запятую в переменной ColumnsForSum
    ' - соединяются (через разделитель JoinSeparator) значения в столбцах,
    '   перечисленных через запятую в переменной ColumnsForJoin
    ' ---------------------------------------------
    ' функция возвращает новый массив (возможно, с меньшей размерностью по вертикали)


    On Error Resume Next
    If Not IsArray(arr) Then MsgBox "Это не массив!", vbCritical: Exit Function
    If ComparedColumn > UBound(arr, 2) Then MsgBox "Нет такого столбца в массиве!", vbCritical: Exit Function
    If ComparedColumn < LBound(arr, 2) Then MsgBox "Нет такого столбца в массиве!", vbCritical: Exit Function

    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, ComparedColumn) <> "" Then
            For j = i + 1 To UBound(arr, 1)
                If arr(j, ComparedColumn) = arr(i, ComparedColumn) Then
                    ' для последующего удаления этой строки из массива
                    arr(j, ComparedColumn) = Empty    ' затираем значение в сравниваемом столбце

                    ' суммируем строки - результат в верхнюю строку
                    For Each col In Split(ColumnsForSum, ",")
                        nCol = Val(col)
                        If nCol > 0 And nCol <= UBound(arr, 2) And nCol <> ComparedColumn Then
                            arr(i, nCol) = Val(Replace(arr(i, nCol), ",", ".")) _
                                           + Val(Replace(arr(j, nCol), ",", "."))
                        End If
                    Next

                    ' сцепляем строки - результат в верхнюю строку
                    For Each col In Split(ColumnsForJoin, ",")
                        nCol = Val(col)
                        If nCol > 0 And nCol <= UBound(arr, 2) And nCol <> ComparedColumn Then
                            If Len(Trim(arr(j, nCol))) > 0 Then
                                arr(i, nCol) = Trim(arr(i, nCol)) & JoinSeparator & Trim(arr(j, nCol))
                            End If
                        End If
                    Next
                End If
            Next j
        End If
    Next i

    ' удаляем ненужные (пустые) строки
    Dim iCount As Long    ' кол-во непустых строк
    For i = LBound(arr) To UBound(arr)
        iCount = iCount - (arr(i, ComparedColumn) <> "")
    Next i

    ' формируем новый массив
    ReDim narr(LBound(arr, 1) To iCount + LBound(arr, 1) - 1, LBound(arr, 2) To UBound(arr, 2))

    iCount = LBound(narr)    ' счётчик записей
    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, ComparedColumn) <> "" Then
            For j = LBound(arr, 2) To UBound(arr, 2)
                narr(iCount, j) = arr(i, j)
            Next j
            iCount = iCount + 1
        End If
    Next i

    JoinedArray = narr
End Function

[Nomercy]

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

то есть для наглядности необходимо было выдавать файл на 80 тыщ строк?

Что надо в итоге получить я так и не понял.

Извиняюсь, Dophin. Я на форумах не особо знаю этикет в размещении примеров.
Данные в столбцах А:С - есть исходные
Столбцы L:M - содержат результат ручной обработки данных
Пример ручной обработки (обычно в день у меня занимает по 1,5-2 часа)
находится в столбцах О:S

Алгоритм следующий: данные в столбце А повторяются, им соответствуют пара данных из столбца В и С. Я сортирую данные диапазона А:С по столбцу А. В итоге получаю набор блоков со сгруппированными значениями в столбце А и соответствующие им значения по В и С. И для каждого блока суммирую эти значения (В + С).
В итоге получаю сводную таблицу по примеру L:М

[Nomercy]

EducatedFool, большое спасибо за отклик.
Сейчас попробую.

У меня попутно вопрос для самообразования, почему макросы выгоднее в обработке большого объёма данных нежели использование массивов?


П.С. EducatedFool, просто невероятная штука!!!!!!! Гигантское спасибоооо)) Еще больше подстегнуло к изучению програмирования))
Не думал что код размером в несколько десятков строк так эффективен

[EducatedFool]

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

почему макросы выгоднее в обработке большого объёма данных нежели использование массивов?

А что вы понимаете под использованием массивов?

Мой макрос как раз и использует массивы.
Как вы намереваетесь использовать массивы без макроса?

Если речь про формулы массива, то при таком объёме данных любые формулы начнут тормозить при пересчёте.
Поэтому оптимальнее использовать макросы.
Если бы количество строк исчислялось не десятками тысяч, а сотнями, можно было бы попробовать использовать формулы.

[Nomercy]

Наверно я написал какую то глупость. Пойду учить матчасть

П.С: После беглого прочтения нескольких книг по VBA (в основном для чайников) я подумал что из данных необходимо сформировать двумерный массив . А затем внутри этого массива произвести обработку этих данных согласно необходимого алгоритма. Потом из полученных данных сформировать новый массив и выгрузить его на лист.
Просто я думал что макросы не могут содержать в себе переменные или массивы. Вобщем сажусь учить VBA, а то ща всякого бреда тут наговорю))

[EducatedFool]

После беглого прочтения нескольких книг по VBA (в основном для чайников) я подумал что из данных необходимо сформировать двумерный массив . А затем внутри этого массива произвести обработку этих данных согласно необходимого алгоритма. Потом из полученных данных сформировать новый массив и выгрузить его на лист.

Именно так и работает мой макрос.

Только обработка двумерного массива вынесена в отдельную процедуру JoinedArray (которая более универсальна, чем требуется в данном случае)

Можно, конечно, намного сократить код, убрав лишние циклы и проверки, вплоть до полного переписывания функции под ваши нужды
Тем самым можно увеличить скорость выполнения кода.
Но это уже предстоит вам сделать самостоятельно - я лишь показал пример.

[IgorGO]

а может сводная таблица с этим справится? попробуйте...