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

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

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

Войти
Новости: Доступно и просто о работе в офисных пакетах
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1 2 »   Вниз
  Печать  
Автор Тема: Сумма ячеек по метке  (Прочитано 2151 раз)
0 Пользователей и 1 Гость смотрят эту тему.
KSumatokhin
Участник
**
Offline Offline

Пол: Мужской
Расположение: Калининград
Сообщений: 9


« Стартовое сообщение: 13 Декабрь 2020, 16:28 »

Здравствуйте уважаемые форумчане,

Нужно написать функцию, которая должна проанализировать два столбца, один с названием, второй с числами,
и просуммировать только те числа, названия которых соответствуют "метке".
Упрощенно - просуммировать ячейки по правилу "метка".

Допустим у вас есть таблица:
Начало   Конец   Сумма
      
T1   U1   S1
U1   N1   5
U1   N2   4
U1   N2   4
      
T1   U2   S2
U2   N1   2
U2   N2   6
U2   N3   4

Нужно в ячейку S1 получить сумму по метке U1, т.е. нужно просуммировать ячейки по "метке" U1 по всему столбцу вниз.
Сделать это нужно не указывая диапазоны и отдельные ячейки. С учетом того, что таблица может расти и уменьшаться.

Я решал это так. В конце столбца с именами ставил "метку" END.
Т.е. дальше чем END таблица не может расти. В таблице могут постоянно добавляться строки, поэтому
нужно найти адрес метки
Код:
=ADDRESS(MATCH("END";A1:A2121;0);1;4)
Получив адрес извлекал номер строки строки =ROW(INDIRECT(E2))
Затем в отдельную ячейку (например D3) добавлял адрес диапазона, с которым нужно провести операцию, в данном случае просуммировать:
Код:
=ADDRESS(ROW(C3);COLUMN(C3);4)&":"&ADDRESS($F$2;COLUMN(C3);4)
Получается область от ячейки C3 до ячейки в столбце "C" со строкой на уровне "метки".
Затем еще один диапазон "названий" которые хранятся в столбце "А":
Код:
=ADDRESS(ROW(A3);1;4)&":"&$E$2
Ну а теперь само суммирование:
Код:
{=SUM(IF($B3=INDIRECT($E3);INDIRECT(D3);0))}

Во вложении файл с примером.

Как бы одной функцией упростить весь процесс описанный выше?
Хотелось бы иметь функцию типа =SUMLABLE("Метка";[кол-во])
Где [кол-во], это опция, указав которую функция ведет подсчет только в определенном количестве столбцов.

P.S. Работает только с OpenCalc. Удобно тем, что если поменять метки с U2 на U1, то сумма автоматом пересчитается. Не нужно указывать диапазон. Можно копировать строками.

* Пример.ods (14.39 Кб - загружено 5 раз.)
Записан
Bigor
Мастер
*****
Offline Offline

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


« Ответ #1: 13 Декабрь 2020, 17:49 »

Добрый день.

Как то все сложно, а чем =sumif() не устраивает?
Код:
=SUMIF(A4:A28;B3;C4:C28)
Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
KSumatokhin
Участник
**
Offline Offline

Пол: Мужской
Расположение: Калининград
Сообщений: 9


« Ответ #2: 13 Декабрь 2020, 18:27 »

Здравствуйте, sumif заменит только последнюю формулу, а каже остальное...
Вы скачивали файл? Посмотрите как он работает. Там одной SUMIF не обойтись же.
Записан
Bigor
Мастер
*****
Offline Offline

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


« Ответ #3: 13 Декабрь 2020, 18:38 »

Там столько всего наворочено Улыбка но если исходить из условия, посчитать сумму значений соответствующих некоторой метке, то sumif достаточно. Если условие Вами упрощено, то опишите полностью чего хотите. Зачем извращения с поиском последней строки, если можно взять заведомо превышающий диапазон?
Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
KSumatokhin
Участник
**
Offline Offline

