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

[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)

[KSumatokhin]

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

[bigor]

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

[KSumatokhin]

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

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

[kompilainenn]

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

Скиньте. Мне интересна ошибка

[bigor]

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

А почему нельзя?

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

=SUMIF(e:e;B3;d:d)
=MAXIFS(C:C;A:A;B3), но лучше все-же ограничить диапазоны
зы не уверен, что =MAXIFS() есть в OOO

[KSumatokhin]

Вот исходный файл.
Правда, он уже раз 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]

Заменил на

не очень удачное решение, поставьте лучше

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

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

[kompilainenn]

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

ясно

[bigor]

Вопрос можно это как-то оптимизировать?

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

[KSumatokhin]

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

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]

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

[KSumatokhin]

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

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

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

[kompilainenn]

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

ранние русские версии есть только для OpenOffice.org 3.3.
Черновики того, что переведено из Calc Guide 6.2, выкладываются на Я.диск, ссылка в первом посте той темы про перевод