Суммируем колонку блоками по общему ID, округляя блок до 300

[petav]

Дано:
Список задач
Часть задач имеет общий TicketID (Значит относятся к одному процессу)

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


Task Duration TicketID
1     10          1
2     10          1
3     10          1
4     10          1
5     10          2
6     10          2
7     10          3
8     10          3
9     10          4

Требуется:
1) Получить  общую duration каждого TicketID
2) Округлить duration TicketID до 300
3) Сложить duration всех TicketID

В примере:
General duration TickeidID №1: 40 (округленная до 300: 300)
General duration TickeidID №2: 20 (округленная до 300: 300)
General duration TickeidID №3: 20 (округленная до 300: 300)
General duration TickeidID №4: 10 (округленная до 300: 300)
All duration=1200

Стенд https://disk.yandex.ru/d/dfsCeC9q-S-IYA

Это возможно формулой реализовать?

[akelot]

То есть, если Duration меньше 300, то необходимо округлить до 300? Я правильно понимаю? Округление можно сделать так "=ЕСЛИ(B2<=300;300;B2)" А вот с суммированием значений из разных строк по определенным параметрам, на вскидку не подскажу.

[kompilainenn]

А вот с суммированием значений из разных строк по определенным параметрам

СУММЕСЛИ (SUMIF)

[mikekaganski]

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

=SUMPRODUCT(CEILING(SUMIFS(Tickets.C4:C90;Tickets.K4:K90;Tickets.K4:K90);300)/COUNTIFS(Tickets.K4:K90;Tickets.K4:K90))

Кстати, 300 с - это не 15 минут

[petav]

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

=SUMPRODUCT(CEILING(SUMIFS(Tickets.C4:C90;Tickets.K4:K90;Tickets.K4:K90);300)/COUNTIFS(Tickets.K4:K90;Tickets.K4:K90))

Сложность
1) Получаю #DIV/0!
когда TicketID отсутствует
2) плюс "длинна@ столбца C заранее не известна
"eeigor" подсказал так:
вместо

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


Tickets.C4:C90

использовать

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


$Tickets.$C$4:INDEX($C$4:$C$999;MATCH(9,99E+307;$C$4:$C$999))


Это можно объеденить

Кстати, 300 с - это не 15 минут

900
fixed!
thaks.

[mikekaganski]

Все объединения - это уже детали. Ответ на вопрос должен быть свободен от таких деталей, чтобы быть понятным и концентрироваться на конкретной проблеме.

Что касается ошибки при отсутствии данных - используйте IFERROR. Кстати, обратите внимание, что такая проблема часто означает, что Вы оперируете не сырыми данными в непрерывном блоке, а форматируете эти исходные данные. Это приводит к усложнению формул, появлению ошибок и удлинению расчёта. Лучше всего храните сырые данные отдельно, производите расчёты на них в наиболее простом виде, а форматирование делайте отдельно (скажем, с использованием сводных таблиц).

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

9,99E+307

Обратите внимание, что это не максимальное число, представимое в Calc. Максимальное равно (2^53-1)*2^(1024-53) = 1,7976931348623157E+308.

[eeigor]

Присвойте имя всей конструкции и потом объединяйте что хотите
https://forumooo.ru/index.php/topic,8750.msg59303.html#msg59303

[mikekaganski]

Присвоить имя конструкции, ссылающейся на лист? У меня такое работает только на том же листе (и в случае, когда конструкция локальна для этого листа, и когда она глобальна).

[mikekaganski]

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


$Tickets.$C$4:INDEX($C$4:$C$999;MATCH(9,99E+307;$C$4:$C$999))


Можно упростить:

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


$Tickets.$C$4:INDEX($Tickets.$C:$C;MATCH(1,79E+308;$Tickets.$C:$C))


(в Вашем исходном коде не единообразно использованы имена листа).

[eeigor]

Диапазон берём с запасом. Весь столбец – не рекомендую. Файл и так тормозит

[mikekaganski]

Весь столбец – не рекомендую. Файл и так тормозит

MATCH без опционального аргумента не может быть лимитирующим фактором ни при каких обстоятельствах. Даже при включении больших листов (с 16 млн строк). Потому что бинарный поиск для 16 миллионов строк даёт не более 24 сравнений. А в случае, скажем, 1000 строк - 10 сравнений. Причём каждое сравнение в этом случае - реально эффективное, без огромных затрат. То есть "лишние" 14 сравнений Вы не сможете измерить никак.

[sokol92]

Вы не сможете измерить никак.

Меняем формулу и запускаем макрос.

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

Option Explicit
Sub Timing
  Dim t As Long, i As Long, ub As Long, oCell
  ub=1000
  oCell=ThisComponent.Sheets.getByName("Tickets").getCellByPosition(2, 3)   ' ячейка C4 (любая из ячеек, от которой зависит формула)
  t=GetSystemTicks()
  For i=1 To ub
    oCell.value=IIf(i Mod 2=0, 720, 721) 
  Next i

  Msgbox "Время (мс): " & (GetSystemTicks()-t)
End Sub

[mikekaganski]

Меняем формулу и запускаем макрос.

У меня получается на имеющемся файле из начального сообщения, с активным листом "Billing", при первоначальной формуле в ячейке Billing.B3: 626-742 мс (среднее в районе 680); при формуле, изменённой на "=IFERROR(SUMPRODUCT(CEILING.MATH($Tickets.C4:INDEX($Tickets.C:C;MATCH(9,99E+307;$Tickets.C:C));300));0)": 625-748 мс (среднее в районе 680). Что я могу из этого извлечь для опровержения ответа #10?

[sokol92]

Это подтверждает, что две формулы имеют практически одинаковую скорость перерасчета.

[mikekaganski]

На самом деле обратите внимание на примечание о непрерывности данных в ответе #5. Особенность реализации хранения данных в Calc создаёт кратную разницу в быстродействии при наличии, скажем, просто пропущенных строк, или вставок с "подзаголовками". Именно на это нужно смотреть в первую очередь при тормозах, ну и на квадратичные алгоритмы. У бинарного поиска алгоритм не квадратичный (его сложность O(log N))