Пол: Мужской
Расположение: Калининград
Сообщений: 9


« Ответ #4: 13 Декабрь 2020, 19:14 »

Исходная таблица довольно большая, используется для расчета электрических нагрузок. Помимо суммирования, нужно найти и максимум по такому же правилу. А еще нужно учитывать, что такие операции нужно провести с большим количеством столбцов...
Иными словами в такой таблице множество операций делается, в ней легко запутаться, поэтому поставил макер "END" для того, чтобы уменьшить сами диапазоны до необходимого, и точно понимать где у меня граница этих расчетов, после которой можно вести другие. В принципе можно и без этого.
По большому счету я хочу получить такую формулу, которая уместиться в одну ячейку, и не будет нуждаться в указании каких то диапазонов. Добавили внизу таблицы строчку с нужной меткой - вся таблица сразу же поменялась, итоговые строчки пересчитались.
Если бы можно было бы в SUMIF указать сразу весь столбец, типа =SUMIF(столбец E;B3;столбец D), то задачу с суммой можно было бы закрывать.
И осталось бы придумать что то типа MAXIF...

Могу скинуть исходную таблицу, может поможете с оптимизацией. А то так много формул, что иногда возникает ошибка и таблица не сохраняется.
Записан
kompilainenn
Мастер
*****
Offline Offline

Сообщений: 3 176



« Ответ #5: 13 Декабрь 2020, 19:21 »

Могу скинуть исходную таблицу, может поможете с оптимизацией. А то так много формул, что иногда возникает ошибка и таблица не сохраняется.
Скиньте. Мне интересна ошибка
Записан

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
Bigor
Мастер
*****
Offline Offline

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


« Ответ #6: 13 Декабрь 2020, 19:29 »

Если бы можно было бы в SUMIF указать сразу весь столбец, типа =SUMIF(столбец E;B3;столбец D), то задачу с суммой можно было бы закрывать.
А почему нельзя?
Код:
=SUMIF(e:e;B3;d:d)

=MAXIFS(C:C;A:A;B3), но лучше все-же ограничить диапазоны
зы не уверен, что =MAXIFS() есть в OOO
« Последнее редактирование: 13 Декабрь 2020, 19:32 от Bigor » Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
KSumatokhin
Участник
**
Offline Offline

Пол: Мужской
Расположение: Калининград
Сообщений: 9


« Ответ #7: 13 Декабрь 2020, 19:36 »

Вот исходный файл.
Правда, он уже раз 10 переделывался и сейчас ошибки нет.

Формула
Код:
=SUMIF(e:e;B3;d:d)
В OO не сработала. Заменил на
Код:
=SUMIF(OFFSET($A3;0;0;2000);$B3;OFFSET(D3;0;0;2000))

В приложенном файле в столбцах с W по AG решается вопрос интерполяции.
Нужно найти значение Kм для значения Kи. Таблица исходных данных находится в листе КИ.
Вопрос можно это как-то оптимизировать?

* 2020-10-26_РНУТ.ods (315.95 Кб - загружено 6 раз.)
« Последнее редактирование: 13 Декабрь 2020, 20:17 от KSumatokhin » Записан
Bigor
Мастер
*****
Offline Offline

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


« Ответ #8: 13 Декабрь 2020, 20:17 »

Заменил на
не очень удачное решение, поставьте лучше
Код:
=SUMIF(e3:e2000;B3;d3:d2000)
offset "летучая" - при любом изменении данных она будет пересчитываться.
Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
kompilainenn
Мастер
*****
Offline Offline

Сообщений: 3 176



« Ответ #9: 13 Декабрь 2020, 20:50 »

Правда, он уже раз 10 переделывался и сейчас ошибки нет.
ясно
Записан

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
Bigor
Мастер
*****
Offline Offline

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


« Ответ #10: 13 Декабрь 2020, 21:23 »

