Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

31 Март 2020, 15:43 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Здесь можно поблагодарить участников форума Улыбка
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: Объединение значений и подсчет суммы столбца  (Прочитано 1449 раз)
0 Пользователей и 1 Гость смотрят эту тему.
artem300
Участник
**
Offline Offline

Сообщений: 5


« Стартовое сообщение: 19 Ноябрь 2019, 19:51 »

Всем привет,

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



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

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

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

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

* cat_dog_1.xlsx (7.34 Кб - загружено 5 раз.)
« Последнее редактирование: 19 Ноябрь 2019, 19:54 от artem300 » Записан
Bigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 865


« Ответ #1: 20 Ноябрь 2019, 07:42 »

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

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

* cat_dog_2.xlsx (9.37 Кб - загружено 6 раз.)
Записан
economist
Форумчанин
***
Offline Offline

Сообщений: 1 161


« Ответ #2: 20 Ноябрь 2019, 09:17 »

Задача может быть сведена к двум этапам:
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% работоспособность макросов по времени жизни продукта.

* cat_dog_3.xls (39 Кб - загружено 3 раз.)
Записан

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
artem300
Участник
**
Offline Offline

Сообщений: 5


« Ответ #3: 20 Ноябрь 2019, 19:37 »

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

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

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

А в какой версии сработает?
Записан
economist
Форумчанин
***
Offline Offline

Сообщений: 1 161


« Ответ #4: 20 Ноябрь 2019, 21:48 »

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

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

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
Bigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 865


« Ответ #5: 21 Ноябрь 2019, 09:25 »

Макрос на 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
Участник
**
Offline Offline

Сообщений: 5


« Ответ #6: 25 Ноябрь 2019, 19:37 »

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

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



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

* cat_dog_4.xlsx (7.47 Кб - загружено 4 раз.)
Записан
kompilainenn
Мастер
*****
Offline Offline

Сообщений: 2 895



« Ответ #7: 25 Ноябрь 2019, 19:39 »

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

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
Bigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 865


« Ответ #8: 25 Ноябрь 2019, 20:41 »

суммы животных нужно считать в разные времена года
можно как-то находить следующий столбец по названию?
переписать формулу для подсчета
Код:
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
Участник
**
Offline Offline

Сообщений: 5


« Ответ #9: 17 Декабрь 2019, 17:14 »

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

Это упрощение и более интересная тема подсчета, чем продажи цифровых продуктов.
Записан
Страниц: 1   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!