Объединение значений и подсчет суммы столбца

[artem300]

Всем привет,

Пример - у меня в xls в одном столбце список животных, а во втором цифры.



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

Кот,15
Собака,20
Сова,10

Количество видов животных и сколько у него строчек произвольное.

Собираю на OpenOffice Basic, что-то уже получается, что-то пока не понимаю как сделать, совсем не программист.

[bigor]

Обязательно макросом?

В файле 2 варианта формулами
1. для неотсортированного списка
2. для отсортированного

[economist]

Задача может быть сведена к двум этапам:
1) Сбор уникальных значений, его вставка в чистое место, сортировка (а она обязательно понадобится) на другом листе
2) Вставка на другом листе, рядом с уникальностями, формулы =СУММЕСЛИ(), которая суммирует нужные цифры

Такой макрос можно привязать к кнопке и любому событию листа/книги/приложения
Уникальности можно выдернуть готовой (чужой) пользовательской функцией на Basic или программированием Расширенного фильтра (наверняка сложнее в Calc, но в Excel именно это самое простое), см. вложение:

Код Выделить Развернуть


Sub GetUnique()
[ANIMALS].AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("REPORT").[ОТЧЕТ], unique:=True
[ОТЧЕТ].Sort key1:=Range("A9"), order1:=xlAscending, Header:=xlYes
[ОТЧЕТ].Offset(0, 1).FormulaR1C1 = "=SUMIF(ANIMALS,RC[-1],ЗНАЧЕНИЯ)"
Sheets("REPORT").[A8] = "По животным"
Sheets("REPORT").[B8] = "Сумма чисел"
End Sub


К сожалению в Calc 6.3.3.2 строка

[ANIMALS].AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets("REPORT").[ОТЧЕТ], unique:=True

не сработает. Поэтому ищите готовую функцию, возвращающую уникальные значения. Практика говорит что искать можно и на русскоязычных VBA-форумах, функции, как правило, легче адаптировать под StarBasic и Calc, чем обычный VBA-код. Но тогда придется перейти на формат ODS в Calc, чтобы была 100% работоспособность макросов по времени жизни продукта.

[artem300]

Спасибо за помощь, мне главное, чтобы работало, подойдут любые средства или версии.

Посмотрю решения, попробую применить.

К сожалению в Calc 6.3.3.2 строка не сработает.

А в какой версии сработает?

[economist]

Точно сработает код из #2 только в MS Excel XP, 2000, 2007, 2010, 2013, 2016, 365

Т.е. если нужно чтобы макрос заработал в OpenOffice|LibreOffice - то придется найти готовый пример получения уникальных значений (поверьте, их миллион) и вместо 2-й строки в #2 вставить его или его вызов.

[bigor]

Макрос на starbasic.
На основе предложенного mikekaganski способа работы с коллекциями

Код Выделить Развернуть

Sub Animals
    Dim iCol As Integer
    Dim NoDupes As New Collection
    oASheet = Thiscomponent.Sheets(0)
    iCol = 9 ' перебираем с 10 строки
    On Error Resume Next
    While oASheet.getCellByPosition(0, iCol).String <> ""
        iCellModel = oASheet.getCellByPosition(0, iCol).String
        NoDupes.Add(iCellModel, iCellModel)
        iCol = iCol + 1
    Wend
    On Error GoTo 0
    iCol = 2 ' вставляем с 3 строки
    For Each iCellModel In NoDupes
        oASheet.getCellByPosition(9, iCol).String=iCellModel
        oASheet.getCellByPosition(10, iCol).Formula="=SUMIF($Лист1.$A$1:$A$27;$Лист1.$j$" & iCol+1 & ";$Лист1.$C$1:$C$27)"
        iCol = iCol + 1
       
    Next iCellModel
End Sub

[artem300]

Спасибо, примеры очень пригодились, адаптировал скрипт Bigor под свои нужды.

Думаю как внести в этот скрипт усложнение, что суммы животных нужно считать в разные времена года.



Времен года больше чем 4 и расположены они с пропуском, можно как-то находить следующий столбец по названию?

[kompilainenn]

Времен года больше чем 4

вместо того, чтобы шифровать задачу, не проще ли сказать, что там на самом деле в этой таблице? учет закладок что ли?

[bigor]

суммы животных нужно считать в разные времена года

можно как-то находить следующий столбец по названию?

переписать формулу для подсчета

Код Выделить Развернуть

For Each iCellModel In NoDupes
        oASheet.getCellByPosition(9, iCol).String=iCellModel
        'oASheet.getCellByPosition(10, iCol).Formula="=SUMIF($Лист1.$A$1:$A$27;$Лист1.$j$" & iCol+1 & ";$Лист1.$C$1:$C$27)"
        oASheet.getCellByPosition(10, iCol).Formula="=SUMPRODUCT(($A$8:$A$25=$j$" & iCol + 1 & " )*($C$7:$H$7=""Весна"")*$C$8:$H$25)"
        oASheet.getCellByPosition(11, iCol).Formula="=SUMPRODUCT(($A$8:$A$25=$j$" & iCol + 1 & " )*($C$7:$H$7=""Лето"")*$C$8:$H$25)"
        oASheet.getCellByPosition(12, iCol).Formula="=SUMPRODUCT(($A$8:$A$25=$j$" & iCol + 1 & " )*($C$7:$H$7=""Осень"")*$C$8:$H$25)"
        oASheet.getCellByPosition(13, iCol).Formula="=SUMPRODUCT(($A$8:$A$25=$j$" & iCol + 1 & " )*($C$7:$H$7=""Зима"")*$C$8:$H$25)"
        iCol = iCol + 1       
    Next iCellModel
можно делать пропуски между столбцами, только диапазон должен включать все нужные столбцы

[artem300]

Большое спасибо, скрипт работает и дает результат, примеры пригодились.

Это упрощение и более интересная тема подсчета, чем продажи цифровых продуктов.