Вопрос можно это как-то оптимизировать?
Если распишите, как должна считаться. А по Вашим формулам долго разбираться придется.
Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
KSumatokhin
Участник
**
Offline Offline

Пол: Мужской
Расположение: Калининград
Сообщений: 9


« Ответ #11: 14 Декабрь 2020, 11:08 »

В моем случае интерполяция это нахождение значения функции в определенной точке на графике. В табличном виде это выглядит так:
100 123 200
1 X 2
Что бы найти X нужно: X = ((2-1)/(200-100))x(123-100)+1 = 1.23 - это сама формула.
Мне нужно находить из такой таблицы (полная версия на листе КИ в файле из ответа 7):
nэ   0,1   0,15   0,2
1   8   5,33   4
10   2,18   1,65   1,39
Красным показаны значения Км при Ки (показаны синим) для разных значений nэ (показаны черным).
Допустим, что из таблицы нужно найти Км при Ки = 0,2, nэ = 5.
Значит
1   5   10
4   X   1,39
X = (1,39-4)/(10-1)x(5-1)+4 = 2,84
Сложнее найти Км при Ки = 0,17, nэ = 5.
Потому что нужно сначала найти значения Км при Ки = 0,15 и Ки=0,2 для nэ = 1, потом для nэ=10,
а уже потом искать Км при Ки = 0,17, nэ = 5.
То есть выполнить 3 интерполяции.
Что бы автоматизировать этот процесс нужно еще решить задачу по поиску ближайших значений,
поэтому в таблице для этого отводится 11 ячеек.
Может есть путь проще, чем тот, которым пошел я? Или есть стандартные функции о которых я не знаю?

P.S. В файле на листе КИ, есть форма демонстрирующая описываемый выше метод.
Например для поиска Км при Ки=0,13 и nэ = 42 нужно ввести 0,13 в ячейку O6, 42 в ячейку М8.
« Последнее редактирование: 14 Декабрь 2020, 13:56 от KSumatokhin » Записан
Bigor
Мастер
*****
Offline Offline

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


« Ответ #12: 14 Декабрь 2020, 23:54 »

Убрал массивные формулы, и переделал интерполяцию по типу билинейной.
Таблица стала чуть быстрее считаться. Выявились расхождения с Вашим расчетом Км
Чуть дополнил лист КИ. 


* 2020-10-26_РНУТ_1.ods (412.82 Кб - загружено 5 раз.)
Записан

Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут
KSumatokhin
Участник
**
Offline Offline

Пол: Мужской
Расположение: Калининград
Сообщений: 9


« Ответ #13: 15 Декабрь 2020, 17:10 »

Bigor, спасибо большое за участие и действенную помощь!
Я переделал таблицу по вашему типу.

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

А еще на форуме увидел тему "Перевод Calc Guide 6.2", в ветке "Документация". Понятно, что сейчас кто-то занят
переводом документации OO и Calc в частности, а может и ранние версии документации переводились уже.
Вопрос выкладывается ли это куда-то, или это для личного пользования, или для пользования внутри ограниченного круга?
Записан
kompilainenn
Мастер
*****
Offline Offline

Сообщений: 3 176



« Ответ #14: 15 Декабрь 2020, 18:09 »

А еще на форуме увидел тему "Перевод Calc Guide 6.2", в ветке "Документация". Понятно, что сейчас кто-то занят
переводом документации OO и Calc в частности, а может и ранние версии документации переводились уже.
Вопрос выкладывается ли это куда-то, или это для личного пользования, или для пользования внутри ограниченного круга?
ранние русские версии есть только для OpenOffice.org 3.3.
Черновики того, что переведено из Calc Guide 6.2, выкладываются на Я.диск, ссылка в первом посте той темы про перевод Веселый
Записан

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут
Страниц: 1 2 »   Вверх
  Печать  
 
Перейти в:  

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