Сумма ячеек по метке

Автор KSumatokhin, 13 декабря 2020, 16:28

0 Пользователи и 1 гость просматривают эту тему.

KSumatokhin

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

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

Допустим у вас есть таблица:

Начало   Конец   Сумма
      
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, то сумма автоматом пересчитается. Не нужно указывать диапазон. Можно копировать строками.

bigor

Добрый день.

Как то все сложно, а чем =sumif() не устраивает?
=SUMIF(A4:A28;B3;C4:C28)
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

KSumatokhin

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

bigor

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

KSumatokhin

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

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

kompilainenn

Цитата: KSumatokhin от 13 декабря 2020, 19:14Могу скинуть исходную таблицу, может поможете с оптимизацией. А то так много формул, что иногда возникает ошибка и таблица не сохраняется.
Скиньте. Мне интересна ошибка
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

bigor

#6
Цитата: KSumatokhin от 13 декабря 2020, 19:14Если бы можно было бы в SUMIF указать сразу весь столбец, типа =SUMIF(столбец E;B3;столбец D), то задачу с суммой можно было бы закрывать.
А почему нельзя? =SUMIF(e:e;B3;d:d)
=MAXIFS(C:C;A:A;B3), но лучше все-же ограничить диапазоны
зы не уверен, что =MAXIFS() есть в OOO
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

KSumatokhin

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

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

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

bigor

Цитата: KSumatokhin от 13 декабря 2020, 19:36Заменил на
не очень удачное решение, поставьте лучше =SUMIF(e3:e2000;B3;d3:d2000)
offset "летучая" - при любом изменении данных она будет пересчитываться.
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

kompilainenn

Цитата: KSumatokhin от 13 декабря 2020, 19:36Правда, он уже раз 10 переделывался и сейчас ошибки нет.
ясно
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

bigor

Цитата: Bigor от 13 декабря 2020, 20:17Вопрос можно это как-то оптимизировать?
Если распишите, как должна считаться. А по Вашим формулам долго разбираться придется.
Поддержать разработчиков LibreOffice можно можно тут, а наш форум вот тут

KSumatokhin

#11
В моем случае интерполяция это нахождение значения функции в определенной точке на графике. В табличном виде это выглядит так:
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.

bigor

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

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

KSumatokhin

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

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

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

kompilainenn

Цитата: KSumatokhin от 15 декабря 2020, 17:10А еще на форуме увидел тему "Перевод Calc Guide 6.2", в ветке "Документация". Понятно, что сейчас кто-то занят
переводом документации OO и Calc в частности, а может и ранние версии документации переводились уже.
Вопрос выкладывается ли это куда-то, или это для личного пользования, или для пользования внутри ограниченного круга?
ранние русские версии есть только для OpenOffice.org 3.3.
Черновики того, что переведено из Calc Guide 6.2, выкладываются на Я.диск, ссылка в первом посте той темы про перевод :D
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут